是这样吗? create or replace procedure procedure1(tablename varchar2,l_cursor out package1.cursor1) is str1 varchar2(100); begin str1:='select * from '|| tablename ; open l_cursor for str1; end; /
试试使用dbms_sql 语句执行动态sql语句,
根据传入的参数,查询不同的表?用动态游标就OK!CREATE OR REPLACE PACKAGE BODY "NB_SUPFIND" AS
PROCEDURE RecSel1( p_StartDate IN CHAR, p_EndDate IN CHAR, p_Region IN VARCHAR2, --分区内容 p_col IN VARCHAR2, --选中项目名 p_table IN VARCHAR2, --操作表名 v_ADDV OUT myrctype --作为输出的游标 ) IS
strsql VARCHAR2(500);
BEGIN
strsql:='SELECT ADDVNM region,YY,SUM('||p_col||') item FROM '||p_table|| ' WHERE YY <= TO_NUMBER('||p_EndDate||') AND YY>=TO_NUMBER('|| p_StartDate||') AND ADDVNM='''||p_Region|| ''' GROUP BY ADDVNM,YY';
str1:='select * from table1';
open cursor for str1;
中的str1改为open cursor for select * from table1,则速度很快.
这句应该是
open l_cursor for str1; 吧?
l_cursor package1.cursor1;
begin
procedure1(l_cursor);
end;
/
create or replace procedure procedure1(tablename varchar2,l_cursor out package1.cursor1)
is
str1 varchar2(100);
begin
str1:='select * from '|| tablename ;
open l_cursor for str1;
end;
/
AS
PROCEDURE RecSel1(
p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
) IS
strsql VARCHAR2(500);
BEGIN
strsql:='SELECT ADDVNM region,YY,SUM('||p_col||') item
FROM '||p_table||
' WHERE YY <= TO_NUMBER('||p_EndDate||') AND YY>=TO_NUMBER('|| p_StartDate||') AND ADDVNM='''||p_Region||
''' GROUP BY ADDVNM,YY';
--打开动态游标
OPEN v_ADDV FOR strsql;
END RecSel1;