MySql預存程序與函數

標籤：輸入   包括   return語句   str   contain   關係   close   沒有   blank   

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

      摘要：預存程序和函數是在資料庫中定義一些SQL語句的集合，然後直接調用這些預存程序和函數來運行已經定義好的SQL語句。預存程序和函數能夠避免開發人員反覆的編寫同樣的SQL語句。而且，預存程序和函數是在MySQLserver中儲存和啟動並執行。能夠降低client和server端的傳輸資料。

一、預存程序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為keyword。
        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調用）。由於函數能夠返回一個表對象。因此它能夠在查詢語句中位於FROMkeyword的後面。 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中使用fetchkeyword來使用游標，文法形式

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

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

MySql預存程序與函數