oracle pl/sql - 在存储过程中使用动态查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5007725/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pl/sql - Using a dynamic query inside a stored procedure
提问by gabsferreira
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
Inside the dynamic query, in this part "SET TARGET = ' || VC2.OBJETIVO || '"the value VC2.OBJETIVOis a Numbertype, and it's replaced like "62481,76". In other words, this comma is making the command wrong and doesn't work.
在动态查询中，这部分"SET TARGET = ' || VC2.OBJETIVO || '"的值VC2.OBJETIVO是一个Number类型，它被替换为“62481,76”。换句话说，这个逗号使命令出错并且不起作用。
Is there an easy way to replace the "," for "."?
有没有一种简单的方法可以将“,”替换为“.”？
Thank you very much! (:
非常感谢！(:
回答by Vincent Malgrat
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:
不要通过附加字符串来构建查询。您让自己面临许多错误和漏洞，首先是 SQL 注入。需要使用动态查询并不能证明不使用绑定变量。如果您确实需要使用动态查询（从您的示例中不清楚为什么静态更新不起作用？！），请执行以下操作：
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 will correctly bind with the appropriate type.
Oracle 将正确绑定到适当的类型。
回答by Nick Pierpoint
I don't see any need to use dynamic SQL at all.
我认为根本不需要使用动态 SQL。
Why not something like:
为什么不是这样的：
FOR VC2 IN (SELECT C.OBJETIVO,
C.AUDITORIA ,
C.NOME,
C.PRODUTO
FROM CALCULO C) LOOP
v_upd := 0;
SELECT
ID_TIPO_TERR
into
v_UPD
FROM
ZREPORTYTD_TMP
WHERE
AUDITORIA = VC2.AUDITORIA
AND TERRITORIO = VC2.NOME
AND PRODUTO = VC2.PRODUTO;
-- is v_upd used anywhere?
UPDATE
ZReportYTD_TMP
SET
TARGET = VC2.OBJETIVO
WHERE
AUDITORIA = VC2.AUDITORIA
AND TERRITORIO = VC2.NOME
AND PRODUTO = VC2.PRODUTO;
END LOOP;
回答by user2156161
I am using Oracle 11g , last couple of day I was facing problem of execute dynamic query in oracle procedure. I did search lots off. finally i have got solution .
我正在使用 Oracle 11g ，最近几天我遇到了在 oracle 过程中执行动态查询的问题。我确实搜索了很多。最后我得到了解决方案。
-- In blow procedure we pass multiple argument at run time
-- We need reference cursor for dynamic query execution
create or replace PROCEDURE FETCH_REPORT1_NEW(IPID IN number ,CAID IN number,
ZOID IN number,CLID IN number,SDATE VARCHAR2 , EDATE
VARCHAR2,OUT_VALUE OUT VARCHAR2)
IS
l_sql varchar(200); TYPE cursor_ref IS REF CURSOR; c1
cursor_ref;
UZID transaction_data.zone_id%TYPE; OUTAGE_MINS
transaction_data.durationmin%TYPE;
BEGIN
l_sql := 'select Avg (durationmin) , zone_id ,
from transaction_data where alarm_id in (1,21,26,20) and zone_id not in(5)';
IF IPID>0 THEN
l_sql := l_sql||' and IP_ID = '||IPID;
END IF;
l_sql := l_sql||' group by (zone_id)';
open c1 for l_sql;
loop
fetch c1 into OUTAGE_MINS,UZID;
dbms_output.put_line(OUTAGE_MINS||UZID);
exit when c1%notfound;
end loop;
close c1;
END;
