用游标一样可以做到.SQL> DECLARE
2 TYPE refCousor IS REF CURSOR;
3 cur_sal refCousor;
4 Type_job emp.job%TYPE;
5 BEGIN
6 OPEN cur_sal FOR SELECT DISTINCT job FROM emp WHERE sal > 2000;
7 LOOP
8 FETCH cur_sal INTO Type_job;
9 EXIT WHEN cur_sal%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE('JOB is:'||Type_job);
11 END LOOP;
12 CLOSE cur_sal;
13 END;
14 /
JOB is:ANALYST
JOB is:MANAGER
JOB is:PRESIDENTPL/SQL 过程已成功完成。
2 TYPE refCousor IS REF CURSOR;
3 cur_sal refCousor;
4 Type_job emp.job%TYPE;
5 BEGIN
6 OPEN cur_sal FOR SELECT DISTINCT job FROM emp WHERE sal > 2000;
7 LOOP
8 FETCH cur_sal INTO Type_job;
9 EXIT WHEN cur_sal%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE('JOB is:'||Type_job);
11 END LOOP;
12 CLOSE cur_sal;
13 END;
14 /
JOB is:ANALYST
JOB is:MANAGER
JOB is:PRESIDENTPL/SQL 过程已成功完成。
解决方案 »
- 各位好,下面这个语句在如果去掉最外面一层group by很快,加上则很慢,大侠们帮忙分析一下,谢谢
- ORACLE中如何在一个存储过程中调用另外一个存储过程?
- 刚写错了,再求一遍
- 大家请帮帮我,我安装Oracle 10g时候遇到好多问题.
- ORACLE 10G 函数问题
- 如何对一个clob类型的字段进行模糊查询???
- 存储过程中,传递字符串类型参数,提示java.sql.SQLException: ORA-00904: "采购部": 标识符无效
- for in 循环
- 如何比对两个日期差几个月?日期格式包括秒或者是天
- Oracle中最长能对多长的字符串建立索引?
- blob SQL 为何出错ORA-00600
- 急!急! SQL脚本向Oracle脚本转换中的几个问题.
如 chanet(牧师) 所说的,你可以用些中间变量存储一些结果,应该可以满足了吧
16:27:03 SQL> desc t1;
名称 空? 类型
----------------------------------------- -------- ---------------------------- A NUMBER(38)16:27:19 SQL> select * from t1;
1
2
3已用时间: 00: 00: 00.16
16:27:22 SQL> create or replace procedure tes as
16:27:26 2 type refcursor is ref cursor;
16:27:26 3 i integer:=1;
16:27:26 4 tp_a integer;
16:27:26 5 cur refcursor;
16:27:26 6 type arra is array(10) of integer;
16:27:26 7 arr1 arra:=arra();
16:27:26 8 begin
16:27:26 9 open cur for select a from t1;
16:27:26 10 loop
16:27:26 11 fetch cur into tp_a;
16:27:26 12 exit when cur%notfound;
16:27:26 13 arr1.extend;
16:27:26 14 arr1(i):=tp_a;
16:27:26 15 dbms_output.put_line(arr1(i));
16:27:26 16 i:=i+1;
16:27:26 17 end loop;
16:27:26 18 close cur;
16:27:26 19 end;
16:27:26 20 /过程已创建。已用时间: 00: 00: 00.32
16:27:26 SQL>
16:27:26 SQL> exec tes
1
2
3PL/SQL 过程已成功完成。已用时间: 00: 00: 00.15