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