人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動態(tài)

MYSQL的存儲過程和函數(shù)簡單寫法

發(fā)布日期:2022-03-24 14:50 | 文章來源:gibhub

什么是存儲過程

簡單的說,就是一組SQL語句集,功能強大,可以實現(xiàn)一些比較復(fù)雜的邏輯功能,類似于JAVA語言中的方法;

ps:存儲過程跟觸發(fā)器有點類似,都是一組SQL集,但是存儲過程是主動調(diào)用的,且功能比觸發(fā)器更加強大,觸發(fā)器是某件事觸發(fā)后自動調(diào)用;

有哪些特性

有輸入輸出參數(shù),可以聲明變量,有if/else, case,while等控制語句,通過編寫存儲過程,可以實現(xiàn)復(fù)雜的邏輯功能;

函數(shù)的普遍特性:模塊化,封裝,代碼復(fù)用;

速度快,只有首次執(zhí)行需經(jīng)過編譯和優(yōu)化步驟,后續(xù)被調(diào)用可以直接執(zhí)行,省去以上步驟;

MySQL存儲過程的創(chuàng)建

語法

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

CREATE PROCEDURE過程名([[IN|OUT|INOUT] 參數(shù)名 數(shù)據(jù)類型[,[IN|OUT|INOUT] 參數(shù)名 數(shù)據(jù)類型…]]) [特性 ...] 過程體

DELIMITER //
 CREATE PROCEDURE myproc(OUT s int)
  BEGIN
   SELECT COUNT(*) INTO s FROM students;
  END
  //
DELIMITER ;

分隔符

MySQL默認以";"為分隔符,如果沒有聲明分割符,則編譯器會把存儲過程當(dāng)成SQL語句進行處理,因此編譯過程會報錯,所以要事先用“DELIMITER //”聲明當(dāng)前段分隔符,讓編譯器把兩個"http://"之間的內(nèi)容當(dāng)做存儲過程的代碼,不會執(zhí)行這些代碼;“DELIMITER ;”的意為把分隔符還原。

參數(shù)

存儲過程根據(jù)需要可能會有輸入、輸出、輸入輸出參數(shù),如果有多個參數(shù)用","分割開。MySQL存儲過程的參數(shù)用在存儲過程的定義,共有三種參數(shù)類型,IN,OUT,INOUT:

  • IN參數(shù)的值必須在調(diào)用存儲過程時指定,在存儲過程中修改該參數(shù)的值不能被返回,為默認值
  • OUT:該值可在存儲過程內(nèi)部被改變,并可返回
  • INOUT:調(diào)用時指定,并且可被改變和返回

其中,sp_name參數(shù)是存儲過程的名稱;proc_parameter表示存儲過程的參數(shù)列表; characteristic參數(shù)指定存儲過程的特性;routine_body參數(shù)是SQL代碼的內(nèi)容,可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束。

proc_parameter中的每個參數(shù)由3部分組成。這3部分分別是輸入輸出類型、參數(shù)名稱和參數(shù)類型。其形式如下:

[ IN | OUT | INOUT ] param_name type

其中,IN表示輸入?yún)?shù);OUT表示輸出參數(shù); INOUT表示既可以是輸入,也可以是輸出; param_name參數(shù)是存儲過程的參數(shù)名稱;type參數(shù)指定存儲過程的參數(shù)類型,該類型可以是MySQL數(shù)據(jù)庫的任意數(shù)據(jù)類型。

characteristic參數(shù)有多個取值。其取值說明如下:

LANGUAGE SQL:說明routine_body部分是由SQL語言的語句組成,這也是數(shù)據(jù)庫系統(tǒng)默認的語言。

[NOT] DETERMINISTIC:指明存儲過程的執(zhí)行結(jié)果是否是確定的。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲過程時,相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是非確定的,相同的輸入可能得到不同的輸出。默認情況下,結(jié)果是非確定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL語句的限制。CONTAINS SQL表示子程序包含SQL語句,但不包含讀或?qū)憯?shù)據(jù)的語句;NO SQL表示子程序中不包含SQL語句;READS SQL DATA表示子程序中包含讀數(shù)據(jù)的語句;MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語句。默認情況下,系統(tǒng)會指定為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER }:指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義者自己才能夠執(zhí)行;INVOKER表示調(diào)用者可以執(zhí)行。默認情況下,系統(tǒng)指定的權(quán)限是DEFINER。

COMMENT 'string':注釋信息。

技巧:創(chuàng)建存儲過程時,系統(tǒng)默認指定CONTAINS SQL,表示存儲過程中使用了SQL語句。但是,如果存儲過程中沒有使用SQL語句,最好設(shè)置為NO SQL。而且,存儲過程中最好在COMMENT部分對存儲過程進行簡單的注釋,以便以后在閱讀存儲過程的代碼時更加方便。

【示例1】 下面創(chuàng)建一個名為num_from_employee的存儲過程。代碼如下:

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
     READS SQL DATA 
     BEGIN 
       SELECT COUNT(*) INTO count_num 
       FROM employee 
       WHERE d_id=emp_id ; 
     END

上述代碼中,存儲過程名稱為num_from_employee;輸入變量為emp_id;輸出變量為count_num。SELECT語句從employee表查詢d_id值等于emp_id的記錄,并用COUNT(*)計算d_id值相同的記錄的條數(shù),最后將計算結(jié)果存入count_num中。代碼的執(zhí)行結(jié)果如下:

mysql> DELIMITER && 
mysql> CREATE PROCEDURE num_from_employee
(IN emp_id INT, OUT count_num INT ) 
  -> READS SQL DATA 
  -> BEGIN 
  -> SELECT COUNT(*) INTO count_num 
  -> FROM employee 
  -> WHERE d_id=emp_id ; 
  -> END && 
Query OK, 0 rows affected (0.09 sec) 
mysql> DELIMITER ;

代碼執(zhí)行完畢后,沒有報出任何出錯信息就表示存儲函數(shù)已經(jīng)創(chuàng)建成功。以后就可以調(diào)用這個存儲過程,數(shù)據(jù)庫中會執(zhí)行存儲過程中的SQL語句。

說明:MySQL中默認的語句結(jié)束符為分號(;)。存儲過程中的SQL語句需要分號來 結(jié)束。為了避免沖突,首先用"DELIMITER &&"將MySQL的結(jié)束符設(shè)置為&&。最后再用"DELIMITER ;"來將結(jié)束符恢復(fù)成分號。這與創(chuàng)建觸發(fā)器時是一樣的。

函數(shù)

在MySQL中,創(chuàng)建存儲函數(shù)的基本形式如下:

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
  其中,sp_name參數(shù)是存儲函數(shù)的名稱;func_parameter表示存儲函數(shù)的參數(shù)列表;RETURNS type指定返回值的類型;characteristic參數(shù)指定存儲函數(shù)的特性,該參數(shù)的取值與存儲過程中的取值是一樣的,請讀者參照14.1.1小節(jié)的內(nèi)容;routine_body參數(shù)是SQL代碼的內(nèi)容,可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束。

func_parameter可以由多個參數(shù)組成,其中每個參數(shù)由參數(shù)名稱和參數(shù)類型組成,其形式如下:param_name type

其中,param_name參數(shù)是存儲函數(shù)的參數(shù)名稱;type參數(shù)指定存儲函數(shù)的參數(shù)類型,該類型可以是MySQL數(shù)據(jù)庫的任意數(shù)據(jù)類型。

【示例2】 下面創(chuàng)建一個名為name_from_employee的存儲函數(shù)。代碼如下:

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN 
       RETURN (SELECT name 
       FROM employee 
       WHERE num=emp_id ); 
     END

上述代碼中,存儲函數(shù)的名稱為name_from_employee;該函數(shù)的參數(shù)為emp_id;返回值是VARCHAR類型。SELECT語句從employee表查詢num值等于emp_id的記錄,并將該記錄的name字段的值返回。代碼的執(zhí)行結(jié)果如下:

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN 
  -> RETURN (SELECT name 
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ;

結(jié)果顯示,存儲函數(shù)已經(jīng)創(chuàng)建成功。該函數(shù)的使用和MySQL內(nèi)部函數(shù)的使用方法一樣。

變量的使用

在存儲過程和函數(shù)中,可以定義和使用變量。用戶可以使用DECLARE關(guān)鍵字來定義變量。然后可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中。本小節(jié)將講解如何定義變量和為變量賦值。

1.定義變量

MySQL中可以使用DECLARE關(guān)鍵字來定義變量。定義變量的基本語法如下:

DECLARE var_name[,...] type [DEFAULT value]

其中, DECLARE關(guān)鍵字是用來聲明變量的;var_name參數(shù)是變量的名稱,這里可以同時定義多個變量;type參數(shù)用來指定變量的類型;DEFAULT value子句將變量默認值設(shè)置為value,沒有使用DEFAULT子句時,默認值為NULL。

【示例3】 下面定義變量my_sql,數(shù)據(jù)類型為INT型,默認值為10。代碼如下:

DECLARE my_sql INT DEFAULT 10 ;

2.為變量賦值

MySQL中可以使用SET關(guān)鍵字來為變量賦值。SET語句的基本語法如下:

SET var_name = expr [, var_name = expr] ...

其中,SET關(guān)鍵字是用來為變量賦值的;var_name參數(shù)是變量的名稱;expr參數(shù)是賦值表達式。一個SET語句可以同時為多個變量賦值,各個變量的賦值語句之間用逗號隔開。

【示例4】 下面為變量my_sql賦值為30。代碼如下:

SET my_sql = 30 ;

MySQL中還可以使用SELECT…INTO語句為變量賦值。其基本語法如下:

SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE condition
  其中,col_name參數(shù)表示查詢的字段名稱;var_name參數(shù)是變量的名稱;table_name參數(shù)指表的名稱;condition參數(shù)指查詢條件。

【示例5】 下面從employee表中查詢id為2的記錄,將該記錄的d_id值賦給變量my_sql。代碼如下:

SELECT d_id INTO my_sql FROM employee WEHRE id=2 ;

定義條件和處理程序

定義條件和處理程序是事先定義程序執(zhí)行過程中可能遇到的問題。并且可以在處理程序中定義解決這些問題的辦法。這種方式可以提前預(yù)測可能出現(xiàn)的問題,并提出解決辦法。這樣可以增強程序處理問題的能力,避免程序異常停止。MySQL中都是通過DECLARE關(guān)鍵字來定義條件和處理程序。本小節(jié)中將詳細講解如何定義條件和處理程序。

1.定義條件

MySQL中可以使用DECLARE關(guān)鍵字來定義條件。其基本語法如下:

DECLARE condition_name CONDITION FOR condition_value 
condition_value: 
   SQLSTATE [VALUE] sqlstate_value | mysql_error_code 

其中,condition_name參數(shù)表示條件的名稱;condition_value參數(shù)表示條件的類型;sqlstate_value參數(shù)和mysql_error_code參數(shù)都可以表示MySQL的錯誤。例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。

【示例6】 下面定義"ERROR 1146 (42S02)"這個錯誤,名稱為can_not_find??梢杂脙煞N不同的方法來定義,代碼如下:

//方法一:使用sqlstate_value 
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ; 
//方法二:使用mysql_error_code 
DECLARE can_not_find CONDITION FOR 1146 ; 

2.定義處理程序

MySQL中可以使用DECLARE關(guān)鍵字來定義處理程序。其基本語法如下:

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement 
handler_type: 
  CONTINUE | EXIT | UNDO 
condition_value: 
  SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING 
    | NOT FOUND | SQLEXCEPTION | mysql_error_code

其中,handler_type參數(shù)指明錯誤的處理方式,該參數(shù)有3個取值。這3個取值分別是CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯誤不進行處理,繼續(xù)向下執(zhí)行;EXIT表示遇到錯誤后馬上退出;UNDO表示遇到錯誤后撤回之前的操作,MySQL中暫時還不支持這種處理方式。

注意:通常情況下,執(zhí)行過程中遇到錯誤應(yīng)該立刻停止執(zhí)行下面的語句,并且撤回前面的操作。但是,MySQL中現(xiàn)在還不能支持UNDO操作。因此,遇到錯誤時最好執(zhí)行EXIT操作。如果事先能夠預(yù)測錯誤類型,并且進行相應(yīng)的處理,那么可以執(zhí)行CONTINUE操作。

condition_value參數(shù)指明錯誤類型,該參數(shù)有6個取值。sqlstate_value和mysql_error_code與條件定義中的是同一個意思。condition_name是DECLARE定義的條件名稱。SQLWARNING表示所有以01開頭的sqlstate_value值。NOT FOUND表示所有以02開頭的sqlstate_value值。SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。sp_statement表示一些存儲過程或函數(shù)的執(zhí)行語句。

【示例7】 下面是定義處理程序的幾種方式。代碼如下:

//方法一:捕獲sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND'; 
//方法二:捕獲mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND'; 
//方法三:先定義條件,然后調(diào)用 
DECLARE can_not_find CONDITION FOR 1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND'; 
//方法四:使用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 
//方法五:使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
//方法六:使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

上述代碼是6種定義處理程序的方法。

第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02,執(zhí)行CONTINUE操作,并且輸出"CAN NOT FIND"信息。

第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146,執(zhí)行CONTINUE操作,并且輸出"CAN NOT FIND"信息。

第三種方法是先定義條件,然后再調(diào)用條件。這里先定義can_not_find條件,遇到1146錯誤就執(zhí)行CONTINUE操作。

第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值,然后執(zhí)行EXIT操作,并且輸出"ERROR"信息。

第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值,然后執(zhí)行EXIT操作,并且輸出"CAN NOT FIND"信息。

第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值,然后執(zhí)行EXIT操作,并且輸出"ERROR"信息。

MySQL存儲過程寫法總結(jié)

1、創(chuàng)建無參存儲過程。

create procedure product()
begin
    select * from user;
end;

一條簡單的存儲過程創(chuàng)建語句,此時調(diào)用的語句為:

call procedure();

##注意,如果是在命令行下編寫的話,這樣的寫法會出現(xiàn)語法錯誤,即再select 那一句結(jié)束
mysql就會進行解釋了,此時應(yīng)該先把結(jié)尾符換一下:

delimiter //
create procedure product()
begin
    select * from user;
end //

最后再換回來

delimiter ;

2、創(chuàng)建有參存儲過程

有參的存儲包括兩種參數(shù),
一個是傳入?yún)?shù);
一個是傳出參數(shù);
例如一個存儲過程:

create procedure procedure2(
out p1 decimal(8,2),
out p2 decimal(8,2),
in p3 int
)
begin
select sum(uid) into p1 from user where order_name = p3;
select avg(uid) into p2 from user ;
end ;

從上面sql語句可以看出,p1和p2是用來檢索并且傳出去的值,而p3則是必須有調(diào)用這傳入的具體值。
看具體調(diào)用過程:

call product(); //無參
call procedure2(@userSum,@userAvg,201708); //有參

當(dāng)用完后,可以直接查詢userSum和userAvg的值:

select @userSum, @userAvg;

結(jié)果如下:

+----------+----------+
| @userSum | @userAvg |
+----------+----------+
| 67.00 | 6.09 |
+----------+----------+
1 row in set (0.00 sec)

3、刪除存儲過程

一條語句: drop procedure product; //沒有括號后面

4、一段完整的存儲過程實例:

-- Name: drdertotal 
-- Parameters : onumber = order number 
--       taxable = 0 if not taxable,1if taxable 
--       ototal = order total variable 
 
create procedure ordertotal( 
in onumber int, 
in taxable boolean, 
out ototal decimal(8,2) 
) commit 'Obtain order total, optionally adding tax'  
begin 
  -- Declare variable for total 
  declare total decimal(8,2); 
  -- Declare tax percentage 
  declare taxrate int default 6; 
   
  --Get the order total 
  select Sum(item_price*quantity) 
  from orderitems 
  where order_num = onumber 
  into total; 
   
  --Is this taxable? 
  if taxable then 
    --Yes, so add taxrate to the total 
    select total+(total/100*taxrate) into total; 
  end if; 
   
  --Add finally, save to out variable 
  select total into ototal; 
end;

上面存儲過程類似于高級語言的業(yè)務(wù)處理,看懂還是不難的,注意寫法細節(jié)

commit關(guān)鍵字:它不是必需的,但如果給出,將在show procedure status的結(jié)果中給出。

if語句:這個例子給出了mysqlif語句的基本用法,if語句還支持elseif和else子句。

通過show procedure status可以列出所有的存儲過程的詳細列表,并且可以在后面加一個

like+指定過濾模式來進行過濾。

海外穩(wěn)定服務(wù)器

版權(quán)聲明:本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學(xué)習(xí)參考,不代表本站立場,如有內(nèi)容涉嫌侵權(quán),請聯(lián)系alex-e#qq.com處理。

實時開通

自選配置、實時開通

免備案

全球線路精選!

全天候客戶服務(wù)

7x24全年不間斷在線

專屬顧問服務(wù)

1對1客戶咨詢顧問

在線
客服

在線客服:7*24小時在線

客服
熱線

400-630-3752
7*24小時客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部