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

mysql存储过程

4

主题

4

帖子

12

积分

新手上路

Rank: 1

积分
12
1.介绍
  存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
  存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。


特点:

  • 封装,复用 -----------------------------> 可以把某一业务 SQL 封装在存储过程中,需要用到的时候直接调用即可。
  • 可以接收参数,也可以返回数据 --------> 再存储过程中,可以传递参数,也可以接收返回值。
  • 减少网络交互,效率提升 ---------------> 如果涉及到多条SQL,每执行一次都是一次网络传输。 而如果封装在存储过程中,我们只需要网络交互一次可能就可以了。
2. 基本语法

  • 创建
  1. CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
  2. BEGIN
  3.   -- SQL语句
  4. END ;
复制代码

  • 调用
  1. CALL 名称 ([ 参数 ]);
复制代码

  • 查看
  1. SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
  2. -- 查询指定数据库的存储过程及状态信息
  3. SHOW CREATE PROCEDURE 存储过程名称 ;
  4. -- 查询某个存储过程的定义
复制代码

  • 删除
  1. DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ;
复制代码
注意:

  在命令行中,执行创建存储过程的 SQL 时,需要通过关键字 delimiter 指定 SQL  语句的结束符。

演示示例
  1. -- 存储过程基本语法
  2. -- 创建
  3. create procedure p1()
  4. begin
  5. select count(*) from student;
  6. end;
  7. -- 调用
  8. call p1();
  9. -- 查看
  10. select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
  11. show create procedure p1;
  12. -- 删除
  13. drop procedure if exists p1;
复制代码
3. 变量
  在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。
3.1 系统变量
  系统变量是 MySQL 服务器提供,不是用户定义的,属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。

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

  • 设置系统变量
  1. SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
  2. SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
复制代码
注意:

  如果没有指定SESSION/GLOBAL,默认是SESSION,会话变量。

  A. 全局变量(GLOBAL): 全局变量针对于所有的会话。

  B. 会话变量(SESSION): 会话变量针对于单个会话,在另外一个会话窗口就不生效了。

演示示例
  1. -- 查看系统变量
  2. show session variables ;
  3. show session variables like 'auto%';
  4. show global variables like 'auto%';
  5. select @@global.autocommit;
  6. select @@session.autocommit;
  7. -- 设置系统变量
  8. set session autocommit = 1;
  9. insert into course(id, name) VALUES (6, 'ES');
  10. set global autocommit = 0;
  11. select @@global.autocommit;
复制代码
3.2 用户定义变量
  用户定义变量 是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接用 "@变量名" 使用就可以。其作用域为当前连接。

  • 赋值
方式一:
  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. set @myname = 'itcast';
  3. set @myage := 10;
  4. set @mygender := '男',@myhobby := 'java';
  5. select @mycolor := 'red';
  6. select count(*) into @mycount from tb_user;
  7. -- 使用
  8. select @myname,@myage,@mygender,@myhobby;
  9. select @mycolor , @mycount;
  10. select @abc;
复制代码
3.3 局部变量
  局部变量是根据需要定义的在局部生效的变量,访问之前,需要 DECLARE 声明。可用作存储过程内的局部变量和输入参数,局部变量的范围是在其内声明的 BEGIN ... END 块。

  • 声明
  1. DECLARE 变量名 变量类型 [DEFAULT ... ] ;
复制代码
  变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

  • 赋值
  1. SET 变量名 = 值 ;
  2. SET 变量名 := 值 ;
  3. SELECT 字段名 INTO 变量名 FROM 表名 ... ;
复制代码
演示示例
  1. -- 声明局部变量 - declare
  2. -- 赋值
  3. create procedure p2()
  4. begin
  5.   declare stu_count int default 0;
  6.   select count(*) into stu_count from student;
  7.   select stu_count;
  8. end;
  9. call p2();
复制代码
4. if

  • 语法
if 用于做条件判断,具体的语法结构为:
  1. IF 条件1 THEN
  2.     .....
  3. ELSEIF 条件2 THEN       -- 可选
  4.     .....
  5. ELSE                    -- 可选
  6.     .....
  7. END IF;
复制代码
  在 if 条件判断的结构中,ELSE IF 结构可以有多个,也可以没有。 ELSE 结构可以有,也可以没有。

  • 案例
  根据定义的分数score变量,判定当前分数对应的分数等级。
  A. score >= 85分,等级为优秀。
  B. score >= 60分 且 score < 85分,等级为及格。
  C. score < 60分,等级为不及格。
  1. create procedure p3()
  2. begin
  3.     declare score int default 58;
  4.     declare result varchar(10);
  5.     if score >= 85 then
  6.         set result := '优秀';
  7.     elseif score >= 60 then
  8.         set result := '及格';
  9.     else
  10.         set result := '不及格';
  11.     end if;
  12.     select result;
  13. end;
  14. call p3();
复制代码
  上述的需求我们虽然已经实现了,但是也存在一些问题,比如:score 分数我们是在存储过程中定义死的,而且最终计算出来的分数等级,我们也仅仅是最终查询展示出来而已。
  那么我们能不能,把 score 分数动态的传递进来,计算出来的分数等级是否可以作为返回值返回呢?
  答案是肯定的,我们可以通过接下来所讲解的参数来解决上述的问题。
5. 参数
  参数的类型,主要分为以下三种:IN、OUT、INOUT。 具体的含义如下:

用法:
  1. CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
  2. BEGIN
  3. -- SQL语句
  4. END ;
复制代码
案例一

根据传入参数score,判定当前分数对应的分数等级,并返回。
  A. score >= 85分,等级为优秀。
  B. score >= 60分 且 score < 85分,等级为及格。
  C. score < 60分,等级为不及格。
  1. create procedure p4(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. -- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
  12. call p4(18, @result);
  13. select @result;
复制代码
案例二

将传入的200分制的分数,进行换算,换算成百分制,然后返回。
  1. create procedure p5(inout score double)
  2. begin
  3.   set score := score * 0.5;
  4. end;
  5. set @score = 198;
  6. call p5(@score);
  7. select @score;
复制代码
6. case
  case 结构及作用,和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式:

  • 语法一:
  1. -- 含义: 当case_value的值为 when_value1时,执行statement_list1,当值为 when_value2时,
  2. 执行statement_list2, 否则就执行 statement_list
  3. CASE case_value
  4.   WHEN when_value1 THEN statement_list1
  5.   [ WHEN when_value2 THEN statement_list2] ...
  6.   [ ELSE statement_list ]
  7. END CASE;
复制代码

  • 语法二:
  1. -- 含义: 当条件search_condition1成立时,执行statement_list1,当条件search_condition2成
  2. 立时,执行statement_list2, 否则就执行 statement_list
  3. CASE
  4.   WHEN search_condition1 THEN statement_list1
  5.   [WHEN search_condition2 THEN statement_list2] ...
  6.   [ELSE statement_list]
  7. END CASE;
复制代码
案例

根据传入的月份,判定月份所属的季节(要求采用case结构)。
  A. 1-3月份,为第一季度
  B. 4-6月份,为第二季度
  C. 7-9月份,为第三季度
  D. 10-12月份,为第四季度
  1. create procedure p6(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 p6(16);
复制代码
8. repeat
  repeat 是有条件的循环控制语句, 当满足 until 声明的条件的时候,则退出循环 。具体语法为:
  1. -- 先判定条件,如果条件为true,则执行逻辑,否则,不执行逻辑
  2. WHILE 条件 DO
  3.   SQL逻辑...
  4. END WHILE;
复制代码
案例

计算从1累加到n的值,n为传入的参数值。(使用repeat实现)
[code]-- A. 定义局部变量, 记录累加之后的值;-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环create procedure p8(in n int)begin  declare total int default 0;  repeat    set total := total + n;    set n := n - 1;    until n  leave xxcreate procedure p9(in n int)begin  declare total int default 0;  sum:loop    if n leave xx-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xxcreate procedure p10(in n int)begin  declare total int default 0;  sum:loop    if n

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具