翼度科技»论坛 编程开发 mysql 查看内容

存储过程

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
存储过程

介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用,类似于函数或者说API,封装了系列操作,暴露接口给你进行操作。

  • 特点
    封装,复用
    可以接收参数,也可以返回数据
    减少网络交互,效率提升
基本语法


  • 创建
    1. CREATE PROCEDURE 存储过程名称([参数列表])
    2. BEGIN
    3.     -- SQL语句
    4. END;
    复制代码
  • 调用
    1. CALL 名称([参数]);
    复制代码
  • 查看
    1. -- 查询指定数据库的存储过程及状态信息
    2. SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名称';
    3. -- 查询某个存储过程的定义
    4. SHOW CREATE PROCEDURE 存储过程名称;
    复制代码
  • 删除
    1. DROP PROCEDURE [IF EXISTS] 存储过程名称;\
    复制代码
注意:在命令行中,执行创建存储过程的SQL语句时,需要通过关键字 delimiter 指定SQL语句的结束符。
代码演示:
  1. -- 存储过程基本语法
  2. -- 创建
  3. CREATE PROCEDURE p1()
  4. BEGIN
  5.         SELECT COUNT(*) FROM emp;
  6. END;
  7. -- 如果在命令行执行的话,上面语句会出错,因为遇到第一个分号就结束了,
  8. -- 所以需要用到 delimiter
  9. -- delimiter $$ --表示指定 $$ 为结束符(记得之后再使用delimiter改回来)
  10. -- CREATE PROCEDURE p1()
  11. -- BEGIN
  12. --         SELECT COUNT(*) FROM emp;
  13. -- END;$$
  14. -- 这样就可以解决
  15. -- 调用
  16. CALL p1();
  17. -- 查看
  18. SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'hsp_db02';
  19. SHOW CREATE PROCEDURE p1;
  20. -- 删除
  21. DROP PROCEDURE IF EXISTS p1;
复制代码
变量


  • 系统变量
    系统变量是MySQL服务器提供,不是用户定义的,属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。

    • 查看系统变量
      1. -- 查看所有系统变量
      2. SHOW [SESSION|GLOBAL] VARIABLES;
      3. -- 可以通过LIKE模糊匹配方式查找变量、
      4. SHOW [SESSION|GLOBAL] VARIABLES LIKE '......';
      5. -- 查看指定变量的值
      6. SELECT @@[SESSION|GLOBAL] 系统变量名;
      复制代码
    • 设置系统变量
      1. SET [SESSION|GLOBAL] 系统变量名 = 值;
      2. SET @@[SESSION|GLOBAL]系统变量名 = 值;
      复制代码
    注意:

    • 如果没有指定是session或global,则默认是session,会话变量。
    • mysql服务重新启动后,所设置的全局参数会失效,要想不失效,可以在/etc/my.cnf中配置
    代码演示:
    1. -- 变量:系统变量
    2. -- 查看系统变量
    3. show variables; -- 所有系统变量
    4. show session variables; -- 会话变量
    5. show global variables; -- 全局变量
    6. show session variables like 'auto%';
    7. show global variables like 'auto%';
    8. select @@global.autocommit;
    9. select @@session.autocommit;
    10. -- 设置系统变量
    11. set session autocommit = 0; -- 设置为0,代表关闭了当前会话的事务的自动提交
    12. set global autocommit = 0; -- 当服务器重启后,这个参数又会初始化为默认值,想要不失效,需要在etc/my.cnf中配置
    复制代码
  • 用户定义变量
    用户定义变量 是用户根据需要自己定义的变量,用户不用提前声明,在用的时候直接用 "@变量名"使用就可以。其作用域为当前连接(会话)。

    • 赋值
      1. SET @var_name = expr[,@var_name = expr]...;
      2. SET @var_name := expr[,@var_name := expr]...;
      复制代码
      1. SELECT @var_name := expr[,@var_name := expr]...;
      2. SELECT 字段名 INTO @var_name FROM 表名;
      复制代码
    • 使用
      1. SELECT @var_name;
      复制代码
    注意:用户定义的变量是无需对其进行声明或初始化的,只不过获取到的值为NULL。
    代码演示:
    1. -- 变量:用户变量
    2. -- 赋值
    3. SET @myname = 'itcast';
    4. SET @myage := 10; -- 推荐
    5. set @mygender := '男', @myhobby := 'java';
    6. SELECT @mycolor := 'red';
    7. SELECT COUNT(*) into @mycount FROM demo;
    8. -- 使用
    9. SELECT @myname, @myage, @mygender, @myhobby;
    10. SELECT @mycolor, @mycount;
    11. SELECT @abc; -- NULL
    复制代码
  • 局部变量
    局部变量是根据需要定义在局部生效的变量,访问之前,需要DECLARE声明。可以用作存储过程内的局部变量和输入参数,局部变量的范围是在其内声明的BEGIN ... END块中。

    • 声明
      1. DECLARE 变量名 变量类型[DEFAULT ...];
      复制代码
      变量类型就是数据库字段类型:INT、BiGINT、CHAR、VARCHAR、DATE、TIME等。
    • 赋值
      1. SET 变量名 = 值;
      2. SET 变量名 := 值;
      3. SELECT 字段名 INTO 变量名 FROM 表名...;
      复制代码
    代码演示:
    1. -- 变量:局部变量
    2. -- 声明 declare
    3. -- 赋值
    4. CREATE PROCEDURE p2()
    5. BEGIN
    6.     DECLARE stu_count INT DEFAULT 0;
    7.     set stu_count := 100;
    8.     SELECT COUNT(*) into stu_count FROM demo;
    9.     SELECT stu_count;
    10. END;
    11. CALL p2();
    复制代码
if判断

语法
  1. IF 条件1 THEN
  2.         ......
  3. ELSEIF 条件2 THEN         -- 可选
  4.         ......
  5. ELSE                     -- 可选
  6.         ......
  7. END IF;
复制代码
代码演示:
  1. CREATE PROCEDURE p1()
  2. BEGIN
  3.         DECLARE score INT DEFAULT 58;
  4.     DECLARE `result` VARCHAR(10);
  5.    
  6.     IF score >= 85 THEN
  7.             SET `result` := '优秀';
  8.     ELSEIF score >= 60 THEN
  9.             SET `result` := '及格';
  10.     ELSE
  11.             SET `result` := '不及格';
  12.     END IF;
  13.    
  14.     SELECT `result`;
  15. END;
  16. CALL p1();
复制代码
参数

类型含义备注IN因为该类参数作为输入,也就是需要调用时传入值默认OUT因为该类参数作为输出,也就是该类参数可以作为作为返回值INOUT既可以作为输入参数,也可以作为输出参数用法:
  1. CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
  2. BEGIN
  3.   -- SQL语句
  4. END;
复制代码
代码演示:
  1. CREATE PROCEDURE p2(IN score INT, OUT `result` VARCHAR(10))
  2. BEGIN
  3.     IF score >= 85 THEN
  4.             SET `result` := '优秀';
  5.     ELSEIF score >= 60 THEN
  6.             SET `result` := '及格';
  7.     ELSE
  8.             SET `result` := '不及格';
  9.     END IF;
  10. END;
  11. CALL p2(68, @result);
  12. SELECT @result;
  13. CREATE PROCEDURE p3(INOUT score INT)
  14. BEGIN
  15.         set score := score / 2;
  16. END;
  17. SET @score := 120;
  18. CALL p3(@score);
  19. SELECT @score;
复制代码
case


  • 语法一
    1. CASE case_value
    2.    WHEN when_value1 THEN statement_list1
    3.    [WHEN when_value2 THEN statement_list2]...
    4.    [ELSE statement_list]
    5. END CASE;
    复制代码
  • 语法二
    1. CASE
    2.         WHEN search_condition1 THEN statement_list1
    3.         [WHEN search_condition2 THEN statement_list2]
    4.         [ELSE statement_list]
    5. END CASE;
    复制代码
代码演示:
  1. CREATE PROCEDURE p4(in month int)
  2. BEGIN
  3.         declare result varchar(10);
  4.     case
  5.             when month >= 1 and month <= 3 THEN
  6.                 set result := '第一季度';
  7.         when month >= 4 and month <= 6 THEN
  8.                 set result := '第二季度';
  9.         when month >= 7 and month <= 9 THEN
  10.                 set result := '第三季度';
  11.         when month >= 10 and month <= 12 THEN
  12.                 set result := '第四季度';
  13.         else
  14.                 SET result := '非法参数';
  15.         END case;
  16.         SELECT concat('您输入的月份为:', month, ' 所属的季度为:', result);
  17. END;
  18. CALL p4(4);
复制代码
循环-repeat
repeat是有条件的循环控制语句,直到满足条件的时候退出循环。具体语法如下:
  1. #先判定条件,如果条件为true,则执行逻辑,否则不执行逻辑
  2. WHILE 条件 DO
  3.     -- SQL逻辑
  4. END WHILE;
复制代码
代码演示:
[code]#计算1到n的累加值CREATE procedure p6(in n int)BEGIN    DECLARE total int DEFAULT 0;    repeat         set total := total + n;         set n := n - 1;     UNTIL n

举报 回复 使用道具