第8章 Oracle 的其它對(duì)象ppt課件

1、第8章 Oracle 的其它對(duì)象8.1 序列8.2 同義詞8.3 存儲(chǔ)過(guò)程8.4 觸發(fā)器8.5 函數(shù)8.6 包.數(shù)據(jù)庫(kù)對(duì)象簡(jiǎn)介 Oracle 數(shù)據(jù)庫(kù)對(duì)象又稱(chēng)方式對(duì)象數(shù)據(jù)庫(kù)對(duì)象是邏輯構(gòu)造的集合，最根本的數(shù)據(jù)庫(kù)對(duì)象是表其他數(shù)據(jù)庫(kù)對(duì)象包括：數(shù)據(jù)庫(kù)對(duì)象 同義詞序列視圖索引. 8.1.1 在OMEC中創(chuàng)建序列 8.1.2 運(yùn)用SQL命令創(chuàng)建序列 8.1.3 維護(hù)序列8.1 序列.序列序列是用于生成獨(dú)一、延續(xù)序號(hào)的對(duì)象序列可以是升序的，也可以是降序的運(yùn)用CREATE SEQUENCE語(yǔ)句創(chuàng)建序列SQL CREATE SEQUENCE toys_seqSTART WITH 10INCREMENT BY 10

2、MAXVALUE 2000MINVALUE 10NOCYCLECACHE 10;指定第一個(gè)序號(hào)從 10 開(kāi)場(chǎng)指定序號(hào)之間的間隔為 10表示序列的最大值為 2000表示序列的最小值為 10在到達(dá)最大值后停頓生成下一個(gè)值指定內(nèi)存中預(yù)先分配的序號(hào)數(shù) . 8.1.2 運(yùn)用SQL命令創(chuàng)建序列CREATE SEQUENCE sequence_nameINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n |NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHEORDER | NOORDER. 8.1.2 運(yùn)用SQ

3、L命令創(chuàng)建序列參數(shù)闡明其中sequence_name表示創(chuàng)建的序列稱(chēng)號(hào)，n表示恣意正整數(shù)值。INCREMENT BY：指定序列遞增或遞減的間隔數(shù)值，缺省值為1。START WITH：序列的起始值。MINVALUE：序列可允許的最小值。假設(shè)指定為NOMINVALUE，那么對(duì)升序序列將運(yùn)用默許值1，而對(duì)降序序列運(yùn)用默許值-1.0E28。. 8.1.2 運(yùn)用SQL命令創(chuàng)建序列參數(shù)闡明MAXVALUE：序列可允許的最大值。對(duì)降序序列，將運(yùn)用該序列默許的最大值假設(shè)指定為NOMAXVALUE，那么將對(duì)升序序列運(yùn)用默許值-1.0E28(-10的28次方)而對(duì)降序序列運(yùn)用默許值-1。CYCLE：指定在到達(dá)序列

4、最小值或最大值之后，序列應(yīng)繼續(xù)生成值。假設(shè)指定為NOCYCLE，那么序列將在到達(dá)最小值或最大值后停頓生成任何值。缺省值是NOCYCLE。.8.1.2運(yùn)用SQL命令創(chuàng)建序列CACHE：由數(shù)據(jù)庫(kù)預(yù)分配并存儲(chǔ)序列值的數(shù)目。默許值 為20。假設(shè)指定為NOCACHE，那么不預(yù)分配序列值的數(shù)目。ORDER：缺省值為NOORDER。指定ORDER參數(shù)使Oracle 9i 在并行環(huán)境下，按照懇求的順序來(lái)產(chǎn)生序列號(hào)。.8.1.3 維 護(hù) 序 列1. 查詢(xún)序列信息序列的信息可以在ALL_SEQUENCE和USER_SEQUENCE數(shù)據(jù)字典中找到，其中USER_SEQUENCE的數(shù)據(jù)構(gòu)造如下表所示。.8.1.3 維

5、 護(hù) 序 列列名 詳細(xì)含義SEQUENCE_NAMEMIN_VALUEMAX_VALUEINCREMENT_BYCYCLE_FLAGORDER_FLAGCACHE_SIZELAST_NUMBER 序列名 最小值 最大值 增量 循環(huán)標(biāo)志 次序標(biāo)志 緩沖大小 最后一個(gè)數(shù).訪(fǎng)問(wèn)序列經(jīng)過(guò)序列的偽列來(lái)訪(fǎng)問(wèn)序列的值NEXTVAL 前往序列的下一個(gè)值CURRVAL 前往序列的當(dāng)前值SQL INSERT INTO toys (toyid, toyname, toyprice) VALUES ( toys_seq.NEXTVAL, TWENTY, 25);SQL INSERT INTO toys (toyid,

6、 toyname, toyprice) VALUES ( toys_seq.NEXTVAL, MAGIC PENCIL, 75);指定序列的下一個(gè)值SQL SELECT toys_seq.CURRVAL FROM dual;檢索序列的當(dāng)前值.8.1.3 維 護(hù) 序 列 2修正序列 當(dāng)修正序列時(shí)，留意不要使依賴(lài)于序列號(hào)的主鍵不獨(dú)一。 利用OEMC修正序列在OEMC的界面中，選擇要修正的序列，單擊鼠標(biāo)右鍵，從彈出的快捷菜單中選擇“查看/編輯詳細(xì)資料，激活“編輯序列窗口，在窗口中對(duì)序列進(jìn)展修正即可。.8.1.3 維 護(hù) 序 列利用SQL命令修正序列ALTER SEQUENCE sequence_na

7、meINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHEORDER | NOORDER.8.1.3 維 護(hù) 序 列 3刪除序列 在OEMC的界面中，選擇要?jiǎng)h除的序列， 單擊鼠標(biāo)右鍵，從彈出的快捷菜單中 選擇“移去即可。 用SQL語(yǔ)句刪除一個(gè)序列和刪除別的對(duì)象類(lèi)似。 其語(yǔ)法方式是DROP SEQUENCE sequence_name.更改和刪除序列SQL ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

8、運(yùn)用ALTER SEQUENCE語(yǔ)句修正序列，不能更改序列的START WITH參數(shù)運(yùn)用DROP SEQUENCE語(yǔ)句刪除序列SQL DROP SEQUENCE toys_seq;. 8.2.1 在OEMC中創(chuàng)建同義詞 8.2.2 運(yùn)用SQL命令創(chuàng)建同義詞8.2 同 義 詞.同義詞 3-1同義詞是現(xiàn)有對(duì)象的一個(gè)別名。簡(jiǎn)化SQL語(yǔ)句隱藏對(duì)象的稱(chēng)號(hào)和一切者提供對(duì)對(duì)象的公共訪(fǎng)問(wèn)同義詞共有兩種類(lèi)型：同義詞私有同義詞公有同義詞私有同義詞只能在其方式內(nèi)訪(fǎng)問(wèn)，且不能與當(dāng)前方式的對(duì)象同名。公有同義詞可被一切的數(shù)據(jù)庫(kù)用戶(hù)訪(fǎng)問(wèn)。.同義詞 3-2CREATE SYNONYM emp FOR SCOTT.emp;SC

9、OTT.emp的別名方式名表名私有同義詞公有同義詞CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;同義詞稱(chēng)號(hào).同義詞 3-3創(chuàng)建或交換現(xiàn)有的同義詞CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp;交換現(xiàn)有的同義詞SQL DROP SYNONYM emp; SQL DROP PUBLIC SYNONYM emp_syn; 刪除同義詞.8.3.1 在OEMC中創(chuàng)建存儲(chǔ)過(guò)程8.3.2 運(yùn)用SQL命令創(chuàng)建存儲(chǔ)過(guò)程8.3 存 儲(chǔ) 過(guò) 程.子程序 2-1命名的 PL/SQL 塊，編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中。子程序的各個(gè)部分：聲

10、明部分可執(zhí)行部分異常處置部分(可選)子程序的分類(lèi)：過(guò)程 執(zhí)行某些操作函數(shù) 執(zhí)行操作并前往值.子程序 2-2子程序的優(yōu)點(diǎn)：模塊化將程序分解為邏輯模塊可重用性可以被恣意數(shù)目的程序調(diào)用可維護(hù)性簡(jiǎn)化維護(hù)操作平安性經(jīng)過(guò)設(shè)置權(quán)限，使數(shù)據(jù)更平安.過(guò)程 8-1過(guò)程是用于完成特定義務(wù)的子程序 例如：前往售票廳訊問(wèn)關(guān)于車(chē)票的信息 排隊(duì)等候 在柜臺(tái)購(gòu)買(mǎi)車(chē)票 .過(guò)程 8-2創(chuàng)建過(guò)程的語(yǔ)法：CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN EXCEPTION END;創(chuàng)建過(guò)程，可指定運(yùn)轉(zhuǎn)過(guò)程需傳送的參數(shù)處置異常 包括在過(guò)程中要執(zhí)行的語(yǔ)句 .過(guò)程 8-3CREATE OR REPLACE

11、 PROCEDURE find_emp(emp_no NUMBER)AS empname VARCHAR2(20);BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no; DBMS_OUTPUT.PUT_LINE(雇員姓名是 | empname);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (雇員編號(hào)未找到);END find_emp;/.過(guò)程 8-4過(guò)程參數(shù)的三種方式：IN用于接受調(diào)用程序的值默許的參數(shù)方式OUT用于向調(diào)用程序前往值 IN OUT用于接受調(diào)用

12、程序的值，并向調(diào)用程序前往更新的值.過(guò)程 8-5SQL CREATE OR REPLACE PROCEDURE itemdesc(item_code IN VARCHAR2)IS v_itemdesc VARCHAR2(5);BEGIN SELECT itemdesc INTO v_itemdesc FROM itemfile WHERE itemcode = item_code; DBMS_OUTPUT.PUT_LINE(item_code| 工程的闡明為|v_itemdesc);END;/SQL SET SERVEROUTPUT ONSQL EXECUTE itemdesc(i201);執(zhí)

13、行過(guò)程的語(yǔ)法： EXECUTE procedure_name(parameters_list);.過(guò)程 8-6SQL CREATE OR REPLACE PROCEDURE test( value1 IN VARCHAR2, value2 OUT NUMBER )IS identity NUMBER;BEGIN SELECT ITEMRATE INTO identity FROM itemFile WHERE itemcode = value1; IF identity SET SERVEROUT ONSQL DECLARE num1 NUMBER := 100; num2 NUMBER :=

14、 200;BEGIN swap(num1, num2); DBMS_OUTPUT.PUT_LINE(num1 = | num1); DBMS_OUTPUT.PUT_LINE(num2 = | num2);END;/.過(guò)程 8-8將過(guò)程的執(zhí)行權(quán)限授予其他用戶(hù)：刪除過(guò)程：SQL GRANT EXECUTE ON find_emp TO MARTIN;SQL GRANT EXECUTE ON swap TO PUBLIC;SQL DROP PROCEDURE find_emp;.函數(shù) 4-1函數(shù)是可以前往值的命名的 PL/SQL 子程序。 創(chuàng)建函數(shù)的語(yǔ)法： CREATE OR REPLACE FUN

15、CTION (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;.函數(shù) 4-2定義函數(shù)的限制：函數(shù)只能接受 IN 參數(shù)，而不能接受 IN OUT 或 OUT 參數(shù)形參不能是 PL/SQL 類(lèi)型函數(shù)的前往類(lèi)型也必需是數(shù)據(jù)庫(kù)類(lèi)型訪(fǎng)問(wèn)函數(shù)的兩種方式：運(yùn)用 PL/SQL 塊運(yùn)用 SQL 語(yǔ)句.函數(shù) 4-3創(chuàng)建函數(shù)：從 SQL 語(yǔ)句調(diào)用函數(shù)：CREATE OR REPLACE FUNCTION fun_hello

16、RETURN VARCHAR2ISBEGIN RETURN 朋友，您好;END;/SQL SELECT fun_hello FROM DUAL;.函數(shù) 4-4CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER;BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile; IF price = min_price AND

17、price CREATE OR REPLACE TRIGGER trig_salAFTER UPDATE OF empsal ON salary_records觸發(fā)器語(yǔ)句為 salary_records 表創(chuàng)建 trig-sal 觸發(fā)器在更新 emp_sal 列之后激活觸發(fā)器觸發(fā)器限制SQL FOR EACH ROWWHEN (NEW.empsalOLD.empsal)DECLARESal_diff NUMBER;只需在WHEN子句中的條件得到滿(mǎn)足時(shí)，才激活trig_sal 觸發(fā)器觸發(fā)器操作SQL BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OU

18、TPUT.PUT_LINE(工資差額：sal_diff);END;假設(shè)WHEN子句中的條件得到滿(mǎn)足，將執(zhí)行BEGIN 塊中的代碼.觸發(fā)器的組成部分 3-3Oracle 數(shù)據(jù)庫(kù)更新表保管更新激活觸發(fā)器AFTER 觸發(fā)器的任務(wù)原理BEFORE 觸發(fā)器的任務(wù)原理更新表激活觸發(fā)器保管更新Oracle 數(shù)據(jù)庫(kù).創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER aiu_itemfileAFTER INSERTON itemfileFOR EACH ROWBEGIN IF (:NEW.qty_hand = 0) THEN DBMS_OUTPUT.PUT_LINE(警告：已插入記錄，但數(shù)量為零);

19、 ELSE DBMS_OUTPUT.PUT_LINE(已插入記錄); END IF;END;/.觸發(fā)器類(lèi)型 6-1觸發(fā)器的類(lèi)型有：觸發(fā)器類(lèi)型方式(DDL)觸發(fā)器DML觸發(fā)器數(shù)據(jù)庫(kù)級(jí)觸發(fā)器語(yǔ)句級(jí)觸發(fā)器行級(jí)觸發(fā)器INSTEAD OF觸發(fā)器.觸發(fā)器類(lèi)型 6-2DDL 觸發(fā)器數(shù)據(jù)庫(kù)級(jí)觸發(fā)器DML 觸發(fā)器語(yǔ)句級(jí)觸發(fā)器行級(jí)觸發(fā)器INSTEAD OF 觸發(fā)器在方式中執(zhí)行 DDL 語(yǔ)句時(shí)執(zhí)行在發(fā)生翻開(kāi)、封鎖、登錄和退出數(shù)據(jù)庫(kù)等系統(tǒng)事件時(shí)執(zhí)行在對(duì)表或視圖執(zhí)行DML語(yǔ)句時(shí)執(zhí)行無(wú)論受影響的行數(shù)是多少，都只執(zhí)行一次對(duì)DML語(yǔ)句修正的每個(gè)行執(zhí)行一次用于用戶(hù)不能直接運(yùn)用 DML 語(yǔ)句修正的視圖.觸發(fā)器類(lèi)型 6-3行級(jí)觸發(fā)

20、器SQL CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20);SQL CREATE SEQUENCE SEQ_TEST;SQL CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGIN IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20020, 不允許更新ID值！);

21、END IF;END;/.觸發(fā)器類(lèi)型 6-4SQL CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT OR UPDATE OR DELETE ON order_masterBEGIN IF UPDATING THEN DBMS_OUTPUT.PUT_LINE(已更新 ORDER_MASTER 中的數(shù)據(jù)); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE(已刪除 ORDER_MASTER 中的數(shù)據(jù)); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(已在 ORDER_MASTER 中

22、插入數(shù)據(jù)); END IF;END;/語(yǔ)句級(jí)觸發(fā)器.觸發(fā)器類(lèi)型 6-5SQL CREATE OR REPLACE TRIGGER upd_ord_view INSTEAD OF UPDATE ON ord_view FOR EACH ROW BEGIN UPDATE order_master SET vencode=:NEW.vencode WHERE orderno = :NEW.orderno;DBMS_OUTPUT.PUT_LINE(已激活觸發(fā)器); END; /INSTEAD OF 觸發(fā)器.觸發(fā)器類(lèi)型 6-6SQL CREATE TABLE dropped_obj ( obj_name

23、 VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE);SQL CREATE OR REPLACE TRIGGER log_drop_objAFTER DROP ON SCHEMABEGIN INSERT INTO dropped_obj VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;/方式觸發(fā)器.啟用和禁用觸發(fā)器刪除觸發(fā)器啟用、禁用和刪除觸發(fā)器SQL ALTER TRIGGER aiu_itemfile DISABLE;SQL ALTER TRIGGER aiu_ite

24、mfile ENABLE;SQL DROP TRIGGER aiu_itemfile;.查看有關(guān)觸發(fā)器的信息SQL SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME=EMP;SQL SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME = BIU_EMP_DEPTNO;USER_TRIGGERS 數(shù)據(jù)字典視圖包含有關(guān)觸發(fā)器的信息.程序包程序包是對(duì)相關(guān)過(guò)程、函數(shù)、變量、游標(biāo)和異常等對(duì)象的封裝程序包由規(guī)范和主體兩部分組成

25、聲明程序包中公共對(duì)象。包括類(lèi)型、變量、常量、異常、游標(biāo)規(guī)范和子程序規(guī)范等聲明程序包私有對(duì)象和實(shí)如今包規(guī)范中聲明的子程序和游標(biāo)程序包規(guī)范主體.創(chuàng)建程序包 2-1程序包規(guī)范CREATE OR REPLACE PACKAGE package_name IS|ASPublic item declarationsSubprogram specificationEND package_name;程序包主體CREATE OR REPLACE PACKAGE BODY package_name IS|ASPrivate item declarationsSubprogram bodiesBEGINInitia

26、lizationEND package_name;.創(chuàng)建程序包 2-2CREATE OR REPLACE PACKAGE pack_meIS PROCEDURE order_proc (orno VARCHAR2); FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2;END pack_me;/CREATE OR REPLACE PACKAGE BODY pack_me AS PROCEDURE order_proc (orno VARCHAR2) IS stat CHAR(1); BEGIN SELECT ostatus INTO stat

27、FROM order_master WHERE orderno = orno; END order_proc; FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2 IS icode VARCHAR2(5); ocode VARCHAR2(5); BEGIN END order_fun;END pack_me;/.程序包的優(yōu)點(diǎn)模塊化更輕松的運(yùn)用程序設(shè)計(jì)信息隱藏新增功能性能更佳.有關(guān)子程序和程序包的信息USER_OBJECTS 視圖包含用戶(hù)創(chuàng)建的子程序和程序包的信息USER_SOURCE 視圖存儲(chǔ)子程序和程序包的源代碼SELECT object_n

28、ame, object_typeFROM USER_OBJECTSWHERE object_type IN (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY);SELECT line, text FROM USER_SOURCEWHERE NAME=TEST;.內(nèi)置程序包擴(kuò)展數(shù)據(jù)庫(kù)的功能為 PL/SQL 提供對(duì) SQL 功能的訪(fǎng)問(wèn)用戶(hù) SYS 擁有一切程序包是公有同義詞可以由任何用戶(hù)訪(fǎng)問(wèn).內(nèi)置程序包一些內(nèi)置程序包：程序包名稱(chēng)說(shuō)明STANDARD和DBMS_STANDARD定義和擴(kuò)展PL/SQL語(yǔ)言環(huán)境DBMS_LOB提供對(duì) LOB數(shù)據(jù)類(lèi)型進(jìn)行操作的功能DB

29、MS_OUTPUT處理PL/SQL塊和子程序輸出調(diào)試信息DBMS_RANDOM提供隨機(jī)數(shù)生成器DBMS_SQL允許用戶(hù)使用動(dòng)態(tài) SQLDBMS_XMLDOM用DOM模型讀寫(xiě)XML類(lèi)型的數(shù)據(jù)DBMS_XMLPARSERXML解析，處理XML文檔內(nèi)容和結(jié)構(gòu)DBMS_XMLQUERY提供將數(shù)據(jù)轉(zhuǎn)換為 XML 類(lèi)型的功能DBMS_XSLPROCESSOR提供XSLT功能，轉(zhuǎn)換XML文檔UTL_FILE用 PL/SQL 程序來(lái)讀寫(xiě)操作系統(tǒng)文本文件.內(nèi)置程序包SQL SET SERVEROUTPUT ONSQL BEGIN DBMS_OUTPUT.PUT_LINE(打印三角形); FOR i IN 1.9 LOOP FOR j IN 1.i LOOP DBMS_OUTPUT.PUT(*); END LOOP for_j; DBMS_OUTPUT.NEW_LINE; END LOOP for_i;END;/DBMS_OUTPUT包顯示 PL/SQL 塊和子程序的調(diào)試信息。.內(nèi)置程序包DBMS_LOB 包提供用于處置大型對(duì)象的過(guò)程和函數(shù)DBMS_XMLQUERY 包用于將查詢(xún)結(jié)果轉(zhuǎn)換為 XML 格式.內(nèi)置程序包SQL DECLARE result CLOB; xmlstr VARCHAR2