程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> MYSQL教程:學習MYSQL存儲過程

MYSQL教程:學習MYSQL存儲過程

編輯:更多數據庫知識

 一、 簡介

    存儲過程(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)。

create procedure pr_param_in(   in id int -- in 類型的 MySQL 存儲過程參數)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> 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)。

MySQL 存儲過程 “out” 參數:從存儲過程內部傳值給調用者。在存儲過程內部,該參數初始值為 null,無論調用者是否給存儲過程參數設置值。

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)傳回給調用者。

MySQL 存儲過程 inout 參數跟 out 類似,都可以從存儲過程內部傳值給調用者。不同的是:調用者還可以通過 inout 參數傳遞值給存儲過程。

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>mysql> call pr_param_inout(@id);+------------+| id_inner_1 |+------------+|         10 |+------------+ +------------+| id_inner_2 |+------------+|         11 |+------------+ +------------+| id_inner_3 |+------------+|         11 |+------------+mysql>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、條件語句

˜ IF-ELSEIF-ELSE語句

 和C語言中if語句相似。

其原型如下:

IF condition1 THEN

statement1;

ELSEIF condition2 THEN

Statement2;

…….

END IF

˜ CASE語句

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

其原型如下:

CASE

WHEN condition1 THEN statement1;

WHEN condition2 THEN statement2;

………

END CASE;

3、迭代

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

˜ ITERATE語句

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

其原型如下:

ITERATE  label

˜ LEAVE語句

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

其原型如下:

LEAVE  label

˜ LOOP語句

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

其原型如下:

[begin_label:]  LOOP

   Statement_list

END LOOP  [end_label]

      ˜ REPEAT語句

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

      其原型如下:

 REPEAT

       Statement_list

 UNTIL condition

 END REPEAT

˜ 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<num do          /*while……do循環語句*/
         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 ;

                             

  l 調用存儲過程

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved