Oracle - 合并/插入语句中的动态 SQL 列名称

Question

我有以下过程，没有错误消息：

create or replace procedure insert_or_upd_movement_baselines_planned_weight_proc(
p_id IN VARCHAR2,
p_date IN DATE,
p_planned_col_name IN VARCHAR2,
p_planned_value IN NUMBER
) as
begin
declare
plsql_block NVARCHAR2(8000);
begin
plsql_block := 'merge into MOVEMENT_BASELINES mb using dual on (mb.MOVEMENT_ID = ' || p_id || ' and mb.MOVEMENT_DATE = ' || p_date || ')
when not matched then insert (mb.MOVEMENT_ID, mb.MOVEMENT_DATE, mb.' || p_planned_col_name || ')
values ( ' || p_id || ', ' || p_date || ', ' || p_planned_value || ')
when matched then update set '
|| p_planned_col_name || ' = ' || p_planned_value || ';';
execute immediate plsql_block;
end;
end insert_or_upd_movement_baselines_planned_weight_proc;

当我尝试使用输入参数的值执行它时，出现编译器错误：

Connecting to the database localDB.
ORA-00933: SQL command not properly ended
ORA-06512: at "RTT.INSERT_OR_UPD_MOVEMENT_BASELINES_PLANNED_WEIGHT_PROC", line 17
ORA-06512: at line 12
Process exited.

我是 Oracle 的新手，想打印动态 sql 以检查出了什么问题，但打印语句似乎不起作用。 我猜问题出在插入语句中的动态列名称上 - 知道出了什么问题吗？ 谢谢

Answer 1

使用动态 SQL 时应始终保持谨慎。首先，最好检查静态 SQL 语句是否工作正常，然后尝试通过修改动态部分来转换它。此外，execute immediate之前的dbms_output可帮助您了解准备好的 sql 在语法上是否正确。其次，连接值容易出现SQL 注入，应避免使用。首选选项是将绑定变量与EXECUTE IMMEDIATEUSING选项一起使用。

由于p_planned_value被定义为数字，这意味着您计划更新/插入的所有列的数据类型都将是整数。我在演示中的示例相应地使用了它。如果不是这种情况，您将不得不重新考虑如何定义过程的参数，以便它适用于其他情况，例如DATE数据类型。

CREATE OR REPLACE PROCEDURE insert_or_upd_movement_baselines_planned_weight_proc (
p_id                 IN VARCHAR2,
p_date               IN DATE,
p_planned_col_name   IN VARCHAR2,
p_planned_value      IN NUMBER
)
AS
plsql_block   VARCHAR2(4000);
BEGIN
plsql_block := 'merge into MOVEMENT_BASELINES mb using 
( select :id as movement_id,:dt as movement_date from dual
) s ON ( mb.movement_id = s.movement_id  
and mb.movement_date = s.movement_date )
when matched then update set '
|| p_planned_col_name || ' = ' || p_planned_value || 
' when not matched then insert (MOVEMENT_ID, MOVEMENT_DATE,'
|| p_planned_col_name || ')
values (:id,:dt,:value)';
EXECUTE IMMEDIATE plsql_block
USING p_id,p_date,p_id,p_date,p_planned_value;
END insert_or_upd_movement_baselines_planned_weight_proc;
/

演示

Answer 2

这部分绝对是腥的：

|| p_date ||

因为它实际上做同样的事情

|| to_char(p_date) ||

因此，日期的不带引号的值将成为语句的一部分，这不会生成有效的 SQL 语句。试试这个：

values ( ' || p_id || ', to_date(''' || to_char(p_date) || '''), ' || p_planned_value || ')

Answer 3

这是Kaushik答案的附录，他们声明(非常正确，如果不是用这么多话的话)你的陈述完全容易受到SQL注入的影响。

我会把你的程序写成如下：

CREATE OR REPLACE PROCEDURE insert_or_upd_movement_baselines_planned_weight_proc(p_id               IN VARCHAR2,
           p_date             IN DATE,
           p_planned_col_name IN VARCHAR2,
           p_planned_value    IN NUMBER) AS
v_sql              CLOB;
v_planned_col_name VARCHAR2(32);
BEGIN
v_planned_col_name := dbms_assert.simple_sql_name(p_planned_col_name);
v_sql := 'MERGE INTO movement_baselines tgt'||CHR(10)||
'USING (SELECT :p_id movement_id,'||CHR(10)||
'              :p_date movement_date,'||CHR(10)||
'              :p_planned_value planned_value'||CHR(10)||
'       FROM   dual) src'||CHR(10)||
'ON (tgt.movement_id = src.movement_id AND tgt.movement_date = src.movement_date)'||CHR(10)||
'WHEN NOT MATCHED THEN'||CHR(10)||
'  INSERT (tgt.movement_id, tgt.movement_date, tgt.'||v_planned_col_name||')'||CHR(10)||
'  VALUES (src.movement_id, src.movement_date, src.movement_date)'||CHR(10)||
'WHEN MATCHED THEN'||CHR(10)||
'  UPDATE'||CHR(10)||
'  SET    tgt.'||v_planned_col_name||' = src.planned_value';

dbms_output.put_line('merge statement: ' || chr(10) || v_sql);
EXECUTE IMMEDIATE v_sql
USING p_id, p_date, p_planned_value;
END;
/

请注意，使用dbms_assert来清理您的输入 - 在本例中，我们正在检查您传递给 p_planned_col_name 的值是否满足其成为有效标识符的要求，这意味着它绝对不能用于 SQL 注入。

此外，我将参数移动到子查询中，这意味着立即执行的using子句现在更短，我认为更清晰，更易于维护。