MySQL 預存程序的函數與基本用法

基本用法

MySQL 預存程序是從 MySQL 5.0 開始逐漸增加新的功能。預存程序在實際應用中也是優點大於缺點。不過最主要的還是執行效率和SQL 代碼封裝。特別是 SQL 代碼封裝功能，如果沒有預存程序。

在外部程式訪問資料庫時（例如 PHP），要組織很多 SQL 陳述式。

特別是商務邏輯複雜的時候，一大堆的 SQL 和條件夾雜在 PHP 代碼中，讓人不寒而慄。現在有了 MySQL 預存程序，商務邏輯可以封裝預存程序中，這樣不僅容易維護，而且執行效率也高。

一、MySQL 建立預存程序

“pr_add” 是個簡單的 MySQL 預存程序，這個MySQL 預存程序有兩個 int 類型的輸入參數 “a”、“b”，返回這兩個參數的和。

代碼如下	複製代碼
drop procedure if exists pr_add;

計算兩個數之和

代碼如下	複製代碼
create procedure pr_add    (    a int,    b int    )    begin    declare c int;    if a is null then    set a = 0;    end if;    if b is null then    set b = 0;    end if;    set c = a + b;    select c as sum;    /*    return c;   不能在 MySQL 預存程序中使用。return 只能出現在函數中。 */    end;

二、調用 MySQL 預存程序

call pr_add(10, 20);  

執行 MySQL 預存程序，預存程序參數為 MySQL 使用者變數。

代碼如下	複製代碼
set @a = 10;    set @b = 20;    call pr_add(@a, @b);

三、MySQL 預存程序特點

建立 MySQL 預存程序的簡單文法為：

代碼如下	複製代碼
create procedure 預存程序名字()    (    [in|out|inout] 參數 datatype    )    begin    MySQL 語句;    end;

MySQL 預存程序參數如果不顯式指定“in”、“out”、“inout”，則預設為“in”。習慣上，對於是“in” 的參數，我們都不會顯式指定。

1. MySQL 預存程序名字後面的“()”是必須的，即使沒有一個參數，也需要“()”

2. MySQL 預存程序參數，不能在參數名稱前加“@”，如：“@a int”。下面的建立預存程序文法在 MySQL 中是錯誤的（在 SQL Server 中是正確的）。 MySQL 預存程序中的變數，不需要在變數名字前加“@”，雖然 MySQL 用戶端使用者變數要加個“@”。

代碼如下	複製代碼
create procedure pr_add    (    @a int, -- 錯誤    b int -- 正確    )

3. MySQL 預存程序的參數不能指定預設值。

4. MySQL 預存程序不需要在 procedure body 前面加 “as”。而 SQL Server 預存程序必須加 “as” 關鍵字。

代碼如下	複製代碼
create procedure pr_add    (    a int,    b int    )    as -- 錯誤，MySQL 不需要 “as”    begin    mysql statement ...;    end;

5. 如果 MySQL 預存程序中包含多條 MySQL 陳述式，則需要 begin end 關鍵字。

代碼如下	複製代碼
create procedure pr_add    (    a int,    b int    )    begin    mysql statement 1 ...;    mysql statement 2 ...;    end;

6. MySQL 預存程序中的每條語句的末尾，都要加上分號 “;”

...   

代碼如下	複製代碼
declare c int;    if a is null then    set a = 0;    end if;    ...    end;

7. MySQL 預存程序中的注釋。

代碼如下	複製代碼
/*    這是個    多行 MySQL 注釋。    */    declare c int; -- 這是單行 MySQL 注釋 （注意 -- 後至少要有一個空格）    if a is null then # 這也是個單行 MySQL 注釋    set a = 0;    end if;    ...    end;

8. 不能在 MySQL 預存程序中使用 “return” 關鍵字。

代碼如下	複製代碼
set c = a + b;    select c as sum;    /*    return c; -- 不能在 MySQL 預存程序中使用。return 只能出現在函數中。    */    end;

9. 調用 MySQL 預存程序時候，需要在過程名字後面加“()”，即使沒有一個參數，也需要“()”

代碼如下	複製代碼
call pr_no_param();

10. 因為 MySQL 預存程序參數沒有預設值，所以在調用 MySQL 預存程序時候，不能省略參數。可以用 null 來替代。

mysql預存程序基本函數

一.字串類

CHARSET(str) //返回字串字元集
CONCAT (string2  [,... ]) //串連字串
INSTR (string ,substring ) //返回substring首次在string中出現的位置,不存在返回0
LCASE (string2 ) //轉換成小寫
LEFT (string2 ,length ) //從string2中的左邊起取length個字元
LENGTH (string ) //string長度
LOAD_FILE (file_name ) //從檔案讀取內容
LOCATE (substring , string  [,start_position ] ) 同INSTR,但可指定開始位置
LPAD (string2 ,length ,pad ) //重複用pad加在string開頭,直到字串長度為length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重複count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str
RPAD (string2 ,length ,pad) //在str後用pad補充,直到長度為length
RTRIM (string2 ) //去除後端空格
STRCMP (string1 ,string2 ) //逐字元比較兩字串大小,
SUBSTRING (str , position  [,length ]) //從str的position開始,取length個字元,
註：mysql中處理字串時，預設第一個字元下標為1，即參數position必須大於等於1

代碼如下

複製代碼

mysql> select substring('abcd',0,2); +-----------------------+ | substring('abcd',0,2) | +-----------------------+ |                       | +-----------------------+ 1 row in set (0.00 sec)   mysql> select substring('abcd',1,2); +-----------------------+ | substring('abcd',1,2) | +-----------------------+ | ab                    | +-----------------------+ 1 row in set (0.02 sec) TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字元 UCASE (string2 ) //轉換成大寫 RIGHT(string2,length) //取string2最後length個字元 SPACE(count) //產生count個空格

二.數學類

ABS (number2 ) //絕對值
BIN (decimal_number ) //十進位轉二進位
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //進位轉換
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小數位元
HEX (DecimalNumber ) //轉十六進位
註：HEX()中可傳入字串，則返回其ASC-11碼，如HEX('DEF')返回4142143
也可以傳入十進位整數，返回其十六進位編碼，如HEX(25)返回19
LEAST (number , number2  [,..]) //求最小值
MOD (numerator ,denominator ) //求餘
POWER (number ,power ) //求指數
RAND([seed]) //隨機數
ROUND (number  [,decimals ]) //四捨五入,decimals為小數位元]

註：傳回型別並非均為整數，如：
(1)預設變為整形值

代碼如下	複製代碼
mysql> select round(1.23); +-------------+ | round(1.23) | +-------------+ |           1 | +-------------+ 1 row in set (0.00 sec) mysql> select round(1.56); +-------------+ | round(1.56) | +-------------+ |           2 | +-------------+ 1 row in set (0.00 sec)

(2)可以設定小數位元，返回浮點型資料

代碼如下	複製代碼
mysql> select round(1.567,2); +----------------+ | round(1.567,2) | +----------------+ |           1.57 | +----------------+ 1 row in set (0.00 sec) SIGN (number2 ) //返回符號,正負或0 SQRT(number2) //開平方

三.日期時間類
 

ADDTIME (date2 ,time_interval ) //將time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉換時區
CURRENT_DATE (  ) //當前日期
CURRENT_TIME (  ) //目前時間
CURRENT_TIMESTAMP (  ) //目前時間戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或時間
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式顯示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上減去一個時間
DATEDIFF (date1 ,date2 ) //兩個日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1為星期天
DAYOFYEAR (date ) //一年中的第幾天
EXTRACT (interval_name  FROM date ) //從date中提取日期的指定部分
MAKEDATE (year ,day ) //給出年及年中的第幾天,產生日期串
MAKETIME (hour ,minute ,second ) //產生時間串
MONTHNAME (date ) //英文月份名
NOW (  ) //目前時間
SEC_TO_TIME (seconds ) //秒數轉成時間
STR_TO_DATE (string ,format ) //字串轉成時間,以format格式顯示
TIMEDIFF (datetime1 ,datetime2 ) //兩個時間差
TIME_TO_SEC (time ) //時間轉秒數]
WEEK (date_time [,start_of_week ]) //第幾周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第幾天
HOUR(datetime) //小時
LAST_DAY(date) //date的月的最後日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分

mysql預存程序總結

核心提示： 一.建立預存程序1.基本文法：

代碼如下	複製代碼
create procedure sp_name() begin

.........
end2.參數傳遞二.調用預存程序1.基本文法：call sp_name()
注意：預存程序名稱後面必須加括弧，哪怕該

 一.建立預存程序

1.基本文法：

代碼如下	複製代碼
create procedure sp_name() begin ......... end

2.參數傳遞
二.調用預存程序

1.基本文法：call sp_name()
注意：預存程序名稱後面必須加括弧，哪怕該預存程序沒有參數傳遞
三.刪除預存程序

1.基本文法：

代碼如下	複製代碼
drop procedure sp_name//

2.注意事項
(1)不能在一個預存程序中刪除另一個預存程序，只能調用另一個預存程序
四.區塊，條件，迴圈

1.區塊定義，常用
begin
......
end;
也可以給區塊起別名，如：
lable:begin
...........
end lable;
可以用leave lable;跳出區塊，執行區塊以後的代碼
2.條件陳述式

代碼如下	複製代碼
if 條件 then statement else statement end if;

3.迴圈語句
(1).while迴圈

代碼如下	複製代碼
[label:] WHILE expression DO statements END WHILE [label] ;

(2).loop迴圈

[label:]

代碼如下	複製代碼
LOOP statements END LOOP [label];(3).repeat until迴圈 [label:] REPEAT statements UNTIL expression END REPEAT [label] ;

五.其他常用命令

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