MySql的預存程序學習小結 附pdf文檔下載

預存程序是一種儲存在資料庫庫中的程式（就像正規語言裡的子程式一樣），準確的來說，MySql支援的“routine（常式）”有兩種：一是我們說的預存程序，二是在其它sql語句中可以傳回值的函數（使用起來和mysql預裝載的函數一樣，如pi（））。

一個預存程序包括名字，參數列表，以及可以包括很多sql語句的sql語句集。在這裡對局部變數，異常處理，迴圈控制和if條件陳述式有新的文法定義。

下面是一個包括預存程序的執行個體聲明： 複製代碼 代碼如下:CREATE PROCEDURE procedurel /*name 預存程序名*/
(IN parameter1 INTEGER) /*parameters 參數*/
BEGIN /*start of blokc 語句塊頭*/
DECLARE variable1 CHAR(10); /*variables變數聲明*/
IF parameter1 = 17 THEN /*start of IF IF條件開始*/
SET variable1 = 'birds'; /*assignment賦值*/
END IF; /*end of IF IF結束*/
INSERT INTO table1 VALUES (variable1);/*statement SQL語句*/
END /*end of block 語句塊結束*/

MySQL 版本：5.0.45 phpMyAdmin版本：2.11.3
首先看MySQL 5.0參考手冊中關於建立預存程序的文法說明：
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement
如果你對MySQL還不太熟悉的話，單單看這個文法結構當然不足以進行MySQL預存程序編程。我之前基本都是使用MS SQL SERVER，所以以下記錄我熟悉MySQL預存程序的過程，也是重點介紹MS SQL SERVER與MySQL區別較大的地方。
第一步，當然是寫個Hello Word的預存程序，如下： 複製代碼 代碼如下:CREATE PROCEDURE phelloword()
BEGIN
SELECT 'Hello Word!' AS F;
END;

將上面建立phelloword預存程序的語句拷到phpMyAdmin中執行，報如下錯誤：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
在這個問題上我糾纏了很久，在MySQL的命令列工具中執行同樣不成功，但是根據提示資訊可以知道執行在 SELECT 'Hello Word!' AS F;處結束，後面的END;沒有執行，這顯然會導致錯誤。
這裡需要選擇以個分隔字元，文法如下：DELIMITER //
分隔字元是通知MySQL用戶端已經輸入完成的符號。一直都是用“;”，但是在預存程序中不行，因為預存程序中很多語句都需要用到分號。
因此上面的預存程序改為： 複製代碼 代碼如下:CREATE PROCEDURE ptest()
BEGIN
SELECT 'Hello Word!' AS F;
END //

另外在phpMyAdmin中執行時，在Delimiter文字框中填寫 //，這次預存程序即可建立成功。
第二步，寫一個包括參數，變數，變數賦值，條件判斷，UPDATE語句，SELECT返回結果集的完整的一個預存程序，如下： 複製代碼 代碼如下:CREATE PROCEDURE plogin
(
p_username char(15),
p_password char(32),
p_ip char(18),
p_logintime datetime
)
LABEL_PROC:
BEGIN
DECLARE v_uid mediumint(8);
DECLARE v_realpassword char(32);
DECLARE v_nickname varchar(30);
DECLARE v_oltime smallint(6);
SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime
FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;
IF (v_uid IS NULL) THEN
SELECT 2 AS ErrorCode;
LEAVE LABEL_PROC;
END IF;
IF (p_password <> v_realpassword) THEN
SELECT 3 AS ErrorCode;
LEAVE LABEL_PROC;
END IF;
UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;
SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;
END LABEL_PROC //

首先要說的是給變數賦值的文法，MySQL中使用SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;這種方式給變數賦值。
其次是條件判斷的文法結構，如下所示： 複製代碼 代碼如下:IF ... THEN
...;
ELSE
IF ... THEN
...;
ELSEIF
...;
ELSE
...;
END IF;
END IF;

最後說說LEAVE 文法的使用。當滿足某種條件，不繼續執行下面的SQL時，在MS SQL SERVER中使用RETURN文法，在MySQL中我沒有找到對應的關鍵字，但是這裡可以利用LEAVE文法來滿足要求，在預存程序的BEGIN前定義一個標籤，如：“LABEL_PROC:” 然後再需要用到RETURN中斷執行的地方執行“LEAVE LABEL_PROC;”即可。
第三步，建立一個執行動態SQL的預存程序。 複製代碼 代碼如下:CREATE PROCEDURE ipsp_getresourcedir
(
p_hashcode char(40)
)
LABEL_PROC:
BEGIN
DECLARE v_sql varchar(200);
SET v_sql = CONCAT('SELECT filedir FROM ipsp_resources WHERE hashcode =\'', p_hashcode, '\' LIMIT 0, 1');
SET @sql = v_sql;
PREPARE sl FROM @sql;
EXECUTE sl;
DEALLOCATE PREPARE sl;
END LABEL_PROC //

這裡提一下 “\”是逸出字元，拼接成的SQL類似 SELECT filedir FROM ipsp_resources WHERE hashcode ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1
另外@sql這個屬於使用者變數，具體用法請查詢MySQL參考手冊。
如果有在MS SQL SERVER上編寫預存程序的經驗的話，看完這些，我想基本的MySQL預存程序編程應該可以應付了吧！
想瞭解更多的內容可查詢MySQL參考手冊或者相關書籍！

mysql 5.0預存程序學習總結(更詳細)

一.建立預存程序
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
顯示某一個預存程序的詳細資料

mysql預存程序中要用到的運算子
mysql預存程序學習總結－操作符
算術運算子
+ 加 SET var1=2+2; 4
- 減 SET var2=3-2; 1
* 乘 SET var3=3*2; 6
/ 除 SET var4=10/3; 3.3333
DIV 整除 SET var5=10 DIV 3; 3
% 模數 SET var6=10%3 ; 1
比較子
> 大於 1>2 False
< 小於 2<1 False
<= 小於等於 2<=2 True
>= 大於等於 3>=2 True
BETWEEN 在兩值之間 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在兩值之間 5 NOT BETWEEN 1 AND 10 False
IN 在集合中 5 IN (1,2,3,4) False
NOT IN 不在集合中 5 NOT IN (1,2,3,4) True
= 等於 2=3 False
<>, != 不等於 2<>3 False
<=> 嚴格比較兩個NULL值是否相等 NULL<=>NULL True
LIKE 簡單模式比對 "Guy Harrison" LIKE "Guy%" True
REGEXP 正則式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL 為空白 0 IS NULL False
IS NOT NULL 不為空白 0 IS NOT NULL True
邏輯運算子
與(AND)

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	NULL
NULL	NULL	NULL	NULL

或(OR)

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

異或(XOR)

XOR	TRUE	FALSE	NULL
TRUE	FALSE	TRUE	NULL
FALSE	TRUE	FALSE	NULL
NULL	NULL	NULL	NULL

位元運算符

| 位或
& 位與
<< 左移位
>> 右移位
~ 位非(單目運算，按位取反)

mysq預存程序中常用的函數，字串類型操作，數學類，日期時間類。

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) //分

附:可用在INTERVAL中的類型
DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,