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