# FileName :oracle_pl/sql.txt
# Write By : hgc 整理
# Create Date : 2007-07-03
# Last Modify : 2007-07-25
# Description :介紹Oracle 10G pl/sql
# source: 精通 oracle 10G pl/sql 編程
第三章 pl/sql基礎
3.1
pl/sql塊有三個組成部分﹐定義﹐執行﹐例外處理三部分。
Declare
/*
* 定義部分﹐定義變量﹑常量﹑復雜數據類型﹑游標﹑例解
*/
Begin
/*
* 執行部分 pl/sql語句和sql語句
*/
Exception
/*
*例外處理部分 處理運行錯誤
*/
pl/sql塊分類﹕
匿名塊﹑命名塊﹑子程序﹑觸發器
子程序包括過程﹑函數﹑包
3.2 定義和使用變量
pl/sql的變形量類型﹐標量﹑復合類型﹑參照類型﹑lob類型
標量類型﹕int﹑integer﹑number﹑date﹑boolean.....
復合類型﹕record﹑table﹑varray
參照類型﹕ref cursor﹑ref object_type
lob類型 : bfile﹑blob﹑clob﹑nclob
set serveroutput on
--example 1 pl/sql record :
declare
type emp_record_type is record (
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
emp_record emp_record_type;
begin
select ename,sal,job into emp_record
from emp where empno = '7788';
dbms_output.put_line('顧員名﹕'||emp_record.name);
end;
--example 2 pl/sql 表:
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=7788;
dbms_output.put_line('顧員名﹕'||ename_table(-1));
end;
--example 3 參照變量
Declare
type c1 is ref cursor;
emp_cursor c1;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor for
select ename,sal from emp where deptno=10;
LOOP
Fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename);
END LOOP;
close emp_cursor;
end;
第四章 使用sql語句
--exapmle 4 多表插入
insert all
when deptno=10 then into dept10
when deptno=20 then into dept20
when deptno=30 then into dept30
when job='CLERK' then into clerk
else into other
select * from emp;
--exapmle 5 使用FIRST操作符執行多表插入
insert first
when deptno=10 then into dept10
when deptno=20 then into dept20
when deptno=30 then into dept30
when job='CLERK' then into clerk
else into other
select * from emp;
4.4 數據分組
--exapmle 6使用rollup操作
select deptno,job,avg(sal) from emp
group by rollup(deptno,job);
--exapmle 7使用cube操作
select deptno,job,avg(sal) from emp
group by cube(deptno,job);
--exapmle 7使用grouping set
select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);
--example等連接
select e.ename,e.sal,d.dname from emp e,dept d
where e.deptno=d.deptno;
--example 不等連接
select a.ename,a.sal,b.grade from emp a,salgrade b
where a.sal between b.losal and b.hisal;
--example 自連接
select manager.ename from emp manager,emp worker
where manager.empno=worker.mgr and worker.ename='BLAKE';
--example 內連接
語法﹕
select table1.column,table2.column
from table1 [inner|left|right|full] join table2 table1.column=table2.column
內連接
select a.dname,b.ename from dept a,emp b
where a.deptno=b.deptno and a.deptno=10;
select a.dname,b.ename from dept a inner join emp b
on a.deptno=b.deptno and a.deptno=10;
在oracle9i中﹐如果主表的主鍵列和從表的外部鍵列名稱相同﹐那么可以使用natural join關
鍵字自動執行內連接操作﹐
select dname,ename from dept natural join emp;
左外連接
select a.dname,b.ename from dept a left join emp b
on a.deptno=b.deptno and a.deptno=10;
右外連接
select a.dname,b.ename from dept a right join emp b
on a.deptno=b.deptno and a.deptno=10;
完全外連接
select a.dname,b.ename from dept a full join emp b
on a.deptno=b.deptno and a.deptno=10;
--exapmle 8 多行子查詢
in :匹配于子查詢結果的任一個值即可
all:必須要符合子查詢結果的所有值。
any 只要符合子查詢結果的任一個值即可
in操作符
select ename,job,sal deptno from emp where job in (select distinct job from emp where deptno=10);
all操作符
select ename,job,sal deptno from emp where sal>all (select distinct sal from emp where deptno=30);
any操作符
select ename,job,sal deptno from emp where sal>any (select distinct sal from emp where deptno=30);
--exapmle 9 case表達式
select ename,sal, case
when sal>3000 then 3
when sal>2000 then 2
else 1 end grade from emp ;
--exapmle 9 with 表達式 ,只支持select http://searchdatabase.techtarget.com.cn/tips/311/2607811.shtml
with summary as (
select dname,sum(sal) as dept_total from emp,dept
where emp.deptno=dept.deptno group by dname
)
select dname,dept_total from summary where dept_total>
(select sum(dept_total)*1/3 from summary);
WITH
SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),
OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K
FROM OBJ O, SEG S
WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+);
WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
Q2 AS (SELECT 3 * 5 M FROM DUAL),
Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;
查看歷史數據
AS OF子句可以查看過去的歷史數據(5分鐘之前的)
select ename,sal from emp as of timestamp to_timestamp(
'2003-05-18 19:59:00','YYYY-MM-DD HH24:MI:SS') WHERE ENAME='CLERK';
使用dbms_flashback包獲取特定的scn的數據。
exec dbms_flashback.enable_at_system_change_number(717402);
select * from emp;
exec dbms_flashback.disable;
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=80 where deptno=v_deptno;
else
dbms_output.put_line('不存在該部門');
end case;
end;
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 then
update emp set comm=50 where ename=v_ename;
end case;
end;
--在values子句中使用記錄變量
declare
dept_record dept%rowtype;
begin
dept_record.deptno :=50;
dept_record.dname :='asdfasdf';
dept_record.loc :='asdfasdfsdf';
insert into dept values dept_record;
end;
--在set子句中使用記錄變量
declare
dept_record dept%rowtype;
begin
dept_record.deptno :=30;
dept_record.dname :='sales';
dept_record.loc :='asdfasdfsdf';
update dept set row=dept_record where deptno=30;
end;
在索引表中使用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=7788;
dbms_output.put_line('雇員名:'||ename_table(-1));
end;
在索引表中使用varchar2 ,oracle9i才允許
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('第一個元素﹕'||area_table.first);
dbms_output.put_line('最后一個元素﹕'||area_table.last);
end;
在pl/sql中使用嵌套表﹐
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
ename_table :=ename_table_type('MAY','MARY','MARRY');--這個必須要的﹐初始化動作
SELECT ENAME INTO ENAME_TABLE(2) FROM EMP
WHERE EMPNO=7788;
DBMS_OUTPUT.PUT_LINE('雇員名﹕'|| ENAME_TABLE(2));
end;
在pl/sql中使用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=7788;
DBMS_OUTPUT.PUT_LINE('雇員名﹕'||ename_table(1) );
END;
pl/sql記錄表 多個欄位
DECLARE
TYPE emp_table_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=7788;
dbms_output.put_line('雇員名﹕'||emp_table(1).ENAME);
dbms_output.put_line('雇員名﹕'||emp_table(1).SAL);
END;
在pl/sql中多維varray
DECLARE
-- 一維定義
TYPE AL_VARRAY_TYPE IS VARRAY(10) OF INT;
-- 二維定義
TYPE nal_varray_type is VARRAY(10) OF al_varray_type;
--初始化
nal nal_varray_type :=nal_varray_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_varray_type(2,4));
BEGIN
FOR I in 1..nal.count LOOP
FOR j in 1..nal(i).count LOOP
DBMS_OUTPUT.PUT_LINE('NVL('||I||','||J||')='
||nal(i)(j));
END LOOP;
END LOOP;
END;
--在pl/sql中使用多級嵌套表
DECLARE
--一級定義
TYPE al_table_type IS TABLE OF INT;
--二級定義
TYPE nal_table_type IS TABLE OF al_table_type;
--初始化
nal nal_table_type :=nal_table_type(
al_table_type(2,4),
al_table_type(5,73));
BEGIN
FOR I IN 1..nal.count LOOP
FOR J IN 1..nal(i).COUNT LOOP
DBMS_OUTPUT.PUT_LINE('NVL('||I||','||J||')='
||nal(i)(j));
END LOOP;
END LOOP;
END;
--在pl/sql中使用多級嵌套表類似多維數組
DECLARE
--一級定義
TYPE al_table_type IS TABLE OF INT
INDEX BY BINARY_INTEGER;
--二級定義
TYPE nal_table_type IS TABLE OF al_table_type
INDEX BY BINARY_INTEGER;
nal nal_table_type;
--初始化
BEGIN
nal(1)(1) :=1;
nal(1)(2) :=2;
nal(2)(1) :=2;
nal(2)(2) :=4;
nal(1)(3) :=4;
FOR I IN 1..nal.count LOOP
FOR J IN 1..nal(i).COUNT LOOP
DBMS_OUTPUT.PUT_LINE('NVL('||I||','||J||')='
||nal(i)(j));
END LOOP;
END LOOP;
END;
--集合方法﹕
集合方法是oracle提供用于操作集合變量的內置的函數和過程﹐exist,count,limit,first,next,prior和next是函數﹐
extend,trim,delete則是過程。
注意集合方法只用于pl/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('') then
ename_table(1) :='scott';
Else
dbms_output.put_line('必須初始化');
End if;
End;
2. count 用于返回集合變量中的元素的總個數。如果元素個數為null,則統計不會包含。
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):='ascott';
ename_table(5):='sacott';
ename_table(10):='sasdcott';
ename_table(-8):='scotasdt';
dbms_output.put_line('集合元素個數﹕'||ename_table.count);
End;
3. Limit 用于返回集合元素的最大個數﹐因嵌套表和索相表的個數沒有限制﹐
所以返回null,對于varray則返回最大個數
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('集合的最大個數﹕'||ename_table.limit);
End;
4. First和Last
First方用于返合集合變量的第一個元素的下標﹐相反Last則返回集合變量的最后一個元素的下標.
Declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
Begin
ename_table(-5) :='adfadf-5';
ename_table(1) :='adfadf1';
ename_table(6) :='adfadf6';
ename_table(10) :='adfadf10';
ename_table(12) :='adfadf6';
Dbms_output.put_line('第一個元素:'||ename_table.first);
Dbms_output.put_line('最后一個元素:'||ename_table.last);
End;
5. prior和next
prior方法返回當前集合元素的前一個元素的下標﹐相反next返回當前集合元素的下一個元素的下標。
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) :='scott';
dbms_output.put_line('元素5的前一個元素﹕'||ename_table.prior(5));
dbms_output.put_line('元素5的后一個元素﹕'||ename_table.next(5));
End;
6. extend
該方法用于擴展集合變量的尺示﹐并為它增加元素﹐適用于嵌套表和varray.該方法有extend,extend(n)和extend(n,i)三種方式。
其中extend用于為集合增加一個null元素﹐extend(n)則為集合增加n個null元素。extend(n,i)則為集合增加n個元素﹐元素值與第i個元素相同。
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('元數總個數﹕'||ename_table.count);
end;
7. trim
該方法用于從集合尾部刪除元數﹐有trim和trim(n)兩種格式﹐trim用于從集合尾部刪除一個元素﹐
而trim(n)用于從集合尾部刪除n個元素。該方法只適于嵌套表和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','A');
ename_table.trim(2);
dbms_output.put_line('元數總個數:'||ename_table.count);
End;
8. Delete
該方法用于刪除元素﹐只適用于嵌套表和索引表﹐而不適用varray.有delete,delete(n),delete(m,n)三種格式
delete用于刪除所有元素﹐delete(n)刪除集合變量的第n個元素﹐而delete(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) :='scotta';
ename_table(1) :='scottb';
ename_table(5) :='scottc';
ename_table(10) :='scottd';
ename_table.delete(5);
dbms_output.put_line('元數總個數﹕'||ename_table.count);
End;
集合賦值
使用嵌套表和varray時﹐通過執行insert,update,fetch,select,賦值語句﹐用戶可以將一個集合的數據賦給另一個集合
從oracle10G始﹐還可以用set,multiset union,multiset intersect,multiset except等集合操作符。
set操作用于取消嵌套表中的重復值;
multiset union 用于取得兩個嵌套表的并集(帶有distinct操作符可以取消重復結果)﹔
multiset intersect 用于取得兩個集合的交集﹔
multiset except 用于取得兩個嵌套表的差集﹐
1. 將一個集合的值賦給別一集合
exp1
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('name_array2的原數據﹕');
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的新數據﹕');
for i in 1.. name_array2.count loop
dbms_output.put_line(' '||name_array2(i));
end loop;
dbms_output.new_line;
End;
注意﹐當進行集合賦值時﹐源集合與目標集合的數據類型必須一致。如果集合元數數據類型一致﹐但集合類型不一致﹐也不能進行
賦值﹐
exp 錯誤的例子
Declare
type name_varray1_type is varray(4) of varchar2(10);
type name_varray2_type is varray(4) of varchar2(10);
name_array1 name_varray1_type;
name_array2 name_varray2_type;
Begin
name_array1 :=name_varray1_type('scott','smith');
name_array2 :=name_array1;
End;
2. 給集合null值
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原有元素的個數﹕'||name_array.count);
name_array :=name_empty;
IF name_array is null then
dbms_output.put_line('name_array現有元素的個數﹕0');
END IF;
End;
3. 使用集合操作符給嵌套表賦值
(1)使用Set操作符
set 操作符用于取消特定嵌套表中的重復值﹐
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操作符
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操作符
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操作符
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操作符
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;
比較集合
從oracle10G開﹐開發人沒不僅可能檢查集合變量是否為null,還可以使用cardinality,submultiset of member of ,is a set, is empty等集合操作符﹐
cardinality用于返回集合變量的元素的個數﹔
submultiset of :用于確定一個嵌套表是否為另一嵌套表的子集﹔
member of 用于檢測特定數據是否為嵌套表的元素﹔
is a set 用于檢測嵌套表是否包含重復的元數值﹔
is empty用于檢測是否為null﹐只適用于嵌套表而不適于varray;
(1)檢測是否為null;
Declare
type name_array_type is array(3) of varchar2(10);
name_array name_array_type;
Begin
if name_array is null then
dbms_output.put_line('name_array 末初始化');
end if;
End;
Declare
type name_array_type is table of varchar2(10);
name_array name_array_type;
Begin
if name_array is empty then
dbms_output.put_line('name_array 末初始化');
end if;
End;
(2)比較兩個嵌套表是否相同
使用這兩相只能針對嵌套表﹐而不能是索引表和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('兩個嵌套表完全相同');
else
dbms_output.put_line('兩個嵌套表數值不同');
end if;
End;
(3) 在嵌套表中使用集合操作符(只適于嵌套表﹐不適行索引表和varray)
1. cardinality 用于返回集合元數的個數
Declare
type nt_table_type is table of number;
nt1 nt_table_type :=nt_table_type(1,2,3,4);
Begin
dbms_output.put_line('元數個數﹕'||cardinality(nt1));
End;
2.使用submultiset of
用于確定一個嵌套表是否為另一嵌套表的子集﹐
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;
3. 使用操作符member of
用于檢測特定數據是否為嵌套表的元素。
Declare
type nt_table_type is table of number;
nt1 nt_table_type :=nt_table_type(1,2,3,5);
v1 number :=1;
Begin
if v1 member of nt1 then
dbms_output.put_line('v1 是nt1的元素');
end if;
End;
4. 使用is a set
用于檢測嵌套表是否包含重復的元數值﹐
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沒有重復值');
end if;
End;
8.3 批量綁定
為9i所新增特性
create table demo(
id number(6) primary key,
name varchar2(10)
);
exp1 不使用批量綁定處理
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('總計時間(秒)﹕'||
to_char((end_time-start_time)/100));
End;
exp2 使用批量綁定
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('總計時間(秒)﹕'||
to_char((end_time-start_time)/100));
End;
結論﹐批量綁定效果明顯優于不使用批量綁定﹐批量綁定使用bulk connect子句和forall語句來完成﹐其中bulk connect 來取得批量數據﹐
該子句只能用于select 語句﹐fetch語句和dml返回子句中;而forall只適于執行批量綁定的dml操作。
8.3.1 forall語句
當要在pl/sql應用程序執行insert,delete,update操作時﹐可以使用forall語句﹐在9i時﹐必須具有連續的元素﹐從10G開始﹐通過使用新增加的indices of 子句和values of子句
可以使用連續的集合元素。從10G開始有三種執行方法
語法一﹕
Forall index in lower_bound ..upper_bound
sql_statement;
語法二﹕
forall index in indices of collection
[between lower_bound 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..5000 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..5000 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) :=i;
end loop;
forall i in 8..10
insert into demo (id) values (id_table(i));
End;
5. 在forall語句上使用indices of子句。
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 demo where id=id_table(i);
End;
6. 在forall語句上使用values of子句 --例子有誤。。。
values of子句是10G新增的功能﹐用于從集合變量中取得集合下標(index)的值﹐
create table new_demo as select * from demo where 'a'='b';
Declare
type id_table_type is table of demo.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(2,4,6);
--forall i in values of index_pointer --錯誤的
--insert into new_demo values(id_table(i),name_table(i)); --錯誤的
forall i in id_table.first ..id_table.last
insert into new_demo values(id_table(i),name_table(i));
End;
8.3.2 BULK CONNECT子句
bulk connect 子句用于取得批量數據﹐適用于select into﹐fetch into,和dml返回語句。可以將批量數據放在pl/sql集合孿量中﹐
語法﹕bulk connect into collection_name[,connect_name]...
1. 在select into語句中使用bulk connect子句
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;
for i in 1..emp_table.count loop
dbms_output.put_line('顧員姓名'||emp_table(i).ename);
end loop;
End;
2. 在dml的返回語句中使用bulk collect子句
Declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
Begin
delete from emp where dempno=&no
Return ename bulk collect into ename_table;
for i in 1..ename_table.count loop
dbms_output.put(ename_table(i)||' ');
end loop;
dbms_output.new_line;
End;
第9章 使用游標
9.1 顯示游標
1. 使用顯示游標
|----|
Declare -->Open -->Fetch -->空-->close
(1)定義游標
Cursor cursor_name is select_statement;
(2)打開游標
open cursor_name;
(3)提取數據
語法一: fetch cursor_name into vari1,vari2....;
語法二: fetch cursor_name
bulk collect into collect1,collect2....[limit rows];
(4)關閉游標
close cursor_name;
2. 顯示游標屬性(%isopen﹑%found﹑%notfound﹑%rowcount)
當使用顯式游標時﹐必須要在顯示游標屬性之前帶有顯示游標的名樂作前綴(游標名.屬性名)
exp1:
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;
exp2:
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;
exp3:在顯示游標中使用fetch ...bulk connect into ..limit語句提取部分數據
當使用fetch ...bulk connect into ..limit語句提數據時﹐默認為全部﹐如果結果集有大量數據
并且使用varray集合變量接收數據﹐那么必須限定每次提取行數。
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.put('顧員名﹕');
for i in 1..(emp_cursor%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;
-- dbms_output.put_line(emp_cursor%rowcount);
End Loop;
close emp_cursor;
End;
exp4:使用游標屬性
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 --not open cursor
open emp_cursor;
end if;
fetch emp_cursor bulk collect into ename_table;
dbms_output.put_line('提取的總計數﹕'||emp_cursor%rowcount);
close emp_cursor;
End;
exp5: 基于游標變量的記錄孌量
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;
close emp_cursor;
End;
9.2 參數游標
語法﹕
cursor cursor_name (parameter_name datatype) is select _statement;
exp:
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.3 使用游標更新或刪除數據
1. 使用游標更新數據
Declare
Cursor emp_cursor is
select ename,sal from emp for update;
v_ename emp.ename%type;
v_oldsal emp.sal%type;
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. 使用游標刪除數據
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子句在特定表上加行共享鎖
為了只在特定的表上加行共享鎖﹐需要在for update子句后面帶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%rowtype;
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語句對被作用行加鎖﹐如果其他會話已經在被作用行上加鎖﹐那么在默認情況下則一直等待。
通過在for update子句中指定nowait語句﹐可以避免等待﹐當指定了nowait子句之后﹐如果其它會話已在被作用行上加鎖﹐則
會報錯。
9.4 游標for循環
當使用游標for循環時﹐oracle會隱含的打開游標﹐提取游標數據并關閉游標。
for record_name in cursor_naem loop
statement1
statement2
.....
emd loop;
1. 使用游標for循環
Declare
cursor emp_cursor is select ename,sal from emp;
Begin
for emp_record in emp_cursor loop
dbms_output.put_line('第'||emp_cursor%rowcount||'個顧員﹕'||emp_record.ename);
end loop;
End;
2. 在游標for循環中直接使子查詢
Begin
for emp_record in (select ename,sal from emp) loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
9.5 使用游標變量
1. 游標變量使用步驟
(1)定義ref cursor類型和游標變量
type ref_type_naem is ref cursor [return return_type];
cursor_variable ref_type_name;
(2)打開游標
open cursor_variable for select-statement;
(3)提取游標數據
語法一﹕fetch cursor_variable into variable1,variable2....;
語法二﹕fetch cursor_variable bulk collect into collect1,collect2...[limit rows];
(4)關閉游標變量
close cursor_variable;
2:游標變量示例
exp1:
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_cursor%rowcount||'個顧員:'||emp_record.ename);
End loop;
close emp_cursor;
End;
exp2: 在定義ref cursor類型時指定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=10;
Loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line('第'||emp_cursor%rowcount||'個顧員:'||emp_record.name);
End loop;
close emp_cursor;
End;
9.6 使用cursor表達式
cursor表達式是oracle9i的新增加的特性。用于返回嵌套游標﹐
exp:
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_dname,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.3 處理非預定義例外
定義例外----->關聯例外和錯誤------>引用例外
Declare
e_integrity exception;
pragma exception_init(e_integrity,-2291);
Begin
update emp set deptno=&dno where empno=&empno;
Exception
when e_integrity then
dbms_output.put_line('該部門不存在');
End;
10.4 處理自定義例外
自定義例外必須顯示觸發。
定義例外------>顯示觸發例外------->引用例外
Declare
e_integrity exception;
pragma exception_init(e_integrity,-2291);
e_no_employee exception;
Begin
update emp set deptno=&dno where empno=&empno;
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;
10.5 使用例外函數
1. sqlcode和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('錯誤號是:'||sqlcode);
dbms_output.put_line(sqlerrm);
End;
2. raise_application_error
raise_application_error(error_number,message,[{true|false}]);
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,'該顧員無補助');
end if;
Exception
when no_data_found then
dbms_output.put_line('該顧員不存在');
End;
10.6 pl/sql 編譯警告
從oracle10G開始﹐為提高pl/sql子程序的健康壯性并避免錯誤﹐在編譯pl/sql子程序之前開發人員
可激活警告檢查。
1. pl/sql警告分類
Server:用于檢查妯現不可預料的結果或錯誤的結果。如參數的別名問題
performance:用于檢查可能引起的性能問題﹐如insert操作為number提供了varchar2的數據。
informational: 用于檢查子程序中的死代碼。
all:用于檢查所有警告
2. 控制pl/sql警告信息
sql> alter system set plsql_warnings='ENABLE:ALL';
sql> alter session set plsql_warnings='enable:performance';
sql> alter procedure hello_compile plsql_warnings='enable:performance';
sql> alter session set plsql_warnings='disable:all';
sql> alter session set plsql_warnings='enable:server','disable:performance','error:06022';
sql> call dbms_warning.set_warning_setting_string('ENABLE:ALL','SESSION');
第十一章 開發子程序
.....
.....
第十四章 開發動態sql
14.1 動態sql簡介
1. 靜態sql
指直接嵌入在pl/sql塊中的sql語句。用于完成特定的或固定的任務。
exp:
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
insert into emp(empno,ename,sal) valeus(v_empno,v_ename,v_sal);
update emp set sal=v_sal where ename=v_ename;
delete from emp where empno=v_empno;
上四條都是靜態sql,用于完成指定的任務
2.動態sql
指在運行pl/sql塊時動態輸入的sql語句﹐如果要在pl/sql塊中執行ddl語句(如create,alter ,drop語句)﹑dcl語句(grant,revoke)或者里面果執行
更回靈活的sql語句(如select語句中不同的where條件)﹐那就必須是動態sql,
3. 靜態sql與動態sql比較
靜態sql在編寫pl/sql塊時直接嵌入的sql語句﹐而動態sql是在運行pl/sql塊是動態輸入的語句。
靜態sql性能要優于動態sql,因此在編寫pl/sql塊是﹐如功能完全確定則使用靜態sql,不確定則使用動態sql.
4. 動態sql的處理方法
當編寫動態sql時﹐根據要處理sql句的不同﹐可以使用三種不同的動態sql方法
(1) 使用execute immediate 語句
execute immediate 語句可以處理多數動態sql操作﹐但不能處理地多行查詢語句。
(2)使用open-for,fetch和close語句
為了處理動態的多行查詢﹐必須使用open-for語句來打開游標﹐用用fetch循環取數據然后close關閉
(3) 使用批量動態sql
14.2 處理非查詢語句
語法﹕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]...];
1.使用 execute immediate 處理ddl操作
exp:
create or replace procedure drp_table(table_name varchar2)
is
sql_statement varchar2(100);
Begin
sql_statement :='drop table '||table_name;
execute immediate sql_statement;
End;
2.使用 execute immediate 處理dcl操作
exp:
create or replace procedure grant_sys_priv(priv varchar2,usernaem varchar2)
is
sql_stat varchar2(100);
Begin
sql_statement :='grant '||priv ||' to '||username;
execute immediate sql_stat;
End;
3. 使用execute immediate 處理dml操作
(1) 處理無占位符和returning的dml語句
Declare
sql_stat varchar2(100);
Begin
sql_stat :='update semp set sal=sal*1.1 where deptno=30';
execute immediate sql_stat;
End;
(2)處理包含占位符的dml語句
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;
(3)處理包含returning子句的dml語句(只處理單行)
Declare
salary number(6,2);
sql_stat varchar2(100);
Begin
sql_stat :='update emp set sal=sal*(1+:percent/100)'
|| ' where empno=:dno returning sal into :salary';
execute immediate sql_stat using &1,&2
returning into salary;
dbms_output.put_line('新工資﹕'||to_char(salary));
End;
Declare
sql_stat varchar2(400);
v_empno emp.empno%type;
v_ename emp.ename%type;
v_deptno emp.deptno%type;
Begin
sql_stat :='insert into emp(empno,ename,deptno) values(:empno,:ename,:deptno) returning empno,ename,deptno into:v_empno,:v_ename,:v_deptno';
execute immediate sql_stat using &1,&2,&3 returning into v_empno,v_ename,v_deptno;
dbms_output.put_line('新工資﹕'||to_char(v_empno));
End;
(4)使用execute immediate處理單行記錄
Declare
sql_stat varchar2(100);
emp_record emp%rowtype;
Begin
sql_stat :='select * from emp where empno =:eno';
execute immediate sql_stat into emp_record using &i;
dbms_output.put_line('顧員'|| emp_record.ename || '的工資為' || emp_record.sal);
End;
14.3處理多行查詢
為了處理select語句返回的多行數據﹐需要使用open_for,fetch和close語句﹐
定義游標變量--->打開游標變量---->循環提取數據----->關閉游標變量
1. 定義游標變量
type cursortype is ref cursor;
cursor_variable cursortype;
2. 打開游標變量
open cursor_variable for dynamic_straint
[using bind_argument[,bind_argumen1...];
3. 循環提取數據
Fetch cursor variable into {var1[var2]....|record_var];
4. 關閉游標變量
close cursor_variable;
5. 多行查詢示例
Declare
Type empcurtype is ref cursor;
emp_cv empcurtype;
emp_record emp%rowtype;
sql_stat varchar2(300);
Begin
sql_stat :='select * from emp where deptno=:dno';
open emp_cv for sql_stat using &do;
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;
14.4 在動態sql中使用bulk子句
1. 在execute immediate 語句中命名用動態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)使用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(400);
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;
(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子句
Declare
type empcurtype is ref cursor;
emp_cv empcurtype;
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
sql_stat varchar2(200);
Begin
sql_stat :=' select ename from emp where job=:titiel';
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 index in lower bound ...upper bound
execute immediate dynamic_straing
using bind_argument|bind_argument(index)
[,bind_argument|bind_argument(index)].....
[{returning|return } bulk collect into bind_argument[,bind_arguent...]];
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(200);
Begin
ename_table :=ename_table_type('SCOTT','SMITH','CLARK');
sql_stat :='update emp set sal=sal*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;