存储过程
创建
DELIMITER //
CREATE PROCEDURE proc_1(IN a INT,IN b INT,OUT c INT)
BEGIN
? ?SET c = a+b;
END//
调用
SET @m=0;
CALL proc_1(3,2,@m);
SELECT @m FROM DUAL;
存储过程中的变量的使用
局部变量
DELIMITER //
CREATE PROCEDURE proc_2(IN a INT,OUT r INT)
BEGIN
DECLARE X INT DEFAULT 0;? ?-- 定义局部变量
DECLARE Y INT DEFAULT 1;
SET X=a*a;
SET Y=a/2;
SET r=X+Y;
END//
SELECT X FROM DUAL;
x，y为局部变量 查询不到，全局才可以
用户变量
DELIMITER //
CREATE PROCEDURE proc_2(IN a INT,OUT r INT)
BEGIN
DECLARE X INT DEFAULT 0;
DECLARE Y INT DEFAULT 1;
SET X=a*a;
SET Y=a/2;
SET r=X+Y;
END//
SET @m=1;
CALL proc_2(6,@m);
SELECT @m FROM DUAL
查询学生数量并返回
DELIMITER //
CREATE PROCEDURE proc_3(OUT c INT)
BEGIN
SELECT COUNT(stu_num) INTO c FROM students;--将查询结果赋值给c
END//
CALL proc_3(@m);
SELECT @m FROM DUAL;
使用变量注意事项
因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享使用，建议少用。
存储过程的参数
共有三种 in\out\inout
in输入参数：在调用过程中传递数据给存储过程的参数
创建添加学生信息的存储过程：
DELIMITER //
CREATE PROCEDURE proc_4(IN snum INT,IN sname VARCHAR(20),IN sage INT,IN sgender CHAR(2),IN stel CHAR(11),IN sqq VARCHAR(11),IN sdate DATETIME,IN cid TINYINT)
BEGIN
INSERT INTO students(stu_num,stu_name,stu_age,stu_gender,stu_tel,stu_qq,stu_enterdate,cid)?
VALUES(snum,sname,sage,sgender,stel,sqq,sdate,cid);
END//
CALL proc_4(12,'sbb',30,'女','1558','7749',NOW(),3)
out输出参数：将存储过程在产生的数据返回给过程的调用者
创建根据学号，查学生姓名的存储过程
DELIMITER //
CREATE PROCEDURE proc_5(IN id INT,OUT sname VARCHAR(20))
BEGIN
SELECT stu_name INTO sname FROM students WHERE stu_num=id;
END//
SET @name='';
CALL proc_5(6,@name)
SELECT @name FROM DUAL?
inout参数
DELIMITER //
CREATE PROCEDURE proc_6(INOUT str VARCHAR(20))
BEGIN
SELECT stu_name INTO str FROM students WHERE stu_num=str;?
END//
SET @name-'6';
CALL proc_6(@name);
SELECT @name FROM DUAL
存储过程中的流程控制
分支语句
if-then-else语句
创建存储过程输入1，添加1条数据，else添加另一条数据
DELIMITER //
CREATE PROCEDURE proc_7(IN a INT)
BEGIN
? IF a=1 THEN
? ? ?INSERT INTO classes(class_name,class_size) VALUES('2101',24);
? ELSE
? ? ?INSERT INTO classes(class_name,class_size) VALUES('2102',66);
? END IF;
END//
CALL proc_7(1)
CALL proc_7(2)
case语句
DELIMITER //
CREATE PROCEDURE proc_8(IN a INT)
BEGIN
? CASE a
? WHEN 1 THEN
? ? ?INSERT INTO classes(class_name,class_size) VALUES('2003',24);
? WHEN 2 THEN
? ? ?INSERT INTO classes(class_name,class_size) VALUES('2004',44);
? ELSE
? ? ?INSERT INTO classes(class_name,class_size) VALUES('2005',34);
? END CASE;
END//
CALL proc_8(1);
CALL proc_8(3)
循环语句
while语句
创建存储过程，添加指定数目的班级信息
DELIMITER//
CREATE PROCEDURE proc_9(IN num INT)
BEGIN
? DECLARE i INT;
? SET i = 0;
? WHILE i<num DO?
? ? INSERT INTO students(stu_name,stu_age,stu_gender,stu_tel,stu_qq,stu_enterdate) VALUES('循环',55,'男','55','66',NOW());
? ? SET i=i+1;
? END WHILE;
END//
CALL proc_9(3)
repeat语句
DELIMITER//
CREATE PROCEDURE proc_10(IN num INT)
BEGIN
? DECLARE i INT;
? SET i = 0;
? REPEAT
? ? INSERT INTO students(stu_name,stu_age,stu_gender,stu_tel,stu_qq,stu_enterdate) VALUES('循环1',55,'男','55','66',NOW());
? ? SET i=i+1;
? UNTIL i>=num
? END REPEAT;
END//
CALL proc_10(3)
loop语句
DELIMITER//
CREATE PROCEDURE proc_11(IN num INT)
BEGIN
? DECLARE i INT;
? SET i = 0;
? myloop:LOOP
? ? INSERT INTO students(stu_name,stu_age,stu_gender,stu_tel,stu_qq,stu_enterdate) VALUES('循环2',55,'男','55','66',NOW());
? ? SET i=i+1;
? ? IF i=num THEN
? ? ? ?LEAVE myloop;
? ? END IF;
? END LOOP;
END//
CALL proc_11(3)
存储过程的管理
存储过程是属于数据库的，只能在本数据库中调用。
查询存储过程
SHOW PROCEDURE STATUS WHERE db='gyq';
SHOW CREATE PROCEDURE gyq.proc_2
修改存储过程
删除存储过程
drop删除数据库中的对象，数据库，数据表，列，存储过程，触发器，视图
delete删除表中的数据
DROP PROCEDURE proc_11
游标
游标可以逐条取出结果集中的每条数据
使用步骤：声明游标，打开游标?，使用游标，关闭游标。
DELIMITER//
CREATE PROCEDURE proc_11(OUT result VARCHAR(2000))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE sname VARCHAR(20);
DECLARE snum INT;
DECLARE sage INT;
DECLARE sqq VARCHAR(11);
DECLARE num INT;
DECLARE i INT;
DECLARE str VARCHAR(50);
DECLARE mycursor CURSOR FOR SELECT stu_name,stu_num,stu_age,stu_qq FROM students;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SELECT COUNT(1) INTO num FROM students;
OPEN mycursor;
SET i=0;
WHILE i<=num DO
? FETCH mycursor INTO sname,snum,sage,sqq;
? SET i=i+1;
? SET str=CONCAT_WS('-',sname,snum,sage,sqq);
? SET result = CONCAT_WS(',',result,str);
END WHILE;
CLOSE mycursor;
END//
SET @str='';
CALL proc_11(@str);
SELECT @str FROM DUAL;