pl/sql-在存储过程中使用动态查询 [英] pl/sql - Using a dynamic query inside a stored procedure

问题描述

我正在使用存储过程使用游标将数据插入到临时表中. 此过程将动态查询存储在变量中，以挂载insert/update命令.

I am using a stored procedure to insert data into a temp table using a cursor. This procedure stores a dynamic query inside a variable to mount the insert/update command.

这里是代码(不是完整的查询，为了方便阅读，我删掉了一些部分):

Here is the code(not the full query, I've cut some parts to make it easier to read):

 FOR VC2 IN (SELECT C.OBJETIVO,
                C.AUDITORIA ,
                C.NOME, 
                C.PRODUTO
           FROM CALCULO C)
  LOOP

  SELECT  ' V_UPD NUMBER := 0;

              SELECT (SELECT ID_TIPO_TERR  
              FROM ZREPORTYTD_TMP 
             WHERE AUDITORIA = ''' || VC2.AUDITORIA || '''
                AND TERRITORIO = ''' || VC2.NOME  || '''
                AND PRODUTO = ''' || VC2.PRODUTO || ''') 
               INTO V_UPD FROM DUAL;

                  UPDATE ZReportYTD_TMP
                     SET  TARGET = ' || VC2.OBJETIVO  || '
                   WHERE AUDITORIA = ''' || VC2.AUDITORIA || '''
                     AND TERRITORIO = ''' || VC2.NOME  || '''
                     AND PRODUTO = ''' || VC2.PRODUTO || ''';'

               INTO V_SQL  FROM DUAL;

               EXECUTE IMMEDIATE (V_SQL);

  END LOOP

在动态查询中，在此部分"SET TARGET = ' || VC2.OBJETIVO || '"中，值VC2.OBJETIVO是Number类型，并且已替换为"62481,76".换句话说，这个逗号使命令错误，并且不起作用.

Inside the dynamic query, in this part "SET TARGET = ' || VC2.OBJETIVO || '" the value VC2.OBJETIVO is a Number type, and it's replaced like "62481,76". In other words, this comma is making the command wrong and doesn't work.

是否有一种简单的方法可以将，"替换为."?

Is there an easy way to replace the "," for "."?

非常感谢！ (:

推荐答案

不要通过附加字符串来构建查询.您首先要面对许多错误和漏洞，首先是SQL注入.使用动态查询并不能证明不使用绑定变量是合理的.如果您确实需要使用动态查询(在您的示例中不清楚为什么静态更新不起作用?！)，请执行以下操作:

Don't build your query by appending strings. You leave yourself open to lots of bugs and vulnerabilities, first of all SQL injection. The need to use dynamic queries doesn't justify not using bind variables. If you really need to use dynamic queries (it is not clear from your example why static update wouldn't work?!), do this instead:

FOR vc2 IN (...) LOOP
   v_sql := 
       'BEGIN
            V_UPD NUMBER := 0;

            SELECT (SELECT ID_TIPO_TERR  
              FROM ZREPORTYTD_TMP 
             WHERE AUDITORIA = :p1
               AND TERRITORIO = :p2
               AND PRODUTO = :p3) 
              INTO V_UPD FROM DUAL;

            UPDATE ZReportYTD_TMP
               SET TARGET = :p4
             WHERE AUDITORIA = :p5
               AND TERRITORIO = :p6
               AND PRODUTO = :p7;
        END';
   EXECUTE IMMEDIATE v_sql USING VC2.AUDITORIA, VC2.NOME, VC2.PRODUTO, 
                                 VC2.OBJETIVO, VC2.AUDITORIA, VC2.NOME, 
                                 VC2.PRODUTO;
END LOOP;

Oracle将正确绑定适当的类型.

Oracle will correctly bind with the appropriate type.

这篇关于pl/sql-在存储过程中使用动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！