8、存储过程
2020-07-08 本文已影响0人
knock
8、存储过程
8.1、删除存储过程
DROP PROCEDURE sel;
8.2、创建存储过程
#---------------------------存储过程-----------------------------------#
delimiter // ###声明以//为结束符号
CREATE PROCEDURE sel()
BEGIN
SELECT username FROM student;
END //
#---------------------------存储过程-----------------------------------#
#执行存储过程
CALL sel();
8.3、创建使用局部变量的存储过程
DROP PROCEDURE IF EXISTS sel; #先删除存储过程
#---------------------------存储过程-----------------------------------#
delimiter // #声明以//为结束符号
CREATE PROCEDURE sel()
BEGIN
DECLARE name1 VARCHAR(200) DEFAULT '我是局部变量name1'; #声明局部变量
DECLARE name2 VARCHAR(200) DEFAULT '我是局部变量name2'; #声明局部变量
SELECT name1,name2; # 查看局部变量的值
SET name1='ls',name2='s1'; #给局部变量赋值
SELECT name1,name2; #查看改变后的局部变量
SELECT username,pwd from student where username=name1 and pwd=name2; ##将局部变量带入查询条件
END
//
#---------------------------存储过程-----------------------------------#
#执行存储过程
CALL sel();
[^]: 局部变量 只在BEGIN ... END 中有效
8.4、创建使用用户变量的存储过程
DROP PROCEDURE IF EXISTS sel; #先删除存储过程
set @name='我是用户变量';#申明一个用户变量
#---------------------------存储过程-----------------------------------#
delimiter // #声明以//为结束符号
CREATE PROCEDURE sel()
BEGIN
SELECT @name; # 查看用户变量的值
SET @name='ls2'; #给用户变量赋值
SELECT @name; #查看改变后的用户变量
SELECT username,pwd from student where username=@name; #将用户变量带入查询条件
END//
#---------------------------存储过程-----------------------------------#
#执行存储过程
CALL sel();
SELECT @name;
[^]: 用户变量 只在当前会话（连接）中有效
8.5、使用into函数给用户变量赋值
SELECT username into @name from student where username='ls'; #通过into函数 将username的值 赋值给 用户变量
SELECT @name; #查看改变后的用户变量
8.6、存储过程的in传参的使用-01
DROP PROCEDURE IF EXISTS sel; #先删除存储过程
set @name='我是用户变量';#申明一个用户变量
#---------------------------存储过程-----------------------------------#
delimiter // #声明以//为结束符号
CREATE PROCEDURE sel(in in_name VARCHAR(200) )
BEGIN
SELECT in_name;
SET in_name='新的in_name';
SELECT in_name;
END//
delimiter ; #声明以;为结束符号
#---------------------------存储过程-----------------------------------#
#执行存储过程
CALL sel(@name);
SELECT @name;
[^]: 执行CALL sel(@name）
[^]: 第一次输出in_name 输出 ’我是用户变量‘
[^]: 给in_name赋值 '新的in_name’ 后，第二次 输出in_name 输出 ‘新的in_name‘，再次输出@name，输出’我是用户变量‘
[^]: 故 将用户变量传入 in 声明的 入参参数后， 存储过程中入参参数可以获取用户变量的值，对入参参数进行修改，入参参数在存储过程中改变，但是不会改变用户变量的值
[^]: 总结：执行存储过程时，传入用户变量给 ’用in 声明的入参参数‘ ，相当于给 ’用in 声明的 入参参数‘ 初始化赋值，后面对 ’用in 声明的 入参参数‘，进行修改等任何操作都与用户变量无关
image.png
8.7、存储过程的in传参的使用-02
DROP PROCEDURE IF EXISTS sel; #先删除存储过程
set @name='我是用户变量';#申明一个用户变量
#---------------------------存储过程-----------------------------------#
delimiter // #声明以//为结束符号
CREATE PROCEDURE sel(in in_name VARCHAR(200) )
BEGIN
SELECT in_name;
SET in_name='新的in_name';
SELECT in_name;
END//
delimiter ; #声明以;为结束符号
#---------------------------存储过程-----------------------------------#
#执行存储过程
CALL sel('aaaaaaaaaaaaaaaaaaaaaa');
[^]: 执行存储过程时，可传入自定义的变量 给 ’用in 声明的入参参数‘，相当于给 ’用in 声明的 入参参数‘ 初始化赋值，后面对 ’用in 声明的 入参参数‘，进行修改等任何操作都与自定义变量无关
image.png
8.8、存储过程的out传参的使用
DROP PROCEDURE IF EXISTS sel; #先删除存储过程
set @name='我是用户变量';#申明一个用户变量
#---------------------------存储过程-----------------------------------#
delimiter // #声明以//为结束符号
CREATE PROCEDURE sel(out out_name VARCHAR(200) )
BEGIN
SELECT out_name;
SET out_name='新的out_name';
SELECT out_name;
END//
delimiter ; #声明以;为结束符号
#---------------------------存储过程-----------------------------------#
#执行存储过程
CALL sel(@name);
SELECT @name;
[^]: 执行CALL sel(@name）
[^]: 第一次输出out_name 为null
[^]: 给out_name赋值 '新的out_name' 后，第二次 输出out_name为 ‘新的out_name‘，再次输出@name，输出’‘新的out_name‘
[^]: 故 将用户变量传入 out 声明的 出参参数后， 存储过程中出参参数不可以获取用户变量的值，对出参参数进行修改，出参参数在存储过程中改变，并且会改变用户变量的值
[^]: 总结：执行存储过程时，传入用户变量给 ’用out声明的出参参数‘ ，用户变量的值不会赋值给 ’用out声明的出参参数‘，但是当改变 ’用out声明的出参参数‘时，会同时改变用户变量的值
[^]: 执行存储过程时，必须传入用户变量来对应 ‘out声明的出参参数’
image.png
8.9、存储过程的inout传参的使用
DROP PROCEDURE IF EXISTS sel; #先删除存储过程
set @name='我是用户变量';#申明一个用户变量
#---------------------------存储过程-----------------------------------#
delimiter // #声明以//为结束符号
CREATE PROCEDURE sel(INOUT inout_name VARCHAR(200) )
BEGIN
SELECT inout_name;
SET inout_name='新的inout_name';
SELECT inout_name;
END//
delimiter ; #声明以;为结束符号
#---------------------------存储过程-----------------------------------#
#执行存储过程
CALL sel(@name);
SELECT @name;
[^]: 执行CALL sel(@name）
[^]: 第一次输出inout_name为 ‘我是用户变量’
[^]: 给inout_name 赋值 '新的inout_name' 后，第二次 输出inout_name为 ‘新的inout_name‘，再次输出@name，输出’‘新的inout_name‘
[^]: 故 将用户变量传入 inout 声明的 出入参参数后， 存储过程中出入参参数可以获取用户变量的值，对出入参参数进行修改，出入参参数在存储过程中改变，并且会改变用户变量的值
[^]: 总结：执行存储过程时，传入用户变量给 ’用inout声明的出入参参数‘ ，用户变量的值会赋值给 ’用inout声明的出入参参数‘，当改变 ’用inout声明的出入参参数‘时，会同时改变用户变量的值
[^]: 执行存储过程时，必须传入用户变量来对应 ‘inout声明的出入参参数’
image.png
8.10、存储过程的流程判断
IF使用
DROP PROCEDURE IF EXISTS sel;
DELIMITER //
CREATE PROCEDURE sel(in p_customerNumber VARCHAR(11), out p_customerLevel varchar(10))
BEGIN
DECLARE creditlim VARCHAR(10);
SELECT pwd INTO creditlim
FROM student
WHERE username = p_customerNumber;
IF creditlim = 's1' THEN SET p_customerLevel = '001'; #判断开始
ELSEIF (creditlim ='s2' OR creditlim = 's3' ) THEN SET p_customerLevel = '002';
ELSE SET p_customerLevel = '003';
END IF; #判断结束
SELECT p_customerLevel;
END //
DELIMITER ;
CALL sel('ls1',@value);
CASE使用
DROP PROCEDURE IF EXISTS sel;
DELIMITER //
CREATE PROCEDURE sel(in p_customerNumber VARCHAR(11), out p_customerLevel varchar(10))
BEGIN
DECLARE creditlim VARCHAR(10);
SELECT pwd INTO creditlim
FROM student
WHERE username = p_customerNumber;
CASE creditlim
WHEN 's1' THEN
SET p_customerLevel = '111111';
WHEN 's2' THEN
SET p_customerLevel = '222222';
ELSE
SET p_customerLevel = '333333';
END CASE;
SELECT p_customerLevel;
END //
DELIMITER ;
CALL sel('ls1',@value);
WHILE使用
DROP PROCEDURE IF EXISTS sel;
DELIMITER //
CREATE PROCEDURE sel(in a int)
BEGIN
declare sum int default 0; -- default 是指定该变量的默认值
declare i int default 1;
while i<=a DO -- 循环开始
set sum=sum+i;
set i=i+1;
end while; -- 循环结束
select sum; -- 输出结果
END //
DELIMITER ;
CALL sel(3);
REPEAT使用
DROP PROCEDURE IF EXISTS sel;
DELIMITER //
CREATE PROCEDURE sel(in a int)
BEGIN
declare sum int default 0;
declare i int default 1;
repeat -- 循环开始
set sum=sum+i;
set i=i+1;
until i>a
end repeat; -- 循环结束
select sum; -- 输出结果
END //
DELIMITER ;
CALL sel(3);
LOOP使用
DROP PROCEDURE IF EXISTS sel;
DELIMITER //
CREATE PROCEDURE sel()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
loop_label: LOOP #开始
IF x > 10 THEN
LEAVE loop_label;#跳出循环体
END IF;
SET x = x + 1;
IF (x mod 2) THEN
ITERATE loop_label; #跳过循环体
ELSE
SET str = CONCAT_WS(',',str,x);
END IF;
END LOOP; #结束开始
SELECT str;
END //
DELIMITER ;
CALL sel();
