PL/SQL用'IN'子句重写级联查询 [英] PL/SQL rewrite concatenated query with 'IN' clause
问题描述
目前,我在pl/sql代码中包含以下语句:
Currently I have in my pl/sql code following statements:
-- vList looks like '1,2,3,4'
vStatement := 'SELECT NAME FROM T_USER WHERE ID IN ( ' || vList || ' ) ';
Execute Immediate vStatement BULK COLLECT INTO tNames;
我认为串联查询是一种不好的做法,因此我想在不使用query的情况下进行此查询.用什么方法重写它?
I think that concatenating of query if bad practice, so I want to make this query without using stings. What is the way to rewrite this ?
P.S.也许这里的人可以指出为什么串联查询很糟糕,因为我没有足够的理由证明这种风格很糟糕.
P.S. maybe people here can point out why concatenation of queries is bad, because i don't have enough reasons to prove that this style is bad.
推荐答案
我的猜测是您之前采取了一些措施才能将vList ID放入分隔的字符串中(您不会说vList的填充方式).为什么不保留为一个查询?
my guess is that you took some steps previously to get vList id's into a delimited string (you don't say how vList was populated ). Why not keep as one query?
begin
...
select name
bulk collect into tNames
from t_user
where id in (select id from some_table where ...);
...
多次运行时进行上下文切换可能会很痛苦,但对我而言,最糟糕的是,您盲目地接受参数输入为数字列表,而实际上可能是任何东西.它可能(无害地)为"1,2,X",并且您会收到运行时错误无效编号".更糟糕的是,这可能是SQL注入攻击.一般来说,它是不好的做法(动态sql确实有它的位置),但绝对不是您的使用方式.
Context switching when run many times can be painful, but to me the worst part is that you are blindly accepting parameter input to be a list of numbers, when it could be anything really. It could (innocently) be '1,2,X', and you'll get a runtime error "invalid number". Or worse, it could be a SQL injection attack. Its bad practice in general (dynamic sql does have its place), but definitely NOT how you're using it.
尝试这样的事情:
create or replace type t_num_tab as table of number;
create or replace procedure test_proc(i_list in t_num_tab) as
type t_name_tab is table of varchar2(100);
l_names t_name_tab;
begin
-- get names
select name
bulk collect into l_names
from user_table
where id in (select * from table(i_list));
-- do something with l_names
dbms_output.put_line('Name count: ' || l_names.count);
end;
如果您需要比数字列表更复杂的对象,则可以创建对象类型.
You can create an object type if you need something more complicated than a list of numbers.
这篇关于PL/SQL用'IN'子句重写级联查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!