一、游标定义
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;