tj第六章SQL編程技術.ppt

1,第六章 SQL 編程技術,2,6.1 SQL 編程基礎,6.1.1 批處理 批處理是一個以“”為結束的語句集這些語句是作為一個組來執(zhí)行并一起提交的 腳本是一系列順序執(zhí)行的批處理腳本文件的文件類型為 .sql 注意：不是語句,3,例：給出含有三個批處理的腳本,CREATE TABLE 學院 ( 學院名稱 char(12) PRIMARY KEY, 院長 char(8) , 學院地址 char(16) , 辦公電話 char(8) , 編制人數(shù) smallint ) GO INSERT INTO 學院 VALUES(計算機學院,劉洋,一號樓4,88889999,50) INSERT INTO 學院 VALUES(商學院,梁山,五號樓302,99998888,60) GO SELECT * FROM 學院 GO,4,6.1.2 變量,1.變量的類型 局部變量 -以 引導,由用戶定義.其作用域定義的轄域中. 全局變量 -以 引導,由系統(tǒng)提供并賦值.其作用域為全局.,5,6.1.2 變量,2.變量的聲明 語句格式: DECLARE 變量名 數(shù)據(jù)類型 , 變量名 數(shù)據(jù)類型 例:聲明變量 DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT,6,6.1.2 變量,3.變量的賦值 語句格式一: SET 變量名稱=表達式 語句格式二: SELECT 變量名稱=表達式 語句格式三: SELECT 變量名稱=字段(或函數(shù)) FROM 表(或視圖)名 WHERE ,7,舉例,例1: DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT SET Name=李華 SET Sex =女 SET Age=20 例2: DECLARE Name CHAR(8) SELECT Name=李華,8,舉例,例3: DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT SELECT Name=姓名,Sex=性別, Age=YEAR(GETDATE()-YEAR(出生日期) FROM 學生 WHERE 學號=00150236 SELECT Name, Sex, Age GO,顯示結果,9,6.1.3控制流語句,包含條件控制語句、無條件轉移語句、循環(huán)語句等。,10,6.1.3控制流語句,.BEGIN END 功能：將一組SQL語句作為一個語句塊。 語句格式： BEGIN END 與IF語句和WHILE語句配合使用,11,6.1.3控制流語句,2.IF ELSE 語句 語句格式： IF ELSE ,12,13,舉例,USE 教學數(shù)據(jù)庫 GO DECLARE gavg smallint SELECT gavg=AVG(成績) FROM 選課 WHERE 課程號=(SELECT 課程號 FROM 課程 WHERE 課程名稱=大學英語) IF (gavg80) PRINT 大學英語課程的平均成績高于80分 ELSE BEGIN PRINT 大學英語課程的平均成績低于80分 select 平均成績:,gavg END,14,6.1.3控制流語句,3.循環(huán)語句 語句格式： WHILE BREAK CONTINUE,15,6.1.3控制流語句,16,舉例:,求出大學英語課程的平均成績 當該分數(shù)小于0分時，循環(huán)做,給所有成績高于平均分的成績加分，當最高分大于時停止加分并顯示加的分數(shù) 該分數(shù)(平均成績)大于0分時，循環(huán)停止并顯示加的分數(shù),17,舉例,DECLARE gavg smallint,COUN smallint SET COUN=0 SELECT gavg=AVG(成績) FROM 選課 WHERE 課程號= (SELECT 課程號 FROM 課程 WHERE 課程名稱=大學英語) WHILE (gavggavg IF(SELECT max(成績) FROM 選課 WHERE 課程號= (SELECT 課程號 FROM 課程 WHERE 課程名稱=大學英語)100 BREAK END PRINT COUN,18,舉例,DECLARE gavg smallint,COUN smallint SET COUN=0 SELECT gavg=AVG(成績) FROM 選課 WHERE 課程號= (SELECT 課程號 FROM 課程 WHERE 課程名稱=大學英語) PRINT gavg WHILE (gavggavg IF(SELECT max(成績) FROM 選課 WHERE 課程號= (SELECT 課程號 FROM 課程 WHERE 課程名稱=大學英語)=100 BREAK ELSE SELECT gavg=AVG(成績) FROM 選課 WHERE 課程號= (SELECT 課程號 FROM 課程 WHERE 課程名稱=大學英語) END PRINT COUN,警告: 聚合或其它 SET 操作消除了空值。 74 （所影響的行數(shù)為 4 行） 警告: 聚合或其它 SET 操作消除了空值。 警告: 聚合或其它 SET 操作消除了空值。 （所影響的行數(shù)為 4 行） 警告: 聚合或其它 SET 操作消除了空值。 - - 2 75 （所影響的行數(shù)為 1 行）,19,4. RETURN語句 語句格式： RETURN 整數(shù) 功能：無條件所在批處理、存儲過程或觸發(fā)器?？梢苑祷匾粋€整數(shù)值,6.1.3控制流語句,20,舉例,題目：根據(jù)給定的學號（ param ）檢查學生的平均成績，若75，將返回狀態(tài)代碼 1，將返回狀態(tài)代碼 2。 USE 教學數(shù)據(jù)庫 CREATE PROCEDURE checkavg param varchar(10) AS IF (SELECT AVG(成績) FROM 選課 WHERE 學號 = param) 75 RETURN ELSE RETURN ,21,執(zhí)行存儲過程：,declare aa smallint exec aa=checkavg 00150236 if aa= print 小于75 else print 大于75,22,6.1.4 EXECUTE語句,功能：執(zhí)行函數(shù)、存儲過程 語法格式： EXECUTE output,23,6.1.5 注釋,.單行注釋 （兩個減號） .多行注釋 /* */,24,6.1.6 程序設計舉例,題目：轉帳，若帳戶的余額大于等于100元，從帳戶上支取100元，存入帳戶.否則，不執(zhí)行轉帳操作。,begin transaction update 帳戶 set 余額=余額-100 where 帳戶=A IF (SELECT 余額 from 帳戶 where 帳戶=A)=0 begin print金額不夠。轉帳失??！ rollback transaction end else begin update 帳戶 set 余額=余額+100 where 帳戶=B print轉帳成功！ commit transaction end,25,事務概述,事務是作為單個邏輯工作單元執(zhí)行的一系列操作。 事務的屬性（ACID) 原子性：原子工作單元 一致性：保證數(shù)據(jù)的一致性 隔離性：并發(fā)事務之間所做的修改要隔離 并發(fā)操作：幾個用戶程序同時讀寫一個數(shù)據(jù)的情況 持久性：對系統(tǒng)的影響要持久,26,事務操作,語法格式： BEGIN TRANSACTION 事務開始 COMMIT TRANSACTION 提交 ROLLBACK TRANSACTION 回滾 說明：如果沒有明確給出BEGIN TRANSACTION語句，則SQL Server是將每個SQL語句都當成一個事務進行執(zhí)行,27,6.3 存儲過程,6.3.1存儲過程的概念 是一組被編譯在一起的T-SQL語句的集合，它們被集合在一起以完成一個特定的任務。 存儲過程的分類 系統(tǒng)存儲過程 擴展存儲過程（提供從SQL Server到外部程序的接口，以便進行各種維護活動） 用戶自定義的存儲過程,28,使用存儲過程的優(yōu)勢,模塊化編程： 創(chuàng)建一個存儲過程存放在數(shù)據(jù)庫中后，就可以被其他程序反復使用。 快速執(zhí)行： 存儲過程第一次被執(zhí)行后，就駐留在內(nèi)存中。以后執(zhí)行就省去了重新分析、優(yōu)化、編譯的過程。 減少網(wǎng)絡通信量 有了存儲過程后，在網(wǎng)絡上只要一條語句就能執(zhí)行一個存儲過程。 安全機制 通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性，通過授權可以讓用戶只能執(zhí)行存儲過程而不能直接訪問數(shù)據(jù)庫對象。,29,6.3.2 創(chuàng)建存儲過程,創(chuàng)建過程語法格式： CREATE PROCEDURE 數(shù)據(jù)類型，OUTPUT AS 執(zhí)行過程語法格式： EXECUTE或EXEC 變量=值 刪除存儲過程語法格式： DROP PROCEDURE ,30,例：檢索某個學生(學號為01111111）的成績單，包括姓名、課程名、分數(shù)。存儲過程名為sc_ grade,IF EXISTS(SELECT name FROM sysobjects WHERE name=sc_grade AND type=P) DROP PROCEDURE sc_grade GO CREATE PROCEDURE sc_grade SNO CHAR(8) AS SELECT 姓名,課程名稱,成績 FROM 學生 S,課程 C ,選課 SC WHERE S.學號=SC.學號 AND C.課程號=SC.課程號 AND S.學號=SNO GO,31,select left(name,15)+space(3) name,xtype from sysobjects where xtype in (U,PK,FK,CK,P,TR) and SUBSTRING(name,1,3)dt_,name xtype - - check_trig TR ScroeProc P t1 U PK_t1_17036CC PK t2 U 系科 U PK_系科 PK 選課1 U 選課11 U 選課 U PK_選課 PK 課程 U PK_課程 PK student U PK_student_3E PK student3 U PK_student3_4 PK checkavg P 教師 U PK_教師 PK 學生 U PK_學生 PK dtproperties U pk_dtproperties PK （所影響的行數(shù)為 24 行）,32,執(zhí)行過程,EXEC sc_grade 01111111 或 EXEC sc_grade sno= 01111111 ,33,例在“教學”數(shù)據(jù)庫建立一個名為ScroeProc的存儲過程，它帶有兩個輸入?yún)?shù)并返回兩個輸出參數(shù)和一個返回值 。程序清單如下： USE 教學 GO -存儲過程ScroeProc若存在，則刪除之 IF EXISTS(SELECT name FROM sysobjects WHERE name=Scroe AND type=P) DROP PROCEDURE ScroeProc GO,34,-創(chuàng)建存儲過程ScroeProc -輸入?yún)?shù)：Dno輸入系科號；Cname輸入課名 -輸出參數(shù)：Avg 接受平均分 CREATE PROCEDURE ScroeProc Dno char(4),Cname varchar(30), Avg decimal OUTPUT AS /*聲明和初始化一個局部變量，用于保存系統(tǒng)函數(shù)ERROR的返回值*/ DECLARE ErrorSave int SET ErrorSave=0,35,-執(zhí)行一個選擇查詢，統(tǒng)計指定系的某門課的平均成績 -通過兩個輸入?yún)?shù)接受系號和課程名稱 -通過一個輸出參數(shù)Avg接受平均分 SELECT Avg=AVG(成績) FROM 選課 sc INNER JOIN 學生 st ON sc.學號=st.學號 INNER JOIN 課程 CO ON sc.課程號=co.課程號 where 系科號 =Dno AND 課程名稱=Cname IF (ERROR0) SET ErrorSave=ERROR select ErrorSave,36,/*執(zhí)行一個修改查詢，將統(tǒng)計指定系的某門課的高于平均分的加1分*/ UPDATE 選課 set 成績=成績+1 where 學號 in (select 學號 st FROM 選課 sc INNER JOIN 學生 st ON sc.學號=st.學號 INNER JOIN 課程 CO ON sc.課程號=co.課程號 where 系科號 =Dno AND 課程名稱=Cname) IF (ERROR0) SET ErrorSave=ERROR RETURN ErrorSave,37,執(zhí)行存儲過程: /*聲明變量，用于保存返回值和輸出結果*/ DECLARE RetCode int, AvgGrade decimal /*執(zhí)行存儲過程，并指定輸入?yún)?shù)和輸出參數(shù)*/ EXECUTE RetCode=ScroeProc 15,數(shù)據(jù)結構, AvgGrade OUTPUT,38,- 0 （所影響的行數(shù)為 1 行） （所影響的行數(shù)為 51 行） - - 84 0 （所影響的行數(shù)為 1 行）,39,6.4 數(shù)據(jù)庫觸發(fā)器,6.4.1 觸發(fā)器的概念 是一種實現(xiàn)復雜完整性約束的特殊存儲過程，是能夠在符合條件是自動觸發(fā)的SQL程序。 1.觸發(fā)器的特點 2.觸發(fā)器的優(yōu)點,40,6.4.1 觸發(fā)器的概念,若觸發(fā)器所依賴的表中有約束，則在執(zhí)行時，約束優(yōu)于觸發(fā)器，而且如果在操作中觸發(fā)器和約束發(fā)生沖突，觸發(fā)器將不執(zhí)行。 觸發(fā)器操作是一個事務操作。,41,6.4.1 觸發(fā)器的概念,在下列情況下可以考慮使用觸發(fā)器： 強制比CHECK約束復雜的數(shù)據(jù)完整性 使用自定義的錯誤信息和執(zhí)行復雜的錯誤處理 實現(xiàn)多張表的級聯(lián)修改 比較數(shù)據(jù)庫修改前后數(shù)據(jù)的狀態(tài) 維護非規(guī)范數(shù)據(jù),42,6.4.2 創(chuàng)建觸發(fā)器,語法格式： CREATE TRIGGER On FOR | AFTER | INSTEAD OF INSERT| UPDATE |DELETE AS 刪除觸發(fā)器語法格式： DROP TRIGGER ,43,語句解釋：,AFTER:指定觸發(fā)器只有在觸發(fā) SQL 語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。 如果僅指定 FOR 關鍵字，則 AFTER 是默認設置。 不能在視圖上定義 AFTER 觸發(fā)器。 INSTEAD OF:指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā) SQL 語句，從而替代觸發(fā)語句的操作。 INSERT,UPDATE,DELETE:是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關鍵字。必須至少指定一個選項。在觸發(fā)器定義中允許使用以任意順序組合的這些關鍵字。如果指定的選項多于一個，需用逗號分隔這些選項。,44,使用觸發(fā)器的限制,(1) CREATE TRIGGER 必須是批處理中的第一條語句，并且只能應用到一個表中。 (2) 觸發(fā)器只能在當前的數(shù)據(jù)庫中創(chuàng)建，但觸發(fā)器可以引用當前數(shù)據(jù)庫的外部對象。 (3) 如果指定觸發(fā)器所有者名限定觸發(fā)器，要以相同的方式限定表名。 (4) 在同一CREATE TRIGGER語句中，可以為多種操作（如 INSERT 和 UPDATE）定義相同的觸發(fā)器操作。,45,使用觸發(fā)器的限制,(5) 一個表的外鍵在 DELETE、UPDATE 操作上定義了級聯(lián)，不能在該表上定義 INSTEAD OF DELETE、INSTEAD OF UPDATE 觸發(fā)器。 (6) 觸發(fā)器中不允許包含以下 T-SQL 語句： CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、LOAD LOG 、RESTORE LOG 、 DISK INIT、DISK RESIZE和RECONFIGURE,46,6.4.3觸發(fā)器的工作原理,當觸發(fā)器被觸發(fā)時，系統(tǒng)會創(chuàng)建兩個專用臨時表：inserted表和deleted表。這兩個表由系統(tǒng)來維護，不允許用戶直接對這兩個表進行修改（可以讀）。它們存放于內(nèi)存中，不存放在數(shù)據(jù)庫中。這兩個表的結構總是與被該觸發(fā)器作用的表的結構相同。觸發(fā)器工作完成后，與該觸發(fā)器相關的這兩個表也會被刪除。,47,6.4.3觸發(fā)器的工作原理,insterted表： 存放由于INSERT或UPDATE語句的執(zhí)行而導致要加到該觸發(fā)表中去的所有新行。即用于插入或更新表的新行值，在插入或更新表的同時，也將其副本存入insterted表中。因此，在insterted表中的行總是與觸發(fā)表中的新行相同。,48,6.4.3觸發(fā)器的工作原理,deleted表： 存放由于DELETE或UPDATE語句的執(zhí)行而導致要從該觸發(fā)表中刪除的所有行。也就是說，把觸發(fā)表中要刪除或要更新的舊行移到deleted表中。因此，deleted表和觸發(fā)表不會有相同的行。,49,6.4.3觸發(fā)器的工作原理,對INSERT操作，只在insterted表中保存所插入的新行，而deleted表中無一行數(shù)據(jù)。 對于DELETE操作，只在deleted表中保存被刪除的舊行，而insterted表中無一行數(shù)據(jù)。 對于UPDATE操作，可以將它考慮為DELETE操作和INSERT操作的結果，所以在inserted表中存放著更新后的新行值，deleted表中存放著更新前的舊行值。,50,例:在學生表上創(chuàng)建觸發(fā)器reminder，若在學生表中添加、更改和刪除數(shù)據(jù)，則將向客戶端顯示信息。,USE 教學 IF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminder GO CREATE TRIGGER reminder ON 學生 FOR INSERT, UPDATE ,DELETE AS print 注意：學生表數(shù)據(jù)被修改。 SELECT * FROM INSERTED SELECT * FROM DELETED GO,51,insert into 學生(學號,姓名) values (9999,dd),UPDATE 學生 SET 姓名=WWWW WHERE 學號=9999,delete from 學生 where 學號=9999,52,舉例：創(chuàng)建一觸發(fā)器，當向成績表插入一記錄時，檢查該記錄的學號在學生表是否存在，檢查課程號在課程表中是否存在，若有一項為否，則不允許插入。,Use 教學 GO alter TRIGGER check_trig ON 成績 for INSERT AS IF EXISTS (SELECT * FROM inserted a WHERE a.學號 NOT IN (SELECT 學號 FROM 學生 ) OR a.課程編號 NOT IN (SELECT 課程編號 FROM 課程 ) BEGIN -SELECT * FROM 成績 -SELECT * FROM inserted print 違背數(shù)據(jù)的一致性！ ROLLBACK TRANSACTION END SELECT * FROM 成績 GO,53,insert into 成績 values (3012,C6,90) SELECT * FROM 成績 WHERE 課程編號 LIKE %6,執(zhí)行插入操作：,insert into 成績 values (5012,C6,90),服務器: 消息 547，級別 16，狀態(tài) 1，行 1 INSERT 語句與 COLUMN FOREIGN KEY 約束 FK_成績_學號_6B24EA82 沖突。該沖突發(fā)生于數(shù)據(jù)庫 教學，表 學生, column 學號。 語句已終止。,54,分別刪除了成績表、課程表、學生表的聯(lián)系,insert into 成績 values (5012,C6,90),55,6.2 游標及其應用,6.2.1 游標的概念 如果要求每次只顯示表格中的一行，該如何處理？ 這在將T-SQL嵌入到其他高級語言（如VC、VB、Delphi等）的編程中經(jīng)常用到。,56,6.2.1 游標的概念,游標(cursor)是一個存儲區(qū)域，用來存放結果集。游標的指針，可以指向與它相關聯(lián)的結果集中的任意一行，以便對當前位置的行進行處理。 游標提供了對一個結果集進行逐行處理的能力： 在結果集中定位特定行 從結果集的當前位置檢索行 支持對結果集中當前位置的行進行數(shù)據(jù)處理（修改/刪除）,57,6.2.2 游標的用法,聲明游標 打開游標 處理數(shù)據(jù)（讀取/修改/刪除