在 PLSQL 中转义单引号

2021-12-06 00:00:00 oracle plsql

我希望 PLSQL 生成如下字符串:

I want PLSQL to generate strings like:

COMMENT ON COLUMN TABLE.COLUMN IS 'comment from database';

我的解决方案是:

declare
  str_comment varchar2(4000);
begin
  for rec in (select table_name, column_name, description from description_table)
  loop
    str_comment:='COMMENT ON COLUMN '||rec.table_name||'.'||rec.column_name||'  IS '''||rec.description||'''; ' ;
    dbms_output.put_line(str_comment);
  end loop;
end;

当 rec.description 中不包含单个 qout 时，输出是可以的.否则需要转义信.我应该如何实施?

Output is OK when it doesn't contain single qoutes in rec.description. Otherwise there is need for escape letter. How should I implement it?

OK 输出行(它有转义字母以保留单个 qoute):

OK output line (It's has escape letter to preserve single qoute):

COMMENT ON COLUMN TABLE1.COLUMN1_LV  IS 'It''s secret';

NOT NOK 输出行，因为没有添加单引号的转义字母并且不会编译:

NOT NOK output line because no escape letter for single quote added and doesn't compile:

COMMENT ON COLUMN TABLE1.COLUMN1_LV  IS 'It's secret';

我的解决方案是不检查描述是否包含单引号.我只是在生成 COMMENT ON 字符串之前用两个单引号替换源(描述)列的单引号，然后我 ROLLBACK.

My solution is not to check if description contains single quotes. I just replace source (description) column's single quote by two single quotes before generating COMMENT ON strings and then I ROLLBACK.

有更好的解决方案吗?

推荐答案

我经常做这类事情(通常生成插入/更新语句).

I do this sort stuff a fair bit (usually generating insert/update statements).

你只需要使用replace函数把所有的'变成''.即更改为:

You just need to use the replace function to turn all the ' into ''. i.e. Change it to:

str_comment:='COMMENT ON COLUMN '||rec.table_name||'.'||rec.column_name
            ||' IS '''||REPLACE( rec.description,'''','''''')||'''; ' ;

相关文章