如何打印 '' 而不是 '

由于空字符串''相当于nullOracle 中的空字符串，因此在这种情况下，您可以通过用 null 替换制表符/换行符来避免该问题；所以而不是这样：

WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME

这样做：

WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), null), CHR(10), null) AS '' || COLUMN_NAME

不过，您不需要在这里使用动态 SQL，因为您可以静态查询数据字典 - 目标模式/表名在这里是变量，而不是标识符：

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
  v_columns CLOB;
  v_query   CLOB;
BEGIN
  -- Get all column names with transformations based on data type
  SELECT RTRIM(XMLAGG(XMLELEMENT(e, 
    CASE 
      WHEN COLUMN_NAME = 'F_M_ID'
      THEN 'TO_CHAR(t.F_M_ID) AS F_M_ID'
      WHEN DATA_TYPE LIKE '%CHAR%'
      THEN 'REPLACE(REPLACE(t.' || COLUMN_NAME || ', CHR(13), null), CHR(10), null) AS ' || COLUMN_NAME
      ELSE 't.' || COLUMN_NAME 
    END || ', ').EXTRACT('//text()') ORDER BY COLUMN_ID).GETCLOBVAL(), ', ') 
  INTO v_columns
  FROM ALL_TAB_COLUMNS 
  WHERE TABLE_NAME = v_table_name
  AND OWNER = v_owner;
  DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/

创建虚拟表如下：

create table LFS_FAMILY_TAB (f_m_id number, foo date, bar varchar2(30))

输出：

TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), null), CHR(10), null) AS BAR

小提琴

功能上与

TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), ''), CHR(10), '') AS BAR

如果您确实想要''（null或者可能必须处理其他文本文字），那么您可以使用 XMLTable，它不会逃避像extract（和 XMLQuery）这样的实体：

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
  v_columns CLOB;
  v_query   CLOB;
BEGIN
  -- Get all column names with transformations based on data type
  SELECT RTRIM(x.str, ', ')
  INTO v_columns
  FROM (
    SELECT XMLELEMENT(r, XMLAGG(XMLELEMENT(e, 
      CASE 
        WHEN COLUMN_NAME = 'F_M_ID'
        THEN 'TO_CHAR(t.F_M_ID) AS F_M_ID'
        WHEN DATA_TYPE LIKE '%CHAR%'
        THEN 'REPLACE(REPLACE(t.' || COLUMN_NAME || ', CHR(13), ''''), CHR(10), '''') AS ' || COLUMN_NAME
        ELSE 't.' || COLUMN_NAME 
      END || ', ') ORDER BY COLUMN_ID)) AS xmlx
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME = v_table_name
    AND OWNER = v_owner
  ) atc
  CROSS JOIN XMLTABLE(
    '/R'
    PASSING atc.xmlx
    COLUMNS str CLOB PATH '.'
  ) x;
  DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/

对于同一个虚拟表，它将生成：

TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), ''), CHR(10), '') AS BAR

小提琴

顺便说一句，您的原始代码生成了'，但这只是一个实体转义的引号'。如果您想要两个''，那么您的代码中就需要更多：

WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''''''), CHR(10), '''''''') AS '' || COLUMN_NAME

但由于这样做会产生''无法真正解决您的问题的效果 - 尽管您随后手动取消转义这些实体。

Oracle 将 4 个连续的单引号表示为一个单引号。因此，在构建某些动态 SQL 时，最易读的方式可能是声明一个单引号变量
sq VARCHAR2(1) := ''''; ，
如下所示：

DECLARE
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; 
  v_query   CLOB;
  sq   VARCHAR2(1) := '''';  -- 4 single quotes are represented as one single quote
BEGIN
  v_query := 'SELECT '|| Chr(10) || 
             '   CASE WHEN COLUMN_NAME = ' || sq || sq || 'F_M_ID' || sq || sq || Chr(10) ||
             '       THEN ' || sq || sq || 'TO_CHAR(t.F_M_ID)' || sq || sq || ' AS F_M_ID ' || Chr(10) ||
             '   ELSE t.' || sq || 'COLUMN_NAME ' || sq || Chr(10) || 
             '   END as SOME_ALIAS ' || Chr(10) || 
             'FROM ' || v_table_name || Chr(10) ||
             '      ... ';
  dbms_output.put_line(v_query);           
END;
/

--  R e s u l t : 
SELECT 
   CASE WHEN COLUMN_NAME = ''F_M_ID''
       THEN ''TO_CHAR(t.F_M_ID)'' AS F_M_ID 
   ELSE t.'COLUMN_NAME '
   END as SOME_ALIAS 
FROM LFS_FAMILY_TAB
      ... 

注意：
这样，您可以在字符串的任何位置插入 sq（或两个、三个）并保持代码的可读性和可维护性...

为什么 4 得到 1 呢？

--  Some text enclosed within quotes and containing a single quote like 
--  "father's house"
--  requires duplication of single quote to result as one single quote
Select '... father''s house ...' as SAMPLE From Dual;

-- if you remove all other text but the single quotes
-- you will end up with 2 outer enclosing single quotes and 2 inner ones resulting as just one:
Select '''' as SAMPLE From Dual;

小提琴

有几种 pl/sql 技术可以简化您的代码并减少遇到类似问题的机会。正如其他人所指出的，EXECUTE IMMEDIATE在这个特定情况下不需要，但如果您无论如何都选择这样做，这里有一些提示。

替代引用又名 Q-quote 语法

这使您可以使用单引号以外的其他字符串分隔符 - 从而无需转义引号（文档）

例子：

BEGIN
    -- the "old way"
    dbms_output.put_line('this is a quote: '', and here are 2 quotes: ''''');
    -- alternative quoting
    dbms_output.put_line(q'!this is a quote: ', and here are 2 quotes: ''!');
END;
/  
this is a quote: ', and here are 2 quotes: ''
this is a quote: ', and here are 2 quotes: ''


PL/SQL procedure successfully completed.

使用绑定变量而不是连接

在 pl/sql 和 sql 中连接变量值不是一个好习惯。它使代码容易受到 sql 注入攻击。此外，它很难阅读，维护起来也很困难（正如您所展示的）。尽可能尝试使用绑定变量。它的编码更好，可读性更强。

例子：

DECLARE
  l_ename emp.ename%TYPE := 'KING';
  l_statement VARCHAR2(4000);
  l_result  VARCHAR2(4000);
BEGIN
   -- without bind variables
   l_statement := q'!
SELECT ename ||' earns '|| sal
  FROM emp where ename = !' || '''KING''';
    EXECUTE IMMEDIATE l_statement INTO l_result;
  dbms_output.put_line(l_result);

   -- with bind variables
   l_statement := q'!
SELECT ename ||' earns '|| sal
  FROM emp where ename = :1!';
    EXECUTE IMMEDIATE l_statement INTO l_result USING l_ename;
  dbms_output.put_line(l_result);
END;
/
KING earns 5000
KING earns 5000