MySQL入門很簡單－學習筆記 – 第14章 預存程序和函數

文章目錄

• 14.1.1、建立預存程序
• 14.1.2、建立儲存函數
• 14.1.3、變數的使用
• 14.1.4、定義條件和處理常式
• 14.1.5、游標的使用
• 14.1.6、流程式控制制的使用
• 14.2.1、調用預存程序
• 14.2.2、調用儲存函數

避免編寫重複的語句

安全性可控

執行效率高

 

14.1、建立預存程序和函數14.1.1、建立預存程序

CREATE PROCEDUREsp_name ([proc_parameter[,...]])

[characteristic...] routine_body

 

procedure 發音 [prə'si:dʒə]

 

proc_parameter           IN|OUT|INOUT param_name type

characteristic               n. 特徵；特性；特色

         LANGUAGESQL                     預設，routine_boyd由SQL組成

         [NOT]DETERMINISTIC          指明預存程序的執行結果是否是確定的，預設不確定

         CONSTAINSSQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA指定程式使用SQL語句的限制

CONSTAINS SQL           子程式包含SQL，但不包含讀寫資料的語句，預設

NO SQL                        子程式中不包含SQL語句

READS SQL DATA                  子程式中包含讀資料的語句

MODIFIES SQL DATA    子程式中包含了寫資料的語句

         SQLSECURITY {DEFINER|INVOKER}，指明誰有許可權執行。

                  DEFINER，只有定義者自己才能夠執行，預設

                  INVOKER    表示調用者可以執行

         COMMENT‘string’  注釋資訊

 

CREATEPROCEDURE num_from_employee (IN emp_id, INT, OUT count_num INT)

         READS SQL DATA

         BEGIN

                  SELECTCOUNT(*)  INTOcount_num

                  FROMemployee

                  WHEREd_id=emp_id;

         END

14.1.2、建立儲存函數

CREATE FUNCTIONsp_name ([func_parameter[,...]])

RETURNS type

[characteristic...] routine_body

CREATEFUNCTION name_from_employee(emp_id INT)

         RETURNSVARCHAR(20)

         BEGIN

                  RETURN (SELECT name FROM employee WHEREnum=emp_id);

         END

14.1.3、變數的使用1．定義變數

DECLARE var_name[,…]type [DEFAULT value]

 

DECLAREmy_sql INT DEFAULT 10;

 

2．為變數賦值

SETvar_name=expr[,var_name=expr]…

 

SELECT col_name[,…]INTO var_name[,…] FROM table_name WHERE condition

 

14.1.4、定義條件和處理常式1．定義條件

DECLARE condition_nameCONDITION FOR condition_value

condition value:

         SQLSTATE[VALUE] sqlstate_value | mysql_error_code

 

對於ERROR 1146(42S02)

sqlstate_value: 42S02

mysql_error_code:1146

//方法一

DECLARE can_not_find CONDITION FOR SQLSTATE ‘42S02’

//方法二

DECLARE can_not_find CONDITION FOR 1146

 

2．定義處理常式

DECLAREhander_type HANDLER FOR condition_value[,…] sp_statement

 

handler_type:

         CONTINUE|EXIT|UNDO

condition_value:

SQLSTATE[VALUE] sqlstate_value | condition_name |SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code

 

UNDO目前MySQL不支援

 

1、捕獲sqlstate_value

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @info=’CANNOT FIND’;

2、捕獲mysql_error_code

DECLARE CONTINUE HANDLER FOR 1146  SET @info=’CAN NOT FIND’;

3、先定義條件，然後調用

DECLARE can_not_find CONDITION FOR 1146;

DECLARE CONTINUE HANDLER FOR can_not_find SET @info=’CANNOT FIND’;

4、使用SQLWARNING

DECLARE EXITHANDLER FOR SQLWARNING SET @info=’CANNOT FIND’;

5、使用NOT FOUND

DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’CANNOT FIND’;

6、使用SQLEXCEPTION

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’CANNOT FIND’;

 

14.1.5、游標的使用

預存程序中對多條記錄處理，使用游標

1．聲明游標

DECLAREcousor_name COURSOR FOR select statement;

 

DECLAREcur_employee CURSOR FOR SELECT name, age FROM employee;

 

2．開啟游標

OPENcursor_name;

 

OPENcur_employee;

 

3．使用游標

FETCHcur_employee INTO var_name[,var_name…];

 

FETCH cur_employeeINTO emp_name, emp_age;

 

4．關閉游標

CLOSEcursor_name

 

CLOSE cur_employee

14.1.6、流程式控制制的使用1．IF語句

IFsearch_condition THEN statement_list

         [ELSEIF search_condition THENstatement_list]…

         [ELSE statement_list]

END IF

 

IF age>20THEN SET @count1=@count1+1;

         ELSEIF age=20 THEN @count2=@count2+1;

         ELSE @count3=@count3+1;

END

 

2．CASE語句

CASE case_value

         WHEN when_value THEN statement_list

         [WHEN when_value THEN statement_list]…

         [ELSE statement_list]

END CASE

 

CASE

         WHEN search_condition THENstatement_list

         [WHEN search_condition THENstatement_list]…

         [ELSE statement_list]

END CASE

 

CASE age

         WHEN 20 THEN SET @count1=@count1+1;

         ELSE SET @count2=@count2+1;

END CASE;

 

CASE

         WHERE age=20 THEN SET@count1=@count1+1;

         ELSE SET @count2=@count2+1;

END CASE;

 

3．LOOP語句

[begin_label:]LOOP

         statement_list

ENDLOOP[end_label]

 

add_num:LOOP

         SET @count=@count+1;

END LOOPadd_num;

 

4．LEAVE語句

跳出迴圈控制

LEAVE label

 

add_num:LOOP

         SET @count=@count+1;

         LEAVE add_num;

END LOOPadd_num;

 

5．ITERATE語句

跳出本次迴圈，執行下一次迴圈

ITERATE label

 

add_num:LOOP

         SET @count=@count+1;

         IF @count=100 THEN LEAVE add_num;

         ELSEIF MOD(@count,3)=0 THEN ITERATEadd_num;

         SELECT * FROM employee;

END LOOPadd_num;

 

6．REPEAT語句

有條件迴圈，滿足條件退出迴圈

[begin_label:]REPEAT

         statement_list

         UNTIL search_condition

ENDREPEAT[end_label]

 

REPEAT

         SET @count=@count+1;

         UNTIL @count=100;

ENDREPEAT;

 

7．WHILE語句

[begin_label:]WHILEsearch_condition DO

         statement_list

ENDREPEAT[end_label]

 

WHILE@count<100 DO

         SET @count=@count+1;

ENDWHILE;

14.2、調用預存程序和函數

預存程序是通過CALL語句來調用的。而儲存函數的使用方法與MySQL內建函式的使用方法是一樣的。執行預存程序和儲存函數需要擁有EXECUTE許可權。EXECUTE許可權的資訊儲存在information_schema資料庫下面的USER_PRIVILEGES表中

14.2.1、調用預存程序

CALL  sp_name([parameter[,…]]) ;

 

14.2.2、調用儲存函數

儲存函數的使用方法與MySQL內建函式的使用方法是一樣的

 

14.3、查看預存程序和函數

SHOW { PROCEDURE| FUNCTION } STATUS [ LIKE  ' pattern ' ];

SHOW CREATE {PROCEDURE | FUNCTION } sp_name ;

SELECT * FROMinformation_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;

 

14.4、修改預存程序和函數

ALTER {PROCEDURE| FUNCTION} sp_name [characteristic ...]

characteristic:

{ CONTAINS SQL |NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY {DEFINER | INVOKER }

| COMMENT'string'

 

14.5、刪除預存程序和函數

DROP {PROCEDURE| FUNCTION } sp_name;