程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql 存儲進程詳解

mysql 存儲進程詳解

編輯:MySQL綜合教程

mysql 存儲進程詳解。本站提示廣大學習愛好者:(mysql 存儲進程詳解)文章只能為提供參考,不一定能成為您想要的結果。以下是mysql 存儲進程詳解正文


MySQL存儲進程

 14.1.1 創立存儲進程

MySQL中,創立存儲進程的根本情勢以下:

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) 
        [characteristic ...] routine_body

個中,sp_name參數是存儲進程的稱號;proc_parameter表現存儲進程的參數列表; characteristic參數指定存儲進程的特征;routine_body參數是SQL代碼的內容,可以用BEGIN…END來標記SQL代碼的開端和停止。

proc_parameter中的每一個參數由3部門構成。這3部門分離是輸出輸入類型、參數稱號和參數類型。其情勢以下:

[ IN | OUT | INOUT ] param_name type

個中,IN表現輸出參數;OUT表現輸入參數; INOUT表現既可所以輸出,也能夠是輸入; param_name參數是存儲進程的參數稱號;type參數指定存儲進程的參數類型,該類型可所以MySQL數據庫的隨意率性數據類型。

characteristic參數有多個取值。其取值解釋以下:

LANGUAGE SQL:解釋routine_body部門是由SQL說話的語句構成,這也是數據庫體系默許的說話。

[NOT] DETERMINISTIC:指明存儲進程的履行成果能否是肯定的。DETERMINISTIC表現成果是肯定的。每次履行存儲進程時,雷同的輸出會獲得雷同的輸入。NOT DETERMINISTIC表現成果長短肯定的,雷同的輸出能夠獲得分歧的輸入。默許情形下,成果長短肯定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子法式應用SQL語句的限制。CONTAINS SQL表現子法式包括SQL語句,但不包括讀或寫數據的語句;NO SQL表現子法式中不包括SQL語句;READS SQL DATA表現子法式中包括讀數據的語句;MODIFIES SQL DATA表現子法式中包括寫數據的語句。默許情形下,體系會指定為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER }:指明誰有權限來履行。DEFINER表現只要界說者本身能力夠履行;INVOKER表現挪用者可以履行。默許情形下,體系指定的權限是DEFINER。

COMMENT 'string':正文信息。

技能:創立存儲進程時,體系默許指定CONTAINS SQL,表現存儲進程中應用了SQL語句。然則,假如存儲進程中沒有應用SQL語句,最好設置為NO SQL。並且,存儲進程中最好在COMMENT部門對存儲進程停止簡略的正文,以便今後在浏覽存儲進程的代碼時加倍便利。

【示例14-1】 上面創立一個名為num_from_employee的存儲進程。代碼以下:

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
     READS SQL DATA 
     BEGIN 
       SELECT COUNT(*) INTO count_num 
       FROM employee 
       WHERE d_id=emp_id ; 
     END 

上述代碼中,存儲進程稱號為num_from_employee;輸出變量為emp_id;輸入變量為count_num。SELECT語句從employee表查詢d_id值等於emp_id的記載,並用COUNT(*)盤算d_id值雷同的記載的條數,最初將盤算成果存入count_num中。代碼的履行成果以下:

mysql> DELIMITER && 
mysql> CREATE PROCEDURE num_from_employee
(IN emp_id INT, OUT count_num INT ) 
  -> READS SQL DATA 
  -> BEGIN 
  -> SELECT COUNT(*) INTO count_num 
  -> FROM employee 
  -> WHERE d_id=emp_id ; 
  -> END && 
Query OK, 0 rows affected (0.09 sec) 
mysql> DELIMITER ; 

代碼履行終了後,沒有報出任何失足信息就表現存儲函數曾經創立勝利。今後便可以挪用這個存儲進程,數據庫中會履行存儲進程中的SQL語句。

解釋:MySQL中默許的語句停止符為分號(;)。存儲進程中的SQL語句須要分號來    停止。為了不抵觸,起首用"DELIMITER &&"將MySQL的停止符設置為&&。最初再用"DELIMITER ;"來將停止符恢復成份號。這與創立觸發器時是一樣的。

14.1.2 創立存儲函數

在MySQL中,創立存儲函數的根本情勢以下:

CREATE FUNCTION sp_name ([func_parameter[,...]]) 
    RETURNS type 
    [characteristic ...] routine_body 

個中,sp_name參數是存儲函數的稱號;func_parameter表現存儲函數的參數列表;RETURNS type指定前往值的類型;characteristic參數指定存儲函數的特征,該參數的取值與存儲進程中的取值是一樣的,請讀者參照14.1.1末節的內容;routine_body參數是SQL代碼的內容,可以用BEGIN…END來標記SQL代碼的開端和停止。

func_parameter可以由多個參數構成,個中每一個參數由參數稱號和參數類型構成,其情勢以下:

param_name type
個中,param_name參數是存儲函數的參數稱號;type參數指定存儲函數的參數類型,該類型可所以MySQL數據庫的隨意率性數據類型。

【示例14-2】 上面創立一個名為name_from_employee的存儲函數。代碼以下:

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN 
       RETURN (SELECT name 
       FROM employee 
       WHERE num=emp_id ); 
     END 

上述代碼中,存儲函數的稱號為name_from_employee;該函數的參數為emp_id;前往值是VARCHAR類型。SELECT語句從employee表查詢num值等於emp_id的記載,並將該記載的name字段的值前往。代碼的履行成果以下:

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN 
  -> RETURN (SELECT name 
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ; 

成果顯示,存儲函數曾經創立勝利。該函數的應用和MySQL外部函數的應用辦法一樣。

14.1.3  變量的應用

在存儲進程和函數中,可以界說和應用變量。用戶可使用DECLARE症結字來界說變量。然後可認為變量賦值。這些變量的感化規模是BEGIN…END法式段中。本末節將講授若何界說變量和為變量賦值。

1.界說變量

MySQL中可使用DECLARE症結字來界說變量。界說變量的根本語法以下:

DECLARE  var_name[,...]  type  [DEFAULT value]

個中, DECLARE症結字是用來聲明變量的;var_name參數是變量的稱號,這裡可以同時界說多個變量;type參數用來指定變量的類型;DEFAULT value子句將變量默許值設置為value,沒有應用DEFAULT子句時,默許值為NULL。

【示例14-3】 上面界說變量my_sql,數據類型為INT型,默許值為10。代碼以下:

DECLARE  my_sql  INT  DEFAULT 10 ;

2.為變量賦值

MySQL中可使用SET症結字來為變量賦值。SET語句的根本語法以下:

SET  var_name = expr [, var_name = expr] ...

個中,SET症結字是用來為變量賦值的;var_name參數是變量的稱號;expr參數是賦值表達式。一個SET語句可以同時為多個變量賦值,各個變量的賦值語句之間用逗號離隔。

【示例14-4】 上面為變量my_sql賦值為30。代碼以下:

SET  my_sql = 30 ;

MySQL中還可使用SELECT…INTO語句為變量賦值。其根本語法以下:

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

個中,col_name參數表現查詢的字段稱號;var_name參數是變量的稱號;table_name參數指表的稱號;condition參數指查詢前提。

【示例14-5】 上面從employee表中查詢id為2的記載,將該記載的d_id值賦給變量my_sql。代碼以下:

SELECT  d_id  INTO  my_sql 
        FROM  employee  WEHRE  id=2 ; 

14.1.4  界說前提和處置法式

界說前提和處置法式是事前界說法式履行進程中能夠碰到的成績。而且可以在處置法式中界說處理這些成績的方法。這類方法可以提早猜測能夠湧現的成績,並提出處理方法。如許可以加強法式處置成績的才能,防止法式異常停滯。MySQL中都是經由過程DECLARE症結字來界說前提和處置法式。本末節中將具體講授若何界說前提和處置法式。

1.界說前提

MySQL中可使用DECLARE症結字來界說前提。其根本語法以下:

DECLARE condition_name CONDITION FOR condition_value 
condition_value: 
   SQLSTATE [VALUE] sqlstate_value | mysql_error_code 

個中,condition_name參數表現前提的稱號;condition_value參數表現前提的類型;sqlstate_value參數和mysql_error_code參數都可以表現MySQL的毛病。例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。

【示例14-6】 上面界說"ERROR 1146 (42S02)"這個毛病,稱號為can_not_find。可以用兩種分歧的辦法來界說,代碼以下:

//辦法一:應用sqlstate_value 
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ; 
//辦法二:應用mysql_error_code 
DECLARE can_not_find CONDITION FOR 1146 ; 

2.界說處置法式

MySQL中可使用DECLARE症結字來界說處置法式。其根本語法以下:

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement 
handler_type: 
  CONTINUE | EXIT | UNDO 
condition_value: 
  SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING 
    | NOT FOUND | SQLEXCEPTION | mysql_error_code 

個中,handler_type參數指明毛病的處置方法,該參數有3個取值。這3個取值分離是CONTINUE、EXIT和UNDO。CONTINUE表現碰到毛病不停止處置,持續向下履行;EXIT表現碰到毛病後立時加入;UNDO表現碰到毛病後撤回之前的操作,MySQL中臨時還不支撐這類處置方法。

留意:平日情形下,履行進程中碰到毛病應當連忙停滯履行上面的語句,而且撤回後面的操作。然則,MySQL中如今還不克不及支撐UNDO操作。是以,碰到毛病時最好履行EXIT操作。假如事前可以或許猜測毛病類型,而且停止響應的處置,那末可以履行CONTINUE操作。

condition_value參數指明毛病類型,該參數有6個取值。sqlstate_value和mysql_error_code與前提界說中的是統一個意思。condition_name是DECLARE界說的前提稱號。SQLWARNING表現一切以01開首的sqlstate_value值。NOT FOUND表現一切以02開首的sqlstate_value值。SQLEXCEPTION表現一切沒有被SQLWARNING或NOT FOUND捕捉的sqlstate_value值。sp_statement表現一些存儲進程或函數的履行語句。

【示例14-7】 上面是界說處置法式的幾種方法。代碼以下:

//辦法一:捕捉sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND'; 
//辦法二:捕捉mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND'; 
//辦法三:先界說前提,然後挪用 
DECLARE can_not_find CONDITION FOR 1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND'; 
//辦法四:應用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 
//辦法五:應用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
//辦法六:應用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR'; 

上述代碼是6種界說處置法式的辦法。第一種辦法是捕捉sqlstate_value值。假如碰到sqlstate_value值為42S02,履行CONTINUE操作,而且輸入"CAN NOT FIND"信息。第二種辦法是捕捉mysql_error_code值。假如碰到mysql_error_code值為1146,履行CONTINUE操作,而且輸入"CAN NOT FIND"信息。第三種辦法是先界說前提,然後再挪用前提。這裡先界說can_not_find前提,碰到1146毛病就履行CONTINUE操作。第四種辦法是應用SQLWARNING。SQLWARNING捕捉一切以01開首的sqlstate_value值,然後履行EXIT操作,而且輸入"ERROR"信息。第五種辦法是應用NOT FOUND。NOT FOUND捕捉一切以02開首的sqlstate_value值,然後履行EXIT操作,而且輸入"CAN NOT FIND"信息。第六種辦法是應用SQLEXCEPTION。SQLEXCEPTION捕捉一切沒有被SQLWARNING或NOT FOUND捕捉的sqlstate_value值,然後履行EXIT操作,而且輸入"ERROR"信息。

感激浏覽此文,願望能贊助到年夜家,感謝年夜家對本站的支撐!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved