目錄
介紹
特點
存儲過程創建
系統變量(不重要)
用戶變量
局部變量
?if 判斷
參數（in, out, inout)
case
?while
repeat
?loop
游標和條件處理程序-handler
存儲函數
為了防止以后忘記，反復去看視頻浪費時間，特寫一篇
介紹
存儲過程是事先經過編譯并存儲在數據庫中的一段 SQL語句的集合，調用存儲過程可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，對于提高數據處理的效率是有好處的。
特點
封裝，復用
可以接收參數，也可以返回數據
減少網絡交互，效率提升
存儲過程創建
--存儲過程創建
CREATE PROCEDURE 存儲過程名(參數列表)
BEGIN--sql語句
END;--調用
CALL 名稱([參數])--查看存儲過程創建語句
show create procedure 存儲過程名--刪除存儲過程
DROP PROCEDURE [IF EXISTS]存儲過程名稱;例子
-- 存儲過程基本語法
-- 創建
create procedure p1()
beginselect count(*)from student;
end;-- 調用
call p1();-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';--方式1show create procedure p1;----方式2-- 刪除
drop procedure if exists p1;系統變量(不重要)
?（默認session）[不要求掌握，了解即可，我是感覺這個沒吊用]
--查看所有系統變量
SHOW [SESSION |GLOBAL] VARIABLES ; --可以通過LKE模糊匹配方式查找變量
SHOW [SESSION|GLOBAL] VARIABLES LIKE'。。'; -- 查看指定變量的值
SELECT @@[SESSION|GLOBAL].系統變量名; 設置系統變量
SET [SESSION|GLOBAL] 系統變量名 = 值
SET @@[SESSION|GLOBAL] 系統變量名 = 值例子
查看系統變量
show session variablesshow global variables結果：（有一堆）
show session variables like 'auto%'結果：?
?設置會話系統變量
select @@session.autocommit --自動提交默認開啟，結果為1
set session autocommit = 0 --設置關閉
select @@session.autocommit -- 結果為0注意，會話系統變量僅對當前查詢控制臺有效，當你打開另一個查詢控制臺，再次查詢，會發現autocommit的結果依然為1，要想對所有查詢控制臺有效，需要把session改為global，但是如果你設置global.autocommit = 0,將mysql重啟，autocommit結果依然為1，如果你想設置重啟服務器autocommit也為0，需要將autocommit = 0寫入mysql的配置文件才可以。
對了，如果你設置autocommit = 0，當你執行sql語句時必須還要執行commit,這樣sql才會生效。（但是，我實驗的，設置為0,也自動提交有效）
用戶變量
賦值：SET @var_name = expr [, @var_name = expr]...;
SET @var_name := expr [, @var_name := expr]...;-- 推薦SELECT @var_name := expr , @var_name := expr ...;
SELECT 字段名 INTO @var_name FROM 表名;使用：SELECT @var_name;例子
-- 變量：用戶變量
-- 賦值
set @myname = 'root';
set @myage := 10;select @mycolor := 'red';
select count(*) into @mycount from tb_user;-- 使用
select @myname, @myage, @mycolor, @mycount;select @abc; -- 輸出為NULL?
局部變量
局部變量 是根據需要定義的在局部生效的變量，訪問之前，需要DECLARE聲明。可用作存儲過程內的局部變量和輸入參數，局部變量的范圍是在其內聲明的BEGIN .. END塊。
聲明：
DECLARE 變量名 變量類型 [DEFAULT..];
變量類型就是數據庫字段類型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
賦值：
SET 變量名=值;
SET 變量名:=值;
SELECT 字段名 INTO 變量名 FROM 表名 ...;
例子
-- 變量：局部變量
-- 聲明 - declare
-- 賦值 -
create procedure p2()
begindeclare stu_count int default 0;select count(*) into stu_count from student;select stu_count;
end;call p2();?if 判斷
語法
IF 條件1 THEN...
ELSEIF 條件2 THEN -- 可選...
ELSE -- 可選...
END IF;例子
create procedure p3()
begindeclare score int default 58;declare result varchar(10);if score >= 85 thenset result :='優秀';elseif score >= 60 thenset result :='及格';elseset result :='不及格';end if;select result;
end;參數（in, out, inout)
| 類型 | 含義 | 備注 |
|---|---|---|
| IN | 該類參數作為輸入，也就是需要調用時傳入值 | 默認 |
| OUT | 該類參數作為輸出，也就是該參數可以作為返回值 | |
| INOUT | 既可以作為輸入參數，也可以作為輸出參數**** |
?用法
CREATE PROCEDURE 存儲過程名稱([IN/OUT/INOUT 參數名 參數類型 ])
BEGIN-- SQL語句
END :例子
-- 根據傳入(in)參數score，判定當前分數對應的分數等級，并返回(out)
-- score >= 85分，等級為優秀。
-- score >= 60分 且 score < 85分，等級為及格
-- score < 60分，等級為不及格。
create procedure p3(in score int, out result varchar(10))
beginif score >= 85 thenset result :='優秀';elseif score >= 60 thenset result :='及格';elseset result :='不及格';end if;select result;
end;-- 將傳入的200分制的分數，進行換算，換算成百分制，然后返回分數 --> inout
create procedure p5(inout score double)
beginset score := score * 0.5;
end;set @score = 198;
call p5(score);
select @score;case
例子?
-- case
-- 根據傳入的月份，判定月份所屬的季節(要求采用case結構)
-- 1-3月份，為第一季度
-- 4-6月份，為第二季度
-- 7-9月份，為第三季度
-- 10-12月份，為第四季度create procedure p6(in month int)
begin declare result varchar(10);case when month >= 1 and month <= 3 thenset result := '第一季度';when month >= 4 and month <= 6 thenset result := '第二季度';when month >= 7 and month <= 9 thenset result := ' 第三季度';when month >= 10 and month <= 12 thenset result := '第四季度';elseset result := '非法參數';end case;select concat('你輸入的月份為：', month, '，所屬季度為：', result);
end;?while
語法
#先判定條件，如果條件為true，則執行邏輯，否則，不執行邏輯
WHILE 條件 DOSOL邏輯...
END WHILE;?例子
-- while計算從1累加到 n 的值，n 為傳入的參數值。
-- A.定義局部變量，記錄累加之后的值;
-- B.每循環一次，就會對 n 進行減1，如果 n 減到0，則退出循環create procedure p7(in n int)
begindeclare total int default 0;while n>0 doset total := total + nset n:=n-1;end while;select total;
end;
call p7( n: 100);repeat
語法
#repeat是有條件的循環控制語句,當滿足條件的時候退出循環。相當于 c 語言中的 do while();
REPEATSOL邏輯.UNTIL 條件
END REPEAT;例子?
-- while計算從1累加到 n 的值，n 為傳入的參數值。
-- A.定義局部變量，記錄累加之后的值;
-- B.每循環一次，就會對 n 進行減1，如果 n 減到0，則退出循環create procedure p8(innint)
begindeclare total int default 0;repeatset total := total + n;set n := n - 1;until n <= 0end repeat;select total;
end;call p8( n: 10);
call p8( n: 100);?loop
?LOOP 實現簡單的循環，如果不在SQL邏輯中增加退出循環的條件，可以用其來實現簡單的死循環。
LOOP可以配合一下兩個語句使用。
LEAVE:配合循環使用，退出循環。
ITERATE:必須用在循環中，作用是跳過當前循環剩下的語句，直接進入下一次循環。
語法
[begin label:] LOOPSQL邏輯..
END LOOP [end label];LEAVE label; ?-- 退出指定標記的循環體
ITERATE label;-- 直接進入下一次循環?例子
-- loop 計算從1到n之間的偶數累加的值，n為傳入的參數值。
-- A.定義局部變量，記錄累加之后的值;
-- B.每循環一次，就會勸進行-1，如果n減到0，則退出循環。------> leave xx
-- C.如果當次累加的數據是奇數，則直接進入下一次循壞。-------> iterate xxcreate procedure p10(in n int)
begin declare total int defatult 0;sum: loopif n <= 0 thenleave sum; #如果加完就退出loopend if;if n %2 = 1 thenset n := n - 1;iterate sum; #如果為奇數跳過end if;set total := total + n;set n := n - 1;end loop sum;select total;
end;游標和條件處理程序-handler
游標語法（指向表的行數據,常用于行數據賦值）
--聲明游標
DECLARE 游標名稱 CURSOR FOR 查詢語句;！！！游標聲明要在局部變量聲明后--打開游標：
OPEN 游標名稱;--獲取游標記錄：
FETCH 游標名稱 INTO 變量[,變量];--關閉游標：
CLOSE 游標名稱;?條件處理程序語法
DECLARE handler_action HANDLERFOR condition_value [,condition_value]... statement;handler_action，可以取以下值：CONTINUE: 繼續執行當前程序EXIT: 終止執行當前程序
condition_value,可以取以下值SOLSTATE sqlstate_value:狀態碼，如 02000SQLWARNING:所有以01開頭的SQLSTATE代碼的簡寫NOT FOUND:所有以02開頭的SOLSTATE代碼的簡寫SOLEXCEPTION:所有沒有被SOLWARNING 或 NOT FOUND捕獲的SOLSTATE代碼的簡寫例子
create procedure p11(in uage int)
begin declare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, profession from tb_user where age <= uage;-- 監控到02000(表示找不到行)的狀態碼后，關閉游標后執行exit退出操作。declare exit handler for sqlstate '02000' close u_cursor; --declare exit handler for not found close u_cursor; drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));open u_cursor;while true dofetch u_cursor into uname,upro;insert into tb_user_pro values(null, uname, upro);end while;close u_cursor;
end;存儲函數
存儲函數是有返回值的存儲過程，存儲函數的參數只能是IN類型的。
存儲函數用的較少，能夠使用存儲函數的地方都可以用存儲過程替換。
語法
CREATE FUNCTION 存儲函數名稱([ 參數列表 ])
RETURNS type [characteristic ...]
BEGIN-- SQL語句RETURN ...;
END ;
characteristic說明:
· DETERMINISTIC:相同的輸入參數總是產生相同的結果
· NO SQL:不包含 SQL語句。
· READS SOL DATA:包含讀取數據的語句，但不包含寫入數據的語句,?例子
create function fun1(n int)
returns int deterministic
begindeclare total int default 0;while n > 0 do set total := total + n;set n := n - 1;end while;return total;
end;
