MySQL_存儲(chǔ)過(guò)程.ppt

1、MySQL 存儲(chǔ)過(guò)程, 存儲(chǔ)過(guò)程介紹,一、什么是存儲(chǔ)過(guò)程? 二、什么情況適合用存儲(chǔ)過(guò)程? 三、存儲(chǔ)過(guò)程有哪些好處? 四、存儲(chǔ)過(guò)程的語(yǔ)法 五、存儲(chǔ)過(guò)程的權(quán)限, 存儲(chǔ)過(guò)程介紹,存儲(chǔ)過(guò)程又稱存儲(chǔ)程序它是一系列保存在數(shù)據(jù)庫(kù)服務(wù)器上的SQL語(yǔ)句可以供客戶端程序調(diào)用它. MySQL的存儲(chǔ)程序可分以下四大類: (1)存儲(chǔ)過(guò)程 (2)函數(shù) (3)觸發(fā)器 (4)事件 與存儲(chǔ)過(guò)程相關(guān)的系統(tǒng)表為Mysql.Proc表 作業(yè):查一下以下的網(wǎng)頁(yè) 相關(guān)資源： 存儲(chǔ)過(guò)程用戶論壇： 存儲(chǔ)過(guò)程FAQ(Frequently asked questions) ,什么是存儲(chǔ)過(guò)程?, 存儲(chǔ)過(guò)程介紹,當(dāng)多個(gè)用不同語(yǔ)言開(kāi)發(fā)的應(yīng)用程序或不同

2、平臺(tái)的應(yīng)用程序需要去執(zhí)行相同的數(shù)據(jù)庫(kù)操作. (避免為各個(gè)程序都開(kāi)發(fā)相同的功能) 安全性要求較高時(shí),使用存儲(chǔ)程序和函數(shù)為一些公共的操作 (程序員不是數(shù)據(jù)庫(kù)管理員他可能不了解很多數(shù)據(jù)庫(kù)的機(jī)制比如事務(wù),錯(cuò)誤回滾等.銀行轉(zhuǎn)帳問(wèn)題可以解釋.) 減輕客戶端應(yīng)用程序的負(fù)擔(dān) (讓更多的操作由數(shù)據(jù)庫(kù)服務(wù)器執(zhí)行),什么情況適合用存儲(chǔ)過(guò)程?, 存儲(chǔ)過(guò)程介紹,1） 提升性能由于客戶端和服務(wù)器間傳遞的信息量少了 2） 存儲(chǔ)過(guò)程允許你使用數(shù)據(jù)庫(kù)上的豐富的庫(kù)函數(shù) 程序員減少編碼量,存儲(chǔ)過(guò)程有哪些好處?, 存儲(chǔ)過(guò)程介紹,CREATE DEFINER = user | CURRENT_USER PROCEDURE sp_nam

3、e (proc_parameter,.) characteristic . routine_body proc_parameter: IN | OUT | INOUT param_name type type: Any valid MySQL data type characteristic: COMMENT string | LANGUAGE SQL | NOT DETERMINISTIC | CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | INVOKER routine_

4、body: Valid SQL routine statement 作業(yè) 查一下其它刪除存儲(chǔ)過(guò)程與修改存儲(chǔ)過(guò)程的語(yǔ)法 Alter Procedure和Drop Procedure,存儲(chǔ)過(guò)程的語(yǔ)法, 存儲(chǔ)過(guò)程介紹,例子: delimiter / CREATE DEFINER = adminlocalhost PROCEDURE account_count() SQL SECURITY INVOKER BEGIN SELECT Number of accounts:, COUNT(*) FROM mysql.user; END / DEFINER參數(shù)代表定義這個(gè)存儲(chǔ)過(guò)程的用戶,只有super權(quán)限的

5、用戶才可以把DEFINER指定為自己以外的帳號(hào) SQL SECURITY INVOKER代表調(diào)用這個(gè)存儲(chǔ)過(guò)程的帳戶以調(diào)用者的權(quán)限來(lái)執(zhí)行SQL語(yǔ)句,限制了沒(méi)有權(quán)限的用 戶對(duì)此存儲(chǔ)過(guò)程的使用,因?yàn)椴樵兿到y(tǒng)所有的帳號(hào)這個(gè)是需要許可的機(jī)密數(shù)據(jù) 作業(yè)查詢一下其它一些與存儲(chǔ)過(guò)程相關(guān)的關(guān)鍵字的含義如: Not Deterministic等,存儲(chǔ)過(guò)程的語(yǔ)法, 存儲(chǔ)過(guò)程介紹,存儲(chǔ)過(guò)程如何調(diào)用呢? Call 存儲(chǔ)過(guò)程名(參數(shù)1,參數(shù)2) 問(wèn)題: 是否阿貓阿狗都可以隨便調(diào)用存儲(chǔ)過(guò)程呢? 不,能執(zhí)行存儲(chǔ)過(guò)程的帳戶需要execute routine權(quán)限.如果系統(tǒng)參數(shù) automatic_sp_privileges =

6、1那么execute routine和alter routine權(quán)限 會(huì)隨create routine權(quán)限而自動(dòng)創(chuàng)建. 其它: MySQL帳戶有28個(gè)如下權(quán)限:select_priv, insert_priv, update_priv, delete_priv, create_priv, reload_priv, shutdown_priv, drop_priv, process_priv, file_priv, grant_priv, references_priv, index_priv, show_db_priv, alter_priv, super_priv, create_tmp_t

7、able_priv, lock_tables_priv, execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv, create_routine_priv, alter_routine_priv, create_user_priv, event_priv, trigger_priv 與權(quán)限相關(guān)的系統(tǒng)表: 實(shí)表:mysql.user, mysql.tables_priv, mysql.column_priv 虛表:Information_schema.user_privileges, In

8、formation_schema.table_privileges, Information_schema.schema_privileges, Information_schema.column_privileges,存儲(chǔ)過(guò)程的權(quán)限, 存儲(chǔ)過(guò)程介紹,如何給用戶分配權(quán)限? grant語(yǔ)句 例子: 在某張表的某些列上賦值某個(gè)權(quán)限 create user testlocalhost identified by 112233; grant select(serviceid,channelid) on kepu.mo_sms to testlocalhost; 在某張表上賦值多個(gè)權(quán)限 grant se

9、lect,drop,alter on kepu.mo_sms to testlocalhost; 作業(yè)查詢一下grant語(yǔ)句的具體語(yǔ)法能為實(shí)際用戶的需求規(guī)劃出該用戶的權(quán)限定義,存儲(chǔ)過(guò)程的權(quán)限, 復(fù)合SQL語(yǔ)句,一、BeginEnd程序塊 二、聲明變量,游標(biāo),條件,處理,信號(hào) 三、變量運(yùn)算符與賦值 四、流程控制 五、庫(kù)函數(shù) 六、游標(biāo)的介紹 七、 狀態(tài)和處理的介紹 八、信號(hào)的介紹 九、其它, 復(fù)合SQL語(yǔ)句,begin_label: BEGIN statement_list END end_label BeginEnd語(yǔ)法用來(lái)書(shū)寫(xiě)復(fù)合語(yǔ)句用在儲(chǔ)存程序中. 可以有空的復(fù)合語(yǔ)句表示什么都不做. Beg

10、in End BeginEnd程序塊可以被打上標(biāo)記在Loop,Repeat,While語(yǔ)句中會(huì)用到標(biāo)記 后面我們會(huì)看到. 其實(shí)BeginEnd就是包含一條或多條SQL語(yǔ)句的集合,里面可以包括任何程序 的三種基本流程順序,選擇,循環(huán).,BeginEnd, 復(fù)合SQL語(yǔ)句,變量的種類? 1)局部變量 2)用戶自定義變量 局部變量和用戶自定義變量的區(qū)別? 局部變量變量必須聲明后再使用而用戶自定義變量可以直接使用. 變量的聲明方式: DECLARE var_name , var_name . type DEFAULT value (局部變量) SET var_name = expr , var_nam

11、e = expr .(用戶自定義變量) 局部變量可見(jiàn)性? 在定義它的BeginEnd程序塊都中可見(jiàn),除了內(nèi)嵌的BeginEnd程序 塊中還有同名變量 作業(yè)查詢一下是否還有其它變量類型,聲明變量,游標(biāo),條件,處理,信號(hào), 復(fù)合SQL語(yǔ)句,運(yùn)算符的種類? 算術(shù)運(yùn)算符,邏輯運(yùn)算符,賦值運(yùn)算符,比較運(yùn)算符 算術(shù)運(yùn)算符:+ - * / % = 賦值的方法: 第一種:SET var_name = expr , var_name = expr . 例:Declare a int; set a := 100; 第二種:SELECT col_name , col_name . INTO var_name , v

12、ar_name . table_expr 例:Declare x int; select id into x from test.t1;,變量運(yùn)算符及賦值, 復(fù)合SQL語(yǔ)句,MySQL支持的流程控制語(yǔ)法: (1)順序 (2)選擇 (3)循環(huán) 選擇:IF語(yǔ)句,CASE語(yǔ)句 循環(huán):LOOP語(yǔ)句,Leave語(yǔ)句,Iterate語(yǔ)句,Repeat語(yǔ)句,While語(yǔ)句. IF語(yǔ)句語(yǔ)法: IF search_condition THEN statement_list ELSEIF search_condition THEN statement_list . ELSE statement_list END

13、IF 例: delimiter / create procedure max_value(in a int, in b int) begin declare maxint int; set maxint:=0; IF a b THEN set maxint:=a; else set maxint:=b; end if; select maxint; end /,流程控制, 復(fù)合SQL語(yǔ)句,case語(yǔ)句語(yǔ)法: CASE case_value WHEN when_value THEN statement_list WHEN when_value THEN statement_list . ELSE

14、 statement_list END CASE Or: CASE WHEN search_condition THEN statement_list WHEN search_condition THEN statement_list . ELSE statement_list END CASE 例: delimiter / create procedure boy_or_girl(in sex char) begin declare msg char(20); case sex when b then set msg:=You are boy; when g then set msg:=Yo

15、u are girl; else set msg:=You are RenYao; end case; select msg; end /,流程控制, 復(fù)合SQL語(yǔ)句,LOOP語(yǔ)句語(yǔ)法: begin_label: LOOP statement_list END LOOP end_label 例: create procedure testleave(in i int) Begin proc: begin if i 1000 then leave proc; else select i; end if; end; end / (Loop需要與Leave一起達(dá)到循環(huán)控制),流程控制, 復(fù)合SQL語(yǔ)

16、句,ITERATE語(yǔ)句語(yǔ)法: REPEAT語(yǔ)句語(yǔ)法: WHILE語(yǔ)句語(yǔ)法: 作業(yè)去查詢一下MySQL的幫助手冊(cè),流程控制, 復(fù)合SQL語(yǔ)句,MySQL提供的庫(kù)函數(shù)種類: 1)字符函數(shù) 2)數(shù)值函數(shù) 3)日期和時(shí)間函數(shù) 4)全文搜索函數(shù) 5)強(qiáng)制轉(zhuǎn)換函數(shù) 6)XML函數(shù) 7)位函數(shù) 8)加密和壓縮函數(shù) 9)數(shù)學(xué)函數(shù) 10)其它(隨機(jī)數(shù),IP地址轉(zhuǎn)換,UUID函數(shù)等等) 作業(yè)具體函數(shù)的原型請(qǐng)去查詢MySQL幫助文檔,庫(kù)函數(shù), 復(fù)合SQL語(yǔ)句,什么是游標(biāo)? 游標(biāo)其實(shí)一種獲取數(shù)據(jù)的一種方式它是一種嵌入式SQL語(yǔ)句是對(duì) SQL語(yǔ)句的拓展. 游標(biāo)的特點(diǎn)? 1)Asensitive: The server

17、may or may not make a copy of its result table 2)Read only: Not updatable 3)Nonscrollable: Can be traversed only in one direction and cannot skip rows,游標(biāo)的介紹, 復(fù)合SQL語(yǔ)句,游標(biāo)的使用步驟: 1)聲明游標(biāo) DECLARE cursor_name CURSOR FOR select_statement 2)打開(kāi)游標(biāo) OPEN cursor_name 3)獲取數(shù)據(jù) FETCH cursor_name INTO var_name , var_n

18、ame . 4)關(guān)閉游標(biāo) CLOSE cursor_name 例:delimiter / create procedure test_cursor() begin declare done int default 0; declare var_usermobile varchar(20); declare var_smscontent varchar(20); /*游標(biāo)在聲明處理前先聲明，而變量最先聲明*/ declare get_mo_sms cursor for select usermobile,smscontent from mo_sms limit 2; declare contin

19、ue handler for not found set done = 1; /* declare continue handler for SQLSTATE value 02000 set done = 1; */ /* NOT FOUND is shorthand for the class of SQLSTATE values that begin with 02 */ open get_mo_sms; read_loop: loop fetch get_mo_sms into var_usermobile,var_smscontent; if done then leave read_

20、loop; end if; select var_usermobile,var_smscontent; end loop; close get_mo_sms; end /,游標(biāo)的介紹, 復(fù)合SQL語(yǔ)句,Conditions and Handlers 為何需要狀態(tài)和處理? 因?yàn)镾QL語(yǔ)句執(zhí)行的時(shí)候會(huì)報(bào)一些異常錯(cuò)誤或者一些警告,這些錯(cuò)誤會(huì) 被數(shù)據(jù)庫(kù)自動(dòng)捕捉到,從而影響存儲(chǔ)程序的執(zhí)行.默認(rèn)如果出現(xiàn)異常錯(cuò)誤后 則程序退出執(zhí)行為了避免程序無(wú)緣無(wú)故的停止(在用戶看來(lái))所以需要聲明 狀態(tài)和處理,以便出錯(cuò)時(shí)進(jìn)入錯(cuò)誤處理語(yǔ)句記錄下錯(cuò)誤類型. 聲明狀態(tài): DECLARE condition_name CONDIT

21、ION FOR condition_value condition_value: SQLSTATE VALUE sqlstate_value | mysql_error_code 聲明狀態(tài)的作用只是為了定義一個(gè)錯(cuò)誤條件使其更形象化一點(diǎn)而handler 可以直接引用此處的condition name做處理(錯(cuò)誤條件是系統(tǒng)中已定義). 例:declare Pri_key_Not_Unique condition for SQLSTATE VALUE 23000,狀態(tài)和處理的介紹, 復(fù)合SQL語(yǔ)句,處理的聲明: DECLARE handler_type HANDLER FOR condition_v

22、alue , condition_value . statement handler_type: CONTINUE | EXIT | UNDO condition_value: SQLSTATE VALUE sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code 處理的作用? 指明當(dāng)發(fā)生特定事件(狀態(tài))時(shí)需執(zhí)行的處理語(yǔ)句.Statement語(yǔ)句可以是單條語(yǔ)句也可以為 復(fù)合語(yǔ)句. 處理類型說(shuō)明: Continue代表在執(zhí)行完處理語(yǔ)句后當(dāng)前存儲(chǔ)程序繼續(xù)執(zhí)行. Exit代表從

23、聲明了handler的BeginEnd程序塊的退出 Undo類型MySQL還未實(shí)現(xiàn). 例: delimiter / CREATE TABLE kepu.t (s1 INT, PRIMARY KEY (s1); create procedure handlerdemo() begin declare Pri_key_Not_Unique condition for SQLSTATE VALUE 23000; declare continue handler for Pri_key_Not_Unique select “Primary key is already exist,can not be

24、 repeat!”; set x=1; insert into kepu.t values (1); set x=2; insert into kepu.t values (1); set x=3; end /,狀態(tài)和處理的介紹, 復(fù)合SQL語(yǔ)句,正在建設(shè)中,信號(hào)的介紹, 復(fù)合SQL語(yǔ)句,什么是預(yù)處理語(yǔ)句Prepare Statement? 預(yù)處理是一種在服務(wù)器端編譯過(guò)的可以執(zhí)行的SQL語(yǔ)句模板,只需被 解析一次就可以反復(fù)調(diào)用再次使用的時(shí)候省去了重復(fù)分析,重復(fù)編譯,重復(fù) 優(yōu)化的環(huán)節(jié).預(yù)處理語(yǔ)句使用更少的資源執(zhí)行速度更快. 復(fù)合語(yǔ)句中也可以使用預(yù)處理指令. 預(yù)處理語(yǔ)句的使用步驟: 1)準(zhǔn)備預(yù)處理

25、的SQL語(yǔ)句 PREPARE stmt_name FROM preparable_stmt 2)執(zhí)行預(yù)處理語(yǔ)句 EXECUTE stmt_name USING var_name , var_name . 3)回收或刪除預(yù)處理語(yǔ)句 DEALLOCATE | DROP PREPARE stmt_name 例: delimiter / create procedure testprepare() begin prepare getdate from select createdt from sms_mt_submit_history limit 1; execute getdate; DEALLOC

26、ATE prepare getdate; end / 作業(yè) 查詢一下預(yù)處理語(yǔ)句還有其它什么作用對(duì)預(yù)處理語(yǔ)句的解釋以上可能并不十分準(zhǔn)確.,其它, 實(shí)用例子分析,例一:利用事件往一張表中插數(shù)據(jù),然后再把表中的數(shù)據(jù)通過(guò)存儲(chǔ)過(guò)程進(jìn) 行計(jì)算插入到另一張表中. 1) 創(chuàng)建兩張臨時(shí)表 Create table t_org(id int not null aotu_increment,v1 int,v2 int, v3 int,primary key(id); Create table t_ tempsums (id int not null aotu_increment,v1sum int,v2sum in

27、t, v3sum int,primary key(id); 2)創(chuàng)建表t_org的事件 Delimiter / Create event t_org_insert On schedule Every 2 minute Starts now() Do Begin Insert into t_org(v1,v2,v3) values(round(rand()*10),round(rand()*10),round(rand()*10); End /,實(shí)用例子分析, 實(shí)用例子分析,例一:利用事件往一張表中插數(shù)據(jù),然后再把表中的數(shù)據(jù)通過(guò)存儲(chǔ)過(guò)程進(jìn) 行計(jì)算插入到另一張表中. 3)創(chuàng)建操作表t_tempsu

28、ms的存儲(chǔ)過(guò)程t_sum Delimiter / Create procedure t_sum Begin Declare v1sum int default 0; Declare v2sum int default 0; Declare v3sum int default 0; Declare sum1 int default 0; Declare sum2 int default 0; Declare sum3 int default 0; Declare flag int default 0; Declare cur1 cursor for select v1,v2,v3 from t_

29、org; Declare continue handler for sqlstate 02000 set flag = 1; Open cur1; Fetch cur1 into v1sum,v2sum,v3sum; While(flag !=1) do Set sum1 = sum1+v1sum; Set sum2 = sum2+v2sum; Set sum3 = sum3+v3sum; Fetch cur1 into v1sum,v2sum,v3sum; End while; Insert into t_tempsums(v1sum,v2sum,v3sum) values(sum1,sum

30、2,sum3); Close cur1; End /,實(shí)用例子分析, 實(shí)用例子分析,例一:利用事件往一張表中插數(shù)據(jù),然后再把表中的數(shù)據(jù)通過(guò)存儲(chǔ)過(guò)程進(jìn) 行計(jì)算插入到另一張表中. 4)創(chuàng)建運(yùn)行存儲(chǔ)過(guò)程t_sum的事件t_tempsums_sums Create event t_tempsums_sums On schedule Every 4 minute Starts now() Do Begin Call t_sum(); End /,實(shí)用例子分析, 實(shí)用例子分析,例二:包含事務(wù),參數(shù),嵌套調(diào)用,游標(biāo),循環(huán)等 1)存儲(chǔ)過(guò)程代碼注釋 - - rep_shadow_rs - 用來(lái)處理信息的增加，更

31、新和刪除 - 每次只更新上次以來(lái)沒(méi)有做過(guò)的數(shù)據(jù) - 根據(jù)不同的標(biāo)志位 - 需要一個(gè)輸出的參數(shù)， - 如果返回為0，則調(diào)用失敗，事務(wù)回滾 - 如果返回為1，調(diào)用成功，事務(wù)提交 - - 測(cè)試方法 - call pro_rep_shadow_rs(rtn); - select rtn; -,實(shí)用例子分析, 實(shí)用例子分析,例二:包含事務(wù),參數(shù),嵌套調(diào)用,游標(biāo),循環(huán)等 2) 選擇區(qū)間進(jìn)行數(shù)據(jù)增加,更新,刪除 create procedure pro_rep_shadow_rs(out rtn int) begin - 聲明變量，所有的聲明必須在非聲明的語(yǔ)句前面 declare iLast_rep_syn

32、c_id int default -1; declare iMax_rep_sync_id int default -1; - 如果出現(xiàn)異常，或自動(dòng)處理并rollback,但不再通知調(diào)用方了 - 如果希望應(yīng)用獲得異常，需要將下面這一句，以及啟動(dòng)事務(wù)和提交事務(wù)的語(yǔ)句全部去掉 declare exit handler for sqlexception rollback; - 查找上一次的 select eid into iLast_rep_sync_id from rep_de_proc_log where tbl=rep_shadow_rs; - 如果不存在，則增加一行 if iLast_rep

33、_sync_id=-1 then insert into rep_de_proc_log(rid,eid,tbl) values(0,0,rep_shadow_rs); set iLast_rep_sync_id = 0; end if; - 下一個(gè)數(shù)字 set iLast_rep_sync_id=iLast_rep_sync_id+1; - 設(shè)置默認(rèn)的返回值為0：失敗 set rtn=0; - 啟動(dòng)事務(wù) start transaction; - 查找最大編號(hào) select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs; -

34、 有新數(shù)據(jù) if iMax_rep_sync_id=iLast_rep_sync_id then - 調(diào)用 call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id); - 更新日志 update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl=rep_shadow_rs; end if; - 運(yùn)行沒(méi)有異常，提交事務(wù) commit; - 設(shè)置返回值為1 set rtn=1; end /,實(shí)用例子分析, 實(shí)用例子分析,例二:包含事務(wù),參數(shù),嵌套調(diào)用,游標(biāo),循環(huán)等 3)真正對(duì)rs0811表進(jìn)行數(shù)據(jù)的增加,更新和刪除 delimite / drop procedure if exists pro_rep_shadow_rs_do / create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int) begin declare iRep_operationtype varchar(1); declare iRep_st