MySQL 創建存儲過程

MySQL 創建存儲過程
“pr_add” 是個簡單的 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 來替代。

call pr_add(10, null);
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

MySQL存儲過程例子，包含事務，參數，嵌套調用，游標，循環等

view plaincopy to clipboardprint?
drop procedure if exists pro_rep_shadow_rs;  
delimiter |  
----------------------------------  
-- rep_shadow_rs  
-- 用來處理信息的增加，更新和刪除  
-- 每次只更新上次以來沒有做過的數據  
-- 根據不同的標志位  
-- 需要一個輸出的參數，  
-- 如果返回為0，則調用失敗，事務回滾  
-- 如果返回為1，調用成功，事務提交  
--  
-- 測試方法  
-- call pro_rep_shadow_rs(@rtn);  
-- select @rtn;  
----------------------------------  
create procedure pro_rep_shadow_rs(out rtn int)  
begin  
    -- 聲明變量，所有的聲明必須在非聲明的語句前面  
    declare iLast_rep_sync_id int default -1;  
    declare iMax_rep_sync_id int default -1;  
    -- 如果出現異常，或自動處理並rollback,但不再通知調用方了  
    -- 如果希望應用獲得異常，需要將下面這一句，以及啟動事務和提交事務的語句

全部去掉  
    declare exit handler for sqlexception rollback;  
    -- 查找上一次的  
    select eid into iLast_rep_sync_id from rep_de_proc_log where

tbl='rep_shadow_rs';  
    -- 如果不存在，則增加一行  
    if iLast_rep_sync_id=-1 then  
      insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs'); 

 
      set iLast_rep_sync_id = 0;  
    end if;  
      
    -- 下一個數字  
    set iLast_rep_sync_id=iLast_rep_sync_id+1;  
    -- 設置默認的返回值為0：失敗  
    set rtn=0;  
      
    -- 啟動事務  
    start transaction;  
    -- 查找最大編號  
    select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;  
    -- 有新數據  
    if iMax_rep_sync_id>=iLast_rep_sync_id then  
        -- 調用  
        call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);  
        -- 更新日志  
        update rep_de_proc_log set

rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';  
    end if;  
      
    -- 運行沒有異常，提交事務  
    commit;  
    -- 設置返回值為1 
    set rtn=1;  
end;  
|  
delimiter ;  
drop procedure if exists pro_rep_shadow_rs_do;  
delimiter |  
---------------------------------  
-- 處理指定編號范圍內的數據  
-- 需要輸入2個參數  
-- last_rep_sync_id 是編號的最小值  
-- max_rep_sync_id 是編號的最大值  
-- 無返回值  
---------------------------------  
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id

int)  
begin  
    declare iRep_operationtype varchar(1);  
    declare iRep_status varchar(1);  
    declare iRep_Sync_id int;  
    declare iId int;  
    -- 這個用於處理游標到達最後一行的情況  
    declare stop int default 0;  
    -- 聲明游標  
    declare cur cursor for select

id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where

rep_sync_id between last_rep_sync_id and max_rep_sync_id;  
    -- 聲明游標的異常處理，設置一個終止標記  
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;  
      
    -- 打開游標  
    open cur;  
      
    -- 讀取一行數據到變量  
    fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;  
    -- 這個就是判斷是否游標已經到達了最後  
    while stop <> 1 do 
        -- 各種判斷  
        if iRep_operationtype='I' then  
            insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs

where rep_sync_id=iRep_sync_id;  
        elseif iRep_operationtype='U' then  
        begin  
            if iRep_status='A' then  
                insert into rs0811 (id,fnbm) select id,fnbm from

rep_shadow_rs where rep_sync_id=iRep_sync_id;  
            elseif iRep_status='B' then  
                delete from rs0811 where id=iId;  
            end if;  
        end;  
        elseif iRep_operationtype='D' then  
            delete from rs0811 where id=iId;  
        end if;   
          
        -- 讀取下一行的數據   
        fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;  
    end while;  -- 循環結束  
    close cur; -- 關閉游標  
 end;  
|