mysql-存儲過程(轉載)

来源:http://www.cnblogs.com/wuyoucao/archive/2017/05/16/6861359.html
-Advertisement-
Play Games

本來想自己寫存儲過程的,但是看到別人寫的很全面,就直接轉載過來了。 轉自(http://www.cnblogs.com/exmyth/p/3303470.html) 14.1.1 創建存儲過程 MySQL中,創建存儲過程的基本形式如下: 其中,sp_name參數是存儲過程的名稱;proc_param ...


本來想自己寫存儲過程的,但是看到別人寫的很全面,就直接轉載過來了。

轉自(http://www.cnblogs.com/exmyth/p/3303470.html)

 

14.1.1 創建存儲過程

MySQL中,創建存儲過程的基本形式如下:

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

其中,sp_name參數是存儲過程的名稱;proc_parameter表示存儲過程的參數列表; characteristic參數指定存儲過程的特性;routine_body參數是SQL代碼的內容,可以用BEGIN…END來標誌SQL代碼的開始和結束。

proc_parameter中的每個參數由3部分組成。這3部分分別是輸入輸出類型、參數名稱和參數類型。其形式如下:

  1. [ 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的存儲過程。代碼如下:

  1. CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num INT )  
  2.           READS SQL DATA  
  3.           BEGIN  
  4.               SELECT  COUNT(*)  INTO  count_num  
  5.               FROM  employee  
  6.               WHERE  d_id=emp_id ;  
  7.           END 

上述代碼中,存儲過程名稱為num_from_employee;輸入變數為emp_id;輸出變數為count_num。SELECT語句從employee表查詢d_id值等於emp_id的記錄,並用COUNT(*)計算d_id值相同的記錄的條數,最後將計算結果存入count_num中。代碼的執行結果如下:

  1. mysql> DELIMITER &&  
  2. mysql> CREATE  PROCEDURE  num_from_employee
    (IN emp_id INT, OUT count_num INT )  
  3.     -> READS SQL DATA  
  4.     -> BEGIN  
  5.     -> SELECT  COUNT(*)  INTO  count_num  
  6.     -> FROM  employee  
  7.     -> WHERE  d_id=emp_id ;  
  8.     -> END &&  
  9. Query OK, 0 rows affected (0.09 sec)  
  10. mysql> DELIMITER ; 

代碼執行完畢後,沒有報出任何出錯信息就表示存儲函數已經創建成功。以後就可以調用這個存儲過程,資料庫中會執行存儲過程中的SQL語句。

說明:MySQL中預設的語句結束符為分號(;)。存儲過程中的SQL語句需要分號來    結束。為了避免衝突,首先用"DELIMITER &&"將MySQL的結束符設置為&&。最後再用"DELIMITER ;"來將結束符恢覆成分號。這與創建觸發器時是一樣的。

 

 

 

14.1.2 創建存儲函數

在MySQL中,創建存儲函數的基本形式如下:

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

其中,sp_name參數是存儲函數的名稱;func_parameter表示存儲函數的參數列表;RETURNS type指定返回值的類型;characteristic參數指定存儲函數的特性,該參數的取值與存儲過程中的取值是一樣的,請讀者參照14.1.1小節的內容;routine_body參數是SQL代碼的內容,可以用BEGIN…END來標誌SQL代碼的開始和結束。

func_parameter可以由多個參數組成,其中每個參數由參數名稱和參數類型組成,其形式如下:

  1. param_name type 

其中,param_name參數是存儲函數的參數名稱;type參數指定存儲函數的參數類型,該類型可以是MySQL資料庫的任意數據類型。

【示例14-2】 下麵創建一個名為name_from_employee的存儲函數。代碼如下:

  1. CREATE  FUNCTION  name_from_employee (emp_id INT )  
  2.           RETURNS VARCHAR(20)  
  3.           BEGIN  
  4.               RETURN  (SELECT  name  
  5.               FROM  employee  
  6.               WHERE  num=emp_id );  
  7.           END 

上述代碼中,存儲函數的名稱為name_from_employee;該函數的參數為emp_id;返回值是VARCHAR類型。SELECT語句從employee表查詢num值等於emp_id的記錄,並將該記錄的name欄位的值返回。代碼的執行結果如下:

  1. mysql> DELIMITER &&  
  2. mysql> CREATE  FUNCTION  name_from_employee (emp_id INT )  
  3.     -> RETURNS VARCHAR(20)  
  4.     -> BEGIN  
  5.     -> RETURN  (SELECT  name  
  6.     -> FROM  employee  
  7.     -> WHERE  num=emp_id );  
  8.     -> END&&  
  9. Query OK, 0 rows affected (0.00 sec)  
  10. mysql> DELIMITER ; 

結果顯示,存儲函數已經創建成功。該函數的使用和MySQL內部函數的使用方法一樣。

 

 

 

14.1.3  變數的使用

在存儲過程和函數中,可以定義和使用變數。用戶可以使用DECLARE關鍵字來定義變數。然後可以為變數賦值。這些變數的作用範圍是BEGIN…END程式段中。本小節將講解如何定義變數和為變數賦值。

1.定義變數

MySQL中可以使用DECLARE關鍵字來定義變數。定義變數的基本語法如下:

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

其中, DECLARE關鍵字是用來聲明變數的;var_name參數是變數的名稱,這裡可以同時定義多個變數;type參數用來指定變數的類型;DEFAULT value子句將變數預設值設置為value,沒有使用DEFAULT子句時,預設值為NULL。

【示例14-3】 下麵定義變數my_sql,數據類型為INT型,預設值為10。代碼如下:

  1. DECLARE  my_sql  INT  DEFAULT 10 ; 

2.為變數賦值

MySQL中可以使用SET關鍵字來為變數賦值。SET語句的基本語法如下:

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

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

【示例14-4】 下麵為變數my_sql賦值為30。代碼如下:

  1. SET  my_sql = 30 ; 

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

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

其中,col_name參數表示查詢的欄位名稱;var_name參數是變數的名稱;table_name參數指表的名稱;condition參數指查詢條件。

【示例14-5】 下麵從employee表中查詢id為2的記錄,將該記錄的d_id值賦給變數my_sql。代碼如下:

  1. SELECT  d_id  INTO  my_sql  
  2.         FROM  employee  WEHRE  id=2 ; 

 

 

14.1.4  定義條件和處理程式

定義條件和處理程式是事先定義程式執行過程中可能遇到的問題。並且可以在處理程式中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題,並提出解決辦法。這樣可以增強程式處理問題的能力,避免程式異常停止。MySQL中都是通過DECLARE關鍵字來定義條件和處理程式。本小節中將詳細講解如何定義條件和處理程式。

1.定義條件

MySQL中可以使用DECLARE關鍵字來定義條件。其基本語法如下:

  1. DECLARE  condition_name  CONDITION  FOR  condition_value  
  2. condition_value:  
  3.       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。可以用兩種不同的方法來定義,代碼如下:

  1. //方法一:使用sqlstate_value  
  2. DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
  3. //方法二:使用mysql_error_code  
  4. DECLARE  can_not_find  CONDITION  FOR  1146 ; 

2.定義處理程式

MySQL中可以使用DECLARE關鍵字來定義處理程式。其基本語法如下:

  1. DECLARE handler_type HANDLER FOR 
    condition_value[,...] sp_statement  
  2. handler_type:  
  3.     CONTINUE | EXIT | UNDO  
  4. condition_value:  
  5.     SQLSTATE [VALUE] sqlstate_value |
    condition_name  | SQLWARNING  
  6.        | 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】 下麵是定義處理程式的幾種方式。代碼如下:

  1. //方法一:捕獲sqlstate_value  
  2. DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
    SET @info='CAN NOT FIND';  
  3. //方法二:捕獲mysql_error_code  
  4. DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  
  5. //方法三:先定義條件,然後調用  
  6. DECLARE  can_not_find  CONDITION  FOR  1146 ;  
  7. DECLARE CONTINUE HANDLER FOR can_not_find SET 
    @info='CAN NOT FIND';  
  8. //方法四:使用SQLWARNING  
  9. DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
  10. //方法五:使用NOT FOUND  
  11. DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  
  12. //方法六:使用SQLEXCEPTION  
  13. 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"信息。  

 

轉自(http://www.cnblogs.com/exmyth/p/3303470.html) 

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 轉載請註明:http://www.cnblogs.com/igoslly/p/6858656.html 初步設置Menu 設置Menu,在ActionBar上添加按鈕操作: 在main/res目錄下添加menu文件夾,創建main.xml文件 showAsAction屬性共有五個值:ifRoom、n ...
  • fragment_shoppingcart.xml 1、採用自定義技術實現購物車的加減號; 2、採用CartStorage實現購物車業務的處理; 3、採用popupwindow實現購物車的對話框 4、實現聯繫客服功能; 5、採用Html5和Android互調技術實現詳情頁面; 6、集成支付寶支付功能 ...
  • fragment_user.xml 1、標題頭採用GradationTitleBar實現仿QQ空間標題欄漸變效果; ...
  • Android N 可以同時顯示多個應用視窗。 在手機上,兩個應用可以在“分屏”模式中左右併排或上下併排顯示。例如,用戶可以 在上面視窗聊QQ,下麵視窗發送簡訊。 如圖所示,兩個app在分屏模式中上下顯示: 如何讓你的app支持多視窗? 如果你的app支持Adnroid N,在AndroidMani ...
  • 1.用代碼設置顏色 ...
  • 最近開發導入其他Android項目遇見的問題,如下圖: 解決辦法: 將org.gradle.jvmargs=的值該為521(堆記憶體分配過高導致) 備忘,希望能幫助到大家 ...
  • 對於數據的操作大體可分為4種:創建、查詢、刪除、修改(簡稱為“增刪改查”,也成為crud,即create/retrieve/update/delete)。 1. 創建數據 :insert into tb_name (欄位列表) values(值列表); 對於左圖的table,若想插入數據,但只對其中 ...
  • 先ctrl+H ,出現如下對話框 點擊“定位”,選擇“空值” 在表格中空的位置上編輯欄輸入0,CTRL+ENTER,即可將缺失數據全部用0補齊。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...