形如‘1000,1001‘的参数在存储过程in 条件中使用 - 避免SQL拼接

 

 

邮件保存:

 

形如‘1000,1001‘的参数在存储过程in 条件中使用 - 避免SQL拼接

 

Hi All

 

昨天晚上YanJing ,GuoMin 的要求,避免SQL拼接,所以有了这个邮件:

          

          

           输入参数:  p_verion_List varchar(2) := '1000,1001 ';

          

          

           我们希望执行的SQL:

           SELECT mdl_ver_typ_desc FROM cost_mdl_ver_type where mdl_ver_typ_Id in (1000,1001);

           

          

 

            因为存储过程中传进来的参数是字符串,不能直接用,下面的SQL是无效的

            --p_verion_List 是字符串

            SELECT mdl_ver_typ_desc FROM cost_mdl_ver_type where mdl_ver_typ_Id in (p_verion_List);

           

            现在ICAM代码中使用的方法是拼接字符串,然后执行动态SQL,伪代码如下

                                                   

            LONG_SQL varchar2 ;

            LONG_SQL = 'SELECT mdl_ver_typ_desc FROM cost_mdl_ver_type where mdl_ver_typ_Id in (' ||p_verion_List || ')';

            EXECUTE  immediate  LONG_SQL ;

 

           

            这个方法没有任何问题,而且在ICAM中大量采用,但是

            1。对于ICAM几百行的SQL来说拼接非常麻烦,不容易阅读,而且编码过程中很容易出错。

            2。难调试,拼接的SQL不能直接执行。

            3。动态SQL没有预编译,可能相对速度慢

           

           

            这是一个避免拼接的方法,直接返回结果集合

          

           SELECT mdl_ver_typ_desc

             FROM cost_mdl_ver_type

            where mdl_ver_typ_Id in

                  (

                   ---------------------------------

                   select mdl_ver_typ_Id

                     from cost_mdl_ver_type

                    where instr(',1000,1001,', ',' || mdl_ver_typ_id || ',') > 0

                   ----------------------------------

                   );

          

           另外一个方法利用的嵌套表:(cosmos_test_boyu.split_boyu的定义在dev 数据库上)

      

       select mdl_ver_typ_desc

         from cost_mdl_ver_type

        where mdl_ver_typ_id in (

                                 ----------------------

                                 SELECT *

                                   FROM TABLE(CAST(cosmos_test_boyu.split_boyu('1000,1001,',

                                                                                ',') AS

                                                    mytable_boyu))

                                 -----------------------------

                                 );

              

           随便乱写下,任何东西都有利有弊,好的话也许以后项目也可以用。感谢guomin ,yan jingyan jie的帮助和测试 ,大家也看看有没有问题或者有更好的方法。

 

 

谢谢

 

Good Yubo!   This is a frequented asked question in the PL/SQL development …

We also should use bind variable to improve the performance of the SQL …

 

I also have an idea like  below ,

 

Idea from

         select level l from dual connect by level <=5;

 

 

And this is the SQL :

 

SELECT mdl_ver_typ_desc FROM cost_mdl_ver_type where mdl_ver_typ_Id in (

 (

 select

   trim( substr (txt,

         instr (txt, ',', 1, level  ) + 1,

         instr (txt, ',', 1, level+1)

            - instr (txt, ',', 1, level) -1 ) )

     as token

    from (select ',1000, 1001,' txt

            from dual)

  connect by level <=

     length('1000, 1001')-length(replace('1000, 1001',',',''))+1

  )

  )

 

 

In Package or Procedure, the test would be like :

 

 

create or replace procedure benny_procedure_test1(p_txt in varchar2)

as

 

txt_p_in varchar2(30);

output_txt varchar(30);

 

cursor c1 is

SELECT mdl_ver_typ_desc  FROM cost_mdl_ver_type where mdl_ver_typ_Id in (

 

 select

   trim( substr (txt,

         instr (txt, ',', 1, level  ) + 1,

         instr (txt, ',', 1, level+1)

            - instr (txt, ',', 1, level) -1 ) )

     as token

    from (select ','||txt_p_in||',' txt

            from dual)

  connect by level <=

     length(txt_p_in)-length(replace(txt_p_in,',',''))+1

 

);

begin

txt_p_in :=p_txt;

 

FOR CC in c1

loop

    dbms_output.put_line(CC.MDL_VER_TYP_DESC);

end loop;

 

end;

 

 

 

 

Best Regards,

Benny Wang

 

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页