MySQL存儲過程實例教程

MySQL存儲過程實例教程

MySQL 5.0以後的版本開始支持存儲過程，存儲過程具有一致性、高效性、安全性和體系結構等特點，本節將通過具體的實例講解PHP是如何操縱MySQL存儲過程的。

實例261：存儲過程的創建
這是一個創建存儲過程的實例
錄像位置：光盤mingrisoft9?lt;/p>

實例說明

為了保證數據的完整性、一致性，提高應用的性能，常采用存儲過程技術。MySQL 5.0之前的版本並不支持存儲過程，隨著MySQL技術的日趨完善，存儲過程將在以後的項目中得到廣泛的應用。本實例將介紹在MySQL 5.0以後的版本中創建存儲過程。

技術要點

一個存儲過程包括名字、參數列表，以及可以包括很多SQL語句的SQL語句集。下面為一個存儲過程的定義過程： create procedure proc_name (in parameter integer)begindeclare variable varchar(20);if parameter=1 thenset variable='MySQL';elseset variable='PHP';end if;insert into tb (name) values (variable);end;  
MySQL中存儲過程的建立以關鍵字create procedure開始，後面緊跟存儲過程的名稱和參數。MySQL的存儲過程名稱不區分大小寫，例如PROCE1()和proce1()代表同一個存儲過程名。存儲過程名不能與MySQL數據庫中的內建函數重名。

存儲過程的參數一般由3部分組成。第一部分可以是in、out或inout。in表示向存儲過程中傳入參數；out表示向外傳出參數；inout表示定義的參數可傳入存儲過程，並可以被存儲過程修改後傳出存儲過程，存儲過程默認為傳入參數，所以參數in可以省略。第二部分為參數名。第三部分為參數的類型，該類型為MySQL數據庫中所有可用的字段類型，如果有多個參數，參數之間可以用逗號進行分割。

MySQL存儲過程的語句塊以begin開始，以end結束。語句體中可以包含變量的聲明、控制語句、SQL查詢語句等。由於存儲過程內部語句要以分號結束，所以在定義存儲過程前應將語句結束標志“;”更改為其他字符，並且該字符在存儲過程中出現的幾率也應該較低，可以用關鍵字delimiter更改。例如： mysql>delimiter //
存儲過程創建之後，可用如下語句進行刪除，參數proc_name指存儲過程名。 drop procedure proc_name 
實現過程

（1）MySQL存儲過程是在“命令提示符”下創建的，所以首先應該打開“命令提示符”窗口。
（2）進入“命令提示符”窗口後，首先應該登錄MySQL數據庫服務器，在“命令提示符”下輸入如下命令：

mysql –u用戶名 –p用戶密碼 
（3）更改語句結束符號，本實例將語句結束符更改為“//”。代碼如下：

delimiter // 
（4）創建存儲過程前應首先選擇某個數據庫。代碼如下：

use 數據庫名
（5）創建存儲過程。
（6）通過call語句調用存儲過程。

實例的關鍵技術是如何創建傳入參數的存儲過程，具體實現代碼如下： delimiter //create procedure pro_reg (in nc varchar(50), in pwd varchar(50), in email varchar(50),in address varchar(50))begininsert into tb_reg (name, pwd ,email ,address) values (nc, pwd, email, address);end;//    

“delimiter //”的作用是將語句結束符更改為“//”。
“in nc varchar(50)……in address varchar(50)”表示要向存儲過程中傳入的參數。

實現過程

（1）通過PHP預定義類mysqli，實現與MySQL數據庫的連接。代碼如下： $conn=new mysqli("localhost","root","root","db_database09");$conn->query("set names gb2312");   

（2）調用存儲過程pro_reg，實現將用戶錄入的注冊信息保存到數據庫。代碼如下： if($sql=$conn->query("call pro_reg('".$nc."','".$pwd."','".$email."','".$address."')")){echo "<SCRIPT>alert('用戶注冊成功!');</SCRIPT>";}else{echo "<SCRIPT>alert('用戶注冊失敗!');</SCRIPT>";

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

 

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