MySQL存儲過程入門了解
0.環境說明：
mysql版本：5.7
1.使用說明
存儲過程是資料庫的一個重要的對象，可以封裝SQL語句集，可以用來完成一些較複雜的業務邏輯，並且可以入參出參(類似於java中的方法的書寫)。
創建時會預先編譯後保存，用戶後續的調用都不需要再次編譯。
// 把editUser類比成一個存儲過程
public void editUser(User user,String username){
String a = "nihao";
user.setUsername(username);
}
main(){
User user = new User();
editUser(user,"張三");
user.getUseranme(); //java基礎還記得不
}
大家可能會思考，用sql處理業務邏輯還要重新學，我用java來處理邏輯（比如循環判斷、循環查詢等）不行嗎？那麼，為什麼還要用存儲過程處理業務邏輯呢？
優點：
在生產環境下，可以通過直接修改存儲過程的方式修改業務邏輯（或bug），而不用重啟伺服器。
執行速度快，存儲過程經過編譯之後會比單獨一條一條執行要快。
減少網路傳輸流量。
方便優化。
缺點：
過程化編程，複雜業務處理的維護成本高。
調試不便
不同資料庫之間可移植性差。-- 不同資料庫語法不一致！
2.準備：
資料庫參閱資料中的sql腳本；
delimiter $$ --聲明結束符
3.語法
-- 官方參考網址
//dev.mysql.com/doc/refman/5.6/en/sql-statements.html
//dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html
3.0 語法結構
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
-- proc_parameter參數部分，可以如下書寫：
[ IN | OUT | INOUT ] param_name type
-- type類型可以是MySQL支援的所有類型
-- routine_body(程式體)部分，可以書寫合法的SQL語句 BEGIN ... END
簡單演示：
-- 聲明結束符。因為MySQL默認使用『;』作為結束符，而在存儲過程中，會使用『;』作為一段語句的結束，導致『;』使用衝突
delimiter $$
CREATE PROCEDURE hello_procedure ()
BEGIN
SELECT 'hello procedure';
END $$
call hello_procedure();
3.1 變數及賦值
類比一下java中的局部變數和成員變數的聲明和使用
局部變數：
用戶自定義，在begin/end塊中有效
語法：
聲明變數 declare var_name type [default var_value];
舉例：declare nickname varchar(32);
-- set賦值
create procedure sp_var01()
begin
declare nickname varchar(32) default 'unkown';
set nickname = 'ZS';
-- set nickname := 'SF';
select nickname;
end$$
-- into賦值
delimiter $$
create procedure sp_var_into()
begin
declare emp_name varchar(32) default 'unkown' ;
declare emp_no int default 0;
select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839;
select emp_no,emp_name;
end$$
用戶變數：
用戶自定義，當前會話（連接）有效。類比java的成員變數
語法：
@var_name
不需要提前聲明，使用即聲明
-- 賦值
delimiter $$
create procedure sp_var02()
begin
set @nickname = 'ZS';
-- set nickname := 'SF';
end$$
call sp_var02() $$
select @nickname$$ --可以看到結果
會話變數：
由系統提供，當前會話（連接）有效
語法：
@@session.var_name
show session variables; -- 查看會話變數
select @@session.unique_checks; -- 查看某會話變數
set @@session.unique_checks = 0; --修改會話變數
全局變數：
由系統提供，整個mysql伺服器有效
語法：
@@global.var_name
舉例
-- 查看全局變數中變數名有char的記錄
show global variables like '%char%';
-- 查看全局變數character_set_client的值
select @@global.character_set_client;
3.2 入參出參
-- 語法
in | out | inout param_name type
舉例
-- IN類型演示
delimiter $$
create procedure sp_param01(in age int)
begin
set @user_age = age;
end$$
call sp_param01(10) $$
select @user_age$$
-- OUT類型，只負責輸出！
-- 需求：輸出傳入的地址字元串對應的部門編號。
delimiter $$
create procedure sp_param02(in loc varchar(64),out dept_no int(11))
begin
select d.deptno into dept_no from dept d where d.loc = loc;
--此處強調，要麼表起別名，要麼入參名不與欄位名一致
end$$
delimiter ;
--測試
set @dept_no = 100;
call sp_param01('DALLAS',@dept_no);
select @dept_no;
-- INOUT類型
delimiter $$
create procedure sp_param03(inout name varchar)
begin
set name = concat('hello' ,name);
end$$
delimiter ;
set @user_name = '小明';
call sp_param03(@user_name);
select @user_name;
3.3 流程式控制制-判斷
官網說明
//dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html
if
-- 語法
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
舉例：
-- 前置知識點：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，單位是unit
select timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499';
-- 需求：入職年限<=38是新手 >38並且<=40老員工 >40元老
delimiter $$
create procedure sp_hire_if()
begin
declare result varchar(32);
if timestampdiff(year,'2001-01-01',now()) > 40
then set result = '元老';
elseif timestampdiff(year,'2001-01-01',now()) > 38
then set result = '老員工';
else
set result = '新手';
end if;
select result;
end$$
delimiter ;
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
舉例：
-- 需求：入職年限年齡<=38是新手 >38並 <=40老員工 >40元老
delimiter $$
create procedure sp_hire_case()
begin
declare result varchar(32);
declare message varchar(64);
case
when timestampdiff(year,'2001-01-01',now()) > 40
then
set result = '元老';
set message = '老爺爺';
when timestampdiff(year,'2001-01-01',now()) > 38
then
set result = '老員工';
set message = '油膩中年人';
else
set result = '新手';
set message = '萌新';
end case;
select result;
end$$
delimiter ;
3.4 流程式控制制-循環
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$$
-- iterate + leave控制循環
delimiter $$
create procedure sp_flow_loop02()
begin
declare c_index int default 1;
declare result_str varchar(256) default '1';
cnt:loop
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
if c_index < 10 then
iterate cnt;
end if;
-- 下面這句話能否執行到？什麼時候執行到？ 當c_index < 10為false時執行
leave cnt;
end loop cnt;
select result_str;
end$$
repeat
[begin_label:] REPEAT
statement_list
UNTIL search_condition -- 直到…為止，才退出循環
END REPEAT [end_label]
-- 需求：循環列印1到10
delimiter $$
create procedure sp_flow_repeat()
begin
declare c_index int default 1;
-- 收集結果字元串
declare result_str varchar(256) default '1';
count_lab:repeat
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
until c_index >= 10
end repeat count_lab;
select result_str;
end$$
while
類比java的while(){}
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
-- 需求：循環列印1到10
delimiter $$
create procedure sp_flow_while()
begin
declare c_index int default 1;
-- 收集結果字元串
declare result_str varchar(256) default '1';
while c_index < 10 do
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
end while;
select result_str;
end$$
3.5 流程式控制制-退出、繼續循環
leave
類比java的breake
-- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).
LEAVE label
iterate
類比java的continue
-- 繼續循環 ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label
3.6 游標
用游標得到某一個結果集，逐行處理數據。
類比jdbc的ResultSet
-- 聲明語法
DECLARE cursor_name CURSOR FOR select_statement
-- 打開語法
OPEN cursor_name
-- 取值語法
FETCH cursor_name INTO var_name [, var_name] ...
-- 關閉語法
CLOSE cursor_name
-- 需求：按照部門名稱查詢員工，通過select查看員工的編號、姓名、薪資。（注意，此處僅僅演示游標用法）
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare lp_flag boolean default true;
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
-- handler 句柄
declare continue handler for NOT FOUND set lp_flag = false;
open emp_cursor;
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal;
if lp_flag then
select e_no,e_name,e_sal;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_falg = 'exit_flag';
close emp_cursor;
end$$
call sp_create_table02('RESEARCH');
DROP PROCEDURE if EXISTS sp_update_create_time;
delimiter $$
create procedure sp_update_create_time()
BEGIN
declare value_id bigint(20);
declare lp_flag boolean default true;
declare update_create_time_cursor cursor for
SELECT
commodity_discnt_item_value_id
FROM
td_co_discnt_item_value
WHERE
create_time IS NULL;
declare continue handler for NOT FOUND set lp_flag = false;
open update_create_time_cursor;
update_create_time_loop:loop
fetch update_create_time_cursor into value_id;
if lp_flag then
UPDATE td_co_discnt_item_value SET create_time =now() WHERE commodity_discnt_item_value_id =value_id;
else leave update_create_time_loop;
end if;
end loop update_create_time_loop;
set @end_falg = 'exit_flag';
close update_create_time_cursor;
end$$
call sp_update_create_time();
說明：以上存儲過程目的為更新表中create_time為空的數據設置為當前時間
特別注意：
在語法中，變數聲明、游標聲明、handler聲明是必須按照先後順序書寫的，否則創建存儲過程出錯。
3.7 存儲過程中的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
}
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';
4.練習
——大家注意，存儲過程的業務過程在java程式碼中一般也可以實現，我們下面的需求是為了練習存儲過程
4.1 利用存儲過程更新數據
為某部門(需指定)的人員漲薪100;如果是公司總裁，則不漲薪。
delimiter //
create procedure high_sal(in dept_name varchar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare lp_flag boolean default true;
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
-- handler 句柄
declare continue handler for NOT FOUND set lp_flag = false;
open emp_cursor;
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal;
if lp_flag then
if e_name = 'king' then
iterate emp_loop;
else
update emp e set e.sal = e.sal + 100 where e.empno = e_no;
end if;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_falg = 'exit_flag';
close emp_cursor;
end //
call high_sal('ACCOUNTING');
結語
歡迎關注微信公眾號『碼仔zonE』，專註於分享Java、雲計算相關內容，包括SpringBoot、SpringCloud、微服務、Docker、Kubernetes、Python等領域相關技術乾貨，期待與您相遇！
