MySql存储过程

66 阅读 0 评论 44 点赞

我是靠谱客的博主老迟到网络，最近开发中收集的这篇文章主要介绍MySql存储过程，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、Mysql存储过程概述

存储过程是数据库的一个重要对象（对象还包括，索引，触发器，视图等），可以封装sql语句集，用来完成比较复杂的业务逻辑，并且还可以入参，出参。

存储过程创建时会进行预编译进行保存，当下次调用时不需要再进行编译。

优点：

    在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。
   执行速度快，存储过程经过编译之后会比单独一条一条执行要快。
   减少网络传输流量。
   方便优化。
缺点：
   过程化编程，复杂业务处理的维护成本高。
   调试不便
   不同数据库之间可移植性差。-- 不同数据库语法不一致！

二、语法结构

一、delimiter

声明结束符；

delimiter //

//：代表的是结束标志，这么做的目的主要是使结束标志与；进行分开，原因是存储过程中可能会有其他的一些操作语句需要用到；，但是存储过程又还没有执行完，所以就需要特别定义一个结束标志。否则存储过程在执行过程中就会认为；是结束标志，然后结束存储过程，可能会导致出错或后面操作无法执行完。

二、语法体结构

create procedure 名称([proc_parameter[....,.....,.....]])

[characteristic ...] routine_body

end （delimiter 声明的结束标识符）

-- proc_parameter参数部分，可以如下书写：
[ IN | OUT | INOUT ] param_name type
-- type类型可以是MySQL支持的所有类型

-- routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END

栗子:

-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突

delimiter $$
create procedure select_demo()
begin
    select 'hello';
end $$


-- 调用存储过程 单独调用使用；，如果是接着上面的话，就用$$
call select_demo();

一、变量以及赋值

一、局部变量：用户自定义，在begin/end中有效

声明变量语法：declare var_name type [default var_value];

栗子:declare name varchar(32);

1.使用set进行赋值

delimiter $$

create procedure demo()

begin

declare name varchar(32) default 'tom';

set name = 'bob';

select name;

end $$

2.使用into赋值

delimiter $$

create procedure demo()

begin

declare tp_name varchar(32) default 'tom';

declare tp_age int default 12;

select e.age,e.name into tp_age,tp_name from emp e where e.no = 9527;

select tp_name,tp_age;

end $$

二、用户变量

用户自定义变量，当前会话【连接】有效。

栗子：

delimiter $$

create procedure demo()

begin

declare name varchar(32) default 'tom';

set @name = 'bob';// 直接使用就行

select name;

end $$

三、入参、出参

语法：in | out | inout param_name type

栗子：

-- in 栗子

delimiter $$

create procedure demo(in name varchar(32))

begin

set @name = name;

end $$

-- out栗子

delimiter $$

create procedure demo(in name varchar(32),out age int(11))

begin

select d.age into age from dept d where d.name = name;

// 注意此处，要么表起别名，要么就是入参和字段名不能一致

end $$

--测试

set @dept_no = 100;
call sp_param01('DALLAS',@dept_no);
select @dept_no;

-- INOUT类型

delimiter $$

create procedure demo(inout name varchar(32))

begin

set name = concat('hello',name);

end $$

--测试

set @name = 'world';

call demo(@name);

select @name;

二、流程控制

1、if

语法：

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

栗子：

-- 需求：入职年限<=3是新手 >3并且<=6老员工 >6元老
-- timestampdiff函数：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unit

delimiter $$
create procedure demo()
begin
declare result varchar(32);
declare years int;
select timestampdiff(year,e.datetime,now()) into years from emp e where e.no = 9527;

if years >6 then set result = '元老'
else if 
years >3 and years <= 3 then set result ='老员工'
else
set result ='新员工'
end if;

select result;  // 查询一下结果
end $$

2、case

此语法是不仅可以用在存储过程，查询语句也可以用！

-- 语法一（类比java的switch）：
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
-- 语法二：
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

栗子：

-- 需求：入职年限<=3是新手 >3并且<=6老员工 >6元老
-- timestampdiff函数：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unit

delimiter $$
create procedure demo()
begin
declare result varchar(32);
declare years int;

select timestampdiff(year,e.datetime,now()) into years from emp e where e.no = 9527;

case 
when years >6 then set result = '元老'
when  years >3 and years <= 3 then set result ='老员工'
else
set result ='新员工'
end case;

select result;  // 查询一下结果
end $$

3、循环控制

loop：

-- LOOP语法
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

栗子：

需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出。

可以把leave看成我们java中的break；与之对应的，就有iterate（继续循环）——类比java的continue

-- 需求：循环打印1到10

--leave控制循环退出
delimiter $$
create procedure sp_flow_loop()
begin
 declare c_index int default 1;
 declare result_str  varchar(256) default '1';
 cnt:loop
 
  if c_index >= 10
  then leave cnt;
  end if;

  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
  
 end loop cnt;
 
 select result_str;
end$$

--leave + iterate控制循环退出
delimiter $$
create procedure demo()
begin
declare index int(11) default 1;
declare result varchar(32) default '1';

nums:loop
    set index = index + 1;
    set result = concat(result,',',index);
    
    if index < 10
        then
     iterate nums;
    end if;
     leave nums;
 end loop nums;
select result;
end $$

repeat ：类似于do------while();

语法结构:

[begin_label:] REPEAT
statement_list
UNTIL search_condition -- 直到…为止，才退出循环
END REPEAT [end_label]

栗子：

delimiter $$
create procedure demo()
begin 
    declare index int(11) default 1;
    declare result varchar(32) default '1';

nums repeat:
    index = index + 1;
    result = concat(result,',',index);
    until index > 10;
end repeat nums;
select result;
end $$

while：相当于java的while(){}语句

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

栗子：

delimiter $$
create procedure demo()
begin 
    declare index int(11) default 1;
    declare result varchar default '1';

while index <10 do
    set index = index + 1;
    set result = concat(result,',',index);
end while;
select result;
end $$

三、游标

用游标的到一个结果集，然后进行逐行处理

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name

栗子：

delimiter $$ 
create procedure demo(in dept_name vachar(32))
begin
    declare e_no int;
    declare e_name varchar(32);
    declare e_sal decimal(7,2);
    
    declare flag boolean default true;

-- 声明游标
    declare emp_curesor cursor for   
    select e.empno,e.ename,e.sal
    from emp e,dept d
    where e.deptno = d.deptno and d.dname = dept_name;  

--声明句柄，主要是用于进行数据逐行遍历的时候，遍历到最后，没有数据的时候返回报错的一个判断
--这里使用continue(表示：如果数据遍历完了，就退出循环，执行循环外的代码)，如果使用exits(则不会执行循环外的代码),
    declare continue handler for NOT FOUND set flae = false;
    
-- 打开语法
    open  emp_curesor;
-- 取值
    emp_loop:loop
    if flag then 
        select e.empno,e.ename,e.sal
    else
        leave emp_loop;
    end if;
        end loop emp_loop;
-- 设置标志，看是否执行到这里
    set @end_flag = 'end_flag';
end $$    


call demo('RESEARCH');

特别注意：

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

四、handler句柄

语法：

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

mysql数据库报错信息：
CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.

SQLWARNING: Shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'.
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.

-- 各种写法：
DECLARE exit HANDLER FOR SQLSTATE '42S01' set @res_table = 'EXISTS';
DECLARE continue HANDLER FOR 1050 set @res_table = 'EXISTS';
DECLARE continue HANDLER FOR not found set @res_table = 'EXISTS';

最后

以上就是老迟到网络为你收集整理的MySql存储过程的全部内容，希望文章能够帮你解决MySql存储过程所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：MySQL
浏览次数：66 次浏览
发布日期：2023-09-13 09:40:44
本文链接：https://www.kaopuke.com/article/k-p-k_14_uzo_6_f2_13__23_g3.html

MySql存储过程

概述

一、Mysql存储过程概述

二、语法结构

一、delimiter

二、语法体结构

一、变量以及赋值

二、流程控制

三、游标

四、handler句柄

最后

评论列表共有 0 条评论

发表评论取消回复

MySql存储过程

概述

一、Mysql存储过程概述

二、语法结构

一、delimiter

二、 语法体结构

一、变量以及赋值

二、流程控制

三、游标

四、handler句柄

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

二、语法体结构

发表评论取消回复