mysql 存儲過程

mysql 存儲過程"/>

mysql 存儲過程

MYSQL存儲過程

原文：.html

一、 簡介

存儲過程(Stored Procedure)是一組為了完成特定功能的SQL語句集，經編譯後存儲在資料庫中。用戶通過指定存儲過程的名字並給出參數(如果該存儲過程帶有參數)來執行它。存儲過程是資料庫中的一個重要對象，任何一個設計良好的資料庫應用程序都應該用到存儲過程。

雖然常用術語是存儲過程(stored procedure)，但MySQL實際上實現了兩中類型，除了存儲過程外，還有存儲函數(stored routine),它們統稱為存儲常式。

二、基本格式

1、存儲過程

CREATE PROCEDURE 過程名 ([過程參數[,...]])

[特性 ...] 過程體

如創建:

CEATE PROCEDURE p1 (a INT)

SELECT a;

調用一下：

CALL p1(8);

將顯示：

+------+

| a      |

+------+

|    8   |

+------+

1 row in set (0.00 sec)

2、存儲函數

CREATE FUNCTION 函數名 ([函數參數[,...]])

RETURNS 返回類型

[特性 ...] 函數體

如創建：

CREATE FUNCTION f1 (x INT)

RETURNS INT

RETURN x;

/* 過程函數一次只能返回一個值 */

調用一下：

SELECT f1 (3);

將顯示：

+-------+

| f1(3) |

+-------+

|     3 |

+-------+

1 row in set (0.00 sec)

3、過程參數

[ IN | OUT | INOUT ] 參數名 參數類型

4、函數參數

參數名 參數類型

5、返回類型

有效的MySQL數據類型即可

6、過程體/函數體

格式如下:

BEGIN

有效的SQL語句

END

7、特性(一般不要求)

LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

8、存儲過程中參數的IN,OUT,INOUT類型

存儲過程可以接受輸入參數，並把參數返回給調用方。不過，對於每個參數，需要聲明其參數名、數據類型，還要指定此參數是用於向過程傳遞信息，還是從過程傳回信息，或是二者兼有。

為聲明參數的作用，使用如下3個關鍵字之一：

-- IN : IN參數只用來向過程傳遞信息，為默認值。

-- OUT : OUT參數只用來從過程傳回信息。

-- INOUT ： INOUT參數可以向過程傳遞信息，如果值改變，則可再從過程外調用。

如果僅僅想把數據傳給 MySQL 存儲過程，那就使用「in」 類型參數；如果僅僅從 MySQL 存儲過程返回值，那就使用「out」 類型參數；如果需要把數據傳給 MySQL 存儲過程，還要經過一些計算後再傳回給我們，此時，要使用「inout」 類型參數。

對於任何聲明為OUT或INOUT的參數，，當調用存儲過程時需要在參數名前加上@符號，這樣該參數就可以在過程外調用了。

下面舉三個實例：MySQL 存儲過程 「in」 參數：跟 C 語言的函數參數的值傳遞類似， MySQL 存儲過程內部可能會修改此參數，但對 in 類型參數的修改，對調用者(caller)來說是不可見的(not visible)。

1) in 類型的 MySQL 存儲過程參數:

create procedure pr_param_in( in id int )

begin

if (id is not null) then

set id = id + 1;

end if;

select id as id_inner;

end;

set @id = 10;

call pr_param_in(@id);

select @id as id_out;

mysql> set @id = 10;

mysql> call pr_param_in(@id);

+----------+

| id_inner |

+----------+

|       11 |

+----------+

mysql> select @id as id_out;

+--------+

| id_out |

+--------+

| 10     |

+--------+

可以看到：用戶變數 @id 傳入值為 10，執行存儲過程後，在過程內部值為：11(id_inner)，但外部變數值依舊為：10(id_out)。

2) out 類型的 MySQL 存儲過程參數:

create procedure pr_param_out( out id int )

begin

select id as id_inner_1;

/* id 初始值為 null*/

if (id is not null) then

set id = id + 1;

select id as id_inner_2;

else

select 1 into id;

end if;

select id as id_inner_3;

end;

set @id = 10;

call pr_param_out(@id);

select @id as id_out;

mysql> set @id = 10;

mysql>mysql> call pr_param_out(@id);

/*外部變數不能傳給存儲過程*/

+------------+

| id_inner_1 |

+------------+

|       NULL |

+------------+

+------------+

| id_inner_3 |

+------------+

|          1 |

+------------+

mysql> select @id as id_out;

/*過程將改變的值傳給外部變數*/

+--------+

| id_out |

+--------+

| 1      |

+--------+

可以看出，雖然我們設置了用戶定義變數 @id 為 10，傳遞 @id 給存儲過程後，在存儲過程內部，id 的初始值總是 null(id_inner_1)。最後 id 值(id_out = 1)傳回給調用者。

3) inout 類型的 MySQL 存儲過程參數:

drop procedure if exists pr_param_inout;

create procedure pr_param_inout( inout id int )

begin

select id as id_inner_1;

-- id 值為調用者傳進來的值

if (id is not null) then

set id = id + 1;

select id as id_inner_2;

else

select 1 into id;

end if;

select id as id_inner_3;

end;

set @id = 10;

call pr_param_inout(@id);

select @id as id_out;

mysql> set @id = 10;

mysql> call pr_param_inout(@id);

+------------+

| id_inner_1 |

+------------+

|         10 |

+------------+

+------------+

| id_inner_2 |

+------------+

|         11 |

+------------+

+------------+

| id_inner_3 |

+------------+

|         11 |

+------------+

mysql> select @id as id_out;

+--------+

| id_out |

+--------+

| 11     |

+--------+

從結果可以看出：我們把 @id(10)，傳給存儲過程後，存儲過程最後又把計算結果值 11(id_inner_3)傳回給調用者。 MySQL 存儲過程 inout 參數的行為跟 C 語言函數中的引用傳值類似。

通過以上例子：如果僅僅想把數據傳給 MySQL 存儲過程，那就使用「in」 類型參數；如果僅僅從 MySQL 存儲過程返回值，那就使用「out」 類型參數；如果需要把數據傳給 MySQL 存儲過程，還要經過一些計算後再傳回給我們，此時，要使用「inout」 類型參數。

9、聲明和設置變數

(1) 聲明變數

在存儲常式使用局部變數之前，必須聲明局部變數，變數聲明通過DECLARE語句實現，其原型如下：

DECLARE variable_name TYPE ;

如：DECLARE x VARCHAR(254);

在聲明變數時，聲明必須放在BEGIN/END塊中。此外聲明必須在執行該塊任何其它語句之前進行。

(2) 設置變數

SET語句用來設置聲明的存儲常式變數值。其原型如下：

SET variable_name =value;

如下聲明和設置變數過程：

DECLARE x INT;

SET x = 155;

也可使用SELECT…..INTO語句設置變數。

如：

SELECT 155 INTO x;

當然，此變數是聲明該變數的BEGIN/END塊作用範圍內的一個局部變數。如果希望在存儲常式外使用此變數，需要將其作為OUT變數傳遞。

10、執行存儲常式

執行存儲常式一般是通過CALL和SELECT語句來完成的。

三、多語句存儲常式

單語句存儲常式非常有用，但存儲常式的真正功能在於它能夠封裝和執行多個SQL語句。

下面介紹創建多語句存儲常式時常用的語法。

1、BEGIN和END

當創建多語句存儲常式時，需要將語句包圍在BEGIN/END塊中。

此塊的原型如下：

BEGIN

statement 1;

statement 2;

……

statement N;

END

注意，塊中每條語句必須以分號結尾。

2、條件語句

1)IF-ELSEIF-ELSE語句

和C語言中if語句相似。

其原型如下：

IF condition1 THEN

statement1;

ELSEIF condition2 THEN

Statement2;

…….

END IF

2)CASE語句

需要比較一組可能的值時CASE語句很有用。也是一種條件判斷語句。

其原型如下：

CASE

WHEN condition1 THEN statement1;

WHEN condition2 THEN statement2;

………

END CASE;

3、迭代

有些任務需要能夠重複地執行一組語句。下面介紹能夠迭代執行和退出循環的各種方法。

1)ITERATE語句

執行ITERATE語句將使嵌入該語句的LOOP、REPEAT或WHILE循環返回頂部，並在此執行。

其原型如下：

ITERATE label

2)LEAVE語句

在得到變數的值或特定任務的結果後，可能希望通過LEAVE命令立即退出循環或BEGIN/END塊。

其原型如下：

LEAVE label

3)LOOP語句

LOOP語句將不斷的迭代處理定義在其代碼塊中的一組語句，直到遇到LEAVE為止。

其原型如下：

[begin_label:] LOOP

Statement_list

END LOOP [end_label]

4)REPEAT語句

REPEAT語句在操作上幾乎與WHILE相同，很想C語言中的DO….WHERE語句。

其原型如下：

REPEAT

Statement_list

UNTIL condition

END REPEAT

5)WHILE語句

其原型如下：

WHILE condition DO

Statement_list

END WHILE

下面寫一個循環語句的存儲過程：

DELIMITER $$

DROP PROCEDURE IF EXISTS `yyw`.`p2` $$

CREATE PROCEDURE `yyw`.`p2` ()

BEGIN

declare v int;

set v=0;

LOOP_LABLE:loop

if v=3 then

set v=v+1;

ITERATE LOOP_LABLE;

end if;

insert into vs values(v);          /*將循環值插入數據表vs中*/

set v=v+1;

if v>=5 then

leave LOOP_LABLE;

end if;

end loop;

END $$

DELIMITER ;

四、從另一個常式中調用常式

DELIMITER //

CREATE PROCEDURE p1()

BEGIN

Statement_list

END//

CREATE PROCEDURE p2()

BEGIN

Statement_list

END//

CREATE PROCEDURE p3()

BEGIN

CALL p1();

CALL p2();

END//

注意：直接採用MySQL的Administrator管理器編輯時，可以直接採用函數文本錄入；

但若在腳本中自動導入存儲過程或函數時，由於MySQL默認以";"為分隔符，則過程體的每一句都被MySQL以存儲過程編譯，則編譯過程會報錯；所以要事先用DELIMITER關鍵字申明當前段分隔符

用完了就把分隔符還原。 如下所示：

DELIMITER ＄＄

Stored Procedures and Functions

DELIMITER ;

五、刪除和查看存儲常式

1、刪除存儲常式

要刪除存儲常式，可以執行DROP語句。

其原型如下：

DROP (PROCEDURE|FUNCTION) P_name;

2、查看常式狀態

其原型如下：

SHOW (PROCEDURE|FUNCTION) STATUS LIKE 『P_name』

如：

SHOW PROCEDURE STATUS LIKE 『P3』/G；

注意: 使用/G選項以垂直格式顯示輸出信息。

3、查看常式的創建語法

通過SHOW CREATE語句可以查看創建特定常式所用的語法。

其原型如下;

SHOW CREATE (PROCEDURE|FUNCTION) Db_name.P_name

六、實例

一般在MySQL Query Browser中更方便的創建存儲過程及修改內容。

(1)簡單的加法運算

DELIMITER $$

DROP PROCEDURE IF EXISTS `yyw`.`p4` $$

CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE `p4`(a int,b int)

BEGIN

declare c int;             /*聲明的變數

if a is null then            /*IF語句

set a = 0;

end if;

if b is null then

set b = 0;

end if;

set c = a + b;

select c as sum;          /*結果顯示c的值

END $$

DELIMITER ;

調用一下：

CALL p4(3,4);

將顯示：

+------+

| sum |

+------+

|    7 |

+------+

1 row in set (0.00 sec)

(2)存儲過程中的循環語句、數據表數據的導入導出及SQL函數的使用

DELIMITER $$

DROP PROCEDURE IF EXISTS `yyw`.`p4` $$

CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE 『pro_prime2』(in num int)

BEGIN

declare i,j,x,y int default 0;    /*聲明變數並默認為0*/

select yywID into j from text1; /*從數據表txte1中欄位yywID的值賦給變數j*/

select 'count ',j;           /*顯示count字元和j的值*/

while i

set x=2;

pp1:while x<=sqrt(j) do    /*調用內部函數SQRT用於求平方*/

if j%x=0 then               /*if循環語句*/

set y=1;

leave pp1;

else

set x=x+1;

end if;

end while;

if y=1 then

set y=0;

else

set i=i+1;

insert into text2 values(j);    /*將j的值插入數據表text2*/

end if;

set j=j+1;            /*實現j的自增*/

end while;

END $$

DELIMITER ;

假如原先在資料庫中分別建立表text1和text2，text1中有一個欄位初始值為3，text2為空；

下面執行一下此存儲過程：

mysql> CALL pro_prime2(5);

+--------+------+

| count | j    |

+--------+------+

| count |    3 |

+--------+------+

1 row in set (0.00 sec)

mysql> select *from text2;

+-------+

| yywID |

+-------+

|     3 |

|     5 |

|     7 |

|    11 |

|    13 |

+-------+

5 rows in set (0.00 sec)

(3)用存儲過程實現計算資料庫中某個成績表總分及平均分，並且調用過程後

能夠自動顯示基本信息(如學號、姓名、總分、平均分等)。

首先在資料庫中建一個成績表(命名為chengjibiao)如下：

+-----+------+--------+-------+--------+

| NUM | Name | Enlish | Maths | Physis |

+-----+------+--------+-------+--------+

|   1 | 楊業 | 92     | 87    | 96     |

|   2 | 劍鋒 | 82     | 98    | 93     |

|   3 | 張美 | 96     | 86    | 94     |

|   4 | 張文 | 76     | 99    | 95     |

|   5 | 葉倩 | 97     | 86    | 88     |

|   6 | 方文 | 87     | 96    | 94     |

|   7 | 李麗 | 97     | 86    | 83     |

|   8 | 賈宇 | 67     | 89    | 77     |

|   9 | 王勃 | 89     | 67    | 75     |

| 10 | 劉三 | 85     | 78    | 95     |

+-----+------+--------+-------+--------+

用SQL語句寫存儲過程p1

DELIMITER $$

DROP PROCEDURE IF EXISTS `yyw`.`p1` $$

CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE `p1`(N int)

BEGIN

declare a int;                  /*    變數的聲明   */

declare b int;

declare c int;

declare d int;

declare e int;

declare f char(100);

declare g decimal(4,2);

set e=1;

create table zongping (NUM int,Name char(255),Enlish int,Maths int,Physis int,Total int,aver decimal(4,2));                     /* 建一個數據表以存放要顯示的內容*/

repeat                 /* 引進一個REPEAT循環，來計算每位學生總分及平均分*/

select Enlish,Maths,Physis,Name into a,b,c,f from chengjibiao where NUM=e;

/* 導出資料庫chengjibiao中的三門成績及姓名，並把它們分別賦給變數a,b,c,f;*/

set d=a+b+c;         /*求和*/

set g=(a+b+c)/3;      /*求平均分*/

insert into zongping (NUM,Name,Enlish,Maths,Physis,Total,aver) values (e,f,a,b,c,d,g);

/*將學號，姓名，三門成績，總分，平均分的數據插入新建的數據表zongping中

set e=e+1;       /*該條件可結束循環*/

until e=N      /*N是調用存儲過程時根據學生數目來設定的*/

end repeat;

select *from zongping;           /* 以數據表的形式顯示運行結果*/

drop table zongping;          /*顯示結果後刪除表，也可不刪*/

END $$

DELIMITER ;

調用存儲過程

CALL P1 (11);

/* 因為原成績表中有10列數據 ，故設N=11，也可根據不同的成績表另設

顯示結果如下：

+------+------+--------+-------+--------+-------+-------+

| Name | Enlish | Maths | Physis | Total | Aver |

+------+------+--------+-------+--------+-------+-------+

|    1 | 楊業 |     92 |    87 |     96 |   275 | 91.67 |

|    2 | 劍鋒 |     82 |    98 |     93 |   273 | 91.00 |

|    3 | 張美 |     96 |    86 |     94 |   276 | 92.00 |

|    4 | 姜文 |     76 |    99 |     95 |   270 | 90.00 |

|    5 | 葉倩 |     97 |    86 |     88 |   271 | 90.33 |

|    6 | 方文 |     87 |    96 |     94 |   277 | 92.33 |

|    7 | 李麗 |     97 |    86 |     83 |   266 | 88.67 |

|    8 | 賈宇 |     67 |    89 |     77 |   233 | 77.67 |

|    9 | 王勃 |     89 |    67 |     75 |   231 | 77.00 |

|   10 | 劉三 |     85 |    78 |     95 |   258 | 86.00 |

+------+------+--------+-------+--------+-------+-------+