mysql預存程序學習筆記

標籤：

1.      預存程序簡介

我們常用的操作資料庫語言SQL語句在執行的時候需要要先編譯，然後執行，而預存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後儲存在資料庫中，使用者通過指定預存程序的名字並給定參數（如果該預存程序帶有參數）來調用執行它。

一個預存程序是一個可程式化的函數，它在資料庫中建立並儲存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程式或平台上執行相同的函數，或者封裝特定功能時，預存程序是非常有用的。資料庫中的預存程序可以看做是對編程中物件導向方法的類比。它允許控制資料的訪問方式。

預存程序通常有以下優點：

(1).預存程序增強了SQL語言的功能和靈活性。預存程序可以用流量控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。

(2).預存程序允許標準組件是編程。預存程序被建立後，可以在程式中被多次調用，而不必重新編寫該預存程序的SQL語句。而且資料庫專業人員可以隨時對預存程序進行修改，對應用程式原始碼毫無影響。

(3).預存程序能實現較快的執行速度。

2.      關於MySQL的預存程序

預存程序是資料庫儲存的一個重要的功能，但是MySQL在5.0以前並不支援預存程序，這使得MySQL在應用上大打折扣。好在MySQL 5.0終於開始已經支援預存程序，這樣即可以大大提高資料庫的處理速度，同時也可以提高資料庫編程的靈活性。

3.      MySQL預存程序建立

MySQL預存程序建立的格式：CREATE PROCEDURE 過程名 ([過程參數[,...]])
[特性 ...] 過程體 

這裡先舉個例子：

 DELIMITER     CREATE PROCEDURE proc1(OUT s int)          BEGIN             SELECT COUNT(*) INTO s FROM user;          END DELIMITER ; 

註：

（1）這裡需要注意的是DELIMITER //和DELIMITER ;兩句，DELIMITER是分割符的意思，因為MySQL預設以";"為分隔字元，如果我們沒有聲明分割符，那麼編譯器會把預存程序當成SQL語句進行處理，則預存程序的編譯過程會報錯，所以要事先用DELIMITER關鍵字申明當前段分隔字元，這樣MySQL才會將";"當做預存程序中的代碼，不會執行這些代碼，用完了之後要把分隔字元還原。

（2）預存程序根據需要可能會有輸入、輸出、輸入輸出參數，這裡有一個輸出參數s，類型是int型，如果有多個參數用","分割開。

（3）過程體的開始與結束使用BEGIN與END進行標識。 

參數

MySQL預存程序的參數用在預存程序的定義，共有三種參數類型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE([[IN |OUT |INOUT ] 參數名 資料類形...])

IN 輸入參數:表示該參數的值必須在調用預存程序時指定，在預存程序中修改該參數的值不能被返回，為預設值

OUT 輸出參數:該值可在預存程序內部被改變，並可返回

INOUT 輸入輸出參數:調用時指定，並且可被改變和返回

變數

Ⅰ. 變數定義

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype為MySQL的資料類型，如:int, float, date, varchar(length)

例如:

1. DECLARE l_int int unsigned default 4000000;  
2. DECLARE l_numeric number(8,2) DEFAULT 9.95;  
3. DECLARE l_date date DEFAULT ‘1999-12-31‘;  
4. DECLARE l_datetime datetime DEFAULT ‘1999-12-31 23:59:59‘;  
5. DECLARE l_varchar varchar(255) DEFAULT ‘This will not be padded‘;    

Ⅱ. 變數賦值

 SET 變數名 = 運算式值 [,variable_name = expression ...]

使用者變數：

①使用者變數名一般以@開頭

1. SELECT ‘Hello World‘ into @x;  SELECT @x;  

MySQL預存程序的調用

用call和你過程名以及一個括弧，括弧裡面根據需要，加入參數，參數包括輸入參數、輸出參數、輸入輸出參數。具體的調用方法可以參看上面的例子。

MySQL預存程序的修改

用ALTER PROCEDURE更改，用CREATE PROCEDURE 建立。

 MySQL預存程序的刪除

DROP PROCEDURE

條件陳述式

Ⅰ. if-then -else語句

    DELIMITER      CREATE PROCEDURE proc2(IN parameter int)           begin          declare var int;           set var=parameter+1;           if var=0 then              insert into t values(17);           end if;           if parameter=0 then              update t set s1=s1+1;           else              update t set s1=s1+2;           end if;           end;      DELIMITER ;         

Ⅱ. case語句：

    DELIMITER       CREATE PROCEDURE proc3 (in parameter int)       begin          declare var int;           set var=parameter+1;           case var           when 0 then                insert into t values(17);           when 1 then                 insert into t values(18);            else                 insert into t values(19);            end case;       end;   DELIMITER ;     

迴圈語句

Ⅰ. while ···· end while：

 

mysql預存程序學習筆記