SQL培訓(xùn) 第二講.ppt

1、SQL培訓(xùn) 第二講,2011年11月3日,主要內(nèi)容：,作業(yè)分析講評(píng) SQL中的常用函數(shù) 聚合分析與分組 存儲(chǔ)過(guò)程 SQL觸發(fā)器,作業(yè)分析講評(píng),以下為一表名為“職工考核信息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（1）從表1中查詢出“工號(hào)”、“姓名”和“職稱”三列的所有數(shù)據(jù)行,作業(yè)分析講評(píng),以下為一表名為“職工考核信息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（2）從表1中查詢出“出生年月”在19800101以后，而且“職稱”為工程師或經(jīng)濟(jì)師的所有數(shù)據(jù)行,作業(yè)分析講評(píng),以下為一表名為“職工考核信

2、息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（3）向表1添加一條數(shù)據(jù),作業(yè)分析講評(píng),以下為一表名為“職工考核信息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（4）從表1中查詢出職工的考核成績(jī)大于70分的部門(mén)名稱，并按成績(jī)高低排序,作業(yè)分析講評(píng),以下為一表名為“職工考核信息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（5）將表2的銷售業(yè)績(jī)按月份、銷售數(shù)量和商品的名稱排名（注：銷售數(shù)量由大到小），并與表1進(jìn)行關(guān)聯(lián)，顯示工號(hào)、姓名、職稱（按工號(hào)進(jìn)行排序）,

3、作業(yè)分析講評(píng),以下為一表名為“職工考核信息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（6）從表2中查詢姓名為“王?！钡穆毠?，如果其某個(gè)月的某種商品銷售數(shù)量超過(guò)100，則對(duì)其表1中的考核成績(jī)加5分,作業(yè)分析講評(píng),以下為一表名為“職工考核信息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（7）將2月份電視機(jī)的銷售數(shù)量超過(guò)50臺(tái)的職工的姓名、性別和籍貫查詢出來(lái),作業(yè)分析講評(píng),以下為一表名為“職工考核信息”的表，請(qǐng)按照要求使用SQL語(yǔ)言執(zhí)行相應(yīng)操作： 表一：職工考核信息表,表二：銷售業(yè)績(jī)明細(xì)表,（8）查詢出

4、1月份沒(méi)有銷售空調(diào)的銷售人員的姓名、年齡和籍貫,一張表有一個(gè)id字段，是integer類型的，然后要你找出id第2大的記錄,select*from(selectrow_number()over(orderbyiddesc)asseq,*fromt_scrap)twhereseq=2,關(guān)于group by表內(nèi)容：2005-05-09 勝2005-05-09 勝2005-05-09 負(fù)2005-05-09 負(fù)2005-05-10 勝2005-05-10 負(fù)2005-05-10 負(fù)如果要生成下列結(jié)果, 該如何寫(xiě)sql語(yǔ)句? 勝 負(fù)2005-05-09 2 22005-05-10 1 2,select

5、 rq, sum(case when shengfu=勝 then 1 else 0 end)勝 ,sum(case when shengfu=負(fù) then 1 else 0 end)負(fù) from #tmp group by rq,表中有A B C三列,用SQL語(yǔ)句實(shí)現(xiàn)：當(dāng)A列大于B列時(shí)選擇A列否則選擇B列，當(dāng)B列大于C列時(shí)選擇B列否則選擇C列。,select (case when ab then a else b end ) ,(case when bc then b esle c end) from table_name,有一張表，里面有3個(gè)字段：語(yǔ)文，數(shù)學(xué)，英語(yǔ)。其中有3條記錄分別表示語(yǔ)

6、文70分，數(shù)學(xué)80分，英語(yǔ)58分，請(qǐng)用一條sql語(yǔ)句查詢出這三條記錄并按以下條件顯示出來(lái)大于或等于80表示優(yōu)秀，大于或等于60表示及格，小于60分表示不及格。 顯示格式： 語(yǔ)文 數(shù)學(xué) 英語(yǔ) 及格 優(yōu)秀 不及格,select(case when 語(yǔ)文=80 then 優(yōu)秀 when 語(yǔ)文=60 then 及格else 不及格) as 語(yǔ)文,(case when 數(shù)學(xué)=80 then 優(yōu)秀 when 數(shù)學(xué)=60 then 及格else 不及格) as 數(shù)學(xué),(case when 英語(yǔ)=80 then 優(yōu)秀 when 英語(yǔ)=60 then 及格else 不及格) as 英語(yǔ),from table,S

7、QL 函數(shù),函數(shù),函數(shù)執(zhí)行,兩種 SQL 函數(shù),函數(shù),單行函數(shù),多行函數(shù),字符函數(shù),字符函數(shù),LOWER UPPER INITCAP,CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE,大小寫(xiě)控制函數(shù),字符控制函數(shù),函數(shù),結(jié)果,大小寫(xiě)控制函數(shù),這類函數(shù)改變字符的大小寫(xiě)。,LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course),sql course SQL COURSE Sql Course,大小寫(xiě)控制函數(shù),顯示員工 Higgins的信息:,SELECT employee_id, last

8、_name, department_id FROM employees WHERE last_name = higgins; no rows selected,SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = higgins;,CONCAT(Hello, World) SUBSTR(HelloWorld,1,5) LENGTH(HelloWorld) INSTR(HelloWorld, W) LPAD(salary,10,*) RPAD(salary, 10, *) TRIM

9、(H FROM HelloWorld),HelloWorld Hello 10 6 *24000 24000* elloWorld,函數(shù),結(jié)果,字符控制函數(shù),這類函數(shù)控制字符:,SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) Contains a? FROM employees WHERE SUBSTR(job_id, 4) = REP;,字符控制函數(shù),1,2,3,1,2,3,數(shù)字函數(shù),ROUND: 四舍五入 ROUND(45.926,

10、 2)45.93 TRUNC: 截?cái)?TRUNC(45.926, 2)45.92 MOD: 求余 MOD(1600, 300)100,SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;,ROUND 函數(shù),DUAL 是一個(gè)偽表，可以用來(lái)測(cè)試函數(shù)和表達(dá)式。,1,2,3,3,1,2,SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;,TRUNC 函數(shù),3,1,2,1,2,3,SELECT last_name, salary, MOD(

11、salary, 5000) FROM employees WHERE job_id = SA_REP;,MOD 函數(shù),日期,Oracle 內(nèi)部使用數(shù)字存儲(chǔ)日期: 世紀(jì),年,月,日,小時(shí),分鐘,秒。 默認(rèn)的日期格式是 DD-MON-RR. 可以只指定年的后兩位在20世紀(jì)存放21世紀(jì)的日期。 同樣可以在21世紀(jì)存放20世紀(jì)的日期。,SELECT last_name, hire_date FROM employees WHERE last_name like G%;,日期,函數(shù)SYSDATE 返回: 日期 時(shí)間,日期的數(shù)學(xué)運(yùn)算,在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。 兩個(gè)日期相減返回日期之間相差的

12、天數(shù)。 可以用數(shù)字除24來(lái)向日期中加上或減去小時(shí)。,日期的數(shù)學(xué)運(yùn)算,SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;,日期函數(shù),兩個(gè)日期相差的月數(shù),MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,向指定日期中加上若干月數(shù),指定日期的下一個(gè)日期,本月的最后一天,日期四舍五入,日期截?cái)?函數(shù),描述,MONTHS_BETWEEN (01-SEP-95,11-JAN-94),日期函數(shù),ADD_MONTHS (1

13、1-JAN-94,6),NEXT_DAY (01-SEP-95,FRIDAY),LAST_DAY(01-FEB-95),19.6774194,11-JUL-94,08-SEP-95,28-FEB-95,ROUND(SYSDATE,MONTH) 01-AUG-95,ROUND(SYSDATE ,YEAR) 01-JAN-96,TRUNC(SYSDATE ,MONTH) 01-JUL-95,TRUNC(SYSDATE ,YEAR) 01-JAN-95,日期函數(shù),Assume SYSDATE = 25-JUL-95:,轉(zhuǎn)換函數(shù),隱性,顯性,數(shù)據(jù)類型轉(zhuǎn)換,隱式數(shù)據(jù)類型轉(zhuǎn)換,Oracle 自動(dòng)完成下列轉(zhuǎn)

14、換：,VARCHAR2 or CHAR,源數(shù)據(jù)類型,目標(biāo)數(shù)據(jù)類型,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,隱式數(shù)據(jù)類型轉(zhuǎn)換,表達(dá)式計(jì)算中, Oracle 自動(dòng)完成下列轉(zhuǎn)換:,VARCHAR2 or CHAR,源數(shù)據(jù)類型,目標(biāo)數(shù)據(jù)類型,VARCHAR2 or CHAR,NUMBER,DATE,顯式數(shù)據(jù)類型轉(zhuǎn)換,NUMBER,CHARACTER,TO_CHAR,TO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換,格式: 必須包含在單引號(hào)中而且大小寫(xiě)敏感。 可以包含任意的有效的日期格式。 可以使用 fm 去掉多余的空格或者前導(dǎo)零。 與日期指

15、用逗號(hào)隔開(kāi)。,TO_CHAR(date, format_model),YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,2004,TWO THOUSAND AND FOUR,02,MON,MONDAY,JULY,MON,JUL,DD,02,日期格式的元素,時(shí)間格式 使用雙引號(hào)向日期中添加字符 日期在月份中的位置,TO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換,SELECT last_name, TO_CHAR(hire_date, fmDD Month YYYY) AS HIREDATE FROM employees;,TO_CHAR 函數(shù)對(duì)數(shù)字的轉(zhuǎn)換,下面是在TO_CHAR 函數(shù)中經(jīng)常使

16、用的幾種格式:,TO_CHAR(number, format_model),9,0,$,L,.,數(shù)字,零,美元符,本地貨幣符號(hào),小數(shù)點(diǎn),千位符,SELECT TO_CHAR(salary, $99,999.00) SALARY FROM employees WHERE last_name = Ernst;,TO_CHAR函數(shù)對(duì)數(shù)字的轉(zhuǎn)換,TO_NUMBER 和 TO_DATE 函數(shù),使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字: 使用 TO_DATE 函數(shù)將字符轉(zhuǎn)換成日期: 這些函數(shù)可以使用fx 修飾符。 n,TO_NUMBER(char, format_model),TO_DATE(cha

17、r, format_model),TO_NUMBER 和 TO_DATE 函數(shù),使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字: 使用 TO_DATE 函數(shù)將字符轉(zhuǎn)換成日期: 這些函數(shù)可以使用 fx 修飾符。,TO_NUMBER(char, format_model),TO_DATE(char, format_model),RR 日期格式,當(dāng)前年 1995 1995 2001 2001,日期 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95,RR 格式 1995 2017 2017 1995,YY 格式 1995 1917 2017 2095,當(dāng)前的年份:,049

18、,049,5099,5099,The return date is in the current century,The return date is in the century after the current one,The return date is in the century before the current one,The return date is in the current century,指定的年份:,RR 日期格式,SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY) FROM employees WHERE hi

19、re_date TO_DATE(01-Jan-90, DD-Mon-RR);,使用RR日期格式查找雇傭日期在1990年之前的員工， 在1999或現(xiàn)在使用下面的命令會(huì)產(chǎn)生相同的結(jié)果:,嵌套函數(shù),單行函數(shù)可以嵌套。 嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。,F3(F2(F1(col,arg1),arg2),arg3),步驟1 = 結(jié)果1,步驟2 =結(jié)果2,步驟3 =結(jié)果3,SELECT last_name, NVL(TO_CHAR(manager_id), No Manager) FROM employees WHERE manager_id IS NULL;,嵌套函數(shù),通用函數(shù),這些函數(shù)適用于任何數(shù)據(jù)類型

20、，同時(shí)也適用于空值： NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ., exprn),NVL 函數(shù),將空值轉(zhuǎn)換成一個(gè)已知的值： 可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。 函數(shù)的一般形式: NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet),SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (sa

21、lary*12*NVL(commission_pct, 0) AN_SAL FROM employees;,使用NVL函數(shù),1,2,1,2,SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) income FROM employees WHERE department_id IN (50, 80);,使用 NVL2 函數(shù),1,2,1,2,SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr

22、2, NULLIF(LENGTH(first_name), LENGTH(last_name) result FROM employees;,使用 NULLIF 函數(shù),1,2,3,1,2,3,使用 COALESCE 函數(shù),COALESCE 與 NVL 相比的優(yōu)點(diǎn)在于 COALESCE 可以同時(shí)處理交替的多個(gè)值。 如果第一個(gè)表達(dá)式費(fèi)空,則返回這個(gè)表達(dá)式，對(duì)其他的參數(shù)進(jìn)行COALESCE 。,SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;,使用 C

23、OALESCE 函數(shù),條件表達(dá)式,在 SQL 語(yǔ)句中使用IF-THEN-ELSE 邏輯。 使用兩種方法: CASE 表達(dá)式 DECODE 函數(shù),CASE 表達(dá)式,在需要使用 IF-THEN-ELSE 邏輯時(shí):,CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr END,SELECT last_name, job_id, salary, CASE job_id

24、WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARY FROM employees;,CASE 表達(dá)式,下面是使用case表達(dá)式的一個(gè)例子：,DECODE 函數(shù),在需要使用 IF-THEN-ELSE 邏輯時(shí):,DECODE(col|expression, search1, result1 , search2, result2,., , default),DECODE 函數(shù),SELECT last_name,

25、job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARY FROM employees;,DECODE 函數(shù),SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHER

26、E department_id = 80;,使用decode函數(shù)的一個(gè)例子：,聚合分析與分組,SQL提供了一些列的聚集函數(shù)對(duì)表中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析，在SQL中可以使用分組命令將列中的數(shù)據(jù)按照一定的條件進(jìn)行分組。一般只將聚合函數(shù)作用在滿足條件的分組上。因此聚合函數(shù)與分組命令一般結(jié)合使用.常見(jiàn)的聚合函數(shù)有sum（），max（），min( ),avg( ),count( ) 常用的分組函數(shù)有GROUP BY(),存儲(chǔ)過(guò)程,將常用的或很復(fù)雜的工作，預(yù)先用SQL語(yǔ)句寫(xiě)好并用一個(gè)指定的名稱存儲(chǔ)起來(lái), 那么以后要叫數(shù)據(jù)庫(kù)提供與已定義好的存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí),只需調(diào)用execute,即可自動(dòng)完成命令。,

27、存儲(chǔ)過(guò)程的優(yōu)點(diǎn),1.存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而一般SQL語(yǔ)句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。 2.當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表進(jìn)行Update,Insert,Query,Delete時(shí)），可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用。 3.存儲(chǔ)過(guò)程可以重復(fù)使用,可減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。 4.安全性高,可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)。,創(chuàng)建存儲(chǔ)過(guò)程,語(yǔ)法 CREATE PROC EDURE owner. procedure_name ; number parameter da

28、ta_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n ,參數(shù) owner 擁有存儲(chǔ)過(guò)程的用戶 ID 的名稱。owner 必須是當(dāng)前用戶的名稱或當(dāng)前用戶所屬的角色的名稱。 procedure_name 新存儲(chǔ)過(guò)程的名稱。過(guò)程名必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。 ;number 是可選的整數(shù)，用來(lái)對(duì)同名的過(guò)程分組，以便用一條 DROP PROCEDURE 語(yǔ)句即可將同組的過(guò)程一起除去。

29、例如，名為 orders 的應(yīng)用程序使用的過(guò)程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語(yǔ)句將除去整個(gè)組。如果名稱中包含定界標(biāo)識(shí)符，則數(shù)字不應(yīng)包含在標(biāo)識(shí)符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?參數(shù) parameter 過(guò)程中的參數(shù)。在 CREATE PROCEDURE 語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值，或者該值設(shè)置為等于另一個(gè)參數(shù)）。存儲(chǔ)過(guò)程最多可以有 2.100 個(gè)參數(shù)。 使用 符號(hào)作為第一個(gè)字符來(lái)指定參數(shù)名稱。參數(shù)名稱必須符合

30、標(biāo)識(shí)符的規(guī)則。每個(gè)過(guò)程的參數(shù)僅用于該過(guò)程本身；相同的參數(shù)名稱可以用在其它過(guò)程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫(kù)對(duì)象的名稱。 data_type 參數(shù)的數(shù)據(jù)類型。除 table 之外的其他所有數(shù)據(jù)類型均可以用作存儲(chǔ)過(guò)程的參數(shù)。但是，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定 cursor 數(shù)據(jù)類型，則還必須指定 VARYING 和 OUTPUT 關(guān)鍵字。對(duì)于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒(méi)有最大數(shù)目的限制。 VARYING 指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。 default 參數(shù)的

31、默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過(guò)程。默認(rèn)值必須是常量或 NULL。如果過(guò)程將對(duì)該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、 和 ）。 OUTPUT 表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給 EXECUTE。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過(guò)程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符,參數(shù) n 表示最多可以指定 2.100 個(gè)參數(shù)的占位符。 RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION RECOMPILE 表明

32、SQL Server 不會(huì)緩存該過(guò)程的計(jì)劃，該過(guò)程將在運(yùn)行時(shí)重新編譯。在使用非典型值或臨時(shí)值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計(jì)劃時(shí)，請(qǐng)使用 RECOMPILE 選項(xiàng)。 ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語(yǔ)句文本的條目。使用 ENCRYPTION 可防止將過(guò)程作為 SQL Server 復(fù)制的一部分發(fā)布。 FOR REPLICATION 指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。.使用 FOR REPLICATION 選項(xiàng)創(chuàng)建的存儲(chǔ)過(guò)程可用作存儲(chǔ)過(guò)程篩選，且只能在復(fù)制過(guò)程中執(zhí)行。本選項(xiàng)不能和 WITH

33、RECOMPILE 選項(xiàng)一起使用。 AS 指定過(guò)程要執(zhí)行的操作。 sql_statement 過(guò)程中要包含的任意數(shù)目和類型的 Transact-SQL 語(yǔ)句。但有一些限制。 n 是表示此過(guò)程可以包含多條 Transact-SQL 語(yǔ)句的占位符。,SQL觸發(fā)器,觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程，不由用戶直接調(diào)用。創(chuàng)建觸發(fā)器時(shí)會(huì)對(duì)其進(jìn)行定義，以便在對(duì)特定表或列作特定類型的數(shù)據(jù)修改時(shí)執(zhí)行。 CREATE PROCEDURE 或 CREATE TRIGGER 語(yǔ)句不能跨越批處理。即存儲(chǔ)過(guò)程或觸發(fā)器始終只能在一個(gè)批處理中創(chuàng)建并編譯到一個(gè)執(zhí)行計(jì)劃中。 用觸發(fā)器還可以強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則 Microsoft SQ

34、L Server 2000 提供了兩種主要機(jī)制來(lái)強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程，它在指定的表中的數(shù)據(jù)發(fā)生變化時(shí)自動(dòng)生效。喚醒調(diào)用觸發(fā)器以響應(yīng) INSERT、UPDATE 或 DELETE 語(yǔ)句。觸發(fā)器可以查詢其它表，并可以包含復(fù)雜的 Transact-SQL 語(yǔ)句。將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到嚴(yán)重錯(cuò)誤（例如，磁盤(pán)空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。,優(yōu)點(diǎn),觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改；不過(guò)，通過(guò)級(jí)聯(lián)引用完整性約束可以更有效地執(zhí)行這些更改。 觸發(fā)器可以強(qiáng)制比用 CHECK 約束定義的約束更為復(fù)雜的約束。 與

35、 CHECK 約束不同，觸發(fā)器可以引用其它表中的列。例如，觸發(fā)器可以使用另一個(gè)表中的 SELECT 比較插入或更新的數(shù)據(jù)，以及執(zhí)行其它操作，如修改數(shù)據(jù)或顯示用戶定義錯(cuò)誤信息。 觸發(fā)器也可以評(píng)估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對(duì)策。 一個(gè)表中的多個(gè)同類觸發(fā)器（INSERT、UPDATE 或 DELETE）允許采取多個(gè)不同的對(duì)策以響應(yīng)同一個(gè)修改語(yǔ)句。,SQL觸發(fā)器語(yǔ)法,CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT DELETE UPDATE WITH AP

36、PEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) updated_bitmask ) column_bitmask .n sql_statement .n ,參數(shù),trigger_name 是觸發(fā)器的名稱。觸發(fā)器名稱必須符合標(biāo)識(shí)符規(guī)則，并且在數(shù)據(jù)庫(kù)中必須唯一。可以選擇是否指定觸發(fā)器所有者名稱。 Table | view 是在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖。可以選擇是否指定表或視圖的所有者名稱。 WITH ENC

37、RYPTION 加密 syscomments 表中包含 CREATE TRIGGER 語(yǔ)句文本的條目。使用 WITH ENCRYPTION 可防止將觸發(fā)器作為 SQL Server 復(fù)制的一部分發(fā)布。 AFTER 指定觸發(fā)器只有在觸發(fā) SQL 語(yǔ)句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級(jí)聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。 如果僅指定 FOR 關(guān)鍵字，則 AFTER 是默認(rèn)設(shè)置。 不能在視圖上定義 AFTER 觸發(fā)器。,參數(shù),INSTEAD OF 指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā) SQL 語(yǔ)句，從而替代觸發(fā)語(yǔ)句的操作。 在表或視圖上，每個(gè) INSERT、UPDATE 或

38、DELETE 語(yǔ)句最多可以定義一個(gè) INSTEAD OF 觸發(fā)器。然而，可以在每個(gè)具有 INSTEAD OF 觸發(fā)器的視圖上定義視圖。 INSTEAD OF 觸發(fā)器不能在 WITH CHECK OPTION 的可更新視圖上定義。如果向指定了 WITH CHECK OPTION 選項(xiàng)的可更新視圖添加 INSTEAD OF 觸發(fā)器，SQL Server 將產(chǎn)生一個(gè)錯(cuò)誤。用戶必須用 ALTER VIEW 刪除該選項(xiàng)后才能定義 INSTEAD OF 觸發(fā)器。 DELETE , INSERT , UPDATE 是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語(yǔ)句時(shí)將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定

39、義中允許使用以任意順序組合的這些關(guān)鍵字。如果指定的選項(xiàng)多于一個(gè)，需用逗號(hào)分隔這些選項(xiàng)。 對(duì)于 INSTEAD OF 觸發(fā)器，不允許在具有 ON DELETE 級(jí)聯(lián)操作引用關(guān)系的表上使用 DELETE 選項(xiàng)。同樣，也不允許在具有 ON UPDATE 級(jí)聯(lián)操作引用關(guān)系的表上使用 UPDATE 選項(xiàng)。,參數(shù),WITH APPEND 指定應(yīng)該添加現(xiàn)有類型的其它觸發(fā)器。只有當(dāng)兼容級(jí)別是 65 或更低時(shí)，才需要使用該可選子句。如果兼容級(jí)別是 70 或更高，則不必使用 WITH APPEND 子句添加現(xiàn)有類型的其它觸發(fā)器（這是兼容級(jí)別設(shè)置為 70 或更高的 CREATE TRIGGER 的默認(rèn)行為）。有關(guān)更

40、多信息，請(qǐng)參見(jiàn) sp_dbcmptlevel。 WITH APPEND 不能與 INSTEAD OF 觸發(fā)器一起使用，或者，如果顯式聲明 AFTER 觸發(fā)器，也不能使用該子句。只有當(dāng)出于向后兼容而指定 FOR 時(shí)（沒(méi)有 INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本將不支持 WITH APPEND 和 FOR（將被解釋為 AFTER）。 NOT FOR REPLICATION 表示當(dāng)復(fù)制進(jìn)程更改觸發(fā)器所涉及的表時(shí)，不應(yīng)執(zhí)行該觸發(fā)器。 AS 是觸發(fā)器要執(zhí)行的操作。,參數(shù),sql_statement 是觸發(fā)器的條件和操作。觸發(fā)器條件指定其它準(zhǔn)則，以確定 DEL

41、ETE、INSERT 或 UPDATE 語(yǔ)句是否導(dǎo)致執(zhí)行觸發(fā)器操作。 當(dāng)嘗試 DELETE、INSERT 或 UPDATE 操作時(shí)，Transact-SQL語(yǔ)句中指定的觸發(fā)器操作將生效。 觸發(fā)器可以包含任意數(shù)量和種類的 Transact-SQL 語(yǔ)句。觸發(fā)器旨在根據(jù)數(shù)據(jù)修改語(yǔ)句檢查或更改數(shù)據(jù)；它不應(yīng)將數(shù)據(jù)返回給用戶。觸發(fā)器中的 Transact-SQL 語(yǔ)句常常包含控制流語(yǔ)言。CREATE TRIGGER 語(yǔ)句中使用幾個(gè)特殊的表： * deleted 和 inserted 是邏輯（概念）表。這些表在結(jié)構(gòu)上類似于定義觸發(fā)器的表（也就是在其中嘗試用戶操作的表）；這些表用于保存用戶操作可能更改的行的

42、舊值或新值。例如，若要檢索 deleted 表中的所有值，請(qǐng)使用： SELECT * FROM deleted * 如果兼容級(jí)別等于 70，那么在 DELETE、INSERT 或 UPDATE 觸發(fā)器中，SQL Server 將不允許引用 inserted 和 deleted 表中的 text、ntext 或 image 列。不能訪問(wèn) inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 觸發(fā)器中檢索新值，請(qǐng)將 inserted 表與原始更新表聯(lián)接。當(dāng)兼容級(jí)別是 65 或更低時(shí)，對(duì) inserted 或 deleted 表中允許空值的text、ntext 或 image 列，將返回空值；如果這些列不可為空，則返回零長(zhǎng)度字符串。 當(dāng)兼容級(jí)別是 80 或更高時(shí)，SQL Server 允許在表或視圖上通過(guò) INSTEAD OF 觸發(fā)器更新 text、ntext 或 image 列。,參數(shù),n 是表示觸發(fā)器中可以包含多條 Transact-SQL 語(yǔ)句的占位符。對(duì)于 IF UPDATE (column) 語(yǔ)句，可以通過(guò)重復(fù) UPDATE (column)