解决方案 »
- dbms_rls.add_policy的问题
- Oracle Berkeley DB 使用期间发生错误 请问如何解决 谢谢大家
- 怎样让数据文件脱机
- oracle9i的用户的数据能导入到oracle8.0.3i吗
- 问一条语句
- 请教:这个Access Sql转换成Oracle Sql 怎么写?
- 请问哪里可以下oracle8.17 for window ?
- 一个从SQLSERVER的语句转化成ORACLE的问题
- Lastdrop(空杯) 请进!
- 导出/导入 简单问题,多谢了!
- 请教各位大牛:影响sqlite存储空间占用的因素有哪些?怎么降低空间占用?
- oracle10g rac中的节点上crs无法启动
必须用存储过程对这个游标进行处理,才能展示查询结果本来可以直接查询一张表,你把结果集放到游标里,自然要解析游标才能看到结果
像java这样的直接接收返回的游标就可以
类似plsql的查询分析器语句是什么意思,只能用类似select .. from 这样的语法?
不过如果结果集较大,不建议这么用SQL> CREATE OR REPLACE package pk_wt
2 is
3 type mytype is ref cursor;
4 procedure p_wt(mycs out mytype);
5 end;
6 /
Package created
SQL>
SQL> CREATE OR REPLACE package BODY pk_wt
2 is
3 procedure p_wt(mycs out mytype)
4 is
5 begin
6 open mycs for select * from scott.emp;
7 end p_wt;
8 end pk_wt;
9 /
Package body created
SQL>
SQL> create type tp_point1 as object(empno NUMBER(4),ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE
2 ,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2))
3 /
Type created
SQL>
SQL> create type tp_point1newt as table of tp_point1
2 /
Type created
SQL>
SQL> create or replace function func_wt return tp_point1newt pipelined
2 as
3 v_mycs pk_wt.mytype;
4 v_empno NUMBER(4);
5 v_ENAME VARCHAR2(10);
6 v_JOB VARCHAR2(9);
7 v_MGR NUMBER(4);
8 v_HIREDATE DATE;
9 v_SAL NUMBER(7,2);
10 v_COMM NUMBER(7,2);
11 v_DEPTNO NUMBER(2);
12 begin
13 pk_wt.p_wt(v_mycs);
14 loop
15 fetch v_mycs into v_empno,v_ENAME,v_JOB,v_MGR,v_HIREDATE,v_SAL,v_COMM,v_DEPTNO;
16 exit when v_mycs%notfound;
17 pipe row(tp_point1(v_empno,v_ENAME,v_JOB,v_MGR,v_HIREDATE,v_SAL,v_COMM,v_DEPTNO));
18 end loop;
19 return;
20 end;
21 /
Function created
SQL> select * from table(func_wt);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 800 20
7499 ALLEN SALESMAN 7698 1981/02/20 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 1250 500 30
7566 JONES MANAGER 7839 1981/04/02 2975 20
7654 MARTIN SALESMAN 7698 1981/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/05/01 2850 30
7782 CLARK MANAGER 7839 1981/06/09 2450 10
7788 SCOTT ANALYST 7566 1987/04/19 3000 20
7839 KING PRESIDENT 1981/11/17 5000 10
7844 TURNER SALESMAN 7698 1981/09/08 1500 0 30
7876 ADAMS CLERK 7788 1987/05/23 1100 20
7900 JAMES CLERK 7698 1981/12/03 950 30
7902 FORD ANALYST 7566 1981/12/03 3000 20
7934 MILLER CLERK 7782 1982/01/23 1300 10
14 rows selected
SQL>