Mysql中的存儲過程詳細教程

有哪些特性

有輸入輸出參數，可以聲明變量，有if/else, case,while等控制語句，通過編寫存儲過程，可以實作複雜的邏輯功能；

函數的普遍特性：子產品化，封裝，代碼複用；

速度快，隻有首次執行需經過編譯和優化步驟，後續被調用可以直接執行，省去以上步驟；

幾點說明：

1、DELIMITER ;;：預設的輸入的結束符為“;”是以遇到“;”MySQL解釋器就認為語句結束可以執行了，但是前面定義了begin，找不到end，就會報錯，可以使用;DELIMITER 來重新定義結束辨別符（這裡定義“;;”為結束辨別符，也就是說隻有執行到“;;”時才認為是語句結束了），不過要注意一點，在最後要記得把它還原回來。

（https://blog.csdn.net/yuxin6866/article/details/52722913）

2、DEFINER：建立者；

3、DECLARE：聲明；

存儲過程弊端

不同資料庫，文法差别很大，移植困難，換了資料庫，需要重新編寫；

不好管理，把過多業務邏輯寫在存儲過程不好維護，不利于分層管理，容易混亂，一般存儲過程适用于個别對性能要求較高的業務，其它的必要性不是很大；

==================正式開始==================

轉自：

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html

記錄MYSQL存儲過程中的關鍵文法：

DELIMITER // 聲明語句結束符，用于區分;

CREATE PROCEDURE demo_in_parameter(IN p_in int) 聲明存儲過程

BEGIN ....END 存儲過程開始和結束符号

SET @p_in=1 變量指派

DECLARE l_int int unsigned default 4000000; 變量定義

什麼是mysql存儲例程?

存儲例程是存儲在資料庫伺服器中的一組sql語句，通過在查詢中調用一個指定的名稱來執行這些sql語句指令。

為什麼要使用mysql存儲過程？

我們都知道應用程式分為兩種，一種是基于web，一種是基于桌面，他們都和資料庫進行互動來完成資料的存取工作。假設現在有一種應用程式包含了這兩種，現在要修改其中的一個查詢sql語句，那麼我們可能要同時修改他們中對應的查詢sql語句，當我們的應用程式很龐大很複雜的時候問題就出現這，不易維護！另外把sql查詢語句放在我們的web程式或桌面中很容易遭到sql注入的破壞。而存儲例程正好可以幫我們解決這些問題。

存儲過程(stored procedure)、存儲例程(store routine)、存儲函數差別

Mysql存儲例程實際包含了存儲過程和存儲函數，它們被統稱為存儲例程。

其中存儲過程主要完成在擷取記錄或插入記錄或更新記錄或删除記錄，即完成select insert deleteupdate等的工作。而存儲函數隻完成查詢的工作，可接受輸入參數并傳回一個結果。

建立mysql存儲過程、存儲函數

create procedure 存儲過程名(參數)

存儲過程體

create function 存儲函數名(參數)

下面是存儲過程的例子：

mysql> DELIMITER //

mysql> CREATE PROCEDURE proc1(OUT sint)

-> BEGIN

-> SELECTCOUNT(*) INTO s FROM user;

-> END

->//

mysql> DELIMITER ;

注：

（1）這裡需要注意的是DELIMITER//和DELIMITER;兩句，DELIMITER是分割符的意思，因為MySQL預設以";"為分隔符，如果我們沒有聲明分割符，那麼編譯器會把存儲過程當成SQL語句進行處理，則存儲過程的編譯過程會報錯，是以要事先用DELIMITER關鍵字申明目前段分隔符，這樣MySQL才會将";"當做存儲過程中的代碼，不會執行這些代碼，用完了之後要把分隔符還原。

（2）存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，這裡有一個輸出參數s，類型是int型，如果有多個參數用","分割開。

（3）過程體的開始與結束使用BEGIN與END進行辨別。

這樣，我們的一個MySQL存儲過程就完成了，是不是很容易呢?看不懂也沒關系，接下來，我們詳細的講解。

(2). 聲明分割符

其實，關于聲明分割符，上面的注解已經寫得很清楚，不需要多說，隻是稍微要注意一點的是：如果是用MySQL的Administrator管理工具時，可以直接建立，不再需要聲明。

(3). 參數

MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存儲過程名([[IN |OUT |INOUT] 參數名資料類形...])

IN 輸入參數:表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被傳回，為預設值

OUT 輸出參數:該值可在存儲過程内部被改變，并可傳回（也就是說調用的時候傳參不起作用，内部擷取不到參數值）

INOUT 輸入輸出參數:調用時指定，并且可被改變和傳回

Ⅰ. IN參數例子

建立:

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)

3.       -> BEGIN

4.       -> SELECT p_in;

5.       -> SET p_in=2;

6.       -> SELECT p_in;

7.       -> END;

8.       -> //

9.  mysql > DELIMITER ;

執行結果:

注意：下面的 SET @p_in=1; 這句其實不需要的，可以直接傳參的，如：CALL demo_in_patameter(1); ，這裡之是以SET是因為後面第15行要輸出@p_in看是否發生變化。

1.  mysql > SET @p_in=1;

2.  mysql > CALL demo_in_parameter(@p_in);

3.  +------+

4.  | p_in |

5.  +------+

6.  |   1  |

7.  +------+

8.

9.  +------+

10.| p_in |

11.+------+

12.|   2  |

13.+------+

14.

15.mysql> SELECT @p_in;

16.+-------+

17.| @p_in |

18.+-------+

19.|  1    |

20.+-------+

以上可以看出，p_in雖然在存儲過程中被修改，但并不影響@p_id的值

Ⅱ.OUT參數例子

建立:

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)

3.       -> BEGIN

4.       -> SELECT p_out;

5.       -> SET p_out=2;

6.       -> SELECT p_out;

7.       -> END;

8.       -> //

9.  mysql > DELIMITER ;

1.  mysql > SET @p_out=1;

2.  mysql > CALL sp_demo_out_parameter(@p_out);

3.  +-------+

4.  | p_out |

5.  +-------+

6.  | NULL  |

7.  +-------+

8.

9.  +-------+

10.| p_out |

11.+-------+

12.|   2   |

13.+-------+

14.

15.mysql> SELECT @p_out;

16.+-------+

17.| p_out |

18.+-------+

19.|   2   |

20.+-------+

Ⅲ. INOUT參數例子

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)

3.       -> BEGIN

4.       -> SELECT p_inout;

5.       -> SET p_inout=2;

6.       -> SELECT p_inout;

7.       -> END;

8.       -> //

9.  mysql > DELIMITER ;

1.  mysql > SET @p_inout=1;

2.  mysql > CALL demo_inout_parameter(@p_inout) ;

3.  +---------+

4.  | p_inout |

5.  +---------+

6.  |    1    |

7.  +---------+

8.

9.  +---------+

10.| p_inout |

11.+---------+

12.|    2    |

13.+---------+

14.

15.mysql > SELECT @p_inout;

16.+----------+

17.| @p_inout |

18.+----------+

19.|    2     |

20.+----------+

(4). 變量

Ⅰ. 變量定義

局部變量聲明一定要放在存儲過程體的開始

DECLAREvariable_name [,variable_name...] datatype [DEFAULTvalue];

其中，datatype為MySQL的資料類型，如:int, float, date,varchar(length)

例如:

1.  DECLARE l_int int unsigned default 4000000;

2.  DECLARE l_numeric number(8,2) DEFAULT 9.95;

3.  DECLARE l_date date DEFAULT '1999-12-31';

4.  DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';

5.  DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

Ⅱ. 變量指派

SET 變量名 = 表達式值 [,variable_name= expression ...]

Ⅲ. 使用者變量

ⅰ. 在MySQL用戶端使用使用者變量

1.  mysql > SELECT 'Hello World' into @x;

2.  mysql > SELECT @x;

3.  +-------------+

4.  |   @x        |

5.  +-------------+

6.  | Hello World |

7.  +-------------+

8.  mysql > SET @y='Goodbye Cruel World';

9.  mysql > SELECT @y;

10.+---------------------+

11.|     @y              |

12.+---------------------+

13.| Goodbye Cruel World |

14.+---------------------+

15.

16.mysql > SET @z=1+2+3;

17.mysql > SELECT @z;

18.+------+

19.| @z   |

20.+------+

21.|  6   |

22.+------+

ⅱ. 在存儲過程中使用使用者變量

1.  mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');

2.  mysql > SET @greeting='Hello';

3.  mysql > CALL GreetWorld( );

4.  +----------------------------+

5.  | CONCAT(@greeting,' World') |

6.  +----------------------------+

7.  |  Hello World               |

8.  +----------------------------+

ⅲ. 在存儲過程間傳遞全局範圍的使用者變量

1.  mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';

2.  mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);

3.  mysql> CALL p1( );

4.  mysql> CALL p2( );

5.  +-----------------------------------------------+

6.  | CONCAT('Last procedure was ',@last_proc       |

7.  +-----------------------------------------------+

8.  | Last procedure was p1                         |

9.  +-----------------------------------------------+

注意:

①使用者變量名一般以@開頭

②濫用使用者變量會導緻程式難以了解及管理

(5). 注釋

MySQL存儲過程可使用兩種風格的注釋

雙模杠：--

該風格一般用于單行注釋

c風格：一般用于多行注釋

例如：

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE proc1 --name存儲過程名

3.       -> (IN parameter1 INTEGER)

4.       -> BEGIN

5.       -> DECLARE variable1 CHAR(10);

6.       -> IF parameter1 = 17 THEN

7.       -> SET variable1 = 'birds';

8.       -> ELSE

9.       -> SET variable1 = 'beasts';

10.     -> END IF;

11.     -> INSERT INTO table1 VALUES (variable1);

12.     -> END

13.     -> //

14.mysql > DELIMITER ;

4. MySQL存儲過程的調用

用call和你過程名以及一個括号，括号裡面根據需要，加入參數，參數包括輸入參數、輸出參數、輸入輸出參數。具體的調用方法可以參看上面的例子。

5. MySQL存儲過程的查詢

我們像知道一個資料庫下面有那些表，我們一般采用showtables;進行檢視。那麼我們要檢視某個資料庫下面的存儲過程，是否也可以采用呢？答案是，我們可以檢視某個資料庫下面的存儲過程，但是是令一鐘方式。

我們可以用

selectname from mysql.proc where db=’資料庫名’;

或者

selectroutine_name from information_schema.routines whereroutine_schema='資料庫名';

showprocedure status where db='資料庫名';

進行查詢。

如果我們想知道，某個存儲過程的詳細，那我們又該怎麼做呢？是不是也可以像操作表一樣用describe 表名進行檢視呢？

答案是：我們可以檢視存儲過程的詳細，但是需要用另一種方法：

SHOWCREATE PROCEDURE 資料庫.存儲過程名;

就可以檢視目前存儲過程的詳細。

6. MySQL存儲過程的修改

ALTER PROCEDURE

更改用CREATEPROCEDURE 建立的預先指定的存儲過程，其不會影響相關存儲過程或存儲功能。

7. MySQL存儲過程的删除

删除一個存儲過程比較簡單，和删除表一樣：

DROPPROCEDURE

從MySQL的表格中删除一個或多個存儲過程。

8. MySQL存儲過程的控制語句

(1). 變量作用域

内部的變量在其作用域範圍内享有更高的優先權，當執行到end。變量時，内部變量消失，此時已經在其作用域外，變量不再可見了，應為在存儲

過程外再也不能找到這個申明的變量，但是你可以通過out參數或者将其值指派

給會話變量來儲存其值。

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE proc3()

3.       -> begin

4.       -> declare x1 varchar(5) default 'outer';

5.       -> begin

6.       -> declare x1 varchar(5) default 'inner';

7.       -> select x1;

8.       -> end;

9.       -> select x1;

10.     -> end;

11.     -> //

12.mysql > DELIMITER ;

(2). 條件語句

Ⅰ. if-then -else語句

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE proc2(IN parameter int)

3.       -> begin

4.       -> declare var int;

5.       -> set var=parameter+1;

6.       -> if var=0 then

7.       -> insert into t values(17);

8.       -> end if;

9.       -> if parameter=0 then

10.     -> update t set s1=s1+1;

11.     -> else

12.     -> update t set s1=s1+2;

13.     -> end if;

14.     -> end;

15.     -> //

16.mysql > DELIMITER ;

Ⅱ. case語句：

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE proc3 (in parameter int)

3.       -> begin

4.       -> declare var int;

5.       -> set var=parameter+1;

6.       -> case var

7.       -> when 0 then

8.       -> insert into t values(17);

9.       -> when 1 then

10.     -> insert into t values(18);

11.     -> else

12.     -> insert into t values(19);

13.     -> end case;

14.     -> end;

15.     -> //

16.mysql > DELIMITER ;

case

        when var=0 then

               insert into t values(30);

        when var>0 then

        when var<0 then

        else

end case

(3). 循環語句

Ⅰ. while ···· end while：

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE proc4()

3.       -> begin

4.       -> declare var int;

5.       -> set var=0;

6.       -> while var<6 do

7.       -> insert into t values(var);

8.       -> set var=var+1;

9.       -> end while;

10.     -> end;

11.     -> //

12.mysql > DELIMITER ;

while條件 do

--循環體

endwhile

Ⅱ. repeat···· end repeat：

它在執行操作後檢查結果，而while則是執行前進行檢查。

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE proc5 ()

3.       -> begin

4.       -> declare v int;

5.       -> set v=0;

6.       -> repeat

7.       -> insert into t values(v);

8.       -> set v=v+1;

9.       -> until v>=5

10.     -> end repeat;

11.     -> end;

12.     -> //

13.mysql > DELIMITER ;

repeat

until循環條件

endrepeat;

Ⅲ. loop ·····endloop:

loop循環不需要初始條件，這點和while 循環相似，同時和repeat循環一樣不需要結束條件,leave語句的意義是離開循環。

1.  mysql > DELIMITER //

2.  mysql > CREATE PROCEDURE proc6 ()

3.       -> begin

4.       -> declare v int;

5.       -> set v=0;

6.       -> LOOP_LABLE:loop

7.       -> insert into t values(v);

8.       -> set v=v+1;

9.       -> if v >=5 then

10.     -> leave LOOP_LABLE;

11.     -> end if;

12.     -> end loop;

13.     -> end;

14.     -> //

15.mysql > DELIMITER ;

Ⅳ. LABLES 标号：

标号可以用在begin repeatwhile 或者loop 語句前，語句标号隻能在合法的語句前面使用。可以跳出循環，使運作指令達到複合語句的最後一步。

(4). ITERATE疊代

Ⅰ. ITERATE:

1.    通過引用複合語句的标号,來從新開始複合語句

2.  mysql > DELIMITER //

3.  mysql > CREATE PROCEDURE proc10 ()

4.       -> begin

5.       -> declare v int;

6.       -> set v=0;

7.       -> LOOP_LABLE:loop

8.      -> if v=3 then

9.      -> set v=v+1;

10.        -> ITERATE LOOP_LABLE;

11.        -> end if;

12.     -> insert into t values(v);

13.     -> set v=v+1;

14.       -> if v>=5 then

15.        -> leave LOOP_LABLE;

16.     -> end if;

17.     -> end loop;

18.     -> end;

19.     -> //

20.mysql > DELIMITER ;

9. MySQL存儲過程的基本函數

(1).字元串類

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

1.  mysql> select substring('abcd',0,2);

2.  +-----------------------+

3.  | substring('abcd',0,2) |

4.  +-----------------------+

5.  |                       |

6.  +-----------------------+

7.  1 row in set (0.00 sec)

8.

9.  mysql> select substring('abcd',1,2);

10.+-----------------------+

11.| substring('abcd',1,2) |

12.+-----------------------+

13.|     ab                |

14.+-----------------------+

15.1 row in set (0.02 sec)

TRIM([[BOTH|LEADING|TRAILING][padding] FROM]string2)//去除指定位置的指定字元

UCASE (string2 ) //轉換成大寫

RIGHT(string2,length) //取string2最後length個字元

SPACE(count) //生成count個空格

(2).數學類

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)預設變為整形值

1.  mysql> select round(1.23);

2.  +-------------+

3.  | round(1.23) |

4.  +-------------+

5.  |           1 |

6.  +-------------+

7.  1 row in set (0.00 sec)

8.

9.  mysql> select round(1.56);

10.+-------------+

11.| round(1.56) |

12.+-------------+

13.|           2 |

14.+-------------+

15.1 row in set (0.00 sec)

(2)可以設定小數位數，傳回浮點型資料

1.  mysql> select round(1.567,2);

2.  +----------------+

3.  | round(1.567,2) |

4.  +----------------+

5.  |           1.57 |

6.  +----------------+

7.  1 row in set (0.00 sec)

SIGN (number2 ) //

(3).日期時間類

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) //分傳回符号,正負或0

SQRT(number2) //開平方

MySql分頁存儲過程

MySql測試版本：5.0.41-community-nt

DROP PROCEDURE IF EXISTS pr_pager;

CREATE PROCEDURE pr_pager(

IN p_table_name VARCHAR(1024),

IN p_fields VARCHAR(1024),

IN p_page_size INT,

IN p_page_now INT,

IN p_order_string VARCHAR(128),

IN p_where_string VARCHAR(1024),

OUT p_out_rows INT

)

NOTDETERMINISTIC

SQL SECURITY DEFINER

Mysql中的存儲過程詳細教程Mysql中的存儲過程詳細教程有哪些特性存儲過程弊端

Mysql中的存儲過程詳細教程

有哪些特性

存儲過程弊端

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Mysql中的存儲過程詳細教程Mysql中的存儲過程詳細教程有哪些特性 存儲過程弊端

Mysql中的存儲過程詳細教程

有哪些特性

存儲過程弊端

繼續閱讀

Mysql中的存儲過程詳細教程Mysql中的存儲過程詳細教程有哪些特性存儲過程弊端