在 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,'''','''''')||'''; ' ;
相关文章