Mysql 預存程序

標籤：

預存程序學習

        文章開始前，首先給出本文樣本表allIntersection。本文中的樣本是在Navicate for MySQL中進行的。

 

 

 

一.預存程序的建立與調用

1.基本文法及樣本：

        建立預存程序的基本文法：

 

Java代碼  

    create procedure sp_name()      begin        ………      end  

        調用此預存程序基本文法：

Sql代碼  　

call sp_name();

 

        注意：預存程序名稱後面必須加括弧，哪怕該預存程序沒有參數傳遞

 

        刪除預存程序的基本文法：

Sql代碼  　　

drop procedure sp_name;  

 

    注意事項：不能在一個預存程序中刪除另一個預存程序，只能調用另一個預存程序。

 

    其他常用命令：
        1. show procedure status
        顯示資料庫中所有儲存的預存程序基本資料，包括所屬資料庫，預存程序名稱，建立時間等。
        2. show create procedure sp_name
        顯示某一個預存程序的詳細資料。

 

    例1:

    建立一個預存程序：

Sql代碼  　　

    create procedure dayrepeat()      begin        select IntersectionUnit as ‘路口單位‘ , IntersectionName as ‘路口名稱‘ , DriveDirection as ‘行車方向‘ , SCar+MCar+BCar as ‘總流量‘        from allintersection;      end;  

 

    調用預存程序dayrepeat()：

Sql代碼  　　

call dayrepeat();  

    返回結果如所示：

 

 

2.帶參數傳遞的預存程序
    例2：

Sql代碼 

　　

    create procedure test1(      out ls decimal(8,2),      out hs decimal(8,2),      out avs decimal(8,2)      )      begin        select min(SCar) as ‘小型車車流量最小值‘        into ls        from allintersection;        select max(SCar) as ‘小型車車流量最大值‘        into hs        from allintersection;        select avg(SCar) as ‘小型車車流量平均值‘        into avs        from allintersection;      end;  

 

        此預存程序接受3個參數：ls、hs、avs。每個參數必須制定類型，這裡使用十進位值。關鍵字OUT指出相應的參數用來從預存程序傳出一個值(返回給調用者)。MySQL支援IN(傳遞給預存程序)、OUT(從預存程序傳出)和INOUT(對預存程序傳入和傳出)
注意：記錄集不是允許的類型，因此，不能通過一個參數返回多個行和列。

        由於此預存程序要求3個參數，因此必須正好傳遞3個參數，不多也不少，所以這條call語句給出3個參數，他們是預存程序儲存結果的3個變數的名字。

Sql代碼  

1. call test1(@小型車車流量最小值,@小型車車流量最大值,@小型車車流量平均值);  

        在調用時，這條語句並不顯示任何資料。它返回以後可以顯示(或在其他處理中使用)的變數。

        為了顯示檢索出的結果，可如下進行：

Sql代碼  

1. SELECT @小型車車流量平均值 , @小型車車流量最大值 , @小型車車流量平均值;  

    檢索結果如所示：

 

 

例3：
        下面是另外一個例子，這次使用IN和OUT參數。isTotal接受路口名稱參數並返回該路口的總車流量。

Sql代碼  　　

    create procedure isTotal(      IN isID int,      OUT iTotal decimal(8,2)      )      begin        select SCar+MCar+BCar AS ‘路口總車流量‘        from allintersection        where ID = isID        into iTotal;      end;  

 

        isID被定義為IN，因為ID被傳入預存程序。iTotal定義為OUT，因為要從預存程序返回合計。

 

        為調用這個預存程序和顯示合計結果，可使用以下語句：

Sql代碼  　　

    call isTotal(1,@total);      select @total;  

 

        結果如所示：

 

 

二.使用遊標

1.建立遊標
        遊標用declare語句建立。如下面的例子所示:

Sql代碼  　　

    create procedure test2()      begin          declare cursorTest cursor          for      select *      from allIntersection;      end;  

 

2.開啟和關閉遊標
        遊標用OPEN CURSOR語句來開啟，用CLOSE CURSOR來關閉

Sql代碼  　　

    open cursorTest;      close cursorTest;  

 

        CLOSE釋放遊標使用的所有內部記憶體和資源，因此在每個遊標不再需要時都應該關閉。在一個遊標關閉後，如果沒有重新開啟，則不能使用它。但是，聲明過的遊標不需要再次聲明，用OPEN語句開啟它就可以了。
3.使用遊標資料
        在一個遊標被開啟後，可以使用FETCH語句分別訪問它的每一行。FETCH語句指定檢索什麼資料(所需的列)，檢索出來的資料存放區在什麼地方。它還向前移動遊標中的內部行指標，使下一條FETCH語句檢索下一行(不重複讀取同一行)。

Sql代碼   　　

    create procedure test3()      begin          declare o int;            -- 聲明一個局部變數        declare cursorTest3 cursor        -- 聲明一個遊標          for            select ID          from allintersection;          open cursorTest3;          -- 開啟遊標          fetch cursorTest3 into o;              -- 擷取IntersectionName          close cursorTest3;  -- 關閉遊標      end;  

        其中FETCH用來檢索當前行的IntersectionName列(將自動從第一行開始)到一個名為o的局部聲明的變數中。對檢索出的資料部做任何處理。

 

例4：

        這個例子迴圈檢索資料，從第一行到最後一行。

Sql代碼  　　

    create procedure test4()      begin          declare done boolean default 0;          declare o int;            -- 聲明一個局部變數        declare cursorTest4 cursor        -- 聲明一個遊標          for            select ID          from allintersection;        declare continue handler for sqlstate ‘02000‘ set done=1;          open cursorTest4;          -- 開啟遊標          -- 遍曆所有的行          repeat                 fetch cursorTest4 into o;              -- 擷取IntersectionName          until done end repeat;           -- 結束迴圈          close cursorTest4;  -- 關閉遊標      end;  

 

        與上一個例子不同的是，這個例子中的FETCH是在REPEAT內，因此它反覆執行到done為真(until done end repeat;規定)。為使它起作用，用一個default 0(假，不結束)定義變數done。那麼，done怎樣才能在結束時被設定為真呢？答案是用以下語句：

Sql代碼  

1. declare continue handler for sqlstate ‘02000‘ set done=1;  

        這條語句定義了一個CONTINUE HANDLER，它是在條件出現時被執行的代碼。這裡，它指出SQLSTATE ‘02000‘出現時，set done=1。SQLSTATE ‘02000‘是一個未找到條件，當REPEAT由於沒有更多的行供迴圈而不能繼續時，出現這個條件。

 

 

例5：

        這個例子比上面的例子更複雜，對取出的資料進行了實際的處理。

Sql代碼  

    create procedure test5()      begin          -- 聲明局部變數          declare done boolean default 0;          declare o int;                         -- declare t DECIMAL(8,2);              declare cursorTest5 cursor        -- 聲明一個遊標                for            select ID from allintersection;        -- 定義continue handler        declare continue handler for sqlstate ‘02000‘ set done=1;                     -- 建立daysRepeat表來儲存結果          create table if not exists daysRepeat          (ID int , total decimal(8,2));                open cursorTest5;          -- 開啟遊標                 -- 遍曆所有的行      fetch cursorTest5 into o;        repeat                 call isTotal(o , @t);                 insert into daysRepeat(ID , total)                 values(o,@t);                 fetch cursorTest5 into o;              -- 擷取IntersectionName          until done end repeat;           -- 結束迴圈          close cursorTest5;  -- 關閉遊標      end;  

 

        然後執行預存程序：

Sql代碼  

1. call test5();    

        結果產生新的表daysRepeat，並往表裡填充資料，填充後的表如所示：

Mysql 預存程序