返回PL/SQL和变量表名称的数据集
我想写一个PL/SQL函数来存储一个变量表名称的select语句(有点奇怪,我知道但它实际上是一个很好的设计决策) 。以下代码不起作用...但我不确定如何同时使用变量表名(构建查询)并返回数据集。任何人都有这方面的经验? TIA。返回PL/SQL和变量表名称的数据集
CREATE OR REPLACE FUNCTION fn_netstat_all (casename in varchar2)
RETURN resultset_subtype
IS
dataset resultset_subtype;
v_sql varchar2(25000);
v_tablename varchar2(50);
begin
v_sql := 'SELECT * FROM ' || casename || '_netstat;';
OPEN dataset FOR
execute immediate v_sql;
return dataset;
end;
如果您resultset_subtype是ref_cursor(或只需更换resultset_subtype用ref_cursor),你可以:
CREATE OR REPLACE
FUNCTION fn_netstat_all (
casename IN VARCHAR2
)
RETURN resultset_subtype
IS
dataset resultset_subtype;
BEGIN
OPEN dataset
FOR 'SELECT * FROM ' || casename || '_netstat';
RETURN dataset;
END fn_netstat_all;
FWIW,你可能想看看进入DBMS_ASSERT包装来包装casename变量来帮助防止动态SQL中的SQL注入攻击。
希望它有帮助...
用于SQL注入建议的+1。 – DCookie
Ollie,该功能不起作用。它在行中给我一个无效的字符错误: OPEN dataset FOR'SELECT * FROM'|| casename || '_netstat;'; – coergo
@coergo你有额外的分号(;)。正确的形式是“SELECT * FROM”|| casename || '_netstat';' – user272735
下面假设所有的表都是类似的。也可以在不使用DBMS_SQL的情况下选择每个表中相似的柱子的子集。我也注意到Ollie提到的SQL注入。
create table so9at (
id number(1),
data varchar2(5)
);
insert into so9at values (1, 'A-AAA');
insert into so9at values (2, 'A-BBB');
insert into so9at values (3, 'A-CCC');
create table so9bt (
id number(1),
data varchar2(5)
);
insert into so9bt values (5, 'B-AAA');
insert into so9bt values (6, 'B-BBB');
insert into so9bt values (7, 'B-CCC');
create table secret_identities (
cover_name varchar2(20),
real_name varchar2(20)
);
insert into secret_identities values ('Batman', 'Bruce Wayne');
insert into secret_identities values ('Superman', 'Clark Kent');
/* This is a semi-secure version immune to certain kind of SQL injections. Note
that it can be still used to find information about any table that ends with
't'. */
create or replace function cursor_of (p_table_id in varchar2)
return sys_refcursor as
v_cur sys_refcursor;
v_stmt constant varchar2(32767) := 'select * from ' || dbms_assert.qualified_sql_name(p_table_id || 't');
begin
open v_cur for v_stmt;
return v_cur;
end;
/
show errors
/* This is an unsecure version vulnerable to SQL injection. */
create or replace function vulnerable_cursor_of (p_table_id in varchar2)
return sys_refcursor as
v_cur sys_refcursor;
v_stmt constant varchar2(32767) := 'select * from ' || p_table_id || 't';
begin
open v_cur for v_stmt;
return v_cur;
end;
/
show errors
create or replace procedure print_values_of (p_cur in sys_refcursor) as
type rec_t is record (
id number,
data varchar2(32767)
);
v_rec rec_t;
begin
fetch p_cur into v_rec;
while p_cur%found loop
dbms_output.put_line('id = ' || v_rec.id || ' data = ' || v_rec.data);
fetch p_cur into v_rec;
end loop;
end;
/
show errors
declare
v_cur sys_refcursor;
begin
v_cur := cursor_of('so9a');
print_values_of(v_cur);
close v_cur;
v_cur := cursor_of('so9b');
print_values_of(v_cur);
close v_cur;
/* SQL injection vulnerability */
v_cur := vulnerable_cursor_of('secret_identities --');
dbms_output.put_line('Now we have a cursor that reveals all secret identities. Just see DBMS_SQL.DESCRIBE_COLUMNS ...');
close v_cur;
/* SQL injection made (mostly) harmless - will throw ORA-44004: invalid qualified SQL name */
v_cur := cursor_of('secret_identities --');
close v_cur;
end;
/
所有的表结构都是相同的吗?如果他们不是你不能使用[本地动态SQL](http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/dynamic.htm#i13130)(这就是你正在使用的以上),但必须依赖[DBMS_SQL](http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_sql.htm)PL/SQL包。 – user272735