愿所有的美好和期待都能如约而至

在 PLSQL 中转义单引号

发布时间:  来源:互联网  作者:匿名  标签:error Escaping single quote in PLSQL exception IT资讯 oracle plsql 技术社区 教程下载 程序员   热度:37.5℃

本文介绍了在 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,'''','''''')||'''; ' ;

这篇关于在 PLSQL 中转义单引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,

勇敢去编程!

勇敢的热爱编程,未来的你一定会大放异彩,未来的生活一定会因编程更好!

TOP