oracle 游标 记录数量,oracle 对象管理 08_游标与记录

一、游标定义

1.游标是查询结果集的平面化展示，通过游标方便定位到结果集中某个特定的行。

2.游标的分类

显示游标:即用户自定义游标，专门用于处理select语句返回的多行数据

隐式游标:系统自动定义的游标，记录集只有单行数据，用于处理select into 和DML语句

3.游标使用的一般过程：

显示游标：声明, 打开, 读取, 关闭

隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的

4显示游标的过程描述

a.声明游标

CURSOR cursor_name IS select_statement

如：CURSOR emp_cur IS SELECT empno,ename,job,sal FROM scott.emp;

b.打开游标，打开游标则执行对应的select语句，将对应的结果集存放到游标当中

OPEN cursor_name

c.获取数据，提取单行数据，需要配合循环语句来使用，提取多行数据，collect为集合变量

FETCH cursor_name INTO var_name1,...var_name2 ;

FETCH cursor_name BULK COLLECT INTO collect1,collect2,...[LIMIT rows];

d.关闭游标

CLOSE cursor_name

5.显示游标的5个属性

cursor_name%ISOPEN         --游标是否打开

cursor_name%FOUND          --最近的FETCH是否提取到数据

cursor_name%NOTFOUND       --最近的FETCH是否没有提取到数据

cursor_name%ROWCOUNT       --返回到目前为止，已经从游标缓冲区中提取到数据的行数

二、游标使用

1.输入job，输出ename,sal值

declare

v_name emp.ename%type;

v_sal  emp.sal%type;

cursor cur_one is select ename,sal from emp where job ='&input';

begin

open cur_one;

dbms_output.put_line('ename          sal');

loop

fetch cur_one into v_name,v_sal;

exit when (cur_one%notfound);

dbms_output.put_line(v_name||'          '||v_sal);

end loop;

close cur_one;

end;

2.使用游标统一定义变量类型

declare

cursor cur_one is select ename,sal,job,mgr,hiredate,comm from emp where job ='&input';

v_cur_col  cur_one%rowtype;

begin

open cur_one;

dbms_output.put_line('ename          sal');

loop

fetch cur_one into v_cur_col;

exit when (cur_one%notfound);

dbms_output.put_line(v_cur_col.ename||'          '||v_cur_col.sal);

end loop;

close cur_one;

end;

3.游标FOR循环

游标FOR循环是为了简化游标使用过程而设计的。使用游标FOR循环检索游标时，游标的打开、数据提取、数据是否检

索到的判断与游标的关闭都是ORACLE系统自动进行的。

--显式

DECLARE

CURSOR CURSOR_006 IS

SELECT ENAME, HIREDATE FROM EMP WHERE SAL < &SAL;

BEGIN

DBMS_OUTPUT.PUT_LINE('ename      hiredate');

FOR V_CUR IN CURSOR_006 LOOP

DBMS_OUTPUT.PUT_LINE(V_CUR.ENAME || '   ' || V_CUR.HIREDATE);

END LOOP;

END;

--隐式

BEGIN

DBMS_OUTPUT.PUT_LINE('ename      hiredate');

FOR V_CUR IN (SELECT ENAME, HIREDATE FROM EMP WHERE SAL < &SAL) LOOP

DBMS_OUTPUT.PUT_LINE(V_CUR.ENAME || '   ' || V_CUR.HIREDATE);

END LOOP;

END;

三、记录

是一个或多个字段且拥有数据类型的集合体，类似于表的数据结构，定义了PL/SQL记录类型之后，可以定义

PL/SQL记录变量，多用于简化单行多列的数据处理。

1.记录的定义

a.自定义记录成员

TYPE TYPE_NAME IS RECORD

(field_name1 datatype1 [NOT NULL] [ := DEFAULT EXPRESSION],

field_name2 datatype2 [NOT NULL] [ := DEFAULT EXPRESSION],

...

field_nameN datatypeN [NOT NULL] [ := DEFAULT EXPRESSION]);

record_name TYPE_NAME;

b.参照数据对象定义

record_name table_name%rowtype

record_name view_name%rowtype

reocrd_name cursor_name%rowtype

四、记录的使用

1.使用记录及记录成员

undefine no

DECLARE

TYPE emp_record_type IS RECORD

(

name emp.ename%TYPE,

salary emp.sal%TYPE,

dno emp.deptno%TYPE

);

emp_record emp_record_type;

BEGIN

SELECT ename, sal, deptno INTO emp_record --[select ename,sal into emp_record.name,emp_record.salary]

FROM emp WHERE empno = &no;

dbms_output.put_line(emp_record.name);   --输出时仅仅输出记录变量的一个成员emp_record.name

END;

2.使用PL/SQL记录(记录成员)变量

DECLARE

dept_record dept%ROWTYPE;

BEGIN

dept_record.deptno := 50;

dept_record.dname := 'ADMINISTRATOR';

dept_record.loc := 'BEIJING';

INSERT INTO dept VALUES dept_record;

END;

3.记录使用的几个问题

a.记录成员非空时必须在定义时给初值

DECLARE

TYPE ex_type IS RECORD

(col1 NUMBER(3),

col2 VARCHAR2(5) NOT NULL);  --编译不通过

ex_record ex_type;

BEGIN

ex_record.col1:=15;

ex_record.col1:=TO_CHAR(ex_record.col1);

ex_record.col2:='John';

DBMS_OUTPUT.PUT_LINE('ex_record.col1 is '||ex_record.col1);

DBMS_OUTPUT.PUT_LINE('ex_record.col2 is '||ex_record.col2);

END;

DECLARE

TYPE ex_type IS RECORD(

col1 NUMBER(3),

col2 VARCHAR2(5) NOT NULL := 'John');

ex_record ex_type;

BEGIN

ex_record.col1 := 15;

ex_record.col1 := TO_CHAR(ex_record.col1);

ex_record.col2:='TOM';--可以赋新值

DBMS_OUTPUT.PUT_LINE('ex_record.col1 is ' || ex_record.col1);

DBMS_OUTPUT.PUT_LINE('ex_record.col2 is ' || ex_record.col2);

END;

b.来自不同的记录类型的记录变量之间不能相互赋值

c.同一个记录的记录变量之间可以相互赋值

d.基于表，游标，自定义记录的使用

DECLARE

CURSOR dept_cur IS

SELECT * FROM dept WHERE deptno = 30;

TYPE dept_type IS RECORD(

deptno NUMBER(2),

dname  VARCHAR2(14),

loc    VARCHAR2(13));

dept_rec1 dept%ROWTYPE;     --声明基于表dept的记录变量

dept_rec2 dept_cur%ROWTYPE; --声明基于游标dept_cur的记录变量

dept_rec3 dept_type;        --声明基于自定义dept_type的记录变量

BEGIN

SELECT * INTO dept_rec1 FROM dept WHERE deptno = 30;

OPEN dept_cur;

LOOP

FETCH dept_cur

INTO dept_rec2;

EXIT WHEN dept_cur%NOTFOUND;

END LOOP;

dept_rec1 := dept_rec2;

dept_rec3 := dept_rec2;

DBMS_OUTPUT.PUT_LINE(dept_rec1.deptno || ' ' || dept_rec1.dname);

DBMS_OUTPUT.PUT_LINE(dept_rec2.deptno || ' ' || dept_rec2.dname);

DBMS_OUTPUT.PUT_LINE(dept_rec3.deptno || ' ' || dept_rec3.dname);

END;