如何使用替换函数更改为动态 sql 转义

Question

所以我尝试将此代码更改为动态SQL

SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM table

起诉

12121-President''S Office

我更改为动态sql

EXECUTE IMMEDIATE
' SELECT  LISTAGG(accounting_dept || ''-''||REPLACE(full_desc,'''''',''''''''), ''; '') WITHIN GROUP (ORDER BY accounting_dept)  FROM table '
INTO v_task;

错误为 ORA-00907：缺少右括号

问题是这部分替换(full_desc"，">

"，""(，如何在没有缓存错误的情况下更改为动态 SQL？

Answer 1

如果你把你的语句放到一个变量中，你可以在它被执行之前输出它：

set serveroutput on
declare
v_stmt varchar2(2000);
v_task varchar2(2000);
begin
v_stmt := 'SELECT  LISTAGG(accounting_dept || ''-''||REPLACE(full_desc,'''''',''''''''), ''; '') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table';
dbms_output.put_line(v_stmt );
execute immediate v_stmt into v_task;
end;
/

它显示它尝试执行的语句，然后显示得到的错误：

SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,''',''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table
Error report -
ORA-00911: invalid character
ORA-06512: at line 7

如果您在 SQL Developer 中手动运行生成的语句，您将获得 ORA-00907：缺少右括号;不完全确定您如何或为什么也会动态地收到该错误 - 它应该首先抱怨分号，使用 ORA-00911。

您可以看到 generate 语句与您开始使用的原始静态语句不匹配。您需要更多转义引号：

declare
v_stmt varchar2(2000);
v_task varchar2(2000);
begin
v_stmt := 'SELECT  LISTAGG(accounting_dept || ''-''||REPLACE(full_desc,'''''''',''''''''''''), ''; '') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table';
dbms_output.put_line(v_stmt );
execute immediate v_stmt into v_task;
end;
/
SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table
PL/SQL procedure successfully completed.

但是按照托尼·安德鲁斯的建议去做，改用替代引用机制。

并不是说你在这里甚至需要动态 SQL;这样做是一样的：

declare
v_task varchar2(2000);
begin
SELECT LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ')
WITHIN GROUP (ORDER BY accounting_dept)
INTO v_task
FROM tableinto v_task;
end;
/

Answer 2

如果将整个select转换为字符串，请使用 Q 引号，如下所示：

q'[SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM table]'