1) create or replace procedure DataSet_SingleRows
(para out number)
is
i number := 0;
n number := 1;
cursor cursor1 is select dm from tmp_proc;
begin
select count(1) into i from user_objects where object_name='TMP_PROC';
if (i > 0) then
execute immediate 'drop table tmp_proc';
end if;
execute immediate 'create global temporary table tmp_proc (dm number,mc number)';
while (n < 10)
loop
execute immediate 'insert into tmp_proc values(,)';
n := n + 1;
end loop;
open cursor1;
fetch cursor1 into para;
end DataSet_SingleRows;
如果事先没有这张临时表的表,执行这个存储过程就会抛错,怎么解决?2) 如何通过procedure返回一个结果集? 上面也只能返回一条记录.
(para out number)
is
i number := 0;
n number := 1;
cursor cursor1 is select dm from tmp_proc;
begin
select count(1) into i from user_objects where object_name='TMP_PROC';
if (i > 0) then
execute immediate 'drop table tmp_proc';
end if;
execute immediate 'create global temporary table tmp_proc (dm number,mc number)';
while (n < 10)
loop
execute immediate 'insert into tmp_proc values(,)';
n := n + 1;
end loop;
open cursor1;
fetch cursor1 into para;
end DataSet_SingleRows;
如果事先没有这张临时表的表,执行这个存储过程就会抛错,怎么解决?2) 如何通过procedure返回一个结果集? 上面也只能返回一条记录.
解决方案 »
- Hibernate Oracle 数据库主键 如何设计?
- 请问数据库的问题!
- 如何在存储过程中解析BLOB中的中文?
- 初学者求教一个关于查询的问题,如果有重复结果就取最新的怎么写sql
- 带参数存储过程的问题
- 求助 database link 问题,大家来帮忙..
- 数据提交后可不可以回滚。。急。。急。。急。。
- 数据库外键该不该使用?
- 如何用PL/SQL语句把一个数据表LONG数据类型的数据转到另一张表的LONG字段中去?
- Oracle-TNS:could not resolve the connect identifier specified
- 请教一个分组查询的SQL
- 帮忙查一下这个trigger错哪了,谢谢!
1.
CREATE OR REPLACE PROCEDURE showemps (where_in IN VARCHAR2 := NULL)
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rec emp%ROWTYPE; fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE (cur, 'SELECT empno, ename FROM emp ' ||
' WHERE ' || NVL (where_in, '1=1'), DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
DBMS_SQL.DEFINE_COLUMN (cur, 2, 'a', 60); fdbk := DBMS_SQL.EXECUTE (cur);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
DBMS_SQL.COLUMN_VALUE (cur, 1, rec.empno);
DBMS_SQL.COLUMN_VALUE (cur, 2, rec.ename);
DBMS_OUTPUT.PUT_LINE (TO_CHAR (rec.empno) || '=' || rec.ename);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;2.
CREATE OR REPLACE PACKAGE pkg_sql AS
TYPE myrcType IS REF CURSOR;
FUNCTION get(strsql VARCHAR) RETURN myrcType;
END pkg_sql;
CREATE OR REPLACE PACKAGE BODY PKG_SQL AS
FUNCTION get(strsql IN VARCHAR) RETURN myrcType IS
rc myrcType;
BEGIN
OPEN rc FOR strsql;
RETURN rc;
END get;
END pkg_sql;