有一存储过程,其中有一个参数,这个参数是用户输入的表名,在存储过程里面根据传入的表名做一个查询,并将查询的结果显示出来.
create procedure test(tableName varchar)
as
begin
select * from tableName --这里如何实现???????????????
end;
...全文
4129打赏收藏
如何执行动态表名的查询???
有一存储过程,其中有一个参数,这个参数是用户输入的表名,在存储过程里面根据传入的表名做一个查询,并将查询的结果显示出来. create procedure test(tableName varchar) as begin select * from tableName --这里如何实现??????????????? end;
>包的定义
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;
下面的例子可以实现sqlplus中desc tablename的功能
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;