Oracle PLSQL游标、游标变量的使用
参考文章：https://www.cnblogs.com/huyong/archive/2011/05/04/2036377.html
在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现
使用有四个步骤：定义、打开、提取、关闭
例子：
09:52:04 SCOTT@std1> DECLARE 09:52:07 2 CURSOR c_cursor 09:52:07 3 IS SELECT ename, sal 09:52:07 4 FROM emp 09:52:07 5 WHERE rownum<11; 09:52:07 6 v_ename emp.ename%TYPE; 09:52:07 7 v_sal emp.ename%TYPE; 09:52:07 8 BEGIN 09:52:07 9 OPEN c_cursor; 09:52:07 10 FETCH c_cursor INTO v_ename, v_sal; 09:52:07 11 WHILE c_cursor%FOUND LOOP 09:52:07 12 DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) ); 09:52:07 13 FETCH c_cursor INTO v_ename, v_sal; 09:52:07 14 END LOOP; 09:52:07 15 CLOSE c_cursor; 09:52:08 16 END; 09:52:09 17 / SMITH---800 ALLEN---1600 WARD---1250 JONES---2975 MARTIN---1250 BLAKE---2850 CLARK---2450 SCOTT---3000 TURNER---1500 ADAMS---1100 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00
10:01:43 SCOTT@std1> DECLARE 11:31:04 2 deptrec dept%Rowtype; 11:31:04 3 dept_name dept.dname%TYPE; 11:31:04 4 dept_loc dept.loc%TYPE; 11:31:04 5 CURSOR c1 IS SELECT dname,loc FROM dept WHERE deptno<=30; 11:31:04 6 CURSOR c2(dept_no NUMBER DEFAULT 10) IS SELECT dname,loc FROM dept WHERE deptno <= dept_no; 11:31:04 7 CURSOR c3(dept_no NUMBER DEFAULT 10) IS SELECT * FROM dept WHERE deptno <= dept_no; 11:31:04 8 11:31:04 9 BEGIN 11:31:04 10 OPEN c1; 11:31:04 11 LOOP 11:31:04 12 FETCH c1 INTO dept_name,dept_loc; 11:31:04 13 EXIT WHEN c1%NOTFOUND; 11:31:04 14 dbms_output.put_line(dept_name||'---'||dept_loc); 11:31:04 15 END LOOP; 11:31:04 16 CLOSE c1; 11:31:04 17 11:31:04 18 OPEN c2; 11:31:04 19 LOOP 11:31:04 20 FETCH c2 INTO dept_name,dept_loc; 11:31:04 21 EXIT WHEN c2%NOTFOUND; 11:31:04 22 dbms_output.put_line(dept_name||'---'||dept_loc); 11:31:04 23 END LOOP; 11:31:04 24 CLOSE c2; 11:31:04 25 11:31:04 26 OPEN c3(dept_no => 20); 11:31:04 27 LOOP 11:31:04 28 FETCH c3 INTO deptrec; 11:31:04 29 EXIT WHEN c3%NOTFOUND; 11:31:04 30 dbms_output.put_line(deptrec.deptno||'---'||deptrec.dname||'---'||deptrec.loc); 11:31:04 31 END LOOP; 11:31:04 32 CLOSE c3; 11:31:04 33 END; 11:31:06 34 / ACCOUNTING---NEW YORK RESEARCH---DALLAS SALES---CHICAGO ACCOUNTING---NEW YORK 10---ACCOUNTING---NEW YORK 20---RESEARCH---DALLAS PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 11:31:07 SCOTT@std1>
游标属性： Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE； Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反； Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE； Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数 例子
15:04:04 SCOTT@std1> set serverout on
15:04:27 SCOTT@std1> DECLARE
15:04:40 2 v_empno emp.empno%TYPE;
15:04:40 3 v_sal emp.sal%TYPE;
15:04:40 4 CURSOR c_cursor IS SELECT empno,sal FROM emp;
15:04:40 5 BEGIN
15:04:40 6 OPEN c_cursor;
15:04:40 7 LOOP
15:04:40 8 FETCH c_cursor INTO v_empno,v_sal;
15:04:40 9 EXIT WHEN c_cursor%NOTFOUND;
15:04:40 10 IF v_sal<1200 THEN
15:04:40 11 UPDATE emp SET sal=sal+50 WHERE empno=v_empno;
15:04:41 12 dbms_output.put_line('编码为'||v_empno||'工资已更新！');
15:04:41 13 END IF;
15:04:41 14 END LOOP;
15:04:41 15 CLOSE c_cursor;
15:04:41 16 END;
15:04:42 17 /
编码为7369工资已更新！
编码为7876工资已更新！
编码为7900工资已更新！
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
15:04:43 SCOTT@std1>
15:04:43 SCOTT@std1> DECLARE
15:06:12 2 v_name emp.ename%TYPE;
15:06:12 3 v_job emp.job%TYPE;
15:06:12 4 CURSOR c1 IS SELECT ename,job FROM emp WHERE deptno=20;
15:06:12 5 BEGIN
15:06:12 6 OPEN c1;
15:06:12 7 LOOP
15:06:12 8 FETCH c1 INTO v_name,v_job;
15:06:12 9 IF c1%FOUND THEN
15:06:12 10 dbms_output.put_line(v_name||'的岗位是'||v_job);
15:06:12 11 ELSE
15:06:12 12 dbms_output.put_line('已经处理完结果');
15:06:12 13 EXIT;
15:06:12 14 END IF;
15:06:12 15 END LOOP;
15:06:12 16 CLOSE c1;
15:06:12 17 END;
15:06:14 18 /
SMITH的岗位是CLERK
JONES的岗位是MANAGER
SCOTT的岗位是ANALYST
ADAMS的岗位是CLERK
FORD的岗位是ANALYST
已经处理完结果
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
15:06:15 SCOTT@std1>
15:06:15 SCOTT@std1> DECLARE
15:38:26 2 v_ename emp.ename%TYPE;
15:38:26 3 v_hiredate emp.hiredate%TYPE;
15:38:26 4 CURSOR c1(dept_id NUMBER,jobid VARCHAR2) IS SELECT ename,hiredate FROM emp WHERE deptno=dept_id AND job=jobid;
15:38:26 5 BEGIN
15:38:26 6 OPEN c1(30,'SALESMAN');
15:38:26 7 LOOP
15:38:26 8 FETCH c1 INTO v_ename,v_hiredate;
15:38:26 9 IF c1%FOUND THEN
15:38:26 10 dbms_output.put_line(v_ename||'的雇佣日期是：'||v_hiredate);
15:38:26 11 ELSE
15:38:26 12 dbms_output.put_line('结果集处理完了');
15:38:26 13 EXIT;
15:38:26 14 END IF;
15:38:26 15 END LOOP;
15:38:26 16 CLOSE c1;
15:38:26 17 END;
15:38:27 18 /
ALLEN的雇佣日期是：20-FEB-81
WARD的雇佣日期是：22-FEB-81
MARTIN的雇佣日期是：28-SEP-81
TURNER的雇佣日期是：08-SEP-81
结果集处理完了
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
15:38:28 SCOTT@std1>
15:38:28 SCOTT@std1> DECLARE
16:22:36 2 TYPE emp_record_type IS RECORD(
16:22:36 3 v_ename emp.ename%TYPE,
16:22:36 4 v_hiredate emp.hiredate%TYPE);
16:22:36 5 v_emp_record emp_record_type;
16:22:36 6
16:22:36 7 CURSOR c1(dept_id NUMBER,jobid VARCHAR2) RETURN emp_record_type IS SELECT ename,hiredate FROM emp WHERE deptno=dept_id AND job=jobid;
16:22:36 8 BEGIN
16:22:36 9 OPEN c1(20,'CLERK');
16:22:36 10 LOOP
16:22:36 11 FETCH c1 INTO v_emp_record;
16:22:36 12 IF c1%FOUND THEN
16:22:36 13 dbms_output.put_line(v_emp_record.v_ename||'的雇佣日期是：'||v_emp_record.v_hiredate);
16:22:36 14 ELSE
16:22:36 15 dbms_output.put_line('结果集处理完了');
16:22:36 16 EXIT;
16:22:36 17 END IF;
16:22:36 18 END LOOP;
16:22:36 19 CLOSE c1;
16:22:36 20 END;
16:22:37 21 /
SMITH的雇佣日期是：17-DEC-80
ADAMS的雇佣日期是：23-MAY-87
结果集处理完了
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
16:22:38 SCOTT@std1>
16:22:38 SCOTT@std1> DECLARE
16:31:29 2 CURSOR c1(dept_id NUMBER,jobid VARCHAR2) IS SELECT ename,hiredate FROM emp WHERE deptno=dept_id AND job=jobid;
16:31:29 3 v_emp_record c1%ROWTYPE;
16:31:29 4 BEGIN
16:31:29 5 OPEN c1(20,'CLERK');
16:31:29 6 LOOP
16:31:29 7 FETCH c1 INTO v_emp_record;
16:31:29 8 IF c1%FOUND THEN
16:31:29 9 dbms_output.put_line(v_emp_record.ename||'的雇佣日期是：'||v_emp_record.hiredate);
16:31:29 10 ELSE
16:31:29 11 dbms_output.put_line('结果集处理完了');
16:31:29 12 EXIT;
16:31:29 13 END IF;
16:31:29 14 END LOOP;
16:31:29 15 CLOSE c1;
16:31:29 16 END;
16:31:30 17 /
SMITH的雇佣日期是：17-DEC-80
ADAMS的雇佣日期是：23-MAY-87
结果集处理完了
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:31:31 SCOTT@std1>
游标的FOR循环 能自动执行游标的open、fetch、close和循环 例子：
16:31:31 SCOTT@std1> DECLARE 16:43:57 2 CURSOR c1 IS SELECT deptno,ename,sal FROM emp; 16:43:57 3 BEGIN 16:43:57 4 FOR v1 IN c1 LOOP 16:43:57 5 dbms_output.put_line(v1.deptno||'---'||v1.ename||'---'||v1.sal); 16:43:57 6 END LOOP; 16:43:57 7 END; 16:43:58 8 / 20---SMITH---850 30---ALLEN---1600 30---WARD---1250 20---JONES---2975 30---MARTIN---1250 30---BLAKE---2850 10---CLARK---2450 20---SCOTT---3000 30---TURNER---1500 20---ADAMS---1150 30---JAMES---1000 20---FORD---3000 10---MILLER---1300 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 16:43:59 SCOTT@std1>
16:43:59 SCOTT@std1> DECLARE
16:58:03 2 CURSOR c1(dept_no NUMBER DEFAULT 10) IS SELECT deptno,dname,loc FROM dept WHERE deptno=dept_no;
16:58:03 3 BEGIN
16:58:03 4 dbms_output.put_line('当dept_no参数值为30:');
16:58:03 5 FOR v1 IN c1(30) LOOP dbms_output.put_line(v1.deptno||'---'||v1.dname||'---'||v1.loc);
16:58:03 6 END LOOP;
16:58:03 7 dbms_output.put_line('当dept_no参数值为10:');
16:58:03 8 FOR v1 IN c1 LOOP dbms_output.put_line(v1.deptno||'---'||v1.dname||'---'||v1.loc);
16:58:04 9 END LOOP;
16:58:04 10 END;
16:58:04 11 /
当dept_no参数值为30:
30---SALES---CHICAGO
当dept_no参数值为10:
10---ACCOUNTING---NEW YORK
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:58:05 SCOTT@std1>
PL/SQL还允许在游标FOR循环语句中使用子查询来实现游标的功能 例子
16:58:05 SCOTT@std1> BEGIN 17:03:37 2 FOR v1 IN (SELECT dname,loc FROM dept) LOOP 17:03:37 3 dbms_output.put_line(v1.dname||'---'||v1.loc); 17:03:37 4 END LOOP; 17:03:37 5 END; 17:03:38 6 / ACCOUNTING---NEW YORK RESEARCH---DALLAS SALES---CHICAGO OPERATIONS---BOSTON PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 17:03:38 SCOTT@std1>
隐式游标
例: 删除EMPLOYEES表中某部门的所有员工，如果该部门中已没有员工，则在DEPARTMENT表中删除该部门
17:36:32 SCOTT@std1> DECLARE 17:36:53 2 v_deptno emp.deptno%TYPE:=&p_deptno; 17:36:53 3 BEGIN 17:36:53 4 DELETE FROM emp WHERE deptno=v_deptno; 17:36:53 5 IF SQL%NOTFOUND THEN 17:36:53 6 DELETE FROM dept WHERE deptno=v_deptno; 17:36:53 7 END IF; 17:36:53 8 END; 17:36:54 9 / Enter value for p_deptno: 10 old 2: v_deptno emp.deptno%TYPE:=&p_deptno; new 2: v_deptno emp.deptno%TYPE:=10; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 17:36:58 SCOTT@std1>
获取更新行数
08:48:43 SYS@std1> conn scott/tiger;
Connected.
08:48:49 SCOTT@std1> DECLARE
08:54:12 2 v_rows NUMBER;
08:54:12 3 BEGIN
08:54:12 4 UPDATE emp SET sal=30000 WHERE deptno=30;
08:54:12 5 v_rows:=SQL%ROWCOUNT;
08:54:12 6 dbms_output.put_line('更新了'||v_rows||'个雇员的工资');
08:54:12 7 ROLLBACK;
08:54:12 8 END;
08:54:13 9 /
更新了6个雇员的工资
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
08:54:14 SCOTT@std1>
NO_DATA_FOUND 和 %NOTFOUND的区别: SELECT … INTO 语句触发 NO_DATA_FOUND 当一个显式游标的WHERE子句未找到时触发%NOTFOUND 当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND 在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND 使用游标更新和删除数据 游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列 为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止 语法：
ORA-0054 ：resource busy and acquire with nowait specified.
>如果使用 FOR UPDATE 声明游标，则可在DELETE和UPDATE 语句中使用WHERE CURRENT OF cursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行 例子
08:54:14 SCOTT@std1> DECLARE 09:21:38 2 v_deptno emp.deptno%TYPE:=&p_deptno; 09:21:38 3 CURSOR emp_cursor IS SELECT empno,sal FROM emp WHERE deptno=v_deptno FOR UPDATE NOWAIT; 09:21:38 4 BEGIN 09:21:38 5 FOR emp_record IN emp_cursor LOOP 09:21:38 6 IF emp_record.sal<1500 THEN 09:21:38 7 UPDATE emp SET sal=1500 WHERE CURRENT OF emp_cursor; 09:21:38 8 END IF; 09:21:38 9 END LOOP; 09:21:38 10 END; 09:21:40 11 / Enter value for p_deptno: 30 old 2: v_deptno emp.deptno%TYPE:=&p_deptno; new 2: v_deptno emp.deptno%TYPE:=30; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 09:21:43 SCOTT@std1>
09:36:06 SCOTT@std1> DECLARE 09:36:11 2 v_emp_record emp%ROWTYPE; 09:36:11 3 CURSOR c1 IS SELECT * FROM emp FOR UPDATE; 09:36:11 4 BEGIN 09:36:11 5 OPEN c1; 09:36:11 6 LOOP 09:36:11 7 FETCH c1 INTO v_emp_record; 09:36:12 8 EXIT WHEN c1%NOTFOUND; 09:36:12 9 IF v_emp_record.deptno=30 AND v_emp_record.job='SALESMAN' THEN 09:36:12 10 UPDATE emp SET sal=20000 WHERE CURRENT OF c1; 09:36:12 11 END IF; 09:36:12 12 END LOOP; 09:36:12 13 CLOSE c1; 09:36:12 14 END; 09:36:12 15 / PL/SQL procedure successfully completed. Elapsed: 00:00:04.39 09:36:17 SCOTT@std1>
游标变量 与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针 但与游标不同的是，游标变量是动态的，而游标是静态的 游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可 游标变量为一个指针，它属于参照类型，所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量类型
TYPE ref_type_name IS REF CURSOR [ RETURN return_type];
其中：ref_type_name为新定义的游标变量类型名称； return_type 为游标变量的返回值类型，它必须为记录变量 在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型 简单的来说：强类型的动态游标是指带有return返回语句的，而弱类型的动态游标是指不带return语句的（也即，弱类型的动态游标可以与任何查询语句匹配，但是强类型的动态游标只能与特定的查询语句匹配。） 声明一个游标变量的两个步骤： 步骤一：定义一个REF CURSOU数据类型，如： TYPE ref_cursor_type IS REF CURSOR; 步骤二：声明一个该数据类型的游标变量，如： cv_ref REF_CURSOR_TYPE; 例子：创建两个强类型定义游标变量和一个弱类型游标变量
DECLARE TYPE deptrecord IS RECORD( deptno dept.deptno%TYPE, dname dept.dname%TYPE, loc dept.loc%TYPE); TYPE depttype1 IS REF CURSOR RETURN dept%ROWTYPE; TYPE depttype2 IS REF CURSOR RETURN deptrecord; TYPE curtype IS REF CURSOR; dept1 depttype1; dept2 depttype2; cr1 curtype;
游标变量操作同样是打开、提取、关闭 打开
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
其中：cursor_variable_name为游标变量，host_cursor_variable_name为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。 OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放 提取
FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称；variable和record_variable分别为普通变量和记录变量名称 关闭
CLOSE {cursor_variable_name | :host_cursor_variable_name}
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称，如果应用程序试图关闭一个未打开的游标变量，则将导致INVALID_CURSOR异常错误。 例子：
09:36:17 SCOTT@std1> DECLARE 10:41:56 2 TYPE emp_job_rec IS RECORD( 10:41:56 3 empno emp.empno%TYPE, 10:41:56 4 ename emp.ename%TYPE, 10:41:56 5 job emp.job%TYPE 10:41:56 6 ); 10:41:57 7 TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec; 10:41:57 8 emp_refcur emp_job_refcur_type; 10:41:57 9 emp_job emp_job_rec; 10:41:57 10 BEGIN 10:41:57 11 OPEN emp_refcur FOR 10:41:57 12 SELECT empno,ename,job FROM emp ORDER BY deptno; 10:41:57 13 10:41:57 14 FETCH emp_refcur INTO emp_job; 10:41:57 15 WHILE emp_refcur%FOUND LOOP 10:41:57 16 dbms_output.put_line(emp_job.empno||':'||emp_job.ename||'is a '||emp_job.job); 10:41:57 17 FETCH emp_refcur INTO emp_job; 10:41:57 18 END LOOP; 10:41:57 19 10:41:57 20 CLOSE emp_refcur; 10:41:57 21 END; 10:41:58 22 / 7369:SMITHis a CLERK 7876:ADAMSis a CLERK 7566:JONESis a MANAGER 7788:SCOTTis a ANALYST 7902:FORDis a ANALYST 7900:JAMESis a CLERK 7844:TURNERis a SALESMAN 7654:MARTINis a SALESMAN 7521:WARDis a SALESMAN 7499:ALLENis a SALESMAN 7698:BLAKEis a MANAGER PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 10:41:58 SCOTT@std1>
10:41:58 SCOTT@std1> DECLARE
11:38:42 2 Type refcur_t IS REF CURSOR;
11:38:42 3 Refcur refcur_t;
11:38:42 4 TYPE sample_rec_type IS RECORD (
11:38:42 5 Id number,
11:38:42 6 Description VARCHAR2 (30)
11:38:42 7 );
11:38:42 8 sample sample_rec_type;
11:38:42 9 selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
11:38:42 10 BEGIN
11:38:42 11 IF selection='D' THEN
11:38:42 12 OPEN refcur FOR
11:38:42 13 SELECT deptno,dname FROM dept;
11:38:42 14 dbms_output.put_line('Department Data');
11:38:42 15 ELSE
11:38:42 16 OPEN refcur FOR
11:38:42 17 SELECT empno,ename FROM emp;
11:38:42 18 dbms_output.put_line('Employee Data');
11:38:42 19 RETURN;
11:38:42 20 END IF;
11:38:42 21
11:38:42 22 dbms_output.put_line('----------------------------');
11:38:42 23 FETCH refcur INTO sample;
11:38:42 24 WHILE refcur%FOUND LOOP
11:38:42 25 dbms_output.put_line(sample.id||':'||sample.DESCRIPTION);
11:38:42 26 FETCH refcur INTO sample;
11:38:43 27 END LOOP;
11:38:43 28 CLOSE refcur;
11:38:43 29 END;
11:38:43 30 /
Enter value for tab: D
old 9: selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
new 9: selection varchar2(1) := UPPER (SUBSTR ('D', 1, 1));
Department Data
----------------------------
20:RESEARCH
30:SALES
40:OPERATIONS
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
11:38:46 SCOTT@std1>
11:38:46 SCOTT@std1> DECLARE 12:04:38 2 TYPE emp_cursor_type IS REF CURSOR; 12:04:38 3 c1 emp_cursor_type; 12:04:38 4 v_emp_record emp%ROWTYPE; 12:04:38 5 v_reg_record dept%ROWTYPE; 12:04:38 6 BEGIN 12:04:38 7 OPEN c1 FOR SELECT * FROM emp WHERE deptno=20; 12:04:38 8 LOOP 12:04:38 9 FETCH c1 INTO v_emp_record; 12:04:38 10 EXIT WHEN c1%NOTFOUND; 12:04:38 11 dbms_output.put_line(v_emp_record.ename||'的雇佣日期：'||v_emp_record.hiredate); 12:04:38 12 END LOOP; 12:04:38 13 OPEN c1 FOR SELECT * FROM dept; 12:04:38 14 LOOP 12:04:38 15 FETCH c1 INTO v_reg_record; 12:04:38 16 EXIT WHEN c1%NOTFOUND; 12:04:38 17 dbms_output.put_line(v_reg_record.deptno||'：'||v_reg_record.dname); 12:04:38 18 END LOOP; 12:04:38 19 CLOSE c1; 12:04:38 20 END; 12:04:38 21 / SMITH的雇佣日期：17-DEC-80 JONES的雇佣日期：02-APR-81 SCOTT的雇佣日期：19-APR-87 ADAMS的雇佣日期：23-MAY-87 FORD的雇佣日期：03-DEC-81 20：RESEARCH 30：SALES 40：OPERATIONS PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 12:04:39 SCOTT@std1>
作者：九命猫幺
博客出处：http://www.cnblogs.com/yongestcat/
欢迎转载，转载请标明出处。
如果你觉得本文还不错，对你的学习带来了些许帮助，请帮忙点击右下角的推荐
博客出处：http://www.cnblogs.com/yongestcat/
欢迎转载，转载请标明出处。
如果你觉得本文还不错，对你的学习带来了些许帮助，请帮忙点击右下角的推荐
