存盤程序（特定功能的SQL陳述句集）
一組為了完成特定功能的SQL陳述句集，存盤在資料庫中，經過第一次編譯后再次呼叫不需要編譯，用戶通過指定存盤程序的名字并給出引數（如果該存盤程序帶有引數）來執行它，存盤程序是資料庫中一個重要物件，
1.創建存盤程序：
create procedure 存盤程序名稱 ([存盤程序引數串列]) [存盤程序特性] SQL代碼內容
1 例：delimiter //
2 create procedure proc()
3 begin
4 select * from userinfo;
5 end //
6 delimiter;
2.創建存盤函式：
create function 存盤函式名稱 ([存盤程序引數串列]) returns type [存盤函式特性] SQL代碼內容
1 例：delimiter //
2 create function NameByZip()
3 returns char(50)
4 return (select username from userinfo where userage='23');
5 //
6 delimiter;
3.變數的使用
1）定義變數：declare 區域變數名稱,...資料型別 [default value];
例：declare myparam int default 100;
2）為變數賦值：set 區域變數名稱=expr,var_name=expr,...;
例：declare var1,var2 int;
set var1=10,var2=10;
4.定義條件和處理程式
1）定義條件：declare 條件名稱 condition for sqlstate 'xxxx';
declare 條件名稱 condition for xxxx;
2）定義處理程式：捕獲 sqlstate_value: delcare continue handler for sqlstate 'xxxx' set @info='no_such_table';
捕獲 mysql_error_code: declare continue handler for xxxx set @info='no_such_table';
5.游標的使用
1）宣告游標：declare 游標名稱 cursor for SQL陳述句;
2）打開游標：open 游標名稱;
3）使用游標：fetch 游標名稱 into 欄位名,...;
4）關閉游標：close 游標名稱;
6.流程控制陳述句
if陳述句：if val is null then select 'val is null';
else select 'val is not null';
end if;
case陳述句：case val
when 1 then select 'val is 1';
when 2 then select 'val is 2';
else select 'val is not 1 or 2';
end case;
loop陳述句：declare id int default 0;
add_loop:loop;
set id = id + 1;
if id>=10 then leave add_loop;
end if;
end loop add_loop;
leave陳述句：退出回圈陳述句
iterate陳述句：重新執行回圈
repeat陳述句：declare id int default 0;
repeat
set id = id + 1;
until id >= 10; ##先執行后判斷
end repeat;
while陳述句：declare id int default 0;
while id >=10 do ##先判斷后執行
set id = id+1;
end while;
7.呼叫存盤程序和呼叫存盤函式
call 存盤程序名稱（存盤程序定義的引數）
select 存盤函式名稱（存盤程序定義的引數）
8.查看存盤程序和函式
show {procedure | function} status like 'x%' 陳述句查看存盤程序和函式的狀態
show create {procedure | function} 存盤函式名稱 陳述句查看存盤程序和函式的定義
9.修改存盤程序和函式
alter {procedure | function}存盤函式名稱 （存盤程序特性）
10.洗掉存盤程序和函式
drop {procedure | function} [if exists] 存盤函式名稱
存盤程序優缺點：
優點：
1）存盤程序因為SQL陳述句已經預編譯過了，因此運行的速度比較快；
2）存盤程序在服務器端運行，減少了客戶端的壓力，
3）允許模塊化程式設計，就是說只需要創建一次程序，以后在程式中就可以呼叫該程序任意次，類似方法的復用；
4）減少網路流量，客戶端呼叫存盤程序只需要傳存盤程序名和相關引數即可，與傳輸SQL陳述句相比自然資料量少了很多；
5）增強了使用的安全性，充分利用系統管理員可以對執行的某一個存盤程序進行權限限制，從而能夠實作對某些資料訪問的限制，避免非授權用戶對資料的訪問，保證資料的安全，程式員直接呼叫存盤程序根本不知道表結構是什么，有什么欄位，沒有直接暴露表名以及欄位名給程式員，
缺點：
除錯麻煩（至少沒有像開發程式那樣容易），可移植性不靈活（因為存盤程序是依賴于具體的資料庫），
存盤程序優化思路：
1）盡量利用一些SQL陳述句來代替一些小回圈，例如聚合函式、求平均函式等，
2）中間結果存放于臨時表，加索引；
3）少使用游標，SQL是一個集合語言，對于集合運算具有較高的性能，而cursors是程序運算，比如對一個100萬行資料進行查詢，游標需要讀表100萬次，而不是使用游標則只需要少量幾次讀取，
4）事務越短越好，SQL server支持并發操作，如果事務過多過長或隔離級別過高，都會造成并發阻塞、死鎖，導致查詢極慢，CPU占用率極低，
5）使用try...catch處理例外；
6）查詢陳述句盡量不要放在回圈內；
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/99375.html
標籤：MySQL
上一篇：MySql事務的簡單使用