ora 错误码

 

1.1 PL/SQL简介 1wlVz#f.  
V07VwVD  
  PL/SQL是ORACLE的过程化语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PL/SQL可以执行SQL语句，SQL语句中也可以使用PL/SQL函数。 r:73uRk  
]I8]mUiUH  
1.2 创建PL/SQL程序块 ma1 (EJ/  
[;*/P/Xih  
DECLARE .l7j8 }  
… U7jhV,gO4  
BEGIN j,/tejl1  
… +I*k0"gj6  
EXCEPTION LFl2uV"  
END; ;9}w|!/  
1.3 PL/SQL数据类型 |Uc <;> l  
dz{#"No0  
名称 7ESN!  
类型 >_rzT9gX&  
说明 y2oB]^z&n  
='7er.~/  
NUMBER m/<< oIlH  
数字型 TX< e_[$/  
能存放整数值和实数值，并且可以定义精度和取值范围 L!CX &  
FZ[@])B  
BINARY_INTEGER #E- VW  
数字型 h Jfa_  
可存储带符号整数，为整数计算优化性能 /_m/U.*  
?noETHz)  
DEC k| _$R?  
数字型 `c qH}2s#  
NUMBER的子类型，小数 %Y)PH-z  
OPvj{Dv$0  
DOUBLE PRECISION lKh2 LY=j  
数字型 h/0<:eZ*  
NUMBER的子类型，高精度实数 Dg9--wI}I9  
7 SjF9x  
INTEGER ?YkO+?}+  
数字型 z. _C*c  
NUMBER的子类型，整数 ;3.T* ?|o  
323yAF  
INT 2#$}yP~  
数字型 3 2iWYN  
NUMBER的子类型，整数 |04}zU%N  
Q8gdI  
NUMERIC =An Z>6  
数字型 WrK^>  
NUMBER的子类型，与NUMBER等价 !Lg}q!*%>V  
cVP49r}}v  
REAL !P i? !  
数字型 .}hZ7>4-  
NUMBER的子类型，与NUMBER等价 #35S7G^@`  
o}^vREO  
SMALLINT koWb@V]  
数字型 K/.hJ  
NUMBER的子类型，取值范围比INTEGER小 3v3Va~fm`  
[,A'  
VARCHAR2 7;s0m0<%~  
字符型 m=l3O:~J  
存放可变长字符串，有最大长度 T^H) lC#R  
p`.fYW:p  
CHAR 8;BwzRtgT  
字符型 ;Wm)e~`,  
定长字符串 ;8Q?`=a  
w|*G`~l09  
LONG u<r('IW0  
字符型 EB<tX`Wp  
变长字符串，最大长度可达32，767 QZP;k!"w  
y`Y}P1y*  
DATE Mi-9sW  
日期型 5mBk[{  
以数据库相同的格式存放日期值 0']M,iC/  
0nn# U  
BOOLEAN F.hC%Ncu  
布尔型 /hX^Cn=6  
TRUE OR FALSE N/p_6GYMa  
jws(`mIf/  
ROWID Yzz8:n  
ROWID <z PyID`  
存放数据库的行号 Q=E6ZxH5;  
~f:fOrLE#  
Hqk2W*UTl  
例子： '_%Jw:4k  
DECLARE /$I )}  
  ORDER_NO NUMBER(3); $}829<gh7  
  CUST_NAME VARCHAR2(20); @9L9c  
  ORDER_DATE DATE; y/:2Re/*Jt  
  EMP_NO INTEGER:=25; =[$*PTe  
  PI CONSTANT NUMBER:=3.1416; j(SQNSFD  
BEGIN S'WmPv  
NULL; &sW/r::,  
END; *?uF&( 0  
1.4 处理PL/SQL的异常 ,Ya&M@^Z  
0-5:"SN'  
1.4.1 PL/SQL的异常 xsa* XR  
Un<~P@T%  
例如： }/=VnCfU  
DECLARE A_[65'*b  
  X NUMBER; D3_,2  
BEGIN .rS0zU  
  X:= ’yyyy’;--Error Here Oh|Hy/&6W  
EXCEPTION   WHEN VALUE_ERROR THEN s]}P jh8  
  DBMS_OUTPUT.PUT_LINE(’EXCEPTION HANDED’); 7^ER?@:W  
END; * c xYB  
"/wZtc  
实现技术： uP<tP:  
EXCEPTION WHEN first_exception THEN `O*+%/(  
… }fdo Aid~  
WHEN second_exception THEN ^pIT,|myY7  
… %9C_p]P*  
WHEN OTHERS THEN H}Z/r2  
/*THERS异常处理器必须排在最后，它处理所有没有明确列出的异常。*/ /jH^OXxb  
… RzgA;ZC'  
END; X?kw=x{2P  
$S Kax#[  
1.4.2 预定义异常 oA+/F]XJ  
Wl? 0|{W  
异常名称 |k,M$@5s  
ORACLE错误 q# C;iK4  
说明 1RHFWK5Si  
VPi*9(LS  
CURSOR_ALREADY_OPEN /0fS;Q^{j  
ORA-6511 .QVN&UyZ  
试图打开一个已打开的光标 &iI5^b-P  
wGLSei-s  
DUP_VAL_ON_INDEX 01w}8a(  
ORA-0001 l1 UN.l'p  
试图破坏一个唯一性限制 Y^2]*e%  
f#3!Q!C^  
INVALID_CURSOR Z(c2F]  
ORA-1001 Dssecc'  
试图使用一个无效的光标 u(C?/HaH  
bR}=bp4K  
INVALID_NUMBER hM[QR'/QS  
ORA-1722 aP`V  
试图对非数字值进行数字操作 |yYu!+U  
BiI`oCX  
LOGIN_DENIED +?y9EZB%  
ORA-1017 qIg^R@  
无效的用户名或者口令 v|#}LQZ  
ylPDM7Ka  
NO_DATA_FOUND >q^l  
ORA-1403 =x^b  
查询未找到数据 VmTPE5d  
Z0Df~ @  
NOT_LOGGED_ON lCC(N?%Q  
ORA-1012 ,+hH|$  
还未连接就试图数据库操作 _pe_w{V-b6  
QGpAG#M9?  
PROGRAM_ERROR ?O.1HEr  
ORA-6501 SFQYrY  
内部错误 Kfl#78$d  
<JNiW8 PG  
ROWTYPE_MISMATCH rjLPX  
ORA-6504 QaYUcma~n  
主变量和光标的类型不兼容 !CO1I-yL  
6+s10?  
STORAGE_ERROR j[e<CGZ  
ORA-6500 !um~P  
内部错误 ^/V>^9CZ  
<Mq vGXI  
TIMEOUT_ON_RESOURCE $]`rWSYtv`  
ORA-0051 :H(w W  
发生超时 ,%X"Caz  
Mc@_[q!xY?  
TOO_MANY_ROWS b'1m 9T780  
ORA-1422 LF X[v  
SELECT INTD命令返回的多行 9 M90X8  
k!0vpps  
TRANSACTION_BACKED_OUT |<2<`3  
ORA-006 kIJ=]wU|v  
由于死锁提交被退回 :4x&B^,53  
p?' F$Wz  
VALUE_ERROR ]T>|Y0|  
ORA-6502 !|-:"hE1h  
转换或者裁剪错误 X<uH [  
=`t%p1  
ZERO_DIVIDE !$!"$-5  
ORA-1476 yG:Pg MrB  
试图被零除 }O8#4-E_Ji  
O^4K o}  
pd oCV  
　 pu5-=QN  
v1~`76^  
1.4.3 自定义异常处理 5Y(f7,JX  
/!,qXfTMB  
DECLARE <e7  
  BAD_ROWID EXCEPTION; tO]` I-  
  X ROWID; -P|st;?#  
PRAGMA EXCEPTION_INIT(BAD_ROWID，-01445); V+2C!)f(  
BEGIN ,wE cRN w  
  SELECT ROWID INTO X FROM TAB 0xQ="aXE  
  WHERE ROWNUM=1; :7-2^7z)  
EXCEPTION WHEN BAD_ROWID THEN cq,0?2R`t  
  DBMS_OUTPUT.PUT_LINE(’CANNOT QUERY ROWID FROM THIS VIEW’); da/Tms`T  
END; b,Vg3BS  
H ]x-s  
  注意：-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到 -)p S/$GC  
这个ORACLE错误，该语句的语法如下： F?B`rw@xr  
  PRAGMA EXCEPTION_INIT(exception_name， error_number); G)G 257K"~  
其中error_number是负数，因为错误号被认为负数，当定义错误时记住使用负号 UqwU3  
iX|K4.Pz{  
1.4.4 自定义异常 rijavZS6  
sW^e D;  
  异常不一定必须是oracle返回的系统错误，用户可以在自己的应用程序中创 }B e;YIhG  
建可触发及可处理的自定义异常 B{ Ab #  
DECLARE K2> CR$ L  
  SALARY_CODE VARCHAR2(1); DYDeb i6  
  INVALID_SALARY_CODE EXCEPTION; ({OQ JBC  
BEGIN ZuFcJ?8i  
  SALARY_CODE:=’X’; F )Iz:  
  IF SALARY_CODE NOT IN(’A’， ’B’， ’C’) THEN vSu dT  
  RAISE INVALID_SALARY_CODE; 'u:-~nSX)  
  END IF; 4*ZY#7h  
EXCEPTION WHEN INVALID_SALARY_CODE THEN `a%MD>R_Lg  
  DBMS_OUTPUT.PUT_LINE(’INVALID SALARY CODE’); D4b-Y[/"  
END; :KgH7s}  
X$;&Mdo.  
1.5 在PL/SQL中单条记录的查询 D<m0G]Ht*  
FieDESsX>  
  在PL/SQL内，有时在没有定义显式光标的情况下需要查询单条记录，并把记录的数据赋给变量。 (-g*U#  
DECLARE =C2sl;7~*  
  ln_dno NUMBER; ~/mh/a&  
  lvs_dname VARCHAR2(40); qx'0(q2Ii(  
BEGIN 7IkPi?&{  
  SELECT DEPT_NO，DEPT_NAME a(|6)w-  
    INTO ln_dno，lvs_dname )A$"COM4  
  FROM dept >`/.i,X .D  
  WHERE DEPT_NO=1; px<psR5  
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)||’.’||lvs_dname); q< XFw-Pv  
EXCEPTION WHEN NO_DATA_FOUND THEN w'K7$F51  
  DBMS_OUTPUT.PUT_LINE(’NO DATA_FOUND’); -G.N  
  WHEN TOO_MANY_ROWS THEN t`Kpbfk  
  DBMS_OUTPUT.PUT_LINE(’TOO_MANY_ROWS’); @Chl>s  
END; 98Vv K?  
5-X$"Z|@  
1.6 用光标查询多条记录 CtCReH03  
M2Nh3ijr  
    光标（CURSOR）是指向一个称为上下文相关区的区域的指针，这个区域在服务器的处理过程全局区（PGA）内，当服务器上执行了一个查询后，查询返回的记录集存放在上下文相关区，通过光标上的操作可以把这些记录检索到客户端的应用程序。 ArtY;.cg%  
;5/Se"Nd  
1.6.1 使用光标的基本方法 ~T@E")uR  
^VYR}1Mw  
DECLARE TIp/-  
  CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS X I Mh<  
    WHERE ROWNUM<=10 5i6Ji(  
    ORDER BY VIEW_NAME; /[Nkk)8-  
  VNAME VARCHAR2(40); "A[. 7w  
BEGIN Z&@P<  
  OPEN C1; XFFm 'W6@  
  FETCH C1 INTO VNAME; )[99SM  
  WHILE C1%FOUND LOOP [g==#[  
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||’ ’||VNAME); /]F Pv7!  
    FETCH C1 INTO VNAME; ^K<!`B  
  END LOOP; Ut%{pc 7^F  
END; F.Bij8/  
,=pn}/ R  
属性 juWbd|ad"  
含量 jlyuu  
/*6[Itm_h  
%FOUND zq$0 ?vGd  
布尔型属性，当最近一次该记录时成功返回，则值为TRUE DRVvC~M-,  
ayg^js2,  
%NOTFOUND 0,cU^HMA  
布尔型属性，它的值总与%FOUND属性的值相反 EQy~ ^7V B  
T'5MO/  
%ISOPEN s+0S,?{$  
布尔型属性，当光标是打开时返回TRUE @f#6Nu  
&8JK^zQq  
%ROWCOUNT ^5R2~  
数字型属性，返回已从光标中读取的记录数 M ~6 $kT  
xH>j  
-]Aqt/w"l  
　 $v1_M 1  
?:i,%]zxC  
1.6.2 使用光标FOR循环 sV]I]DR  
CsN^u H  
DECLARE sA oxLI  
  CURSOR C1 IS JgBC:t^/pV  
  SELECT VIEW_NAME e s<  
    FROM ALL_VIEWS t$rla _rbY  
    WHERE ROWNUM<=10 w[AL'1s]  
    ORDER BY VIEW_NAME; MfO: BX@$  
BEGIN ri6KD  
  FOR I IN C1 LOOP UqNUX?(  
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME); ]J@-,FFC  
  END LOOP; n~.$iN  
END LOOP; &xUD (  
EXCEPTION WHEN OTHERS THEN U_ l9CZ  
  NULL; 2_y]MXG+%  
END; I2Imb9k~B  
M:b#">M  
1.6.3 带参数的光标 m uy^>2p  
p4*L}Q  
DECLARE R TpNxr{[  
  CURSOR C1(VIEW_PATTERN VARCHAR2) IS KmTFJ,i M  
    SELECT VIEW_NAME }Sbk qd5  
    FROM ALL_VIEWS ?-vWNv  
    WHERE VIEW_NAME LIKE VIEW_PATTERN||’%’ AND h]s~w  
    ROWNUM<=10 "`V@?+3  
    ORDER BY VIEW_NAME; IBo  
  VNAME VARCHAR2(40); j<*7p:L7_>  
BEGIN jez0 A  
  FOR I IN C1(’USER_AR’) LOOP u,So+%  
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME); qqf`z,u  
  END LOOP; .Rk8qR B  
  DBMS_OUTPUT.PUT_LINE(??); #XDgvX >  
  FOR I IN C1(’USER’) LOOP sE% $]Jp  
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME); H{*D c_  
  END LOOP; +-!E% $  
EXCEPTION WHEN OTHERS THEN p~$cwbQ!  
  DBMS_OUTPUT.PUT_LINE(’AAA’); ')"+ a^c  
END; rW{!8FhI  
=XP[3~  
1.7 创建代表数据库记录和列的变量 Su#0 F0  
owAO&"C  
变量名 基表名.列名%TYPE &/h7 E  
DECLARE Blpk n1  
  D_NO DEPT.DEPT_NO%TYPE; -Jd|H*wWo  
  D_NAME DEPT.DEPT_NAME%TYPE; O. * 0;5  
BEGIN k/K)nH@)  
  SELECT DEPT_NO，DEPT_NAME INTO D_NO，D_NAME 5KC Qvv/  
  FROM DEPT; UHl3/m7g  
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO)); *xnZTj:  
EXCEPTION WHEN NO_DATA_FOUND THEN (KQLh,h7  
  NULL; *byUqY3(  
END; {"o9pIh{~  
6,t6~Uo/  
变量名 基表名%ROWTYPE .s{ "NqRA  
DECLARE (?zg.y  
  D VEQU12%ROWTYPE; Vb,V N?l  
BEGIN ' i+L  
  SELECT ASSET12ID，ASSET12NAME 2yA)SGri  
    INTO D.ASSET12ID， D.ASSET12NAME ^xe+(83S2?  
    FROM VEQU12; >9H^r/  
  DBMS_OUTPUT.PUT_LINE(D.ASSET12ID); S f?;j{?G  
EXCEPTION Y5aG^wE[:  
WHEN NO_DATA_FOUND THEN |Y uf/G%/  
  NULL; =+iY<~8  
WHEN TOO_MANY_ROWS THEN I# tlaz#  
  DBMS_OUTPUT.PUT_LINE(’TOO_MANY_ROWS’); BHr,jC  
END; TdPd8ig8{  
说明： sRt7.fe  
  当用户要创建一个变量来表示一个基表列或者要创建多个变量来代表一整条记录时，可以实际使用%TYPE属性和%ROWTYPE属性，使用%TYPE属性和%ROWTYPE属性可以保证当基表的结构或者其中某列的数据类型改变了时，用户的PL/SQL代码仍可正常工作。 )5@P|{FF  
L"jA#ULg  
1.8 怎样用PL/SQL表实现数组功能 rfonM~3?'  
r]Hrz'C`  
  PL/SQL表与其他过程化语言(如C语言)的一维数组类似。实现PL/SQL表需要创建一个数据类型并另外进行变量说明。 *m]Y6  
  Type <类型名> Is D`QMlRzXy  
  Table Of <数据类型> %qcCv9  
  Index by Binary_Integer; r]LP=K1  
以下为一个例子： g:RS7od=,  
Declare i9 CQ~  
  Type Array_type is prM)t8SE  
    Table Of Number Y/<w|LkD8  
    Index by Binary_Integer; KiMlbF.~V  
  My_Array Array_type; Q7`)&^ Hx  
Begin vnf2Z,f%  
  For I In 1..10 Loop !:xE X~  
    My_Array(I) := I*2; K)1Lg? j  
  End Loop; it>Bf;  
  For I In 1..10 Loop B4zuWCE@  
    Dbms_Output.Put_line(To_char(My_Array(I))); @^# 9N!Fj]  
  End Loop; |Ul4n@+2  
End;

******************

　　因为在PL/SQL 中并没有数组. 这是偶查资料找的范例和自己写的范例来解释如何在PL/SQL 中使用数组. 也许很多人已知道， 不过就是让不知道的朋友们了解一下吧。 9Ns%<FRO@  
.G<Or`K^i  
---------------------- 单维数组 ------------------------ q}E'x/s2m  

DECLARE xi.QHKBZaH  
TYPE emp_ssn_array IS TABLE OF NUMBER ((BdT:T/_  
INDEX BY BINARY_INTEGER; k$,y1hH;f8  
7l '1  
best_employees emp_ssn_array; h{PJ4U{W  
worst_employees emp_ssn_array; y$?O0S%F  
X'cm0}2  
BEGIN TfA;4 ^  
best_employees(1) := ’123456’; + hMF/@  
best_employees(2) := ’888888’; BlfW~l'mx  
1?# Wg>7'  
worst_employees(1) := ’222222’; 0,[- 4m  
worst_employees(2) := ’666666’; Q02:qn?T  
$^"_Fox]A/  
FOR i IN 1..best_employees.count LOOP #o-CG PE  
DBMS_OUTPUT.PUT_LINE(’i=’|| i || ’， best_employees= ’ ||best_employees(i) i#y3QCNqf^  
|| ’， worst_employees= ’ ||worst_employees(i)); Z<0+<tt  
END LOOP; ~.wDb,*  
</cH9D`dE  
END;

#dgWXO  
,/K1cW~U5  
---------------------- 多维数组 ------------------------ uvrfR?%QK  

DECLARE jbqhNsTNK  
;mLbJT  
TYPE emp_type IS RECORD ^J>jU`)CJ  
( emp_id employee_table.emp_id%TYPE， R?Q@)POW  
emp_name employee_table.emp_name%TYPE， VB 8t"5  
emp_gender employee_table.emp_gender%TYPE ); *Df|D/,WE  
[pmZ0/l  
TYPE emp_type_array IS TABLE OF %+G/oF |  
emp_type INDEX BY BINARY_INTEGER; ];1z%.  
=5LtEgHU  
emp_rec_array emp_type_array; 1` m ~c  
emp_rec emp_type; .d9VV&  
m5zP|s1`['  
BEGIN S_6`.@B}  
emp_rec.emp_id := 300000000; _:M6~XHo  
emp_rec.emp_name := ’Barbara’; ;myu8B7&  
emp_rec.emp_gender := ’Female’; m3 (fr  
Zu/#;O  
emp_rec_array(1) := emp_rec; #&r^~>,#L-  
UH%?{>oRh  
emp_rec.emp_id := 300000008; wm*`  
emp_rec.emp_name := ’Rick’; bMkn(_H)/  
emp_rec.emp_gender := ’Male’; W|D'S}J  
:7:Nx`D8  
emp_rec_array(2) := emp_rec; j*W]^uT,  
!p&M,6  
FOR i IN 1..emp_rec_array.count LOOP GE~mu76%  
DBMS_OUTPUT.PUT_LINE(’i=’||i _a15R/S  
||’， emp_id =’||emp_rec_array(i).emp_id I|Z5*iXqCm  
||’， emp_name =’||emp_rec_array(i).emp_name 9l{r&]  
||’， emp_gender = ’||emp_rec_array(i).emp_gender); mjBX a  
END LOOP; ut-UTW  
4~G9._  
END; (O&ooM* o  
-------------- Result -------------- 3TtnLay.k  
i=1， emp_id =300000000， emp_name =Barbara， emp_gender = Female 1S.nqOfx  
i=2， emp_id =300000008， emp_name =Rick， emp_gender = Male

x8zUGvtQ  
7U?x8%H*  
注：在PL/SQL 中是没有数组(Array) 概念的. 但是如果程序员想用Array 的话， 就得变通一下， 用TYPE 和Table of Record 来代替多维数组， 一样挺好用的。 6/b B#a  
emp_type 就好象一个table 中的一条record 一样， 里面有id， name，gender等。emp_type_array 象个table， 里面含有一条条这样的record (emp_type)，就象多维数组一样。 7c+TS--  

************************

游标是什么:

　　游标字面理解就是游动的光标。

　　用数据库语言来描述:游标是映射在结果集中一行数据上的位置实体，有了游标用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等等。

　　游标的分类:

　　显式游标和隐式游标

　　显示游标的使用需要4步:

　　1.声明游标

　　CURSOR mycur(vartype number) is

　　select emp_no，emp_zc from cus_emp_basic

　　where com_no = vartype;

　　2.打开游标

　　open mycur(000627) 注:000627:参数

　　3.读取数据

　　fetch mycur into varno，varprice;

　　4.关闭游标

　　close mycur;

　　游标的属性：

　　oracle 游标有4个属性: %ISOPEN ， %FOUND ， %NOTFOUND， %ROWCOUNT

　　%ISOPEN 判断游标是否被打开，如果打开%ISOPEN 等于true，否则等于false

　　%FOUND %NOTFOUND 判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false

　　%ROWCOUNT 返回当前位置为止游标读取的记录行数。

　　示例:

　　set serveroutput on;

　　declare

　　varno varchar2(20);

　　varprice varchar2(20);

　　CURSOR mycur(vartype number) is

　　select emp_no，emp_zc from cus_emp_basic

　　where com_no = vartype;

　　begin

　　if mycur%isopen = false then

　　open mycur(000627);

　　end if;

　　fetch mycur into varno，varprice;

　　while mycur%found

　　loop

　　dbms_output.put_line(varno||%27，%27||varprice);

　　if mycur%rowcount=2 then

　　exit;

　　end if;

　　fetch mycur into varno，varprice;

　　end loop;

　　close mycur;

　　end;

　　pl/sql 记录的结构和c语言中的结构体类似，是由一组数据项构成的逻辑单元。

　　pl/sql 记录并不保存再数据库中，它与变量一样，保存再内存空间中，在使用记录时候，要首先定义记录结构，然后声明记录变量。可以把pl/sql记录看作是一个用户自定义的数据类型。

　　set serveroutput on;

　　declare

　　type person is record

　　(

　　empno cus_emp_basic.emp_no%type，

　　empzc cus_emp_basic.emp_zc%type);

　　person1 person;

　　cursor mycur(vartype number)is

　　select emp_no，emp_zc from cus_emp_basic

　　where com_no=vartype;

　　begin

　　if mycur%isopen = false then

　　open mycur(000627);

　　end if;

　　loop

　　fetch mycur into person1;

　　exit when mycur%notfound;

　　dbms_output.put_line(%27雇员编号:%27||person1.empno||%27，地址:%27||person1.empzc);

　　end loop;

　　close mycur;

　　end;

　　典型游标for 循环

　　游标for循环示显示游标的一种快捷使用方式，它使用for循环依次读取结果集中的行数据，当form循环开始时，游标自动打开(不需要open)，每循环一次系统自动读取游标当前行的数据(不需要fetch)，当退出for循环时，游标被自动关闭(不需要使用close)使用游标for循环的时候不能使用open语句，fetch语句和close语句，否则会产生错误。

　　set serveroutput on;

　　declare

　　cursor mycur(vartype number)is

　　select emp_no，emp_zc from cus_emp_basic

　　where com_no=vartype;

　　begin

　　for person in mycur(000627) loop

　　dbms_output.put_line(%27雇员编号:%27||person.emp_no||%27，地址:%27||person.emp_zc);

　　end loop;

　　end;

9Z rWG  
**********************

 

一、 什么是光标

Oracle 使用两种光标：显式光标和隐式光标。不管语句返回多少条纪录， PL/SQL 为使用的每一条 UPDATE 、 DELETE 和 INSERT 等 SQL 命令隐式的声明一个光标。（要管理 SQL 语句的处理，必须隐式的给它定义一个光标。）用户声明并使用显示光标处理 SELECT 语句返回的多条记录。显示的定义光标一种结构，它使用户能够为特定的语句指定内存区域，以便以后使用。

二、 光标的作用

当 PL/SQL 光标查询返回多行数据时，这些记录组被称为活动集。 Oracle 将这种活动集存储在您创建的显示定义的已命名的光标中。Oracle 光标是一种用于轻松的处理多行数据的机制，没有光标， Oracle 开发人员必须单独地、显式地取回并管理光标查询选择的每一条记录。

光标的另一项功能事，它包含一个跟踪当前访问的记录的指针，这使您的程序能够一次处理多条记录。

三、 使用显示光标的基本方法

步骤如下：

1 、声明光标

声明光标的语法如下：

DECLARE cursor_name

Is

SELECT statement

其中， cursor_name 是您给光标指定的名称； SELECT statement 是给光标活动集返回记录的查询。

声明光标完成了下面两个目的：

给光标命名；

将一个查询与光标关联起来。

值得注意的是，必须在 PL/SQL 块的声明部分声明光标；给光标指定的名称是一个未声明的标识符，而不是一个 PL/SQL 变量，不能给光标名称赋值，也不能将它用在表达式中。 PL/SQL 块使用这个名称来引用光标查询。

例： DECLARE

CURSOR c1

Is

SELECT VIEW_NAME FROM ALL_VIEWS

WHERE ROWNUM<=10 ；

另外还可以在光标定义语句中声明光标的参数，例：

CURSOR c1 （ view _nbr number ）

Is

SELECT VIEW_NAME FROM ALL_VIEWS

WHERE ROWNUM<= view _nbr ；

光标参数只对相应的光标是可见的，不能在光标范围之外引用该光标的参数。如果试图这样做， Oracle 将返回一个错误，指出该变量没有定义。

2 、打开光标

打开光标的语法如下：

OPEN cursor_name ；

其中 cursor_name 是您以前定义的光标名称。

打开光标将激活查询并识别活动集，可是在执行光标取回命令之前，并没有真正取回记录。 OPEN 命令还初始化了光标指针，使其指向活动集的第一条记录。光标被打开后，直到关闭之前，取回到活动集的所有数据都是静态的，换句话说，光标忽略所有在光标打开之后，对数据执行的 SQL DML 命令（ INSERT 、 UPDATE 、 DELETE 和 SELECT ）。因此只有在需要时才打开它，要刷新活动集，只需关闭并重新打开光标即可。

3 、从光标中取回数据

FETCH 命令以每次一条记录的方式取回活动集中的记录。通常将 FETCH 命令和某种迭代处理结合起来使用，在迭代处理中， FETCH 命令每执行一次，光标前进到活动集的下一条记录。

FETCH 命令的语法：

FETCH cursor_name INTO record_list ；

其中， cursor_name 是前面定义的光标的名称； record_list 是变量列表，它接受活动集中的列。 FETCH 命令将活动集的结果放置到这些变量中。

执行 FETCH 命令后，活动集中的结果被取回到 PL/SQL 变量中，以便在 PL/SQL 块中使用。每取回一条记录，光标的指针就移向活动集的下一条记录。

例：

FETCH C1 INTO VNAME;

WHILE C1%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||’ ’||VNAME);

END LOOP;

其中，使用属性 ’%FOUND’ 使得当 FETCH 到达活动集的结尾时，不会引发异常。其它属性及含义见下表：

属性 含量

%FOUND 布尔型属性，当最近一次该记录时成功返回，则值为 TRUE

%NOTFOUND 布尔型属性，它的值总与 %FOUND 属性的值相反

%ISOPEN 布尔型属性，当光标是打开时返回 TRUE

%ROWCOUNT 数字型属性，返回已从光标中读取的记录数

属性 含量

%FOUND 布尔型属性，当最近一次该记录时成功返回，则值为 TRUE

%NOTFOUND 布尔型属性，它的值总与 %FOUND 属性的值相反

%ISOPEN 布尔型属性，当光标是打开时返回 TRUE

%ROWCOUNT 数字型属性，返回已从光标中读取的记录数

4 、关闭光标

CLOSE 语句关闭以前打开的光标，使得活动集不确定。当用户的程序或会话结束时， Oracle 隐式关闭光标。光标被关闭后，就不能对它执行任何操作了 ， 否则将引发异常。

CLOSE 语句的语法是：

CLOSE cursor_name ；

其中， cursor_name 是以前打开的光标的名称。

完整的程序代码如下：

DECLARE

CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS

WHERE ROWNUM<=10

ORDER BY VIEW_NAME;

VNAME VARCHAR2(40);

BEGIN

OPEN C1;

FETCH C1 INTO VNAME;

WHILE C1%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||’’||VNAME);

END LOOP;

END;

…… CLOSE C1;

四、 小结

光标是一种结构 ， 能够以一次一条记录的方式处理多行查询的结果 . 为每条 DML 语句创建隐式光标 ， 而显式光标是由用户创建的 ， 以便处理返回多条记录的查询。而且 ， 通过消除反复地分析代码 ， 光标提高了代码的处理速度。

**************************

一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。 ETjlq]@j  
Ojie.+'SB  
　　首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。 M<g>z6  
3H@TvV/;f  
　　Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明： }UQBaqDH  
q.uIZ  
　　一、本地动态SQL &{/ `Q ,  
7GWPsaPn  
　　本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。 `!> zYcmT  
.+|G`*1<i  
　　1、本地动态SQL执行DDL语句： I zM=?,`  
W446;)?5  
　　需求：根据用户输入的表名及字段名等参数动态建表。 b4S7 Q"g  
$9M>B<]  

create or replace procedure proc_test M)V z9,   ( el;^cMY       table_name in varchar2，     --表名 Czid"Ih-       field1 in varchar2，          --字段名 D2[uex       datatype1 in varchar2，      --字段类型 ` {k>I^Pg       field2 in varchar2，          --字段名 *~lD;{2       datatype2 in varchar2        --字段类型 g4oFUyk{   ) as E@JxY       str_sql varchar2(500); Q7N4@w;e   begin o~NeS|a       str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’，’||field2||’ ’||datatype2||’)’; mB"zyL-       execute immediate str_sql;   --动态执行DDL语句 ngsax1xO       exception L}XERO TR           when others then j /)cdP               null; ,*E%D _   end ;

a}i{b2B  
　　以上是编译通过的存储过程代码。下面执行存储过程动态建表。 O<6/0ub&+h  
eb9qg.9Z  
 

SQL> execute proc_test(’dinya_test’，’id’，’number(8) not null’，’name’，’varchar2(100)’); ]DmqhK`   oYJ&BPuA'   PL/SQL procedure successfully completed Sw5H+!   O])/kS`   SQL> desc dinya_test; y{j>4g$:z   Name Type          Nullable Default Comments fi bR:8   ---- ------------- -------- ------- -------- l_,8_u7G   ID   NUMBER(8) =WO{h48]   TzK?bbgr!   NAME VARCHAR2(100) Y m|f|u3'z$   b%)a5H(   SQL>

P AKh v.7  
 　　到这里，就实现了我们的需求，使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。 f3596a  
.d8~]@U!<  
  　　2 、本地动态SQL 执行DML 语句。 //=g; o'  
|H |ewVUY  
　　需求：将用户输入的值插入到上例中建好的dinya_test表中。 t=yM}# r$  
^ 5UIbA(  

create or replace procedure proc_insert $MP'j9-S?   ( /Mi y+<8$       id in number，                                 --输入序号 T$RZRZo       name in varchar2                             --输入姓名 @y}1%{,%   ) as Cj ykM])       str_sql varchar2(500); WzZb-F   begin MT|}[|_       str_sql:=’insert into dinya_test values(:1，:2)’; y<.1+TG       execute immediate str_sql using id，name; --动态执行插入操作 lS 9rgq<n       exception ^P/(IDJCo           when others then XkOsnI8n               null; z4{ H=   end ;

t|%wVj?_  
　　执行存储过程，插入数据到测试表中。 :WJ[ a#  
u-$(TyDEl|  
 

SQL> execute proc_insert(1，’dinya’); HSE9-c =   PL/SQL procedure successfully completed P=OHiG/z   SQL> select * from dinya_test; 4RGEg;]S           ID      NAME /!Cix}}1           1      dinya

KuL2X@)}  
　　在上例中，本地动态SQL执行DML语句时使用了using子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态SQL的时候，使用RETURNING INTO 子句，如： g"Mqh!{ FI  
.'k]]2%ILp  

declare pC2ZN       p_id number:=1; >qk[//^O       v_count number; pLjet~2}iJ   begin 3omFd#EP       v_string:=’select count(*) from table_name a where a.id=:id’; A(zF[/{]       execute immediate v_string into v_count using p_id;  q_S`@2Dzz,   end ;

aV"K%#N  
　　更多的关于动态SQL中关于返回值及为输出输入绑定变量执行参数模式的问题，请读者自行做测试。 &S[tI$  
Z)}q=NjA  
  　　二、使用 DBMS_SQL 包 v l59|W6  
/;AW/& Ea  
　　使用DBMS_SQL包实现动态SQL的步骤如下：A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。B、使用DBMS_SQL包的parse过程来分析该字符串。C、使用DBMS_SQL包的bind_variable过程来绑定变量。D、使用DBMS_SQL包的execute函数来执行语句。 Y^5X>  
Cr0 /7  
　　1、使用DBMS_SQL包执行DDL语句 N}{CL(xi  
}G n2%  
　　需求：使用DBMS_SQL包根据用户输入的表名、字段名及字段类型建表。 L'=e /&  
6GzmzhX4  

create or replace procedure proc_dbms_sql kfb+OE:7   ( &H/$O.?f       table_name in varchar2，       --表名 g#FqjE|mx       field_name1 in varchar2，      --字段名 6Nx TW       datatype1 in varchar2，        --字段类型 ~Av]LW       field_name2 in varchar2，      --字段名 "%ag^v9       datatype2 in varchar2         --字段类型 mh44   )as O60jC;{F       v_cursor number;              --定义光标 fh$U"       v_string varchar2(200);      --定义字符串变量 pvy;L[c       v_row number;                  --行数 K *vNv 4   begin *sJx0<!M}       v_cursor:=dbms_sql.open_cursor;     --为处理打开光标 l[ G ,sq"       v_string:=’create table ’||table_name||’(’||field_name1||’ ’||datatype1||’，’||field_name2||’ ’||datatype2||’)’; 4EB$e?       dbms_sql.parse(v_cursor，v_string，dbms_sql.native);    --分析语句 GU&XK7L       v_row:=dbms_sql.execute(v_cursor);   --执行语句 W/~ZmA.       dbms_sql.close_cursor(v_cursor);     --关闭光标 G#@<bg3       exception GMNf#;x           when others then *c3 o&-ke9               dbms_sql.close_cursor(v_cursor);  --关闭光标 R:ar85F               raise; -5>g 0o2   end;

R>0ta Q  
 　　以上过程编译通过后，执行过程创建表结构： 1gm{.*G  
Z$hxo )|  

SQL> execute proc_dbms_sql(’dinya_test2’，’id’，’number(8) not null’，’name’，’varchar2(100)’); x NC>m&T   ~,guw7F   PL/SQL procedure successfully completed ;kFp)*i   (#,.;Y   SQL> desc dinya_test2; )- /w   Name Type          Nullable Default Comments 2U+&F'&Q   ---- ------------- -------- ------- -------- JQCwI`%i   ID   NUMBER(8)                                |q>Mw-=   NAME VARCHAR2(100) Y                         5a& w M   qw/{o:ce]   SQL>

Gpu?z- )  
 　　2、使用DBMS_SQL包执行DML语句 a*hThr+$M  
#77UKYj2L-  
　　需求：使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。 qrc/Q;$  
<^Tj}5 )n  
　　查看表中已有记录： ea0tx3'  
oNZ W#<K  

SQL> select * from dinya_test2; ,d> ~='          ID NAME }dJ ~Iy           1 Oracle X&.$/xaT           2 CSDN T zYg H           3 ERP J7;n;Mx   SQL>

C$G88hesn  
　　建存储过程，并编译通过： 1vK(^u[  
LY!3u0PnlT  

create or replace procedure proc_dbms_sql_update B m@oB2x)   ( DD6`k*RIk.       id number， $17 v,       name varchar2 [xs)u3b   )as g yH7((#i       v_cursor number;            --定义光标 a2=uM}Hsp       v_string varchar2(200);   --字符串变量 ]^6y NtLK       v_row number;               --行数 z2nUul(2   begin fF9oYOh|       v_cursor:=dbms_sql.open_cursor;    --为处理打开光标 _Dcc<-.       v_string:=’update dinya_test2 a set a.name=:p_name where a.id=:p_id’; 1nlE3Y?AV       dbms_sql.parse(v_cursor，v_string，dbms_sql.native);   --分析语句 mb~=Xy k&       dbms_sql.bind_variable(v_cursor，’:p_name’，name);     --绑定变量 LcpyW=)}"V       dbms_sql.bind_variable(v_cursor，’:p_id’，id);          --绑定变量 w[,?- Xm       v_row:=dbms_sql.execute(v_cursor);　　　　　　　　　　　--执行动态ＳＱＬ we H@S       dbms_sql.close_cursor(v_cursor);                        --关闭光标 ;1R?9JN"       exception C$'D]fX           when others then !Hd vCYB>               dbms_sql.close_cursor(v_cursor);                --关闭光标 Uaus>Frx.T               raise; zldfRo/wl   end;

B0b[p*g Il  
　　执行过程，根据用户输入的参数更新表中的数据： {uaZ<4N.  
5|<yfk8*J  

SQL> execute proc_dbms_sql_update(2，’csdn_dinya’); (~j,mk   !US8aT   PL/SQL procedure successfully completed Y7<zm}=(/   @s_3 0+   SQL> select * from dinya_test2; :J^qjAV          ID NAME 2 ;JQX!           1 Oracle :.*HQt9N           2 csdn_dinya 6cM<>&e           3 ERP /ht ?G n   SQL>

v=!YfAn  
 　　执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用dbms_sql包来执行DML语句的功能。 KU-'+k2s;p  
EIr@g  
 　　使用DBMS_SQL中，如果要执行的动态语句不是查询语句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行，如果要执行动态语句是查询语句，则要使用DBMS_SQL.define_column定义输出变量，然后使用DBMS_SQL.Execute， DBMS_SQL.Fetch_Rows， DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。 w5 `#q&?  
  K UKACUL  
　　总结说明： q/NY72tj0  
'cV?i&;  
　　在Oracle开发过程中，我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是，PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同，在DDL中使用绑定变量是非法的（bind_variable(v_cursor，’:p_name’，name)），分析后不需要执行DBMS_SQL.Bind_Variable，直接将输入的变量加到字符串中即可。另外，DDL是在调用DBMS_SQL.PARSE时执行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。 5{d/u E%'p