MySQL資料庫——儲存和函數

標籤：基本   cti   roc   rac   比較   sql語句   資料   關鍵字   continue   

一、預存程序
1 基本文法
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
　　[characteristic ...] routine_body
格式[IN|OUT|INOUT]param_name type

2. IN、OUT、INOUT參數
（1）帶IN的預存程序

 1 #建立儲存過程.cmd 中運行  2 CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20))  3 BEGIN 4 IF p_name is null or p_name=‘‘ THEN 5 SELECT * FROM student;  6 ELSE 7 SELECT * FROM student WHERE sname LIKE p_name;  8 END IF;  9 END10 #調用並輸出結果 11 call sp_search(‘張平‘);12 DROP PROCEDURE SP_SEARCH;

（2）帶OUT的預存程序

 1 #帶OUT返回的  2 CREATE PROCEDURE SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT)  3 BEGIN 4 IF p_name is null or p_name=‘‘ THEN 5 SELECT * FROM t_user;  6 ELSE 7 SELECT * FROM t_user WHERE USER_NAME LIKE p_name;  8 END IF;  9 SELECT FOUND_ROWS() INTO p_int; 10 END11 #調用並輸出結果 12 CALL SP_SEARCH2(‘王%‘,@p_num); 13 SELECT @p_num;

（3）帶INOUT的預存程序

1 #帶INOUT的預存程序 2 CREATE PROCEDURE sp_inout(INOUT p_num INT) 3 BEGIN4 SET p_num=p_num*10; 5 END6 #調用並輸出結果 7 SET @p_num=2; 8 call sp_inout(@p_num); 9 SELECT @p_num;

3.預存程序體
(1) 局部變數
在預存程序體中可以聲明局部變數，用來儲存預存程序體中臨時結果。
DECLARE var_name[,…] type [DEFAULT value]
Var_name:指定局部變數的名稱
Type:用於聲明局部變數的資料類型
default子句:用於為局部變數指定一個預設值。若沒有指定，預設為null.
如：Declare cid int(10);
使用說明：
局部變數只能在預存程序體的begin…end語句塊中聲明。
局部變數必須在預存程序體的開頭處聲明。
局部變數的作用範圍僅限於聲明它的begin..end語句塊，其他語句塊中的語句不可以使用它。
局部變數不同於使用者變數，兩者區別：局部變數聲明時，在其前面沒有使用@符號，並且它只能在begin..end語句塊中使用；而使用者變數在聲明時，會在其名稱前面使用@符號，同時已聲明的使用者變數存在於整個會話之中。
(2) set語句
使用set語句為局部變數賦值
Set var_name=expr
Set cid=910;

(3) select … into 語句
把選定列的值直接儲存到局部變數中，文法格式
Select col_name[,…] into var_name[,…] table_expr
Col_name:用於指定列名
Var_name:用於指定要賦值的變數名
Table_expr:表示select語句中的from字句及後面的文法部分
說明:預存程序體中的select…into語句返回的結果集只能有一行資料。
(4) 定義處理常式
是事先定義程式執行過程中可能遇到的問題。並且可以在處理常式中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，並提出解決方案。
DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement
handler_type:CONTINUE | EXIT | UNDO
Condition_value:Sqlwarning | not found | sqlexception

4.流程式控制制語句
（1）條件判斷語句
If語句
If search_condition then statement_list
[elseif search_condition then statement_list]…
[else statement_list]
End if
Search_condition參數:條件判斷語句
Statement_list參數:不同條件的執行語句
多重IF的預存程序執行個體

 1 #帶多重IF的預存程序  2 CREATE PROCEDURE SP_SGRADE_LEVEL(IN p_level char(1))  3 BEGIN 4 IF p_level =‘A‘ THEN 5 SELECT * FROM sc WHERE grade >=90;  6 ELSEIF p_level =‘B‘ THEN 7 SELECT * FROM sc WHERE grade <90 AND grade>=80;  8 ELSEIF p_level =‘C‘ THEN 9 SELECT * FROM sc WHERE grade <80 AND grade>=70; 10 ELSEIF p_level =‘D‘ THEN11 SELECT * FROM sc WHERE grade <60; 12 ELSE13 SELECT * FROM sc; 14 END IF; 15 END

Case 語句
表達形式1

Case case_value
When when_value then statement_list
[When when_value then statement_list]…
[else statement_list]
End case

表達形式2
Case
When search_condition then statement_list
End case

 1 CREATE PROCEDURE SP_SGRADE_LEVEL1(IN p_level char(1))  2 BEGIN 3 DECLARE p_num int DEFAULT 0;  4 CASE p_level  5 WHEN ‘A‘ THEN 6 SET p_num=90;  7 WHEN ‘B‘ THEN 8 SET p_num=80;  9 WHEN ‘C‘ THEN10 SET p_num=70; 11 WHEN ‘D‘ THEN12 SET p_num=60; 13 ELSE14 SET p_num=0; 15 END CASE; 16 SELECT * FROM sc , student s WHERE sc.sno=s.sno 17 AND sc.grade >= p_num AND sc.grade< p_num+10; 18 END

Repeat語句文法格式

[begin_label:]
repeat
Statement_list
Until search_condition
End repeat
[end_label]
Repeat語句首先執行statement_list中的語句，然後判斷條件search_condition是否為真，倘若為真，則結束迴圈，若不為真，繼續迴圈。
Repeat先執行後判斷，while先判斷後執行。
使用範例：

 1 #帶repeat的預存程序  2 CREATE PROCEDURE sp_cal2(IN p_num INT,OUT p_result INT)  3 BEGIN 4 SET p_result=1;  5 REPEAT  6 SET p_result = p_num * p_result;  7 SET p_num = p_num-1;  8 UNTIL p_num<=1  9 END REPEAT; 10 END

5.調用預存程序

Call sp_name([parameter[,…]]);
Sp_name被調用預存程序的名稱
Parameter:指定調用預存程序所要使用的參數。
6.修改預存程序
代碼如下:
Alter procedure proc_name[characteristic…]
只能修改預存程序的特徵，如果要修改預存程序的內容，可以先刪除該預存程序，然後再重新建立
7.刪除預存程序
代碼如下:
Drop procedure [if exists] sp_name; 

二、函數
1. 定義

MySQL中，建立儲存函數的基本形式如下：
CREATE FUNCTION sp_name([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
Return
調用儲存函數

Select sp_name([func_parameter…]) 

Select fn_search(2);

2.預存程序和函數區別
1）一般來說，預存程序實現的功能要複雜一點，而函數的實現的功能針對性比較強。預存程序，功能強大，可以執行包括修改表等一系列資料庫操作；使用者定義函數不能用於執行一組修改全域資料庫狀態的操作。
2）對於預存程序來說可以返回參數，如記錄集，而函數只能傳回值或者表對象。函數只能返回一個變數；而預存程序可以返回多個。預存程序的參數可以有IN,OUT,INOUT三種類型，而函數只能有IN類~~預存程序聲明時不需要傳回型別，而函式宣告時需要描述傳回型別，且函數體中必須包含一個有效RETURN語句。
3）預存程序，可以使用非確定函數，不允許在使用者定義函數主體中內建非確定函數。
4）預存程序一般是作為一個獨立的部分來執行（ EXECUTE 語句執行），而函數可以作為查詢語句的一個部分來調用（SELECT調用），由於函數可以返回一個表對象，因此它可以在查詢語句中位於FROM關鍵字的後面。 SQL語句中不可用預存程序，而可以使用函數。

三、遊標（游標）
1.定義
查詢語句可能查詢出多條記錄，在預存程序和函數中
使用遊標來逐條讀取查詢結果集中的記錄。
遊標的使用包括聲明遊標、開啟遊標、使用遊標和關閉遊標。
遊標必須聲明遊標、開啟遊標、使用遊標和關閉遊標。
遊標必須聲明在處理常式之前，並且聲明在變數和條件之後。
2.聲明遊標

Declare cursor_name cursor for select_statement;
Cursor_name:遊標名稱
Select_statement:select語句的內容
Declare cur_sc cursor for select sno,cno,grade from sc;
3.開啟遊標

Open cursor_name
Open cur_sc;
4.使用遊標
Mysql中使用fetch關鍵字來使用遊標，文法形式

Fetch cur_name into var_name[,var_name…];
Cur_name表示遊標的名稱
Var_name表示將遊標中的select語句查詢出來的資訊存入該參數。Var_name必須在聲明遊標前就定義好。
Fetch cur_sc into sc_sno,sc_cno,sc_grade;

5.關閉遊標

Close cursor_name;
Close cur_employee;
每個遊標不再需要時都應該被關閉，
使用close語句將會釋放遊標所使用的全部資源。
在一個遊標被關閉後，如果沒有重新被開啟，則不能被使用。
對於聲明過的遊標，則不需要再次聲明，可直接使用open語句開啟。

MySQL資料庫——儲存和函數