PL/SQL筆記
PL/SQL塊中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)以及事務(wù)控制語句(COMMIT,ROLLBACK,SAVEPOINT),而不能直接嵌入DDL語句(CREATE,ALTER,DROP)和DCL語句(GRANT,REVOKE)

1.檢索單行數(shù)據(jù)
?
? 1.1使用標(biāo)量變量接受數(shù)據(jù)
? v_ename emp.ename%type;
? v_sal?? emp.sal%type;
? select ename,sal into v_ename,v_sal from emp where empno=&no;

? 1.2使用記錄變量接受數(shù)據(jù)
? type emp_record_type is record(
? ename emp.ename%type,sal emp.sal%type);
? emp_record emp_record_type;
? select ename,sal into emp_record from emp where empno=&no;

? 1.3嵌入SELECT語句注意事項(xiàng):
? 使用SELECT INTO語句時(shí),必須要返回一條數(shù)據(jù),并且只能返回一條數(shù)據(jù)
?
? no_date_found:
? select into沒有返回?cái)?shù)據(jù)
? too_many_rows:
? select into返回多條數(shù)據(jù)
?
? where子句使用注意事項(xiàng):
? 使用的變量名不能與列名相同,否則觸發(fā)TOO_MANY_ROWS例外.
?

2.操縱數(shù)據(jù)
? 2.1使用VALUES子句插入數(shù)據(jù)
? v_deptno dept.deptno%type;
? v_dname dept.dname%type;
? v_deptno:=no;
? v_dname:='&name';
? insert into dept (deptno,dname) values(v_deptno,v_dname);

? 2.2使用子查詢插入數(shù)據(jù)
? v_deptno emp.deptno%type:=&no;
? insert into employee select * from emp where deptno=v_deptno;
?
? 2.3更新數(shù)據(jù)
? 使用表達(dá)式更新列值
? v_deptno dept.deptno%type:=no;
? v_loc dept.loc%type:='&loc';
? update dept set loc=v_loc where deptno=v_deptno;

? 2.4使用子查詢更新列值
? v_ename emp.ename%type:='&name';
? update emp set (sal,comm) = (select sal,comm from emp where ename=v_ename) where job = (select job from emp where ename=v_ename)

? 2.5刪除數(shù)據(jù)
? 使用變量刪除數(shù)據(jù)
? v_deptno dept.deptno%type:=&no;
? delete from dept where deptno=v_deptno;
?
? 2.6使用子查詢刪除數(shù)據(jù)
? v_ename emp.ename%type:='&name';
? delete from emp where deptno=(select deptno from emp where ename=v_ename);

?
3.SQL游標(biāo)
? 游標(biāo)是指向上下文區(qū)的指針,包括隱含游標(biāo)(SQL游標(biāo))和顯式游標(biāo)兩種類型
? SQL游標(biāo)用于處理SELECT INTO ,INSERT,UPDATE以及DELETE語句.
? 顯式游標(biāo)用于處理多行的SELECT語句
? SQL游標(biāo)包括:SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN等四種屬性
?
? 3.1 SQL%ISOPEN:執(zhí)行時(shí),會(huì)隱含的打開和關(guān)閉游標(biāo).因此該屬性的值永遠(yuǎn)都是FALSE
?
? 3.2 SQL%FOUND:用于確定SQL語句執(zhí)行是否成功.當(dāng)SQL有作用行時(shí),為TRUE,否則為FALSE
? v_deptno emp.deptno%type:=&no;
? update emp set sal=sal*1.1 where deptno=v_deptno;
? if sql%found then dbms_output.put_line('執(zhí)行成功');? else dbms_output.putline('失敗'); endif
?
? 3.3 sql%notfound:確定SQL語句執(zhí)行是否成功,當(dāng)SQL有作用行時(shí),為false,否則為true
?
? 3.4 sql%rowcount:返回SQL語句所作用的總計(jì)行數(shù)
? v_deptno emp.deptno%type:=&no;
? update emp set sal=sal*1.1 where deptno=v_deptno;
? dbms_output.put_line('修改了'||sql%rowcount||'行');

4.事務(wù)控制語句
? 事務(wù)控制語句包括COMMIT,ROLLBACK以及SAVEPOINT等三種語句
?
? v_sal emp.sal%type:=&salary;
? v_ename emp.ename%type:='&name';
? update emp set sal=v_sal where ename=v_ename;
? commit;
?exception
??? when others then
????? rollback;

? insert into temp values(1);
? savepoint a1;
? insert into temp values(2);
? savepoint a2;
? insert into temp values(3);
? savepoint a3;
? rollback to a2;
? commit;

5.控制結(jié)構(gòu)
? 條件分支語句
?
? 5.1簡單條件判斷
? v_sal number(6,2);
? select sal into v_sal from emp where lower(ename)=lowe('&&name');
? if v_sal<2000 then update emp set sal=v_sal+200 where lower(ename)=lower('&name')
? end if

? 5.2二重條件分支
? v_comm number(6,2);
? select comm into v_comm from emp where empno=&&no;
? if v_comm<>0 then update emp set comm=v_comm+100 where empno=&no;
? else update emp set comm=200 where empno=&no;
? end if

? 5.3多重條件分支
? v_job varchar2(10);
? v_sal number(6,2);
? select job,sal into v_job,v_sal from emp where empno=&&no;
? if v_job='president' then update emp set sal=v_sal+1000 where empno=&no;
? elseif v_job='manager' then update emp set sal=v_sal+500 where empno=&no;
? else update emp set sal=v_sal+200 where empno=&no;
? end if;

? 5.4 CASE語句:
? 在CASE語句中使用單一選擇符進(jìn)行等值比較
? declare
?? v_deptno emp deptno%type;
? begin
?? v_deptno:=&no;
?? case v_deptno
??????? when 10 then update emp set comm=100 where deptno=v_deptno;
??????? when 20 then update emp set comm=80? where deptno=v_deptno;
??????? when 30 then update emp set comm=50? where deptno=v_deptno;
?? else
??????? dbms_output.put_line("不存在');
?? end case;
?? end;

?? 5.5 在CASE語句中使用多種條件比較
?? declare
???? v_sal emp.sal%type;
???? v_ename emp.ename%type;
?? begin
???? select ename,sal into v_ename,v_sal from emp where empno=&no;
???? case
?????? when v_sal<1000 then update emp set comm=100 where ename=v_ename;
?????? when v_sal<2000 then update emp set comm=80? where ename=v_ename;
?????? when v_sal<6000 tehn update emp set comm=50? where ename=v_ename;
???? end case;
?? end;

?? 5.6循環(huán)語句
?? 有基本循環(huán),WHILE循環(huán),FOR循環(huán)
??
?? 基本循環(huán):一定要包含EXIT語句,定義循環(huán)控制變量
?? create table temp(cola int);
?? declare
???? i int:=1;
?? begin
???? loop
?????? insert into temp values(i);
?????? exit when i=10;
??????? i:=i+1;
???? end loop;
? end;

? 5.7 WHILE循環(huán):定義循環(huán)控制變量,并在循環(huán)體內(nèi)改變循環(huán)控制變量的值
? declare
??? i int:=1;
? begin
??? while i<=10 loop
????? insert into temp values(i);
????? i:=i+1;
??? end loop;
? end;

? 5.8 for循環(huán):使用FOR循環(huán)時(shí),ORACLE會(huì)隱含定義循環(huán)控制變量.
? for counter in[reverse]
? lower_bound..upper_bound loop
??? statement1;
??? statement2;
??? .......
? end loop;
? 5.9 counter是循環(huán)控制變量,并且該變量由ORACLE隱含定義,不需要顯示定義;lower_bound和upper_bound分別對(duì)應(yīng)循環(huán)控制變量的上下界值.默認(rèn)情況下,FOR循環(huán),每次會(huì)自動(dòng)增一,指定REVERSE選項(xiàng)時(shí),每次循環(huán)控制變量會(huì)減一
? begin
??? for i in reverse 1..10 loop
????? insert into temp values(i);
??? end loop;
? end;

?
? 5.10嵌套循環(huán)和標(biāo)號(hào):通過在嵌套循環(huán)中使用標(biāo)號(hào),可以區(qū)分內(nèi)層循環(huán)和外層循環(huán),并且可以在內(nèi)層循環(huán)中直接退出外層循環(huán)
? declare
??? result int;
??? begin
??? <<outer>>
??? for i in 1..100 loop
??? <<inter>>
??? for j in 1..100 loop
??? result:=i*j;
??? exit outer when result=1000;
??? exit when result=500;
??? end loop inner;
??? dbms_ouput.put_line(result);
??? end loop outer;
??? dbms_output.put_line(result);
??? end;

???
6.順序控制語句
? PL/SQL不僅提供了條件分支語句和循環(huán)控制語句,而且還提供了順序控制語句GOTO和NULL.一般情況下不使用
??
? 6.1 GOTO:用于跳轉(zhuǎn)到特定標(biāo)號(hào)處去執(zhí)行語句.?
? GOTO LABEL_NAME;
?
? declare
??? i int :=1;
? begin
??? loop
????? insert into temp values(i);
????? if i=10 then
???????? goto end_loop
????? end if;
????? i:=i+1;
?? end loop;
?? <<end_loop>>
?? dbms_output.put_line('循環(huán)結(jié)束');
?? end;

?
?? 6.2 null:不會(huì)執(zhí)行任何操作,并且會(huì)直接將控制傳遞到下一條語句.
?? declare
???? v_sal emp.sal%type;
???? v_ename emp.ename%type;
?? begin
???? select ename,sal into v_ename,v_sal from emp where empno=&no;
???? if v_sal<3000 then update emp set comm=sal*0.1 where ename=v_ename;
???? else
?????? null;
???? end if;
?? end;

??
7.復(fù)合數(shù)據(jù)類型
? 7.1定義PL/SQL記錄
?
? 自定義PL/SQL記錄:需要定義PL/SQL記錄類型和記錄變量,identifier用于指定記錄變量名

? type type_name is record(
?????? field_declaration[,
?????? field_declaration]...
? );
? identifier type_name;
?
? declare
??? type emp_record_type is record(
???????? name emp.ename%type,
???????? salary em.sal%type,
???????? dno emp.deptno%type);
??? emp_record emp_record_type;

??? 使用%rowtype屬性定義記錄變量:可以基于表或視圖定義記錄變量
??? 當(dāng)使用%ROWTYPE屬性定義記錄變量時(shí),記錄成員個(gè)數(shù),名稱,類型與表或視圖列的個(gè)數(shù)，名稱,類型完全相同.
??? dept_record dept%rowtype;
??? emp_record emp%rowtype;

??
??? 在select into 語句中使用PL/SQL 記錄
??? 在select into 語句中使用記錄變量
??? set serveroutput on
??? declare
????? type emp_record_type is record(
?????????? name emp.ename%type,
?????????? salary em.sal%type,
?????????? dno emp.deptno%type);
????? emp_record emp_record_type;
??? begin
????? select ename,sal,deptno into emp_record from emp where empno=&no;
????? dbms_output.put_line(emp_record.name);
??? end;

??? 7.2在select into 語句中使用記錄成員
??? declare
????? type emp_record_type is record(
?????????? name emp.ename%type,
?????????? salary emp.sal%type,
?????????? dna emp.deptno%type);
????? emp_record emp_record_type;
??? begin
????? select ename,sal into emp_record.name,emp_record.salary from emp where empno=&no;
????? dbms_output.put_line(emp_record.name);
??? end;

??? 7.3在insert語句中使用PL/SQL記錄
??? 在VALUES子句中使用記錄變量
??? declare
????? dept_record dept%rowtype;
??? begin
????? dept_record.deptno:=50;
????? dept_record.dname:='administrator';
????? dept_record.loc:='beijing';
????? insert int dept values dept_record;
??? end;

??? 在VALUES子句中使用記錄成員
??? declare
????? dept_record dept%rowtype;
??? begin
????? dept_record.deptno:=60;
????? dept_record.dname:='sales';
????? insert into dept (deptno,dname) values (dept_record.deptno,dept_record.dname);
??? end;

??? 7.4在UPDATE語句中使用PL/SQL記錄
??? 在SET子句中使用記錄變量
??? declare
????? dept_record dept%rowtype;
??? begin
????? dept_record.deptno:=30;
????? dept_record.dnama:='sales';
????? dept_record.loc:='shanghai';
????? update dept set row=dept_record where deptno=30;
?? end;

?? 在SET子句中使用記錄成員
?? declare
???? dept_record dept%rowtype;
?? begin
???? dept_record.loc:='guangzhou';
???? update dept set loc=dept_record.loc where deptno=10;
?? end;

?
?? 7.5在DELETE語句中使用PL/SQL記錄:只能在DELETE語句中的WHERE子句中使用記錄成員
?? declare
???? dept_record dept%rowtype;
?? begin
???? dept_record.deptno:=50;
???? delete from dept where deptno=dept_record.deptno;
?? end;

?
8.pl/sql集合
? 處理單行單列數(shù)據(jù),可以使用標(biāo)量變量,處理單行多列的數(shù)據(jù)，可以使用PL/SQL記錄
? 處理單列多行數(shù)據(jù),可以使用PL/SQL集合
?
? PL/SQL集合類型類似于高級(jí)語言數(shù)組的一種復(fù)合數(shù)據(jù)類型
? 包括:索引表(PL/SQL表),嵌套表(NESTED TABLE),變長數(shù)組(VARRAY)三種
?
? 8.1 索引表:PL/SQL表 元素個(gè)數(shù)沒有限制,并且下標(biāo)可以是負(fù)值
? 定義索引表:
????? type type_name is table of element_type [not null] index by key_type;
????? identifier type_name;
?
? 在索引表中使用BINARY_INTEGER和PLS_INTEGER
? set serveroutput on
? declare
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? ename_table ename_table_type;
? begin
??? select ename into ename_table(-1) from emp where empno=&no;
??? dbms_output.put_line('雇員名:'||ename_table(-1));
? end;

? 在索引表中使用VARCHAR2
? set serveroutput on
? declare
??? type area_table_type is table of number index by varchar2(10);
??? area_table area_table_type;
? begin
??? area_table('北京'):=1;
??? area_table('上海'):=2;
??? area_table('廣州'):=3;
??? dbms_output.put_line('第一個(gè)元素:'||area_table.first);
??? dbms_output.put_line('最后一個(gè)元素:'||area_table.last);
? end;

? 8.2 嵌套表:元素個(gè)數(shù)從1開始,并且元素個(gè)數(shù)沒有限制
? 定義嵌套表:
????? type type_name is table of element_type;
????? identifier type_name;
??
????? declare
??????? type ename_table_type is table of emp.ename%type;
??????? ename_table ename_table_type:=ename_table_type('A','A');

? 在PL/SQL塊中使用嵌套表:使用嵌套表變量時(shí),必須首先使用構(gòu)造方法初始化嵌套表變量,然后才能在塊內(nèi)引用嵌套表元素
? declare
??? type ename_talbe_type is table of emp.ename%type;
??? ename_talbe ename_table_type;
? begin
??? ename_table:=ename_table_type('mary','mary','mary');
??? select ename into ename_table(2) from emp where empno=&no;
??? dbms_output.put_line('雇員名:'||ename_table(2));
? end;

? 在表列中使用嵌套表:
? 在表列中使用嵌套表類型,必須首先使用CREATE TYPE命令建立嵌套表類型.
? 當(dāng)使用嵌套表類型作為表列的數(shù)據(jù)類型時(shí),必須要為嵌套表列指定專門的存儲(chǔ)表
? create type phone_type is table of varchar2(20);
? /
? create table employee(
???? id number(4),name varchar2(10),sal number(6,2),
???? phone phone_type
? )nested table phone store as phone_table;

? 8.3 在PL/SQL塊中為嵌套表列插入數(shù)據(jù)
? 當(dāng)定義嵌套表類型時(shí),ORACLE自動(dòng)為該類型生成相應(yīng)的構(gòu)造方法.當(dāng)為嵌套表列插入數(shù)據(jù)時(shí),需要使用嵌套表的構(gòu)造方法
? begin
??? insert into employee values(1,'scott',800,phone_type('0471-3456788','13804711111'));
? end;

? 在PL/SQL塊中檢索嵌套表列的數(shù)據(jù)
? 當(dāng)在PL/SQL塊中檢索嵌套表列的數(shù)據(jù)時(shí),需要定義嵌套表類型的變量接受其數(shù)據(jù).
? set serveroutput on
? declare
??? phone_table phone_type;
? begin
??? select phone into phone_table from employee where id=1;
??? for i in 1..phone_table.count loop
??? dbms_output.put_line('電話:'||phone_table(i));
??? end loop;
? end;

?
? 8.4 在pl/sql塊中更新嵌套表列的數(shù)據(jù)
? 更新嵌套表列的數(shù)據(jù)時(shí),首先需要定義嵌套表變量,并使用構(gòu)造方法初始化變量,然后才可在執(zhí)行部分使用UPDATE語句更新數(shù)據(jù)
? declare
??? phone_table phone_type:=phone_type('0471-3456788','13804711111','0471-2233066','13056278568');
? begin
??? update employee set phone=phone_talbe where id=1;
? end;

? 8.5變長數(shù)組(varray)
? VARRAY也是一種用于處理PL/SQL數(shù)組的數(shù)據(jù)類型, 它也可以做為表列的數(shù)據(jù)類型使用.
? 元素下標(biāo)以1開始,并且元素的最大個(gè)數(shù)是有限制的
? 定義VARRAY的語法:
????? type type_name is varray(size_limite) of element_type [not mull];
????? identifier type_name;
? 當(dāng)使用VARRAY元素時(shí),必須要使用其構(gòu)造方法初始化VARRAY元素.

? declare
??? type ename_table_type is varrar(20) of emp.ename%type;
??? ename_talbe ename_table_type:=ename_table_type('A','A');

? 8.6 在PL/SQL塊中使用VARRAY
? 必須首先使用其構(gòu)造方法來初始化VARRAY變量,然后才能在PL/SQL塊內(nèi)引用VARRAY元素
? declare
??? type ename_table_type is varray(20) of emp.ename%type;
??? ename_table ename_table_type:=ename_table_type('mary');
? begin
??? select ename into ename_table(1) from emp where empno=&no;
??? dbms_output.put_line('雇員名:'||ename_table(1));
? end;

? 在表列中使用varray
? 要在表列中引用該數(shù)據(jù)類型,則必須使用CREATE TYPE命令建立VARRAY類型
? create type phone_type is varray(20) of varchar2(20);
? /
? create table employee(
??? id number(4),name varchar2(10),
??? sal number(6,2),phone phone_type);
? 在PL/SQL塊中操縱VARRAY列的方法與操縱嵌套表列的方法完全相同.嵌套表列元素個(gè)數(shù)沒有限制,而VARRAY列的元素個(gè)數(shù)是有限制的.
?

? PL/SQL記錄表
? PL/SQL變量處理單行單列數(shù)據(jù)
? PL/SQL記錄處理單行多列數(shù)據(jù)
? PL/SQL集合處理多行單列數(shù)據(jù)
? PL/SQL記錄表處理多行多列數(shù)據(jù)
?
? 8.7 PL/SQL記錄表結(jié)合了PL/SQL記錄和PL/SQL集合的優(yōu)點(diǎn)
? declare
??? type emp_talbe_type is table of emp%rowtype index by binary_integer;
??? emp_table emp_table_type;
? begin
??? select * into emp_table(1) from emp where empno=&no;
??? dbms_output.put_line('雇員姓名:'||emp_table(1).ename);
??? dbms_output.put_line('雇員姓名:'||emp_table(1).sal);
? end;

? 8.8 多級(jí)集合
? 多級(jí)集合是指嵌套了集合類型的集合類型

? 在PL/SQL塊中使用多級(jí)VARRAY:實(shí)現(xiàn)多維數(shù)組功能
? 定義二維VARRAY(10,10):
? declare
??? type a1_varray_type is varray(10) of int;--定義一維VARRAY
??? type na1_varray_type is varray(10) of a1_varray_type;--定義二維VARRAY集合
??? --初始化二維集合變量
??? nv1 nal_varray_type:=nal_varray_type(
??????? a1_varray_type(58,100,102),
??????? a1_varray_type(55,6,73),
??????? a1_varray_type(2,4);
??? begin
????? dbms_output.put_line('顯示二維數(shù)組所有元素');
????? for i in 1..nv1.count loop
????????? for j in 1..nv1(i).count loop
????????????? dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
????????? end loop;
????? end loop;
?? end;

? 在PL/SQL塊中使用多級(jí)嵌套表
? 如果多維數(shù)組的元素個(gè)數(shù)沒有限制,那么可以在嵌套表類型中嵌套另一個(gè)嵌套表類型
?
? 8.9 二維嵌套表
? declare
??? type a1_table_type is table of int;--定義一維嵌套表
??? type nal_table_type is table of a1_table_type;--定義二維嵌套表集合
??? --初始化二維集合變量
??? nvl nal_table_type:=nal_table_type(
??????? a1_table_type(2,4),
??????? a1_table_type(5,73));
? begin
??? dbms_output.put_line('顯示二維數(shù)組所有元素');
??? for i in 1..nvl.count loop
?????? for j in 1..nvl(i).count loop
?????????? dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
?????? end loop;
??? end loop;
? end

? 在PL/SQL塊中使用多級(jí)索引表
? 二維索引表:
? declare
??? type a1_table_type is table of int index by binary_integer;
??? type nal_table_type is table of al_table_type index by binary_integer;
??? nvl nal_table_type;
? begin
??? nvl(1)(1):=10;
??? nvl(1)(2):=5;
??? nvl(2)(1):=100;
??? nvl(2)(2):=50;
??? dbms_output.put_line('顯示二維數(shù)組所有元素');
??? for i in 1..nvl.count loop
?????? for j in 1..nvl(i).count loop
????????? dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
?????? end loop;
??? end loop;
? end;

?
? 8.10集合方法:ORACLE提供的用于操縱集合變量的內(nèi)置函數(shù)或過程,其中EXISTS,COUNT,LIMIT,FIRST,NEXT,FRIOR和NEXT是函數(shù)
?????????? 而EXTEND,TRIM和DELETE則是過程
? 集合方法只能在PL/SQL語句中使用,不能在SQL語句中使用.
? 集合方法EXTEND和TRIM只適用于嵌套表和VARRAY,而不適合于索引表

? 1.EXISTS:用于確定集合元素是否存在
? declare
??? type ename_table_type is table of emp.ename%type;
??? ename_table ename_table_type;
? begin
??? if ename_table.exists(1) then
?????? ename_table(1):='SCOTT';
??? else
?????? dbms_output.put_line('必須初始化集合元素');
??? end if;
? end;

? 2.COUNT:用于返回當(dāng)前集合變量中的元素總個(gè)數(shù).
? declare
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? ename_table ename_table_type;
? begin
??? ename_table(-5):='scott';
??? ename_table(1):='smith';
??? ename_table(5):='mary';
??? ename_table(10):='blake';
??? dbms_output.put_line('集合元素總個(gè)數(shù):'||ename_table.count);
? end;

? 3.LIMIT:用于返回集合元素的最大個(gè)數(shù).因?yàn)榍短妆砗退饕淼挠鄶?shù)個(gè)數(shù)沒有限制,返回NULL
??????? 對(duì)于VARRAY來說，該方法返回VARRAY所允許的最大元素個(gè)數(shù)
? declare
??? type ename_table_type is varray(20) of emp.ename%type;
??? ename_table ename_table_type:=ename_table_type('mary');
? begin
??? dbms_output.put_line('集合元素的最大個(gè)數(shù):'||ename_table.limit);
? end;

? 4.FIRST和LAST:FIRST用于返回集合變量第一個(gè)元素的下標(biāo),而LAST方法則用于返回集合變量最后一個(gè)元素的下標(biāo)
? declare
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? ename_table ename_table_type;
? begin
??? ename_table(-5):='scott';
??? ename_table(1):='smith';
??? ename_table(5):='mary';
??? ename_table(10):='blake';
??? dbms_output.put_line('第一個(gè)元素:'||ename_table.first);
??? dbms_output.put_line('最后一個(gè)元素:'||ename_table.last);
? end;

? 5.FRIOR和NEXT:PRIOR返回當(dāng)前集合元素的前一個(gè)元素的下標(biāo),而NEXT方法則用于返回當(dāng)前集合元素的后一個(gè)元素的下標(biāo)
? declare
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? ename_table ename_table_type;
? begin
??? ename_table(-5):='scott';
??? ename_table(1):='smith';
??? ename_table(5):='mary';
??? ename_table(10):='blake';
??? dbms_output.put_line('元素5的前一個(gè)元素:'||ename_table.prior(5));
??? dbms_output.put_line('元素5的后一個(gè)元素:'||ename_table.next(5));
? end;

? 6.EXTEND:用于擴(kuò)展集合變量的尺寸,并為它們?cè)黾釉?只適用于嵌套表和VARRAY.
???????? 三種調(diào)用格式:EXTEND,EXTEND(N),EXTEND(N,I):添加N個(gè)元素,值與第I個(gè)元素相同
? declare
??? type ename_table_type is varray(20) of varchar2(10);
??? ename_table ename_table_type;
? begin
??? ename_table:=ename_table_type('mary');
??? ename_table.extend(5,1);
??? dbms_output.put_line('元素個(gè)數(shù):'||ename_table.count);
? end;

? 7.TRIM:用于從集合尾部刪除元素,有TRIM和TRIM(N)兩種調(diào)用格式.
?????? 只適用于嵌套表和VARRAY
? declare
??? type ename_table_type is table of varchar2(10);
??? ename_table ename_table_type;
? begin
??? ename_table:=ename_table_type('a','a','a','a','a');
??? ename_table.trim(2);
??? dbms_table.put_line('元素個(gè)數(shù):'||ename_table.count);
? end;

? 8.DELETE:刪除結(jié)合元素,但該方法只適用于嵌套表和索引表,不適用于VARRAY.
???????? 有DELETE,DELETE(N),DELETE(M,N)三種調(diào)用方式.
???????? DETELE(M,N)刪除集合變量從M到N之間的所有元素
? declare
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? ename_table ename_table_type;
? begin
??? ename_table(-5):='scott';
??? ename_table(1):='smith';
??? ename_table(5):='mary';
??? ename_table(10):='blake';
??? ename_table.delete(5);
??? dbms_output.put_line('元素總個(gè)數(shù):'||ename_table.count);
? end;

?
? 8.11集合賦值
? 使用嵌套表和VARRAY時(shí),通過執(zhí)行INSERT,UPDATE,FETCH,SELECT賦值語句,可以將一個(gè)集合的數(shù)據(jù)賦值給另一個(gè)集合.
? 當(dāng)給嵌套表賦值時(shí),還可以使用SET,MULTISET UNION,MULTISET INTERSECT,MULTISET EXCEPT等集合操作符
? SET:用于取消嵌套表中的重復(fù)值.
? MULTISET UNION:取得兩個(gè)嵌套表的并集(DISTINCT)
? MULTISET INTERSECT:用于取得兩個(gè)嵌套表的交集.
? NULTISET EXCEPT:用于取得兩個(gè)嵌套表的差集
?
? 1.將一個(gè)集合的數(shù)據(jù)賦值個(gè)另一個(gè)集合
??? 源集合和目標(biāo)集合的數(shù)據(jù)類型必須完全一致.
? declare
??? type name_varray_type is varray(4) of varchar2(10);
??? name_array1 name_varray_type;
??? name_array2 name_varray_type;
? begin
??? name_array1:=name_varray_type('scott','smith');
??? name_array2:=name_varray_type('a','a','a','a');
??? dbms_output.put_line('name_array2的原數(shù)據(jù):');
??? for i in 1..name_array2.count loop
?????? dbms_output.put_line(' '||name_array2(i));
??? end loop;
??? dbms_output.new_line;
??? name_array2:=name_array1;
??? dbms_output.put('name_array2的新數(shù)據(jù):');
??? for i in 1..name_array2.count loop
?????? dbms_output.put(' '||name_array2(i));
??? end loop;
??? dbms_output.new_line;
? end;

? 2.給集合賦NULL值:清空集合變量的所有數(shù)據(jù)(集合方法DETELE,TRIM也可以)
? declare
??? type name_varray_type is varray(4) of varchar2(10);
??? name_array name_varray_type;
??? name_empty name_varray_type;
? begin
??? name_array:=name_varray_type('scott','smith');
??? dbms_output.put_line('name_array的原有元素個(gè)數(shù):'||name_array.count);
??? name_array:=name_empty;
??? if name_array is null then?????
?????? dbms_output.put_line('name_array的現(xiàn)有元素個(gè)數(shù):0');
??? end if;
? end;

? 3.使用集合操作符給嵌套表賦值
???
??? 1.使用SET操作符:用于取消特定嵌套表中的重復(fù)值.
? declare
??? type nt_table_type is table of number;
??? nt_table nt_table_type:=nt_table_type(2,4,3,1,2);
??? result nt_table_type;
? begin
??? result:=set(nt_table);
??? dbms_output.put('result:');
??? for i in 1..result.count loop
????? dbms_output.put(' '||result(i));
??? end loop;
??? dbms_output.new_line;
? end;

??? 2.使用MULTISET UNION操作符:取得兩個(gè)嵌套表的并集.結(jié)果集中會(huì)包含重復(fù)值
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3);
??? nt2 nt_table_type:=nt_table_type(3,4,5);
??? result nt_table_type;
? begin
??? result:=nt1 MULTISET union nt2;
??? dbms_output.put('result:');
??? for i in 1..result.count loop
????? dbms_output.put(' '||result(i));
??? end loop;
??? dbms_output.new_line;
? end;

? 3.使用MULTISET UNION DISTINCT操作符:用于取得兩個(gè)嵌套表的并集,并取消重復(fù)結(jié)果.
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3);
??? nt2 nt_table_type:=nt_table_type(3,4,5);
??? result nt_table_type;
? begin
??? result:=nt1 multiset union distinct nt2;
??? dbms_output.put('result:');
??? for i in 1..result.count loop
????? dbms_output.put(' '||result(i));
??? end loop;
??? dbms_output.new_line;
? end;
?
? 4.使用MULTISET INTERSECT操作符:用于取得兩個(gè)嵌套表的交集
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3);
??? nt2 nt_table_type:=nt_table_type(3,4,5);
??? result nt_table_type;
? begin
??? result:=nt1 multiset intersect nt2;
??? dbms_output.put('result:');
??? for i in 1..result.count loop
????? dbms_output.put(' '||result(i));
??? end loop;
??? dbms_output.new_line;
? end;
?
? 5.使用MULTISET EXCEPT操作符:取得兩個(gè)嵌套表的差集.在NT1中存在,但在NT2中不存在
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3);
??? nt2 nt_table_type:=nt_table_type(3,4,5);
??? result nt_table_type;
? begin
??? result:=nt1 multiset except nt2;
??? dbms_output.put('result:');
??? for i in 1..result.count loop
?????? dbms_output.put(' '||result(i));
??? end loop;
??? dbms_output.new_line;
? end;

? 8.4比較集合
? 函數(shù)cardinality用于返回嵌套表變量的元素個(gè)數(shù)??
? 操作符SUBMULTISET OF用于確定一個(gè)嵌套表是否為另一個(gè)嵌套表的子集
? 操作符MEMBER OF用于檢測(cè)特定數(shù)據(jù)是否為嵌套表元素
? 操作符IS A SET用于檢測(cè)嵌套表是否包含重復(fù)的元素值
? 操作符IS EMPTY用于檢測(cè)嵌套表是否為NULL.
?
? 1.檢測(cè)集合是否為NULL
? declare
??? type name_table_type is table of varchar2(10);
??? name_table name_table_type;
? begin
??? if name_table is empty then
?????? dbms_output.put_line('name_table未初始化');
??? end if;
? end;

? 2.比較嵌套表是否相同
? 使用比較符=和!=檢測(cè)兩個(gè)嵌套表是否相同.不能比較VARRAY和索引表
? declare
??? type name_table_type is table of varchar2(10);
??? name_table1 name_table_type;
??? name_table2 name_table_type;
? begin
??? name_table1:=name_table_type('scott');
??? name_table2:=name_table_type('smith');
??? if name_table1=name_table2 then
????? dbms_output.put_line('兩個(gè)嵌套表完全相同');
??? else
????? dbms_output.put_line('兩個(gè)嵌套表數(shù)值不同');
??? end if;
? end;

? 3.在嵌套表上使用集合操作符
??? 在嵌套表上使用ANSI集合操作符CARDINALITY,MEMBER OF, IS A SET.不適用于VARRAY和索引表
??? 使用函數(shù)CARDINALITY
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3,1);
? begin
??? dbms_output.put_line('元素個(gè)數(shù):'||cardinality(nt1));
? end;

??? 使用操作符SUBMULTISET OF:用于確定一個(gè)嵌套表是否為另一個(gè)嵌套表的子集.
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3);
??? nt2 nt_table_type:=nt_table_type(1,2,3,4);
? begin
??? if nt1 submultiset of nt2 then
?????? dbms_output.put_line('nt1是nt2的子集);
??? end if;
? end;

? 使用操作符MEMBER OF :用于檢測(cè)特定數(shù)據(jù)是否為嵌套表的元素.
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3,5);
??? v1 number:=&v1;
? begin
??? if v1 MEMBER OF nt1 then
????? dbms_output.put_line('v1是nt1的元素');
??? end if;
? end;

? 使用操作符IS A SET:用于檢測(cè)嵌套表是否包含重復(fù)的元素值
? declare
??? type nt_table_type is table of number;
??? nt1 nt_table_type:=nt_table_type(1,2,3,5);
? begin
??? if nt1 is a set then
????? dbms_output.put_line('嵌套表NT1無重復(fù)值');
??? end if;
? end;

?

? 8.5批量綁定
? 當(dāng)在select,insert,update,delete語句上處理批量數(shù)據(jù)時(shí),通過批量綁定,可以極大的加快數(shù)據(jù)處理速度,提高應(yīng)用程序性能
? 批量綁定是使用BULK COLLECT子句和FORALL語句來完成的
? BULK COLLECT 子句用于取得批量數(shù)據(jù),該子句只能用于SELECT語句,FETCH語句和DML返回子句
? FORALL只適用于執(zhí)行批量的DML操作

? 1.不使用批量綁定
? declare
??? type id_table_type is table of number(6) index by binary_integer;
??? type name_table_type is table of varchar2(10) index by binary_integer;
??? id_table id_table_type;
??? name_table name_table_type;
??? start_time number(10);
??? end_time number(10);
? begin
??? for i in 1..5000 loop
?????? id_table(i):=i;
?????? name_table(i);='name'||to_char(i);
??? end loop;
??? start_time:=dbms_utility.get_time;
??? for i in 1..id_table.count loop
??????? insert into demo values(id_table(i),name_table(i));
??? end loop;
??? end_time:=dbms_utility.get_time;
??? dbms_output.put_line('總計(jì)時(shí)間(秒):'||to_char((end_time-start_time)/100));
? end;

? 2.使用批量綁定
?? declare
??? type id_table_type is table of number(6) index by binary_integer;
??? type name_table_type is table of varchar2(10) index by binary_integer;
??? id_table id_table_type;
??? name_table name_table_type;
??? start_time number(10);
??? end_time number(10);
? begin
??? for i in 1..5000 loop
?????? id_table(i):=i;
?????? name_table(i);='name'||to_char(i);
??? end loop;
??? start_time:=dbms_utility.get_time;
??? forall i in 1..id_table.count
?????? insert into demo values(id_table(i),name_table(i));
??? end_time:=dbms_utility.get_time;
??? dbms_output.put_line('總計(jì)時(shí)間(秒):'||to_char((end_time-start_time)/100));
? end;

? 8.6 FORALL語句
? 執(zhí)行批量insert,update,delete操作時(shí),使用forall語句,FORALL不是循環(huán)語句
? oracle9i當(dāng)使用FORALL語句時(shí),必須具有連續(xù)的元素
? oracle10g通過使用indices of和values of子句,可以使用不連續(xù)的集合元素.
? forall三種執(zhí)行語法:
? forall index in lower_bound..upper_bound sql_statement;
? forall index in indices of collection [between lower_bond.and.upper_bound] sql_statement;
? forall index in values of index_collection sql_statement;

? 1.在insert語句上使用批量綁定
? declare
??? type id_table_type is table of number(6) index by binary_integer;
??? type name_table_type is table of varchar2(10) index by binary_integer;
??? id_table id_table_type;
??? name_table name_table_type;
? begin
??? for i in 1..10 loop
????? id_table(i):=i;
????? name_table(i):='name'||to_char(i);
??? end loop;
??? forall i in 1..id_table.count
??????? insert into demo values(id_table(i),name_table(i));
? end;

? 2.在update語句上使用批量綁定
?? declare
??? type id_table_type is table of number(6) index by binary_integer;
??? type name_table_type is table of varchar2(10) index by binary_integer;
??? id_table id_table_type;
??? name_table name_table_type;
? begin
??? for i in 1..5 loop
????? id_table(i):=i;
????? name_table(i):='n'||to_char(i);
??? end loop;
??? forall i in 1..id_table.count
?????? update demo set name=name_table(i) where id=id_table(i);
? end;

? 3.在DELETE語句上使用批量綁定
? declare
??? type id_table_type is table of number(6) index by binary_integer;
??? id_table id_table_type;
? begin
??? for i in 1..3 loop
????? id_table(i):=i;
??? end loop;
??? forall i in 1..id_table.count
????? delete from demo where id=id_table(i);
? end;

? 4.在FORALL語句中使用部分集合元素
? declare
??? type id_table_type is table of number(6) index by binary_integer;
??? id_table id_table_type;
? begin
??? for i in 1..10 loop
????? id_table(i):=11-i;
??? end loop;
??? forall i in 8..10
?????? insert into demo (id) values (id_table(i));
? end;

? 5.在FORALL語句上使用INDECES OF子句:用于跳過NULL集合元素
? declare
??? type id_table_type is table of number(6);
??? id_table id_table_type;
? begin
??? id_table:=id_table_type(1,null,3,null,5);
??? forall i in indices of id_table
?????? delete from demo where id=id_table(i);
? end;
?
? 6.在FORALL語句上使用VALUES OF子句
? create table new_demo as select * from demo where 1=0
? declare
??? type id_table_type is table of demp.id%type;
??? type name_table_type is table of demo.name%type;
??? id_table id_table_type;
??? name_table name_table_type;
??? type index_pointer_type is table of pls_integer;
??? index_pointer index_pointer_type;
? begin
??? select * bulk collect into id_table,name_table from demo;
??? index_pointer:=index_pointer_type(6,8,10);
??? forall i in values of index_pointer
??????? insert into new_demo values(id_table(i),name_table(i));
? end;

? 7.使用SQL%BULK_ROWCOUNT屬性:專門為FORALL語句提供,用于取得在執(zhí)行批量綁定操作時(shí)第i個(gè)元素所作用的行數(shù)
? declare
??? type dno_table_type is table of number(3);
??? dno_table dno_table_type:=dno_table_type(10,20);
? begin
??? forall i in 1..dno_table.count
?????? update emp set sal=sal*1.1 where deptno=dno_table(i);
?????? dbms_output.put_line('第2個(gè)元素更新的行數(shù):'||sql%bulk_rowcount(2));
? end;

?
? 8.7 BULK COLLECT子句
? 用于取得批量數(shù)據(jù)，只適用于select into 語句,fetch into 語句和DML返回子句
? 可將批量數(shù)據(jù)存放到PL/SQL集合變量中
? 1.在select into 語句中使用BULK COLLECT 子句:可以一次將SELECT語句的多行結(jié)果檢索到集合變量中
? declare
??? type emp_table_type is table of emp%rowtype index by binary_integer;
??? emp_table emp_table_type;
? begin
??? select * bulk collect into emp_table from emp where deptno=&no;
??? for i in 1..emp_table.count loop
?????? dbms_output.put_line('雇員姓名:'||emp_table(i).ename);
??? end loop;
? end;

? 2.在DML的返回語句中使用BULK COLLECT子句
? 為了取得DML操作所改變的數(shù)據(jù),可以使用RETURNING子句.
? declare
??? type ename_table_type is table of emp.ename%type;
??? ename_table ename_table_type;
? begin
??? delete from emp where deptno=&no;
????? returning ename bulk collect into ename_table;
??? dbms_output.put('雇員名');
??? for i in 1..ename_table.count loop
?????? dbms_output.put(ename_table(i)|| ' ');
??? end loop;
??? dbms_output.new_line;
? end;

?

9.使用游標(biāo)
? 9.1當(dāng)在PL/SQL塊中執(zhí)行查詢語句SELECT和數(shù)據(jù)操縱語句DML時(shí),ORACLE會(huì)為其分配上下文區(qū)(CONTEXT AREA),游標(biāo)指上下文區(qū)指針
? 對(duì)于數(shù)據(jù)操縱語句和單行SELECT INTO語句來說,ORACLE會(huì)為他們分配隱含游標(biāo).
? 使用顯示游標(biāo)處理多行數(shù)據(jù),也可使用SELECT..BULK COLLECT INTO 語句處理多行數(shù)據(jù).

? 1.顯示游標(biāo)
??? 定義游標(biāo)
??? cursor cursor_name is select_statement;
? 2.打開游標(biāo):執(zhí)行對(duì)應(yīng)的SELECT語句并將SELECT語句的結(jié)果暫時(shí)存放到結(jié)果集中.
??? open cursor_name;
? 3.提取數(shù)據(jù)
??? 打開游標(biāo)后,SELECT語句的結(jié)果被臨時(shí)存放到游標(biāo)結(jié)果集中,使用FETCH語句只能提取一行數(shù)據(jù)
??? 通過使用FETCH..BULK COLLECT INTO語句每次可以提取多行數(shù)據(jù)
??? fetch cursor_name into variable1,varibale2,...;
??? fetch cursor_name bulk collect into collect1,collect2,...[limit rows];
? 4.關(guān)閉游標(biāo)
??? close cursor_name;
?
? 9.2顯示游標(biāo)屬性
??? 用于返回顯示游標(biāo)的執(zhí)行信息,包括%isopen,%found,%notfound,%rowcount
? 1.%isopen:確定游標(biāo)是否打開 if cl%isopen then ... else? open c1; end if;
? 2.%found:檢查是否從結(jié)果集中提取到了數(shù)據(jù)
??? loop
????? fetch c1 into var1,var2;
????? if c2%found then ... else exit;
??? end loop;
? 3.%notfound
??? loop
?????? fetch c1 into var1,var2;
?????? exit when c2%notfound;
?????? ...
??? end loop;
? 4.%rowcount:返回當(dāng)前行為止已經(jīng)提取到的實(shí)際行數(shù)
??? loop
????? fetch c1 into my_ename,my_deptno;
????? if c1%rowcount>10 then
????? ...
????? end if;
????? ...
??? end loop;
?
? 9.3顯示游標(biāo)使用示例
? 1.在顯示游標(biāo)中使用fetch..into語句:只能處理一行數(shù)據(jù),除非用循環(huán)語句
? declare
??? cursor emp_cursor is select ename,sal from emp where deptno=10;
??? v_ename emp.ename%type;
??? v_sal emp.sal%type;
? begin
??? open emp_cursor;
??? loop
????? fetch emp_cursor into v_ename,v_sal;
????? exit when emp_cursor%notfound;
????? dbms_output.put_line(v_ename||': '||v_sal);
?? end loop;
?? close emp_cursor;
? end;

? 2.在顯示游標(biāo)中,使用FETCH..BALK COLLECT INTO語句提取所有數(shù)據(jù)
? declare
??? cursor emp_cursor is select ename from emp where deptno=10;
??? type ename_table_type is table of varchar2(10);
??? ename_table ename_table_type;
? begin
??? open emp_cursor;
??? fetch emp_cursor bulk collect into ename_table;
??? for i in 1..ename_table.count loop
??????? dbms_output.put_line(ename_table(i));
??? end loop;
??? close emp_cursor;
? end;

? 3.在顯示游標(biāo)中使用FETCH..BULK COLLECT INTO ..LIMIT語句提取部分?jǐn)?shù)據(jù)
? declare
??? type name_array_type is varray(5) of varchar2(10);
??? name_array name_array_type;
??? cursor emp_cursor is select ename from emp;
??? rows int:=5;
??? v_count int:=0;
? begin
??? open emp_cursor;
??? loop
????? fetch emp_cursor bulk collect into name_array limit rows;
????? dbms_output.pur('雇員名');
????? for i in 1..(emp_currsor%rowcount-v_count) loop
??????? dbms_output.put(name_array(i)||' ');
????? end loop;
????? dbms_output.new_line;
???? v_count:=emp_cursor%rowcount;
???? exit when emp_cursor%notfound;
??? end loop;
??? close emp_cursor;
? end;

? 4.使用游標(biāo)屬性
? declare
??? cursor emp_cursor is select ename from emp where deptno=10;
??? type ename_table_type is table of varchar2(10);???
??? ename_table ename_table_type;
? begin
??? if not emp_cursor%isopen then
?????? open emp_cursor;
??? end if;
??? fetch emp_cursor bulk collect into ename_table;
??? dbms_output.put_line('提取的總計(jì)行數(shù):'||emp_cursor%rowcount);
??? close emp_cursor;
? end;

? 5.基于游標(biāo)定義記錄變量
? declare
??? cursor emp_cursor is select ename,sal from emp;
??? emp_record emp_cursor%rowtype;
? begin
??? open emp_cursor;
??? loop
????? fetch emp_cursor into emp_record;
????? exit when emp_cursor%notfound;
????? dbms_output.put_line('雇員名:'||emp_record.ename||',雇員工資:'||emp_record.sal);
??? end loop;
? end;

? 9.4參數(shù)游標(biāo)
? 定義參數(shù)游標(biāo)時(shí),游標(biāo)參數(shù)只能指定數(shù)據(jù)類型,而不能指定長度.
? cursor cursor_name(parameter_name datatype) is select_statment;
? declare
??? cursor emp_cursor(no number) is select ename from emp where deptno=no;
??? v_ename emp.ename%type;
? begin
??? open emp_cursor(10);
??? loop
????? fetch emp_cursor into v_ename;
????? exit when emp_cursor%notfound;
????? dbms_output.put_line(v_ename);
??? end loop;
??? close emp_cursor;
? end;

? 9.5使用游標(biāo)更新或刪除數(shù)據(jù)
? 要通過游標(biāo)更新或刪除數(shù)據(jù),在定義游標(biāo)時(shí)必須要帶有FOR UPDATE子句
? cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait];
? for update子句用于在游標(biāo)結(jié)果集數(shù)據(jù)上家行共享鎖,防止其他用戶在相應(yīng)行執(zhí)行DML操作
? of子句確定哪些表要加鎖,沒有OF子句,則在所引用的全部表上加鎖
? nowait子句用于指定不等待鎖
? 必須在UPDATE后DELETE語句中引用WHERE CURRENT OF子句
? update table_name set column=.. where current of cursor_name;
? delete table_name where current of cursor_name;
?

? 1.使用游標(biāo)更新數(shù)據(jù)
? declare
??? cursor emp_cursor is select ename,sal from emp for update;
??? v_ename emp.ename%type;
??? v_sal emp.sal%tyep;
? begin
??? open emp_cursor;
??? loop
????? fetch emp_cursor into v_ename,v_oldsal;
????? exit when emp_cursor%notfound;
????? if v_oldsal<2000 then
???????? update emp set sal=sal+100 where current of emp_cursor;
????? end if;
??? end loop;
??? close emp_cursor;
? end;

? 2.使用游標(biāo)刪除數(shù)據(jù)
? declare
??? cursor emp_cursor is select ename,sal,deptno from emp for update;
??? v_ename emp.ename%type;
??? v_oldsal emp.sal%type;
??? v_deptno emp.deptno%type;
? begin
??? open emp_cursor;
??? loop
????? fetch emp_cursor into v_ename,v_oldsal,v_deptno;
????? exit when emp_cursor%notfound;
????? if v_deptno=30 then
???????? delete from emp where current of emp_cursor;
????? end if;
??? end loop;
??? close emp_cursor;
? end;

? 3.使用OF子句在特定表上加行共享鎖
? declare
??? cursor emp_cursor is select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno
??? for update of emp.deptno;
??? emp_record emp_cursor%type;
? begin
??? open emp_cursor;
??? loop
????? fetch emp_cursor into emp_record;
????? exit when emp_cursor%notfound;
????? if emp_record.deptno=30 then
???????? update emp set sal=sal+100 where current of emp_cursor;
????? end if;
????? dbms_output.put_line('雇員名:'||emp_record.ename||',工資:'||emp_record.sal||',部門名:'||emp_record.dname);
??? end loop;
??? close emp_cursor;
? end;

? 4.使用nowait子句
? 通過在FOR UPDATE子句中指定NOWAIT語句,可以避免等待鎖.若已經(jīng)被作用行加鎖,則提示錯(cuò)誤信息
? declare
??? cursor emp_cursor is select ename,sal from emp for update nowait;
??? v_ename emp.ename%type;
??? v_oldsal emp.sal%type;
? begin
??? open emp_cursor;
??? loop
????? fetch emp_cursor into v_ename,v_sal;
????? exit when emp_cursor%notfound;
????? if v_oldsal<2000 then
??????? update emp set sal=sal+100 where current of emp_cursor;
????? end if;
??? end loop;
??? close emp_cursor;
? end;

?

? 9.6游標(biāo)FOR循環(huán)
? 使用FOR循環(huán)時(shí),ORACLE會(huì)隱含的打開游標(biāo),提取游標(biāo)數(shù)據(jù)并關(guān)閉游標(biāo)
? for record_name in cursor_name loop
????? statement1;
????? statement2;
????? ...
? end loop;
? 每循環(huán)一次提取一次數(shù)據(jù)，在提取了所有數(shù)據(jù)后,自動(dòng)退出循環(huán)并隱含的關(guān)閉游標(biāo)
? 1.使用游標(biāo)FOR循環(huán)?
? declare
??? cursor emp_cursor is select ename,sal from emp;
? begin
??? for emp_record in emp_cursor loop
????? dbms_output.put_line('第'||emp_curosr%rowcount||'個(gè)雇員: '||emp_record.ename);
??? end loop;
? end;

? 2.在游標(biāo)FOR循環(huán)中直接使用子查詢
? begin
??? for emp_record in (select ename,sal from emp) loop
????? dbms_output.put_line(emp_record.ename);
??? end loop;
? end;

? 9.7使用游標(biāo)變量
? PL/SQL的游標(biāo)變量中存放著指向內(nèi)存地址的指針.
?
? 1.游標(biāo)變量使用步驟
??? 包括定義游標(biāo)變量,打開游標(biāo),提取游標(biāo)數(shù)據(jù),關(guān)閉游標(biāo)等四個(gè)階段
? 1.1定義ref cursor類型和游標(biāo)變量
? type ref_type_name is ref cursor [return return_type];
? cursor_varibale ref_type_name;
? 當(dāng)指定RETURN子句時(shí),其數(shù)據(jù)類型必須是記錄類型,不能在包內(nèi)定義游標(biāo)變量
? 1.2打開游標(biāo)
? open cursor_variable for select_statement;
? 1.3提取游標(biāo)數(shù)據(jù)
? fetch cursor_varibale into variable1,variable2,...;
? fetch cursor_varibale bulk collect into collect1,collect2,...[limit rows]
? 1.4關(guān)閉游標(biāo)變量
? close cursor_varibale;

? 2.游標(biāo)變量使用示例
? 2.1在定義FEF CURSOR類型時(shí)不指定RETURN子句
? 在打開游標(biāo)時(shí)可以指定任何的SELECT語句
? declare
??? type emp_cursor_type is ref cursor;
??? emp_cursor emp_cursor_type;
??? emp_record emp%rowtype;
? begin
??? open emp_cursor for select * from emp where deptno=10;
??? loop
????? fetch emp_cursor into emp_record;
????? exit when emp_cursor%notfound;
????? dbms_output.put_line('第'||emp_curosr%rowcount||'個(gè)雇員: '||emp_record.ename);
??? end loop;
??? close emp_cursor;
? end;

? 2.2在定義REF CURSOR類型時(shí)指定RETURN子句
? 在打開游標(biāo)時(shí)SELECT語句的返回結(jié)果必須與RETURN子句所指定的記錄類型相匹配.
? declare
??? type emp_record_type is record(name varchar2(10),salary number(6,2));
??? type emp_cursor_type is ref cursor return emp_record_type;
??? emp_cursor emp_cursor_type;
??? emp_record emp_record_type;
? begin
??? open emp_cursor for select ename,sal from emp where deptno=20;
??? loop
????? fetch emp_cursor into emp_record;
????? exit when emp_cursor%notfound;
????? dbms_output.put_line('第'||emp_curosr%rowcount||'個(gè)雇員: '||emp_record.ename);
??? end loop;
??? close emp_cursor;
? end;

?

? 9.7使用CURSOR表達(dá)式
? CURSOR表達(dá)式用于返回嵌套游標(biāo)
? 結(jié)果集不僅可以包含普通數(shù)據(jù),而且允許包含嵌套游標(biāo)的數(shù)據(jù)
? cursor(subquery)

? declare
??? type refcursor is ref cursor;
??? cursor dept_cursor(no number) is select a.dname,cursor(select ename,sal from emp where deptno=a.deptno)
??? from dept a where a.deptno=no;
??? empcur refcursor;
??? v_dname dept.dname%type;
??? v_ename emp.ename%type;
??? v_sal emp.sal%type;
? begin
??? open dept_cursor(&no);
??? loop
????? fetch dept_cursor into v_danme,empcur;
????? exit when dept_cursor%notfound;
????? dbms_output.put_line('部門名:'||v_dname);
????? loop
??????? fetch empcur into v_ename,v_sal;
??????? exit when empcur%notfound;
??????? dbms_output.put_line('雇員名:'||v_ename||',工資:'||v_sal);
????? end loop;
??? end loop;
??? close dept_cursor;
? end;
?
?

???
? 10開發(fā)子程序:過程和函數(shù)
? 過程:執(zhí)行特定操作
? 函數(shù):用于返回特定數(shù)據(jù)
??
? 10.1過程
? 語法:create [or replace] procedure procedure_name(argument1 [model] datatype1,arguement2 [mode2],...)
?????? is [as]
?????? pl/sql block;

? 1.建立過程:不帶任何參數(shù)
? create or replace procecdure out_time
? is
? begin
??? dbms_output.put_line(systimestemp);
? end;
? 2.調(diào)用過程
? set serveroutput on
? exec out_time

? set serveroutput on
? call out_time();

? 3.建立過程:帶有IN參數(shù)
? create or replace procedure add_employee
? (eno number,name varchar2,sal number,job varchar2 default 'clerk',dno number)
? is
? e_integrity exception;
? pragma exception_init(e_integrity,-2291);
? begin
??? insert into imp(empno,ename,sal,job,deptno) valres(eno,name,sal,job,dno);
? exception
??? when dup_val_on_index then
????? raise_application_error(-20000,'雇員號(hào)不能重復(fù)');
??? when e_integrity then
????? raise_application_error(-20001,'部門不存在');
? end;

? exec add_employee(1111,'clark',2000,'manager',10)

? 4.建立過程:帶有OUT參數(shù)
? create or replace procedure query_employee
? (eno number,name out varchar2,salary out number)
? is
? begin
??? select ename,sal into name,salary from emp where empno=eno;
? exception
??? when no_date_found then
????? raise_application_error(-20000,'該雇員不存在');
? end;
? 當(dāng)在應(yīng)用程序中調(diào)用該過程時(shí),必須要定義變量接受輸出參數(shù)的數(shù)據(jù)
? sql>var name varchar2(10)
????? var salary number
????? exec query_employee(7788,:name,:salary)
????? print name salary
?
?
? 5.建立過程:帶有IN OUT參數(shù)(輸入輸出參數(shù))
? create or replace procedure compute
? (num1 in out number,num2 in out number)
? is
??? v1 number;
??? v2 number;
? begin
??? v1:num1/num2;
??? v2:mod(num1,num2);
??? num1:=v1;
??? num2:=v2;
? end;

? sql>var n1 number
????? var n2 number
????? exec :n1:=100
????? exec :n2:=30
????? exec ecmpute(:n1,:n2)
????? print n1 n2

? 6.為參數(shù)傳遞變量和數(shù)據(jù)
??? 位置傳遞,名稱傳遞,組合傳遞三種
??? 1.位置傳遞:在調(diào)用子程序時(shí)按照參數(shù)定義的順序?yàn)閰?shù)指定相應(yīng)的變量或數(shù)值
????? exec add_dept(40,'sales','new york');
????? exec add_dept(10);
??? 2.名稱傳遞:在調(diào)用子程序時(shí)指定參數(shù)名,并使用關(guān)聯(lián)符號(hào)=>為其提供相應(yīng)的數(shù)值或變量
????? exec add_dept(dname=>'sales',dno=>50);
????? exec add_dept(dno=>30);
??? 3.組合傳遞:同時(shí)使用位置傳遞和名稱傳遞
????? exec add_dept(50,loc=>'new york');
????? exec add_dept(60,dname=>'sales',loc=>'new york');

? 7.查看過程原代碼
??? oracle會(huì)將過程名,源代碼以及其執(zhí)行代碼存放到數(shù)據(jù)字典中.執(zhí)行時(shí)直接按照其執(zhí)行代碼執(zhí)行
??? 可查詢數(shù)據(jù)字典(user_source)
??? select text from user_source where name='add_dept';
?
??? 刪除過程
??? drop procedure add_dept;

??

? 10.2函數(shù)
? 用于返回特定函數(shù)
? 語法:create [or replace] function function_name
????????????? (argument1 [mode1] datatype1,
?????????????? argument2 [mode2] datatype2,
?????????????? .....)
?????? return datatype?????? --函數(shù)頭部必須要帶有RETURN子句,至少要包含一條RETURN語句
?????? is|as? pl/sql block;
??
? 1.建立函數(shù):比帶任何參數(shù)
? create or replace function get_user
? return varchar2
? is
??? v_user varchar2(100);
? begin
??? select username into v_user from user_users;
??? return v_user;
? end;

? 2.使用變量接受函數(shù)返回值
??? sql>var v1 varchar2(100)
??????? exec :v1:=get_user
??????? print v1
??? 在SQL語句中直接調(diào)用函數(shù)
??? select get_user from dual;
??? 使用DBMS_OUTPUT調(diào)用函數(shù)
??? set serveroutput on
??? exec dbms_output.put_line('當(dāng)前數(shù)據(jù)庫用戶:'||ger_user)
?
? 3.建立函數(shù):帶有IN參數(shù)
??? create or replace function get_sal(name in varchar2)
??? return number
??? as
????? v_sal emp.sal%type;
??? begin
????? select sal into v_sal from emp where upper(ename)=upper(name);
????? return v_sal;
??? exception
????? when no_data_found then
??????? raise_application_error(-20000,'該雇員不存在');
??? end;

? 4.建立函數(shù):帶有out參數(shù)
? create or replace function get_info(name varchar2,title out varchar2)
? return varchar2
? as
??? deptname dept.dname%type;
? begin
??? select a.job,b.dname into title,deptname from emp a,dept b and a.deptno=b.deptno
??? and upper(a.ename)=upper(name);
??? return deptname
? exception
??? when no_data_found then
????? raise_application_error(-20000,'該雇員不存在');
? end;

? sql>var job varchar2(20)
????? var dname varchar2(20)
????? exec :dname:=get_info('scott',:job)
????? print danme job

? 5.建立函數(shù):帶有IN OUT參數(shù)
? create or replace function result(num1 number,num2 in out number)
? return number
? as
??? v_result number(6);
??? v_remainder number;
? begin
??? v_result:=num1/num2;
??? v_remainder:=mod(num1,num2);
??? num2:=v_remainder;
??? return v_result;
? exception
??? when zero_divide then
????? raise_application_error(-20000,'不能除0');
? end;

? sql>var result1 number
????? var result2 number
????? exec :result2:=30
????? exec :result1:=result(100,:result2)
????? print result result2

? 6.函數(shù)調(diào)用限制
? SQL語句中只能調(diào)用存儲(chǔ)函數(shù)(服務(wù)器端),而不能調(diào)用客戶端的函數(shù)
? SQL只能調(diào)用帶有輸入?yún)?shù),不能帶有輸出,輸入輸出函數(shù)
? SQL不能使用PL/SQL的特有數(shù)據(jù)類型(boolean,table,record等)
? SQL語句中調(diào)用的函數(shù)不能包含INSERT,UPDATE和DELETE語句

? 7.查看函數(shù)院源代碼
? oracle會(huì)將函數(shù)名及其源代碼信息存放到數(shù)據(jù)字典中user_source
? set pagesize 40
? select text from user_source where name='result';
? 8.刪除函數(shù)
? drop function result;
?
?

? 10.3管理子程序
? 1.列出當(dāng)前用戶的子程序
? 數(shù)據(jù)字典視圖USER_OBJECTS用于顯示當(dāng)前用戶所包含的所有對(duì)象.(表,視圖,索引,過程,函數(shù),包)
? sql>col object_name format a20
????? select object_name,created,status from user_objects where object_type in ('procedure','function')
?????
? 2.列出子程序源代碼
? select text from user_source where name='raise_salsry';

? 3.列出子程序編譯錯(cuò)誤
? 使用SHOW ERRORS命令確定錯(cuò)誤原因和位置
? show errors procedure raise_salary
?
? 使用數(shù)據(jù)字典視圖USER_ERRORS確定錯(cuò)誤原因和位置
? col text format a50
? select line||'/'||position as "line/col",text error from user_errors where name='raise_salary';

? 4.列出對(duì)象依賴關(guān)系
? 使用數(shù)據(jù)字典視圖USER_DEPENDENCIES確定直接依賴關(guān)系
? select name,type from user_dependencies where referenced_name='emp';

? 使用工具視圖DEPTREE和IDEPTREE確定直接依賴和間接依賴關(guān)系
? 先運(yùn)行SQL腳本UTLDTREE.SQL來建立這兩個(gè)視圖和過程DEPTREE_FILL,然后調(diào)用DEPTREE_FILL填充這兩個(gè)視圖
? sql>@%oracle_home%\rdbms\admin\utldtree
????? exec deptree_fill('TABLE','scott','emp')
? 執(zhí)行后會(huì)將直接或間接依賴于SCOTT.EMP表的所有對(duì)象填充到視圖DEPTREE和IDEPTREE中.
????? select nested_level,name,type from deptree;
????? select * from ideptree

? 5.重新編譯子程序
? 當(dāng)修改了被引用對(duì)象的結(jié)構(gòu)時(shí),就會(huì)將相關(guān)依賴對(duì)象轉(zhuǎn)變?yōu)闊o效(INVALID)狀態(tài)。
? alter table emp add remark varchar2(10);
? select object_name,object_type from user_objects where status='invalid';
? 為了避免子程序的運(yùn)行錯(cuò)誤,應(yīng)該重新編譯這些存儲(chǔ)對(duì)象
? alter procedure add_employee compile;
? alter view dept10 compile;
? alter function get_info compile;

?

? 11.開發(fā)包
? 包用于邏輯組合相關(guān)的PL/SQL類型,項(xiàng)和子程序,由包規(guī)范和包體組成
? 1.建立包規(guī)范:包規(guī)范是包與應(yīng)用程序之間的接口,用于定義包的公用組件,包括常量,變量,游標(biāo),過程,函數(shù)等
? create [or replace] package package_name
? is|as
???? public type and item declarations
???? subprogram specificationsend package_name;

? create or replace package emp_package is
??? g_deptno number(3):=30;
??? procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno);
??? procedure fire_employee(eno number);
??? function get_sal(eno number) return number;
? end emp_package;

? 2.建立包體:用于實(shí)現(xiàn)包規(guī)范所定義的過程和函數(shù)
? create [or replace] package body package_name
? is|as
???? private type and item declarations
???? subprogram bodies
? end package_name;

? create or repalce package body emp_package is

??? function validate_deptno(v_deptno number)
????? return boolean
??? is
????? v_temp int;
??? begin
????? select 1 into v_temp from dept where deptno=v_deptno;
????? return true;
??? exception
????? when no_date_found then
??????? return false;
??? end;

??? procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
??? is
??? begin
????? if validate_deptno(dno) then
???????? insert into emp(empno,ename,sal,deptno) values(eno,name,salsry,dno);
????? else
???????? raise_application_error(-20010,'不存在該部門');
????? end if;
??? exception
????? when dup_val_on_index then
???????? raise_application_error(-20012,'該雇員已存在');
??? end;

??? procedure fire_employee(eno number) is
??? begin
????? delete from emp where empno=eno;
????? if sql%notfound then
???????? raise_application_error(-20012,'該雇員不存在');
????? end if;
??? end;
???
??? function get_sal(eno number) return number
??? is
????? v_sal emp.sal%type;
??? begin
????? select sal into v_sal from emp where empno=eno;
????? return v_sal;
??? exception
????? when no_data_found then
??????? raise_application_error(-20012,'該雇員不存在');
??? end;
? end emp_package;

? 3.調(diào)用包組件
? 3.1在同一個(gè)包內(nèi)調(diào)用包組件
? create or replace package body emp_package is
?
? procedure add_employee(eno number,name vauchar2,salary number,dno number default g_deptno)
? is
? begin
??? if validate_deptno(dno) then
?????? insert into emp(empno,ename,sal,deptno) values(eno,name,salary,dno);
??? else
?????? raise_application_error(-20010,'該部門不存在')
??? end if;
??? exception
????? when dup_val_on_index then
?????? raise_application_error(-20011,'該雇員已存在')
??? end;
??? .........

? 3.2調(diào)用包公用變量
? exec emp_package.g_deptno:=20
? 3.3調(diào)用包公用過程
? exec emp_package.add_employee(1111,'mary',2000)
? 3.4調(diào)用包公用函數(shù)
? var salary number
? exec :salary:=emp_package.get_sal(7788)
? print salary
? 3.5以其他用戶身份調(diào)用包公用組件
? conn system/manager
? exec scott.emp_package.add_employee(1115,'scott',1200)
? exec scott.emp_package.fire_employee(1115)
? 3.6調(diào)用遠(yuǎn)程數(shù)據(jù)庫包的公用組件
? exec emp_package.add_employee@orasrv(1116,'scott',1200 )
?
? 4.查看源代碼:存放在數(shù)據(jù)字典USER_SCOURCE中
? select text from user_source where name='emp-package' and type='package';
? 5.刪除包
? drop package emp_package;
?
?
? 6.使用包重載
? 重載(overload)是指多個(gè)具有相同名稱的子程序
? 1.建立包規(guī)范??
? 同名的過程和函數(shù)必須具有不同的輸入?yún)?shù),同名函數(shù)返回值的數(shù)據(jù)類型必須完全相同
? create or replace package overload is
??? function get_sal(eno number) return number;
??? function get_sal(name varchar2) return number;
??? procedure file_employee(eno number);
??? procedure file_employee(name varchar2);
? end;

? 2.建立包體
? 必須要給不同的重載過程和重載函數(shù)提供不同的實(shí)現(xiàn)代碼
? create or replace package body overload is
??
??? function get_sal(eno number) return number
??? is
????? v_sal emp.sal%type;
??? begin
????? select sal into v_sal from emp where empno=eno;
????? return v_sal;
??? exception
????? when no_data_found then
??????? raise_application_error(-20020,'該雇員不存在');
??? end;

??? function get_sal(name varchar2) return? number
??? is
????? v_sal emp.sal%type;
??? begin
????? select sal into v_sal from emp where upper(ename)=upper(name);
????? return v_sal;
??? exception
????? when no_data_found then
??????? raise_application_error(-20020,'該雇員不存在');
??? end;

??? procedure fire_employee(eno number) is
??? begin
????? delete from emp where empno=no;
????? if sql%notfound then
??????? raise_application_error(-20020,'該雇員不存在');
????? end if;
??? end;

??? procedure fire_employee(name varchar2) is
??? begin
????? delete from emp where upper(ename)=upper(name);
????? if sql%notfound then
??????? raise_application_error(-20020,'該雇員不存在');
????? end if;
??? end;
? end;

? 3.調(diào)用重載過程和重載函數(shù)
? var sal1 number
? var sal2 number
? exec :sal1:=overload.get_sal('scott')
? exec :sal2:=overload.get_sal(7685)
? exec overload.fire_employee(7369)
? exec overload.fire_employee('scott')

? 7.使用包構(gòu)造過程
? 類似于高級(jí)語言中的構(gòu)造函數(shù)和構(gòu)造方法
? 1.建立包規(guī)范
? 包的構(gòu)造過程用于初始化包的全局變量.
? create or replace package emp_package is
??? minsal number(6,2);
??? maxsal number(6,2);
??? procedure add_employee(eno number,name varchar2,salary number,dno number);
??? procedure upd_sal(eno number,salary number);
??? procedure upd_sal(name varchar2,salary number);
? end;
?
? 2.建立包體
? 包的構(gòu)造過程沒有任何名稱,它是實(shí)現(xiàn)了包的其他過程后,以BEGIN開始,END結(jié)束的部分
? create or replace package body emp_package is
???
??? procedure add_employee(eno number,name varchar2,salary number,dno number)
??? is
??? begin
??? if salary between minsal and maxsal then
????? insert into emp (empno,ename,sal,deptno) values(eno,name,salary,dno);
??? else
????? raise_application_error(-20001,'工資不在范圍內(nèi)');
??? end if;
??? exception
????? when dup_val_on_index then
??????? raise_application_error(-20002,'該雇員已經(jīng)存在');
??? end;
?
??? procedure upd_sal(eno number,salary number) is
??? begin
????? if salary between minsal and maxsal then
???????? update emp set sal=salary where empno =eno;
???????? if sql%notfound then
?????????? raise_application_error(-20003,'不存在雇員號(hào)');
???????? end if;
????? else
???????? raise_application_errpr(-20001,'工資不在范圍內(nèi)');
????? end if;
?? end;
?? procedure upd_sal(name varchar2,salary number) is
?? begin
???? if salary between minsal and maxsal then
??????? update emp set sal=salary where upper(ename)=upper(name);
??????? if sql%notfound then
??????????? raise_application_error(-20004,'不存在該雇員名');
??????? end if;
???? else
??????? raise_application_error(-20001,'工資不在范圍內(nèi)');
???? end if;
?? end;
? begin
??? select mi(sal),max(sal) into minsal,maxsal from emp ;
? end;

? 調(diào)用包公用組件:構(gòu)造過程只調(diào)用一次
? exec emp_package.add_employee(1111,'mary',3000,20)
? exec emp_package.upd_sal('mary',2000)
?

? 8.使用純度級(jí)別
? 在SQL中引用包的公用函數(shù)，該公用函數(shù)不能包含DML語句(insert,update,delete),也不能讀寫遠(yuǎn)程包的變量
? 為了對(duì)包的公用函數(shù)加以限制,在定義包規(guī)范時(shí),可以使用純度級(jí)別(purity level)限制公用函數(shù)
? 語法:pragma restrict_references (function_name,wnds[,wnps][,rnds][,rnps]);
? wnds:用于限制函數(shù)不能修改數(shù)據(jù)庫數(shù)據(jù)(禁止DML)
? wnps:用于限制函數(shù)不能修改包變量(不能給包變量賦值)
? rnds:用于限制函數(shù)不能讀取數(shù)據(jù)庫數(shù)據(jù)(禁止SELECT操作)
? rnps:用于限制函數(shù)不能讀取包變量(不能將包變量賦值給其他變量)

? 1.建立包規(guī)范
? create or replace package purity is
??? minsal number(6,2);
??? maxsal number(6,2);
??? function max_sal return number;
??? function min_sal return number;
??? pragma restrict_references(max_sal,wnps);--不能修改
??? pragma restrict_references(min_sal,wnps);
? end;
?
? 2.建立包體
? create or replace package body purity is
?? function max_sal return number
?? is
?? begin
???? return maxsal;
?? end;
??
?? function min_sal return number
?? is
?? begin
???? return minsal;
?? end;
??
?? begin
???? select min(sal),max(sal) into minsal,maxsal from emp;
?? end;

?? 3.調(diào)用包的公用函數(shù)
?? var minsal number
?? var maxsal number
?? exec :minsal:=purity.minsal()
?? exec :maxsal:=purity.maxsal()
?? print minsal maxsal

??

? 12.觸發(fā)器
? 存放在數(shù)據(jù)庫中，并被隱含執(zhí)行的存儲(chǔ)過程.
? 可基于表和視圖的DML(INSERT,UPDATE,DELETE),系統(tǒng)事件(啟動(dòng),關(guān)閉,登陸數(shù)據(jù)庫)和DDL操作建立觸發(fā)器.
? 觸發(fā)器由觸發(fā)事件,觸發(fā)條件,觸發(fā)操作三部分組成
? 1.1觸發(fā)事件
? 啟動(dòng)和關(guān)閉數(shù)據(jù)庫,ORACLE錯(cuò)誤消息,用戶登錄和斷開會(huì)話,特定表或視圖的DML操作,在任何方案上的DDL語句
?
? 1.2.觸發(fā)條件(可選)
? 指使用WHEN子句指定一個(gè)BOOLEAN表達(dá)式,返回為TRUE時(shí),觸發(fā)
?
? 1.3.觸發(fā)操作
? 指包含SQL語句和其他執(zhí)行代碼的PL/SQL塊.
? 觸發(fā)器的代碼大小不能超過32K.(可使用CALL語句調(diào)用存儲(chǔ)過程)
? 出發(fā)器只能包含SELECT,INSERT,UPDATE,DELETE語句,不能包含DDL語句(CREATE,ALTER,DROP)和事務(wù)控制語句(COMMIT,ROLLBACK,SAVEPOINT)

?
? 2.建立DML觸發(fā)器
? 當(dāng)建立DML觸發(fā)器時(shí),需要指定觸發(fā)時(shí)機(jī)(BEFORE,AFTER),觸發(fā)事件(INSERT,UPDATE,DELETE),表名,觸發(fā)類型,觸發(fā)條件,操作.
? 2.1觸發(fā)時(shí)機(jī)
? 指定觸發(fā)器的觸發(fā)時(shí)間,之前(BEFORE),之后(AFTER)
? 2.2觸發(fā)事件
? 指定導(dǎo)致觸發(fā)器執(zhí)行的DML操作,也即INSERT,UPDATE,DELTE操作
? 2.3表名
? 必須指定DML操作所對(duì)應(yīng)的表
? 2.4觸發(fā)類型
? 指定觸發(fā)事件發(fā)生后,需要執(zhí)行幾次觸發(fā)操作,默認(rèn)執(zhí)行一次觸發(fā)器代碼
? 如果指定行觸發(fā)類型,則會(huì)在每個(gè)被作用行上執(zhí)行一次觸發(fā)器代碼
? 2.5觸發(fā)條件
? 指定執(zhí)行觸發(fā)器代碼的條件
? 當(dāng)編寫DML觸發(fā)器時(shí),只允許在行觸發(fā)器上指定觸發(fā)條件
? 2.6觸發(fā)操作
? 指定觸發(fā)器執(zhí)行代碼.(若使用PL/SQL,JAVA,或外部存儲(chǔ)過程,可直接使用CALL調(diào)用相應(yīng)過程)???
? 2.7DML觸發(fā)器觸發(fā)順序
? DML觸發(fā)器在單行數(shù)據(jù)上的觸發(fā)順序
? 對(duì)于單行數(shù)據(jù)而言,無論是語句觸發(fā)器,還是行觸發(fā)器,觸發(fā)器代碼實(shí)際只被執(zhí)行一次,順序?yàn)?
? before語句,before行,dml操作,after行,after語句
?
? DML觸發(fā)器在多行數(shù)據(jù)上的觸發(fā)順序
? 對(duì)于多行數(shù)據(jù)而言,語句觸發(fā)器只被執(zhí)行一次,而行觸發(fā)器在每個(gè)作用行上都執(zhí)行一次
?

? 3.語句觸發(fā)器
? 當(dāng)執(zhí)行DML操作時(shí)會(huì)自動(dòng)執(zhí)行觸發(fā)器的相應(yīng)代碼.
? 使用語句觸發(fā)器時(shí),不能記錄列數(shù)據(jù)的變化
? 語法:create [or replace] trigger trigger_name
?????? timing event1 [or event2 or event3]
?????? on table_name
?????? pl/sql block
? 3.1建立BEFORE語句觸發(fā)器
? create or replace trigger tr_sec_emp
? before insert or update or delete on emp
? begin
??? if to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('SAT','SUN') THEN
?????? raise_application_error(-20001,'不能在休息日改變雇員信息');
??? end if;
? end;

? 3.2使用條件謂詞:inserting,updating,deleting是該操作時(shí)返回TRUE,否則FALSE
? create or replace trigger tr_sec_emp
? before insert or update or delete on emp
? begin
??? if to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('SAT','SUN') THEN
????? CASE
??????? WHEN INSERTING THEN
????????? raise_application_error(-20001,'不能在休息日增加雇員');
??????? WHEN UPDATING THEN
????????? raise_application_error(-20002,'不能在休息日更新雇員');
??????? WHEN DELETING THEN
????????? raise_application_error(-20003,'不能在休息日解雇雇員');
????? end case;
??? end if;
? end;

?
? 4.建立AFTER語句觸發(fā)器
? 為了審計(jì)DML操作,或者在DML操作之后執(zhí)行匯總運(yùn)算.(計(jì)算INSERT,UPDATE,DELETE的操作次數(shù))
? create table audit_table(name varchar2(20),ins int,upd int,del int,starttime date,endtime date);
?
? create or replace trigger tr_audit_emp
? after insert or update or delete on emp
? declare
??? v_temp int;
? begin
??? select count(*) into v_temp from audit_table where name='emp';
??? if v_temp=0 then
?????? insert into audit_table values('emp',0,0,0,sysdate,null);
??? end if;
??? case
????? when inserting then update audit_table set ins=ins+1,endtime=sysdate where name='emp';
????? when updating then update audit_table set upd=upd+1,endtime=sysdate where name='emp';
????? when deleting then update audit_table set del=del+1,endtime=sysdate where name='emp';
??? end case;
? end;

? 5.行觸發(fā)器
? 執(zhí)行DML操作時(shí),每作用一行就觸發(fā)一次的觸發(fā)器.審計(jì)數(shù)據(jù)變化時(shí),可以使用行觸發(fā)器
? create [or replace] trigger trigger_name
? timing event1 [or event2 or event3]
? on table_name
? [referencing old as old |new as new]
? for each row
? [when condition]
? pl/sql block;

? 5.1建立BEFORE行觸發(fā)器
? create or relpace trigger tr_emp_sal
? before update of sal on emp
? for each row
? begin
??? if :new.sal<:old.sal then
?????? raise_application_error(-20010,'工資只漲不降');
??? end if ;
? end;

? 5.2建立AFTER行觸發(fā)器
? 為了審計(jì)DML操作數(shù)據(jù)變化，則應(yīng)該使用AFTER行觸發(fā)器
? create table audit_emp_change(name varchar2(10),oldsal number(6,2),newsal number(6,2),time date);
?
? create or replace trigger tr_sal_change
? after update of sal on emp
? for each row
? declare
??? v_temp int;
? begin
??? select count(*) into v_temp from audit_emp_change where name=:old.ename;
????? if v_temp=0 then
???????? insert into audit_emp_change values(:old.ename,:old.sal,:new,sal,sysdate);
????? else
???????? update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
????? end if;
? end;

? 5.3限制行觸發(fā)器
? 使用行觸發(fā)器時(shí),默認(rèn)情況下會(huì)在每個(gè)被作用行上執(zhí)行一次觸發(fā)器代碼,為了使得在特定條件下執(zhí)行行觸發(fā)器代碼,就需要使用WHEN子句對(duì)觸發(fā)器加以限制
? create or replace trigger tr_sal_change
? after update of sal on emp
? for each row
? when (old.job='salesman')
? declare
??? v_temp int;
? begin
??? select count(*) into v_temp from audit_emp_change where name=:old.ename;
??? if v_temp=0 then
?????? insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
??? else
?????? update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
??? end if;
? end;

? 5.4DML觸發(fā)器使用注意事項(xiàng)
? 編寫DML觸發(fā)器時(shí),不能從觸發(fā)器所對(duì)應(yīng)的基表中讀取數(shù)據(jù).建立時(shí)不會(huì)出現(xiàn)錯(cuò)誤,但在執(zhí)行相應(yīng)觸發(fā)操作時(shí)會(huì)顯示錯(cuò)誤信息
?
? 6.使用DML觸發(fā)器
? DML觸發(fā)器可以用于實(shí)現(xiàn)數(shù)據(jù)安全保護(hù),數(shù)據(jù)審計(jì),數(shù)據(jù)完整性,參照完整性,數(shù)據(jù)復(fù)制等
? 6.1控制數(shù)據(jù)安全
? 在服務(wù)器級(jí)控制數(shù)據(jù)安全是通過授予和收回對(duì)象權(quán)限來完成的.
? conn scott/tiger
? grant select,insert,update,delete on emp to smith;

? create or replace trigger tr_emp_time
? before insert or update or delete on emp
? begin
??? if to_char(sysdate,'hh24') not between '9' and '17' then
?????? raise_application_error(-20101,'非工作時(shí)間');
??? end if;
? end;

? 6.2實(shí)現(xiàn)數(shù)據(jù)審計(jì)
? 審計(jì)可以用于監(jiān)視非法和可疑的數(shù)據(jù)庫活動(dòng).ORACLE數(shù)據(jù)庫本身提供了審計(jì)功能
? 對(duì)EMP表上的DML操作進(jìn)行審計(jì)
? audit insert,update,delete on emp by access;
? 只能審計(jì)SQL操作,寫入到數(shù)據(jù)字典中,而不會(huì)記載數(shù)據(jù)變化.為了審計(jì)SQL操作引起的數(shù)據(jù)變化,使用DML觸發(fā)器
? create or replace trigger tr_sal_change
? after update of sal on emp
? for each row
? declare
??? v_temp int;
? begin
??? select count(*) into v_temp from audit_emp_change where name=:old.ename;
??? if v_temp=0 then
?????? insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
??? else
?????? update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
??? end if;
? end;

? 6.3實(shí)現(xiàn)數(shù)據(jù)完整性
? 數(shù)據(jù)完整性用于確保數(shù)據(jù)庫數(shù)據(jù)滿足特定的商業(yè)邏輯或企業(yè)規(guī)則.
? CHECK約束:alter table emp add constraint ck_sal check (sal>=800);
? create or replace trigger tr_check_sal
? before update of sal on emp
? for each row
? when (new.sal<old.sal or new.sal>1.2*old.sal)
? begin
??? raise_application_error(-20931,'工資只升不降,并且升幅不能超過20%');
? end;

? 6.4實(shí)現(xiàn)參照完整性
? 指若兩個(gè)表之間具有主從關(guān)系(即主外鍵關(guān)系),當(dāng)刪除主表數(shù)據(jù)時(shí),必須確保相關(guān)的從表數(shù)據(jù)已經(jīng)被刪除.
? 當(dāng)修改主表的主鍵列數(shù)據(jù)時(shí),必須確保相關(guān)從表數(shù)據(jù)已經(jīng)被修改.為了實(shí)現(xiàn)級(jí)聯(lián)刪除，可在定義外部鍵約束時(shí)指定ON DELETE CASCADE關(guān)鍵字
? alter table emp add constraint fk_deptno foreign key (deptno) references dept(deptno) on delete cascade;

? 實(shí)現(xiàn)級(jí)聯(lián)更新,可以使用觸發(fā)器
? create or replace trigger tr_update_cascade
? after update of deptno on dept
? for each row
? begin
??? update emp set deptno=:new.deptno where deptno=:old.deptno;
? end;

? 7.建立INSTEAD OF觸發(fā)器
? 對(duì)于簡單視圖可直接執(zhí)行INSERT,UPDATE,DELETE操作,但對(duì)于復(fù)雜視圖,不允許直接執(zhí)行DML操作
? 具有集合操作符:UNION,UNION ALL,INTERSECT,MINUS
? 具有分組函數(shù):MIN,MAX,SUM,AVG,COUNT等
? 具有GROUP BY ,CONNECT BY ,START WITH等子句
? 具有DISTINCT關(guān)鍵字
? 具有連接查詢
? 為了在具有以上情況的復(fù)雜視圖上執(zhí)行DML操作，必須要基于視圖建立INSTEAD-OF觸發(fā)器
? 在建立了INSTEAD-OF觸發(fā)器之后,就可以基于復(fù)雜視圖執(zhí)行INSERT,UPDATE,DELETE語句
? 注意事項(xiàng):
????? INSTEAD OF選項(xiàng)只適用于視圖
????? 當(dāng)基于視圖建立觸發(fā)器時(shí),不能指定BEFORE和AFTER選項(xiàng)
????? 在建立視圖時(shí)沒有指定WITH CHECK OPTION選項(xiàng)
????? 當(dāng)建立INSTEAD OF觸發(fā)器時(shí),必須指定FOR EACH ROW選項(xiàng)
?
? 7.1建立復(fù)雜DEPT_EMP
? create or replace view dept_emp as select a.deptno,a.dname,b.empno,b.ename from dept a,emp b where a.deptno=b.deptno;
? 不允許執(zhí)行DML操作
?
? 7.2建立INSTEAD-OF觸發(fā)器
? 為了在復(fù)雜視圖上執(zhí)行DML操作,必須要基于復(fù)雜視圖來建立INSTEAD-OF觸發(fā)器
? create or replace trigger tr_instead_of_dept_emp
? instead of insert on dept_emp
? for each row
? declare
??? v_temp int;
? begin
??? select count(*) into v_temp from dept where deptno=:new.deptno;
??? if v_temp=0 then
?????? insert into dept (deptno,dname) values(:new.deptno,:new.dname);
??? end if;
??? select count(*) into v_temp from emp where empno=:new.empno;
??? if v_temp=0 then
?????? insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);
??? end if;
? end;
? 當(dāng)建立INSTEAD-OF觸發(fā)器之后,就可以在復(fù)雜視圖DEPT_EMP上執(zhí)行INSERT操作了
? insert into dept_emp values(50,'admin','1223','mary');
? insert into dept_emp values(10,'admin','1224','bake');

?

? 8.建立系統(tǒng)事件觸發(fā)器
? 基于ORACLE系統(tǒng)事件(logon,startup)所建立的觸發(fā)器,跟蹤系統(tǒng)或數(shù)據(jù)庫變化的機(jī)制
?
? 8.1常用事件屬性函數(shù)
? ora_client_ip_address:返回客戶端IP地址
? ora_database_name:返回當(dāng)前數(shù)據(jù)庫名
? ora_des_encrypted_password:用于返回DES加密后的用戶口令
? ora_dict_obj_name:用于返回DDL操作所對(duì)應(yīng)的數(shù)據(jù)庫對(duì)象名
? ora_dict_obj_list(name_list out ora_name_list_t):返回在事件中被修改的對(duì)象名列表
? ora_dict_obj_owner:返回DDL操作所對(duì)應(yīng)的對(duì)象的所有者名
? ora_dict_obj_owner_list(owner_list out ora_name_list_t):返回在事件中被修改對(duì)象的所有者列表
? ora_dict_obj_type:返回DDL操作所對(duì)應(yīng)的數(shù)據(jù)庫對(duì)象的類型
? ora_grantee(user_list out ora_name_list_t):返回授權(quán)事件的授權(quán)者
? ora_instance_num:返回例程號(hào)
? ora_is_alter_column(column_name in varchar2):用于檢測(cè)特定列是否被修改
? ora_is_creating_nested_table:用于檢測(cè)是否正在建立嵌套表
? ora_is_drop_column(column_name in varchar2):用于檢測(cè)特定列是否被刪除
? ora_is_servererror(error_number):用于檢測(cè)是否返回特定oracle錯(cuò)誤
? ora_login_user:返回登錄用戶名
? ora_sysevent:用于返回觸發(fā)觸發(fā)器的系統(tǒng)事件名

? 8.2建立例程啟動(dòng)和關(guān)閉觸發(fā)器(特權(quán)用戶)
? 記載例程啟動(dòng)和關(guān)閉的事件和時(shí)間
? conn sys/oracle as sysdba
? create table event_table(event varchar2(30),time date);
?
? create or replace trigger tr_startup
? after startup on database
? begin
??? insert into event_table values(ora_sysevent,sysdate);
? end;

? create or replace trigger tr_shutdown
? before shutdown on database
? begin
??? insert into event_table values(ora_sysevent,sysdate);
? end;

? shutdown
? shutup
?
? 8.3建立登錄和退出觸發(fā)器
? 記載用戶登陸和退出事件.
? conn sys/oracle as sysdba
? create table log_table(username varchar2(20),logon_time date,logoff_time date,address varchar2(20));
?
? create or replace trigger tr_logon
? after logon on database
? begin
??? insert into log_table (username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
? end;
?
? create or replace trigger tr_logoff
? before logoff on database
? begin
??? insert into log_table (username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
? end;
?
? conn scott/tiger@orc1
? conn system/manager@orc1
? conn sys/oracle@orc1 as sysdba
?
? 8.4建立DDL觸發(fā)器?
? 記載系統(tǒng)所發(fā)生的DDL事件(CREATE,ALTER,DROP等)
? conn sys/oracle as sysdba
? create table event_ddl(event varchar2(20),username varchar2(10),owner varchar2(10),objname varchar2(20),objtype varchar2(10),time date);

? create or replace trigger tr_ddl
? after ddl on scott.schema
? begin
??? insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
? end;

? conn scott/tiger
? create table temp(cola int);
? drop table temp
?
? 9.管理觸發(fā)器
? 9.1顯示觸發(fā)器信息:在數(shù)據(jù)字典中USER_TRIGGERS
? conn scott/tiger
? select trigger_name,status from user_triggers where table_name='emp';
?
? 9.2禁止觸發(fā)器
? 使觸發(fā)器臨時(shí)失效,處于ENABLE狀態(tài),則會(huì)觸發(fā)
? alter trigger tr_check_sal disable;

? 9.3激活觸發(fā)器
? 使觸發(fā)器重新生效
? alter trigger tr_check_sal enable;

? 9.4禁止或激活表的所有觸發(fā)器
? alter table emp disable all triggers
? alter table emp enable all triggers
?
? 9.5重新編譯觸發(fā)器
? 使用ALTER TABLE命令修改表結(jié)構(gòu)時(shí),會(huì)使得觸發(fā)器轉(zhuǎn)變?yōu)镮NVALID狀態(tài),需要重新編譯觸發(fā)器,才能繼續(xù)生效
? alter trigger tr_check_sal compile;
?
? 9.6刪除觸發(fā)器
? drop trigger tr_check_sal
?
?

? 10.開發(fā)動(dòng)態(tài)SQL
? 在PL/SQL塊中編寫動(dòng)態(tài)SQL語句時(shí),需要將SQL語句存放到字符串變量中,而且SQL可以包含占位符
? 1.動(dòng)態(tài)SQL處理方法
? 1.1使用EXECUTE IMMEDIATE語句
? 可處理DDL語句(CREATE,ALTER,DROP),DCL語句(GRANT,REVOKE),DML語句(INSERT,UPDATE,DELETE)以及單行SELECT語句
? 不能用于處理多行查詢語句
?
? 1.2使用OPEN-FOR,FETCH和CLOSE語句
? 為了處理動(dòng)態(tài)的多行查詢操作,必須要使用OPEN-FOR語句打開游標(biāo),使用FETCH循環(huán)提取數(shù)據(jù),CLOSE關(guān)閉游標(biāo)
?
? 1.3使用批量動(dòng)態(tài)SQL
?
? 2.處理非查詢語句:DDL,DCL,DML
? 語法:execute immediate dynamic_string
?????? [into (define_variable[,define_variable]...|record)]
?????? [using [in|out|in out] bind_argument [,[in|out|in out] bind_argument]...]
?????? [(returning | return) into bind_argument[,bind_argument]...]

? 2.1使用EXECUTE IMMEDIATE處理DDL操作
? EXECUTE IMMEDIATE后面只需要帶有DDL語句,不需要INTO 和USING子句
?
? create or replace procedure drop_table(table_name varchar2)
? is
??? sql_statement varchar2(100);
? begin
??? sql_statement:='drop table '||table_name;
??? execute immediate sql_statement;
? end;

? exec drop_table('worker');
?
? 2.2使用EXECUTE IMMEDIATE處理DCL操作
? conn system/manager
? create or replace procedure grant_sys_priv(priv varchar2,username varchar2)
? is
??? sql_stat varchar2(100);
? begin
??? sql_stat:='grant '||priv||' to '||username;
??? execute immediate sql_stat;
? end;

? exec grant_sys_priv('create session','scott')

? 2.3使用EXECUTE IMMEDIATE處理DML操作
? 如果DML語句包含有占位符,那么在EXECUTE IMMEDIATE語句之后必須要帶有USING子句
? 如果DML語句帶有RETURNING子句,那么在EXECUTE IMMEDIATE語句之后需要帶有RETURNING INTO子句
?
? 2.4處理無占位符和RETURNING子句的DML語句
? declare
??? sql_stat varchar2(100);
? begin
??? sql_stat:='update emp set sal=sal*1.1 where deptno=30';
??? execute immediate sql_stat;
? end;

? 2.5處理包含占位符的DML語句:要使用USING子句
? declare
??? sql_stat varchar2(100);
? begin
??? sql_stat:='update emp set sal=sal*(1+:percent/100)'||' where deptno=:dno';
??? execute immediate sql_stat using &1,&2;
? end;

? 2.6處理包含RETURNING子句的DML語句:必須使用RETURNING INTO子句接受返回?cái)?shù)據(jù)
? 當(dāng)直接使用EXECUTE IMMEDIATE處理帶有RETURNING子句的DML時(shí),只能處理作用在單行上的DML語句
? 如果DML語句作用在多行上,則必須要使用BULK子句
? declare
??? salary number(6,2);
??? sql_stat varchar2(100);
? begin
??? sql_stat:='update emp set sal=sal*(1+percent/100)'||' where empno=:eno returning sal into :salary';
??? execute immediate sal_stat using &1,&2 returning into salary;
??? dbms_output.put_line('新工資:'||salary);
? end;

? 2.7使用EXECUTE IMMEDIATE處理單行查詢:需要使用INTO子句接受返回?cái)?shù)據(jù)
? declare
??? sql_stat varchar2(100);
??? emp_record emp%rowtype;
? begin
??? sql_stat:='select * from emp where empno=:enp';
??? execute immediate sql_stat into emp_record using &1;
??? dbms_output.put_line('雇員'||emp_record.ename||'的工資為'||emp_record.sal);
? end;

?
? 3.處理多行查詢語句
? 使用EXECUTE IMMEDIATE只能處理單行查詢語句.
? 為了動(dòng)態(tài)的處理SELECT語句所返回的多行數(shù)據(jù),需要使用OPEN-FOR ,FETCH ,CLOSE語句
?
? 3.1定義游標(biāo)變量
? 動(dòng)態(tài)處理多行查詢需要使用游標(biāo)變量來完成
? type cursortype is ref cursor;
? cursor_variable cursortype;
? 3.2打開游標(biāo)變量
? open cursor_variable for dynamic_string [using bind_argument[,bind_argument]...];
? 3.3循環(huán)提取數(shù)據(jù)
? fetch cursor_variable into {var1[,var2]...|record_var};
? 3.4關(guān)閉游標(biāo)變量
? close cursor_variable
? 3.5多行查詢示例
? declare
??? type empcurtyp is ref cursor;
??? emp_cv empcurtyp;
??? emp_record emp%rowtype;
??? sql_stat varchar2(100);
? begin
??? sql_stat:='select * from emp where deptno=:dno';
??? open emp_cv for sql_stat using &dno;
??? loop
????? fetch emp_cv into emp_record;
????? exit when emp_cv%notfound;
????? dbms_output.put_line('雇員名:'||emp_record.ename||',工資:'||emp_record.sal);
??? end loop;
??? close emp_cv;
? end;

? 4.在動(dòng)態(tài)ＳＱＬ中使用BULK子句
? 三種語句支持BULK子句:EXECUTE IMMEDIATE,FETCH,FORALL
? 1在EXECUTE IMMEDIATE語句中使用動(dòng)態(tài)BULK子句(處理多行查詢語句)
? execute immediate dynamic_string
? [bulk collect into define_variable[,define_variable...]]
? [using bind_argument[,bind_argument...]]
? [[returning | return]
? bulk collect into return_variable[,return_variable...]];

? 1.1使用BULK子句處理DML語句返回子句
? declare
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? type sal_table_type is table of emp.sal%type index by binary_integer;
??? ename_table ename_table_type;
??? sal_table sal_table_type;
??? sql_stat varchar2(100);
? begin
??? sql_stat:='update emp set sal=sal*(1+:percent/100)'||' where deptno=:dno'||' returning ename,sal into :name,:salary';
??? execute immediate sql_stat using &percent,&dno returning bulk collect into ename_table,sal_table;
??? for i in 1..ename_table.count loop
????? dbms_output.put_line('雇員'||ename_table(i)||'的新工資為'||sal_table(i));
??? end loop;
? end;

? 1.2使用BULK子句處理多行查詢
? declare
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? ename_table ename_table_type;
??? sql_stat varchar2(100);
? begin
??? sql_stat:='select ename from emp where deptno=:dno';
??? execute immediate sql_stat bulk collect into ename_table using &dno;
??? for i in 1..ename_table.count loop
????? dbms_output.put_line(ename_table(i));
??? end loop;
? end;

? 2.在FETCH語句中使用BULK子句
? 默認(rèn)情況下FETCH語句每次只能提取單行數(shù)據(jù),為了處理所有數(shù)據(jù),需要使用循環(huán)語句.通過引入BULK,一次就可以提取所有數(shù)據(jù)
? fetch dynamic_cursor bulk collect into define_variable[,define_variable...];

? declare
??? type empcurtyp is ref cursor;
??? emp_cv empcurtyp;
??? type ename_table_type is table of emp.ename%type index by binary_integer;
??? ename_table ename_table_type;
??? sql_stat varchar2(100);
? begin
??? sql_stat:='select ename from emp where job=:title';
??? open emp_cv for sql_stat using '&job';
??? fetch emp_cv bulk collect into ename_table;
??? for i in 1..ename_table.count loop
????? dbms_output.put_line(ename_table(i));
??? end loop;
??? close emp_cv;
? end;

?
? 3.在FORALL語句中使用BULK子句
? FORALL只使用于動(dòng)態(tài)的INSERT,UPDATE,DELETE語句,而不適合于動(dòng)態(tài)的SELECT語句,并且FORALL語句要和EXECUTE IMMEDIATE語句結(jié)合使用
? forall index in lower bound..upper bound
???? execute immediate dynamic_string
???? using bind_argument |bind_argument(index)
???? [,bind_argument| bind_argument(index)]...
???? [(returning | return) bulk collect into bind_argument[,bind_argument...]];

? declare
??? type ename_table_type is table of emp.ename%type;
??? type sal_table_type is table of emp.sal%type;
??? ename_table ename_table_type;
??? sal_table sal_table_type;
??? sql_stat varchar2(100);
? begin
??? ename_table:=ename_table_type('scott','smith','clark');
??? sql_stat:='update emp set sal=sl*1.1 where ename=:1'||' returning sal into :2';
??? forall i in 1..ename_table.count
?????? execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
??? for j in 1..ename_table.count loop
????? dbms_output.put_line('雇員'||ename_table(j)||'的新工資'||sal_table(j));
??? end loop;
?end;

?

? 11.對(duì)象類型
? 1.1對(duì)象類型組件
? 對(duì)象類型包括屬性和方法.
? 1.2對(duì)象類型和對(duì)象實(shí)例
? 對(duì)象實(shí)例OBJECT INSTANCE是對(duì)象類型的具體實(shí)現(xiàn),對(duì)應(yīng)于現(xiàn)實(shí)世界的具體對(duì)象
? 1.3構(gòu)造對(duì)象類型
? 對(duì)象類型包括對(duì)象類型規(guī)范和對(duì)象類型體兩部分
? 1.4對(duì)象類型屬性
? 必須要提供屬性名和數(shù)據(jù)類型
? 對(duì)象類型屬性不能使用以下數(shù)據(jù)類型:LONG,LONG RAW,ROWID,UROWID,PL/SQL特有類型,不能指定對(duì)象屬性的默認(rèn)值,也不能指定NOT NULL選項(xiàng)
? 1.5對(duì)象類型方法
? 用于描述對(duì)象所要執(zhí)行的操作
? 定義方法:可以定義構(gòu)造方法,MEMBER方法,STATIC方法,MAP方法以及ORDER方法
?
? 構(gòu)造方法CONSTRUCT METHOD:
? 用于初始化對(duì)象并返回對(duì)象實(shí)例,類似于JAVA語言的構(gòu)造方法
? 構(gòu)造方法是與對(duì)象類型同名的函數(shù),其默認(rèn)參數(shù)是對(duì)象類型的所有屬性
?
? MEMBER方法
? 用于訪問對(duì)象實(shí)例的數(shù)據(jù)
? 當(dāng)使用MEMBER方法時(shí),可以使用內(nèi)置參數(shù)SELF訪問當(dāng)前對(duì)象實(shí)例
? 只能由對(duì)象實(shí)例調(diào)用,而不能由對(duì)象類型調(diào)用.
? object_type_instance.method();

? STATIC方法:
? 用于訪問對(duì)象類型,在對(duì)象類型上執(zhí)行全局操作,而不需要訪問特定對(duì)象實(shí)例的數(shù)據(jù)
? 只能由對(duì)象類型調(diào)用,而不能由對(duì)象實(shí)例調(diào)用
?
? MAP方法:
? 是對(duì)象類型的一種可選方法,可以將對(duì)象實(shí)例影射為標(biāo)量類型數(shù)據(jù)(DATE,NUMBER,VARCHAR2),然后根據(jù)該標(biāo)量類型數(shù)據(jù)可以排序?qū)ο髮?shí)例,對(duì)象類型最多只能定義一個(gè)MAP方法
?
? ORDER方法:
? MAP方法可以在多個(gè)對(duì)象實(shí)例之間進(jìn)行排序,而ORDER方法只能比較兩個(gè)對(duì)象實(shí)例的大小.
? 定義對(duì)象類型時(shí),最多只能定義一個(gè)ORDER方法
? MAP方法和ORDER方法不能同時(shí)定義,如果不需要比較對(duì)象實(shí)例,則不需要定義MAP和ORDER方法
?

? 2.對(duì)象表OBJECT TABLE
? 指包含對(duì)象類型列的表,對(duì)象表至少會(huì)包含一個(gè)對(duì)象類型列.
? ORACLE包含行對(duì)象和列對(duì)象兩種對(duì)象表????
? 行對(duì)象是指直接基于對(duì)象類型所建立的表,而列對(duì)象則是包含多個(gè)列的對(duì)象表
? 行對(duì)象:create table employee of employee_type;
? 列對(duì)象:create table department(
??????????????? dno number,dname varchar2(10),
??????????????? employee employee_type);
? 3.對(duì)象類型繼承:TYPE INHERITANCE
? 指一個(gè)對(duì)象類型繼承另一個(gè)對(duì)象類型
?
? 4.REF數(shù)據(jù)類型
? REF是指向行對(duì)象的邏輯指針,是ORACLE的一種內(nèi)置數(shù)據(jù)類型
? 建表時(shí)通過使用REF引用行對(duì)象,可以使不同表共享相同對(duì)象,從而降低內(nèi)存占用.
? ref table department(dno number(2),dname varchar2(10),emp ref employee_type);

? 11.2建立和使用簡單對(duì)象類型
? 建立對(duì)象類型規(guī)范的語法：
? create or replace type type_name as object(
??? attribute1 datatype[,attribute2 datatype,...],
??? [member|static method1 spec,member|static method2 spec,..]);

? 建立對(duì)象類型體的語法:
? create or replace type body type_name as
??? member|static method1 body;
??? member|static method2 body;
??? ....]

? 1.建立和使用不包含任何方法的對(duì)象類型
? 對(duì)象類型可以不包含任何方法
? create or replace type person_type1 as object(
??? name varchar2(10),gender varchar2(2),birthdate date);

? 1.1建立行對(duì)象
? 行對(duì)象是指直接基于對(duì)象類型所建立的表
? create table person_tab1 of person_typ1;

? 為行對(duì)象插入數(shù)據(jù)
? begin
??? insert into person_tab1 values('馬麗','女','11-1月-76');
??? insert into person_tab2 values(person_type1('王名','男','11-12月-76);--使用對(duì)象類型的構(gòu)造方法來插入數(shù)據(jù)
? end;

? 檢索行對(duì)象數(shù)據(jù)
? 必須要使用函數(shù)VALUE取得行對(duì)象數(shù)據(jù),并檢索到對(duì)象類型變量中
? declare
??? person person_typ1;
? begin
??? select value(p) into person from person_tab1 p where p.name='&name';
??? dbms_output.put_line('性別:'||person.gender);
??? dbms_output.put_line('出生日期:'||person.birthdate);
? end;

? 更新行對(duì)象數(shù)據(jù)
? 如果按照對(duì)象屬性更新數(shù)據(jù),則必須要為行對(duì)象定義別名.
? begin
??? update person_tab1 p set p.birthdate='11-2月-76' where p.name='馬麗';
? end;
?
? 刪除行對(duì)象數(shù)據(jù)
? 如果按照對(duì)象屬性刪除數(shù)據(jù),則必須要為行對(duì)象定義別名
? begin
??? delete from person_tab1 p where p.name='馬麗';
? end;

? 1.2建立列對(duì)象
? create table employee_tab1(
??? eno number(6),person person_typ1,sal number(6,2),job varchar2(10));

? 為列對(duì)象employee_tab1插入數(shù)據(jù)
? begin
??? insert into empoyee_tab1(eno,sal,job,person) value1,2000,'高級(jí)鉗工',person_typ1('王名','男','11-1月-76');
? end;

? 檢索對(duì)象類型列的數(shù)據(jù)
? 檢索列對(duì)象的對(duì)象類型列數(shù)據(jù)時(shí)可以直接將對(duì)象實(shí)例數(shù)據(jù)檢索到對(duì)象類型變量中
? declare
??? employee person_typ1;
??? salary number(6,2);
? begin
??? select person,sal into employee,salary from employee_tab1 where eno=&no;
??? dbms_output.put_line('雇員名:'||employee.name);
??? dbms_output.put_line('雇員工資:'||salary);
? end;

? 更新對(duì)象列數(shù)據(jù)
? 更新列對(duì)象的對(duì)象列數(shù)據(jù)時(shí)必須要為列對(duì)象定義別名,并且引用對(duì)象屬性(列對(duì)象別名.對(duì)象類型列名.對(duì)象屬性名)
? begin
??? update employee_tab? p set p.person.birthdate='&newdate' where p.person.name='&name';
? end;

? 依據(jù)對(duì)象屬性刪除數(shù)據(jù)
? 依據(jù)對(duì)象屬性刪除列數(shù)據(jù)時(shí)必須要為對(duì)象表定義別名,并且引用對(duì)象屬性(列對(duì)象別名.對(duì)象類型列名.對(duì)象屬性名)
?

? 2.建立和使用包含MEMBER方法的對(duì)象類型
? MEMBER方法用于訪問對(duì)象實(shí)例的數(shù)據(jù),如果在對(duì)象類型中需要訪問特定對(duì)象實(shí)例的數(shù)據(jù),則必須要定義MEMBER方法.
? MEMBER方法只能由對(duì)象實(shí)例調(diào)用,而不能由對(duì)象類型調(diào)用.
? create or replace type person_typ2 as object(
??? name varchar2(10),gender varchar2(2),biethdate date,address varchar2(100),
??? member procedure change_address(new_addr varchar2),
??? member function get_info feturn varchar2);

? 在建立類型規(guī)范時(shí)定義了MEMBER方法,所以必須要通過對(duì)象類型體實(shí)現(xiàn)這些方法.
? create or replace type body person_typ2 is?
??? member procedure change address(new_addr varchar2)
??? is
??? begin
????? address:=new_addr;
??? end;
??? member function get_info return varchar2
??? is
????? v_info varchar2(100);
??? begin
????? v_info:='姓名'||name||',出生日期:'||birthdate;
????? return v_info;
??? end;
? end;

? 使用對(duì)象類型
? create table employee_tab2(
??? eno number(6),person person_typ2,sal number(6,2),job varchar2(10));
??? insert into employee_tab2(eno,sal,job,person) vaules(1,2000,''高級(jí)焊工',person_typ2('王名','男','11-1月-76','福州市'));
??? insert into employee_tab2(eno,sal,job,person) values(2,1500,'質(zhì)量檢查員',person_typ2('馬麗','女','11-1月-76','福州市'));

? 調(diào)用對(duì)象方法
? declare
??? v_person person_typ2;
? begin
??? select person into v_person from employee_tab2 where eno=&&no;
??? v_person.change_address('福清');
??? update employee_tab2 set person=v_person where eno=&no;
??? dbms_output.put_line(v_person.get_info);
? end;

? 3.建立和使用包含STATIC方法的對(duì)象類型
? static方法用于訪問對(duì)象類型,在對(duì)象類型上執(zhí)行全局操作
? STATIC方法只能由對(duì)象類型訪問,不能由對(duì)象實(shí)例訪問
? 建立類型規(guī)范:
? create or replace type person_typ3 as object(
??? name varchar2(10),gender varchar2(2),
??? birthdate date,regdate date,
??? static function getdate return date,
??? member function get_info return varchar2);
?
? 建立對(duì)象類型體:
? create or replace type body person_typ3 is
??? static function getdate return date is
??? begin
????? return sysdate;
??? end;
??? member function get_info return varchar2
??? is
??? begin
????? return '姓名:'||name||',注冊(cè)日期:'||regdate;
??? end;
? end;

? 使用對(duì)象類型及其STATIC方法和MEMBER方法
? create table employee_tab3(
??? eno number(6),person person_typ3,sal number(6,2),job varchar2(10));
?
? 在對(duì)象類型上使用STATIC方法:對(duì)象類型調(diào)用
? begin
??? insert into employee_tab3(eno,sal,job,person) values(&no,&salary,'&title',person_typ3("&name','&sex','&birthdate',person_typ3.getdate()));

? 使用MEMBER方法:實(shí)例對(duì)象調(diào)用
? declare
??? v_person person_typ3;
? begin
??? select person into v_person from employee_tab3 where eno=&no;
??? dbms_output.put_line(v_person.get_info());
? end;

?
? 4.建立和使用包含MAP方法的對(duì)象類型
? MAP方法用于將對(duì)象實(shí)例映射為標(biāo)量數(shù)值(NUMBER,DATE,VARCAHR2等)
? 為了排序多個(gè)對(duì)象實(shí)例的是數(shù)據(jù),可以在建立對(duì)象類型時(shí)定義MAP方法
? 一個(gè)對(duì)象類型最多只能定義一個(gè)MAP方法,并且MAP方法和ORDER方法不能同時(shí)使用
? 建立對(duì)象類型規(guī)范:
? create or replace type person_typ4 as object(
??? name varchar2(10),gender varchar2(2),birthdate date,
??? map member function getage return varchar2);

? 建立對(duì)象類型體:
? create or replace type body person_typ4 is
??? map member function getage return varchar2
??? is
??? begin
????? return trunc((sysdate-birthdate)/365);
??? end;
? end;

? 使用對(duì)象類型和方法:
? create table employee_tab4(
??? eno number(6),person person_typ4,sal number(6,2),job varchar2(10));
? insert into employee_tab4(eno,sal,job,person) values (1,1500,'圖書管理員',person_typ4('馬麗','女','11-1月-76'));
? insert into employee_tab4(eno,sal,job,person) values (2,1500,'圖書管理員',person_typ4('馬麗','女','11-1月-76'));
? insert into employee_tab4(eno,sal,job,person) values (3,1500,'圖書管理員',person_typ4('馬麗','女','11-1月-76'));
?
? 使用MAP方法getage比較對(duì)象實(shí)例的方法
? declare
??? type person_table_type is table of person_typ4;
??? person_table person_table_type;
??? v_temp varchar2(100);
? begin
??? select person bulk collect into person_table from employee_tab4;
??? if person_table(1).getage()>person_table(2).getage() then
?????? v_temp:=preson_table(1).name||'比'||person_table(2).name||'大';
??? else
?????? v_temp:=preson_table(1).name||'比'||person_table(2).name||'小';
??? end if;
??? dbms_output.put_line(v_temp);
? end;

?
? 5.建立和使用包含ORDER方法的對(duì)象類型
? order方法用于比較兩個(gè)對(duì)象實(shí)例的大小.
? 一個(gè)對(duì)象類型最多只能包含一個(gè)ORDER方法,并且OREDER方法不能與MAP方法同時(shí)使用
? 建立對(duì)象類型規(guī)范:
? create or replace type person_typ5 as object(
??? name varchar2(10),gender varchar2(2),birthdate date,
??? order member functon compare( p person_typ5) return int);

? 建立對(duì)象類型體:
? create or replace type body person_typ5 is
??? order member function compare(p person_typ5) return int
??? is
??? begin
????? case
??????? when birthdate>p.birthdate then return 1;
??????? when birthdate=p.birthdate then return 0;
??????? when birthdate<p.birthdate then return -1;
????? end case;
??? end;
? end;

? 使用對(duì)象類型及其方法
? create table employee_tab5(
??? eno number(6),person person_typ5,sal number(6,2),job varchar2(10));
? insert into employee_tab5(eno,sal,job,person) values(1,1500,'圖書管理員',person_typ5('馬麗','女','11-1月-76));
? insert into employee_tab5(eno,sal,job,person) values(2,1500,'圖書管理員',person_typ5('馬麗','女','11-1月-76));
?
? 使用ORDER方法比較不同對(duì)象實(shí)例的數(shù)據(jù)
? declare
??? type person_table is table of person_typ5;
??? person_table person_table_type;
??? v_temp varchar2(100);
? begin
??? select person bulk collect into person_table from employee_tab5;
??? if person_table(1),compare(person_table(2))=1 then
?????? v_temp:=person_table(1).name||'比'||person_table(2).name||'大';
??? else
?????? v_temp:=person_table(1).name||'比'||person_table(2).name||'小';
??? end if;
??? dbms_output.put_line(v_temp);
? end;

? 6.建立和使用自定義構(gòu)造方法的對(duì)象類型
? 當(dāng)自定義構(gòu)造方法時(shí),構(gòu)造方法的名稱必須要與對(duì)象類型的名稱完全相同,并且必須要使用CONSTRUCTOR FUNCTION關(guān)鍵字定義構(gòu)造方法
? 建立對(duì)象類型以及構(gòu)造方法:
? create or replace type person_typ6o as object(
??? name varchar2(10),gender varchar2(2),birthdate date,
??? constructor function person_typ6(name varchar2) return selfas result,
??? constructor function person_typ6(name varchar2,gender varchar2) return self as result,
??? constructor function person_typ6(name varchar2,gender varchar2,birthdate date) return self as result);
?
? 建立對(duì)象類型體實(shí)現(xiàn)其構(gòu)造方法:
? create or replace type body person_typ6 is
??? constructor function person_typ6(name varchar2) return self as result
??? is
??? begin
????? self.name:=name;
????? self.gender:='女';
????? self.birthdate:=sysdate;
????? return;
??? end;
??? constructor function person_typ6(name varchar2,gender varchar2) return self as result
??? is
??? begin
????? self.name:=name;
????? self.gender:=gender;
????? self.birthdate:=sysdate;
????? return;
??? end;
??? constructor function person_typ6(name varchar2,gender varchar2,birthdate date) return self as result
??? is
??? begin
????? self.name:=name;
????? self.gender:=gender;
????? self.birthdate:=birthdate;
????? return;
??? end;
? end;

? 使用各種構(gòu)造方法為其插入數(shù)據(jù):
? create table employee_tab6(eno number(6),person person_typ6,sal number(6,2),job varchar2(10));
??? insert into employee_tab6(eno,sal,job,person) values(1,1500,'圖書管理員',person_typ6('馬麗'));
??? insert into employee_tab6(eno,sal,job,person) values(1,1500,'圖書管理員',person_typ6('馬麗','男'));
??? insert into employee_tab6(eno,sal,job,person) values(1,1500,'圖書管理員',person_typ6('馬麗'.'男','11-1月-76'));
?

? 12.3.建立和使用復(fù)雜對(duì)象類型
? 復(fù)雜對(duì)象類型是指與其他對(duì)象類型具有關(guān)聯(lián)關(guān)系的對(duì)象類型
? 1.對(duì)象類型嵌套
? 建立對(duì)象類型
? create or replace type addr_typ7 as object(
??? state varchar2(20),city varchar2(20),
??? street varchar2(50),zipcode varchar2(6),
??? member function get_addr return varchar2);
?
? 建立對(duì)象類型體實(shí)現(xiàn)該方法:
? create or replace type body addr_typ7 as
??? member function get_addr return varchar2 is
??? begin
????? return state||city||street;
??? end;
? end;

? 建立對(duì)象類型(嵌套):
? create or replace type person_typ7 as object(
??? name varchar2(10),gender varchar2(2),birthdate date,address addr_typ7,
??? member function get_info return varchar2);

? 建立對(duì)象類型體:
? create or replace type body person_typ7 as
??? member fucntion get_info return varchar2
??? is
??? begin
????? return '姓名:'||name||',家庭住址:'||address.get_addr();
??? end;
? end;

? 建立操縱對(duì)象表:
? create table employee_tab7(
??? eno number(6),person person_typ7,sal number(6,2),job varchar2(10));
?
? 操縱對(duì)象表
? 在PLS/SQL塊中為對(duì)象表插入數(shù)據(jù)
? begin
??? insert into employee_tab7(eno,sal,job,person) values(1,1500,'圖書管理員',person_typ7('馬麗','女','11-1月-76',addr_typ7('內(nèi)蒙古','呼和浩特','囫圇路10','010010')));
??? insert into employee_tab7(eno,sal,job,person) values(2,1500,'圖書管理員',person_typ7('馬麗','女','11-1月-76',addr_typ7('內(nèi)蒙古','呼和浩特','囫圇路20','010010')));
? end;

? 在PL/SQL塊中更新對(duì)象列數(shù)據(jù)
? declare
??? v_pserson person_typ7;
? begin
??? select person into v_person from employee_tab7 where eno=&&no;
??? v_person.address.street:='&street';
??? update employee_tab7 set person=v_person where eno=&no;
? end;

? 在PL/SQL塊中檢索對(duì)象列數(shù)據(jù)
? declare
??? v_person person_typ7;
? begin
??? select person into v_person from employee_tab7 where eno=&no;
??? dbms_output.put_line(v_person.get_info);
? end;

? 在PL/SQL塊中刪除對(duì)象表數(shù)據(jù)
? begin
??? delete from employee_tab7 where eno=&no;
? end;

? 2.參照對(duì)象類型
? 指在建立對(duì)象表時(shí)使用REF定義表列,REF實(shí)際是指向行對(duì)象數(shù)據(jù)的邏輯指針
? 通過使用REF定義表列,可以使得一個(gè)對(duì)象表引用另一個(gè)對(duì)象表(行對(duì)象)的數(shù)據(jù),從而節(jié)省磁盤空間和內(nèi)存空間
? 建立對(duì)象類型:
? create or replace type person_typ8 as object(
??? name varchar2(10),gender varchar2(2),birchdate date,address varchar2(50),
??? member function get_info return varchar2);

? 建立對(duì)象類型體:
? create or replace type body person_typ8 as
??? member function get_info return varchar2
??? is
??? begin
????? return '姓名:'||name||',家庭住址:'||address;
??? end;
? end;

? 建立行對(duì)象并追加數(shù)據(jù)
? create table person_tab8 of person_typ8;
??? insert into person_tab8 values('馬麗','女','11-1月-76','內(nèi)蒙古呼和浩特10號(hào)');
??? insert into person_tab8 values('王名','男','11-1月-76','內(nèi)蒙古呼和浩特11號(hào)');
?

? 對(duì)象表引用對(duì)象表(REF)
? 建立對(duì)象表(引用表)
? create table employee_tab8(
??? eno number(6),person ref person_typ8,sal number(6,2),job varchar2(10));
?
? 操縱對(duì)象表
? 為對(duì)象表出入數(shù)據(jù)
? 引用行對(duì)象表時(shí),需要使用函數(shù)REF(),其返回值實(shí)際是指向相應(yīng)數(shù)據(jù)行的指針
? begin
??? insert into employee_tab8 select 1,ref(a),2000,'圖書管理員' from person_tab8 a where a.name = '馬麗';
??? insert into employee_tab8 select 1,ref(a),2000,'圖書管理員' from person_tab8 a where a.name = '王名';
? end;

? 檢索REF對(duì)象列數(shù)據(jù)
? 為了取得行對(duì)象的相應(yīng)數(shù)據(jù),必須要使用DEREF函數(shù)取得REF列所對(duì)應(yīng)的實(shí)際數(shù)據(jù)
? declare
??? v_person person_typ8;
? begin
??? select deref(person) into v_person from employee_tab8 where eno=&no;
??? dbms_output.put_line(v_person.get_info);
? end;

? 更新REF對(duì)象列數(shù)據(jù)
? 要修改其列所引用的數(shù)據(jù),就必須修改相應(yīng)的行對(duì)象
? declare
??? v_person person_typ8;
? begin
??? select deref(person) into v_person from employee_tab8 where eno=&no;
??? v_person.address:='&address';
??? update person_tab8 set address=v_person.address where name=v_person.name;
? end;

? 刪除對(duì)象表數(shù)據(jù)
? begin
??? delete from employee_tab8 where eno=&no;
? end;

?

? 3.對(duì)象類型繼承
? 指一個(gè)對(duì)象類型繼承另一個(gè)對(duì)象類型,并且對(duì)象類型繼承由父類型和子類型組成
? 父類型用于定義不同對(duì)象類型的公用屬性和方法,而子類型不僅繼承了父類型的公用屬性和方法,而且還可具有自己的私有屬性和方法.
? 使用對(duì)象類型繼承時(shí),在定義父類型時(shí)必須要指定NOT FINAL 選項(xiàng),如果不指定該選項(xiàng),默認(rèn)為FINAL,表示該對(duì)象類型不能被繼承
?
? 建立父對(duì)象類型:
? create or replace type person_typ9 as object(
??? name varchar2(10),gender varchar2(2),birthdate date,address varchar2(50),
??? member function get_info return varchar2) not final;

? 建立對(duì)象類型體:
? create or replace type body person_typ9 as
??? member function get_info return varchar2
??? is
??? begin
????? return '姓名:'||name||',家庭住址:'||address;
??? end;
? end;

? 建立子對(duì)象類型(繼承):
? create or replace type employee_typ9 under person_typ9(
?? eno number(6),sal number(6,2),job varchar2(10),
?? member function get_other return varchar2);

? 建立對(duì)象類型體(私有方法):
? create or replace type body employee_typ9 as
??? member function get_other return varchar2
??? is
??? begin
????? return '雇員名稱:'||name||',工資:'||sal;
??? end;
? end;

? 建立對(duì)象表并插入數(shù)據(jù)
? sql>create table employee_tab9 of employee_typ9;
? sql>insert into employee_tab9 values('馬麗','女','01-11月-76','呼和浩特15號(hào)',2,2000,'高級(jí)鉗工');
? sql>insert into employee_tab9 values('王名','男','01-11月-76','呼和浩特15號(hào)',2,2000,'高級(jí)鉗工');???
?
? 使用對(duì)象方法輸出數(shù)據(jù)
? declare
??? v_employee employee_typ9;
? begin
??? select value(a) into v_employee from employee_tab9 a where a.eno=&no;
??? dbms_output.put_line(v_employee.get_info);
??? dbms_output.put_line(v_employee.get_other);
? end;
?
?
? 4.維護(hù)對(duì)象類型
? 顯示對(duì)象類型信息? 執(zhí)行CREATE TYPE 命令建立對(duì)象類型時(shí),ORACLE會(huì)將對(duì)象類型的信息存放到數(shù)據(jù)字典中(USER_TYPES)
? select type_name,attributes,final from user_types;
? desc person_typ1

? 增加和刪除對(duì)象類型屬性
　如果已經(jīng)基于對(duì)象類型建立了對(duì)象類型或?qū)ο蟊?，那么在?duì)象類型增加或刪除屬性時(shí)必須要帶有ＣＡＳＣＡＤＥ關(guān)鍵字
? alter type preson_typ1 add attribute address varchar2(50) cascade
? alter type person_typ1 drop attrbute birthdate cascade;

? 增加和刪除對(duì)象類型方法
? alter type person_typ1 add member function get_info return varchar2 cascade;
? create or replace type body person_typ1 as
??? member function get_info return varchar2 is
??? begin
????? return '雇員名:'||name||',家庭住址:'||address;
??? end;
? end;

?

? 13.處理例外

? 1.例外分類:預(yù)定義例外,非預(yù)定義例外,自定義例外三種
? 2.例外處理:
? 傳遞例外:如果在例外處理部分EXCEPTON沒有捕捉例外,ORACLE會(huì)將例外傳遞到調(diào)用環(huán)境.
? 捕捉并處理例外:使用例外處理部分完成
? exception
??? when exception1 [or exception2...] then
??? statement1;
??? statement2;
??? .....
??? when ....
??? ...
??? when others then??? --必須是例外處理部分的最后一條子句
??? statement1;
??? ...
??
? 2.處理預(yù)定義例外
? 常用預(yù)定義例外
? 2.1access_into_null:ora-06530錯(cuò)誤.在引用對(duì)象屬性之前,必須首先初始化對(duì)象,否則觸發(fā)例外
? 2.2case_not_found:ora-06592.在編寫CASE語句時(shí),如果在WHEN子句中沒有包含必須的條件分支,并且沒有包含ELSE子句,就會(huì)觸發(fā)
? undef no
? declare
??? v_sal emp.sal%type;
? begin
?? select sal into v_sal from emp where empno=&&no;
?? case
???? when v_sal<1000 then
?????? update emp set sal=sal+100 where empno=&no;
???? when v_sal<2000 then
?????? update emp set sal=sal+150 where empno=&no;
???? when v_sal<3000 then
?????? update emp set sal=sal+200 where empno=&no;
?? end case;
?? exception
???? when case_not_found then
???? dbms_output.put_line('在CASE語句中缺少與'||v_sal||'相關(guān)的條件');
?? end;

? 2.3collection_is_null:ora-06531
? 在給集合元素(嵌套表和VARRAY類型)賦值前,必須首先初始化集合元素,否則觸發(fā)例外
? declare
??? type ename_table_type is table of emp.ename%type;
??? ename_table ename_table_type;
? begin
??? select ename into ename_table(2) from emp where empno=&no;
??? dbms_output.put_line('雇員名:||ename_table(2);
??? exception
????? when collection_null then
??????? dbms_output.put_line('必須使用構(gòu)造方法初始化集合元素');
? end;
?
? 2.4cursor_already_open:ora-06511
? 當(dāng)重新打開已經(jīng)打開的游標(biāo)時(shí),會(huì)隱含的觸發(fā)例外.已經(jīng)使用OPEN打開了顯示游標(biāo),并執(zhí)行FOR循環(huán),就會(huì)隱含的觸發(fā)該例外
? declare
??? cursor emp_cursor is select ename,sal from emp;
? begin
??? open emp_cursor;
??? for emp_record in emp_cursor loop
????? dbms_output.put_line(emp_record.ename);
??? end loop;
??? exception
????? when cursor_already_open then
????? dbms_output.put_line('游標(biāo)已打開');
? end;

? 2.5DUP_VAL_ON_INDEX:ORA-00001
? 當(dāng)在唯一索引所對(duì)應(yīng)的列上鍵入重復(fù)值時(shí),觸發(fā)例外
? begin
??? update dept set deptno=&new_no where deptno=&old_no;
??? exception
????? when dup_val_on_index then
??????? dbms_output.put_line('在DEPTNO列上不能出現(xiàn)重復(fù)值');
? end;

? 2.6invalid_cursor:ora-01001
? 當(dāng)試圖在不合法的游標(biāo)上執(zhí)行操作時(shí),會(huì)隱含的觸發(fā)例外.要從未打開的游標(biāo)提取數(shù)據(jù),或者關(guān)閉未打開的游標(biāo),則觸發(fā)例外
? declare
??? cursor emp_cursor is select ename,sal from emp;
??? emp_record emp_cursor%rowtype;
? begin
??? fetch emp_cursor into emp_record;
??? close emp_cursor;
? exception
??? when invalid_cursor then
????? dbms_output.put_line('請(qǐng)檢查游標(biāo)是否已經(jīng)打開');
? end;

? 2.7invalid_number:ora-01722
? 當(dāng)內(nèi)嵌SQL語句不能有效的將字符轉(zhuǎn)變成數(shù)字時(shí),會(huì)隱含觸發(fā)例外
? begin
??? update emp set sal=sal+'1oo';
? exception
??? when invalid_number then
????? dbms_output.put_line('輸入的數(shù)字值不正確');
? end;
?
? 2.8no_date_found:ora-01403
? 當(dāng)執(zhí)行SELECT INTO 未返回行,或者引用了索引表未初始化的元素時(shí),會(huì)隱含觸發(fā)例外
? declare
??? v_sal emp.sal%type;
? begin
??? select sal into v_sal from emp where lower(ename)=lower('&name');
? exception
??? when no_data_found then
????? dbms_output.put_line('不存在該雇員');
? end;
?
? 2.9too_many_rows:ora-01422
? 當(dāng)執(zhí)行select into 語句時(shí),如果返回超過一行,則會(huì)觸發(fā)例外
? declare
??? v_ename emp.ename%type;
? begin
??? select ename into v_ename from emp where sal=&sal;
? exception
??? when too_many_rows then
????? dbms_output.put_line('返回多行');
? end;

? 2.10zero_divide:ora-01476
? 如果使用數(shù)字值除0,則會(huì)隱含觸發(fā)例外
? declare
??? num1 int:=100;
??? num2 int:=0;
??? num3 number(6,2);
? begin
??? num3:=num1/num2;
? exception
??? when zero_divide then
????? dbms_output.put_line('分母不能為0');
? end;

? 2.11subscript_beyond_count:ora-06533
? 當(dāng)使用嵌套表或VARRAY元素時(shí),如果元素下標(biāo)超出了嵌套表或VARRAY元素的范圍,則回隱含的觸發(fā)例外
? declare
??? type emp_array_type is varray(20) of varchar2(10);
??? emp_output.put_line(emp_array(3));
? begin
??? emp_array:=emp_array_type('scott','maray');
??? dbms_output.put_line(emp_array(3));
? exception
??? when subscript_beyond_count then
????? dbms_output.put_line('超出下標(biāo)范圍');
? end;

? 2.12subscript_outside_limit:ora-06532
? 當(dāng)使用嵌套表或VARRAY元素時(shí),如果元素下標(biāo)為負(fù)值,則會(huì)隱含觸發(fā)例外
? declare
??? type emp_array_type is varray(20) of varray2(10);
??? emp_array emp_array_type;
? begin
??? emp_array:=emp_array_type('scott','mary');
??? dbms_output.put_line(emp_array(-1);
? exception
??? when subscript_outside_limit then
????? dbms_output.put_line('嵌套表和VARRAY下標(biāo)不能為負(fù)值');
? end;

? 2.13value_error:ora-06502
? 如果變量長度不足以容納實(shí)際數(shù)據(jù),則會(huì)隱含的出發(fā)例外
? declare
??? v_ename varchar2(5);
? begin
??? select ename into v_ename from emp where empno=&no;
??? dbms_output.put_line(v_ename);
? exception
??? when value_error then
????? dbms_output.put_line('變量尺寸不足');
? end;

? 2.14其他預(yù)定義例外
? login_denied:ora-01017連接數(shù)據(jù)庫時(shí),提供了不正確的用戶名和口令
? not_logged_on:ora-01012沒有連接到數(shù)據(jù)庫
? program_error:ora-06501存在PL/SQL內(nèi)部問題,可能需要重新安裝數(shù)據(jù)字典和PL/SQL系統(tǒng)包
? rowtype_mismatch:ora-06504宿主游標(biāo)變量和PL/SQL游標(biāo)變量的返回類型不兼容
? self_is_null:ora-30625使用對(duì)象類型時(shí),如果在NULL實(shí)例上調(diào)用成員方法,則會(huì)隱含觸發(fā)例外
? storage_error:ora-06500如果超出內(nèi)存或者內(nèi)存被損壞
? sys_invalid_rowid:ora-01410當(dāng)字符串轉(zhuǎn)變?yōu)镽OWID時(shí),必須使用有效的字符串,否則觸發(fā)例外
? timeout_on_resource:ora-00051ORACLE在等待資源時(shí)出現(xiàn)超時(shí)錯(cuò)誤

? 3.處理非預(yù)定義例外
? 使用預(yù)定義例外,只能處理21個(gè)ORACLE錯(cuò)誤.
? 使用非預(yù)定義例外包括三步:
? 在定義部分定義例外名,然后在例外和ORACLE錯(cuò)誤之間建立關(guān)聯(lián),最終在例外處理部分捕捉并處理例外.
? 當(dāng)定義ORACLE錯(cuò)誤和例外之間的關(guān)聯(lián)關(guān)系時(shí),需要使用偽過程EXCEPTION_INIT
? declare
??? e_integrity exception;
??? pragma exception_init(e_integrity,-2291);
? begin
??? update emp set deptno=&dno where empno=&eno;
? exception
??? when e_integrity then
????? dbms_output.put_line('該部門不存在');
? end;

?

? 4.處理自定義例外
? 自定義例外與ORACLE錯(cuò)誤沒有任何關(guān)聯(lián)
? 與預(yù)定義和非預(yù)定義不同,自定義例外必須顯示觸發(fā)
? declare
??? e_integrity exception;
??? pragma exception_init(e_integrity,-2291);
??? e_no_employee exception;
? begin
??? update emp set deptno=&dno where empno=&eno;
??? if sql%notfound then
?????? raise e_no_employee;
??? end if;
??? exception
????? when e_integrity then
??????? dbms_output.put_line('該部門不存在');
????? when e_no_employee then
??????? dbms_output.put_line('該雇員不存在');
? end;

?

? 5.使用例外函數(shù)
? 函數(shù)SQLCODE用于取得ORACLE錯(cuò)誤號(hào),而SQLERRM則用于取得與之相關(guān)的錯(cuò)誤信息
? 通過在存儲(chǔ)過程.函數(shù).包中使用RAISE_APPLICATION_ERROR可以自定義錯(cuò)誤號(hào)和錯(cuò)誤消息
? 5.1SQLCODE和SQLERRM
? undef v_sal
? declare
??? v_ename emp.ename%type;
? begin
??? select ename into v_ename from emp where sal=&&v_sal;
??? dbms_output.put_line('雇員名:'||v_ename);
? exception
??? when no_data_found then
????? dbms_output.put_line('不存在工資為:'||&v_sal||'的雇員');
??? when others then
????? dbms_output.put_line('錯(cuò)誤號(hào):'||SQLCODE);
????? dbms_output.put_line('錯(cuò)誤號(hào):'||sqlerrm);
? end;

? 5.2raise_application_error
? 用于在PL/SQL應(yīng)用程序中自定義錯(cuò)誤消息
? 該過程只能在數(shù)據(jù)庫端的子程序(過程,函數(shù),包,觸發(fā)器)中使用,不能在匿名快和客戶端的子程序中使用.
? raise_application_error(error_number,message[,[true|false]]);
? error_number:必須在-20000和-20999之間的負(fù)整數(shù).MESSAGE不能超過2048字節(jié)
? TRUE:該錯(cuò)誤會(huì)被放在先前錯(cuò)誤堆棧中,FALSE:則會(huì)替換先前所有錯(cuò)誤.
? create or replace procedure raise_comm(eno number,commission number)
? is
??? v_comm emp.comm%type;
? begin
??? select comm into v_comm from emp where empno=eno;
??? if v_comm is null then
?????? raise_application_error(-20001,'該雇員無補(bǔ)助');
??? end if;
? exception
??? when no_data_found then
????? dbms_output.put_line('該雇員不存在');
? end;

? 6.pl/sql編譯警告
? 6.1PL/SQL警告分類:
? severe:該警告用于檢查可能出現(xiàn)的不可預(yù)料結(jié)果或錯(cuò)誤結(jié)果,例如參數(shù)的別名問題
? performance:用于檢查可能出現(xiàn)的不可預(yù)料結(jié)果或錯(cuò)誤結(jié)果,例如參數(shù)的別名問題
? inforamational:用于檢查子程序中的死代碼
? all:該關(guān)鍵字用于檢查所有警告(severe,perforamce,informational)
?
? 6.2控制PL/SQL警告消息
? 為了使數(shù)據(jù)庫在編譯PL/SQL子程序時(shí)發(fā)出警告消息,需設(shè)置初始化參數(shù)PLSQL_WARNINGS.
? 初始化PLSQL_WARNINGS不僅可在系統(tǒng)級(jí)或會(huì)話級(jí)設(shè)置,也可在ALTER PROCEDURE命令中進(jìn)行設(shè)置激活或禁止
? alter system set plsql_warnings='enable:all';
? alter session set plsql_warnings='enable:performance';
? alter procedure hello compile plsql_warnings='enable:performance';
? alter session set plsql_warnings='disable:all';
? alter session set plsql_warnings='enable:severe','disable:performance','error:06002';
?
? 當(dāng)激活或禁止PL/SQL編譯警告時(shí),不僅可以使用ALTER SYSTEM,ALTER SESSION,ALTER PROCEDURE命令,還可使用PL/SQL系統(tǒng)包DBMS_WARNINGS
? SQL>call dbms_warning.set_warning_setting_string('enable:all','session');
?
? 7.使用PL/SQL編譯警告
? 檢測(cè)死代碼
? 為了檢測(cè)該子程序是否包含死代碼,必須首先激活警告檢查,然后重新編譯子程序,最后使用SHOW ERRORS命令顯示警告錯(cuò)誤
? alter session set plsql_warnings='enable:informational';
? alter prodedure dead_code compile;
? show errors

? 檢測(cè)引起性能問題的代碼
? 編寫PL/SQL子程序時(shí),如果數(shù)值與變量的數(shù)據(jù)類型不符合,ORACLE會(huì)隱含的轉(zhuǎn)換數(shù)據(jù)類型,但因?yàn)閿?shù)據(jù)類型轉(zhuǎn)換會(huì)影響子程序性能,所以在編寫PL/SQL子程序時(shí)應(yīng)該盡可能避免性能問題.
? create or replace procedure update_sal(name varchar2,salary varchar2)
? is
? begin
??? upodate emp set sal=salary where ename=name;
? end;
? 為了檢測(cè)該子程序是否會(huì)引起性能問題,應(yīng)首先激活警告檢查,然后重新編譯子程序,最后再使用SHOW ERRORS顯示警告錯(cuò)誤
? alter session set plsql_warnings='enable:performance';
? alter procedure update_sal compile;
? show errors

?
?

??
? 14.使用LOB對(duì)象(large object)
? LOB類型專門用于存儲(chǔ)大對(duì)象的數(shù)據(jù),包括大文本,圖形/圖象,視頻剪切等大數(shù)據(jù)
? 1.LOB類型
? oracle將lob分為兩種:內(nèi)部lob和外部lob
? 內(nèi)部lob包括:clob,blob和nclob三種類型,被存儲(chǔ)在數(shù)據(jù)庫中，并且支持事務(wù)操作
? 外部LOB只有BFILE一種類型,該類型的數(shù)據(jù)被存儲(chǔ)在操作系統(tǒng)OS文件中,并且不支持事務(wù)操作.
? CLOB/NCLOB用于存儲(chǔ)用于存儲(chǔ)大批量字符數(shù)據(jù),BLOB用于存儲(chǔ)大批量二進(jìn)制數(shù)據(jù),BFILE則存儲(chǔ)著指向OS文件的指針
? 1.1.使用LOB列時(shí),如果數(shù)據(jù)小于4000字節(jié),則與其他列相鄰存放(行內(nèi)),如果數(shù)據(jù)大于4000字節(jié),則數(shù)據(jù)被存放到專門的LOB段中(行外)
? 1.2.臨時(shí)LOB
? 編寫PL/SQL應(yīng)用程序時(shí),可以使用臨時(shí)LOB.臨時(shí)LOB相當(dāng)于局部變量,與數(shù)據(jù)庫表無關(guān),并且只能又當(dāng)前應(yīng)用程序建立和使用
? 當(dāng)在SQL語句中使用臨時(shí)LOB時(shí),他們只能作為輸入宿主變量使用,可在WHERE子句，VALUES子句和SET子句中使用臨時(shí)LOB,而不能在INTO子句中使用臨時(shí)LOB

? 2.DBMS_LOG包
? 2.1常量
? DBMS_LOB包定義了一些常量,這些常量可以在PL/SQL應(yīng)用程序中直接引用.
? file_readonly constant binary_integer:=0;
? lob_readonly constant binary_integer:=0;
? lobmaxsize constant integer:=4294967295;
? call constant pls_integer:=12;
? session constant pls_integer:=10;
? 2.2過程APPEND
? 用于將源LOB變量的內(nèi)容添加到目標(biāo)LOB變量的尾部.
? 該過程只適用與內(nèi)部LOB類型(BLOB CLOB),不適合BFILE類型
? dbms_lob.append(dest_lob in out nocopy blob,src_lob in blob);
? dbms_lob.append(dest_lob in out nocopy character set any_cs,src_lob in clob character set dest_lob%charset);
? dest_lob用于指定目標(biāo)LOB變量,SRC_LOB用于指定源LOB變量.
? declare
??? dest_lob clob;
??? src_lob clob;
? begin
??? src_lob:='中國';
??? dest_lob:='你好, ';
??? dbms_lob.append(dest_lob,src_lob);
??? dbms_output.put_line(dest_lob);
? end;

? 3.過程CLOSE
? 用于關(guān)閉已經(jīng)打開的LOB,不僅使用于CLOB,NCLOB和BLOB類型,也使用于BFILE類型
? dbms_lob.close(lob_loc in out nocopy blob/clob/bfile);

? 4.函數(shù)compare
? 用于比較兩個(gè)LOB的全部內(nèi)容或部分內(nèi)容.不僅適用于CLOB,NCLOB,BLOB類型,也適用于BFILE類型
? 只能用于比較同類型的LOB變量
? dbms_lob.compare(lob_1 in blob/clob/bfile,lob_2 in blob/clob/bfile,amount in integer:=4294967295,
?????????????????? offset_1 in integer:=1,offset_2 in integer:=1) return integer;
? lob_1指定第一個(gè)LOB變量,lob_2指定第二個(gè)lob變量,amount指定字符個(gè)數(shù)(CLOB)或字節(jié)個(gè)數(shù)(BLOB)
? offset_1指定第一個(gè)LOB的起始比較位置,offset_2指定第二個(gè)LOB的起始位置
? 比較結(jié)果相同,則返回O,如果比較不同,則返回一個(gè)非0的整數(shù)
? declare
??? dest_lob clob;
??? src_lob clob;
? begin
??? src_lob:='中國';
??? dest_lob:='&content';
??? if dbms_lob.compare(src_lob,dest_lob)=0 then
?????? dbms_output.put_line('內(nèi)容相同');
??? else
?????? dbms_output.put_line('內(nèi)容不同');
??? end if;
? end;
?
? 5.過程copy
? 用于將源LOB變量的部分或全部內(nèi)容復(fù)制到目標(biāo)LOB變量中,只適用于內(nèi)部LOB類型(CLOB,NCLOB,BLOB),不適用BFILE類型
? dbms_lob.copy(
?????? dest_lob in out nocopy blob/clob/nclob,?
?????? src_lob in blob/clob/nclob,
?????? anount in integer,
?????? dest_offset in integer:=1,?? 指定要復(fù)制到目標(biāo)LOB變量的起始位置
?????? src_offset in integer:=1);?? 指定源LOB變量中開始復(fù)制的起始位置
?

? declare
??? dest_lob clob;
??? src_lob clob;
??? amount int;
? begin
??? src_lob:='中國';
??? dest_lob:='你好, ';
??? amount:=dbms_lob.getlength(src_lob);
??? dbms_lob.copy(dest_lob,src_lob,amout,3);
??? dbms_output.put_line(dest_lob);
? end;

?
? 6.過程CREATETEMPORARY
? 用于建立臨時(shí)LOB,只適用于內(nèi)部LOB類型(BLOB/CLOB/NCLOB),但不適用于BFILE類型.建立在用戶的臨時(shí)表空間
? dbms_lob.createtemporary(
????? lob_loc in out nocopy blob/clob/nclob,
????? cache in boolean,dur in pls_integer:=10);
? LOB_LOC指定LOB定位符,CACHE指定是否要將LOB讀取到緩沖區(qū),DUR指定何時(shí)清除臨時(shí)LOB(10:會(huì)話結(jié)束清除臨時(shí)LOB,12:調(diào)用結(jié)束清除臨時(shí)LOB)
? declare
??? src_lob clob;
? begin
??? dbms_lob.createtemporary(src_lob,true);
? end;

? 7.過程ERASE
? 用于刪除LOB變量的全部內(nèi)容或部分內(nèi)容,只適用內(nèi)部LOB類型(BLOB/CLOB/NCLOB),而不適用于BFILE類型
? DBMS_LOB.ERASE(
????? lob_loc in out nocopy blob/clob/nclob,
????? amount in out nocopy integer,
????? offset in integer:=1);? --指定開始刪除內(nèi)容的起始位置
?
? declare
??? src_lob clob;
??? offset int;
??? amount int;
? begin
??? src_lob:='歡迎使用PL/SQL編程指南';
??? amount:=10;
??? offset:=5;
??? dbms_lob.erase)src_lob,amount,offset);
??? dbms_output.put_line(src_lob);
? end;

? 8.過程fileclose
? 用于關(guān)閉已經(jīng)打開的BFILE定位符所指向的OS文件
???? dbms_lob.fileclose (file_loc in out nocopy bfile);
? file_loc用于指定BFILE定位符
? 9.過程FILECOLSEALL
? 用于關(guān)閉當(dāng)前會(huì)話已經(jīng)打開的所有BFILE文件
???? dbms_lob.filecloseall
? 10.函數(shù)FILEEXISTS
? 用于確定BFILE定位符所指向的OS文件是否存在
????? dbms_lob.fileexists(file_loc in bfile) return integer;
? 如果文件存在,則返回1,如果文件不存在,則返回0
? declare
??? file1 bfile;
? begin
??? file1:=bfilename('G','readme.doc);
??? if dbms_lob.fileexists(file1)=0 then
?????? dbms_output.put_line('文件不存在');
??? else
?????? dbms_output.put_line('文件存在');
??? end if;
? end;

? 11.過程FILEGETNAME
? 用于取得BFILE定位符所對(duì)應(yīng)的目錄別名和文件名.
????? dbms_lob.filegetname(file_loc in bfile,dir_alias out varchar2,filename out varchar2);
? dir_alias用于取得BFILE定位符所對(duì)應(yīng)的目錄別名,FILENAME取得BFILE定位符所對(duì)應(yīng)的文件名.
? declare
??? dir_alias varchar2(20);
??? filename varchar2(50);
??? file_loc bfile;
? begin
??? select filename into file_loc from bfile_table where fno=&no;
??? dbms_lob.filegetname(file_loc,dir_alias,filename);
??? dbms_output.put_line('目錄別名:'||dir_alias);
??? dbms_output.put_line('文件名:'||filename);
? end;

? 12函數(shù)fileeisopen
? 用于確定BFILE所對(duì)應(yīng)的OS文件是否已經(jīng)打開
??? dbms_lob.fileisopen(file_loc in bfile) return integer;
? 如果已經(jīng)被打開,則返回1,否則返回0
? declare
??? file1 bfile;
? begin
??? file1:=bfilename('G','readme1.doc');
??? if dbms_lob.fileisopen(file1)=0 then
?????? dbms_output.put_line('文件未打開');
??? else
?????? dbms_output.put_line('文件已經(jīng)打開');
??? end if;
? end;

? 13.過程FILEOPEN
? 用于打開BFILE所對(duì)應(yīng)的OS文件
? dbms_lob.fileopen(
???? file_loc in out nocopy bfile,
???? open_mode in binary_integer:=file_readonly);
? open_mode用于指定文件的打開模式,OS文件只能以只讀方式打開.
? declare
??? file1 bfile;
? begin'
??? file1:=bfilename('G','readme.doc');
??? if dbms_lob.fileexists(file1)=1 then
?????? dbms_lob.fileopen(file1);
?????? dbms_output.put_line('文件已經(jīng)被打開');
??? end if;
??? dbms_lob.fileclose(file1);
? end;

? 14.過程freetemporary
? 用于釋放在默認(rèn)臨時(shí)表空間中的臨時(shí)LOB
????? dbms_lob.freetemporary(
???????? lob_loc in out nocopy blob/clob/nclob);
? LOB_LOC指定LOB定位符
? declare
??? src_lob clob;
? begin
??? dbms_lob.createtemporary(src_lob,true);
??? src_lob:='中國';
??? dbms_lob.freetemporary(src_lob);
? end;

? 15函數(shù)GETCHUNKSIZE
? 當(dāng)建立包含CLOB列或BLOB列的表時(shí),通過指定CHUNK參數(shù)可以指定操作LOB需要分配的字節(jié)數(shù)(數(shù)據(jù)塊的整數(shù)倍).
? 通過使用函數(shù)GETCHUNKSIZE可以取得CHUNK參數(shù)所對(duì)應(yīng)的值
? dbms_lob.getchunksize(lob_loc in blob/clob/nclob) return integer;
?
? declare
??? src_lob clob;
??? chunksize int;
? begin
??? src_lob:='中國';
??? chunksize:=dbms_lob.getchunksize(src_lob);
??? dbms_output.put_line('chunk 尺寸:'||chunksize);
? end;

? 16.函數(shù)getlength
? 用于取得LOB數(shù)據(jù)的實(shí)際長度.適用于CLOB和BLOB.BFILE類型
? dbms_lob.getlength(lob_loc blob/clob/nclob) return integer;
? declare
??? file1 bfile;
??? length int;
? begin
??? file1:=bfilename('G','readme.doc');
??? length:=dbms_lob.getlength(file1);
??? dbms_output.put_line('文件長度:'||length);
? end;

? 17.函數(shù)instr????
? 用于返回特定樣式數(shù)據(jù)在LOB中從某偏移位置開始第N次出現(xiàn)時(shí)的具體位置,適合于BLOB,CLOB,BFILE類型
? dbms_lob.instr(lob_loc in blob/clob/nclob/bfile,pattern in raw/varchar2,
???????????????? offset in integer:=1,nth in integer:=1) return integer;
? pattern指定要搜索的字符串或二進(jìn)制數(shù)據(jù),OFFSET指定搜索的起始位置,NTH指定第N次的出現(xiàn)次數(shù)
? declare
??? src_lob clob;
??? location int;
??? offset int;
??? occurence int;
? begin
??? src_lob:='中國中國中國中國中國';
??? offset:=2;
??? occurence:=2;
??? location:=dbms_lob.instr(src_lob,'中國',offset,occurence);
??? dbms_output.put_line('從第'||offset||'字符開始,中國第'||occurence||'次出現(xiàn)的具體位置:'||location);
? end;

? 18.函數(shù)isopen
? 用于確定LOB是否已經(jīng)被打開,適用于BLOB,CLOB,BFILE類型
? dbms_lob.isopen(lob_loc in blob/clob/bfile) return integer;
? 已經(jīng)打開返回1,否則返回0
? declare
??? src_lob clob;
? begin
??? src_lob:='中國,中國,偉大的中國');
??? if dbms_lob.isopen(src_lob)=0 then
?????? dbms_lob.open(src_lob,1);
??? end if;
??? dbms_lob.close(src_lob);
? end;

? 19.函數(shù)istemporary
? 用于確定LOB定位符是否為臨時(shí)LOB
? dbms_lob.istemporary(lob_loc in blob/clob/nclob) return integer;
? 是臨時(shí)LOB,則返回1,否則返回0
? declare
??? src_lob clob;
? begin
??? if dbms_lob.istemporary(src_lob)=1 then
?????? dbms_output.put_line('已經(jīng)是臨時(shí)LOB');
??? else
?????? dbms_output.put_line('臨時(shí)LOB需要建立');
?????? dbms_lob.createtemporary(src_lob,true);
??? end if;
??? dbms_lob.freetemporary(src_lob);
? end;

? 20.過程LOADFROMFILE
? 用于將BFILE的部分或者全部內(nèi)容復(fù)制到目標(biāo)LOB變量(CLOB,BLOB)中
? dbms_lob.loadfromfile(dest_lob in out nocopy blob/clob,
??????????????????????? src_file in bfile,amount in integer,
??????????????????????? dest_offset in integer:=1,src_offset in integer:=1);
? src_file指定BFILE定位符,數(shù)據(jù)裝載時(shí)不進(jìn)行字符集轉(zhuǎn)換.
? declare
??? src_lob bfile;
??? dest_lob clob;
??? amount int;
? begin
??? src_lob:=bfilename('g','a.txt');
??? dbms_lob.createtemporary(dest_lob,true);
??? dbms_lob.fileopen(src_lob,0);
??? amount:=dbms_lob.getlength(src_lob);
??? dbms_lob.loadfromfile(dest_lob,src_lob,amount);
??? dbms_lob.fileclose(src_lob);
??? dbms_lob.freetemporary(dest_lob);
? end;

? 21.過程LOADBLOBFROMFILE
? 將BFILE數(shù)據(jù)裝載到BLOB中,并且在裝載后可以取得新的偏移位置
? dbms_lob.loadblobfromfile(dest_lob in out nocopy blob,src_bfile in bfile,
??????????????????????????? amount in integer,dest_offset in out integer,src_offset in out integer);
? src_bfile指定BFILE定位符,DEST_OFFSET(IN)指定目標(biāo)LOB的起始位置,DEST_OFFSET(OUT)取得裝載后的偏移位置
? SRC_OFFSET(IN)指定BFILE定位符的起始位置,SRC_OFFSET(OUT)取得BFILE讀取完成后的偏移位置.
? declare
??? src_lob bfile;
??? dest_lob blob;
??? amount int;
??? src_offset int:=1;
??? dest_offset int:=1;
? begin
??? src_lob:=bfilename('G','a.txt');
??? dbms_lob.createtemporary(dest_lob,true);
??? dbms_lob.fileopen(src_lob,0);
??? amount:=dbms_lob.getlength(src_lob);
??? dbms_lob.loadblobfromfile(dest_lob,src_lob,amount,dest_offset,src_offset);
??? dbms_lob.fileclose(src_lob);
??? dbms_lob.freetemporary(dest_lob);
??? dbms_output.put_line('新的偏移位置:'||dest_offset);
? end;

? 22.過程loadclobfromfile
? 將BFILE數(shù)據(jù)裝載到CLOB中.當(dāng)使用該過程裝載到CLOB中,可以指定字符集ID號(hào),并進(jìn)行字符集轉(zhuǎn)換.
? dbms_lob.loadclobfromfile(
??????????????????????????? dest_lob in out nocopy clob,
??????????????????????????? src_bfile in bfile,amout in integer,
??????????????????????????? dest_offset in out integer,
??????????????????????????? src_offset in out integer,
??????????????????????????? src_csid in number,?????????????????? --指定源文件的字符集ID號(hào)
??????????????????????????? lang_context in out integer,????????? --指定語言上下文(IN)取得先前裝載語言上下文(OUT)
??????????????????????????? warning out integer);
? declare
??? src_lob bfile;
??? dest_lob clob;
??? amount int;
??? src_offset int:=1;
??? csid int:=0;
??? lc int:=0;
??? warning int;
? begin
??? src_lob:=bfilename('G','a.txt');
??? dbms_lob.createtemporary(dest_lob,true);
??? dbms_lob.fileopen(src_lob,0);
??? amount:=dbms_lob.getlength(src_lob);
??? dbms_lob.loadclobfromfile(dest_lob,src_lob,amount,dest_offset,src_offset,csid,lc,warning);
??? dbms_lob.fileclose(src_lob);
??? dbms_output.put_line(dest_lob);
??? dbms_lob.freetemporary(dest_lob);
? end;

? 23.過程OPEN
? 在打開LOB時(shí)指定LOB的讀寫模式.只讀(DBMS_LOB.LOB_READONLY)? 讀寫(DBMS_LOB.LOB_READWRITE)
? 使用于BLOB,CLOB,BFILE
? dbms_lob.open(lob_loc in out nocopy blob/clob/bfile,open_mode in binary_integer);
?
? declare
??? src_lob clob;
??? v1 varchar2(100):='中國';
??? amount int;
? begin
??? amount:=length(v1);
??? dbms_lob.createtemporary(src_lob,true);
??? dbms_lob.open(src_lob,dbms_lob.lob_readwrite);
??? dbms_lob.write(src_lob,amount,1,v1);
??? dbms_lob.colse(src_lob);
??? dbms_output.put_line(src_lob);
??? dbms_lob.freetemporary(src_lob);
? end;

? 24.過程READ
? 用于將LOB數(shù)據(jù)讀取到緩沖區(qū),適用于BLOB,CLOB,BFILE
? DBMS_LOB.READ(LOB_LOC IN BLOB/CLOB/BFILE,AMOUT IN OUT NOCOPY BINARY_INTEGER,
??????????????? OFFSET IN INTEGER,BUFFER OUT RAW/VARCHAR2);
? declare
??? src_lob clob:='偉大的中國';
??? amount int;
??? buffer varchar2(200);
??? offset int:=1;
? begin
??? amount:=dbms_lob.getlength(src_lob);
??? dbms_lob.open(src_lob,dbms_lob.lob_readonly);
??? dbms_lob.read(src_lob,amount,offset,buffer);
??? dbms_output.put_line(buffer);
??? dbms_lob.close(src_lob);
? end;
?
? 25.函數(shù)SUBSTR
? 用于返回LOB中從指定位置開始的部分內(nèi)容,適用BLOB,CLOB,BFILE
? dbms_lob.substr(lob_loc in blob/clob/bfile,aount in integer:=32767,offet in integer:=1) return raw;
? declare
??? src_lob clob:='中國,中國，偉大的中國';
??? amount int;
??? v1 varchar2(200);
??? offset int;
? begin
??? amount:=10;
??? offset:=4;
??? v1:=dbms_lob.substr(src_lob,amout,offset);
??? dbms_output.put_line(v1);
? end;

? 26.過程trim
? 用于截?cái)郘OB內(nèi)容到指定長度,只適用BLOB和CLOB,不適用于BFILE
? dbms_lob.trim(lob_loc in out nocopy blob/clob/nclob,newlen in integer);
? newlen用于指定截?cái)嗪蟮腖OB長度
? declare
??? src_lob clob:='中國,中國,偉大的中國';
??? amount int;
? begin
??? amout:=5;
??? dbms_lob.trim(src_lob,amout);
??? dbms_output.put_line(src_lob);
? end;

? 27過程write
? 用于將緩沖區(qū)數(shù)據(jù)寫入到LOB中的特定位置,只適用BLOB,CLOB,不適用BFILE
? dbms_lob.write(lob_loc in out nocopy blob/clob,amout in binary_integer,
???????????????? offset in integer,buffer in raw/varchar2);
? buffer用于指定要寫入的內(nèi)容
? declare
??? src_lob clob:='我的祖國';
??? amount int;
??? offset int;
??? buffer varchar2(100):=',偉大的中國':
? begin
??? offset:=dbms_lob.getlength(src_lob)+1
??? amount:=length(buffer);
??? dbms_lob.write(src_lob,amout,offset,buffer);
??? dbms_output.put_line(src_lob);
? end;

? 28.過程WRITEAPPEND
? 用于將緩沖區(qū)數(shù)據(jù)寫人到LOB尾部,只適用于BLOB,CLOB,不適用BFILE
? DBMS_LOB.WRITEAPPEND(LOB_LOC IN OUT NOCOPY BLOB/CLOB/NCLOB,AMOUT IN BINARY_INTEGER,BUFFER IN RAW);
?
? declare
??? src_lob clob:='我的祖國';
??? amount int;
??? buffer varchar2(100):=',偉大的中國';
? begin
??? amount:=length(buffer);
??? dbms_lob.writepaaend(src_lob,amount,buffer);
??? dbms_output.put_line(src_lob);
? end;

?

? 14.3訪問LOB
? 1.建立包含CLOB列的表
? create table lob_example1(id number(6) primary key,name varchar2(10),resume clob);

? 2.初始化CLOB列
? 使用函數(shù)EMPTY_CLOB()初始化CLOB列,分配了LOB定位符
? insert into lob_example1 values(1,'王名',empty_clob());
? insert into lob_example1 values(2,'馬麗',empty_clob());
? commit;

? 3.更新CLOB列的數(shù)據(jù)
? write? writeappend
? 如果要更新CLOB列的數(shù)據(jù),那么在檢索CLOB列時(shí)就必須帶有FOR UPDATE子句
? declare
??? lob_loc clob;
??? text varchar2(200);
??? amount int;
??? offset int;
? begin
??? select resume into lob_loc from lob_example1 where id=&id for update;
??? offset:=dbms_lob.getlength(lob_loc)+1;
??? text:='&resume';
??? amount:=length(text);
??? dbms_lob.write(lob_loc,amount,offset,text);
??? commit;
? end;

? 4.讀取CLOB列的數(shù)據(jù)
? 為了讀取CLOB列的所有數(shù)據(jù),應(yīng)該使用循環(huán)方式進(jìn)行處理(READ)
? declare
??? lob_loc clob;
??? buffer varchar2(200);
??? amount int;
??? offset int;
? begin
??? select resume into lob_loc from lob_example1 where id=&id;
??? offset:=6;
??? amount:=dbms_lob.getlength(lob_loc);
??? dbms_lob.read(lob_loc,amount,offset,buffer);
??? dbms_output.put_line(buffer);
? end;

? 5.將文本內(nèi)容寫入到CLOB列
? 為了避免字符集問題,建議使用LOADCLOBFROMFILE
? declare
??? lobloc clob;
??? fileloc bfile;
??? amount int;
??? src_offset int:=1;
??? dest_offset int:=1;
??? csid int:=0;
??? lc int:=0;
??? warning int;
? begin
??? fileloc:=bfilename('G','馬麗.txt');
??? dbms_lob.fileopen(fileloc,0);
??? amount:=dbms_lob.getlength(fileloc);
??? select resume into lobloc from lob_example1 where id=2 for update;
??? dbms_lob.loadclobfromfile(lobloc,fileloc,amount,dest_offset,src_offset,csid,lc,warning);
??? dbms_lob.fileclose(fileloc);
??? commit;
? end;

?
? 6.將clob列內(nèi)容寫入到文本文件
? 要將CLOB列的內(nèi)容寫入到文本文件時(shí),不僅需要使用DBMS_LOB包讀取CLOB列的內(nèi)容,而且需要使用UTL_FILE包建立文本文件并寫入內(nèi)容
? declare
??? lobloc clob;
??? amount int;
??? offset int:=1;
??? buffer varchar2(2000);
??? handle UTL_FILE.FILE_TYPE;
? begin
??? select resume into lobloc from lob_example1 where id=&id;
??? amount:=dbms_lob.getlength(lobloc);
??? dbms_lob.read(lobloc,amount,offset,buffer);
??? handle:=utl_file.fopen('user_dir','a.txt','w',2000);
??? utl_file.put_line(handle,buffer);
??? utl_file.fclose(handle);
?
? 14.4.訪問BLOB(二進(jìn)制數(shù)據(jù))
? 1.建立包含BLOB列的表
? create table lob_example2(id number(6) primary key,name varchar2(10),photo blob);
?
? 2.初始化BLOB列
? 使用函數(shù)EMPTY_BLOB()初始化BLOB列
? insert into lob_example2 values(1,'王名',empty_blob());
? insert into lob_example2 values(2,'馬麗',empty_blob());
? commit;

? 3.將二進(jìn)制文件內(nèi)容寫入到BLOB列
? loadblobfromfile
? declare
??? lobloc blob;
??? fileloc bfile;
??? amount int;
??? src_offset int:=1;
??? dest_offset int:=1;
? begin
??? select photo into lobloc from lob_example2 where id=&id for update;
??? fileloc:=bfilename('G','&filename');
??? dbms_lob.fileopen(fileloc,0);
??? amount:=dbms_lob.getlength(fileloc);
??? dbms_lob.loadblobfromfile(lobloc,fileloc,amount,dest_offset,src_offset);
??? dbms_lob.fileclose(fileloc);
??? commit;
? end;
?
? 4.讀取BLOB列數(shù)據(jù)
? 因?yàn)锽LOB列中存放著二進(jìn)制數(shù)據(jù),當(dāng)讀取數(shù)據(jù)時(shí)應(yīng)該使用RAW變量接受其數(shù)據(jù)READ
? declare
??? lobloc blob;
??? buffer raw(2000);
??? amount int;
??? offset int:=1;
? begin
??? select photo into lobloc from lob_example2 where id=&id;
??? amount:=dbms_lob.getlength(lobloc);
??? dbms_lob.read(lobloc,amount,offset,buffer);
? end;
? 讀取到BUFFER中,如果大小大于2000字節(jié),需要使用循環(huán)方式讀取數(shù)據(jù).

? 5.將BLOB列的內(nèi)容寫入到二進(jìn)制文件
? 不僅需要使用DBMS_LOB包讀取BLOB列的內(nèi)容,而且需要使用UTL_FILE包建立二進(jìn)制文件并寫入內(nèi)容
? declare
??? lobloc blob;
??? amount int;
??? offset int:=1;
??? buffer raw(1000);
??? handle utl_file.file_type;
? begin
??? select photo into lobloc from lob_example2 where id=&id;
??? amount:=dbms_lob.getlength(lobloc);
??? dbms_lob.read(lobloc,amount,offset,buffer);
??? handle:=utl_file.fopen('user_dir','a.bmp','w',1000);
??? utl_file.put_raw(handle,buffer);? ---寫入到文本文件
??? utl_file.fclose(handle);
? end;

? 14.5訪問BFILE
? BFILE存放著指向OS文件的指針,所對(duì)應(yīng)的OS文件內(nèi)容只能讀取,不能修改.
? 當(dāng)ORACLE數(shù)據(jù)庫中使用BFILE類型訪問OS文件時(shí),必須首先建立DIRECTORY對(duì)象,而建立DIRECTORY對(duì)象則要求用戶必須具有CREATE ANY DIRECTORY權(quán)限.
? conn system/manager
? grant create any directory to scott;
? conn scott/tiger
? create directory bfile_dir as 'G:\BFILE_EXAMPLE';
? 當(dāng)建立DIRECTTORY對(duì)象時(shí),一定要確保OS目錄已經(jīng)存在.? 1.建立包含BFILE列的表
? create table lob_example3(
??? id number(6) primary key,name varchar2(10),resume bfile);
? 2.初始化BFILE列
? 使用函數(shù)BFILENAME()來初始化BFILE列
? 當(dāng)使用BFILENAME()函數(shù)時(shí),DIRECTORY對(duì)象必須使用大寫格式
? insert into lob_example3 values(1,'王名',bfilename('BFILE_DIR','王名.TXT'));
? insert into lob_example3 values(2,'馬麗',bfilename('BFILE_DIR','王麗.TXT'));
? 3.讀取BFILE列的內(nèi)容
? 使用DBMS_LOB包的READ過程,應(yīng)該使用RAW變量接受其讀出的數(shù)據(jù)
? declare
??? buffer raw(2000);
??? amount int;
??? offset int;
??? lobloc bfile;
? begin
??? select resume into lobloc from lob_example3 where id=&id;
??? dbms_lob.fileopen(lobloc,0);
??? amount:=dbms_lob.getlength(lobloc);
??? offset:=1;
??? dbms_lob.read(lobloc,amount,offset,buffer);
??? dbms_lob.fileclose(lobloc);
? end;
?

oracle pl/sql 基礎(chǔ)