福建省電力公司oracle培訓教材PLSQL語言篇ppt課件

1、ORACLE入門 PL/SQL言語篇技術(shù)支持部 湯慶鋒福州磬基電子本課程學習內(nèi)容本課程學習內(nèi)容PL/SQLPL/SQL簡介簡介PL/SQLPL/SQL數(shù)據(jù)類型數(shù)據(jù)類型(ORACLE(ORACLE的數(shù)據(jù)類型的數(shù)據(jù)類型) ) ORACLORACL內(nèi)置的內(nèi)置的SQLSQL函數(shù)函數(shù)PL/SQLPL/SQL中運用中運用SQLSQLPL/SQLPL/SQL中游標的運用中游標的運用動態(tài)動態(tài)PL/SQLPL/SQLPL/SQLPL/SQL的異常處置的異常處置PL/SQLPL/SQL簡介簡介 PL/SQL(Procedural Language/SQL) PL/SQL(Procedural Language/S

2、QL)即模塊化的程序設(shè)計言語，用于從各即模塊化的程序設(shè)計言語，用于從各種環(huán)境中訪問種環(huán)境中訪問ORACLEORACLE數(shù)據(jù)庫。它具備了許多數(shù)據(jù)庫。它具備了許多SQLSQL中所沒有的過程化屬性方面中所沒有的過程化屬性方面的特點。主要包括：的特點。主要包括：變量和類型變量和類型控制構(gòu)造條件語句、循環(huán)語句控制構(gòu)造條件語句、循環(huán)語句)過程、函數(shù)過程、函數(shù)游標游標異常處置異常處置PL/SQLPL/SQL程序的用途程序的用途無名塊無名塊就是沒有命名的就是沒有命名的PL/SQLPL/SQL塊，它可以嵌入某一個運用之中塊，它可以嵌入某一個運用之中. .存儲過程、函數(shù)存儲過程、函數(shù)也就是命名了的也就是命名了的P

3、L/SQLPL/SQL塊，它可以接納參數(shù)，并且可以反復塊，它可以接納參數(shù)，并且可以反復的被調(diào)用。的被調(diào)用。觸發(fā)器觸發(fā)器是與數(shù)據(jù)庫中的表相關(guān)的是與數(shù)據(jù)庫中的表相關(guān)的PL/SQLPL/SQL塊，可以自動的觸發(fā)。塊，可以自動的觸發(fā)。包包命名了的命名了的PL/SQLPL/SQL塊，由一組相關(guān)的過程、函數(shù)和標識符組成。塊，由一組相關(guān)的過程、函數(shù)和標識符組成。PL/SQLPL/SQL的程序構(gòu)造的程序構(gòu)造 PL/SQL PL/SQL的根本單位是的根本單位是“塊塊(Block)(Block)。一切的。一切的PL/SQLPL/SQL程序都是由一個或多程序都是由一個或多個個PL/SQLPL/SQL塊構(gòu)成的，這些塊

4、可以相互進展嵌套。通常一個塊完成程序的一個塊構(gòu)成的，這些塊可以相互進展嵌套。通常一個塊完成程序的一個單元的任務(wù)。一個根本的塊由三個部分組成：單元的任務(wù)。一個根本的塊由三個部分組成：定義部分定義部分定義變量、常量、游標、異常處置定義變量、常量、游標、異常處置可執(zhí)行部分可執(zhí)行部分 包括對數(shù)據(jù)庫進展操作的包括對數(shù)據(jù)庫進展操作的SQLSQL語句，以及語句，以及對塊中的語句進展組織、控制的對塊中的語句進展組織、控制的PL/SQLPL/SQL語句。語句。異常處置異常處置Exception) Exception) 部分部分可執(zhí)行部分中的語句，在執(zhí)行過程中可執(zhí)行部分中的語句，在執(zhí)行過程中出錯或出現(xiàn)非正常景象時

5、，所做的呼應(yīng)出錯或出現(xiàn)非正常景象時，所做的呼應(yīng)處置處置DECLAREBEGINEXCEPTIONENDPL/SQL塊構(gòu)造塊構(gòu)造PL/SQLPL/SQL數(shù)據(jù)類型數(shù)據(jù)類型字 段 類 型中 文 說 明限 制 條 件其 它 說 明C H A R固 定 長 度 字 符 串最 大 長 度2 0 0 0 b ytesVA R C H A R 2可 變 長 度 的 字 符 串最 大 長 度4 0 0 0 b ytes可 做 索 引 的 最大 長 度 7 4 9N C H A R根 據(jù) 字 符 集 而 定 的 固定 長 度 字 符 串最 大 長 度2 0 0 0 b ytesN VA R C H A R 2根

6、據(jù) 字 符 集 而 定 的 可變 長 度 字 符 串最 大 長 度4 0 0 0 b ytesD AT E日 期 （ 日 -月 -年 ）D D -M M -Y Y （ H H -M I-S S ）經(jīng)過嚴格測試 ， 無 千 蟲 問題L O N G超 長 字 符 串最 大 長 度2 G （ 2 3 1 -1 ）足 夠 存 儲 大 部頭 著 作R AW固 定 長 度 的 二 進 制 數(shù)據(jù)最 大 長 度2 0 0 0 b ytes可 存 放 多 媒 體圖 象 聲 音 等L O N G R A W可 變 長 度 的 二 進 制 數(shù)據(jù)最 大 長 度2 G同 上B L O B二 進 制 數(shù) 據(jù)最 大 長 度

7、4 GC L O B字 符 數(shù) 據(jù)最 大 長 度4 GN C L O B根 據(jù) 字 符 集 而 定 的 字符 數(shù) 據(jù)最 大 長 度4 GB F IL E存 放 在 數(shù) 據(jù) 庫 外 的 二進 制 數(shù) 據(jù)最 大 長 度4 GR O W ID數(shù) 據(jù) 表 中 記 錄 的 唯 一行 號1 0 b ytes*為0 或1N R O W ID二 進 制 數(shù) 據(jù) 表 中 記 錄的 唯 一 行 號最 大 長 度4 0 0 0b ytesN U M B E R (P,S )數(shù) 字 類 型P 為 整 數(shù) 位 ， S 為 小 數(shù) 位D E C IM A L (P,S )數(shù) 字 類 型P 為 整 數(shù) 位 ， S 為 小

8、數(shù) 位IN T E G E R整 數(shù) 類 型小 的 整 數(shù)F L O AT浮 點 數(shù) 類 型N U M B E R (3 8 )， 雙 精 度R E A L實 數(shù) 類 型N U M B E R (6 3 )， 精 度 更 高PL/SQLPL/SQL數(shù)據(jù)類型數(shù)據(jù)類型常用的數(shù)據(jù)類型常用的數(shù)據(jù)類型CHARCHAR： 存放固定長度的字符串存放固定長度的字符串VARCHAR2VARCHAR2：存放可變長度的字符串：存放可變長度的字符串NUMBERNUMBER： 存放存放0 0、正負數(shù)、浮點數(shù)、正負數(shù)、浮點數(shù)DATEDATE： 存放時間數(shù)據(jù)包括日期和時間存放時間數(shù)據(jù)包括日期和時間LONGLONG： 存放變

9、長字符串。普通用來存儲大文本存放變長字符串。普通用來存儲大文本RAW LONG RAW LONG 存放多媒體數(shù)據(jù)存放多媒體數(shù)據(jù), ,如聲音、圖片如聲音、圖片例如：創(chuàng)建一雇員表例如：創(chuàng)建一雇員表CREATE TABLE empCREATE TABLE emp( ( empno number(4), empno number(4), ename varchar2(10), ename varchar2(10), hiredate date, hiredate date, sal number(7,2), sal number(7,2), deptno number(2) deptno number

10、(2););ORACLEORACLE內(nèi)置的內(nèi)置的SQLSQL函數(shù)函數(shù) SQL SQL函數(shù)按照傳入?yún)?shù)的類型，可分為字符串函數(shù)、數(shù)值函數(shù)、日期函數(shù)、函數(shù)按照傳入?yún)?shù)的類型，可分為字符串函數(shù)、數(shù)值函數(shù)、日期函數(shù)、其他函數(shù)。以下分別列舉較常用的部分進展闡明。其他函數(shù)。以下分別列舉較常用的部分進展闡明。字符串函數(shù)：字符串函數(shù)：UPPER(s)UPPER(s)將字符串將字符串s s轉(zhuǎn)換成大寫的方式前往。轉(zhuǎn)換成大寫的方式前往。LOWER(s)LOWER(s)將字符串將字符串s s轉(zhuǎn)換成小寫的方式前往。轉(zhuǎn)換成小寫的方式前往。SUBSTR(s,a ,b)SUBSTR(s,a ,b)前往從字符位置前往從字符位置

11、a a開場有開場有b b個字符長的個字符長的s s的一部分。的一部分。假設(shè)假設(shè)a a為正數(shù)為正數(shù): :從左邊向右邊計算從左邊向右邊計算假設(shè)假設(shè)a a為負數(shù)為負數(shù): :從右邊向左邊計算從右邊向左邊計算實例：實例：Select substr(Select substr(abcdefg123abcdefg123,4) from dual; ,4) from dual; 結(jié)果前往：結(jié)果前往：defg123defg123Select substr(Select substr(abcdefg123abcdefg123,4,2) from dual; ,4,2) from dual; 結(jié)果前往：結(jié)果前往：d

12、edeSelect substr(Select substr(abcdefg123abcdefg123,-4,2) from dual; ,-4,2) from dual; 結(jié)果前往：結(jié)果前往：g1g1RTRIM(s1,s2)RTRIM(s1,s2)前往刪除從最右邊算起出如今前往刪除從最右邊算起出如今s2s2中的字符的中的字符的s1s1。s2s2缺省為空格缺省為空格實例：實例：Select rtrim(Select rtrim(aabbccddaabbccdd, ,cdcd) from dual; ) from dual; 結(jié)果前往：結(jié)果前往：aabbaabb Select rtrim( Se

13、lect rtrim(aabbccddaabbccdd, ,dcdc) from dual; ) from dual; 結(jié)果前往：結(jié)果前往：aabbaabbORACLORACL內(nèi)置的內(nèi)置的SQLSQL函數(shù)函數(shù) Concat(s1,s2)Concat(s1,s2) 前往串接上前往串接上s2s2之后的之后的s1.s1.該函數(shù)與該函數(shù)與|運算符作用一樣。運算符作用一樣。 實例：實例：select concat(abc,def) from dual; select concat(abc,def) from dual; 前往前往結(jié)果：結(jié)果：abcdefabcdef select abc|def from

14、 dual; select abc|def from dual; 前往結(jié)果：前往結(jié)果：abcdefabcdef Length(s)Length(s) 以字節(jié)為單位前往字符串以字節(jié)為單位前往字符串s s的長度。的長度。ORACLORACL內(nèi)置的內(nèi)置的SQLSQL函數(shù)函數(shù)數(shù)值函數(shù)數(shù)值函數(shù)Ceil(n)Ceil(n)前往大于或等于前往大于或等于n n的整數(shù)的整數(shù)Select ceil(18.6),ceil(-18.6) from dual;Select ceil(18.6),ceil(-18.6) from dual;Floor(n)Floor(n)前往小于或等于前往小于或等于n n的整數(shù)的整數(shù)Se

15、lect floor(18.6),floor(-18.6) from dual;Select floor(18.6),floor(-18.6) from dual;Mod(x,y)Mod(x,y)前往前往x x除以除以y y得余數(shù)，假設(shè)得余數(shù)，假設(shè)y y為為0 0，那么前往，那么前往x x。Select mod(23,5),mod(4,1.3) from dual; Select mod(23,5),mod(4,1.3) from dual; 前往結(jié)果：前往結(jié)果：1.1 , 1.1 , 0.10.1Round(x,y)Round(x,y)前往舍入到小數(shù)點右邊前往舍入到小數(shù)點右邊y y為的為的x

16、 x值。值。Select round(1.56),round(1.56,1),round(123.4,-1)Select round(1.56),round(1.56,1),round(123.4,-1)from dual; from dual; 前往結(jié)果：前往結(jié)果：1.1 , 0.1 ,1201.1 , 0.1 ,120ORACLORACL內(nèi)置的內(nèi)置的SQLSQL函數(shù)函數(shù) 日期函數(shù)日期函數(shù) SysdateSysdate 前往當前的日期和時間前往當前的日期和時間 Add_months(D,x)Add_months(D,x) Last_day(D)Last_day(D) 前往日期前往日期D D的

17、月份的最后一天的日期的月份的最后一天的日期 Months_Between(D1,D2)Months_Between(D1,D2) 前往在前往在D1D1和和D2D2之間月的數(shù)目。之間月的數(shù)目。 Trunc(D,format)Trunc(D,format) 前往結(jié)尾由前往結(jié)尾由formatformat指定的單位的日期。指定的單位的日期。 例如：例如： Select trunc(sysdate,Select trunc(sysdate,yearyear) from dual; ) from dual; 前往今年的第一前往今年的第一天天 Select trunc(sysdate,Select trun

18、c(sysdate,mmmm) from dual; ) from dual; 前往本月的第一天前往本月的第一天 Select trunc(sysdate,Select trunc(sysdate,D D) from dual; ) from dual; 前往本周的第一天前往本周的第一天ORACLORACL內(nèi)置的內(nèi)置的SQLSQL函數(shù)函數(shù) 轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) To_char(DTo_char(D，format)format) 將日期轉(zhuǎn)換為指定格式的字符串。將日期轉(zhuǎn)換為指定格式的字符串。 例如：例如： Select to_char(sysdate,Select to_char(sysdate,yy

19、yy/mm/dd hh:mi:ssyyyy/mm/dd hh:mi:ss) from ) from dual; dual; To_Date(string,format)To_Date(string,format) 將字符串轉(zhuǎn)換成日期格式將字符串轉(zhuǎn)換成日期格式 例如：例如： Select to_date(Select to_date(2000/10/012000/10/01, ,yyyy/mm/ddyyyy/mm/dd) from dual; ) from dual; Last_day(D)Last_day(D) 前往日期前往日期D D的月份的最后一天的日期的月份的最后一天的日期 To_Numb

20、er(string,format)To_Number(string,format)ORACLORACL內(nèi)置的內(nèi)置的SQLSQL函數(shù)函數(shù)其它函數(shù)其它函數(shù)Nvl(a,b)Nvl(a,b)空值交換函數(shù)，假設(shè)空值交換函數(shù)，假設(shè)a a為空，那么交換成為空，那么交換成b b。例如：例如：Select ename,sal,sal+nvl(comm,0) from dual; Select ename,sal,sal+nvl(comm,0) from dual; DECODE(DECODE(條件條件, ,值值1,1,翻譯值翻譯值1,1,值值2,2,翻譯值翻譯值2,.2,.值值n,n,翻譯值翻譯值n,n,缺省值

21、缺省值) )該函數(shù)的含義如下：該函數(shù)的含義如下： IF IF 條件條件= =值值1 THEN 1 THEN RETURN(RETURN(翻譯值翻譯值1) 1) ELSIF ELSIF 條件條件= =值值2 THEN 2 THEN RETURN(RETURN(翻譯值翻譯值2) 2) . . ELSIF ELSIF 條件條件= =值值n THEN n THEN RETURN(RETURN(翻譯值翻譯值n) n) ELSE ELSE RETURN(RETURN(缺省值缺省值) ) END IF END IF PL/SQLPL/SQL的注釋的注釋 注釋加強了可閱讀性，使得程序更易于了解。注釋加強了可閱

22、讀性，使得程序更易于了解。單行注釋單行注釋- comment- comment多行注釋多行注釋/ /* * comment comment * */ /留意：此注釋不能作用在留意：此注釋不能作用在SQLSQL言語上。言語上。例如：例如：DECLAREDECLARE v_deptno number(2); - v_deptno number(2); -與雇員表中部門代碼字段交互的與雇員表中部門代碼字段交互的變量變量 v_sal number(7,2); - v_sal number(7,2); -與雇員表中工資字段交互的變量與雇員表中工資字段交互的變量BEGINBEGIN / /* *this

23、is this is a test! a test! * */ / select deptno,sal into v_deptno,v_sal from emp where select deptno,sal into v_deptno,v_sal from emp where empno=7788;empno=7788;END;END;PL/SQLPL/SQL塊的定義部分塊的定義部分 在在PL/SQLPL/SQL塊中援用的一切標識符，都必需在定義部分中明確塊中援用的一切標識符，都必需在定義部分中明確定義。定義。定義常量定義常量 格式：標識符格式：標識符 CONSTANT CONSTANT數(shù)據(jù)

24、類型：數(shù)據(jù)類型：= = 表達式表達式 例：定義一常量例：定義一常量PIPI，值為，值為3.143.14。PI CONSTANT NUMBER(3,2) := 3.14;PI CONSTANT NUMBER(3,2) := 3.14;定義標量型變量定義標量型變量 標量型數(shù)據(jù)類型，是指數(shù)據(jù)類型為個體型。標量型數(shù)據(jù)類型，是指數(shù)據(jù)類型為個體型。 格式：格式： NOT NULL :=|DEFAULT NOT NULL :=|DEFAULT 例：定義一寬度為例：定義一寬度為1010個字符的字符串變量個字符的字符串變量X X。 DECLARE DECLARE X CHAR(5) X CHAR(5)； y C

25、HAR(5):= y CHAR(5):=ORACLEORACLE; ; Z CHAR(5) default Z CHAR(5) default oracleoracle; ;代表數(shù)據(jù)庫列的變量代表數(shù)據(jù)庫列的變量先看一個例如：創(chuàng)建一先看一個例如：創(chuàng)建一PL/SQLPL/SQL塊，根據(jù)部門號，前往部門稱號塊，根據(jù)部門號，前往部門稱號. .DECLAREDECLARE v_dname dept.dname%type; v_dname dept.dname%type;BEGINBEGIN SELECT dname INTO v_dname FROM DEPT WHERE deptno=10; SELE

26、CT dname INTO v_dname FROM DEPT WHERE deptno=10; DBMS_OUTPUT.PUT_LINE(v_dname); DBMS_OUTPUT.PUT_LINE(v_dname);EXCEPTION WHEN NO_DATA_FOUND THENEXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE(sorry:no data found!sorry:no data found!););END;END;問題：問題：所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型不知道？所援用的

27、數(shù)據(jù)庫表中的數(shù)據(jù)類型不知道？所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型未來改動改動怎樣辦？所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型未來改動改動怎樣辦？PL/SQLPL/SQL塊的定義部分塊的定義部分另一種定義標量型變量的方法另一種定義標量型變量的方法%TYPE%TYPE 定義一個變量定義一個變量, ,其數(shù)據(jù)類型與知變量的數(shù)據(jù)類型一樣，或者與數(shù)據(jù)庫其數(shù)據(jù)類型與知變量的數(shù)據(jù)類型一樣，或者與數(shù)據(jù)庫表的某個列的數(shù)據(jù)類型一樣。表的某個列的數(shù)據(jù)類型一樣。%TYPE%TYPE的優(yōu)點在于：的優(yōu)點在于：所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以不用知道。所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以不用知道。所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以實時改動。所援用的數(shù)

28、據(jù)庫表中的數(shù)據(jù)類型可以實時改動。格式：格式： NOT NULL :=|DEFAULT NOT NULL :=|DEFAULT .%TYPE%TYPE例：定義一個變量，其數(shù)據(jù)類型基于另一個變量例：定義一個變量，其數(shù)據(jù)類型基于另一個變量DECLAREDECLARE V_1 NUMBER(7,2); V_1 NUMBER(7,2); V_11 V1%TYPE := 12345.6; V_11 V1%TYPE := 12345.6;例：定義一個變量，其數(shù)據(jù)類型基于數(shù)據(jù)庫中表的列例：定義一個變量，其數(shù)據(jù)類型基于數(shù)據(jù)庫中表的列DECLAREDECLARE v_ename EMP.ENAME%TYPE; v

29、_ename EMP.ENAME%TYPE; V_SAL EMP.SAL%TYPE; V_SAL EMP.SAL%TYPE;PL/SQLPL/SQL塊的定義部分塊的定義部分另一種定義組合型變量的方法另一種定義組合型變量的方法%ROWTYPE%ROWTYPE 定義一個變量定義一個變量, ,其數(shù)據(jù)類型與數(shù)據(jù)庫表的數(shù)據(jù)構(gòu)造一樣。其數(shù)據(jù)類型與數(shù)據(jù)庫表的數(shù)據(jù)構(gòu)造一樣。%ROWTYPE%ROWTYPE的優(yōu)點在于：的優(yōu)點在于：所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以不用知道。所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以不用知道。所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以實時改動。所援用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以實時改動。簡易格式：簡易格

30、式： %ROWTYPE%ROWTYPE例：例：DECLAREDECLARE v_emp emp%rowtype; v_emp emp%rowtype;BEGINBEGIN SELECT SELECT * * INTO v_emp FROM emp WHERE empno=7788; INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE(v_emp.empno); DBMS_OUTPUT.PUT_LINE(v_emp.empno); DBMS_OUTPUT.PUT_LINE(v_emp.ename); DBMS_OUTPUT.PUT

31、_LINE(v_emp.ename); DBMS_OUTPUT.PUT_LINE(v_emp.job); DBMS_OUTPUT.PUT_LINE(v_emp.job); DBMS_OUTPUT.PUT_LINE(v_emp.sal); DBMS_OUTPUT.PUT_LINE(v_emp.sal);END;END;變量的援用和賦值變量的援用和賦值 標量變量賦值標量變量賦值 格式：格式： :=:= ； 例：例：V_NAME := V_NAME := JOANJOAN; ; v_demptno:=10; v_demptno:=10; 組合型變量賦值組合型變量賦值 格式：變量格式：變量. .域名主

32、鍵值：域名主鍵值：= =表達式；表達式； 例：例：v_emp.sal:=8888;v_emp.sal:=8888; v_empm:=8888; v_empm:=8888;PL/SQLPL/SQL中運用中運用SQLSQL 在在PL/SQLPL/SQL塊中，經(jīng)過塊中，經(jīng)過SQLSQL語句對語句對ORACLEORACLE數(shù)據(jù)庫中的數(shù)據(jù)進展數(shù)據(jù)庫中的數(shù)據(jù)進展存取。在存取。在PL/SQLPL/SQL中：中：可以運用的可以運用的SQLSQL語句有：語句有：SELECTSELECT、INSERTINSERT、DELETEDELETE、UPDATEUPDATE、COMMITCOMMIT、ROLLBACKROL

33、LBACK不可以直接運用的不可以直接運用的SQLSQL語句有：語句有：數(shù)據(jù)定義語句數(shù)據(jù)定義語句DDLDDL，如：，如：CREATE TALBECREATE TALBE，DROP TABLEDROP TABLE數(shù)據(jù)控制語句數(shù)據(jù)控制語句DCLDCL，如：，如：GRANTGRANT、REVOKEREVOKE備注：在備注：在PL/SQL2.1PL/SQL2.1以上版本，允許經(jīng)過以上版本，允許經(jīng)過DBMS_SQLDBMS_SQL包來創(chuàng)建動態(tài)包來創(chuàng)建動態(tài)SQLSQL語句。語句。PL/SQLPL/SQL中運用中運用SQLSQLSELECTSELECT語句語句SELECTSELECT語句：將數(shù)據(jù)從數(shù)據(jù)庫中檢索

34、出來并放入語句：將數(shù)據(jù)從數(shù)據(jù)庫中檢索出來并放入PL/SQLPL/SQL變量中。變量中。格式：格式：SELECT SELECT INTO INTO FROM FROM 例：查詢某個雇員的姓名及工資。例：查詢某個雇員的姓名及工資。DECLAREDECLARE v_empno emp.empno%type:=7788; v_empno emp.empno%type:=7788; v_ename emp.ename%type; v_ename emp.ename%type; v_sal emp.sal%type; v_sal emp.sal%type;BEGINBEGIN select ename,s

35、al into v_ename,v_sal from emp where empno=v_empno; select ename,sal into v_ename,v_sal from emp where empno=v_empno; DBMS_OUTPUT.PUT_LINE(v_empno|v_ename|v_sal); DBMS_OUTPUT.PUT_LINE(v_empno|v_ename|v_sal);EXCEPTION WHEN NO_DATA_FOUND THENEXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(sorry

36、:no data found!); DBMS_OUTPUT.PUT_LINE(sorry:no data found!);END;END;/ /PL/SQLPL/SQL中的中的SELECTSELECT語句中必需包含語句中必需包含INTOINTO子句，而且對應(yīng)的個數(shù)要一樣，位置要一一子句，而且對應(yīng)的個數(shù)要一樣，位置要一一對應(yīng)。對應(yīng)。查詢結(jié)果只前往一條記錄，否那么會產(chǎn)生異常情況。查詢結(jié)果只前往一條記錄，否那么會產(chǎn)生異常情況。1 1查詢結(jié)果多于一條記錄查詢結(jié)果多于一條記錄 異常變量：異常變量：TOO_MANY_ROWSTOO_MANY_ROWS2 2查詢結(jié)果沒有前往記錄查詢結(jié)果沒有前往記錄 異常變量

37、：異常變量：NO_DATA_FOUNDNO_DATA_FOUNDPL/SQLPL/SQL中運用中運用SQLSQL 在在PL/SQLPL/SQL中，對數(shù)據(jù)庫進展插入中，對數(shù)據(jù)庫進展插入(INSERT)(INSERT)、刪除、刪除(DELETE)(DELETE)、修正、修正UPDATEUPDATE語句，其語法方式與語句，其語法方式與SQLSQL中的是完全一樣的。中的是完全一樣的。例：在例：在EMPEMP表中刪去某個雇員。表中刪去某個雇員。BEGINBEGIN DELETE emp WHERE empno=7788; DELETE emp WHERE empno=7788; COMMIT; COMM

38、IT;END;END;PL/SQLPL/SQL的執(zhí)行部分的執(zhí)行部分流程控制語句流程控制語句流程控制語句主要有三種：流程控制語句主要有三種：條件控制條件控制循環(huán)控制循環(huán)控制跳轉(zhuǎn)控制跳轉(zhuǎn)控制流程控制語句流程控制語句條件控制條件控制語法格式：語法格式：IF 條件條件THEN 語句；語句；ELSIF 條件條件THEN 語句；語句；ELSE 語句；語句；END IF；例：根據(jù)職務(wù)浮開工資例：根據(jù)職務(wù)浮開工資IF v_job=MANAGER THEN v_sal := v_sal*1.3;ELSIF v_job=SALESMAN THEN v_sal := v_sal*1.2;ELSE v_sal :=

39、v_sal*1.1;END IF；update emp set sal=v_salwhere empno=1234;流程控制語句流程控制語句循環(huán)控制循環(huán)控制在在PL/SQLPL/SQL中循環(huán)控制的有以下四種：中循環(huán)控制的有以下四種：簡單循環(huán)簡單循環(huán)FORFOR循環(huán)循環(huán)WHEREWHERE循環(huán)循環(huán)用于游標的用于游標的FORFOR循環(huán)循環(huán)循環(huán)控制循環(huán)控制簡單循環(huán)簡單循環(huán)語法格式：LOOP語句1；語句2； EXIT WHEN 條件；END LOOP；例：把數(shù)值1到50順序插入表中。V_counter:=1;LOOPINSERT INTO temp_tableVALUES (v_counter);EX

40、IT WHEN v_counter50；V_count :=v_count+1;END LOOP；循環(huán)控制循環(huán)控制FORFOR循環(huán)循環(huán)語法格式：FOR 循環(huán)變量IN REVERSE 下界.上界LOOP語句1；語句2； END LOOP；REVERSE:使計數(shù)器由上界到下界遞減計數(shù)例：把數(shù)值1到50順序插入表中。FOR v_counter IN 1.50 LOOPINSERT INTO temp_tableVALUES (v_counter);END LOOP；循環(huán)控制循環(huán)控制WHILEWHILE循環(huán)循環(huán)語法格式：WHILE 條件LOOP語句1；語句2； END LOOP；例：把數(shù)值1到50順序

41、插入表中。V_counter:=1;WHILE v_counter=50 LOOPINSERT INTO temp_tableVALUES (v_counter);V_count:=v_count+1;END LOOP；跳轉(zhuǎn)控制語句跳轉(zhuǎn)控制語句語法格式：語法格式：GOTO GOTO ;在進展在進展PL/SQLPL/SQL編程時編程時, ,盡量防止或不用盡量防止或不用GOTOGOTO語句語句, ,由于這種無由于這種無條件的跳轉(zhuǎn)語句條件的跳轉(zhuǎn)語句 突破了程序的邏輯性突破了程序的邏輯性, ,有悖于自頂向下的編程有悖于自頂向下的編程風格風格. .PL/SQLPL/SQL游標的運用游標的運用游標游標(C

42、URSOR)(CURSOR)的功能的功能, ,是是ORALCEORALCE系統(tǒng)為了將一切查詢結(jié)果前往給用戶程序而系統(tǒng)為了將一切查詢結(jié)果前往給用戶程序而提供的。一個游標提供的。一個游標, ,實踐上是在內(nèi)存中開辟一個任務(wù)區(qū)實踐上是在內(nèi)存中開辟一個任務(wù)區(qū), ,它對應(yīng)一條它對應(yīng)一條SELECTSELECT語語句。當翻開游標時，就是執(zhí)行游標所對應(yīng)的句。當翻開游標時，就是執(zhí)行游標所對應(yīng)的SELECTSELECT語句，并將其查詢結(jié)果放語句，并將其查詢結(jié)果放入任務(wù)區(qū)，并且指針指向任務(wù)區(qū)的首部。經(jīng)過光標上的操作可以把這些記錄入任務(wù)區(qū)，并且指針指向任務(wù)區(qū)的首部。經(jīng)過光標上的操作可以把這些記錄檢索到客戶端的運用程序

43、。檢索到客戶端的運用程序。CURSOR內(nèi)存區(qū)POINTERSELECTINTO:SELECTINTO:只能查詢數(shù)據(jù)庫的單條記錄，并把記錄的數(shù)據(jù)賦給變量。只能查詢數(shù)據(jù)庫的單條記錄，并把記錄的數(shù)據(jù)賦給變量。游標游標定義和支配游標定義和支配游標步驟：步驟：定義游標定義游標翻開游標翻開游標從游標中取值從游標中取值封鎖游標封鎖游標定義游標定義游標 定義游標，就是定義一個游標名，以及與其相對應(yīng)的定義游標，就是定義一個游標名，以及與其相對應(yīng)的SELECTSELECT語句。語句。語法格式：語法格式：CURSOR CURSOR 游標名游標名I S I S SELECTSELECT子句；子句；例如：定義一個包含一

44、切雇員記錄的游標。例如：定義一個包含一切雇員記錄的游標。 cursor cur_emp iscursor cur_emp is select select * * from emp; from emp;翻開游標翻開游標 翻開游標，就是執(zhí)行游標所對應(yīng)的翻開游標，就是執(zhí)行游標所對應(yīng)的SELECTSELECT語句，將其查詢語句，將其查詢結(jié)結(jié)果放入任務(wù)區(qū)，并且指針指向任務(wù)區(qū)的首部。果放入任務(wù)區(qū)，并且指針指向任務(wù)區(qū)的首部。語法格式：語法格式：OPEN OPEN 游標名；游標名； 從游標中取值從游標中取值 取值任務(wù)是將游標任務(wù)區(qū)中的數(shù)據(jù)取出一行，放入指定的取值任務(wù)是將游標任務(wù)區(qū)中的數(shù)據(jù)取出一行，放入指定的

45、輸輸 出變量中。出變量中。 語法格式：語法格式： FETCH FETCH 游標名游標名INTO INTO 變量變量1 1, ,變量變量2 2 ； 例如：例如：fetch cur_emp into fetch cur_emp into v_empno,v_ename,v_sal,v_comm,v_deptnov_empno,v_ename,v_sal,v_comm,v_deptno 封鎖游標封鎖游標 釋放與該游標相關(guān)的資源。釋放與該游標相關(guān)的資源。 語法格式：語法格式： CLOSE CLOSE ; 例如：例如：close cur_emp;close cur_emp;游標的屬性游標的屬性 從游標任

46、務(wù)區(qū)中逐一地取數(shù)據(jù)，可以在循環(huán)中完成。但循環(huán)從游標任務(wù)區(qū)中逐一地取數(shù)據(jù)，可以在循環(huán)中完成。但循環(huán)的開場以及終了，需以游標屬性為根據(jù)。的開場以及終了，需以游標屬性為根據(jù)。游標屬性有：游標屬性有： %ISOPEN %ISOPEN： 判別游標能否被翻開判別游標能否被翻開 %NOTFOUND %NOTFOUND：判別何時中斷循環(huán)：判別何時中斷循環(huán) %FOUND %FOUND： 與與%NOTFOUND%NOTFOUND相反相反 %ROWCOUNT %ROWCOUNT：實踐從游標任務(wù)區(qū)抽取的記錄數(shù)：實踐從游標任務(wù)區(qū)抽取的記錄數(shù)例如：例如：Open cur_emp;Open cur_emp;LoopLoop

47、 fetch cur_emp into v_empno,v_ename,v_sal,v_deptno; fetch cur_emp into v_empno,v_ename,v_sal,v_deptno; exit when cur_emp%NOTFOUND; exit when cur_emp%NOTFOUND;End loop;End loop;游標游標用于游標的用于游標的FORFOR循環(huán)循環(huán) 游標的游標的FORFOR循環(huán)，是一種簡單的游標操作方法，系統(tǒng)隱式地循環(huán)，是一種簡單的游標操作方法，系統(tǒng)隱式地進展游標的翻開、提取數(shù)據(jù)、循環(huán)、封鎖。進展游標的翻開、提取數(shù)據(jù)、循環(huán)、封鎖。格式：格式：

48、FOR FOR 記錄變量記錄變量IN IN 游標名游標名LOOPLOOP 語句語句; END LOOP ; END LOOP ; :由系統(tǒng)隱含定義的記錄名由系統(tǒng)隱含定義的記錄名例如：例如：DeclareDeclare cursor cur_emp is select cursor cur_emp is select * * from emp; from emp;BeginBegin for v_emp in cur_emp loop for v_emp in cur_emp loop DBMS_OUTPUT.PUT_LINE(v_emp.ename); DBMS_OUTPUT.PUT_LINE

49、(v_emp.ename); DBMS_OUTPUT.PUT_LINE(v_emp.sal); DBMS_OUTPUT.PUT_LINE(v_emp.sal); end loop; end loop;End; End; 一個完好的例如一個完好的例如例：建立一存儲過程，根據(jù)職務(wù)修正工資例：建立一存儲過程，根據(jù)職務(wù)修正工資CREATE OR REPLACE PROCEDURE p_update_sal AS CURSOR cur_emp IS SELECT * FROM emp; v_emp cur_emp%ROWTYPE;BEGIN OPEN cur_emp; LOOP FETCH cur_em

50、p INTO v_emp; EXIT WHEN cur_emp%NOTFOUND; IF v_emp.job=MANAGER THEN v_emp.sal:=v_emp.sal*1.3;ELSIF v_emp.job=SALESMAN THEN v_emp.sal:=v_emp.sal*1.2; ELSE v_emp.sal:=v_emp.sal*1.1; END IF； UPDATE emp SET sal=v_emp.sal WHERE empno=v_emp.empno; END LOOP; CLOSE cur_emp; COMMIT;END;一個完好的例如一個完好的例如( (用用FOR

51、FOR循環(huán)循環(huán)) )CREATE PROCEDURE p_update_sal AS CURSOR cur_emp IS SELECT * FROM emp;BEGIN FOR v_emp IN cur_emp LOOP IF v_emp.job=MANAGER THEN v_emp.sal:=v_emp.sal*1.3; ELSIF v_emp.job=SALESMAN THEN v_emp.sal:=v_emp.sal*1.2; ELSE v_sal := v_sal*1.1; END IF； UPDATE emp SET sal=v_emp.sal WHERE empno=v_emp.e

52、mpno; END LOOP; COMMIT;END;例如例如DECLARE DECLARE CURSOR c1 is CURSOR c1 is SELECT ename, empno, sal FROM emp SELECT ename, empno, sal FROM emp ORDER BY sal DESC; - start with highest paid employee ORDER BY sal DESC; - start with highest paid employee my_ename CHAR(10); my_ename CHAR(10); my_empno NUMB

53、ER(4); my_empno NUMBER(4); my_sal NUMBER(7,2); my_sal NUMBER(7,2); BEGIN BEGIN OPEN c1; OPEN c1; FOR i IN 1.5 LOOP FOR i IN 1.5 LOOP FETCH c1 INTO my_ename, my_empno, my_sal; FETCH c1 INTO my_ename, my_empno, my_sal; EXIT WHEN c1%NOTFOUND; EXIT WHEN c1%NOTFOUND; INSERT INTO temp VALUES (my_sal, my_e

54、mpno, my_ename); INSERT INTO temp VALUES (my_sal, my_empno, my_ename); COMMIT; COMMIT; END LOOP; END LOOP; CLOSE c1; CLOSE c1; END; END; 異常處置異常處置 PL/SQL PL/SQL中，將程序執(zhí)行過程中的一個警告或錯誤稱為一個異中，將程序執(zhí)行過程中的一個警告或錯誤稱為一個異常常(EXCEPTION)(EXCEPTION)。異常情況的種類有三種：。異常情況的種類有三種：預定義的預定義的ORACLEORACLE錯誤錯誤ORACLEORACLE預定一的異常情況大約有

55、預定一的異常情況大約有2424個。對這種異常情況的處置，個。對這種異常情況的處置，無無須在程序中定義，由須在程序中定義，由ORACLEORACLE自動將其引發(fā)。自動將其引發(fā)。非預定義的非預定義的ORACLEORACLE錯誤錯誤即其他規(guī)范的即其他規(guī)范的ORACLEORACLE錯誤。對這種異常情況的處置，需在定義錯誤。對這種異常情況的處置，需在定義部分定義部分定義，然后由，然后由ORACLEORACLE自動將其引發(fā)。自動將其引發(fā)。用戶定義的錯誤用戶定義的錯誤程序執(zhí)行過程中，出現(xiàn)編程人員以為非正常的。對這種異常情程序執(zhí)行過程中，出現(xiàn)編程人員以為非正常的。對這種異常情況的處置況的處置，需在定義部分定義

56、，然后顯式由地將其引發(fā)。，需在定義部分定義，然后顯式由地將其引發(fā)。異常處置異常處置語法格式：語法格式：EXCEPTIONEXCEPTION WHEN WHEN THEN1 THEN 語句；語句； WHEN WHEN 異常情況異常情況2 2 THEN THEN 語句；語句； WHEN OTHERS THEN WHEN OTHERS THEN 語句；語句； OTHERSOTHERS：指沒有列在異常處置部分中的：指沒有列在異常處置部分中的其他異常情況。其他異常情況。DECLAREBEGINEXCEPTIONENDPL/SQL塊執(zhí)行過程塊執(zhí)行過程異常發(fā)生異常處置異常處置異常處置預定義的預定義的ORAC

57、LEORACLE錯誤錯誤預定義的異常名稱預定義的異常名稱錯誤號錯誤號說明說明CURSOR_ALREADY_OPENCURSOR_ALREADY_OPENORA-6511ORA-6511試圖打開一個已打開的光標試圖打開一個已打開的光標LOGIN_DENIED LOGIN_DENIED ORA-1017ORA-1017無效的用戶名或者口令無效的用戶名或者口令NO_DATA_FOUNDNO_DATA_FOUNDORA-1403ORA-1403查詢未找到數(shù)據(jù)查詢未找到數(shù)據(jù)NOT_LOGGED_ON NOT_LOGGED_ON ORA-1012ORA-1012還未連接就試圖數(shù)據(jù)庫操作還未連接就試圖數(shù)據(jù)庫

58、操作DUP_VAL_ON_INDEX DUP_VAL_ON_INDEX ORA-0001ORA-0001試圖破壞一個唯一性限制試圖破壞一個唯一性限制TIMEOUT_ON_RESOURCE TIMEOUT_ON_RESOURCE ORA-0051ORA-0051發(fā)生超時發(fā)生超時TRANSACTION_BACKED_OUT TRANSACTION_BACKED_OUT ORA-006ORA-006由于死鎖提交被退回由于死鎖提交被退回TOO_MANY_ROWSTOO_MANY_ROWSORA-1422ORA-1422SELECT INTDSELECT INTD命令返回的多行命令返回的多行異常處置異常處置預定義異常例如：預定義異常例如：BEGINBEGIN insert into emp (empno,ename) values (7788,testuser); insert into emp (empno,ename) va