單擊此處編輯母版標(biāo)題樣式,單擊此處編輯母版文本樣式,第二級(jí),第三級(jí),第四級(jí),第五級(jí),*,.,*,單擊此處編輯母版標(biāo)題樣式,單擊此處編輯母版文本樣式,第二級(jí),第三級(jí),第四級(jí),第五級(jí),*,.,*,單擊此處編輯母版標(biāo)題樣式,單擊此處編輯母版文本樣式,第二級(jí),第三級(jí),第四級(jí),第五級(jí),*,.,*,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)化程序語(yǔ)言回顧,SQL,過程結(jié)構(gòu),變量定義，輸入輸出參數(shù),賦值語(yǔ)句,選擇語(yǔ)句,循環(huán)語(yǔ)句,游標(biāo),表變量,異常處理,運(yùn)行和調(diào)試,實(shí)際案例學(xué)習(xí),總結(jié),.,單句,SQL,語(yǔ)句 總結(jié),(7)SELECT(8)DISTINCT(10),(1)FROM,(3)JOIN,(2)ON,(4)WHERE,(5)GROUP BY,(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,insert,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,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),過程結(jié)構(gòu)：即按照第二、三代編程語(yǔ)言的結(jié)構(gòu)來編寫多句的,SQL,程序。,SQL1999,規(guī)范：,Function,Procedure,method 3,種數(shù)據(jù)庫(kù)對(duì)象,實(shí)現(xiàn)以上,3,種對(duì)象，可以使用數(shù)據(jù)庫(kù)內(nèi)部語(yǔ)言,sql,或者外部語(yǔ)言,(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ù)庫(kù)內(nèi)，減少編譯所耗費(fèi)的時(shí)間,.,緩存，編譯好的存儲(chǔ)過程會(huì)進(jìn)入緩存，所以對(duì)經(jīng)常執(zhí)行的存儲(chǔ)過程，除了第一次執(zhí)行外，其他次執(zhí)行的速度會(huì)有明顯提高,.,減少網(wǎng)絡(luò)傳輸，特別對(duì)于處理一些數(shù)據(jù)的存儲(chǔ)過程，不必像直接用,sql,語(yǔ)句實(shí)現(xiàn)那樣多次傳送數(shù)據(jù)到客戶端,.,性能更快，利用數(shù)據(jù)庫(kù)中向量化操作和眾多具有較高性能的系統(tǒng)函數(shù),.,更好的封裝，用戶不需要了解內(nèi)部具體的表和數(shù)據(jù)等信息,.,更好的安全性，防止,sql,注入。,缺點(diǎn)：,增加程序員學(xué)習(xí)成本，需要多學(xué)一門語(yǔ)言。,開發(fā)調(diào)試工具不如普通程序語(yǔ)言，函數(shù)庫(kù),API,不如普通程序語(yǔ)言豐富。,增加數(shù)據(jù)庫(kù)的工作負(fù)載。,可移植性較差。如果換數(shù)據(jù)庫(kù)，那么需要重新編寫存儲(chǔ)過程或者函數(shù)。,存儲(chǔ)過程是天使,or,魔鬼？,Tradeoff,多層系統(tǒng)。,.,系統(tǒng),Trade-off,.,普通程序語(yǔ)言,(1),賦值,x=1;x:=1;x=1,選擇,if(1=x)x=2;else x=3;,If,else,then,elsif,elseif,else if,begin,end,&,|,!,and,or,not.,.,普通程序語(yǔ)言,(2),循環(huán)語(yǔ)句：,while,do.while,For,foreach,.,普通程序語(yǔ)言,(3),函數(shù),function,函數(shù)調(diào)用,/,遞歸,.,學(xué)習(xí)程序語(yǔ)言的步驟,變量定義，數(shù)字，字符串。,1+1,，數(shù)字字符串相互轉(zhuǎn)化,If.else,while,for,case/switch,語(yǔ)句。,數(shù)組,函數(shù)定義和調(diào)用,系統(tǒng)函數(shù)庫(kù),異常處理,看代碼，抄代碼，改代碼。,10%,高級(jí)特性。,一個(gè)語(yǔ)言的學(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,存儲(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%,.,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_str=b output,select a,b,.,SQL Server,存儲(chǔ)過程,(4),聲明 變量定義,全局變量 （,hungarian,命名法）,Declare a int (,如果有多個(gè)變量，用逗號(hào)隔開，最后一個(gè)不要加逗號(hào),),基本賦值語(yǔ)句,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.,問題是,who 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),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,語(yǔ)句所影響的行數(shù)。,.,SQL Server,存儲(chǔ)過程,(7),選擇語(yǔ)句,IF cost 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 transaction,.,SQL Server,存儲(chǔ)過程,(9),當(dāng)一個(gè)存儲(chǔ)過程調(diào)用另外一個(gè)存儲(chǔ)過程，通常會(huì)發(fā)生事務(wù)嵌套調(diào)用。,在嵌套的情況下，只有當(dāng)最外層的,commit,，對(duì)數(shù)據(jù)庫(kù)所有的修改才是永恒的。,SQL Server,為每個(gè)客戶端連接保存一個(gè)已打開的事務(wù)，記錄在,trancount,中，每次打開一個(gè)，,trancount,加一，每次,commit,，減一，直到最后,trancount=1,的時(shí)候,commit,保存所有的,Begin transaction transaction=1,Begin transaction transaction=2,Begin transaction 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_name,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(yǔ)言,(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ù),Begin 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 s