PLSQL程序設(shè)計(jì)

1、1 pl/sql 程序設(shè)計(jì)（上冊(cè)）寫在前面之所以不怕麻煩把有關(guān)pl/sql 的知識(shí)寫下來(lái)，一方面，是為了加深理解，便于以后隨時(shí)翻閱，另一方面，是因?yàn)樽约嚎磿偸遣荒軓念^看到尾，仔仔細(xì)細(xì)，喜歡跳躍式閱讀，導(dǎo)致學(xué)習(xí)過(guò)程中，容易有遺漏。所以，將學(xué)習(xí)到的知識(shí)總結(jié)起來(lái)，方便監(jiān)督自己，方便大家查閱。本文所記錄的內(nèi)容當(dāng)然不是本人憑經(jīng)驗(yàn)而寫，而是學(xué)習(xí)的筆記，參考的資料以 oracle9i plsql fundamentals 和oracle9i develop plsql program units 為核心，其次，還包括精通 oracle10g plsql編程和 oracle plsql best pract

2、ices等。由于是學(xué)習(xí)筆記么，說(shuō)是教程，其實(shí)是在吸引大家的注意，呵呵，主要是想在方便大家查閱和入門者入門外，希望大家多多指教， 多多提出意見(jiàn)，共同把這塊知識(shí)學(xué)熟、學(xué)通、學(xué)透。quasar wei s email: quasar wei s qq: 734089783quasar wei s real name: weizhaozhe of neu 2 目錄pl/sql 程序設(shè)計(jì)（上冊(cè)）. 1第一章認(rèn)識(shí) pl/sql . 3第二章sql回顧 . 5第三章 pl/sql的基本結(jié)構(gòu) . 7第四章 pl/sql的數(shù)據(jù)類型及使用 . 10 第五章流程控制 . 29 第六章游標(biāo) . 35 第七章異常處理

3、. 46 第八章下冊(cè)預(yù)知 . 49 3 第一章 認(rèn)識(shí) pl/sql 這一章主要記錄了兩個(gè)問(wèn)題：1 什么是 pl/sql ？ 2 pl/sql 有什么優(yōu)勢(shì)？下面從這兩個(gè)問(wèn)題展開論述。什么是 pl/sql? pl/sql 即 procedural language/standard query language, pl/sql是具備程序設(shè)計(jì)語(yǔ)言特性 （如：定義變量、流程控制、面向?qū)ο蟮龋?的、關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)查詢語(yǔ)言sql的過(guò)程性拓展（ procedural extension ） 。sql 是非過(guò)程語(yǔ)言，非過(guò)程，讓我理解，就是不用你了解語(yǔ)句的處理過(guò)程，而直接達(dá)到開發(fā)者的目的。如：select na

4、me from student這條語(yǔ)句，就是向數(shù)據(jù)庫(kù)說(shuō)：?我要學(xué)生的姓名?，然后數(shù)據(jù)庫(kù)就會(huì)返回相應(yīng)的字段值而我們并不知道它是如何處理的也基本無(wú)法控制。而pl/sql 語(yǔ)言，則具備程序設(shè)計(jì)語(yǔ)言甚至高級(jí)程序設(shè)計(jì)語(yǔ)言的特點(diǎn)，更好地滿足我們對(duì)數(shù)據(jù)的操作。pl/sql 有什么優(yōu)勢(shì)？pl/sql 的優(yōu)勢(shì)可以概括為以下五點(diǎn)（待補(bǔ)充） ：1 具備程序設(shè)計(jì)語(yǔ)言的特性（模塊化、信息隱藏、面向?qū)ο蟮龋? 異常處理， pl/sql 具備異常處理的機(jī)制；3 可移植，想想 java吧，看看兩者有什么共同之處？pl/sql 的jvm就是 oracle server ，呵呵4 改善性能，一方面，通過(guò)pl/sql 語(yǔ)句塊（b

5、lock）可以一次性向 oracle server 發(fā)送多個(gè) sql 語(yǔ)句，減少網(wǎng)絡(luò)的傳輸； 另一方面，有4 些 pl/sql 可以在客戶端執(zhí)行（前提是某些客戶端含有pl/sql 引擎） ，可以有效的減少與oracle server 的交互；再有， pl/sql 通常會(huì)存儲(chǔ)在 oracle數(shù)據(jù)庫(kù)中，這對(duì)于網(wǎng)絡(luò)程序來(lái)說(shuō)真是天降福音，因?yàn)?，這樣一系列的處理交互， 都可以以一個(gè)調(diào)用數(shù)據(jù)庫(kù)本身存在的程序而完成，大大提高了性能。5 可以與 sql交互， 在 pl/sql 中可以嵌入 sql 的 dml、 dql、tcl（事務(wù)控制語(yǔ)言）語(yǔ)句。5 第二章sql回顧你都開始對(duì) pl/sql 感性趣了，那么 s

6、ql肯定是多多少少聽說(shuō)過(guò)的，呵呵，就回顧一些基礎(chǔ)吧，那些拓展的內(nèi)容，建議是遇見(jiàn)了再研究吧。dql ：源碼 dql.sql：select dname, max(sal) - 5 對(duì)列篩選（分組字段或聚集函數(shù)）from emp left outer join dept - 1 確定表using(deptno) where deptno 0 - 2 確定行（記錄）group by dname - 3 將行分組having max(comm) is null or max(comm) 0 - 4 對(duì)組篩選order by dname - 6 對(duì)結(jié)果集排序如上的源碼表示了sql語(yǔ)句的執(zhí)行順序，這個(gè)一定要

7、理解好啊！思考題：?jiǎn)栴}：為什么帶有 group by的 select和 having只能含有分組字段和聚集函數(shù)呢？答：因?yàn)?group by分組后，只能對(duì)組進(jìn)行操作了。也就是說(shuō)，無(wú)論你是篩選行having還是列 select ，都要以組為單位進(jìn)行，所以只能使用組共有的屬性，除了分組字段外， 只有像什么每組的最大值啊、最小值啊、 平均值啊等等這些組的特性或叫 組員共有的特性。所以在使用時(shí)一定注意， 帶有 group by后，having和 select中只能有 -分組字6 段和聚集函數(shù)dml ：insert into values就不再重復(fù)了， 不過(guò)聽說(shuō)現(xiàn)在可以一次性向多個(gè)表中插入呢，使用 in

8、sert into all和 insert into first，具體用法如下：insert into all/first when 條件 then into 表 1 when 條件 then into 表 2 when 條件 then into 表 3 else into 表 4 select from 表源至于 all 和 first的區(qū)別就是：如果是all ，就會(huì)插入所有滿足條件的表，而如果是first 直插入第一個(gè)滿足條件的表。tcl：commit; rollback to a; savepoint a; 不多寫了，因?yàn)槲乙仓恢肋@點(diǎn)兒了，呵呵，不過(guò)聽說(shuō)sql 有個(gè)優(yōu)化 hint 挺好

9、玩，如： insert /+append+/ into table values xxx；還有對(duì)group by的拓展 rollup 和 cube，還有 grouping函數(shù)、group set等，以后再研究把，誰(shuí)讓俺是菜鳥呢？7 第三章 pl/sql的基本結(jié)構(gòu)本章將從 pl/sql 的分類、 pl/sql 的基本結(jié)構(gòu)以及第一個(gè)pl/sql 程序展開。pl/sql 的分類：按照 pl/sql 的運(yùn)行環(huán)境， 可以分為客戶端 pl/sql 和服務(wù)器端pl/sql 。通常，我們（指開發(fā)者）接觸到的pl/sql 都是存儲(chǔ)在服務(wù)器 oracle數(shù)據(jù)庫(kù)中的， 所以主要學(xué)習(xí)這一種就可以了，至于客戶端的 pl

10、/sql ，我也不太了解啊 ,o()o 唉希望大蝦在與我聯(lián)系，加上?。“凑招问?，可分為命名的pl/sql 和匿名的 pl/sql，其中，命名的，又可以分為子程序和觸發(fā)器，而子程序又可以分為包、函數(shù)、過(guò)程。是不是有點(diǎn)兒？不過(guò)不要緊，等你看過(guò)整個(gè)文章，再回來(lái)看就是很簡(jiǎn)單了。pl/sql 的基本結(jié)構(gòu)如下：declare 在這里可以定義變量、常量、異常等。begin 在這兒可以寫一些執(zhí)行sql 或 pl/sql 語(yǔ)句。exception 這兒可以捕獲并處理異常怎樣在發(fā)生異常后讓程序繼續(xù)執(zhí)行其他語(yǔ)句呢？8 答：寫在這塊兒不就得了，呵呵end；結(jié)束了，別忘了?；?啊，呵呵你的第一個(gè)程序？也是我的， ? h

11、ello, world!?有人說(shuō) hello world太老套了， boring，有什么，在你什么都不懂時(shí)還能開發(fā)出更高級(jí)的么，我們就hello world呵呵。源碼：說(shuō)明：源碼都是由pl/sql developer 8 開發(fā)，于 oracle10g測(cè)試- created on 2010-4-27 by quasar /* 這就是你的第一個(gè)程序了，也是我的，哈哈*/declare msg varchar2(15); -定義變量begin msg := hello world; -賦值dbms_output.put_line(msg); -內(nèi)臵包使用end; 運(yùn)行結(jié)果：hello world p

12、l/sql 的注釋可以通過(guò)這個(gè)程序看出來(lái)啊，自己看吧，不多說(shuō)9 了10 第四章 pl/sql的數(shù)據(jù)類型及使用本章將介紹 pl/sql 的數(shù)據(jù)類型，有些數(shù)據(jù)類型很?簡(jiǎn)單?，有些很難理解， 不過(guò)都是因?yàn)檫@些數(shù)據(jù)類型你沒(méi)有使用過(guò)，用幾次就知道了，在這里，先認(rèn)識(shí)認(rèn)識(shí)它們，不熟也沒(méi)關(guān)系。pl/sql 的變量類型可以分為標(biāo)量、 復(fù)合類型、 參照類型和 lob類型四類，下面就分別介紹給大家。標(biāo)量，就是單值變量，這么說(shuō)應(yīng)該可以理解吧。 常用的類型如下：字符串： varchar2(n) 、char(n) 、long、long raw 在程序設(shè)計(jì)中，常常用到字符串的處理，在pl/sql 中，有很好的支持，不但有字

13、符串函數(shù)，還有對(duì)正則表達(dá)式的支持。我們最常用的數(shù)據(jù)類型就是 varchar2(n) 了，n 用于最大字符長(zhǎng)度。其中，char和long、long raw不推薦使用，原因是：前者浪費(fèi)資源后者不夠靈活。數(shù)字：number （m, n） 、binary_integer 、binary_float、binary_double 對(duì)于數(shù)字類型， number是不錯(cuò)的選擇，那么m, n代表什么呢？如果定義為 number(6, 2) 則代表整數(shù)位最大長(zhǎng)度是4， 小數(shù)位最大長(zhǎng)度是 2，你猜猜吧， m 和 n 的含義。其中， binary_integer在定義表時(shí)是很常用的。binary_float和 bina

14、ry_double是 oracle 10g的新數(shù)據(jù)類型，注意在賦值時(shí)格式分別如下： 223.4323f和 23423.4545d, 像 java吧,可能都遵循ieee-754 的浮點(diǎn)數(shù)標(biāo)準(zhǔn)吧。布爾：boolean 11 布爾值分別為： true、false、null 日期：date和 timestamp date默認(rèn)只顯示日期， 而 timestamp默認(rèn)情況下會(huì)顯示時(shí)間信息，還有上下午標(biāo)志呢， timestamp是 oracle 9i新加入的數(shù)據(jù)類型。標(biāo)量的定義格式：vname datatype constantnot null := / default exp 例如：name varcha

15、r2(30); real_name varchar2(30) constant default 魏照哲 ; name varchar2(10) := hello; 下面是一個(gè)標(biāo)量使用的例子程序：源碼:scalar.tst 例子程序來(lái)自 scott/scott 用戶或 hr/hr 用戶(alter user scott account unlock 解鎖) - created on 2010-4-27 by quasardeclare eno number(4); empname varchar2(3); empjob varchar2(9); begin select empno, ename

16、, job into eno, empname, empjob from emp where 12 empno = 7788; dbms_output.put_line(oracle 第一個(gè)員工的信息： ); dbms_output.put_line( 員工號(hào)： |eno); dbms_output.put_line( 員工姓名： |empname); dbms_output.put_line( 職位：|empjob); end; 運(yùn)行結(jié)果：如上就是說(shuō) varchar2不夠長(zhǎng)啊，呵呵，交給你個(gè)定義標(biāo)量scalar的法寶： %type，通過(guò)它你就可以使用字段的數(shù)據(jù)類型了，避免類型不匹配等錯(cuò)誤。更

17、改程序empname的定義如下：empname emp.ename%type; 運(yùn)行結(jié)果：oracle第一個(gè)員工的信息：?jiǎn)T工號(hào)： 7788 員工姓名： scott 職位： analyst 接下來(lái)就是復(fù)合變量，即多值變量，包括：記錄、索引表、嵌套表、變長(zhǎng)數(shù)組。其中，后三者有可叫做集合（collection） 。分別針對(duì)13 不同的數(shù)據(jù)結(jié)構(gòu)使用， 如記錄是單行多列時(shí)使用， 其他三個(gè)是多行單列，行和列就代表數(shù)據(jù)庫(kù)表的行和列，慢慢體會(huì)啊記錄：記錄，用于存儲(chǔ)單行多列，即數(shù)據(jù)庫(kù)表中的一條記錄。記錄的定義有兩中格式， 一種是自定義記錄類型， 然后通過(guò)自定義類型定義；一種是通過(guò)表或游標(biāo)的%rowtype 屬性

18、定義，還記得字段的%type 么，差不多。游標(biāo)在后文中介紹。自定義：type rname_type is record ( col1 ctype, col2 ctype, col3 ctype ); ranme rname_type; 光說(shuō)不練犯暈啊，呵呵，下面就看一些記錄的應(yīng)用吧。源碼 record.tst ：- created on 2010-4-27 by quasar declare -通過(guò)自定義和%rowtype屬性定義變量type emp_basic_record is record ( eno emp.empno%type, empname emp.ename%type, 14

19、empjob emp.job%type ); emp_basic_info emp_basic_record; emp_detail_info emp%rowtype; begin select empno, ename, job into emp_basic_info from emp where empno = 7788; dbms_output.put_line( 員工 7788基本信息： ); dbms_output.put_line( 姓名：|emp_basic_info.empname); dbms_output.put_line( 工作：|emp_basic_info.empjo

20、b); -來(lái)了一個(gè)新的 scott員工select ename, job, mgr, sal into emp_detail_info.ename, emp_detail_info.job, emp_detail_info.mgr, emp_detail_info.sal from emp where empno = 7788; emp_detail_info.empno := 8899; emp_detail_info.hiredate := sysdate; -入職吧15 insert into emp values emp_detail_info; commit; dbms_output

21、.new_line; dbms_output.put_line( 入職員工： |emp_detail_info.empno); -老的 scott受不了小的 scott ，讓他改名為 lit scottemp_detail_info.ename := lit scott; update emp set row = emp_detail_info where emp.empno = 8899; commit; dbms_output.new_line; dbms_output.put_line( 入職員工改名為： |emp_detail_info.ename); -小 scott被 fire 了

22、delete from emp where emp.empno = emp_detail_info.empno; commit; dbms_output.new_line; dbms_output.put_line( 員工離職，是誰(shuí)？ |emp_detail_info.ename); end; 運(yùn)行結(jié)果：?jiǎn)T工 7788基本信息：姓名： scott 16 工作： analyst 入職員工： 8899 入職員工改名為： lit scott 員工離職，是誰(shuí)？ lit scott 以上的代碼說(shuō)明了記錄類型的變量在select into 、insert、update 、delete中的應(yīng)用，極其記錄子元

23、素的使用。其中有個(gè)新的關(guān)鍵自，就是 row 代表表中的一行，猜也猜到了吧！集合：pl/sql 表（傳統(tǒng)叫做索引表，又叫index_by table ） ，是一種pl/sql 特有的、單維的、無(wú)容量限制的、松散存儲(chǔ)的列表結(jié)構(gòu)。用于存儲(chǔ)多行單列的數(shù)據(jù)，有點(diǎn)兒類似高級(jí)語(yǔ)言中的數(shù)組，而記錄跟c語(yǔ)言中的結(jié)構(gòu)體類似。不可以作為表的字段類型。注意：在 oracle9i 以后，叫做pl/sql 表了，因?yàn)樗饕念愋筒辉倬窒拊赽inary_integer了，可以是varchar2和 pls_integer。也叫做關(guān)聯(lián)表(associative table).下面就看看怎么使用吧！源碼 plsqltable.ts

24、t ：- created on 2010-4-27 by quasar 17 declare type names_type is table of emp.ename%type index by binary_integer; names names_type; type ano_names_type is table of emp.ename%type index by varchar2(10); ano_names ano_names_type; begin names(2) := elaine; names(-18) := soprano; ano_names(girl) := ela

25、ine; ano_names(man) := soprano; dbms_output.put_line(ano_names(girl); dbms_output.put_line(names(-18); end; 運(yùn)行結(jié)果：elaine soprano 以上是 plsql table 的使用，其索引不僅僅可以是18 binary_integer哦！19 嵌套表（ nested table ） ，它與 plsql表不同，定義需要create語(yǔ)句，也正因?yàn)榇耍梢宰鳛閿?shù)據(jù)庫(kù)表的字段。 它也是單維的、容量無(wú)限的，初始情況下是密集存儲(chǔ)的，但是經(jīng)過(guò)刪除則變?yōu)橄∈璧牧恕Ｋ饕?到很大的整數(shù)??！定義格式之

26、類的啊，說(shuō)一些理論還不如讓你看源碼，呵呵，但還是補(bǔ)充一句，定義分為兩種：sql 定義和編程定義，格式如下：create or replace type xxx is table of xxx; type xxx is table of xxx; 首先，定義類型 createnestedtable.sql：源碼 nestedtable.tst ：- created on 2010-4-27 by quasar declare insert_table nested_names_type := nested_names_type(jack, quasar, patrick, shu); get_n

27、ested_names nested_names_type; type ano_nested_names_type is table of varchar2(30); prog_nested_names ano_nested_names_type := 20 ano_nested_names_type(); begin insert into test_nested_table values(1234, insert_table); commit; select tnt.nested_names into get_nested_names from test_nested_table tnt

28、where id = 1234; for i in get_nested_names.first.get_nested_names.last loop dbms_output.put_line(get_nested_names(i); end loop; prog_nested_names.extend(2); prog_nested_names(1) := quasar; prog_nested_names(2) := elaine; end; 運(yùn)行結(jié)果：jack quasar patrick 21 shu 以上的程序說(shuō)明了嵌套表的使用特點(diǎn)，類型定義可以在sql 語(yǔ)句中，也可以在 plsql

29、 程序中，而且前者可以作為表的字段類型，注意的是必須使用nested table xxx store as xxx ，后面學(xué)的 varray就不用指定存儲(chǔ)表， 因?yàn)樗谴鎯?chǔ)在所在表中。 使用前要使用構(gòu)造函數(shù)初始化，向 java一樣，然后，在使用時(shí)，一般使用內(nèi)臵extend拓展并初始化單個(gè)元素。變長(zhǎng)數(shù)組 varray和嵌套表很像啊，區(qū)別就是varray容量有限是緊湊儲(chǔ)存的而且使用它作為字段時(shí)不需要指定存儲(chǔ)表。不多說(shuō)了， 直接看源碼吧。首先，定義必須的前提createvarray.sql ：源碼 varray.tst ：- created on 2010-4-27 by quasar declar

30、e type prog_vr_names_type is varray(10) of varchar2(20); prog_vr_names prog_vr_names_type := prog_vr_names_type(); put_vr_names varray_type := varray_type(jacky, christfa); 22 get_vr_names varray_type; begin prog_vr_names.extend(3); prog_vr_names(1) := elaine; prog_vr_names(3) := jobh; insert into t

31、est_varray values(1234, put_vr_names); commit; select tv.vr_names into get_vr_names from test_varray tv where tv.id = 1234; dbms_output.put_line( 共取出記錄： |get_vr_names.count| 條！); end;運(yùn)行結(jié)果：那還用問(wèn)，必須的，兩條??！以上學(xué)習(xí)了復(fù)合類型的使用，后面還要學(xué)習(xí)集合類型的函數(shù)使用，不過(guò)，我猜我現(xiàn)在對(duì)集合類型的了解有點(diǎn)混亂，先給大家列個(gè)表吧，然后再繼續(xù)學(xué)習(xí)集合類型。23 特性索引表嵌套表變長(zhǎng)數(shù)組存儲(chǔ)類型稀疏初始稀疏一直稀

32、疏容量限制無(wú)限無(wú)限有限可以用于表字段類型不可以可以，但必須指定存儲(chǔ)表可以初始化前空集合null null 元素初始化默認(rèn)為空構(gòu)造函數(shù)+extend 構(gòu)造函數(shù)+extend 索引類型數(shù)字、字符串1- 很大1 很大差不多得了，主要是會(huì)用，呵呵，下面就學(xué)學(xué)集合函數(shù)吧！集合提供給大家如下函數(shù)，見(jiàn)下表：函數(shù)作用用法索引表嵌套表變長(zhǎng)數(shù)組extend 增加一個(gè)空元素extend(n) n y y delete 刪除元素delete 全部delete(n) delete(m,n) n y y trim 從尾部刪除trim 一個(gè)trim(n) n y y 24 還有就是一些其他的函數(shù)了， 包括：exists,

33、count, limit （只適用于 varray）,first和 last 下標(biāo), prior和 next 下標(biāo)。集合的操作不只是這些，不過(guò)我是一時(shí)接受不了太多.還包括集合賦值、集合比較啊，使用時(shí)再細(xì)細(xì)體會(huì)吧！希望大蝦賜教??！在這一部分當(dāng)中， 批量綁定很重要啊， 能夠大大提高在處理集合時(shí)的效率，感覺(jué)也比較常用，主要是forall 和 bulk collect兩種語(yǔ)法，下面就學(xué)學(xué)批量綁定（ bulk binding）吧！forall: 使用格式：forall index in start.end forall index in indices of collection forall inde

34、x in values of indexs_collection bulk collect: 使用格式：.bulk collect into collection 源碼 bulkbinding.tst：- created on 2010-4-28 by quasar declare type dname_table_type is table of dept_bck.dname%type; v_dname_table dname_table_type; v_return_table dname_table_type; 25 v_dname dept_bck.dname%type; type i

35、ndex_table_type is table of binary_integer; v_index_table index_table_type := index_table_type(2,4); begin -bulk collectselect dname bulk collect into v_dname_table from dept_bck; -forall in .forall i in v_dname_table.first.v_dname_table.last update dept_bck set dname = v_dname_table(i) returning dn

36、ame bulk collect into v_return_table; -dml returningdbms_output.put_line(dml影響的行數(shù) bulkbinding：|v_return_table.count); commit; -plain insertinsert into dept_bck values(90, yyyy, xxxx) returning dname into v_dname; dbms_output.put_line( 插入：|v_dname|sql%rowcount); -implicit cursor or sql cursor26 -fora

37、ll in indices of .v_dname_table(2) := null; forall i in indices of v_dname_table update dept_bck set dname = v_dname_table(i); dbms_output.put_line( 第 2 個(gè)元素影響的行數(shù)為：|sql%bulk_rowcount(2); commit; -forall in values of .forall i in values of v_index_table update dept_bck set dname = v_dname_table(i); co

38、mmit; end; 以上代碼介紹了bulk binding中 forall 和 bulk collect的使用，其中,forall針對(duì)集合的不連續(xù)性， 又有 forall in indices of 和 forall in values of 變體，挺好的??！哈哈哈除了批量綁定 forall 和 bulk collect之外，你發(fā)現(xiàn)什么了？對(duì)了，sql%bulk_rowcount是專門為批量綁定而設(shè)的游標(biāo)屬性， 就是決定了在批量操作中， 某個(gè)元素所影響的行數(shù)。 還有 returning之句，可以獲得dml 的影響結(jié)果記錄。游標(biāo)，很常用的，這里隱隱約約提到了隱式游標(biāo)，后文會(huì)開一章詳細(xì)討論游標(biāo)c

39、ursor！參照變量：27 參照變量是存放數(shù)值指針的變量，可以通過(guò)參照變量， 共享相同的對(duì)象，降低占用空間。參照變量包括游標(biāo)變量（ref cursor）和對(duì)象類型便來(lái)那個(gè) （ref object_type ）兩種，其中，兩者都將在后文中介紹，不過(guò)，還是先認(rèn)識(shí)以下游標(biāo)變量吧，感性的認(rèn)識(shí)哦！源碼 refcursor.tst ：- created on 2010-4-29 by quasardeclare type emp_ref_cur_type is ref cursor; v_emp_ref_cur emp_ref_cur_type; -v_emp emp%rowtype;type emp_t

40、able_type is table of emp%rowtype; emp_table emp_table_type; begin open v_emp_ref_cur for select * from emp; fetch v_emp_ref_cur bulk collect into emp_table; - bulk collectclose v_emp_ref_cur; dbms_output.put_line( 員工信息表單如下： ); for i in emp_table.first.emp_table.last loop 28 dbms_output.put_line( 工號(hào)

41、： |emp_table(i).empno); dbms_output.put_line( 姓名： |emp_table(i).ename); dbms_output.put_line( 工作： |emp_table(i).job); dbms_output.put_line( 工資： |emp_table(i).sal); dbms_output.put_line( 補(bǔ)助： |nvl(emp_table(i).comm, 0); dbms_output.new_line; end loop; end; 以上講了標(biāo)量、復(fù)合變量（記錄和集合）、參照變量（游標(biāo)變量和對(duì)象類型便變量），還差一個(gè)就是大

42、對(duì)象lob 了，在這里就介紹以下，后面另開辟一章單獨(dú)講解對(duì)它的處理。lob：大對(duì)象在 oracle中有兩種：內(nèi)部 lob，外部 lob；內(nèi)部 lob 包括 clob、nclob、blob；外部 lob 只包含 bfile。內(nèi)部 lob 存儲(chǔ)在數(shù)據(jù)庫(kù)中，數(shù)據(jù)的操作支持事務(wù)。外部的lob存儲(chǔ)在 os 操作系統(tǒng)中，如 bfile，數(shù)據(jù)庫(kù)存放的是在os中的文件指針。clob/nclob用于存儲(chǔ)字符文件，blob 用于存儲(chǔ)二進(jìn)制文件。bfile 用于存儲(chǔ)二進(jìn)制文件，如：電影。學(xué)習(xí)了這章，你有點(diǎn)兒迷糊，是啊，文章結(jié)構(gòu)弄錯(cuò)了，應(yīng)該先學(xué)下一章，流程控制，哈哈哈29 第五章流程控制在上一章節(jié)的例子中， 你可能認(rèn)

43、識(shí)了循環(huán)， 還有些什么流程控制呢？那還用說(shuō)，無(wú)非那主要的三種：順序、選擇、循環(huán)，還有那臭名昭著的 goto，沒(méi)什么可說(shuō)的，就直接代碼展示吧，呵呵，其實(shí)是不會(huì)寫選擇結(jié)構(gòu)：語(yǔ)法：if xxx then elsif xxx then elsif xxx then else xxx end if; case when xxx then when xxx then else xxx end case; 循環(huán)結(jié)構(gòu)語(yǔ)法：loop 30 exit when end loop; while loop end loop; for xxx in . loop end loop; 順序結(jié)構(gòu)： goto label ；

44、源碼 control.tst：- created on 2010-4-29 by quasar declare dept_record dept%rowtype; emp_record emp%rowtype; cursor dept_cur is select * from dept; 31 type emp_table_type is table of emp_record%type; emp_table emp_table_type; begin if dept_cur%isopen then -ifnull; else open dept_cur; dbms_output.put_li

45、ne(open.); end if; /* open后才可以使用屬性 found notfound rowcount 的默認(rèn)值*/dbms_output.put_line(cursor%isopen 的初始值：|booleantochar(dept_cur%isopen); dbms_output.put_line(cursor%found的初始值：|booleantochar(dept_cur%found); dbms_output.put_line(cursor%notfound的初始值：|booleantochar(dept_cur%notfound); dbms_output.put_

46、line(cursor%count 的初始值：|dept_cur%rowcount); 32 while true loop - whilefetch dept_cur into dept_record; if dept_cur%found then dbms_output.put_line(dept_record.dname| locating in |dept_record.loc); else dbms_output.put_line(no data end!); exit; end if; end loop; /* 取值后*/dbms_output.put_line(cursor%is

47、open 的初始值：|booleantochar(dept_cur%isopen); dbms_output.put_line(cursor%found的初始值：|booleantochar(dept_cur%found); dbms_output.put_line(cursor%notfound的初始值：33 |booleantochar(dept_cur%notfound); dbms_output.put_line(cursor%count 的初始值：|dept_cur%rowcount); if dept_cur%isopen then close dept_cur; end if;

48、/* select * bulk collect into emp_table from emp; dbms_output.new_line; for i in emp_table.first.emp_table.last loop - for case emp_table(i).empno when 7788 then dbms_output.put_line(this is the first employee:scott!); else dbms_output.put_line(this is |emp_table(i).ename); end case; - case end loop

49、;*/end; 34 除了認(rèn)識(shí)這些控制流程語(yǔ)句， 還附加地通過(guò)這個(gè)了解了游標(biāo)的屬性，下一章就是處理多行數(shù)據(jù)時(shí)最常用的、最好用的、最簡(jiǎn)便的游標(biāo)了，哈哈哈，拭目以待吧！呵呵呵35 第六章游標(biāo)游標(biāo)， cursor是 oracle9i之前處理多行數(shù)據(jù)的唯一選擇，不過(guò)現(xiàn)在你可以使用前面介紹的select xx bulk collect into from了。雖然如此，游標(biāo)的靈活與簡(jiǎn)便性還是不可代替的。游標(biāo)可以分為隱式游標(biāo)和顯示游標(biāo)。隱式游標(biāo)具備游標(biāo)的特性，是在 oracle執(zhí)行 sql語(yǔ)句時(shí)提供給用戶的游標(biāo)，命名為sql，可以通過(guò) sql%xxx 訪問(wèn)游標(biāo)的四個(gè)屬性：isopen/found/notfo

50、und/rowcount（注意這個(gè)屬性，可能跟你理解的不同啊），這幾個(gè)屬性的含義分別是：除了isopen外，在通過(guò) fetch 之后，是否找到數(shù)據(jù)和已經(jīng)取得幾條的意思，在第五章的 control.tst 中有關(guān)這方面的測(cè)試，回去看吧！再就是顯示游標(biāo)了，顯示游標(biāo)可以分為：0 普通顯示游標(biāo) 1 參數(shù)游標(biāo)、2 游標(biāo)變量（參照變量的一種） 、3 用于 dml 的游標(biāo)（簡(jiǎn)稱dml 游標(biāo)） 、4 游標(biāo)表達(dá)式（又叫嵌套游標(biāo)） ，就這些了，下面就一個(gè)一個(gè)認(rèn)識(shí)吧。在后面還會(huì)涉及到特殊的針對(duì)游標(biāo)的 ?游標(biāo)for 循環(huán)?。顯示游標(biāo)在這個(gè)顯示游標(biāo)中，我們將學(xué)習(xí)使用游標(biāo)的聲明、打開、處理、關(guān)閉固定的操作和處理時(shí)獲得游標(biāo)

51、數(shù)據(jù)的三種fetch 語(yǔ)句：fetch xxx into xxx, xxx; ( 取得一條 ) fetch xxx bulk collect into xxx; （取得全部）fetch xxx bulk collect into xxx limit xxx; 36 （取得 n 條）除了按照正常的套路處理游標(biāo)，還可以使用最簡(jiǎn)便的游標(biāo)for循環(huán)處理游標(biāo)，游標(biāo)for 循環(huán)會(huì)默認(rèn)地打開、處理、關(guān)閉游標(biāo)，很好吧，哈哈哈源碼 plaincursor.tst ：- created on 2010-4-29 by quasardeclare cursor dept_cursor is -declaresele

52、ct * from dept; d_record dept_cursor%rowtype; type d_table_type is table of d_record%type; d_table d_table_type; type d_varray_type is varray(2) of d_record%type; d_varray d_varray_type; begin -游標(biāo) for 循環(huán) 1for dept_record in dept_cursor loop dbms_output.put_line(dept:|dept_record.dname); 37 end loop;

53、 dbms_output.new_line; -最簡(jiǎn)單的游標(biāo) for 循環(huán) 2for dept_record in (select * from dept) loop dbms_output.put_line(dept:|dept_record.dname); end loop; -fetch intodbms_output.new_line; open dept_cursor; - openloop fetch dept_cursor into d_record; -fetchexit when dept_cursor%notfound; dbms_output.put_line(dept:

54、|d_record.dname); end loop; close dept_cursor; -close-fetch bulk collect into (without limit)38 dbms_output.new_line; open dept_cursor; fetch dept_cursor bulk collect into d_table; close dept_cursor; for i in d_table.first.d_table.last loop dbms_output.put_line(dept:|d_table(i).dname); end loop; -fe

55、tch bulk collect into limitdbms_output.new_line; open dept_cursor; loop fetch dept_cursor bulk collect into d_varray limit 2; exit when dept_cursor%notfound; dbms_output.put_line(-); for i in 1.2 loop dbms_output.put_line(dept:|d_varray(i).dname); end loop; end loop; close dept_cursor; end; 看到了吧，游標(biāo)

56、for 循環(huán)還可以直接與查詢語(yǔ)句使用，如果對(duì)于39 遍歷數(shù)據(jù)，再好不過(guò)了。那么什么是帶有參數(shù)的游標(biāo)呢？就是聲明時(shí)帶有一個(gè)參數(shù)唄，看代碼就知道嘍，嘻嘻嘻源碼 paramcursor.tst ：- created on 2010-4-29 by quasar declare cursor dept_param_cur(n dept.deptno%type) is select * from dept where dept.deptno = n; dept_record dept%rowtype; begin - test statements hereopen dept_param_cur(20)

57、; - 在 open時(shí)傳入?yún)?shù)loop fetch dept_param_cur into dept_record; exit when dept_param_cur%notfound; dbms_output.put_line(20 is |dept_record.dname); end loop; close dept_param_cur; end; 看到了吧，是不是超級(jí)好用，超級(jí)簡(jiǎn)單啊，呵呵，就是聲明的時(shí)40 候有點(diǎn)兒變化啊。那么什么又是游標(biāo)變量呢？游標(biāo)變量有點(diǎn)像二級(jí)指針啊，游標(biāo)就是個(gè)指針，指向一塊內(nèi)存區(qū)域，游標(biāo)變量就是二級(jí)指針了，呵呵，至于怎么使用，看代碼吧，注意return啊，呵呵呵

58、源碼 refcursor.tst ：- created on 2010-4-29 by quasar declare type dept_ref_cursor_type is ref cursor; - without returnv_dept_ref_cursor dept_ref_cursor_type; v_dept_record dept%rowtype; type t_emp_record is record ( empname emp.ename%type, sal emp.sal%type ); v_emp_record t_emp_record; type emp_ref_cu

59、rsor_type is ref cursor return t_emp_record; - with returnv_emp_ref_cur emp_ref_cursor_type; begin 41 - test statements hereopen v_dept_ref_cursor for select * from dept; loop fetch v_dept_ref_cursor into v_dept_record; exit when v_dept_ref_cursor%notfound; dbms_output.put_line(d:|v_dept_record.dnam

60、e); end loop; close v_dept_ref_cursor; -with returnopen v_emp_ref_cur for select ename, sal from emp; fetch v_emp_ref_cur into v_emp_record; dbms_output.put_line(emp:|v_emp_record.empname| |v_emp_record.sal); close v_emp_ref_cur; end; 其實(shí)，我看啊，游標(biāo)變量帶不帶return沒(méi)什么大用，返回什么不42 還是取決于 select語(yǔ)句，扯什么扯，哈哈哈怎樣通過(guò)游標(biāo)進(jìn)行