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屋！