有一存储过程,其中有一个参数,这个参数是用户输入的表名,在存储过程里面根据传入的表名做一个查询,并将查询的结果显示出来.
create procedure test(tableName varchar)
as
begin
select * from tableName --这里如何实现???????????????
end;
create procedure test(tableName varchar)
as
begin
select * from tableName --这里如何实现???????????????
end;
pl/sql中select一定要有into
上面的动态sql可以用
exec 'select * from ' tableName;
好象execute immediate只能返回单行记录.而且我使用过这种方法,但无法在缓冲区显示输出结果.
恢复Eric_1999:
我就是要实现根据用户输入的表,查询所对应的表记录功能的存储过程.但不知道怎么做?
或者创建动态的cursor等等!
ex:
set serverout on size 10000000;
select * from &tablename;
set serverout off;保存为c:\test.sql
然后执行 @c:\test.sql
我查了DBMS_SQL的一些资料,不过不太明白,你可否举个例子.
to xiaoxiao1984:
我需要创建的是存储过过程,可否讲解一下.
declare
c number;
d number;
col_cnt integer;
f boolean;
rec_tab dbms_sql.desc_tab;
col_num number;
procedure print_rec(rec in dbms_sql.desc_rec) is
begin
dbms_output.new_line;
dbms_output.put_line('col_type = '
|| rec.col_type);
dbms_output.put_line('col_maxlen = '
|| rec.col_max_len);
dbms_output.put_line('col_name = '
|| rec.col_name);
dbms_output.put_line('col_name_len = '
|| rec.col_name_len);
dbms_output.put_line('col_schema_name = '
|| rec.col_schema_name);
dbms_output.put_line('col_schema_name_len = '
|| rec.col_schema_name_len);
dbms_output.put_line('col_precision = '
|| rec.col_precision);
dbms_output.put_line('col_scale = '
|| rec.col_scale);
dbms_output.put('col_null_ok = ');
if (rec.col_null_ok) then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
begin
c := dbms_sql.open_cursor; dbms_sql.parse(c, 'select * from scott.bonus', dbms_sql);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab); col_num := rec_tab.first;
if (col_num is not null) then
loop
print_rec(rec_tab(col_num));
col_num := rec_tab.next(col_num);
exit when (col_num is null);
end loop;
end if;
dbms_sql.close_cursor(c);
end;
/同理可以取出你动态表明的各个列的类型然后动态建立cursor,fetch出结果
1) 包头
create or replace package mypk
as
type t_cursor is ref cursor;
procedure proc(name varchar2,c out t_cursor,a number);
end;2) 包体
create or replace package body mypk
as
procedure proc(name varchar2,c out t_cursor,a number)
as
begin
open c for select * from test where id=a and name=name;
end proc;
end;