程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySql的存儲過程學習小結 附pdf文檔下載

MySql的存儲過程學習小結 附pdf文檔下載

編輯:關於MYSQL數據庫
存儲過程是一種存儲在數據庫庫中的程序(就像正規語言裡的子程序一樣),准確的來說,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 ,
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved