MY SQL存儲過程、游標、觸發器

来源:http://www.cnblogs.com/xiaofox0018/archive/2016/10/24/5992122.html
-Advertisement-
Play Games

MySQL5 中添加了存儲過程的支持。 大多數SQL語句都是針對一個或多個表的單條語句。並非所有的操作都怎麼簡單。經常會有一個完整的操作需要多條才能完成 存儲過程簡單來說,就是為以後的使用而保存的一條或多條MySQL語句的集合。可將其視為批文件。雖然他們的作用不僅限於批處理。 為什麼要使用存儲過程: ...


MySQL5 中添加了存儲過程的支持。

 大多數SQL語句都是針對一個或多個表的單條語句。並非所有的操作都怎麼簡單。經常會有一個完整的操作需要多條才能完成

 存儲過程簡單來說,就是為以後的使用而保存的一條或多條MySQL語句的集合。可將其視為批文件。雖然他們的作用不僅限於批處理。

 為什麼要使用存儲過程:優點

1 通過吧處理封裝在容易使用的單元中,簡化複雜的操作

2 由於不要求反覆建立一系列處理步驟,這保證了數據的完整性。如果開發人員和應用程式都使用了同一存儲過程,則所使用的代碼是相同的。還有就是防止錯誤,需要執行的步驟越多,出錯的可能性越大。防止錯誤保證了數據的一致性。

3 簡化對變動的管理。如果表名、列名或業務邏輯有變化。只需要更改存儲過程的代碼,使用它的人員不會改自己的代碼了都。

4 提高性能,因為使用存儲過程比使用單條SQL語句要快

5 存在一些職能用在單個請求中的MySQL元素和特性,存儲過程可以使用它們來編寫功能更強更靈活的代碼

換句話說3個主要好處簡單、安全、高性能

    缺點:

 1 一般來說,存儲過程的編寫要比基本的SQL語句複雜,編寫存儲過程需要更高的技能,更豐富的經驗。

 2 你可能沒有創建存儲過程的安全訪問許可權。許多資料庫管理員限制存儲過程的創建,允許用戶使用存儲過程,但不允許創建存儲過程

    存儲過程是非常有用的,應該儘可能的使用它們

    執行存儲過程

        MySQL稱存儲過程的執行為調用,因此MySQL執行存儲過程的語句為CALL        .CALL接受存儲過程的名字以及需要傳遞給它的任意參數

            CALL productpricing(@pricelow , @pricehigh , @priceaverage);

            //執行名為productpricing的存儲過程,它計算並返回產品的最低、最高和平均價格

    創建存儲過程

        CREATE  PROCEDURE 存儲過程名()

           一個例子說明:一個返回產品平均價格的存儲過程如下代碼:

           CREATE  PROCEDURE  productpricing()

           BEGIN

            SELECT Avg(prod_price)  AS priceaverage

           FROM products;

           END;

        //創建存儲過程名為productpricing,如果存儲過程需要接受參數,可以在()中列舉出來。即使沒有參數後面仍然要跟()。BEGIN和END語句用來限定存儲過程體,過程體本身是個簡單的SELECT語句

        在MYSQL處理這段代碼時會創建一個新的存儲過程productpricing。沒有返回數據。因為這段代碼時創建而不是使用存儲過程。

 

    Mysql命令行客戶機的分隔符

        預設的MySQL語句分隔符為分號 ; 。Mysql命令行實用程式也是 ; 作為語句分隔符。如果命令行實用程式要解釋存儲過程自身的 ; 字元,則他們最終不會成為存儲過程的成分,這會使存儲過程中的SQL出現句法錯誤

        解決方法是臨時更改命令實用程式的語句分隔符

            DELIMITER //    //定義新的語句分隔符為//

            CREATE PROCEDURE productpricing()

            BEGIN

            SELECT Avg(prod_price) AS priceaverage

            FROM products;

            END //

            DELIMITER ;    //改回原來的語句分隔符為 ;

            除\符號外,任何字元都可以作為語句分隔符

        CALL productpricing();  //使用productpricing存儲過程

        執行剛創建的存儲過程並顯示返回的結果。因為存儲過程實際上是一種函數,所以存儲過程名後面要有()符號

    刪除存儲過程

        DROP PROCEDURE productpricing ;     //刪除存儲過程後面不需要跟(),只給出存儲過程名

        為了刪除存儲過程不存在時刪除產生錯誤,可以判斷僅存儲過程存在時刪除

        DROP PROCEDURE IF EXISTS

        使用參數

        Productpricing只是一個簡單的存儲過程,他簡單地顯示SELECT語句的結果。

        一般存儲過程並不顯示結果,而是把結果返回給你指定的變數

            CREATE PROCEDURE productpricing(

            OUT p1 DECIMAL(8,2),

            OUT ph DECIMAL(8,2),

            OUT pa DECIMAL(8,2),

            )

            BEGIN

            SELECT Min(prod_price)

            INTO p1

            FROM products;

            SELECT Max(prod_price)

            INTO ph

            FROM products;

            SELECT Avg(prod_price)

            INTO pa

            FROM products;

            END;

            此存儲過程接受3個參數,p1存儲產品最低價格,ph存儲產品最高價格,pa存儲產品平均價格。每個參數必須指定類型,這裡使用十進位值。關鍵字OUT指出相應的參數用來從存儲過程傳給一個值(返回給調用者)。MySQL支持IN(傳遞給存儲過程)、OUT(從存儲過程中傳出、如這裡所用)和INOUT(對存儲過程傳入和傳出)類型的參數。存儲過程的代碼位於BEGIN和END語句內,如前所見,它們是一些列SELECT語句,用來檢索值,然後保存到相應的變數(通過INTO關鍵字)

        調用修改過的存儲過程必須指定3個變數名:

        CALL productpricing(@pricelow , @pricehigh , @priceaverage);

        這條CALL語句給出3個參數,它們是存儲過程將保存結果的3個變數的名字

    變數名  所有的MySQL變數都必須以@開始

    使用變數

        SELECT @priceaverage ;

        SELECT @pricelow , @pricehigh , @priceaverage ;   //獲得3給變數的值

        下麵是另一個例子,這次使用IN和OUT參數。ordertotal接受訂單號,並返回該訂單的合計

            CREATE PROCEDURE ordertotal(

           IN onumber INT,

           OUT ototal DECIMAL(8,2)

            )

            BEGIN

            SELECT Sum(item_price*quantity)

            FROM orderitems

            WHERE order_num = onumber

            INTO ototal;

            END;

            //onumber定義為IN,因為訂單號時被傳入存儲過程,ototal定義為OUT,因為要從存儲過程中返回合計,SELECT語句使用這兩個參數,WHERE子句使用onumber選擇正確的行,INTO使用ototal存儲計算出來的合計

    為了調用這個新的過程,可以使用下列語句:

        CALL ordertotal(2005 , @total);   //這樣查詢其他的訂單總計可直接改變訂單號即可

        SELECT @total;

    建立智能的存儲過程

        上面的存儲過程基本都是封裝MySQL簡單的SELECT語句,但存儲過程的威力在它包含業務邏輯和智能處理時才顯示出來

        例如:你需要和以前一樣的訂單合計,但需要對合計增加營業稅,不活只針對某些顧客(或許是你所在區的顧客)。那麼需要做下麵的事情:

            1 獲得合計(與以前一樣)

            2 吧營業稅有條件地添加到合計

            3 返回合計(帶或不帶稅)

        存儲過程的完整工作如下:

            -- Name: ordertotal

            -- Parameters: onumber = 訂單號

            --           taxable = 1為有營業稅 0 為沒有

            --           ototal = 合計

            CREATE  PROCEDURE ordertotal(

            IN onumber INT,

            IN taxable BOOLEAN,

            OUT ototal DECIMAL(8,2)

            -- COMMENT()中的內容將在SHOW PROCEDURE STATUS ordertotal()中顯示,其備註作用

            ) COMMENT 'Obtain order total , optionally adding tax'

            BEGIN

            -- 定義total局部變數

            DECLARE total DECIMAL(8,2)

            DECLARE taxrate INT DEFAULT 6;

 

            -- 獲得訂單的合計,並將結果存儲到局部變數total中

            SELECT Sum(item_price*quantity)

            FROM orderitems

            WHERE order_num = onumber

            INTO total;

 

            -- 判斷是否需要增加營業稅,如為真,這增加6%的營業稅

            IF taxable THEN

            SELECT total+(total/100*taxrate) INTO total;

                  END IF;

            -- 把局部變數total中才合計傳給ototal中

            SELECT total INTO ototal;

            END;

            此存儲過程有很大的變動,首先,增加了註釋(前面放置--)。在存儲過程複雜性增加時,這樣很重要。在存儲體中,用DECLARE語句定義了兩個局部變數。DECLARE要求制定變數名和數據類型,它也支持可選的預設值(這個例子中taxrate的預設設置為6%),SELECT 語句已經改變,因此其結果存儲到total局部變數中而不是ototal。IF語句檢查taxable是否為真,如果為真,則用另一SELECT語句增加營業稅到局部變數total,最後用另一SELECT語句將total(增加了或沒有增加的)保存到ototal中。

    COMMENT關鍵字  本列中的存儲過程在CREATE PROCEDURE 語句中包含了一個COMMENT值,他不是必需的,但如果給出,將在SHOW PROCEDURE STATUS的結果中顯示

    IF語句   這個例子中給出了MySQL的IF語句的基本用法。IF語句還支持ELSEIF和ELSE子句(前者還使用THEN子句,後者不使用)

    檢查存儲過程

        為顯示用來創建一個存儲過程的CREATE語句,使用SHOW CREATE PROCEDURE語句

            SHOW CREATE PROCEDURE ordertotal;

        為了獲得包括何時、有誰創建等詳細信息的存儲過程列表。使用SHOW PROCEDURE STATUS.限制過程狀態結果,為了限制其輸出,可以使用LIKE指定一個過濾模式,例如:SHOW PROCEDURE STATUS LIKE ''ordertotal;

MySQL5添加了對游標的支持

    只能用於存儲過程

    由前幾章可知,mysql檢索操作返回一組稱為結果集的行。都與mysql語句匹配的行(0行或多行),使用簡單的SELECT語句,沒有辦法得到第一行、下一行或前10行,也不存在每次行地處理所有行的簡單方法(相對於成批處理他們)

    有時,需要在檢索出來的行中前進或後退一行或多行。這就是使用游標的原因。游標(cursor)是一個存儲在MYSQL伺服器上的資料庫查詢,它不是一條SELECT語句,而是被該語句檢索出來的結果集。在存儲了游標之後,應用程式可以根據需要滾動或瀏覽其中的數據。

    游標主要用於互動式應用,其中用戶需要滾動屏幕上的數據,並對數據進行瀏覽或做出更改。

    使用游標

        使用游標涉及幾個明確的步驟:

            1 在能夠使用游標前,必須聲明(定義)它,這個過程實際上沒有檢索數據,它只是定義要使用的SELECT語句

            2 一旦聲明後,必須打開游標以供使用。這個過程用錢嗎定義的SELECT語句吧數據實際檢索出來

            3 對於填有數據的游標,根據需要取出(檢索)的各行

            4 在接受游標使用時,必須關閉它 如果不明確關閉游標,MySQL將會在到達END語句時自動關閉它

    創建游標

        游標可用DECLARE 語句創建。 DECLARE命名游標,並定義相應的SELECT語句。根據需要選擇帶有WHERE和其他子句。如:下麵第一名為ordernumbers的游標,使用了檢索所有訂單的SELECT語句

            CREATE PROCEDURE processorders()

            BEGIN

            DECLARE ordernumbers CURSOR

            FOR

            SELECT order_num FROM orders ;

            END;

            存儲過程處理完成後,游標就消失,因為它局限於存儲過程

    打開和關閉游標

            CREATE PROCEDURE processorders()

            BEGIN

            DECLAREordernumbers CURSOR

            FOR

            SELECT order_num FROM orders ;

            Open ordernumbers ;

            Close ordernumbers ;  //CLOSE釋放游標使用的所有內部記憶體和資源,因此,每個游標不需要時都應該關閉

            END;

    使用游標數據

        在一個游標被打開後,可以使用FETCH語句分別訪問它的每一行。FETCH指定檢索什麼數據(所需的要列),檢索出來的數據存儲在什麼地方。它還向前移動游標中的內部行指針,使下一條FETCH語句檢索下一行,相當於PHP中的each()函數

迴圈檢索數據,從第一行到最後一行

            CREATE PROCEDURE processorders()

            BEGIN

            -- 聲明局部變數

            DECLARE done BOOLEAN DEFAULT 0;

            DECLARE o INT;

 

            DECLAREordernumbers CURSOR

            FOR

            SELECT order_num FROM orders ;

            -- 當SQLSTATE為02000時設置done值為1

            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

            --打開游標

            Open ordernumbers ;

            -- 開始迴圈

            REPEAT

            -- 把當前行的值賦給聲明的局部變數o中

            FETCH ordernumbers INTO o;

            -- 當done為真時停止迴圈

            UNTIL done END REPEAT;

            --關閉游標

            Close ordernumbers ;  //CLOSE釋放游標使用的所有內部記憶體和資源,因此,每個游標不需要時都應該關閉

            END;

        語句中定義了CONTINUE HANDLER ,它是在條件出現時被執行的代碼。這裡,它指出當SQLSTATE '02000'出現時,SET done=1。SQLSTATE '02000'是一個未找到條件,當REPEAT沒有更多的行供迴圈時,出現這個條件。

    DECLARE 語句次序  用DECLARE語句定義局部變數必須在定義任意游標或句柄之前定義,而句柄必須在游標之後定義。不遵守此規則就會出錯

重覆和迴圈   除這裡使用REPEAT語句外,MySQL還支持迴圈語句,它可用來重覆執行代碼,直到使用LEAVE語句手動退出為止。通常REPEAT語句的語法使它更適合於對游標進行的迴圈。

為了把這些內容組織起來,這次吧取出的數據進行某種實際的處理

        CREATE PROCEDURE processorders()

        BEGIN

        -- 聲明局部變數

        DECLARE done BOOLEAN DEFAULT 0;

        DECLARE o INT;

        DECLARE t DECIMAL(8,2)

 

        DECLAREordernumbers CURSOR

        FOR

        SELECT order_num FROM orders ;

        -- 當SQLSTATE為02000時設置done值為1

        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

        -- 創建一個ordertotals的表

        CREATE TABLE IF NOT EXISTS ordertotals( order_num INT , total DECIMAL(8,2))

        --打開游標

        Open ordernumbers ;

        -- 開始迴圈

        REPEAT

        -- 把當前行的值賦給聲明的局部變數o中

        FETCH ordernumbers INTO o;

        -- 用上文講到的ordertotal存儲過程並傳入參數,返回營業稅計算後的合計傳給t變數

        CALL ordertotal(o , 1 ,t)

        -- 把訂單號和合計插入到新建的ordertotals表中

        INSERT INTO ordertotals(order_num, total) VALUES(o , t);

        -- 當done為真時停止迴圈

        UNTIL done END REPEAT;

        --關閉游標

        Close ordernumbers ;  //CLOSE釋放游標使用的所有內部記憶體和資源,因此,每個游標不需要時都應該關閉

        END;

        最後SELECT * FROM ordertotals就能查看結果了

 使用觸發器

    MySQL5版本後支持觸發器

    只有表支持觸發器,視圖不支持觸發器

    MySQL語句在需要的時被執行,存儲過程也是如此,但是如果你想要某條語句(或某些語句)在事件發生時自動執行,那該怎麼辦呢:例如:

        1 每增加一個顧客到某個資料庫表時,都檢查其電話號碼格式是否正確,區的縮寫是否為大寫

        2 每當訂購一個產品時,都從庫存數量中減少訂購的數量

        3 無論何時刪除一行,都在某個存檔中保留一個副本

    這寫例子的共同之處是他們都需要在某個表發生更改時自動處理。這就是觸發器。觸發器是MySQL響應一下任意語句而自動執行的一條MySQL語句(或位於BEGIN和END語句之間的一組語句)

    1 DELETE

    2 INSERT

    3 UPDATE

    其他的MySQL語句不支持觸發器

    創建觸發器

        創建觸發器需要給出4條信息

        1 唯一的觸發器名;  //保存每個資料庫中的觸發器名唯一

        2 觸發器關聯的表;

        3 觸發器應該響應的活動(DELETE、INSERT或UPDATE)

        4 觸發器何時執行(處理前還是後,前是BEFORE 後是AFTER)

        創建觸發器用CREATE TRIGGER

        CREATE TRIGGER newproduct AFTER INSERT ON products

        FOR EACH ROW SELECT'Product added'

       創建新觸發器newproduct ,它將在INSERT語句成功執行後執行。這個觸發器還鎮定FOR EACH ROW,因此代碼對每個插入的行執行。這個例子作用是文本對每個插入的行顯示一次product added

        FOR EACH ROW 針對每個行都有作用,避免了INSERT一次插入多條語句

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

-Advertisement-
Play Games
更多相關文章
  • 對於HTTPS我在網上找了一堆資料看了下, 各種協議和證書已經有點暈了 最後我現有的感覺是, 在HTTP伺服器上放一個證書, 在原本的HTTP訪問之前客戶端先檢查證書是否正確 如果客戶端證書檢查正確, 說明對 這個伺服器就是我要連接的那個server 不對, 就說明這個server是個假冒的 同樣也 ...
  •  ...
  • (六)與系統交互 6.1後臺通知 1.關於後臺通知,下麵展示6種樣式。值得一提的是,筆者的小米5只能顯示基本樣式,雷軍真是良心廠商啊。 2.首先上佈局xml 3.接著是完整代碼 6.3定時執行周期任務 1.AlarmManager用來管理和執行任務,可以在程式沒有運行的時候執行。並且有多種啟動和計算 ...
  • 清理單個應用緩存 通過反射調用方法 需要許可權:android.permission.DELETE_CACHE_FILES. 以上許可權為系統許可權,手機衛士應用為用戶應用,不能拿到此許可權 換思路: 調用系統清除緩存的界面,讓用戶在系統清除緩存界面實現清除邏輯 查找系統清除緩存的界面方法: ...
  • (四)實現設備硬體交互與媒體交互 4.6自定義攝像頭覆蓋層 1.將Camera中的內容實時的繪製到SurfaceView中 若要自定義拍攝界面,只要重新定義surface的界面即可 以下展示全部代碼 2.改變拍攝方向,調用setDisplayOrientation(90)後,拍攝方向方向才會垂直顯示 ...
  • 來自sqlite3源碼 /*** Compute a string length that is limited to what can be stored in** lower 30 bits of a 32-bit signed integer.**** The value returned w... ...
  • (1):Android體繫結構; 應用程式層(Applications), 應用程式框架層(Application Framework),系統運行庫層(Librarries),Linux內核層(Linux Kernel); (2)Android項目中文件夾的作用: 存放一些資源文件的信息,用於讀取文 ...
  • 關於Android程式的構架, 當前(2016.10)最流行的模式即為MVP模式, Google官方提供了Sample代碼來展示這種模式的用法. 本文為閱讀官方sample代碼的閱讀筆記和分析. ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...