數(shù)據(jù)庫系統(tǒng)概念教學(xué)課件3procedure 文檔資料

1、Functions and Procedural,Constructs,函數(shù)和過程結(jié)構(gòu),陳良育,Outline,?,復(fù)習(xí)：?jiǎn)尉?SQL,總結(jié),?,函數(shù)和過程結(jié)構(gòu)定義、優(yōu)點(diǎn)和使用場(chǎng)景。,?,結(jié)構(gòu)化程序語言回顧,?,SQL,過程結(jié)構(gòu),?,變量定義，輸入輸出參數(shù),?,賦值語句,?,選擇語句,?,循環(huán)語句,?,游標(biāo),?,表變量,?,異常處理,?,運(yùn)行和調(diào)試,?,實(shí)際案例學(xué)習(xí),?,總結(jié),單句,SQL,語句,總結(jié),?,(7) SELECT (8) DISTINCT (10) ,?,(1) FROM ,?,(3) JOIN ,?,(2) ON ,?,(4) WHERE ,?,(5) GROUP BY ,?,

2、(6) HAVING ,?,(9) ORDER BY ,?,Update t_stu set stu_name = ,張三, age = 20 where stu_id = 2,?,Delete from t_stu where stu_id = 2 and stu_name = ,張三,?,Insert into t_stu(stu_id, stu_name) values(2, ,張三,),?,Create table t_stu (stu_id int , stu_name varchar(100),?,Drop table t_stu,所學(xué)過的,SQL,關(guān)鍵字,?,Create, in

3、sert, select, update, delete, drop, table,?,Int, char, varchar, datetime,?,Null, is not null, is null,?,From, where, and, or, not, distinct, like (%), as,?, =, , !=, between and,?,Group by, having,?,Count, sum, min, max, avg,?,order by, asc, desc,?,Join, left outer join, right outer join, full join,

4、?,in, exists, union, union all, with, case when then else,?,View, primary key, foreign key, constraint, check,?,Round, ascii, char, left, len, lower, ltrim,replace,reverse, right,rtrim,space,stuff,substring,upper, convert, datepart, getdate,?,All, any, some, minus, except, intersect (,能不用盡量不用,),過程結(jié)構(gòu)

5、,?,過程結(jié)構(gòu)：即按照第二、三代編程語言的,結(jié)構(gòu)來編寫多句的,SQL,程序。,?,SQL2019,規(guī)范：,Function, Procedure,method 3,種數(shù)據(jù)庫對(duì)象,?,實(shí)現(xiàn)以上,3,種對(duì)象，可以使用數(shù)據(jù)庫內(nèi)部語,言,sql,或者外部語言,(c+,java,c#, and etc).,?,function, routine, subroutine, method,procedure,區(qū)別,?,Function, Procedure.,重點(diǎn)。,存儲(chǔ)過程優(yōu)點(diǎn),/,缺點(diǎn),?,預(yù)編譯，存儲(chǔ)過程預(yù)先編譯好放在數(shù)據(jù)庫內(nèi)，減少編譯所耗費(fèi)的時(shí)間,.,?,緩存，編譯好的存儲(chǔ)過程會(huì)進(jìn)入緩存，所以對(duì)經(jīng)常

6、執(zhí)行的存儲(chǔ)過程，,除了第一次執(zhí)行外，其他次執(zhí)行的速度會(huì)有明顯提高,.,?,減少網(wǎng)絡(luò)傳輸，特別對(duì)于處理一些數(shù)據(jù)的存儲(chǔ)過程，不必像直接用,sql,語句實(shí)現(xiàn)那樣多次傳送數(shù)據(jù)到客戶端,.,?,性能更快，利用數(shù)據(jù)庫中向量化操作和眾多具有較高性能的系統(tǒng)函數(shù),.,?,更好的封裝，用戶不需要了解內(nèi)部具體的表和數(shù)據(jù)等信息,.,?,更好的安全性，防止,sql,注入。,?,缺點(diǎn)：,?,增加程序員學(xué)習(xí)成本，需要多學(xué)一門語言。,?,開發(fā)調(diào)試工具不如普通程序語言，函數(shù)庫,API,不如普通程序語言豐富。,?,增加數(shù)據(jù)庫的工作負(fù)載。,?,可移植性較差。如果換數(shù)據(jù)庫，那么需要重新編寫存儲(chǔ)過程或者函數(shù)。,?,存儲(chǔ)過程是天使,or

7、,魔鬼？,Tradeoff,多層系統(tǒng)。,系統(tǒng),Trade-off,普通程序語言,(1),?,賦值,x=1; x:=1; x=1,?,選擇,if(1=x) x=2; else x=3; ,普通程序語言,(2),?,循環(huán)語句：,while, do.while,?,For, foreach,普通程序語言,(3),?,函數(shù),function,?,函數(shù)調(diào)用,/,遞歸,學(xué)習(xí)程序語言的步驟,?,變量定義，數(shù)字，字符串。,?,1+1,，數(shù)字字符串相互轉(zhuǎn)化,?,If.else, while, for, case/switch,語句。,?,數(shù)組,?,函數(shù)定義和調(diào)用,?,系統(tǒng)函數(shù)庫,?,異常處理,?,看代碼，抄代碼

8、，改代碼。,10%,?,高級(jí)特性。,?,一個(gè)語言的學(xué)習(xí)，只有在項(xiàng)目實(shí)踐后，才能夠真正掌握。,SQL Server,存儲(chǔ)過程,(1),?,創(chuàng)建,Create procedure pr_t2 as select a, b from t2,?,執(zhí)行,Execute pr_t2,或者,exec pr_t2,?,刪除,Drop procedure pr_t2,?,修改,alter procedure pr_t2 as .,?,Create procedure pr_t2 as select a, b from t2,?,Go -,表示執(zhí)行上一句話,?,-,注釋,?,/* */,注釋,SQL Server

9、,存儲(chǔ)過程,(2),?,帶參數(shù)的存儲(chǔ)過程,?,Create procedure pr_t2,?,stu_id int, -,形式入?yún)??,stu_name varchar(10),?,As,?,Begin,?,SET NOCOUNT ON; -,屏蔽顯示多少行受影響的信息,?,Select stu_id, stu_name from t_stu where stu_id = stu_id and,stu_name like stu_name,?,End,?,執(zhí)行,?,Execute pr_t2 stu_id=1, stu_name=%tom%,?,Execute pr_t2 1, %tom%,

10、SQL Server,存儲(chǔ)過程,(3),?,CREATE PROCEDURE pr_stu,?,age int,?,stu_id int OUTPUT,?,stu_id_str varchar OUT,?,AS,?,set stu_id = (SELECT MAX(stu_id) from t_stu where age ,age);,?,select stu_id_str = convert(varchar, stu_id);,?,GO,?,執(zhí)行,?,declare a int, b varchar,?,exec pr_stu age=30, stu_id=a output, stu_id_

11、str = b output,?,select a,b,SQL Server,存儲(chǔ)過程,(4),?,聲明,變量定義,全局變量,（,hungarian,命名法）,?,Declare a int (,如果有多個(gè)變量，用逗號(hào)隔開，最后一個(gè)不要加逗號(hào),),?,基本賦值語句,?,Set a=1,或者,select a=1,?,Select a=count(1), b = sum(a) from t1,?,Select a=stu_id from t_stu;,?,如果只有一條記錄,那么,就把這條記錄的,stu_id,賦給,a,?,如果有多條記錄,那么返回最后一條記錄的,stu_id,給,a.,問題是,w

12、ho is,the last one?,SQL Server,存儲(chǔ)過程,(5),?,如果查詢不返回任何紀(jì)錄呢？,?,Declare a int,?,Select a=1,?,Select a=stu_id from t_stu where 1=2,?,Select a,?,變量的值沒有被改變,SQL Server,存儲(chǔ)過程,(6),?,Update t_stu set a = age = age +1 where stu_id =2,?,全局變量,?,identity,?,返回最近一個(gè),identiti,值, create table t_stu(stu_id int identity(1,1

13、),stu_name varchar(100), age int);,?,Daclare stu_id int,?,Insert into t_stu(stu_name, age) values(Tom, 20),?,Select stu_id=identity,?,error 0,成功,非零,錯(cuò)誤號(hào),?,rowcount sql,語句所影響的行數(shù)。,SQL Server,存儲(chǔ)過程,(7),?,選擇語句,?,IF cost = compareprice,?,BEGIN,?,.,?,END,?,ELSE,?,begin,?,.,?,end,?,語句塊,begin . End,，,可以嵌套，注意縮

14、進(jìn),?,While a 1,?,Begin,?,?,End,SQL Server,存儲(chǔ)過程,(8),?,事務(wù),Transaction,?,一般對(duì),DML,有效，即,insert, update, delete. Select,不改變?nèi)魏螖?shù)據(jù),?,SQL Server,單句,sql,默認(rèn)自動(dòng)提交,?,顯式事務(wù),?,Begin transaction,?,Update.,?,Insert.,?,Insert.,?,Commit transaction,?,Begin transaction,?,begin transaction,?,commit transaction,?,Commit tran

15、saction,SQL Server,存儲(chǔ)過程,(9),?,當(dāng)一個(gè)存儲(chǔ)過程調(diào)用另外一個(gè)存儲(chǔ)過程，通常會(huì)發(fā)生事務(wù)嵌套調(diào)用。,?,在嵌套的情況下，只有當(dāng)最外層的,commit,，對(duì)數(shù)據(jù)庫所有的修改才是,永恒的。,?,SQL Server,為每個(gè)客戶端連接保存一個(gè)已打開的事務(wù)，記錄在,trancount,中，每次打開一個(gè)，,trancount,加一，每次,commit,，減一，,直到最后,trancount=1,的時(shí)候,commit,保存所有的,?,Begin transaction transaction=1,?,Begin transaction transaction=2,?,Begin tr

16、ansaction transaction=3,?,commit transaction transaction=2,?,commit transaction transaction=1,?,Commit transaction transaction=0,SQL Server,存儲(chǔ)過程,(12),?,Declare cur_stu cursor,?,Set cur_stu=cursor for select stu_id, stu_name, age from t_stu,?,Open cur_stu,?,Fetch next from cur_stu into stu_id, stu_na

17、me, age,?,While(fetch_status =0) - 0,成功,非零,失敗,?,Begin,應(yīng)用處理,fetch next from cur_stu into stu_id, stu_name, age,?,End,?,Close cur_stu,?,Deallocate cur_stu,?,游標(biāo)很好寫，因?yàn)榉衔覀兊谌Z言,(c,c+,c#,java),的風(fēng)格,?,但是游標(biāo)實(shí)際性能很慢，因?yàn)椴环系谒拇枋鲂?SQL,向量式風(fēng)格,SQL Server,存儲(chǔ)過程,(10),?,但是,rollback,回滾就不一樣，,一個(gè),rollback,會(huì)取消所有已經(jīng)打開的事務(wù),?,Beg

18、in transaction -trancount=1,?,begin transaction - trancount=2,?,rollback transaction trancount=0,?,rollback transaction -,本句報(bào)錯(cuò),?,解決辦法,?,if trancount 0,?,rollback transaction,?,保存點(diǎn)：回滾部分事務(wù)的機(jī)制,?,Create procedure pr_test val int output as,?,Begin transaction,?,save transaction s1;,?,insert into .,?,roll

19、back transation s1;,?,Commit transaction,SQL Server,存儲(chǔ)過程,(11),?,游標(biāo),cursor,可以理解為一個(gè)二維的結(jié)果集,?,1. Declare Cursor,創(chuàng)建基于,select,語句的游標(biāo),?,declare cursor_name cursor for select .,?,declare cursor_name cursor,?,set cursor_name=cursor for select ,?,2. Open,語句打開游標(biāo),?,3. Fetch,語句獲取游標(biāo)的當(dāng)前記錄，存儲(chǔ)到局部變量中,?,4.,應(yīng)用處理,?,5. Cl

20、ose,語句關(guān)閉游標(biāo),?,6. Deallocate,語句釋放游標(biāo),SQL Server,表變量,(13),?,表變量：類似于臨時(shí)表，但是比臨時(shí)表性能更好，更輕量。,?,Declare mytable table(id int primary key, name varchar(20),?,Insert into mytable values(1, jack),?,Insert into mytable values(2,tom),?,Select * from mytable,?,Go,?,表變量只能存活在定義它們的批處理腳本，存儲(chǔ)過程、函數(shù)和觸發(fā)器，,?,當(dāng)定義環(huán)境執(zhí)行結(jié)束后，臨時(shí)表就自動(dòng)

21、刪除。,?,不同的連接相互之間不能看到臨時(shí)表的數(shù)據(jù)。,?,表變量和正常表進(jìn)行連接操作時(shí)候，需要用偽名。,?,Select a.* from mytable a, t_stu b where a.id = b.stu_id,SQL Server,異常處理,(14),?,錯(cuò)誤代號(hào)，查詢文檔,?,基于,error,的錯(cuò)誤處理,?,如果執(zhí)行一句,sql,語句有錯(cuò)誤，那么,error,就會(huì)被置上錯(cuò)誤的代碼。,?,If error 0,?,Begin,?,print error.,?,rollback transaction,?,End,?,error,有一個(gè)重要的缺陷是在執(zhí)行每句,sql,語句后，都會(huì)更

22、改,error,這樣,導(dǎo)致后面的語句所產(chǎn)生的狀態(tài)覆蓋了前面的,?,如果在,sqlserver 2000,以前，可以參考論文,Error Handling in T-SQL:,From Casual to Religious,有簡(jiǎn)便的處理方法，其中包含,goto,的使用。,SQL Server,異常處理,(15),?,Try-Catch,語句,?,begin try,?,insert.,?,update.,?,insert.,?,End try,?,Begin catch,?,.,?,End catch,?,在,catch,模塊中，可以使用以下幾個(gè)函數(shù),?,Error_message(),返回給

23、調(diào)用應(yīng)用程序的錯(cuò)誤信息,?,Error_number(),錯(cuò)誤號(hào),?,Error_severity(),錯(cuò)誤等級(jí),?,Error_state(),狀態(tài),?,Error_procedure(),錯(cuò)誤的存儲(chǔ)過程名字,?,Error_line(),錯(cuò)誤的過程行號(hào),SQL Server,存儲(chǔ)過程,-,調(diào)試,(16),?,SQL Server 2000,內(nèi)自帶的調(diào)試工具,?,SQL Server 2019,把調(diào)試存儲(chǔ)過程的功能放到,visual studio 2019,里面。,?,可參看附件描述的,visual studio,調(diào)試方法,?,如果沒有現(xiàn)成工具，那么可以在存儲(chǔ)過程里面采用,print,中間變量的具,體值來判斷程序是否正確,?,但是需要特別注意：產(chǎn)品正式發(fā)布的時(shí)候，需要把這些,print,語句屏蔽,了。否則會(huì)影響返回的結(jié)果。,?,SQL Server 2019,存儲(chǔ)過程調(diào)試，,cns/ryanding/archive/2019/12/06/1896762.html,?,SQL Server 2019,，,mssqltips/sqlservertip/2410/sql-server-2019-,tsql-debugging-enhance