Master MySQL’s stored procedures in one article
- WBOYforward
- 2022-05-27 15:04:432245browse
This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about stored procedures, including stored procedure parameter passing, process control, cursor and exception handling, etc. Let’s take a look at the problem below. I hope it will be helpful to everyone.
Recommended learning: mysql video tutorial
MySQL version 5.0 begins to support stored procedures, which are a set of SQL statements. , powerful function, can realize some more complex logical functions, similar to the method in JAVA language, the storage is database SQL and layer-level code encapsulation and reuse
1. There are input and output parameters, and variables can be declared , there are control statements such as if/else/case/while, and complex logical functions can be realized by writing stored procedures
2. Common features of functions: modularization, encapsulation, code reuse
3. Fast speed. Only the first execution needs to go through compilation and optimization steps. Subsequent calls can be executed directly, eliminating the above steps.
Format
Create a stored procedure
-- 创建存储过程 delimiter $$ create procedure proc01() begin select empno,ename from emp; end $$ delimiter ; -- 调用存储过程 call proc01;
Define variables
Local variables
User-defined, valid in begin/end blocks
delimiter $$ create procedure proc02() begin declare var_name01 varchar(20) default 'aaa'; -- 声明/定义变量 set var_naem01 = 'zhangsan'; -- 给变量赋值 select var_name01; -- 输出变量的值 end $$ dilimiter ; call proc02();
User variables
User-defined, the current session (connection) is valid. Analogous to java member variables
delimiter $$ create procedure proc04() begin set @var_name01 = 'beijing'; select @var_name01; end $$ dilimiter ; call proc04(); select @var_name01; -- 外部也是可以使用用户变量
System variables
1. System variables are divided into global variables and session variables
2. Global variables are automatically initialized to default values by the server when MYSQL is started. These default values can be changed by changing the my.ini file
3. Session variables Every time a new connection is established, it is initialized by MYSQL. MYSQL will copy the values of all current global variables as session variables
4. In other words, if after establishing a session , if the values of session variables and global variables have not been manually changed, then the values of all these variables will be the same
5. The difference between global variables and session variables is that modifications to global variables will affect the entire server. However, modifications to session variables will only affect the current session (that is, the current database connection)
6. The values of some system variables can be changed dynamically using statements, but some system variables The value is read-only. For those system variables that can be changed, we can use the set statement to change them
System variables-global variables
is determined by the system system, in Valid in the entire database
Stored procedure
Stored procedure parameter-in
in means Incoming parameters can be passed in numerical values or variables. Even if a variable is passed in, the value of the variable will not be changed. It can be changed internally and only acts within the scope of the function
delimiter $$ create procedure proc06(in param_empno int) begin select*from emp where empno = param_empno end $$ delimiter ; call proc06(1001);
Passing multiple parameters
Stored procedure passing parameters-out
out means passing values from within the stored procedure to the caller
Stored procedure parameter passing -inout
inout represents a variable that can be returned after modifying the parameters passed in from the outside. You can either use the value of the incoming variable or modify the variable. Value (even if the function is executed)
流程控制
流程控制-判断
IF语句包含多个条件判断,根据结果为TURE和FALSE执行语句,与编程语言中的if、else if、else语法类似,其语法格式如下
-- 存储过程-if -- 案例1 -- 输入学生的成绩,来判断成绩的级别 /* score<60:不及格 score>=60 , score<80 :及格 score>=80 , score<90 :良好 score>=90 , score<=100 :优秀 score>100 :成就错误 */ delimiter $$ create procedure proc_12_if (in score int) begin if score<60 then select '不及格'; elseif score>=60 and score<80 then select '及格'; elseif score>=80 and score<90 then select '良好'; elseif score>=90 and score<=100 then select '优秀'; else select '成绩错误' end if; end $$ delimiter ; set @score=55; call proc_12_if(@score)
-- 输入员工的名字,判断工资的情况
/*
sal<10000 :适用薪资
sal>=10000 and sal<20000 :转正薪资
sal>=20000 :元老薪资
*/
delimiter $$
create procedure proc_13_if(in in_ename varchar)
begin
declare var_sal decimal(7,2); -- 定义局部变量,7个长度,2个小数
declare result varchar(20);
select sal into var_sal from emp where ename=in_eanme; -- 将值赋给局部变量
if var_sal <10000 then set result ='适用薪资';
elseif var_sal <20000 then set result ='转正薪资';
else set result ='元老薪资';
end if;
select result;
end$$
delimiter ;
-- 调用
call proc_13_if('刘备');
流程控制语句-case
CASE是另一个条件判断语句,类似于编程语言中的switch语法
语法一:当case后面的语句与when后面的语句相等时,则会执行then后面的语句,如果均不匹配则执行else
语法二:直接写case 当when后面的条件成立则执行then后面的语句,如果都不成立,则执行else
-- 流程控制语句:case /* 支付方式: 1.微信支付 2.支付宝支付 3.银行卡支付 4.其他支付 */ -- 格式1 delimiter $$ create procedure proc14_case(in pay_type int) begin case pay_type when 1 then select '微信支付'; when 2 then select '支付宝支付'; when 3 then select '银行卡支付'; else select '其他方式支付'; end case; end$$ delimiter ; call proc14_case(2); call proc14_case(3);
-- 语法二 -- 输入学生的成绩,来判断成绩的级别 /* score<60:不及格 score>=60 , score<80 :及格 score>=80 , score<90 :良好 score>=90 , score<=100 :优秀 score>100 :成就错误 */ delimiter $$ create procedure proc_15_case (in score int) begin case when score<60 then select '不及格'; when score>=60 and score<80 then select '及格'; when score>=80 and score<90 then select '良好'; when score>=90 and score<=100 then select '优秀'; else select '成绩错误' end case; end $$ delimiter ; call proc_15_case(88);
控制流程-循环
概述
循环是一段在程序中只出现一次,但可能会连续运行多次的代码
循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环
循环分类
while、repeat、loop
循环控制
leave 类似于break,跳出,结束当前所在的循环
iteater 类似于continue,继续,结束本次循环,继续下一次
while循环
-- 需求:向表中添加指定条数据 -- while delimiter $$ create procedure proc16_while(in insertCount int) begin declare i int default 1; -- 设置循环的增量 label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456'); set i=i+1; end while label; end$$ delimiter ; call proc16_while(10);
-- 需求:向表中添加指定条数据 -- while+leave delimiter $$ create procedure proc16_while(in insertCount int) begin declare i int default 1; -- 设置循环的增量 label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456'); if i=5 then leave label; -- 如果i=5则跳出循环 set i=i+1; end while label; end$$ delimiter ; call proc16_while(10);
repeat循环
格式
-- 存储过程-循环控制-repeat delimiter $$ create procedure proc18_repaet(in insertCount int) begin declare i int default 1; label:repeat insert into user(uid,username,password)values(i,concat('user-',i),'123456'); set i = i+1; until i>inserCount -- 这里不能写; 表示跳出循环 end repeat label; select '循环结束'; end$$ delimiter ; call proc18_repeat(100);
loop循环
-- 存储过程-循环控制-loop
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
declare i default 1;
label:loop insert into user (uid,username,password) values (i,concat('user-')i,'123456');
set i= i+1;
if i>insertCount then leave label;
end if;
end loop label;
end$$
delimiter ;
call proc19_loop()
游标
游标(cursor)是庸才存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理,光标的使用包括光标的声明、OPEN、FETCH、CLOSE
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
-- 需求:输入一个部门名,查询该部门员工的编号、名字、薪资,将查询的结果集添加游标
delimiter $$
create procedure proc19_cursor(in in_danme varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 声明游标
declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;
-- 打开游标
open my_cursor;
-- 通过游标获取值
-- fetch my_cursor into var_empno,var_ename,var_sal;
-- select var_empno,var_ename,var_sal; -- 注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
end loop label;
-- 关闭游标
close my_cursor;
end$$
delimiter ;
call proc19_cursor('销售部');
异常处理-HANDLER句柄
MySQL存储过程也提供了对异常处理的功能:通过定义HANDLER来完成异常声明的实现
格式
在语法中,变量声明、游标声明、handler声明必须按照先后顺序书写的,否则创建存储过程出错
DECALARE handler_action 创建句柄
FOR 后面为条件值,为什么异常提供处理
statment 当没有数据发现这个异常发生时采取什么措施
handler_action :{
CONTINUE 继续执行
EXIT 退出
UNDO 取消(目前MySQL不支持)
}
condition_value :{
mysql_error_code 错误码
condition 条件名
SQLWARNING SQL警告
NOT FOUND 数据没有发现
SQLEXCEPTION SQL异常
}
/*用于测试句柄,改造上面游标代码会出现1329错误代码的情况*/
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
-- 需求:输入一个部门名,查询该部门员工的编号、名字、薪资,将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(in in_danme varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 定义标记值
declare flag int default 1;
-- 声明游标
declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;
-- 定义句柄:定义异常的处理方式
/*
1:异常处理完之后,程序应该如何执行
continue :继续执行剩余代码
exit:直接终止程序
undo:不支持
2:触发条件
条件码:1329.....
条件名:
SQLWARNING
NOT FOUND
SQLEXCEPTION
3:异常触发之后执行什么代码
设置flag的值 -> 0
*/
declare continue handler for 1329 set flag=0 ;
-- 打开游标
open my_cursor;
-- 通过游标获取值
-- fetch my_cursor into var_empno,var_ename,var_sal;
-- select var_empno,var_ename,var_sal; -- 注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
-- 判断flag的值如果为1则执行,否则不执行
if flag =1 then
select var_empno,var_ename,var_sal;
else leave label;
end if;
end loop label;
-- 关闭游标
close my_cursor;
end$$
delimiter ;
call proc19_cursor('销售部');
推荐学习：mysql视频教程
The above is the detailed content of Master MySQL’s stored procedures in one article. For more information, please follow other related articles on the PHP Chinese website!