pl/sql - 在存储过程中使用动态查询

2021-12-24 00:00:00 oracle plsql

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

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.

相关文章