Master MySQL's stored procedures in one article

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=80 , score=90 , score100 :成就错误
*/
delimiter $$
create procedure proc_12_if (in score int)
begin
	if score=60 and score=80 and score=90 and score<pre class="brush:php;toolbar:false">-- 输入员工的名字,判断工资的情况
/*
sal=10000 and 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 <h2 id="流程控制语句-case">流程控制语句-case </h2><p> CASE是另一个条件判断语句,类似于编程语言中的switch语法</p><p>语法一:当case后面的语句与when后面的语句相等时,则会执行then后面的语句,如果均不匹配则执行else</p><p>语法二:直接写case 当when后面的条件成立则执行then后面的语句,如果都不成立,则执行else</p><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/f3e5c1eb9e43a98c353ae8c97ddc55ee-12.png?x-oss-process=image/resize,p_40" class="lazy" alt=""    style="max-width:90%"  style="max-width:90%"></p><pre class="brush:php;toolbar:false">-- 流程控制语句: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=80 , score=90 , score100 :成就错误
*/
delimiter $$
create procedure proc_15_case (in score int)
begin
	case
	when score=60 and score=80 and score=90 and score<h2 id="控制流程-循环">控制流程-循环</h2><p><strong>概述</strong></p><p>循环是一段在程序中只出现一次,但可能会连续运行多次的代码</p><p>循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环</p><p><strong>循环分类</strong></p><p>while、repeat、loop</p><p><strong>循环控制</strong></p><p>leave 类似于break,跳出,结束当前所在的循环</p><p>iteater 类似于continue,继续,结束本次循环,继续下一次</p><h3 id="while%E5%BE%AA%E7%8E%AF">
<strong>while</strong>循环</h3><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/f3e5c1eb9e43a98c353ae8c97ddc55ee-13.png?x-oss-process=image/resize,p_40" class="lazy" alt=""    style="max-width:90%"  style="max-width:90%"></p><pre class="brush:php;toolbar:false">-- 需求:向表中添加指定条数据
-- while
delimiter $$
create procedure proc16_while(in insertCount int)
begin
	declare i int default 1;  -- 设置循环的增量
	label:while i<pre class="brush:php;toolbar:false">-- 需求:向表中添加指定条数据
-- while+leave
delimiter $$
create procedure proc16_while(in insertCount int)
begin
	declare i int default 1;  -- 设置循环的增量
	label:while i<h3 id="repeat循环">repeat循环</h3><p><strong>格式</strong></p><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/f3e5c1eb9e43a98c353ae8c97ddc55ee-14.png?x-oss-process=image/resize,p_40" class="lazy" alt=""    style="max-width:90%"  style="max-width:90%"></p><pre class="brush:php;toolbar:false">-- 存储过程-循环控制-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!