Oracle10g數(shù)據(jù)庫(kù)管理系統(tǒng)

1、實(shí)用數(shù)據(jù)庫(kù)技術(shù)實(shí)用數(shù)據(jù)庫(kù)技術(shù) Oracle10g數(shù)據(jù)庫(kù)管理系統(tǒng)學(xué)習(xí)內(nèi)容學(xué)習(xí)內(nèi)容lOracle 概述lOracle 10g數(shù)據(jù)庫(kù)安裝lOracle 10g數(shù)據(jù)庫(kù)卸載 lOracle 10g常用管理工具lOracle10g表管理l使用過程和觸發(fā)器Oracle 概述概述lOracle公司 lOracle數(shù)據(jù)庫(kù)Oracle 10g數(shù)據(jù)庫(kù)安裝數(shù)據(jù)庫(kù)安裝l服務(wù)器安裝需求l版本l企業(yè)版l標(biāo)準(zhǔn)版l個(gè)人版l最低配置 CPU：Pentium 166 內(nèi)存：128MB 硬盤空間：企業(yè)版(176GB)：標(biāo)準(zhǔn)版(176GB)：個(gè)人版(172GB) 視頻：256色Oracle 10g數(shù)據(jù)庫(kù)安裝數(shù)據(jù)庫(kù)安裝l客戶端安裝需求l

2、安裝類型 l管理者(administrator) l運(yùn)行時(shí)環(huán)境(runtime)l自定義(custom)l最低配置 CPU：Pentium 166 內(nèi)存：128MB 硬盤空間：管理者(647MB)：運(yùn)行時(shí)環(huán)境(486MB) Oracle 10g數(shù)據(jù)庫(kù)安裝數(shù)據(jù)庫(kù)安裝過程過程l(略)Oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)卸載卸載 l1、停止服務(wù) l2、卸載Oracle產(chǎn)品l3、清理注冊(cè)表l4、清理環(huán)境變量l5、清理磁盤 Oracle卸載卸載 1/5l開始設(shè)置控制面板管理工具服務(wù)停止所有Oracle服務(wù)Oracle卸載卸載 2/5l開始程序Oracle Installation Products Univers

3、al Installer l卸裝所有Oracle產(chǎn)品，但Universal Installer本身不能被刪除 Oracle卸載卸載 3/5l運(yùn)行regeditlHKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices 滾動(dòng)這個(gè)列表，刪除所有Oracle入口。lHKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication，除所有Oracle入口。Oracle卸載卸載 4/5l開始設(shè)置控制面板系統(tǒng)高級(jí)環(huán)境變量 l刪除環(huán)境變量CLASSPATH和PATH中有關(guān)Oracle的設(shè)定 Or

4、acle卸載卸載 5/5l從桌面上、STARTUP（啟動(dòng)）組、程序菜單中，刪除所有有關(guān)Oracle的組和圖標(biāo) l刪除Program FilesOracle目錄 l重新啟動(dòng)計(jì)算機(jī)，重起后才能完全刪除Oracle所在目錄,l刪除與Oracle有關(guān)的文件，選擇Oracle所在的缺省目錄C:Oracle，刪除這個(gè)入 口目錄及所有子目錄，并從Windows 2000目錄（一般為C:WINNT）下刪除以下文件ORACLE.INI、oraodbc.ini等等。Oracle10g常用管理工具常用管理工具lOEM控制臺(tái)lSQL*PLUS工具介紹Oracle10g表管理表管理l在Oracle中建表，主要有兩種方式

5、：l一種是在SQL*PLUS中執(zhí)行建表的SQL語(yǔ)句。l另一種是通過OEM工具。 Oracle10g數(shù)據(jù)中的數(shù)據(jù)類型數(shù)據(jù)中的數(shù)據(jù)類型 數(shù)據(jù)類型數(shù)據(jù)類型類型說明類型說明CHAR字符型，最長(zhǎng)為字符型，最長(zhǎng)為2000BNCHAR基于字符集的字符型，同上基于字符集的字符型，同上VARCHAR2 變長(zhǎng)字符型，最長(zhǎng)為變長(zhǎng)字符型，最長(zhǎng)為4000BNVARCHAR2基于字符集的變長(zhǎng)字符型，同上基于字符集的變長(zhǎng)字符型，同上VARCHAR 同同VARCHAR2LONG變長(zhǎng)字符型，最長(zhǎng)為變長(zhǎng)字符型，最長(zhǎng)為2GBNUMBER(s,d)數(shù)字型，總位數(shù)為數(shù)字型，總位數(shù)為s位，小數(shù)位為位，小數(shù)位為d位，總長(zhǎng)最大為位，總長(zhǎng)最大

6、為38位位DATE 日期型日期型RAW純二進(jìn)制數(shù)據(jù)類型，最長(zhǎng)為純二進(jìn)制數(shù)據(jù)類型，最長(zhǎng)為2000BLONG RAW 變長(zhǎng)二進(jìn)制數(shù)據(jù)類型，最長(zhǎng)為變長(zhǎng)二進(jìn)制數(shù)據(jù)類型，最長(zhǎng)為2GBBLOB二進(jìn)制大對(duì)象類型，最長(zhǎng)為二進(jìn)制大對(duì)象類型，最長(zhǎng)為4GBNLOB包含定寬的多位數(shù)字符的字符大對(duì)象類型，最長(zhǎng)為包含定寬的多位數(shù)字符的字符大對(duì)象類型，最長(zhǎng)為4GBCLOB包含單個(gè)字符的字符大對(duì)象類型，最長(zhǎng)為包含單個(gè)字符的字符大對(duì)象類型，最長(zhǎng)為4GBBFILE數(shù)據(jù)庫(kù)外的大型二進(jìn)制文件的輸入指示器類型，最大為數(shù)據(jù)庫(kù)外的大型二進(jìn)制文件的輸入指示器類型，最大為4GBPL/SQL語(yǔ)言基礎(chǔ)語(yǔ)言基礎(chǔ)l注釋l變量聲明l運(yùn)算符lPL/SQL

7、塊結(jié)構(gòu)l控制語(yǔ)句PL/SQL 程序設(shè)計(jì)簡(jiǎn)介程序設(shè)計(jì)簡(jiǎn)介lPL /SQL是一種高級(jí)數(shù)據(jù)庫(kù)程序設(shè)計(jì)語(yǔ)言，該語(yǔ)言專門用于在各種環(huán)境下對(duì)ORACLE數(shù)據(jù)庫(kù)進(jìn)行訪問。由于該語(yǔ)言集成于數(shù)據(jù)庫(kù)服務(wù)器中，所以PL/SQL代碼可以對(duì)數(shù)據(jù)進(jìn)行快速高效的處理。除此之外，可以在ORACLE數(shù)據(jù)庫(kù)的某些客戶端工具中，使用PL/SQL語(yǔ)言也是該語(yǔ)言的一個(gè)特點(diǎn)。 SQL與PL/SQLlPL/SQL是 Procedure Language & Structured Query Language 的縮寫。ORACLE的SQL是支持ANSI(American national Standards Institute)和I

8、SO92 (International Standards Organization)標(biāo)準(zhǔn)的產(chǎn)品。PL/SQL是對(duì)SQL語(yǔ)言存儲(chǔ)過程語(yǔ)言的擴(kuò)展。 為什么使用PL/SQLl有利于客戶/服務(wù)器環(huán)境應(yīng)用的運(yùn)行 l適合于客戶環(huán)境 PL/SQL塊結(jié)構(gòu)塊結(jié)構(gòu)lPL/SQL程序由三個(gè)塊組成，即聲明部分、執(zhí)行部分、異常處理部分。DECLARE /* 聲明部分: 在此聲明PL/SQL用到的變量,類型及游標(biāo)，以及局部的存儲(chǔ)過程和函數(shù) */BEGIN /* 執(zhí)行部分: 過程及SQL 語(yǔ)句 , 即程序的主要部分 */EXCEPTION /* 執(zhí)行異常部分: 錯(cuò)誤處理 */END;PL/SQL塊可以分為三類 l無名塊：

9、動(dòng)態(tài)構(gòu)造，只能執(zhí)行一次。l子程序：存儲(chǔ)在數(shù)據(jù)庫(kù)中的存儲(chǔ)過程、函數(shù)及包等。當(dāng)在數(shù)據(jù)庫(kù)上建立好后可以在其它程序中調(diào)用它們。l觸發(fā)器：當(dāng)數(shù)據(jù)庫(kù)發(fā)生操作時(shí)，會(huì)觸發(fā)一些事件，從而自動(dòng)執(zhí)行相應(yīng)的程序。標(biāo)識(shí)符lPL/SQL程序設(shè)計(jì)中的標(biāo)識(shí)符定義與SQL 的標(biāo)識(shí)符定義的要求相同。要求和限制有：l標(biāo)識(shí)符名不能超過30字符；l第一個(gè)字符必須為字母；l不分大小寫；l不能用-(減號(hào));l不能是SQL保留字。實(shí)例實(shí)例l下面的例子將會(huì)刪除所有的紀(jì)錄，而不是KING 的記錄。DECLARE v_ename varchar2(20) :=KING; BEGIN DELETE FROM emp WHERE ename=v_en

10、ame; END;如何運(yùn)行如何運(yùn)行PL/SQL塊結(jié)構(gòu)？塊結(jié)構(gòu)？ 如何運(yùn)行如何運(yùn)行PL/SQL塊結(jié)構(gòu)？塊結(jié)構(gòu)？ 建議的命名方法建議的命名方法PL/SQL 變量類型實(shí)例l插入一條記錄并顯示 DECLARE Row_id ROWID; info VARCHAR2(40);BEGININSERT INTO dept VALUES (90, SERVICE, BEIJING)RETURNING rowid, dname|:|to_char(deptno)|:|locINTO row_id, info;DBMS_OUTPUT.PUT_LINE(ROWID:|row_id);DBMS_OUTPUT.PUT_

11、LINE(info);END;PL/SQL 變量類型實(shí)例l其中，RETURNING子句用于檢索INSERT語(yǔ)句中所影響的數(shù)據(jù)行數(shù)，當(dāng)INSERT語(yǔ)句使用VALUES 子句插入數(shù)據(jù)時(shí)，RETURNING 子句還可將列表達(dá)式、ROWID和REF值返回到輸出變量中。在使用RETURNING 子句是應(yīng)注意以下幾點(diǎn)限制：l不能并行DML語(yǔ)句；l不能檢索LONG 類型信息；l當(dāng)通過視圖向基表中插入數(shù)據(jù)時(shí)，只能與單基表視圖一起使用。PL/SQL 變量類型實(shí)例l修改一條記錄并顯示 DECLARE Row_id ROWID; info VARCHAR2(40);BEGINUPDATE dept SET dept

12、no=80 WHERE DNAME=SERVICERETURNING rowid, dname|:|to_char(deptno)|:|locINTO row_id, info;DBMS_OUTPUT.PUT_LINE(ROWID:|row_id);DBMS_OUTPUT.PUT_LINE(info);END;PL/SQL 變量類型實(shí)例l其中，RETURNING子句用于檢索被修改行信息，當(dāng)UPDATE語(yǔ)句修改單行數(shù)據(jù)時(shí)，RETURNING 子句可以檢索被修改行的ROWID值，以及行中被修改列的列表達(dá)式，并可將他們存儲(chǔ)到PL/SQL變量或復(fù)合變量中；當(dāng)UPDATE語(yǔ)句修改多行數(shù)據(jù)時(shí)，RETURN

13、ING 子句可以將被修改行的ROWID值，以及列表達(dá)式值返回到復(fù)合變量數(shù)組中。在UPDATE中使用RETURNING 子句的限制與INSERT語(yǔ)句中對(duì)RETURNING子句的限制相同。PL/SQL 變量類型實(shí)例l刪除一條記錄并顯示 DECLARE Row_id ROWID; info VARCHAR2(40);BEGINDELETE dept WHERE DNAME=SERVICERETURNING rowid, dname|:|to_char(deptno)|:|locINTO row_id, info;DBMS_OUTPUT.PUT_LINE(ROWID:|row_id);DBMS_OUT

14、PUT.PUT_LINE(info);END;PL/SQL 變量類型實(shí)例lRETURNING子句用于檢索被刪除行信息，當(dāng)DELETE語(yǔ)句修改單行數(shù)據(jù)時(shí)，RETURNING 子句可以檢索被刪除行的ROWID，以及被刪除行中列的列表達(dá)式，并可將他們存儲(chǔ)到PL/SQL變量或復(fù)合變量中；當(dāng)UPDATE語(yǔ)句修改多行數(shù)據(jù)時(shí)，RETURNING 子句可以將被修改行的ROWID，以及列表達(dá)式值返回到復(fù)合變量數(shù)組中。在UPDATE中使用RETURNING 子句的限制與INSERT語(yǔ)句中對(duì)RETURNING子句的限制相同 。復(fù)合類型復(fù)合類型lORACLE 在 PL/SQL 中除了提供象前面介紹的各種類型外，還提供

15、一種稱為復(fù)合類型的類型-記錄和表。l記錄類型l使用%TYPEl使用%ROWTYPE記錄類型記錄類型l記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元存儲(chǔ)起來，它必須包括至少一個(gè)標(biāo)量型（數(shù)字型、字符型、布爾型、日期型）或RECORD 數(shù)據(jù)類型的成員，稱作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但邏輯相關(guān)的信息。定義記錄類型語(yǔ)法如下:TYPE record_type IS RECORD( Field1 type1 NOT NULL := exp1 , Field2 type2 NOT NULL := exp2 , . . . . . . Fieldn typen NOT NULL

16、 := expn ) ;實(shí)例實(shí)例DECLARE TYPE test_rec IS RECORD( Code VARCHAR2(10), Name VARCHAR2(30) NOT NULL :=a book); V_book test_rec;BEGIN V_book.code :=123; V_ :=C+ Programming; DBMS_OUTPUT.PUT_LINE(v_book.code|v_);END;使用使用%TYPEl定義一個(gè)變量，其數(shù)據(jù)類型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫(kù)表的某個(gè)列的數(shù)據(jù)類型相同，這時(shí)可以使用%TYPE。l使

17、用%TYPE特性的優(yōu)點(diǎn)在于：l所引用的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類型可以不必知道；l所引用的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類型可以實(shí)時(shí)改變。實(shí)例實(shí)例 1DECLARE - 用 %TYPE 類型定義與表相配的字段 TYPE t_Record IS RECORD( T_no emp.empno%TYPE, T_name emp.ename%TYPE, T_sal emp.sal%TYPE ); - 聲明接收數(shù)據(jù)的變量 v_emp t_Record;BEGIN SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE(

18、TO_CHAR(v_emp.t_no)|v_emp.t_name|TO_CHAR(v_emp.t_sal);END;實(shí)例實(shí)例 2DECLARE v_empno emp.empno%TYPE :=&no; Type r_record is record ( v_name emp.ename%TYPE, v_sal emp.sal%TYPE, v_date emp.hiredate%TYPE); Rec r_record;BEGIN SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno; DBMS_OUTPUT.

19、PUT_LINE(Rec.v_name|-|Rec.v_sal|-|Rec.v_date);END;使用使用%ROWTYPElPL/SQL 提供%ROWTYPE操作符, 返回一個(gè)記錄類型，其數(shù)據(jù)類型和數(shù)據(jù)庫(kù)表的數(shù)據(jù)結(jié)構(gòu)相一致。l使用%ROWTYPE特性的優(yōu)點(diǎn)在于：l所引用的數(shù)據(jù)庫(kù)中列的個(gè)數(shù)和數(shù)據(jù)類型可以不必知道；l所引用的數(shù)據(jù)庫(kù)中列的個(gè)數(shù)和數(shù)據(jù)類型可以實(shí)時(shí)改變。實(shí)例實(shí)例DECLARE v_empno emp.empno%TYPE :=&no; rec emp%ROWTYPE;BEGIN SELECT * INTO rec FROM emp WHERE empno=v_empno; DB

20、MS_OUTPUT.PUT_LINE(姓名:|rec.ename|工資:|rec.sal|工作時(shí)間:|rec.hiredate); END;運(yùn)算符和表達(dá)式l算術(shù)運(yùn)算符 運(yùn)算符和表達(dá)式l關(guān)系運(yùn)算符運(yùn)算符和表達(dá)式l邏輯運(yùn)算符變量賦值l在PL/SQL編程中，變量賦值是一個(gè)值得注意的地方，它的語(yǔ)法如下：l variable 是一個(gè)PL/SQL變量, expression 是一個(gè)PL/SQL 表達(dá)式。 variable := expression ; 字符及數(shù)字運(yùn)算特點(diǎn)字符及數(shù)字運(yùn)算特點(diǎn) l空值加數(shù)字仍是空值：NULL + = NULLl空值加（連接）字符，結(jié)果為字符：NULL | = BOOLEAN

21、賦值賦值 l布爾值只有TRUE, FALSE及 NULL 三個(gè)值。如：DECLAREdone BOOLEAN;/* the following statements are legal: */BEGIN done := FALSE; WHILE NOT done LOOP Null; END LOOP;END;游標(biāo)賦值游標(biāo)賦值 l游標(biāo)賦值是通過 SELECT語(yǔ)句來完成的，每次執(zhí)行 SELECT語(yǔ)句就賦值一次，一般要求被賦值的變量與SELECT中的列名要一一對(duì)應(yīng)。游標(biāo)賦值游標(biāo)賦值 DECLAREemp_id emp.empno%TYPE :=7788;emp_name emp.ename%TYP

22、E;wages emp.sal%TYPE;BEGINSELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages FROM emp WHERE empno = emp_id;DBMS_OUTPUT.PUT_LINE(emp_name|-|to_char(wages);END;結(jié)果：SCOTT-3000可轉(zhuǎn)換的類型賦值可轉(zhuǎn)換的類型賦值 lCHAR 轉(zhuǎn)換為轉(zhuǎn)換為 NUMBER：l使用 TO_NUMBER 函數(shù)來完成字符到數(shù)字的轉(zhuǎn)換，如：lv_total := TO_NUMBER(100.0) + sal;lNUMBER 轉(zhuǎn)換為轉(zhuǎn)換為CHAR

23、：l使用 TO_CHAR函數(shù)可以實(shí)現(xiàn)數(shù)字到字符的轉(zhuǎn)換，如：lv_comm := TO_CHAR(123.45) | 元 ;l字符轉(zhuǎn)換為日期：字符轉(zhuǎn)換為日期：l使用 TO_DATE函數(shù)可以實(shí)現(xiàn) 字符到日期的轉(zhuǎn)換，如：lv_date := TO_DATE(2001.07.03,yyyy.mm.dd);l日期轉(zhuǎn)換為字符：日期轉(zhuǎn)換為字符：l使用 TO_CHAR函數(shù)可以實(shí)現(xiàn)日期到字符的轉(zhuǎn)換，如：lv_to_day := TO_CHAR(SYSDATE, yyyy.mm.dd hh24:mi:ss) ;變量作用范圍及可見性變量作用范圍及可見性 lPL/SQL的變量作用范圍特點(diǎn)是：l變量的作用范圍是在你所引

24、用的程序單元（塊、子程序、包）內(nèi)。即從聲明變量開始到該塊的結(jié)束。l一個(gè)變量（標(biāo)識(shí)）只能在你所引用的塊內(nèi)是可見的。l當(dāng)一個(gè)變量超出了作用范圍，PL/SQL引擎就釋放用來存放該變量的空間（因?yàn)樗赡懿挥昧耍?。l在子塊中重新定義該變量后，它的作用僅在該塊內(nèi)。實(shí)例實(shí)例 DECLARE Emess char(80);BEGIN DECLARE V1 NUMBER(4); BEGIN SELECT empno INTO v1 FROM emp WHERE LOWER(job)=president; DBMS_OUTPUT.PUT_LINE(V1); EXCEPTION When TOO_MANY_ROWS

25、 THEN DBMS_OUTPUT.PUT_LINE (More than one president); END;實(shí)例實(shí)例 續(xù)續(xù) DECLARE V1 NUMBER(4); BEGIN SELECT empno INTO v1 FROM emp WHERE LOWER(job)=manager; EXCEPTION When TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (More than one manager); END;EXCEPTION When others THEN Emess:=substr(SQLERRM,1,80); DBMS_OUTPU

26、T.PUT_LINE (emess);END;注釋注釋 在PL/SQL里，可以使用兩種符號(hào)來寫注釋，即：l使用雙 - ( 減號(hào)) 加注釋 V_Sal NUMBER(12,2); - 工資變量。 l使用 /* */ 來加一行或多行注釋 /*/ /* 文件名： department_salary.sql */ /*/示例 簡(jiǎn)單數(shù)據(jù)插入例子 DECLAREv_ename VARCHAR2(20) := Bill;v_sal NUMBER(7,2) :=1234.56;v_deptno NUMBER(2) := 10;v_empno NUMBER(4) := 8888;BEGININSERT INTO

27、 emp ( empno, ename, JOB, sal, deptno , hiredate ) VALUES ( v_empno, v_ename, Manager, v_sal, v_deptno, TO_DATE(1954.06.09,yyyy.mm.dd) );COMMIT;END;示例 簡(jiǎn)單數(shù)據(jù)刪除例子 DECLAREv_empno number(4) := 8888;BEGINDELETE FROM emp WHERE empno=v_empno;COMMIT;END;PL/SQL流程控制語(yǔ)句流程控制語(yǔ)句 lPL/SQL的流程控制語(yǔ)句包括如下三類：l控制語(yǔ)句：IF 語(yǔ)句；l循環(huán)

28、語(yǔ)句：LOOP語(yǔ)句，EXIT語(yǔ)句；l順序語(yǔ)句：GOTO語(yǔ)句，NULL語(yǔ)句。 條件語(yǔ)句IF THEN PL/SQL 和 SQL語(yǔ)句END IF;IF THENPL/SQL 和 SQL語(yǔ)句ELSE其它語(yǔ)句END IF;條件語(yǔ)句IF THEN PL/SQL 和 SQL語(yǔ)句ELSIF THEN 其它語(yǔ)句ELSIF THEN 其它語(yǔ)句ELSE 其它語(yǔ)句END IF;條件語(yǔ)句 實(shí)例DECLARE v_empno emp.empno%TYPE :=&empno; V_salary emp.sal%TYPE; V_comment VARCHAR2(35);BEGIN SELECT sal INTO v

29、_salary FROM emp WHERE empno=v_empno; IF v_salary1500 THEN V_comment:= Fairly less; ELSIF v_salary 3000 THEN V_comment:= A little more; ELSE V_comment:= Lots of salary; END IF; DBMS_OUTPUT.PUT_LINE(V_comment);END;CASE 表達(dá)式CASE selectorWHEN expression1 THEN result1WHEN expression2 THEN result2WHEN exp

30、ressionN THEN resultN ELSE resultN+1END;CASE 表達(dá)式 實(shí)例DECLAREV_grade char(1) := UPPER(&p_grade);V_appraisal VARCHAR2(20);BEGINV_appraisal :=CASE v_gradeWHEN A THEN ExcellentWHEN B THEN Very GoodWHEN C THEN GoodELSE No such gradeEND;DBMS_OUTPUT.PUT_LINE(Grade:|v_grade| Appraisal: | v_appraisal);END;

31、CASE 表達(dá)式 實(shí)例代碼運(yùn)行結(jié)果：Enter value for p_grade: Aold 2: V_grade char(1) := UPPER(&p_grade);new 2: V_grade char(1) := UPPER(A);Grade:A Appraisal: Excellent循環(huán)l簡(jiǎn)單循環(huán)簡(jiǎn)單循環(huán) LOOP 要執(zhí)行的語(yǔ)句; EXIT WHEN /*條件滿足，退出循環(huán)語(yǔ)句*/END LOOP;LOOP循環(huán)循環(huán) 實(shí)例實(shí)例DECLARE int NUMBER(2) :=0;BEGIN LOOP int := int + 1; DBMS_OUTPUT.PUT_LINE(i

32、nt 的當(dāng)前值為:|int); EXIT WHEN int =10; END LOOP;END;循環(huán)lWHILE 循環(huán)循環(huán) WHILE LOOP 要執(zhí)行的語(yǔ)句;END LOOP;WHILE循環(huán)循環(huán) 實(shí)例實(shí)例DECLARE x NUMBER :=1;BEGIN WHILE x=10 LOOP DBMS_OUTPUT.PUT_LINE(X的當(dāng)前值為:|x); x:= x+1; END LOOP;END;循環(huán)循環(huán) l數(shù)字式循環(huán)l每循環(huán)一次，循環(huán)變量自動(dòng)加1，使用關(guān)鍵字REVERSE，循環(huán)變量自動(dòng)減1。跟在IN REVERSE 后面的數(shù)字必須是從小到大的順序，而且必須是整數(shù)，不能是變量或表達(dá)式。可以使

33、用EXIT 退出循環(huán)。FOR 循環(huán)計(jì)數(shù)器 IN REVERSE 下限 . 上限 LOOP 要執(zhí)行的語(yǔ)句;END LOOP;數(shù)字式循環(huán)數(shù)字式循環(huán) 實(shí)例實(shí)例BEGIN FOR int in 1.10 LOOP DBMS_OUTPUT.PUT_LINE(int 的當(dāng)前值為: |int); END LOOP;END;數(shù)字式循環(huán)數(shù)字式循環(huán) 實(shí)例實(shí)例CREATE TABLE temp_table(num_col NUMBER);DECLAREV_counter NUMBER := 10;BEGIN INSERT INTO temp_table(num_col) VALUES (v_counter ); F

34、OR v_counter IN 20 . 25 LOOP INSERT INTO temp_table (num_col ) VALUES ( v_counter ); END LOOP; INSERT INTO temp_table(num_col) VALUES (v_counter ); FOR v_counter IN REVERSE 20 . 25 LOOP INSERT INTO temp_table (num_col ) VALUES ( v_counter ); END LOOP;END ;DROP TABLE temp_table;GOTO和標(biāo)簽ll標(biāo)號(hào)是用括起來的標(biāo)識(shí)符 G

35、OTO label;GOTO和標(biāo)簽 實(shí)例DECLARE V_counter NUMBER := 1;BEGIN LOOP DBMS_OUTPUT.PUT_LINE(V_counter的當(dāng)前值為:|V_counter); V_counter := v_counter + 1; IF v_counter 10 THEN GOTO l_ENDofLOOP; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(V_counter的當(dāng)前值為:|V_counter);END ;NULL 語(yǔ)句 DECLARE. . .BEGIN IF v_num IS NULL THEN GOTO

36、 print1; END IF; NULL; - 不需要處理任何數(shù)據(jù)。END;游標(biāo)概念l為了處理 SQL 語(yǔ)句獲取一個(gè)表中記錄，ORACLE提供一個(gè)上下文，它提供了一個(gè)指向語(yǔ)句的指針以及查詢的活動(dòng)集(active set)。游標(biāo)是一個(gè)指向上下文的句柄( handle)或指針。通過游標(biāo)，PL/SQL可以控制上下文區(qū)和處理語(yǔ)句時(shí)上下文區(qū)會(huì)發(fā)生些什么事情。游標(biāo)概念l對(duì)于不同的SQL語(yǔ)句，游標(biāo)的使用情況不同： 處理顯式游標(biāo)處理顯式游標(biāo)l顯式游標(biāo)處理需四個(gè) PL/SQL步驟 ：l定義游標(biāo) 格式：CURSOR cursor_name IS select_statement;l打開游標(biāo) 格式：OPEN cu

37、rsor_name;l提取游標(biāo)數(shù)據(jù) 格式：FETCH cursor_name INTO variable_list | record_variable ; l關(guān)閉游標(biāo) 格式：CLOSE cursor_name;示例 1 DECLARE CURSOR c_cursor IS SELECT ename, sal FROM emp WHERE rownum11; v_ename emp.ename%TYPE; v_sal emp.sal%TYPE;BEGINOPEN c_cursor;FETCH c_cursor INTO v_ename, v_sal;WHILE c_cursor %FOUND L

38、OOP DBMS_OUTPUT.PUT_LINE(v_ename|-|to_char(v_sal) ); FETCH c_cursor INTO v_ename, v_sal;END LOOP;CLOSE c_cursor;END;示例 2 DECLAREDeptRec dept%ROWTYPE;Dept_name dept.dname%TYPE;Dept_loc dept.loc%TYPE;CURSOR c1 IS SELECT dname, loc FROM dept WHERE deptno = 30;CURSOR c2(dept_no NUMBER DEFAULT 10) ISSELE

39、CT dname, loc FROM dept WHERE deptno = dept_no;CURSOR c3(dept_no NUMBER DEFAULT 10) IS SELECT * FROM dept WHERE deptno 20);LOOPFETCH c3 INTO deptrec;EXIT WHEN c3%NOTFOUND;DBMS_OUTPUT.PUT_LINE(deptrec.deptno|-|deptrec.dname |-|deptrec.loc);END LOOP;CLOSE c3;END;游標(biāo)屬性游標(biāo)屬性 l %FOUND 布爾型屬性，當(dāng)最近一次讀記錄時(shí)成功返回,則

40、值為TRUE；l %NOTFOUND 布爾型屬性，與%FOUND相反；l %ISOPEN 布爾型屬性，當(dāng)游標(biāo)已打開時(shí)返回 TRUE；l %ROWCOUNT 數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù)。示例DECLARE v_empno emp.empno%TYPE; v_sal emp.sal%TYPE; CURSOR c_cursor IS SELECT empno, sal FROM emp; BEGIN OPEN c_cursor; LOOP FETCH c_cursor INTO v_empno, v_sal; EXIT WHEN c_cursor %NOTFOUND; IF v_sal=

41、1200 THEN UPDATE emp SET sal=sal+50 WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE(編碼為|v_empno|工資已更新!);END IF;DBMS_OUTPUT.PUT_LINE(記錄數(shù):| c_cursor %ROWCOUNT); END LOOP; CLOSE c_cursor;END; 游標(biāo)的FOR循環(huán) FOR index_variable IN cursor_namevalue, value LOOP- 游標(biāo)數(shù)據(jù)處理代碼END LOOP;游標(biāo)中使用游標(biāo)中使用FOR循環(huán)示例循環(huán)示例 DECLARE CURSOR c_

42、sal IS SELECT empno, ename, sal FROM emp ;BEGIN-隱含打開游標(biāo) FOR v_sal IN c_sal LOOP -隱含執(zhí)行一個(gè)FETCH語(yǔ)句 DBMS_OUTPUT.PUT_LINE( to_char(v_sal.empno)|-|v_sal.ename|-|to_char(v_sal.sal) ; -隱含監(jiān)測(cè)c_sal%NOTFOUND END LOOP;-隱含關(guān)閉游標(biāo)END;有參數(shù)游標(biāo)中使用有參數(shù)游標(biāo)中使用FOR循環(huán)示例循環(huán)示例 DECLARECURSOR c_cursor(dept_no NUMBER DEFAULT 10) ISSELECT

43、 dname, loc FROM dept WHERE deptno 30)DBMS_OUTPUT.PUT_LINE(c1_rec.dname|-|c1_rec.loc);END LOOP;DBMS_OUTPUT.PUT_LINE(CHR(10)|使用默認(rèn)的dept_no參數(shù)值10：);FOR c1_rec IN c_cursor LOOPDBMS_OUTPUT.PUT_LINE(c1_rec.dname|-|c1_rec.loc);END LOOP;END;游標(biāo)游標(biāo)FOR循環(huán)語(yǔ)句中使用子查詢來實(shí)循環(huán)語(yǔ)句中使用子查詢來實(shí)現(xiàn)游標(biāo)現(xiàn)游標(biāo) 實(shí)例實(shí)例BEGINFOR c1_rec IN (SELECT

44、 dname, loc FROM dept) LOOPDBMS_OUTPUT.PUT_LINE(c1_rec.dname|-|c1_rec.loc);END LOOP;END;處理隱式游標(biāo)l當(dāng)查詢返回結(jié)果超過一行時(shí)，就需要一個(gè)顯式游標(biāo)。顯式游標(biāo)主要是用于對(duì)查詢語(yǔ)句的處理，尤其是在查詢結(jié)果為多條記錄的情況下；而對(duì)于非查詢語(yǔ)句，如修改、刪除操作，則由ORACLE 系統(tǒng)自動(dòng)地為這些操作設(shè)置游標(biāo)并創(chuàng)建其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱為隱式游標(biāo)，隱式游標(biāo)的名字為SQL，這是由ORACLE 系統(tǒng)定義的。對(duì)于隱式游標(biāo)的操作，如定義、打開、取值及關(guān)閉操作，都由ORACLE 系統(tǒng)自動(dòng)地完成，無需用戶進(jìn)行處理

45、。用戶只能通過隱式游標(biāo)的相關(guān)屬性，來完成相應(yīng)的操作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶自定義的顯示游標(biāo)無關(guān)的、最新處理的一條SQL 語(yǔ)句所包含的數(shù)據(jù)。處理隱式游標(biāo)處理隱式游標(biāo)格式調(diào)用為： SQL%當(dāng)執(zhí)行一條DML語(yǔ)句后，DML語(yǔ)句的結(jié)果保存在四個(gè)游標(biāo)屬性中，這些屬性用于控制程序流程或者了解程序的狀態(tài)。當(dāng)運(yùn)行DML語(yǔ)句時(shí)，PL/SQL打開一個(gè)內(nèi)建游標(biāo)并處理結(jié)果，游標(biāo)是維護(hù)查詢結(jié)果的內(nèi)存中的一個(gè)區(qū)域，游標(biāo)在運(yùn)行DML語(yǔ)句時(shí)打開，完成后關(guān)閉。處理隱式游標(biāo)處理隱式游標(biāo)隱 式 游 標(biāo) 只 使 用 S Q L % F O U N D 、SQL%NOTFOUND、SQL%ROWCOUNT三 個(gè) 屬

46、性 ， S Q L % F O U N D ，S Q L % N O T F O U N D 是 布 爾 值 ，SQL%ROWCOUNT是整數(shù)值。 處理隱式游標(biāo)處理隱式游標(biāo)SQL%FOUND，布爾型屬性，當(dāng)最近一次讀記錄時(shí)成功返回，則值為TRUE。在執(zhí)行任何DML語(yǔ)句前SQL%FOUND和SQL%NOTFOUND的值都是NULL，在執(zhí)行DML語(yǔ)句后，SQL%FOUND的屬性值將是：INSERT為TRUE；DELETE和UPDATE，至少有一行被DELETE或UPDATE為TRUE；SELECT INTO至少返回一行為TRUE；SQL%FOUND為TRUE時(shí),SQL%NOTFOUND為FALSE

47、。處理隱式游標(biāo)處理隱式游標(biāo) 示例示例DECLAREV_deptno emp.deptno%TYPE :=&p_deptno;BEGINDELETE FROM emp WHERE deptno=v_deptno;IF SQL%NOTFOUND THENDELETE FROM dept WHERE deptno=v_deptno;END IF;END;異常錯(cuò)誤處理l異常情況處理是用來處理正常執(zhí)行過程中未預(yù)料的事件，程序塊的異常處理預(yù)定義的錯(cuò)誤和自定義錯(cuò)誤，由于PL/SQL程序塊一旦產(chǎn)生異常而沒有指出如何處理時(shí)，程序就會(huì)自動(dòng)終止整個(gè)程序運(yùn)行。有三種類型的異常：有三種類型的異常：l預(yù)定義預(yù)定義

48、 ( Predefined ) 異常異常lORACLE預(yù)定義的異常情況大約有24個(gè)。對(duì)這種異常情況的處理，無需在程序中定義，由ORACLE自動(dòng)將其引發(fā)。l非預(yù)定義非預(yù)定義 ( Predefined ) 異常異常l即其他標(biāo)準(zhǔn)的ORACLE錯(cuò)誤。對(duì)這種異常情況的處理，需要用戶在程序中定義，然后由ORACLE自動(dòng)將其引發(fā)。l用戶定義用戶定義(User_define) 異常異常l程序執(zhí)行過程中，出現(xiàn)編程人員認(rèn)為的非正常情況。對(duì)這種異常情況的處理，需要用戶在程序中定義，然后顯式地在程序中將其引發(fā)。異常處理部分結(jié)構(gòu)異常處理部分結(jié)構(gòu) EXCEPTION WHEN first_exception THEN W

49、HEN second_exception THEN WHEN OTHERS THEN END;預(yù)定義的異常處理實(shí)例實(shí)例DECLARE v_empno emp.empno%TYPE :=&empno; v_sal emp.sal%TYPE;BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=v_empno; IF v_sal=1500 THEN UPDATE emp SET sal=sal+100 WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE(編碼為|v_empno|員工工資已更新!); ELSEDBMS

50、_OUTPUT.PUT_LINE(編碼為|v_empno|員工工資已經(jīng)超過規(guī)定值!); END IF;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(數(shù)據(jù)庫(kù)中沒有編碼為|v_empno|的員工); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(程序運(yùn)行錯(cuò)誤!請(qǐng)使用游標(biāo)); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END; 非預(yù)定義的異常處理l非定義的ORACLE錯(cuò)誤進(jìn)行定義，步驟如下： l1.在PL/SQL 塊的定義

51、部分定義異常情況：l EXCEPTION;l2.將其定義好的異常情況，與標(biāo)準(zhǔn)的ORACLE錯(cuò)誤聯(lián)系起來，使用EXCEPTION_INIT語(yǔ)句：lPRAGMA EXCEPTION_INIT(, )；l3.在PL/SQL 塊的異常情況處理部分對(duì)異常情況做出相應(yīng)的處理。示例示例INSERT INTO dept VALUES(50, FINANCE, CHICAGO);DECLARE v_deptno dept.deptno%TYPE :=&deptno; deptno_remaining EXCEPTION; PRAGMA EXCEPTION_INIT(deptno_remaining, -

52、2292); /* -2292 是違反一致性約束的錯(cuò)誤代碼 */BEGIN DELETE FROM dept WHERE deptno=v_deptno;EXCEPTION WHEN deptno_remaining THEN DBMS_OUTPUT.PUT_LINE(違反數(shù)據(jù)完整性約束!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END; 存儲(chǔ)函數(shù)和過程存儲(chǔ)函數(shù)和過程lORACLE 提供可以把PL/SQL 程序存儲(chǔ)在數(shù)據(jù)庫(kù)中，并可以在任何地方來運(yùn)行它。這樣就叫存儲(chǔ)過程或函數(shù)。過程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是

53、被命名的PL/SQL塊，均存儲(chǔ)在數(shù)據(jù)庫(kù)中，并通過輸入、輸出參數(shù)或輸入/輸出參數(shù)與其調(diào)用者交換信息。過程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù)，而過程則不返回?cái)?shù)據(jù)。在本節(jié)中主要介紹：l創(chuàng)建存儲(chǔ)過程和函數(shù)；l建立和管理存儲(chǔ)過程和函數(shù)。創(chuàng)建函數(shù)創(chuàng)建函數(shù) l語(yǔ)法如下：CREATE or REPLACE FUNCTION function_name (argment in| in out TYPE, argment in | out | in out type RETURN RETURN_type IS | AS BEGIN FUNCTION_bodyEXCEPTION . . . . . .END

54、function_name;創(chuàng)建函數(shù)創(chuàng)建函數(shù) 示例示例CREATE OR REPLACE FUNCTION get_salary(Dept_no NUMBER,Emp_count OUT NUMBER)RETURN NUMBER ISV_sum NUMBER;BEGINSELECT SUM(sal), count(*) INTO V_sum, emp_countFROM emp WHERE deptno=dept_no;RETURN v_sum;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(你需要的數(shù)據(jù)不存在!); WHEN OT

55、HERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END get_salary;函數(shù)的調(diào)用l格式為： argument_value1,argument_value2 函數(shù)的調(diào)用 實(shí)例DECLAREV_num NUMBER;V_sum NUMBER;BEGINV_sum :=get_salary(30, v_num);DBMS_OUTPUT.PUT_LINE(30號(hào)部門工資總和：|v_sum|，人數(shù)：|v_num);END;運(yùn)行結(jié)果：30號(hào)部門工資總和：9400，人數(shù)：6創(chuàng)建過程l創(chuàng)建過程語(yǔ)法創(chuàng)建過程語(yǔ)法 ：CREATE OR REPLACE P

56、ROCEDURE Procedure_name (argment IN | OUT | IN OUT Type, argment IN | OUT | IN OUT Type AUTHID DEFINER | CURRENT_USER IS | AS BEGINEXCEPTIONEND;實(shí)例實(shí)例 1CREATE table logtable (userid VARCHAR2(10), logdate date);CREATE OR REPLACE PROCEDURE logexecution ISBEGININSERT INTO logtable (userid, logdate) VALUE

57、S (USER, SYSDATE);END;實(shí)例實(shí)例 2CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN emp.empno%TYPE) ASNo_result EXCEPTION;BEGIN DELETE FROM emp WHERE empno=v_empno; IF SQL%NOTFOUND THEN RAISE no_result; END IF; DBMS_OUTPUT.PUT_LINE(編碼為|v_empno|的員工已被除名!);EXCEPTION WHEN no_result THEN DBMS_OUTPUT.PUT_LINE(你需要的數(shù)

58、據(jù)不存在!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END DelEmp;實(shí)例實(shí)例 3CREATE OR REPLACE PROCEDURE InsertEmp( v_empno in emp.empno%TYPE, v_name in emp.ename%TYPE, v_deptno in emp.deptno%TYPE) AS empno_remaining EXCEPTION; PRAGMA EXCEPTION_INIT(empno_remaining, -1); /* -1 是違反唯一約束條件的錯(cuò)誤代碼 */

59、BEGIN INSERT INTO emp(empno, ename, hiredate, deptno) VALUES(v_empno, v_name, sysdate, v_deptno); DBMS_OUTPUT.PUT_LINE(插入數(shù)據(jù)記錄成功!);EXCEPTION WHEN empno_remaining THEN DBMS_OUTPUT.PUT_LINE(違反數(shù)據(jù)完整性約束!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END InsertEmp;調(diào)用存儲(chǔ)過程lORACLE 使用EXECUTE 語(yǔ)句來實(shí)現(xiàn)

60、對(duì)存儲(chǔ)過程的調(diào)用：EXECUTE Procedure_name( parameter1, parameter2);存儲(chǔ)過程和調(diào)用存儲(chǔ)過程和調(diào)用 實(shí)例實(shí)例1 CREATE OR REPLACE PROCEDURE QueryEmp(v_empno IN emp.empno%TYPE, v_ename OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE) ASBEGIN SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE(編碼為|v_empno|的員工已經(jīng)查到!);EXCEPTION WHEN NO_DA