mysql存儲過程技術(shù)

1、 存儲過程的概念 為什么使用存儲過程 基本創(chuàng)建、刪除語法 存儲過程中的變量 begin.end復合語句 流程控制結(jié)構(gòu)語句 注釋語法 使用權(quán)限 條件和異常處理程序 游標 存儲過程的概念：存儲過程的概念： 所謂的存儲過程就是存儲在數(shù)據(jù)庫當中的可以執(zhí)行特定工作(查詢和更新)的一組sql代碼的程序段。 與自定義函數(shù)的區(qū)別： 自定義函數(shù)有且只有一個返回值，就像普通的函數(shù)一樣，可以直接在表達式中嵌入調(diào)用。 存儲過程可以沒有返回值，也可以有任意個輸出參數(shù)，必須單獨調(diào)用。 執(zhí)行的本質(zhì)都一樣。只是函數(shù)有如只能返回一個變量的限制。而存儲過程可以返回多個。而函數(shù)是可以嵌入在sql中使 用的，可以在select中調(diào)用

2、，而存儲過程不行。 函數(shù)限制比較多，比如不能用臨時表，只能用表變量。還有一些函數(shù)都不可用等等。而存儲過程的限制相對就比較少。 一般來說，存儲過程實現(xiàn)的功能要復雜一點，而函數(shù)的實現(xiàn)的功能針對性比較強。 對于存儲過程來說可以返回參數(shù)，而函數(shù)只能返回值或者表對象。 存儲過程一般是作為一個獨立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一個部分來調(diào)用，由于函數(shù)可以返回一個表 對象，因此它可以在查詢語句中位于from關(guān)鍵字的后面。 為什么使用存儲過程：為什么使用存儲過程： 存儲過程的優(yōu)點： 存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般sql語句每執(zhí)行一次就編譯一次, 所以使用存儲過

3、程可提高數(shù)據(jù)庫執(zhí)行速度。 當對數(shù)據(jù)庫進行復雜操作時（如對多個表進行update、insert、query、delete時），可將此復雜操作用存儲過程封 裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。 存儲過程可以重復使用，可減少數(shù)據(jù)庫開發(fā)人員的工作量。 安全性高，可設(shè)定只有某此用戶才具有對指定存儲過程的使用權(quán)。 基本的創(chuàng)建、刪除語法：基本的創(chuàng)建、刪除語法： create procedure sp_name (proc_parameter,.) characteristic . routine_body 默認地，子程序與當前數(shù)據(jù)庫關(guān)聯(lián)。要明確地把子程序與一個給定數(shù)據(jù)庫關(guān)聯(lián)起來，可以在創(chuàng)建子程序的時候

4、指定其 名字為db_name.sp_name。 sp_name 存儲過程的名字 proc_parameter指定參數(shù)為in， out，或inout characteristic 特征 routine_body 包含合法的sql過程語句。 drop procedure | function if exists sp_name 這個語句被用來移除一個存儲程序或函數(shù)。即，從服務(wù)器移除一個制定的子程序。在mysql 5.1中，你必須有alter routine權(quán)限才可用此子程序。這個權(quán)限被自動授予子程序的創(chuàng)建者。 if exists 子句是一個mysql的擴展。如果程序或函數(shù)不存在，它防止發(fā)生錯誤。

5、基本的創(chuàng)建、調(diào)用、刪除語法：基本的創(chuàng)建、調(diào)用、刪除語法： delimiter / drop procedure if exists test / create procedure test /* 存儲過程名 */ (in inparms int, out outparams varchar(32) /* 輸入?yún)?shù) */ begin /* 語句塊頭 */ declare var char(10); /* 變量聲明 */ if inparms = 1 then /* if條件開始*/ set var = hello; /* 賦值 */ else set var = world; end if; /

6、* if結(jié)束 */ insert into t1 values (var); /* sql語句 */ select name from t1 limit 1 into outparams; end / delimiter ; call test(1, out); 存儲過程的變量：存儲過程的變量： l 聲明變量： declare var_name,. type default value 這個語句被用來聲明局部變量。要給變量提供一個默認值，需要包含一個default子句。值可以被指定為一個表達式， 不需要為一個常數(shù)。如果沒有default子句，初始值為null。 局部變量的作用范圍在它被聲明的b

7、egin . end塊內(nèi)。它可以被用在嵌套的塊中，除了那些用相同名字聲明變量的 塊。 l 變量賦值，set語句： set var_name = expr , var_name = expr . 也可以用語句代替set來為用戶變量分配一個值。在這種情況下，分配符必須為:=而不能用=，因為在非set語句中=被 視為一個比較 操作符，如下所示： mysql set t1=0, t2=0, t3=0; mysql select t1:=0,t2:=0,t3:=0; 對于使用select語句為變量賦值的情況，若返回結(jié)果為空，即沒有記錄，此時變量的值為上一次變量賦值時的值，如 果沒有對變量賦過值，則為nu

8、ll。 l 變量賦值，select . into語句 select col_name,. into var_name,. table_expr這個select語法把選定的列直接存儲到變量。因此，只有單一 的行可以被取回。 select id,data into x,y from test.t1 limit 1; begin.end復合語句：復合語句： begin_label: begin statement_list end end_label 存儲子程序可以使用begin . end復合語句來包含多個語句。statement_list 代表一個或多個語句的列表。 statement_list

9、之內(nèi)每個語句都必須用分號（；）來結(jié)尾。 復合語句可以被標記。除非begin_label存在，否則end_label不能被給出，并且如果二者都存在，他們必須是同樣的。 使用多重語句需要客戶端能發(fā)送包含語句定界符;的查詢字符串。這個符號在命令行客戶端被用delimiter命令來處理。 改變查詢結(jié)尾定界符;（比如改變?yōu)?）使得; 可被用在子程序體中。 流程控制結(jié)構(gòu)語句：流程控制結(jié)構(gòu)語句： l if語句 if search_condition then statement_list elseif search_condition then statement_list . else statement

10、_list end if if實現(xiàn)了一個基本的條件構(gòu)造。如果search_condition求值為真，相應(yīng)的sql語句列表被執(zhí)行。如果沒有search_condition匹配， 在else子句里的語句列表被執(zhí)行。statement_list可以包括一個或多個語句。 舉例： delimiter / create procedure p1(in parameter1 int) begin declare variable1 int; set variable1 = parameter1 + 1; if variable1 = 0 then insert into t values (17); en

11、d if; if parameter1 = 0 then update t set s1 = s1 + 1; else update t set s1 = s1 + 2; end if; end; / delimiter ; 流程控制結(jié)構(gòu)語句：流程控制結(jié)構(gòu)語句： l case語句 case case_value when when_value then statement_list when when_value then statement_list . else statement_list end case or: case when search_condition then stat

12、ement_list when search_condition then statement_list . else statement_list end case 存儲程序的case語句實現(xiàn)一個復雜的條件構(gòu)造。如果search_condition 求值為真，相應(yīng)的sql被執(zhí)行。如果沒有搜索條件匹配， 在else子句里的語句被執(zhí)行。 舉例： create procedure p2 (in parameter1 int) begin declare variable1 int; set variable1 = parameter1 + 1; case variable1 when 0 then

13、 insert into t values (17); when 1 then insert into t values (18); else insert into t values (19); end case; end; / 流程控制結(jié)構(gòu)語句：流程控制結(jié)構(gòu)語句： l 循環(huán)語句 while end while loop end loop repeat end repeat goto 前三種是標準的循環(huán)方式，至于goto就如c語言里的goto一樣，盡量少用！ 在循環(huán)中還穿插一些循環(huán)控制語句，如leave（類似c語言的break）、iterate（類似c語言的continue）等。 leave

14、語句 leave label 這個語句被用來退出任何被標注的流程控制構(gòu)造。它和begin . end或循環(huán)一起被使用。 iterate語句 iterate label iterate只可以出現(xiàn)在loop, repeat, 和while語句內(nèi)。iterate意思為：再次循環(huán)。 流程控制結(jié)構(gòu)語句：流程控制結(jié)構(gòu)語句： l 循環(huán)語句 while end while 舉例： create procedure p4 () begin declare v int; set v = 0; while v = 5 then leave loop_label; end if; end loop; end; / b

15、egin_label: loop statement_list end loop end_label loop允許某特定語句或語句群的重復執(zhí)行，實現(xiàn)一個簡單的循環(huán)構(gòu)造。在循環(huán)內(nèi)的語句一直重復直到循環(huán)被退出， 退出通常伴隨著一個leave 語句。 流程控制結(jié)構(gòu)語句：流程控制結(jié)構(gòu)語句： l 循環(huán)語句 repeat end repeat 舉例： create procedure p6 () begin declare v int; set v = 0; repeat insert into t values (v); set v = v + 1; until v = 5 end repeat; en

16、d; / 功能與while差不多，差別是在執(zhí)行一次后檢查，而while是在開始時檢查，累死dowhile功能。 流程控制結(jié)構(gòu)語句：流程控制結(jié)構(gòu)語句： 補充：迭代（iterate）語句 create procedure p7 () begin declare v int; set v = 0; loop_label: loop if v = 3 then set v = v + 1; iterate loop_label; end if; insert into t values (v); set v = v + 1; if v = 5 then leave loop_label; end if

17、; end loop; end; / 注釋語法：注釋語法： mysql存儲過程可使用兩種風格的注釋 雙模杠：-，該風格一般用于單行注釋 c風格：/* 注釋內(nèi)容 */， 一般用于多行注釋 使用權(quán)限：使用權(quán)限： 關(guān)于存儲過程的權(quán)限管理，一種是sql security invoker，一種是sql security definer,也就是一種是調(diào)用者 權(quán)限，一種是定義者權(quán)限，如果使用第一種，那么執(zhí)行的時候是以執(zhí)行者本身的權(quán)限來操作存儲過程中包含的表。如 果是第二種，那么執(zhí)行的時候，是以該存儲過程的定義者權(quán)限來操作。 條件和異常處理程序：條件和異常處理程序： declare handler_type

18、handler for condition_value,. sp_statement handler_type: continue | exit condition_value: sqlstate value sqlstate_value | condition_name | sqlwarning | not found | sqlexception 這個語句指定每個可以處理一個或多個條件的處理程序。如果產(chǎn)生一個或多個條件，指定的語句被執(zhí)行。 對一個continue處理程序，當前子程序的執(zhí)行在執(zhí)行處理程序語句之后繼續(xù)。對于exit處理程序，當前 begin.end復合語句的執(zhí)行被終止。undo

19、 處理程序類型語句還不被支持。 sqlwarning是對所有以01開頭的sqlstate代碼的速記。 not found是對所有以02開頭的sqlstate代碼的速記。 sqlexception是對所有沒有被sqlwarning或not found捕獲的sqlstate代碼的速記。 聲明自定義條件： declare condition_name condition for condition_value condition_value: sqlstate value sqlstate_value 條件和異常處理程序：條件和異常處理程序： 舉例： create table test.t (s1

20、int,primary key (s1); delimiter / create procedure handlerdemo () begin declare continue handler for sqlstate 23000 set x2 = 1; set x = 1; insert into test.t values (1); set x = 2; insert into test.t values (1); set x = 3; end;/ delimiter ; 游標：游標： 聲明游標 declare cursor_name cursor for select_statement 這個語句聲明一個光標。也可以在子程序中定義多個光標，但是一個塊中的每一個光標必須有唯一的名字。 打開游標 open cursor_name 這個語句打開先前聲明的光標。 游標fetch fetch cursor_name into var_name , var_name . 這個語句用指定的打開光標讀取下一行（如果有下一行的話），并且前進光標