MySql預存程序與函數

標籤：mysql   預存程序   函數   

        林炳文Evankaka原創作品。轉載請註明出處http://blog.csdn.net/evankaka

      摘要：預存程序和函數是在資料庫中定義一些SQL語句的集合，然後直接調用這些預存程序和函數來執行已經定義好的SQL語句。預存程序和函數可以避免開發人員重複的編寫相同的SQL語句。而且，預存程序和函數是在MySQL伺服器中儲存和執行的，可以減少用戶端和伺服器端的資料轉送。

一、預存程序1.1、基本文法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])　　[characteristic ...] routine_body

     Sp_name:預存程序的名稱，預設在當前資料庫中建立。這個名稱應當盡量避免與MySQL的內建函數相同的名稱

      Proc_parameter:預存程序的參數列表
      格式[IN|OUT|INOUT]param_name type
      Param_name為參數名，type為參數的資料類型。多個參數彼此間用逗號分隔。輸入參數、輸出參數和輸入/輸出參數，分別用in/out/inout標識。參數的取名不要與數    據表的列名相同。

   Characteristic:預存程序的某些特徵設定，分別介紹
        1 COMMENT’string’:用於對預存程序的描述，其中string為描述內容,comment為關鍵字。
        2 LANGUAGE SQL:指明編寫這個預存程序的語言為SQL語言。這個選項可以不指定。
        3 DETERMINISTIC:表示預存程序對同樣的輸入參數產生相同的結果;NOT DETERMINISTIC，則表示會產生不確定的結果（預設）。

        4 contains sql | no sql | reads sql data | modifies sql data Contains sql表示預存程序包含讀或寫資料的語句（預設）
        No sql表示不包含sql語句
        Reads sql data表示預存程序只包含讀資料的語句
        Modifies sql data 表示預存程序只包含寫資料的語句
        5 sql security:這個特徵用來指定預存程序使用建立該預存程序的使用者(definer)的許可來執行，還是使用調用者(invoker)的許可來執行。預設是definer

    Routine_body:預存程序的主體部分，包含了在程序呼叫的時候必須執行的sql語句。以begin開始，以end結束。如果預存程序體中只有一條sql語句,可以省略begin-end標誌。

1.2、資料準備

CREATE TABLE    t_user    (        USER_ID INT NOT NULL AUTO_INCREMENT,        USER_NAME CHAR(30) NOT NULL,        USER_PASSWORD CHAR(10) NOT NULL,        USER_EMAIL CHAR(30) NOT NULL,        PRIMARY KEY (USER_ID),        INDEX IDX_NAME (USER_NAME)    )    ENGINE=InnoDB DEFAULT CHARSET=utf8;

然後這是插入的一些資料：

1.3 IN、OUT、INOUT參數

（1）、帶IN的預存程序

//建立儲存過程.cmd 中運行CREATE  PROCEDURE  SP_SEARCH(IN p_name CHAR(20))BEGINIF p_name is null or p_name=‘‘ THENSELECT * FROM t_user;ELSESELECT * FROM t_user WHERE USER_NAME LIKE p_name;END IF;END

因為;分會衝突，所以要加delimiter //。將//設定為結束運行符號

如下：

調用：

//調用並輸出結果CALL SP_SEARCH(‘林炳文‘)

結果

（2）、帶OUT的預存程序

//帶OUT返回的CREATE  PROCEDURE  SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT)BEGINIF p_name is null or p_name=‘‘ THENSELECT * FROM t_user;ELSESELECT * FROM t_user WHERE USER_NAME LIKE p_name;END IF;SELECT FOUND_ROWS() INTO p_int;END

調用輸出：統計帶林開頭的人數

//調用並輸出結果CALL SP_SEARCH2(‘林%‘,@p_num);SELECT @p_num;

（3）、帶INOUT的預存程序

//帶INOUT的預存程序CREATE PROCEDURE sp_inout(INOUT p_num INT)BEGINSET p_num=p_num*10;END//調用並輸出結果SET @p_num=2;call sp_inout(@p_num);SELECT @p_num;

輸出結果：

1.4、預存程序體   

       預存程序體中可以使用各種sql語句和過程式語句的組合，來封裝資料庫應用中複雜的商務邏輯和處理規則，以實現資料庫應用的靈活編程。下面主要介紹幾個用於構造預存程序體的常用文法元素。
1、局部變數
在預存程序體中可以聲明局部變數，用來儲存預存程序體中臨時結果。

DECLARE var_name[,…] type [DEFAULT value]Var_name:指定局部變數的名稱Type:用於聲明局部變數的資料類型default子句:用於為局部變數指定一個預設值。若沒有指定，預設為null.

如：

Declare cid int(10);

使用說明：
局部變數只能在預存程序體的begin…end語句塊中聲明。
局部變數必須在預存程序體的開頭處聲明。
局部變數的作用範圍僅限於聲明它的begin..end語句塊，其他語句塊中的語句不可以使用它。
局部變數不同於使用者變數，兩者區別：局部變數聲明時，在其前面沒有使用@符號，並且它只能在begin..end語句塊中使用；而使用者變數在聲明時，會在其名稱前面使用@符號，同時已聲明的使用者變數存在於整個會話之中。
2、set語句
使用set語句為局部變數賦值

Set var_name=exprSet cid=910;

3、select … into 語句
把選定列的值直接儲存到局部變數中，文法格式

Select col_name[,…] into var_name[,…] table_exprCol_name:用於指定列名Var_name:用於指定要賦值的變數名Table_expr:表示select語句中的from字句及後面的文法部分

說明:預存程序體中的select…into語句返回的結果集只能有一行資料。

4、定義處理常式
是事先定義程式執行過程中可能遇到的問題。並且可以在處理常式中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，並提出解決方案。

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statementhandler_type:CONTINUE | EXIT | UNDOCondition_value:Sqlwarning | not found | sqlexception

5、流程式控制制語句
（1）條件判斷語句
If語句

If search_condition then statement_list[elseif search_condition then statement_list]…[else statement_list]End if

Search_condition參數:條件判斷語句
Statement_list參數:不同條件的執行語句

多重IF的預存程序執行個體

資料準備

學生表：

CREATE TABLE    t_student    (        STU_ID INT NOT NULL,        STU_NAME CHAR(10) NOT NULL,        STU_CLASS INT NOT NULL,        STU_SEX CHAR(2) NOT NULL,        STU_AGE INT NOT NULL,        PRIMARY KEY (STU_ID)    )    ENGINE=InnoDB DEFAULT CHARSET=utf8;

資料如下：

成績表（STU_ID是學生表是外鍵關係）：

CREATE TABLE    t_grade    (        STU_ID INT NOT NULL,        STU_SCORE INT NOT NULL,        FOREIGN KEY (STU_ID) REFERENCES t_student (STU_ID),        INDEX STU_ID (STU_ID)    )    ENGINE=InnoDB DEFAULT CHARSET=utf8;

然後寫一個預存程序：返回各個分數等級的人

//帶多重IF的預存程序CREATE  PROCEDURE SP_SCHOLARSHIP_LEVEL(IN p_level char(1))BEGINIF p_level =‘A‘  THENSELECT * FROM t_grade WHERE STU_SCORE >=90;ELSEIF  p_level =‘B‘  THENSELECT * FROM t_grade WHERE STU_SCORE <90 AND  STU_SCORE>=80;ELSEIF  p_level =‘C‘  THENSELECT * FROM t_grade WHERE STU_SCORE <80 AND  STU_SCORE>=70;ELSEIF  p_level =‘D‘  THENSELECT * FROM t_grade WHERE STU_SCORE <60;ELSESELECT * FROM t_grade;END IF;END

調用過程：

//調用並輸出結果CALL SP_SCHOLARSHIP_LEVEL(‘A‘);

Case 語句
表達形式1

Case case_valueWhen when_value then statement_list[When when_value then statement_list]…[else statement_list]End case

表達形式2

CaseWhen search_condition then statement_listEnd case

使用範例

CREATE  PROCEDURE SP_SCHOLARSHIP_LEVEL3(IN p_level char(1))BEGINDECLARE p_num int DEFAULT  0;CASE p_levelWHEN ‘A‘  THENSET p_num=90;WHEN ‘B‘  THENSET p_num=80;WHEN ‘C‘  THENSET p_num=70;WHEN ‘D‘  THENSET p_num=60;ELSESET p_num=0;END CASE;SELECT * FROM t_grade g, t_student s WHERE g.STU_ID=s.STU_ID AND g.STU_SCORE >= p_num ;END

調用：

//調用並輸出結果CALL SP_SCHOLARSHIP_LEVEL3(‘d‘);

(2)迴圈語句
While語句、repeat語句和loop語句。
While語句

[begin_label:]while search_condition doStatement_listEnd while[end_label]

       判斷條件search_condition是否為真,若為真,則執行statement_list中的語句，然後再進行判斷，如若仍然為真則繼續迴圈，直至條件判斷不為真時迴圈結束。
使用範例

//帶while的預存程序CREATE PROCEDURE sp_cal(IN p_num INT,OUT p_result INT)BEGIN SET p_result=1; WHILE p_num > 1 DO  SET p_result = p_num * p_result;  SET p_num = p_num-1; END WHILE;END//調用並輸出結果CALL sp_cal(5,@result);SELECT @result;

輸出結果：計算5！

Repeat語句文法格式

[begin_label:]repeatStatement_listUntil search_conditionEnd repeat[end_label]

        Repeat語句首先執行statement_list中的語句，然後判斷條件search_condition是否為真，倘若為真，則結束迴圈，若不為真，繼續迴圈。
Repeat先執行後判斷，while先判斷後執行。
使用範例：

//帶repeat的預存程序CREATE PROCEDURE sp_cal2(IN p_num INT,OUT p_result INT)BEGIN  SET p_result=1;  REPEAT    SET p_result = p_num * p_result;    SET p_num = p_num-1;    UNTIL p_num<=1  END REPEAT;END//調用並輸出結果CALL sp_cal2(5,@result);SELECT @result;

1.5、 調用預存程序

Call sp_name([parameter[,…]]);Sp_name被調用預存程序的名稱Parameter:指定調用預存程序所要使用的參數。

1.6、 修改預存程序

Alter procedure proc_name[characteristic…]

只能修改預存程序的特徵，如果要修改預存程序的內容，可以先刪除該預存程序，然後再重新建立
1.7、 刪除預存程序

Drop procedure [if exists] sp_name;

二、函數2.1、 定義

MySQL中，建立儲存函數的基本形式如下：

CREATE FUNCTION sp_name([func_parameter[,...]])RETURNS type[characteristic ...] routine_bodyReturn

子句用於聲明儲存函數傳回值的資料類型。預存程序是使用者定義的一系列sql語句的集合，涉及特定表或其它對象的任務，使用者可以調用預存程序，而函數通常是資料庫已定義的方法，它接收參數並返回某種類型的值並且不涉及特定使用者表。

調用儲存函數

Select sp_name([func_parameter…])Select fn_search(2);

刪除儲存函數drop
修改儲存函數alter 修改儲存函數的某些相關特徵。
2.2、函數使用例子

（比較大小 ，返回大的數）

/**函數使用**/CREATE FUNCTION sp_cal_max(p_num1 INT,p_num2 INT)RETURNS INTBEGINIF p_num1 >= p_num2 THENRETURN p_num1;ELSERETURN p_num2;END IF;END

調用：

SET @p_num1=2;SET @p_num2=34;SELECT sp_cal_max(@p_num1,@p_num2);

2.3、預存程序和函數區別

1）一般來說，預存程序實現的功能要複雜一點，而函數的實現的功能針對性比較強。預存程序，功能強大，可以執行包括修改表等一系列資料庫操作；使用者定義函數不能用於執行一組修改全域資料庫狀態的操作。

2）對於預存程序來說可以返回參數，如記錄集，而函數只能傳回值或者表對象。函數只能返回一個變數；而預存程序可以返回多個。預存程序的參數可以有IN,OUT,INOUT三種類型，而函數只能有IN類~~預存程序聲明時不需要傳回型別，而函式宣告時需要描述傳回型別，且函數體中必須包含一個有效RETURN語句。

3）預存程序，可以使用非確定函數，不允許在使用者定義函數主體中內建非確定函數。

4）預存程序一般是作為一個獨立的部分來執行（ EXECUTE 語句執行），而函數可以作為查詢語句的一個部分來調用（SELECT調用），由於函數可以返回一個表對象，因此它可以在查詢語句中位於FROM關鍵字的後面。 SQL語句中不可用預存程序，而可以使用函數。

三、游標（遊標） 3.1 定義

     查詢語句可能查詢出多條記錄，在預存程序和函數中使用游標標來逐條讀取查詢結果集中的記錄。游標的使用包括聲明游標、開啟游標、使用游標和關閉游標。游標必須聲明游標、開啟游標、使用游標和關閉游標。游標必須聲明在處理常式之前，並且聲明在變數和條件之後。

1 聲明游標

Declare cursor_name cursor forselect_statement;Cursor_name:游標名稱Select_statement:select語句的內容Declare cur_employee cursor forselect name,age from employee;

2 開啟游標

Open cursor_nameOpen cur_employee;

3 使用游標

Mysql中使用fetch關鍵字來使用游標，文法形式

Fetch cur_name intovar_name[,var_name…];Cur_name表示游標的名稱Var_name表示將游標中的select語句查詢出來的資訊存入該參數。Var_name必須在聲明游標前就定義好。Fetch cur_employee intoemp_name,emp_age;

4 關閉游標

Close cursor_name;Close cur_employee;

每個游標不再需要時都應該被關閉，使用close語句將會釋放游標所使用的全部資源。在一個游標被關閉後，如果沒有重新被開啟，則不能被使用。對於聲明過的游標，則不需要再次聲明，可直接使用open語句開啟。

3.2、使用範例

（將表test_cur1資料複製到test_cur2）

CREATE TABLE `test_cur1` (    `id` int(11) NOT NULL auto_increment,    `type` char(11) default NULL,    `order1` char(11) default NULL,    PRIMARY KEY  (`id`)  ) INSERT INTO `test_cur1` VALUES (1, ‘145‘, ‘d1‘);  INSERT INTO `test_cur1` VALUES (2, ‘134‘, ‘1d‘);  INSERT INTO `test_cur1` VALUES (3, ‘123‘, ‘1ad‘);  INSERT  INTO `test_cur1` VALUES (4, ‘121‘, ‘1as‘); CREATE TABLE `test_cur2` (    `id` int(11) NOT NULL auto_increment,    `type` char(11) default NULL,    `order1` char(11) default NULL,    PRIMARY KEY  (`id`)  ) 

然後寫游標了：

create procedure get_cur ()BEGIN  DECLARE done INT DEFAULT 0;  DECLARE ID int(11);  DECLARE type char(11);  DECLARE order1 char(11);  DECLARE mycur CURSOR FOR SELECT * FROM test_cur1;//定義游標  DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1;  //開啟游標  OPEN mycur;  //開始迴圈  REPEAT    FETCH mycur INTO ID,type,order1;//取出游標的內容到臨時變數    IF NOT done THEN      INSERT INTO test_cur2 VALUES (ID,type,order1);//插入到另一張表    END IF;    UNTIL done END REPEAT;//當done=1時結束迴圈  //關閉游標  CLOSE mycur;END

運行：

call get_cur()

來看看兩張表的資料：這是表2

這是表1

說明資料已成功複製過去了

著作權聲明：本文為博主林炳文Evankaka原創文章，未經博主允許不得轉載。

MySql預存程序與函數