MySQL_存儲過程.ppt

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

2、平臺的應用程序需要去執(zhí)行相同的數(shù)據(jù)庫操作. (避免為各個程序都開發(fā)相同的功能) 安全性要求較高時,使用存儲程序和函數(shù)為一些公共的操作 (程序員不是數(shù)據(jù)庫管理員他可能不了解很多數(shù)據(jù)庫的機制比如事務,錯誤回滾等.銀行轉(zhuǎn)帳問題可以解釋.) 減輕客戶端應用程序的負擔 (讓更多的操作由數(shù)據(jù)庫服務器執(zhí)行),什么情況適合用存儲過程?, 存儲過程介紹,1） 提升性能由于客戶端和服務器間傳遞的信息量少了 2） 存儲過程允許你使用數(shù)據(jù)庫上的豐富的庫函數(shù) 程序員減少編碼量,存儲過程有哪些好處?, 存儲過程介紹,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è) 查一下其它刪除存儲過程與修改存儲過程的語法 Alter Procedure和Drop Procedure,存儲過程的語法, 存儲過程介紹,例子: delimiter / CREATE DEFINER = adminlocalhost PROCEDURE account_count() SQL SECURITY INVOKER BEGIN SELECT Number of accounts:, COUNT(*) FROM mysql.user; END / DEFINER參數(shù)代表定義這個存儲過程的用戶,只有super權(quán)限的

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

6、1那么execute routine和alter routine權(quán)限 會隨create routine權(quán)限而自動創(chuàng)建. 其它: MySQL帳戶有28個如下權(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)表: 實表: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,存儲過程的權(quán)限, 存儲過程介紹,如何給用戶分配權(quán)限? grant語句 例子: 在某張表的某些列上賦值某個權(quán)限 create user testlocalhost identified by 112233; grant select(serviceid,channelid) on kepu.mo_sms to testlocalhost; 在某張表上賦值多個權(quán)限 grant se

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

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

11、e = expr .(用戶自定義變量) 局部變量可見性? 在定義它的BeginEnd程序塊都中可見,除了內(nèi)嵌的BeginEnd程序 塊中還有同名變量 作業(yè)查詢一下是否還有其它變量類型,聲明變量,游標,條件,處理,信號, 復合SQL語句,運算符的種類? 算術(shù)運算符,邏輯運算符,賦值運算符,比較運算符 算術(shù)運算符:+ - * / % = 賦值的方法: 第一種: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;,變量運算符及賦值, 復合SQL語句,MySQL支持的流程控制語法: (1)順序 (2)選擇 (3)循環(huán) 選擇:IF語句,CASE語句 循環(huán):LOOP語句,Leave語句,Iterate語句,Repeat語句,While語句. IF語句語法: 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 /,流程控制, 復合SQL語句,case語句語法: 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 /,流程控制, 復合SQL語句,LOOP語句語法: 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一起達到循環(huán)控制),流程控制, 復合SQL語

16、句,ITERATE語句語法: REPEAT語句語法: WHILE語句語法: 作業(yè)去查詢一下MySQL的幫助手冊,流程控制, 復合SQL語句,MySQL提供的庫函數(shù)種類: 1)字符函數(shù) 2)數(shù)值函數(shù) 3)日期和時間函數(shù) 4)全文搜索函數(shù) 5)強制轉(zhuǎn)換函數(shù) 6)XML函數(shù) 7)位函數(shù) 8)加密和壓縮函數(shù) 9)數(shù)學函數(shù) 10)其它(隨機數(shù),IP地址轉(zhuǎn)換,UUID函數(shù)等等) 作業(yè)具體函數(shù)的原型請去查詢MySQL幫助文檔,庫函數(shù), 復合SQL語句,什么是游標? 游標其實一種獲取數(shù)據(jù)的一種方式它是一種嵌入式SQL語句是對 SQL語句的拓展. 游標的特點? 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,游標的介紹, 復合SQL語句,游標的使用步驟: 1)聲明游標 DECLARE cursor_name CURSOR FOR select_statement 2)打開游標 OPEN cursor_name 3)獲取數(shù)據(jù) FETCH cursor_name INTO var_name , var_n

18、ame . 4)關(guān)閉游標 CLOSE cursor_name 例:delimiter / create procedure test_cursor() begin declare done int default 0; declare var_usermobile varchar(20); declare var_smscontent varchar(20); /*游標在聲明處理前先聲明，而變量最先聲明*/ 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 /,游標的介紹, 復合SQL語句,Conditions and Handlers 為何需要狀態(tài)和處理? 因為SQL語句執(zhí)行的時候會報一些異常錯誤或者一些警告,這些錯誤會 被數(shù)據(jù)庫自動捕捉到,從而影響存儲程序的執(zhí)行.默認如果出現(xiàn)異常錯誤后 則程序退出執(zhí)行為了避免程序無緣無故的停止(在用戶看來)所以需要聲明 狀態(tài)和處理,以便出錯時進入錯誤處理語句記錄下錯誤類型. 聲明狀態(tài): DECLARE condition_name CONDIT

21、ION FOR condition_value condition_value: SQLSTATE VALUE sqlstate_value | mysql_error_code 聲明狀態(tài)的作用只是為了定義一個錯誤條件使其更形象化一點而handler 可以直接引用此處的condition name做處理(錯誤條件是系統(tǒng)中已定義). 例:declare Pri_key_Not_Unique condition for SQLSTATE VALUE 23000,狀態(tài)和處理的介紹, 復合SQL語句,處理的聲明: 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 處理的作用? 指明當發(fā)生特定事件(狀態(tài))時需執(zhí)行的處理語句.Statement語句可以是單條語句也可以為 復合語句. 處理類型說明: Continue代表在執(zhí)行完處理語句后當前存儲程序繼續(xù)執(zhí)行. Exit代表從

23、聲明了handler的BeginEnd程序塊的退出 Undo類型MySQL還未實現(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)和處理的介紹, 復合SQL語句,正在建設中,信號的介紹, 復合SQL語句,什么是預處理語句Prepare Statement? 預處理是一種在服務器端編譯過的可以執(zhí)行的SQL語句模板,只需被 解析一次就可以反復調(diào)用再次使用的時候省去了重復分析,重復編譯,重復 優(yōu)化的環(huán)節(jié).預處理語句使用更少的資源執(zhí)行速度更快. 復合語句中也可以使用預處理指令. 預處理語句的使用步驟: 1)準備預處理

25、的SQL語句 PREPARE stmt_name FROM preparable_stmt 2)執(zhí)行預處理語句 EXECUTE stmt_name USING var_name , var_name . 3)回收或刪除預處理語句 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è) 查詢一下預處理語句還有其它什么作用對預處理語句的解釋以上可能并不十分準確.,其它, 實用例子分析,例一:利用事件往一張表中插數(shù)據(jù),然后再把表中的數(shù)據(jù)通過存儲過程進 行計算插入到另一張表中. 1) 創(chuàng)建兩張臨時表 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ù)據(jù),然后再把表中的數(shù)據(jù)通過存儲過程進 行計算插入到另一張表中. 3)創(chuàng)建操作表t_tempsu

28、ms的存儲過程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ù)據(jù),然后再把表中的數(shù)據(jù)通過存儲過程進 行計算插入到另一張表中. 4)創(chuàng)建運行存儲過程t_sum的事件t_tempsums_sums Create event t_tempsums_sums On schedule Every 4 minute Starts now() Do Begin Call t_sum(); End /,實用例子分析, 實用例子分析,例二:包含事務,參數(shù),嵌套調(diào)用,游標,循環(huán)等 1)存儲過程代碼注釋 - - rep_shadow_rs - 用來處理信息的增加，更

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

32、c_id int default -1; declare iMax_rep_sync_id int default -1; - 如果出現(xiàn)異常，或自動處理并rollback,但不再通知調(diào)用方了 - 如果希望應用獲得異常，需要將下面這一句，以及啟動事務和提交事務的語句全部去掉 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; - 下一個數(shù)字 set iLast_rep_sync_id=iLast_rep_sync_id+1; - 設置默認的返回值為0：失敗 set rtn=0; - 啟動事務 start transaction; - 查找最大編號 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; - 運行沒有異常，提交事務 commit; - 設置返回值為1 set rtn=1; end /,實用例子分析, 實用例子分析,例二:包含事務,參數(shù),嵌套調(diào)用,游標,循環(huán)等 3)真正對rs0811表進行數(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