一文掌握MySQL的預存過程
- WBOY轉載
- 2022-05-27 15:04:432257瀏覽
本篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於預存程序的相關內容，包括了預存程序傳參、流程控制、以及遊標和異常處理等問題，下面一起來看一下，希望對大家有幫助。
推薦學習：mysql影片教學
#MySQL5.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;
定義變數
局部變數
使用者自訂,在begin/end區塊中有效
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語句進行更改
系統變數-全域變數
由系統系統,在整個資料庫中有效
預存程序
預存程序傳參-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>=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视频教程
以上是一文掌握MySQL的預存過程的詳細內容。更多資訊請關注PHP中文網其他相關文章！