mysql------存儲過程和函數

1、建立存儲過程和函數

（1） 建立存儲過程

建立存儲過程，需要使用 CREATE    PROCEDURE 語句 。

文法： CREATE   PROCEDURE

            sp_name   ( [proc_parameter] )   [characteristics ...]

            routine_body

sp_name ：存儲過程的名字；

proc_parameter：為指定存儲過程的參數清單，清單形式如下：[ IN | OUT | INOUT ] param_name   type 。IN表示輸入參數，OUT表示輸出參數，INOUT表示可以輸入也可以輸出，param_name表示參數名稱，  type表示參數類型；

characteristics：指定存儲過程的特性；

routine_body：SQL代碼的内容，可以用begin...end來表示開始和結束

例1：建立檢視fruits表的存儲過程， 每次調用這個存儲過程的時候都會執行SELECT語句檢視表的内容，代碼的執行過程如下

DELIMITER //
 CREATE PROCEDURE Proc()
     BEGIN
     SELECT * FROM fruits;
     END //
Query OK, 0 rows affected (0.00 sec)

 DELIMITER ;
           

delimiter //是将mysql的結束符設定成// ,完畢後delimiter ;恢複預設結束符，當使用DELIMITER指令時，應該避免使用反斜杠（’\’）字元，因為反斜線是MySQL的轉義字元。

例2：建立一個擷取fruits表記錄條數的存儲過程，名稱是CountProc，COUNT(*) 計算後把結果放入參數param1中

DELIMITER // 
 CREATE PROCEDURE CountProc(OUT param1 INT)
  BEGIN
  SELECT COUNT(*) INTO param1 FROM fruits;
  END //
Query OK, 0 rows affected (0.00 sec)
 DELIMITER ;
           

（2） 建立存儲函數

建立存儲過程，需要使用 CREATE   FUNCTION 語句 。 文法：CREATE   FUNCTION      func_name( [func_parameter] )

                 RETURNS    type

                 [characteristic ...]         routine_body

 func_name：存儲函數的名字；

func_parameter：為指定存儲過程的參數清單，清單形式如下：[ IN | OUT | INOUT ] param_name   type 。IN表示輸入參 數，OUT表示輸出參數，INOUT表示可以輸入也可以輸出，param_name表示參數名稱，  type表示參數類型；

RETURNS    type ：表示函數傳回資料的類型；

characteristics：指定存儲函數的特性。

例1：建立存儲函數，名稱為NameByZip，參數為空，該函數傳回SELECT語句的查詢結果，傳回的數值類型為字元串型

CREATE FUNCTION NameByZip ()
 RETURNS CHAR(50)
 RETURN  (SELECT s_name FROM suppliers WHERE s_call= '48075');
           

（3） 變量的使用

A、定義變量

文法：DECLARE    var_name[,varname]…     date_type    [DEFAULT value]；

如果沒有default  子句，初始值為null

例1：定義名稱為myparam的變量，類型為INT類型，預設值為100

DECLARE  myparam  INT  DEFAULT 100;
           

B、為變量指派

文法：SET    var_name = expr [, var_name = expr] ...;

例1：聲明3個變量，分别為var1、var2和var3，資料類型為INT，使用SET為變量指派

DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;
           

MySQL中還可以通過SELECT ... INTO為一個或多個變量指派

文法：SELECT   col_name[,...]    INTO    var_name[,...] table_expr;

這個SELECT文法把標明的列直接存儲到對應位置的變量。

col_name表示字段名稱；var_name表示定義的變量名稱；table_expr表示查詢條件表達式，包括表名稱和WHERE子句。

例2：聲明變量fruitname和fruitprice，通過SELECT ... INTO語句查詢指定記錄并為變量指派

DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT f_name,f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id ='a1';
           

（4）   定義條件和處理程式

A、定義條件 B、定義處理程式

（5） 光标的使用

Mysql的光标隻能在存儲過程和函數中使用

A、聲明光标

建立光智語法：Declare 光标名 custor for  select語句内容

如：daclare cursor_fruit cursor for selectf_name,f_price from fruits;

B、 打開光标

文法：open 光标名;

如：open cusor_fruit;

C、 使用光标

文法：fetch 光标名  into 參數1，參數2……

參數必須在聲明光标之前就定義好，把select查詢出來的資訊存入該參數中。

如：fetch cursor_fruit into fruit_name,fruit_price;

表示将cursor_fruit中select語句查詢出來的資訊存入fruit_name和fruit_price中。

D、 關閉光标

文法：close 光标名

如：close cursor_fruit;

（6） 流程控制的使用 A、IF 語句 B、 CASE 語句 C、 LOOP 語句 D、 LEAVE 語句 E、 ITERATE 語句 F、 REPEAT 語句 G、 WHILE 語句

2、調用存儲過程和函數

（1） 調用存儲過程

存儲過程的調用是通過 CALL 語句進行調用的 。 文法：CALL    sp_name ([parameter[,...]])

例1：定義存儲過程

DELIMITER //
 CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT)
     BEGIN
       SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
     END //
Query OK, 0 rows affected (0.00 sec)

  DELIMITER ;
           

調用存儲過程

CALL CountProc1 (101, @num);
Query OK, 1 row affected (0.00 sec)
           

檢視傳回結果

select @num;
+------+
| @num |
+------+
|    3 |
+------+
1 row in set (0.02 sec)
           

該存儲過程傳回了指定s_id=101的水果商提供的水果種類，傳回值存儲在num變量中，使用SELECT檢視，傳回結果為3。

當存儲過程中傳入中文參數時，要加上character set gbk

如：create procedure useinfo(in u_name varchar(50) characterset gbk,out u_age int)

（2）   調用存儲函數

存儲函數的使用方法與MySQL内部函數的使用方法是一樣的

例1：定義存儲函數CountProc2，然後調用這個函數

DELIMITER //
 CREATE FUNCTION  CountProc2 (sid INT)
     RETURNS INT
     BEGIN
     RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
     END //
Query OK, 0 rows affected (0.00 sec)
  DELIMITER ;
           

調用存儲函數

SELECT CountProc2(101);
+--------------------+
| Countproc(101) |
+--------------------+
|             3 |
+-------------------+
           

3、 檢視存儲過程和函數

(1)SHOW   STATUS 語句檢視存儲過程和函數的狀态

文法：SHOW {PROCEDURE | FUNCTION}     STATUS     [LIKE 'pattern']

 如：SHOW    PROCEDURE   STATUS    LIKE     'C%'\G

(2)SHOW    CREATE語句檢視存儲過程和函數的定義

文法：SHOW  CREATE   {PROCEDURE | FUNCTION}    sp_name

如：SHOW    CREATE    FUNCTION      test.CountProc    \G

(3)從 information_schema.Routines 表中檢視存儲過程和函數的資訊

MySQL 中存儲過程和函數的資訊存儲在 information_schema 資料庫下的 Routines 表中 。 可以通過查詢該表的記錄來查詢存儲過程和函數的資訊。 文法：SELECT   *    FROM       information_schema.Routines     WHERE     ROUTINE_NAME=' sp_name ' ;

如：SELECT   *   FROM    information_schema.Routines   WHERE        ROUTINE_NAME='CountProc'     AND  ROUTINE_TYPE = 'FUNCTION'     \G

4、修改存儲過程和函數

使用 ALTER 語句可以修改存儲過程或函數的特性。 文法：ALTER {PROCEDURE | FUNCTION}      sp_name [characteristic ...]

例1：修改存儲過程CountProc的定義。将讀寫權限改為MODIFIES   SQL   DATA，并指明調用者可以執行

ALTER  PROCEDURE  CountProc  
MODIFIES SQL DATA
SQL SECURITY INVOKER ; 
           

查詢修改後的CountProc表資訊 

SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE
      FROM information_schema.Routines
     WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE='PROCEDURE';
           

5、删除存儲過程和函數

删除存儲過程和函數，可以使用 DROP 語句 。

文法：DROP {PROCEDURE | FUNCTION}     [IF    EXISTS]       sp_name

例1：删除存儲過程和存儲函數

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;
           

轉載請注明出處：http://blog.csdn.net/linshuxin111/