程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySql存儲進程與函數詳解

MySql存儲進程與函數詳解

編輯：MySQL綜合教程

MySql存儲進程與函數詳解。本站提示廣大學習愛好者：（MySql存儲進程與函數詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是MySql存儲進程與函數詳解正文

存儲進程和函數是在數據庫中界說一些SQL語句的聚集，然後直接挪用這些存儲進程和函數來履行曾經界說好的SQL語句。存儲進程和函數可以免開辟人員反復的編寫雷同的SQL語句。並且，存儲進程和函數是在MySQL辦事器中存儲和履行的，可以削減客戶端和辦事器真個數據傳輸。
1、存儲進程
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)) 
BEGIN 
IF p_name is null or p_name='' THEN 
SELECT * FROM t_user; 
ELSE 
SELECT * 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) 
BEGIN 
IF p_name is null or p_name='' THEN 
SELECT * FROM t_user; 
ELSE 
SELECT * 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) 
BEGIN 
SET 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=expr
Set cid=910;

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

Select col_name[,…] into var_name[,…] table_expr 
Col_name:用於指定列名 
Var_name:用於指定要賦值的變量名 
Table_expr:表現select語句中的from字句及前面的語法部門

解釋:存儲進程體中的select…into語句前往的成果集只能有一行數據。
4、界說處置法式
是事前界說法式履行進程中能夠碰到的成績。而且可以在處置法式中界說處理這些成績的方法。這類方法可以提早猜測能夠湧現的成績，並提出處理辦法。

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement 
handler_type:CONTINUE | EXIT | UNDO 
Condition_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)) 
BEGIN 
IF p_level ='A' THEN 
SELECT * FROM t_grade WHERE STU_SCORE >=90; 
ELSEIF p_level ='B' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <90 AND STU_SCORE>=80; 
ELSEIF p_level ='C' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <80 AND STU_SCORE>=70; 
ELSEIF p_level ='D' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <60; 
ELSE 
SELECT * FROM t_grade; 
END IF; 
END

挪用進程：

//挪用並輸入成果 
CALL SP_SCHOLARSHIP_LEVEL('A');

Case 語句
表達情勢1

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

表達情勢2

Case 
When search_condition then statement_list 
End case

應用典范

CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL3(IN p_level char(1)) 
BEGIN 
DECLARE p_num int DEFAULT 0; 
CASE p_level 
WHEN 'A' THEN 
SET p_num=90; 
WHEN 'B' THEN 
SET p_num=80; 
WHEN 'C' THEN 
SET p_num=70; 
WHEN 'D' THEN 
SET p_num=60; 
ELSE 
SET 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 do 
Statement_list 
End 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:] 
repeat 
Statement_list 
Until search_condition 
End 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、函數
2.1、界說

MySQL中，創立存儲函數的根本情勢以下：
CREATE FUNCTION sp_name([func_parameter[,...]]) 
RETURNS type 
[characteristic ...] routine_body 
Return

子句用於聲明存儲函數前往值的數據類型。存儲進程是用戶界說的一系列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 INT 
BEGIN 
IF p_num1 >= p_num2 THEN 
RETURN p_num1; 
ELSE 
RETURN 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、光標（游標）
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_name 
Open 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

運轉：