視圖、存儲過程、函數(shù)、游標與觸發(fā)器介紹

1、視圖、存儲過程、函數(shù)、游標與觸發(fā)器學習目標掌握使用視圖掌握使用存儲過程掌握使用自定義標量函數(shù)、表值函數(shù)掌握使用游標掌握使用觸發(fā)器-、視圖的定義視圖由SELECT查詢語句所定義的一個虛擬 表，是查看數(shù)據(jù)的一種非常有效的方式， 同真實的數(shù)據(jù)表一樣，視圖也包含一系列 帶有名稱的數(shù)據(jù)列和數(shù)據(jù)行，但視圖與數(shù) 據(jù)表又有很大的不同，視圖中的數(shù)據(jù)并不 真實的存在于數(shù)據(jù)庫中。1.1、 視圖的優(yōu)點簡化查詢提供一種安全機制視圖掩碼（對長的字段重新命名）數(shù)據(jù)即時更新1.2、 視圖的分類 標準視圖：使用最頻繁的視圖，不存儲任 何數(shù)據(jù)，不占用任何存儲空間 索引視圖：擁有唯一群集索引的視圖被稱 為索引視圖，它存儲真實索引數(shù)

2、據(jù)，占用 一定的存儲空間。 分區(qū)視圖：現(xiàn)在用分區(qū)表進行替代1.2.K標準視圖-創(chuàng)建帶仃部門編號的emp視圖SELECT dbo.EMP.EMPNO, dbo.EMP.ENAME, dbo.EMP.JOB, dbo.EMP.MGR, dbo.EMP.HIREDATE, dbo.EMP.SAL, dbo.EMP.COMM, dbo.EMP.DEPTNO,dbo.DEPT.DNAMEFROM dbo.EMP INNER JOINdbo.DEPT ON dbo.EMP.DEPTNO = dbo.DEPT.DEPTNO -通過視圖修改數(shù)據(jù)update v_emp_with_deptname set sa

3、l=800 v/here empno=7369注意：(1)可以修改基于兩個或兩個以上基表的視圖，但是每次修改只能影響一 個基表，不能同時修改。(2)不能修改通過計算得到的列、有內(nèi)置函數(shù)的列以及有聚合函數(shù)的列1.2.2,索引視圖創(chuàng)建各部門人數(shù)的視圖drop view v_countOfDept gocreate view v_countOfDept WITH SCHEMABINDING asSELECT EMP.deptno,count_big(*) empcountFROM dbu.EMPgroup by emp.deptno創(chuàng)建聚合索引CREATE UNIQUE CLUSTERED INDE

4、X Lv_countOfDept_deptnoON v_countOfDept(deptno)注意：(1)創(chuàng)建索引視圖，必須擁有唯聚合索引，如果創(chuàng)建聚合索引,帶有聚合函數(shù)的基礎視 圖必須使用WITH SCHEMABINDING , group by以及count_big函數(shù)(2)使用索引視圖能提高數(shù)據(jù)庫效率(3)如果視圖引用任何.確定性函數(shù)，則不能在視圖上創(chuàng)建聚集索引1.2.3, 分區(qū)視圖-創(chuàng)建分區(qū)視圖-將表進行行分割，emp表分解為emp1和emp2 select * into emp1 from emp where empno=7782 goCREATE VIEWv emp with 1

5、and 2ASSELECT * FROM emp1UNION ALLSELECT * FROM emp2注意：分區(qū)視圖在sqlserver2005M被分區(qū)表替代新建說由a a 國& ZD 二 g rployttE tl2 23 疝。,5年9ffl w 王回IMHH31ATION 50(二、存儲過程1.3、在Management Studio中倉Li建視圖3 C i)n,nRnAftriH_oCMIMPOATIOM_SCX IMFOMUHON_SO( IKFOWAnUNR IMFOFWAnON.SCM存儲過程是數(shù)據(jù)庫系統(tǒng)中封裝的代碼模塊， 它采用T-SQL語言來編寫，經(jīng)編譯后存放在 數(shù)據(jù)庫服務器

6、中，具有很好的可重用性， 可用于高效地完成某些操作存儲過程可以充分利用服務器的高性能運 算能力，無需把大量的結(jié)果集送往客戶端 進行處理，大大減少了網(wǎng)絡數(shù)據(jù)傳輸?shù)拈_ 銷，提高了應用程序訪問數(shù)據(jù)庫的速度和 效率2.1、 使用存儲過程的好處與特點存儲過程是已編譯過的，并在服務器上注冊和保存的代碼 模塊，因面比一般的程序語句執(zhí)行起來速度更快，同時減 少了網(wǎng)絡流量，節(jié)省了大量可間和數(shù)據(jù)流塞。存儲過程可以使用控制語句和變量，并且在一個存儲過程 中可以調(diào)用其他存儲過程，使得用戶可以進行模塊化程序 龍計，大大提嵩了用戶設計愈序的效率。存儲過程具有安全特性和所有權鏈接，可以執(zhí)行所有的權馥翳鼠翻驪藕麻儲過程的權限

7、但不擁有存儲過程可以提高應用程序的安全性，防止SQL嵌入式攻 擊存儲過程可以允許代碼綁定，引用當前不存在的對象，這 些對象僅在存儲過程執(zhí)行時存在2.2、 存儲過程的分類用戶自定義的存儲過程：最主栗的存儲過 程系統(tǒng)存儲過程：sp_前綴，系統(tǒng)預定義擴展存儲過程：保存在DLL動態(tài)鏈接庫中并 從動態(tài)鏈接庫中執(zhí)行的C+程序代碼，用于 擴展SQLSERVER2005,性育旨，以字符xp_開 頭，通常與其它系統(tǒng)存儲過程一起使用虛 過程序集調(diào)用。2.3、 存儲過程的設計規(guī)則CREATE PROCEDURE定義本身可包/舌除下歹” CREATE語句以外的任何數(shù)量和類型的SQL語 句，存儲過程中的任意地方都不能使

8、用下列語句:C CREATE RULECREATE DEFAULTCREATE FUNCTIONCREATE TRIGGERCREATE PROCEDURECREATE VIEWUSE DATABASE2.4、 創(chuàng)建存儲過程的語法CREATE PROC | PROCEDURE schema_name. procedure_name parameter type_schema_name. data_typeOUTPUT 5.nAS 2.5、 建和使用存儲過程-創(chuàng)建不帶參數(shù)的存儲過程it exists(select name from sysobjects where name二pro_name，

9、and type=*p*) drop procedure pro_nameGocreate procedure pro_name asdeclare v_name varchar(10),v_sal decimal(10,2)beginbegin tryselect v_name=ename.v_sal=sal from emp where empno=7369if v_salv_name varchar(10),(3v_sal decimal(10,2)beginbegin tryselect v_name=ename,(5)v_sal=sal from emp where empno=(5

10、)vempno if (2v_sal2500print I：資超過2500elseprint資少于2500end trybegin catchprint 鋪誤號:l+cast(error as varchar(10)print 錯誤內(nèi)容:+error_message()end catchend使用存儲過程pro_name 7369一創(chuàng)建帶檢出參數(shù)的存儲過程if exists(select name from sysobjects where name=*pro name* and type=p*)drop procedure pro_nameGocreate procedure pro_nam

11、e vempno int,v_name varchar(13) output,v_sal decimal。0,2) out asbeginbegin tryselect v_name=ename.v_sal=sal from emp where empno=vempnoend trybegin catchprint錯誤 * J :,*cast(error as varchar(10)print 錯誤面 容:+error_message()end catchend-使用存儲過程declare v_name varchar(10) .v_sal decimal。0.2)exec pro_name

12、7369 ,v_name output.v_sal outputif v_sal2500print，I.資超過2500elseprint，I：資少于2500go2.6存儲過程的執(zhí)行過程和重編譯存儲過程在第1次執(zhí)行時，要經(jīng)過語法分析、解析、編譯和執(zhí)行共4個階段, 當添加新的索引或更期某些列數(shù)值之后，存儲過程有不自動執(zhí)行優(yōu)化， 可以強制徐卜,次啟動服霧器前次編譯該春儲過程,以更新原有的寂行計劃。可以有三種方式實現(xiàn)：- -1使用spjecompHe系統(tǒng)存儲過程 exec sp_recompile hh- 2創(chuàng)建存儲過程時增加with recompile選項 ALTER procedure dbo.h

13、h with recompile as begin select county) ,count from aa end- 3執(zhí)行重新編譯 exec hh with recompile2.7 加密存儲過程-使用with encrypVon語句對存儲過程進行加密ALTER procedure dbo.hh with recompile,encryption as begin select count(*) count, from aa end2.8 使用擴展存儲過程使用外圍配置器配置，如下圖:敲入 exec xp_cmdshell dir c:u結(jié)果是列出c盤而具體內(nèi)容相干的還有幾個都是以xp_開

14、頭，具體可以查詢msdn文檔。-比如使用xpjileexist判斷文件是否存在use masterdeclare ret intexec xp_fileexistHeAtempweb.configret outputprint ret三、用戶自定義函數(shù)用戶自定義函數(shù)是接受參數(shù)、執(zhí)行操作（例如復 雜計算）并將操作結(jié)果以值的形式返回的子程序。 返回值可以是單個標量值或結(jié)果集。SQL Server 2005支持3木中類型的Transact-SQL用 戶自定義函數(shù)：標量函數(shù)、內(nèi)嵌表值函數(shù)和多語 句表值函數(shù)。在SQL Server中使用用戶自定義函數(shù)有以下優(yōu)點: 允許模塊化程序設計。執(zhí)行速度更快。減少網(wǎng)

15、絡流量。3.1創(chuàng)建用戶自定義函數(shù)(1)標量函數(shù)標量型函數(shù)返回 個確定類型的標量值其返回值類型為除TEXT、NTEXT、IMAGE、CURSOR. TIMESTAMP、table夕卜的其它數(shù)據(jù)類型。函數(shù)體語句 定義在BEGIN-END語句內(nèi)，其中包含了可以返回值的TransactSQL命令創(chuàng)建標量函數(shù)的語法如下：CHtA 11 FUNC I ION schema_name. J tunction_name( parameter_name AS type_schema_name. parameter_data_type =default -.n)RETURNS return_data_typeWI

16、TH ,.n AS一BEGINfunction_bodyRETURN scalar expressionEND其中各參數(shù)的角義如下。 schema_name用戶仃定義函數(shù)所屬的架構的名稱。 function_name用戶rT定義函數(shù)的名稱。 parameter_name用戶門定2函數(shù)的參數(shù)。type_schema_name. parameter_data_type參藪的數(shù)據(jù)滅型及其所屈的架構，前者為可選項。=default 參數(shù)的默認值。 return_data_type用戶?I定義標義函數(shù)的返回值。 function_body函數(shù)體，指定一系列定義函數(shù)值的Transact-SQL語句。 sc

17、alar_expression指定標定函數(shù)返回的標的值。 指定函女將具有以卜.一個或多個選項，共有兩個：ENCRYPTION指示數(shù)據(jù)庫引擎對包含CREATE FUNCTION語句文本的目錄視圖列進行加密。SCHEMABINDING指定將函數(shù)綁定到其引用的數(shù)據(jù)庫對象。創(chuàng)建和使用標收函數(shù)創(chuàng)建函數(shù) if exists(select name from sysobjectswhere name=*get_salary_by_deplno*)drop function get_salary_by_deptno gocreate function get_salary_by_deptno(v_dept_n

18、o int)Returns int as begindeclare v_sum intselect v sum=sum(sal) from empwhere deptno = v_dept_noif(a)error0beginreturn -1 -其中返回-1示函出錯了 endreturn v_sum end使用函數(shù)print dbo.get_salary_by_deptno(10)(2)內(nèi)嵌表值函數(shù)內(nèi)聯(lián)表值型函數(shù)以表的形式返回一個返回值，即它返回的 是一個表內(nèi)聯(lián)表值型函數(shù)沒有由BEGIN-END語句括起來 的函數(shù)體。其返回的表由一個位于RETURN子句中的 SELECT命令段從數(shù)據(jù)庫中篩選出

19、來。內(nèi)聯(lián)表值型函數(shù)功 能相當于一個參數(shù)化的視圖。創(chuàng)建內(nèi)嵌表值函數(shù)的語法如下：CREATE FUNCTION schema_name. function_name( parameter_name AS type_schema name. parameter data type=default ,.n)RETURNS TABLEWITH ,.n ASRETURN ( select_stmt)其中各參數(shù)的含義如下。schema_name function_nameparameter_name type_schema_name.parameter_data_type = default vfuncti

20、on_option的含義與標量函數(shù)中的各 參數(shù)相同。 TABLE指定表值函數(shù)的返回值為表。 select_stmt定義內(nèi)嵌表值函數(shù)的返回值的單個SELECT 語句。使用內(nèi)嵌表值函數(shù)：drop function f_get_emp goCREATE FUNCTION f_get_emp()RETURNS table asRETURN(select * from emp)注意：返回table數(shù)據(jù)類型的用戶定義函數(shù)實際上 是視圖，可以對返11的數(shù)據(jù)直接更新，并影響基 表。(3)多語句表值函數(shù)多聲明表值型函數(shù)可.以看作標量型和內(nèi)聯(lián)表值型函數(shù)的結(jié)合體。它的 返回值是一個表，但它和林量型函數(shù)一樣有一個用B

21、EGIN-END語句 括號來的函數(shù)體，返回值的衣中的數(shù)據(jù)是由函數(shù)體中的語句插入的。 山詼可見，爸可以進行多次在詢，對數(shù)據(jù)進行多次篩選與合并，彌補 了內(nèi)聯(lián)表值型函數(shù)的不足。創(chuàng)建多語句表值函數(shù)的語法如下：CREATE FUNCTION schema_name. function_name(S)pArAmAtftr_namR AS type schema name. 1 parameter data type=default 7.n )一RETURNS return_variable TABLE WITH ,.n ASBEGINfunction_bodyRETURNEND其中各參數(shù)的含義如下。sch

22、ema_name function_name parameter_nametype_schema_name. parameter_data_typ = default vfunction_option的含義與標量函數(shù)中的各參 數(shù)相同。 TABLE指定表值函數(shù)的返回值為表。在多語句表值函數(shù)中， return_variable是TABLE變量，用于存儲和匯總應作為 函數(shù)值返回的行。 定義表數(shù)加;類應。表聲明包含列定義和列約束(或表約束)。(3) function_body函數(shù)體，指定一系列定義函數(shù)值的TransactSQL語句。使用多語句表值函數(shù)： drop function f_get_emp

23、goCREATE FUNCTION f_get_emp() RETURNS abc table(empno int) as begininsert into abc select empno from emp returnendselect * from dbo.f_get_emp()3.2修改刪除用戶自定義函數(shù)使用ALTER FUNCTION語句可以修改用戶 自定義函數(shù)的定義，ALTER FUNCTION 句的語法及參數(shù)與CREATE FUNCTION的 語法及參數(shù)類似。使用DROP FUNCTION語句可以從當前數(shù) 據(jù)庫中刪除一個或多個用戶自定義函數(shù)。33定義函數(shù)與存儲過程的 區(qū)別存儲過程

24、與函數(shù)主要區(qū)別于返回值方面：(1)存儲過程可以使用output參數(shù)返回多個值，而函數(shù)只可以返叵| 個 加，木可以使南output參數(shù)(2)關于返回結(jié)果集方面的區(qū)別：存儲過程中雖然沒有return語句，但是也可以返回結(jié)果集。比女II： create procedure sp1asbeginselect name, fid Jk from table_1print 111select testname ,fid from table_2end這個結(jié)果，可.以被被使用：insert into table_3(name, fid_fk) exec sp1對廠自定義函數(shù)，它必須指定定義為返回值為tabl

25、e類型的數(shù)據(jù)v.。且在代班顯式的網(wǎng)該table 中insert：或者，只是制定返固他為table類型,彳、指定return而對尊變狀，匕接return該表.即:create function fn1()returns tableasreturn select fid. testname from table_2或齊create function fn1()returns v table(fid int primary key not null,testname nchar(10)asbegininsert into v select fid,testname from table_2End使用

26、insert into table3 select * from fn1 ()它們的區(qū)別:(1)前者是返回杳詢的結(jié)果集合,只是作為一系列功能的副產(chǎn)品：而后者,就是為了返回值而創(chuàng)建的。(2)前若返回俏不能被仃接引用：而是必須被使川為execspl或齊insert into table的形式使用.后者 這回值可以黛直接引用.四、游標在數(shù)據(jù)庫中，游標是一個十分重要的概念。 游標實際上是一種能從包括多條數(shù)據(jù)記錄 的結(jié)果集中每次提取一條記錄的機制。當決定對結(jié)果集進行處理時，必須聲明一 個指向該結(jié)果集的游標。4.1、 游標的分類MS SQL SERVER支持三種類型的游標： Transact_SQL游標，

27、API服務器游標和客 戶游標。一其中Transact_SQL游標就是我們同樣使用的游標 一Transact_SQL 游標是由DECLARECURSOR語法定義、主要用在Transact_SQL腳本、存儲過程和觸發(fā)器中。4.2、 Transact SQL 游標每一個游標必須仃四個組成部分這四個關鍵部分必須符合下面的順序:1 .DECLARE 游標2.OPEN游標3 .從一個游標中FETCH佶息4 .CLOSE 或DEALLOCATE 游標通常我們使用DECLARE來聲明一個游標聲明一個游標主要包括以下 主要內(nèi)容：游標名字數(shù)據(jù)來源（表和列）選取條件屬性（僅讀或可修改）其語法格式如下：DECLARE

28、 cursor_name INSENSITIVE SCROLL CURSORFOR select statementFOR READ ONLY | UPDATE OF column_name ,.n其中： Acursor name指游標的名字。INSENSITIVE表明MS SQL SERVER會將游標定義所選取出來的數(shù)據(jù)記錄存放 在,臨時表內(nèi)（建立在tempdb數(shù)據(jù)庫卜）。對該游標的讀取操作 皆山臨時表來應答。因此，對基本表的修改并不影響游標提取的 數(shù)據(jù)，崗游標不會隨著基本表內(nèi)容的改變而改變，同時也無法通 過游林米更新基本表。收律不使用該保留字，那么對基本表的更 新、刪除甄春反映到游標市。S

29、CROLI表明方的提取操作（如FIRST、 LAST、 PRIOR. NEXT、 RELATIVE.ABSOLUTE）都可用如果。使用該保留字，那么只能進行NEXT提取操作、由 此可見.SCROLL極大地增加了提取數(shù)據(jù)的是活性，可以隨意讀取結(jié)果集中的任 行數(shù)據(jù)汕泉，而不必關閉再重開游標。select_statement是定好吉果集的SELECT語句。READ ONLY&明不允許游標內(nèi)的數(shù)據(jù)被更新盡管在缺省狀態(tài)卜.游標是允許更新的。UPDATE OF column name,.nl定義在游標中可被修改的列，如果不指出要更新的列，那么所仃的列都將被更新,-卜.面是一個游標的例子-1DECLARE

30、tempcursor CURSOR FOR select sal,comm from emp where sal1000declare sal decimal。0,2),comm intbegin-2open tempcursor;-3fetch next from tempcursor into sal,commprint，資為:+cast(sal as varchar(10)print，補助為:+cast(comm as varchar(10)-4close tempcursorDEALLOCATE tempcursorEnd- 在這里例子中明顯有一處缺陷，就是只能取到一條記錄- F面增加

31、循環(huán)和判斷DECLARE tempcursor CURSOR FOR select sal,comm from emp where sal1000declare sal decimal。0,2),comm intbeginopen tempcursor;fetch next from tempcursor into sal,commwhile (g(5)fetch_status=0beginprint 工資為:+cast(sal as varchar(10)print 補助為 1+casXcomm as varchar(10)fetch next from tempcursor into sa

32、l,commendclose tempcursorDEALLOCATE tempcursorEnd- 注意：fetch_status是一個游標專用的系統(tǒng)函數(shù)，用于表示是否取到值五、觸發(fā)器Microsoft SQL Server 2005系統(tǒng)提供了兩 種強制業(yè)務邏輯和數(shù)據(jù)完整性的機制，即 約束技術和觸發(fā)器技術。5.1、 觸發(fā)器的作用觸發(fā)器是一種特殊的存儲過程，它在特定 語言事件發(fā)生時自動執(zhí)行，通常用于實現(xiàn) 強制 業(yè)務規(guī)則和數(shù)據(jù)完整性。觸發(fā)器支持嵌套，最多可以嵌套32層5.2、 觸發(fā)器的分類DML觸發(fā)器：DML觸發(fā)器是當數(shù)據(jù)庫服務 器中發(fā)生數(shù)據(jù)操作語言（DML）事件時要 執(zhí)行的操作。DML事件包括

33、對表或視圖發(fā) 出的UPDATE、INSERT或DELETE語句 C AFTER觸發(fā)器：指SQL語句成功執(zhí)行之后觸發(fā) OINSTEAD OF觸發(fā)器。DDL觸發(fā)器521、DML AFTER觸發(fā)器DML AFTER觸發(fā)器是當數(shù)據(jù)庫服務器中發(fā) 生數(shù)據(jù)操作語言（DML）事件時要執(zhí)行的 操作，成功執(zhí)行之后觸發(fā)。特別注意：SQLServer觸發(fā)器中可以擁有事 務回滾功能，當有錯誤發(fā)生時，執(zhí)行 rollback,這樣導致DML失效。. DML AFTER角蟲發(fā)器的語法創(chuàng)建DML觸發(fā)器的語法格式為：CREATE TRIGGERschema_name . trigger_nameON tableFOR | AFTERINSERT , UPDATE , DELETE ASsql_statement其中各參數(shù)的含義如下。(1) schema_nameDML觸爰器所屬架構的名稱。(2) trigger_name觸發(fā)器而名稱。(3) table對其執(zhí)行DML觸發(fā)器的表。(4) AFTER指定DML觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都 已成功執(zhí)行時才被激發(fā)。如果寫FOR則默認為AFTER(5