Oracle PLSQL游標(biāo)、游標(biāo)變量的使用
參考文章：http://www.rzrgm.cn/huyong/archive/2011/05/04/2036377.html
在 PL/SQL 程序中，對(duì)于處理多行記錄的事務(wù)經(jīng)常使用游標(biāo)來(lái)實(shí)現(xiàn)
使用有四個(gè)步驟：定義、打開(kāi)、提取、關(guān)閉
例子：
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>
游標(biāo)屬性： Cursor_name%FOUND 布爾型屬性，當(dāng)最近一次提取游標(biāo)操作FETCH成功則為 TRUE,否則為FALSE； Cursor_name%NOTFOUND 布爾型屬性，與%FOUND相反； Cursor_name%ISOPEN 布爾型屬性，當(dāng)游標(biāo)已打開(kāi)時(shí)返回 TRUE； Cursor_name%ROWCOUNT 數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù) 例子
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('已經(jīng)處理完結(jié)果');
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
已經(jīng)處理完結(jié)果
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('結(jié)果集處理完了');
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
結(jié)果集處理完了
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('結(jié)果集處理完了');
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
結(jié)果集處理完了
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('結(jié)果集處理完了');
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
結(jié)果集處理完了
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:31:31 SCOTT@std1>
游標(biāo)的FOR循環(huán) 能自動(dòng)執(zhí)行游標(biāo)的open、fetch、close和循環(huán) 例子：
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('當(dāng)dept_no參數(shù)值為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('當(dāng)dept_no參數(shù)值為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 /
當(dāng)dept_no參數(shù)值為30:
30---SALES---CHICAGO
當(dāng)dept_no參數(shù)值為10:
10---ACCOUNTING---NEW YORK
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:58:05 SCOTT@std1>
PL/SQL還允許在游標(biāo)FOR循環(huán)語(yǔ)句中使用子查詢來(lái)實(shí)現(xiàn)游標(biāo)的功能 例子
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>
隱式游標(biāo)
例: 刪除EMPLOYEES表中某部門的所有員工，如果該部門中已沒(méi)有員工，則在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>
獲取更新行數(shù)
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||'個(gè)雇員的工資');
08:54:12 7 ROLLBACK;
08:54:12 8 END;
08:54:13 9 /
更新了6個(gè)雇員的工資
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
08:54:14 SCOTT@std1>
NO_DATA_FOUND 和 %NOTFOUND的區(qū)別: SELECT … INTO 語(yǔ)句觸發(fā) NO_DATA_FOUND 當(dāng)一個(gè)顯式游標(biāo)的WHERE子句未找到時(shí)觸發(fā)%NOTFOUND 當(dāng)UPDATE或DELETE 語(yǔ)句的WHERE 子句未找到時(shí)觸發(fā) SQL%NOTFOUND 在提取循環(huán)中要用 %NOTFOUND 或%FOUND 來(lái)確定循環(huán)的退出條件，不要用 NO_DATA_FOUND 使用游標(biāo)更新和刪除數(shù)據(jù) 游標(biāo)修改和刪除操作是指在游標(biāo)定位下，修改或刪除表中指定的數(shù)據(jù)行。這時(shí)，要求游標(biāo)查詢語(yǔ)句中必須使用FOR UPDATE選項(xiàng)，以便在打開(kāi)游標(biāo)時(shí)鎖定游標(biāo)結(jié)果集合在表中對(duì)應(yīng)數(shù)據(jù)行的所有列和部分列 為了對(duì)正在處理(查詢)的行不被另外的用戶改動(dòng)，ORACLE 提供一個(gè) FOR UPDATE 子句來(lái)對(duì)所選擇的行進(jìn)行鎖住。該需求迫使ORACLE鎖定游標(biāo)結(jié)果集合的行，可以防止其他事務(wù)處理更新或刪除相同的行，直到您的事務(wù)處理提交或回退為止 語(yǔ)法：
ORA-0054 ：resource busy and acquire with nowait specified.
>如果使用 FOR UPDATE 聲明游標(biāo)，則可在DELETE和UPDATE 語(yǔ)句中使用WHERE CURRENT OF cursor_name子句，修改或刪除游標(biāo)結(jié)果集合當(dāng)前行對(duì)應(yīng)的數(shù)據(jù)庫(kù)表中的數(shù)據(jù)行 例子
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>
游標(biāo)變量 與游標(biāo)一樣，游標(biāo)變量也是一個(gè)指向多行查詢結(jié)果集合中當(dāng)前數(shù)據(jù)行的指針 但與游標(biāo)不同的是，游標(biāo)變量是動(dòng)態(tài)的，而游標(biāo)是靜態(tài)的 游標(biāo)只能與指定的查詢相連，即固定指向一個(gè)查詢的內(nèi)存處理區(qū)域，而游標(biāo)變量則可與不同的查詢語(yǔ)句相連，它可以指向不同查詢語(yǔ)句的內(nèi)存處理區(qū)域（但不能同時(shí)指向多個(gè)內(nèi)存處理區(qū)域，在某一時(shí)刻只能與一個(gè)查詢語(yǔ)句相連），只要這些查詢語(yǔ)句的返回類型兼容即可 游標(biāo)變量為一個(gè)指針，它屬于參照類型，所以在聲明游標(biāo)變量類型之前必須先定義游標(biāo)變量類型。在PL/SQL中，可以在塊、子程序和包的聲明區(qū)域內(nèi)定義游標(biāo)變量類型
TYPE ref_type_name IS REF CURSOR [ RETURN return_type];
其中：ref_type_name為新定義的游標(biāo)變量類型名稱； return_type 為游標(biāo)變量的返回值類型，它必須為記錄變量 在定義游標(biāo)變量類型時(shí)，可以采用強(qiáng)類型定義和弱類型定義兩種。強(qiáng)類型定義必須指定游標(biāo)變量的返回值類型，而弱類型定義則不說(shuō)明返回值類型 簡(jiǎn)單的來(lái)說(shuō)：強(qiáng)類型的動(dòng)態(tài)游標(biāo)是指帶有return返回語(yǔ)句的，而弱類型的動(dòng)態(tài)游標(biāo)是指不帶return語(yǔ)句的（也即，弱類型的動(dòng)態(tài)游標(biāo)可以與任何查詢語(yǔ)句匹配，但是強(qiáng)類型的動(dòng)態(tài)游標(biāo)只能與特定的查詢語(yǔ)句匹配。） 聲明一個(gè)游標(biāo)變量的兩個(gè)步驟： 步驟一：定義一個(gè)REF CURSOU數(shù)據(jù)類型，如： TYPE ref_cursor_type IS REF CURSOR; 步驟二：聲明一個(gè)該數(shù)據(jù)類型的游標(biāo)變量，如： cv_ref REF_CURSOR_TYPE; 例子：創(chuàng)建兩個(gè)強(qiáng)類型定義游標(biāo)變量和一個(gè)弱類型游標(biāo)變量
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;
游標(biāo)變量操作同樣是打開(kāi)、提取、關(guān)閉 打開(kāi)
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
其中：cursor_variable_name為游標(biāo)變量，host_cursor_variable_name為PL/SQL主機(jī)環(huán)境（如OCI: ORACLE Call Interface，Pro*c 程序等）中聲明的游標(biāo)變量。 OPEN…FOR 語(yǔ)句可以在關(guān)閉當(dāng)前的游標(biāo)變量之前重新打開(kāi)游標(biāo)變量，而不會(huì)導(dǎo)致CURSOR_ALREAD_OPEN異常錯(cuò)誤。新打開(kāi)游標(biāo)變量時(shí)，前一個(gè)查詢的內(nèi)存處理區(qū)將被釋放 提取
FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};
其中：cursor_variable_name和host_cursor_variable_name分別為游標(biāo)變量和宿主游標(biāo)變量名稱；variable和record_variable分別為普通變量和記錄變量名稱 關(guān)閉
CLOSE {cursor_variable_name | :host_cursor_variable_name}
其中：cursor_variable_name和host_cursor_variable_name分別為游標(biāo)變量和宿主游標(biāo)變量名稱，如果應(yīng)用程序試圖關(guān)閉一個(gè)未打開(kāi)的游標(biāo)變量，則將導(dǎo)致INVALID_CURSOR異常錯(cuò)誤。 例子：
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.rzrgm.cn/yongestcat/
歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)標(biāo)明出處。
如果你覺(jué)得本文還不錯(cuò)，對(duì)你的學(xué)習(xí)帶來(lái)了些許幫助，請(qǐng)幫忙點(diǎn)擊右下角的推薦
博客出處：http://www.rzrgm.cn/yongestcat/
歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)標(biāo)明出處。
如果你覺(jué)得本文還不錯(cuò)，對(duì)你的學(xué)習(xí)帶來(lái)了些許幫助，請(qǐng)幫忙點(diǎn)擊右下角的推薦
浙公網(wǎng)安備 33010602011771號(hào)