한 기사로 MySQL의 저장 프로시저를 마스터하세요

이 글에서는 mysql 관련 지식을 소개합니다. 주로 저장 프로시저 매개변수 전송, 프로세스 제어, 커서 및 예외 처리 등 저장 프로시저 관련 내용을 소개합니다. 함께 살펴보도록 하겠습니다. 모든 사람.

권장 학습: mysql 비디오 튜토리얼

MySQL 버전 5.0은 저장 프로시저를 지원하기 시작했습니다. 저장 프로시저는 강력하며 SQL 문 집합과 유사한 좀 더 복잡한 논리 기능을 구현할 수 있습니다. JAVA 언어로 저장하는 방법은 데이터베이스 SQL과 레이어 레벨 코드 캡슐화 및 재사용

1입니다. 입력 및 출력 매개변수가 있고 변수를 선언할 수 있으며 if/else/case/while 등의 제어문이 있습니다. 논리 함수

2. 함수의 공통 기능: 모듈화, 캡슐화, 코드 재사용

3. 빠르며 첫 번째 실행에만 컴파일 및 최적화 단계가 필요하며 후속 호출은 직접 실행할 수 있습니다. 위 단계

형식

저장 프로시저 만들기

-- 创建存储过程
delimiter $$
create procedure proc01()
begin
	select empno,ename from emp;
end $$
delimiter ;
-- 调用存储过程
call proc01;

변수 정의

로컬 변수

사용자 정의, 시작/끝 블록에서 유효

delimiter $$
create procedure proc02()
begin
	declare var_name01 varchar(20) default 'aaa';  -- 声明/定义变量
	set var_naem01 = 'zhangsan'; -- 给变量赋值
	select var_name01; -- 输出变量的值
end $$
dilimiter ;
call proc02();

사용자 변수

사용자 정의, 현재 세션(연결)이 유효합니다. Java의 멤버 변수

delimiter $$
create procedure proc04()
begin
	set @var_name01 = 'beijing';
	select @var_name01;
end $$
dilimiter ;
call proc04();
select @var_name01; -- 外部也是可以使用用户变量

시스템 변수

1와 유사합니다. 시스템 변수는 전역 변수와 세션 변수

2로 나뉩니다. . MYSQL이 시작될 때 전역 변수가 시작됩니다. 서버가 자동으로 기본값으로 초기화되면 my.ini 파일을 변경하여 이러한 기본값을 변경할 수 있습니다

3 세션 변수는 매번 MYSQL에 의해 초기화됩니다. 새로운 연결이 이루어지고 MYSQL은 현재 모든 전역 변수의 값을 세션 변수로 복사합니다

4. 즉, 세션이 형성된 후 세션 변수와 전역 변수의 값을 수동으로 변경하지 않으면, 그러면 이 모든 변수의 값은 동일하게 됩니다

5. 전역 변수와 세션 변수의 차이점은 전역 변수를 수정하면 전체 서버에 영향을 주지만 세션 변수를 수정하면 현재 세션에만 영향을 미친다는 것입니다. 는 현재 데이터베이스 연결)

6. 일부 시스템 변수의 값은 명령문을 사용하여 동적으로 변경할 수 있지만 일부 시스템 변수의 값은 변경 가능한 시스템 변수에 대해 읽기 전용입니다. set 문을 사용하여 변경할 수 있습니다.

시스템 변수 - 전역 변수

시스템 시스템에 의해 전체 데이터베이스에서 유효합니다

Stored Procedure

Stored Procedure 매개 변수-in

in 들어오는 매개변수를 나타내며 값이나 변수를 전달할 수 있습니다. 변수를 전달하더라도 변수의 값은 변경되지 않으며 내부적으로 변경될 수 있으며 함수 범위 내에서만 작동합니다

delimiter $$
create procedure proc06(in param_empno int)
begin
	select*from emp where empno = param_empno
end $$
delimiter ;
call proc06(1001);

여러 매개변수 전달

저장 프로시저에 매개변수 전달 -out

out은 저장 프로시저 내에서 매개변수 전달을 의미합니다. 값은 호출자에게 제공됩니다.

저장 프로시저 매개변수-inout

inout은 외부에서 전달된 매개 변수를 수정한 후 반환할 수 있는 변수입니다. 들어오는 변수의 값을 사용하거나 변수의 값을 수정할 수 있습니다(함수가 실행된 경우에도)

流程控制

流程控制-判断

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视频教程

위 내용은 한 기사로 MySQL의 저장 프로시저를 마스터하세요의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!