存儲(chǔ)過程是一種存儲(chǔ)在數(shù)據(jù)庫庫中的程序（就像正規(guī)語言里的子程序一樣），準(zhǔn)確的來說，MySql支持的“routine（例程）”有兩種：一是我們說的存儲(chǔ)過程，二是在其它sql語句中可以返回值的函數(shù)（使用起來和mysql預(yù)裝載的函數(shù)一樣，如pi（））。

一個(gè)存儲(chǔ)過程包括名字，參數(shù)列表，以及可以包括很多sql語句的sql語句集。在這里對(duì)局部變量，異常處理，循環(huán)控制和if條件語句有新的語法定義。

下面是一個(gè)包括存儲(chǔ)過程的實(shí)例聲明：


MySQL 版本：5.0.45 phpMyAdmin版本：2.11.3
首先看MySQL 5.0參考手冊(cè)中關(guān)于創(chuàng)建存儲(chǔ)過程的語法說明：
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
如果你對(duì)MySQL還不太熟悉的話，單單看這個(gè)語法結(jié)構(gòu)當(dāng)然不足以進(jìn)行MySQL存儲(chǔ)過程編程。我之前基本都是使用MS SQL SERVER，所以以下記錄我熟悉MySQL存儲(chǔ)過程的過程，也是重點(diǎn)介紹MS SQL SERVER與MySQL區(qū)別較大的地方。
第一步，當(dāng)然是寫個(gè)Hello Word的存儲(chǔ)過程，如下：

將上面創(chuàng)建phelloword存儲(chǔ)過程的語句拷到phpMyAdmin中執(zhí)行，報(bào)如下錯(cuò)誤：
#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
在這個(gè)問題上我糾纏了很久，在MySQL的命令行工具中執(zhí)行同樣不成功，但是根據(jù)提示信息可以知道執(zhí)行在 SELECT 'Hello Word!' AS F;處結(jié)束，后面的END;沒有執(zhí)行，這顯然會(huì)導(dǎo)致錯(cuò)誤。
這里需要選擇以個(gè)分隔符，語法如下：DELIMITER //
分隔符是通知MySQL客戶端已經(jīng)輸入完成的符號(hào)。一直都是用“;”，但是在存儲(chǔ)過程中不行，因?yàn)榇鎯?chǔ)過程中很多語句都需要用到分號(hào)。
因此上面的存儲(chǔ)過程改為：

另外在phpMyAdmin中執(zhí)行時(shí)，在Delimiter文本框中填寫 //，這次存儲(chǔ)過程即可創(chuàng)建成功。
第二步，寫一個(gè)包括參數(shù)，變量，變量賦值，條件判斷，UPDATE語句，SELECT返回結(jié)果集的完整的一個(gè)存儲(chǔ)過程，如下：

首先要說的是給變量賦值的語法，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;這種方式給變量賦值。
其次是條件判斷的語法結(jié)構(gòu)，如下所示：

最后說說LEAVE 語法的使用。當(dāng)滿足某種條件，不繼續(xù)執(zhí)行下面的SQL時(shí)，在MS SQL SERVER中使用RETURN語法，在MySQL中我沒有找到對(duì)應(yīng)的關(guān)鍵字，但是這里可以利用LEAVE語法來滿足要求，在存儲(chǔ)過程的BEGIN前定義一個(gè)標(biāo)簽，如：“LABEL_PROC:” 然后再需要用到RETURN中斷執(zhí)行的地方執(zhí)行“LEAVE LABEL_PROC;”即可。
第三步，創(chuàng)建一個(gè)執(zhí)行動(dòng)態(tài)SQL的存儲(chǔ)過程。

這里提一下 “\”是轉(zhuǎn)義字符，拼接成的SQL類似 SELECT filedir FROM ipsp_resources WHERE hashcode ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1
另外@sql這個(gè)屬于用戶變量，具體用法請(qǐng)查詢MySQL參考手冊(cè)。
如果有在MS SQL SERVER上編寫存儲(chǔ)過程的經(jīng)驗(yàn)的話，看完這些，我想基本的MySQL存儲(chǔ)過程編程應(yīng)該可以應(yīng)付了吧！
想了解更多的內(nèi)容可查詢MySQL參考手冊(cè)或者相關(guān)書籍！

mysql 5.0存儲(chǔ)過程學(xué)習(xí)總結(jié)(更詳細(xì))

一.創(chuàng)建存儲(chǔ)過程
1.基本語法：

create procedure sp_name()
begin
………
end
2.參數(shù)傳遞
二.調(diào)用存儲(chǔ)過程
1.基本語法：call sp_name()
注意：存儲(chǔ)過程名稱后面必須加括號(hào)，哪怕該存儲(chǔ)過程沒有參數(shù)傳遞
三.刪除存儲(chǔ)過程
1.基本語法：
drop procedure sp_name//
2.注意事項(xiàng)
(1)不能在一個(gè)存儲(chǔ)過程中刪除另一個(gè)存儲(chǔ)過程，只能調(diào)用另一個(gè)存儲(chǔ)過程
四.區(qū)塊，條件，循環(huán)

1.區(qū)塊定義，常用
begin
……
end;
也可以給區(qū)塊起別名，如：
lable:begin
………..
end lable;
可以用leave lable;跳出區(qū)塊，執(zhí)行區(qū)塊以后的代碼
2.條件語句

if 條件 then
statement
else
statement
end if;
3.循環(huán)語句
(1).while循環(huán)
[label:] WHILE expression DO
statements
END WHILE [label] ;

(2).loop循環(huán)

[label:] LOOP
statements
END LOOP [label];

(3).repeat until循環(huán)

[label:] REPEAT
statements
UNTIL expression
END REPEAT [label] ;

五.其他常用命令
1.show procedure status
顯示數(shù)據(jù)庫中所有存儲(chǔ)的存儲(chǔ)過程基本信息，包括所屬數(shù)據(jù)庫，存儲(chǔ)過程名稱，創(chuàng)建時(shí)間等
2.show create procedure sp_name
顯示某一個(gè)存儲(chǔ)過程的詳細(xì)信息


mysql存儲(chǔ)過程中要用到的運(yùn)算符
mysql存儲(chǔ)過程學(xué)習(xí)總結(jié)－操作符
算術(shù)運(yùn)算符
+ 加 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
比較運(yùn)算符
> 大于 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
<=> 嚴(yán)格比較兩個(gè)NULL值是否相等 NULL<=>NULL True
LIKE 簡(jiǎn)單模式匹配 "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
邏輯運(yùn)算符
與(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

位運(yùn)算符

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

mysq存儲(chǔ)過程中常用的函數(shù)，字符串類型操作，數(shù)學(xué)類，日期時(shí)間類。

mysql存儲(chǔ)過程基本函數(shù)
一.字符串類

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //連接字串
INSTR (string ,substring ) //返回substring首次在string中出現(xiàn)的位置,不存在返回0
LCASE (string2 ) //轉(zhuǎn)換成小寫
LEFT (string2 ,length ) //從string2中的左邊起取length個(gè)字符
LENGTH (string ) //string長(zhǎng)度
LOAD_FILE (file_name ) //從文件讀取內(nèi)容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定開始位置
LPAD (string2 ,length ,pad ) //重復(fù)用pad加在string開頭,直到字串長(zhǎng)度為length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重復(fù)count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str
RPAD (string2 ,length ,pad) //在str后用pad補(bǔ)充,直到長(zhǎng)度為length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比較兩字串大小,
SUBSTRING (str , position [,length ]) //從str的position開始,取length個(gè)字符,
注：mysql中處理字符串時(shí)，默認(rèn)第一個(gè)字符下標(biāo)為1，即參數(shù)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 ) //轉(zhuǎn)換成大寫
RIGHT(string2,length) //取string2最后length個(gè)字符
SPACE(count) //生成count個(gè)空格

二.數(shù)學(xué)類

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


注：返回類型并非均為整數(shù)，如：
(1)默認(rèn)變?yōu)檎沃?
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)可以設(shè)定小數(shù)位數(shù)，返回浮點(diǎn)型數(shù)據(jù)
mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
| 1.57 |
+—————-+
1 row in set (0.00 sec)

SIGN (number2 ) //返回符號(hào),正負(fù)或0
SQRT(number2) //開平方


三.日期時(shí)間類


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


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

最新評(píng)論