数据库存储过程

• 存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件，虽然它们的作用不仅限于批处理。在我看来， 存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。
• 为什么要使用存储过程
• 通过把处理封装在容易使用的单元中，简化复杂的操作(正如前面例子所述)。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
• create
• procedure
• porcedureName ()
• begin
• select name from user;
• end;
• 存储过程用create procedure 创建， 业务逻辑和sql写在begin和end之间。
• mysql中可用call porcedureName ();来调用过程。
• 该存储过程没有参数， 只是在调用的时候查询了用户表的用户名而已， 调用结果如下
• 删除存储过程
  • DROP PROCEDURE IF EXISTS porcedureName; -- 没有括号()
  • 三个特别的字段in，out以及inout的区别
  • 参数in的使用(代表输入，意思说你的参数要传到存过过程的过程里面去）
  • //为了避免存储过程中分号(";")结束语句，我们使用分隔符告诉mysql解释器,该段命令是否已经结束了。
  • **
  • 案例功能：求1-n的和
  • */
  • delimiter $
  • create procedure p1(in n int)
  • begin
  • declare total int default 0;
  • declare num int default 0;
  • while num < n do
  • set num:=num+1;
  • set total:=total+num;
  • end while;
  • select total;
  • end$
  • call p1(10)$
  • 参数out的使用(代表往外输出)
• //这里还要注意一点的就是我们的输出参数一定要设置相应类型的初始，否则不管你怎么计算得出的结果都为NULL
• **
• 案例功能：求1-n的和
• */
• delimiter $
• create procedure p2(in n int,out total int)
• begin
• declare num int default 0;
• set total:=0;
• while num < n do
• set num:=num+1;
• set total:=total+num;
• end while;
• end$
• 注意：对于第一个输入参数我们可以理解，但是第二个输出参数我们到底应该怎么输？
• 这里我们需要对第二个参数定义一个变量名(更形象点就是你输入一个输入类型的参数n，
• 由输出参数total往外发射输出我们只需要定义一个变量名来接收这个输出值即可)
• call p2(100,@sum)$//这里的@sum就是我定义用来接收处处total的值
• select @sum$
• 总结in、out区别：
• in:表示输入一个值，你需要一个值，我给你一个值
• out:你往外输出一个值，你输出的那个值我就拿一个变量来接收你给我输出的那个值
• 参数inout的使用(既能输入一个值又能传出来一个值)
• /**
• 功能：传一个年龄，自动让年龄增长10岁
• */
• delimiter $
• create procedure p3(inout age int)
• begin
• set age:=age+10;
• end$
• 注意：调用的时候，我这里需要和大家声明一下，inout型的参数值既是输入类型又是输出类型，你给它一个值，值不是变量，不是变量那out的时候它怎么赋给这个值是不是？
• 因此我们需要先设置一个变量并初始化这个值，调用的时候直接传这个变量即可。
• set @currentAge=8$
• call p3(@currentAge)$
• select @currentAge$
• 变量的用法：
• SELECT s.* FROM score s;
• SELECT s.* FROM student s;
• #procedure 过程
• DELIMITER $$
• CREATE PROCEDURE hello_procedure ()
• BEGIN
• SELECT 'hello procedure';
• END $$
• #调用存储过程
• CALL hello_procedure;
• #declare 声明 设置局部变量
• DELIMITER $$
• CREATE PROCEDURE sp_var01()
• BEGIN
• DECLARE nickname VARCHAR(32) DEFAULT 'unknown';
• SET nickname = '文常浩';
• SELECT nickname;
• END $$
• CALL sp_var01;
• #into 赋值
• DELIMITER $$
• CREATE PROCEDURE sp_var_into()
• BEGIN
• DECLARE sname VARCHAR(32) DEFAULT 'unknown';
• DECLARE sno INT DEFAULT 0;
• SELECT s.sno,s.sname INTO sno,sname
• FROM student s
• WHERE s.sno = 1001;
• SELECT sno,sname;
• END $$
• CALL sp_var_into();
• #成员变量只用户变量的使用
• DELIMITER $$
• CREATE PROCEDURE sp_var_02()
• BEGIN
• SET @nickname = '王启辉';
• END $$
• CALL sp_var_02();
• SELECT @nickname;
• SHOW SESSION VARIABLES;
• SELECT @@session.unique_checks;
• SELECT @@global.character_set_client;
• 、
• #存储过程出入参的使用
• DELIMITER $$
• CREATE PROCEDURE sp_var_in(IN age INT)
• BEGIN
• SET @age = age;
• END $$
• CALL sp_var_in(22);
• SELECT @age;
• #存储过程出参的使用
• DELIMITER$$
• CREATE PROCEDURE sp_var_out(IN sno INT(10),OUT sname VARCHAR(10))
• BEGIN
• SELECT s.sname INTO sname
• FROM student s
• WHERE s.sno = sno;
• END$$
• CALL sp_var_out(1001,@sname);
• SELECT @sname;
• #inout类型参数 ，这个参数既可以当成入参来使用
• #也可以当成出参来使用
• DELIMITER $$
• CREATE PROCEDURE sp_var_inout(INOUT username VARCHAR(10))
• BEGIN
• SET username = CONCAT('hello',username);
• END $$
• SET @username = '金宇';
• CALL sp_var_inout(@username);
• SELECT @username;
• #存储过程里面使用流程空控制
• #选择结构
• #if结构
• DELIMITER $$
• CREATE PROCEDURE sp_var_if(IN sage INT(3))
• BEGIN
• DECLARE result VARCHAR(2);
• IF sage = 20
• THEN SET result = '大一';
• ELSEIF sage = 21
• THEN SET result = '大二';
• ELSEIF sage = 22
• THEN SET result = '大三';
• ELSE
• SET result = '大四';
• END IF;
• SELECT result;
• END $$
• CALL sp_var_if(22);
• #case 结构
• DELIMITER $$
• CREATE PROCEDURE sp_var_case(IN age INT(3))
• BEGIN
• DECLARE result VARCHAR(2);
• CASE age
• WHEN 20
  • THEN SET result = '大一';
• WHEN 21
• THEN SET result = '大二';
• WHEN 22
• THEN SET result = '大三';
• WHEN 23
• THEN SET result = '大四';
• END CASE;
• SELECT result;
• END $$
• CALL sp_var_case(22);
• DELIMITER $$
• CREATE PROCEDURE sp_var_case02(IN age INT(3))
• BEGIN
• DECLARE result VARCHAR(2);
• CASE
• WHEN age<10
  • THEN SET result = '童年';
• WHEN age<18
• THEN SET result = '少年';
• WHEN age<30
• THEN SET result = '青年';
• WHEN age<40
• THEN SET result = '中年';
• ELSE
• SET result = '老年';
• END CASE;
• SELECT result;
• END $$
• CALL sp_var_case02(50);
• #循环结构 leave
• DELIMITER $$
• CREATE PROCEDURE sp_var_leave()
• BEGIN
• DECLARE a INT(10) DEFAULT 1;
• DECLARE str VARCHAR(255) DEFAULT 1;
• cnt:LOOP
• IF a>=10
• THEN LEAVE cnt;
• END IF;
• SET a = a+1;
• SET str = CONCAT(str,',',a);
• END LOOP cnt;
• SELECT str;
• END $$
• CALL sp_var_leave();
• #循环结构 iterate
• DELIMITER $$
• CREATE PROCEDURE sp_var_iterate()
• BEGIN
• DECLARE a INT(10) DEFAULT 1;
• DECLARE str VARCHAR(255) DEFAULT 1;
• cnt:LOOP
• SET a = a+1;
• SET str = CONCAT(str,',',a);
• IF a<10
• THEN ITERATE cnt;
• END IF;
• LEAVE cnt;
• END LOOP cnt;
• SELECT str;
• END $$
• CALL sp_var_iterate();
• #循环结构 repeat
• DELIMITER $$
• CREATE PROCEDURE sp_var_repeat()
• BEGIN
• DECLARE a INT(10) DEFAULT 1;
• DECLARE str VARCHAR(255) DEFAULT 1;
• cnt:REPEAT
• SET a = a+1;
• SET str = CONCAT(str,',',a);
• UNTIL a>=10
• END REPEAT cnt;
• SELECT str;
• END $$
• CALL sp_var_repeat();
• #while循环
• DELIMITER $$
• CREATE PROCEDURE sp_var_while()
• BEGIN
• DECLARE a INT(10) DEFAULT 1;
• DECLARE str VARCHAR(255) DEFAULT 1;
• WHILE a<10 DO
• SET a = a+1;
• SET str = CONCAT(str,',',a);
• END WHILE;
• SELECT str;
• END $$
• CALL sp_var_while();
• #游标的用法
• DELIMITER $$
• CREATE PROCEDURE sp_var_youbiao()
• BEGIN
• DECLARE sname VARCHAR(10);
• DECLARE sage INT(3);
• DECLARE endFlag BOOLEAN DEFAULT TRUE;
• DECLARE student_cursor CURSOR FOR
  • SELECT s.sname,s.sage FROM student s;
• DECLARE CONTINUE HANDLER FOR NOT FOUND SET endFlag = FALSE;
• OPEN student_cursor;
• cnt:LOOP
• FETCH student_cursor INTO sname,sage;
• IF endFlag
• THEN UPDATE student s SET s.sage = sage+1 WHERE s.sname = sname;
• ELSE
• LEAVE cnt;
• END IF;
• END LOOP cnt;
• CLOSE student_cursor;
• END $$
• CALL sp_var_youbiao();
• DROP PROCEDURE IF EXISTS sp_var_02;
• #存储函数
• #mysql默认不允许创建自定义函数
• #开启可以自定义函数
• SET GLOBAL log_bin_trust_function_creators=TRUE;
• DELIMITER //
• CREATE FUNCTION test()
• RETURNS VARCHAR(10)
• BEGIN
• RETURN(SELECT sname FROM student WHERE sno = 1001);
• END //SELECT test()
• 参考网址：mysql数据库之存储过程_mysql 存储过程开发_勤奋上进的兔子的博客-优快云博客
• 参考:mysql存储过程（通俗易懂）_mysql 存储过程 declare_巧克力不假的博客-优快云博客