Oracle学习笔记（3）

来源：互联网发布：淘宝会员优惠购编辑：程序博客网时间：2024/06/04 22:26

--游标cursor

游标的作用：不让程序中的select直接向终端屏幕输出，转向将select结果输出到PL/SQL程序中的内存变量

(a).隐式游标

a). SELECT ... INTO ... FROM ... 【只能返回一行，不能多行】

b). FOR cur IN (SELECT ...) LOOP

...

END LOOP ;

(b).显式游标

定义游标： CURSOR cursorName[(parameter[,parameter]...)] IS SELECT ...

打开游标： OPEN cursorName;

抓取游标数据到变量： FETCH cursorName INTO(变量列表 | 记录型)；

关闭游标： CLOSE cursorName;

例：

Name Type Nullable Default Comments

------- ------------ -------- ------- --------

EMP_NO NUMBER(10 ) Y

NAME VARCHAR2(20 ) Y

AGE NUMBER(3 ) Y

TEL VARCHAR2(30 ) Y

DEPT_NO NUMBER(5 ) Y

SAL NUMBER(15 ,2) Y

BD DATE Y

DECLARE

v_id NUMBER( 3);

v_name VARCHAR2(5 );

v_age NUMBER(3 );

v_sal NUMBER(12 );

CURSOR cur1 IS SELECT ROWNUM, NAME,age,sal FROM t1;

BEGIN

OPEN cur1;

FOR i IN 1..10 LOOP

FETCH cur1 INTO v_id,v_name,v_age,v_sal;

dbms_output.put_line(v_id||',' ||v_name||','||v_age|| ','||v_sal);

END LOOP ;

CLOSE cur1;

END;

DECLARE

TYPE rec IS RECORD (

v_id NUMBER( 3),

v_name VARCHAR2(5 ),

v_age NUMBER(3 ),

v_sal NUMBER(12 )

);

recv rec;

CURSOR cur1 IS SELECT ROWNUM, NAME,age,sal FROM t1;

BEGIN

OPEN cur1;

FOR i IN 1..10 LOOP

FETCH cur1 INTO recv;

dbms_output.put_line(recv.v_id||',' ||recv.v_name||','||recv.v_age|| ','||recv.v_sal);

END LOOP ;

CLOSE cur1;

END;

DECLARE

recv t1% ROWTYPE;

CURSOR cur1 IS SELECT * FROM t1;

BEGIN

OPEN cur1;

FOR i IN 1..10 LOOP

FETCH cur1 INTO recv;

dbms_output.put_line(recv.EMP_NO||',' ||recv.name||','||recv.age|| ','||recv.sal);

END LOOP ;

CLOSE cur1;

END;

游标属性【使用方式：游标名游标属性例：cursorName% FOUND】

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

　%NOTFOUND 布尔型属性，与%FOUND相反；

　%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；

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

DECLARE/*表中有多少条数据就输出多少条*/

rowscount NUMBER(2 );

recv t1% ROWTYPE;

CURSOR cur1 IS SELECT * FROM t1;

BEGIN

SELECT COUNT (*) INTO rowscount FROM t1;

OPEN cur1;

FOR i IN 1..rowscount LOOP

FETCH cur1 INTO recv;

dbms_output.put_line(recv.EMP_NO||',' ||recv.name||','||recv.age|| ','||recv.sal);

END LOOP ;

CLOSE cur1;

END;

DECLARE/*表中有多少条数据就输出多少条*/

rowscount NUMBER(2 );

recv t1% ROWTYPE;

CURSOR cur1 IS SELECT * FROM t1;

BEGIN

SELECT COUNT (*) INTO rowscount FROM t1;

OPEN cur1;

FOR i IN 1..20 LOOP

FETCH cur1 INTO recv;

EXIT WHEN cur1%NOTFOUND;

dbms_output.put_line(recv.EMP_NO||',' ||recv.name||','||recv.age|| ','||recv.sal);

END LOOP ;

CLOSE cur1;

END;

带参数的游标使用

DECLARE

rowscount NUMBER(2 );

recv t1% ROWTYPE;

CURSOR cur1(v_emp_no NUMBER/*地处的变量只能声明类型，不能指定长度*/ ) IS SELECT * FROM t1 WHERE emp_no=v_emp_no ORDER BY sal;

BEGIN

SELECT COUNT (*) INTO rowscount FROM t1;

OPEN cur1(1 );

FOR i IN 1..20 LOOP

EXIT WHEN cur1%NOTFOUND;

FETCH cur1 INTO recv;

dbms_output.put_line(recv.EMP_NO||',' ||recv.name||','||recv.age|| ','||recv.sal);

END LOOP ;

CLOSE cur1;

END;

DECLARE

rowscount NUMBER(2 );

recv t1% ROWTYPE;

CURSOR cur1(v_emp_no NUMBER/*地处的变量只能声明类型，不能指定长度*/ ) IS SELECT * FROM t1 WHERE emp_no=v_emp_no ORDER BY sal;

BEGIN

FOR i IN 1..10 LOOP

OPEN cur1(i);

LOOP

FETCH cur1 INTO recv;

EXIT WHEN cur1%NOTFOUND;

dbms_output.put_line(cur1%rowcount ||','||recv.EMP_NO|| ','||recv.name||',' ||recv.age||','||recv.sal);

dbms_output.put_line('---------------------------------------' );

END LOOP ;

CLOSE cur1;

END LOOP ;

END;

游标的for循环

DECLARE/*不带参数*/

CURSOR cura IS SELECT * FROM t1;

BEGIN

FOR recv IN cura LOOP

dbms_output.put_line(cura%rowcount ||','||recv.EMP_NO|| ','||recv.name||',' ||recv.age||','||recv.sal);

END LOOP ;

END;

DECLARE/*带参数*/

CURSOR cura(v_emp_no NUMBER) IS SELECT * FROM t1 WHERE emp_no=v_emp_no;

BEGIN

FOR recv IN cura(3) /*参数在这里使用*/ LOOP

dbms_output.put_line(cura%rowcount ||','||recv.EMP_NO|| ','||recv.name||',' ||recv.age||','||recv.sal);

END LOOP ;

END;

DECLARE/*带参数*/

CURSOR cura(v_emp_no NUMBER) IS SELECT * FROM t1 WHERE emp_no=v_emp_no;

BEGIN

FOR i IN 1..10 LOOP

FOR recv IN cura(i)/*参数在这里使用*/ LOOP

dbms_output.put_line(cura%rowcount ||','||recv.EMP_NO|| ','||recv.name||',' ||recv.age||','||recv.sal);

END LOOP ;

END;

隐式游标属性

　　SQL% FOUND 布尔型属性,当最近一次读记录时成功返回，则值为true；

　　SQL% NOTFOUND 布尔型属性,与%found相反；

　　SQL % ROWCOUNT 数字型属性, 返回已从游标中读取得记录数；

　　SQL % ISOPEN 布尔型属性, 取值总是FALSE。SQL命令执行完毕立即关闭隐式游标。

例：

DECLARE

　　 v_deptno emp.deptno% TYPE :=2 ;

　　BEGIN

　　 DELETE FROM emp WHERE deptno=v_deptno;

　　 IF SQL %FOUND THEN

　　 DELETE FROM dept WHERE deptno=v_deptno;

　　 END IF ;

　　END;

DECLARE

TYPE mycur IS REF CURSOR;

cur1 mycur;

rotype t1% ROWTYPE;

BEGIN

OPEN cur1 FOR SELECT * FROM t1;

LOOP

FETCH cur1 INTO rotype;

EXIT WHEN cur1%NOTFOUND;

dbms_output.put_line(rotype.name|| ' , '||rotype.age);

END LOOP ;

CLOSE cur1;

END;

CREATE TABLE t2 AS SELECT NAME ,age FROM t1;

DECLARE

t1_r t1% ROWTYPE;

TYPE mycur IS REF CURSOR RETURN t1_r%ROWTYPE/*添加return语句，进行请类型检查，限制返回的类型,return后面的返回类型必须是记录类型的*/ ;

cur1 mycur;

rotype t1% ROWTYPE;

BEGIN

OPEN cur1 FOR SELECT * FROM t1;

LOOP

FETCH cur1 INTO rotype;

EXIT WHEN cur1%NOTFOUND;

dbms_output.put_line(rotype.name|| ' , '||rotype.age);

END LOOP ;

CLOSE cur1;

END;

DECLARE

t1_r t1% ROWTYPE;

TYPE mycur IS REF CURSOR;

cur1 mycur;

rotype t1% ROWTYPE;

i NUMBER(5 ):=1;

BEGIN

OPEN cur1 FOR 'SELECT * FROM t1 where emp_no=:a' USING i/*此处使用变量条件，使用条件变量时只能是弱类型游标*/ ;

LOOP

FETCH cur1 INTO rotype;

EXIT WHEN cur1%NOTFOUND;

dbms_output.put_line(rotype.name|| ' , '||rotype.age);

END LOOP ;

CLOSE cur1;

END;

DECLARE

t1_r t1% ROWTYPE;

TYPE mycur IS REF CURSOR;

cur1 mycur;

rotype t1% ROWTYPE;

i NUMBER(5 ):=1;

tableName VARCHAR2(20 ):='t1'; /*使用游标变量可以灵活指定表名*/

BEGIN

OPEN cur1 FOR 'SELECT * FROM '||tableName|| ' where emp_no=:a' USING i/*此处使用变量条件，使用条件变量时只能是弱类型游标*/ ;

LOOP

FETCH cur1 INTO rotype;

EXIT WHEN cur1%NOTFOUND;

dbms_output.put_line(rotype.name|| ' , '||rotype.age);

END LOOP ;

CLOSE cur1;

END;