【文章內容簡介】 與設計 張申勇 第 9 章 數據庫完整性與安全 創(chuàng)建存儲過程 OPEN curCourse FETCH curCourse INTO courseName, count, avg WHILE (FETCH_STATUS=0) BEGIN /* 將 班級編號 、 班級名稱 、 課程名稱 、 課程選課人數 、 課程平均分 插入到臨時表 myTemp中 */ INSERT INTO myTemp VALUES( classNo, className, courseName, count, avg ) 獲取下一游標值，取該班下一門課程的 課程名 、 選課人數 和 平均分 FETCH curCourse INTO courseName, count, avg END CLOSE curCourse DEALLOCATE curCourse 獲取游標 curClass的下一個值，即取下一個 班級 FETCH curClass INTO classNo, className END CLOSE curClass DEALLOCATE curClass 顯示臨時表的內容，同時將臨時表的內容返回給調用者 SELECT * FROM myTemp END 2023/1/24 24 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 創(chuàng)建存儲過程 ?在本例中，獲取 班級編號 、 班級名稱 不能寫成： SELECT classNo=classNo, className=className FROM Class WHERE institute=institute ?因為： ?一個學院有 多個班級 ，該查詢 返回一個元組集合 。 ?變量 classNo和 className僅分別接收一個數據 。 ?必須使用 游標 ，本例定義游標為 curClass。 2023/1/24 25 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 執(zhí)行存儲過程 ?使用存儲過程時，必須執(zhí)行命令 EXECUTE ?語法： EXECUTE procedurName [ [parameterName =] expr, [parameterName =] variableName [OUTPUT] [, [parameterName =] expr, [parameterName =] variableName [OUTPUT] ] ] ?注意： EXECUTE的參數 必須與對應的 PROCEDURE的參數 相匹配。 ? [例 ] 執(zhí)行存儲過程 proStudentByNo1 EXECUTE proStudentByNo1 39。080000139。 2023/1/24 26 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 執(zhí)行存儲過程 ? [例 ] 執(zhí)行存儲過程 proStudentByNo2 DECLARE sName varchar(20), avg numeric(5, 1) EXECUTE proStudentByNo2 39。080000139。, sName OUTPUT, avg OUTPUT SELECT sName, avg ? [例 ] 執(zhí)行過程 proInstitute EXECUTE proInstitute 39。信息管理學院 39。 ?也可以使用命令： DECLARE institute varchar(30) SET institute=39。信息管理學院 39。 EXECUTE proInstitute institute 2023/1/24 27 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 修改和刪除存儲過程 ? 修改存儲過程 ? 語法為： ALTER PROCEDURE procedureName [ parameterName datatype [= defaultValue] [OUTPUT] [, parameterName datatype [= defaultValue] [OUTPUT] ] ] AS SQLStatements ? 注意： 由于存儲過程是在服務器端執(zhí)行， 程序中不需要有 輸出命令 SELECT，由 SELECT引出的輸出不會在客戶端出現 。 ? [例 ] 修改存儲過程 proStudentAvg， 將顯示結果內容刪除 。 ALTER PROCEDURE proStudentAvg( sNo char(7), avg numeric(6, 2) OUTPUT ) AS BEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=0 2023/1/24 28 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 修改和刪除存儲過程 定義、打開、獲取游標 DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c WHERE =sNo AND = AND = OPEN curGrade FETCH curGrade INTO sName, cName, grade WHILE (FETCH_STATUS=0) BEGIN 業(yè)務處理 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade END CLOSE curGrade DEALLOCATE curGrade 2023/1/24 29 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 修改和刪除存儲過程 IF count=0 SELECT avg = 0 ELSE SELECT avg=sum/count END ?刪除存儲過程 ?語法： DROP PROCEDURE procedureName ?[例 ] 刪除存儲過程 proStudentAvg DROP PROCEDURE proStudentAvg 2023/1/24 30 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 目 錄 數據庫安全性 數據庫完整性 游標 存儲過程 觸發(fā)器 應用與安全設計 2023/1/24 31 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 觸發(fā)器 ? 觸發(fā)器 (trigger)是用戶定義在 關系表 上的一類由 事件驅動 的存儲過程，由服務器自動激活。 ? 觸發(fā)器可進行更為復雜的檢查和操作，具有更精細和更強大的數據控制能力。 ? 觸發(fā)器是一種特殊的存儲過程，不管什么原因造成的 數據變化 都能自動響應，對于每條 SQL語句，觸發(fā)器僅執(zhí)行一次，事務 可用于觸發(fā)器中。 ? 事務定義： BEGIN TRANSACTION [ transactionName ] COMMIT TRANSACTION [ transactionName ] ROLLBACK TRANSACTION [ transactionName ] ? 有兩個特殊的表用在觸發(fā)器語句中，不同的數據庫管理系統其名稱不一樣： ? 在 SQL Server中使用 deleted和 inserted表； ? Oracle數據庫使用 old和 new表。 2023/1/24 32 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 觸發(fā)器 ? 注意： ? 這兩張表的結構與作用的表結構完全一致； ? 當作用表的 SQL語句開始時，自動產生這兩張表的結構與內容 ； ? 當 SQL語句執(zhí)行完畢，這兩張表也隨即刪除。 ? 下面以 SQL Server為例介紹觸發(fā)器： ? deleted表 ? 存儲 DELETE和 UPDATE語句執(zhí)行時 所影響的行的拷貝 ； ? 在 DELETE和 UPDATE語句執(zhí)行前被作用的行轉移到 deleted表中。 ?將被刪除的元組或修改前的元組值存入該表中 ? inserted表 ? 存儲 INSERT和 UPDATE語句執(zhí)行時 所影響的行的拷貝 ； ? 在 INSERT和 UPDATE語句執(zhí)行期間，新行被同時加到 inserted表和觸發(fā)器 表中。 ?將被插入的元組或修改后的元組值存入該表中，同時更新基本表。 2023/1/24 33 北京理工大學珠海學院 計算機學院 數據庫原理與設計 張申勇 第 9 章 數據庫完整性與安全 觸發(fā)器 ?實際上， UPDATE命令是 刪除后緊跟著插入 ，舊行首先拷貝到 deleted表中，新行同時拷貝到 inserted表和 基本表