oracle 在 PLSQL 中转义单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6678478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Escaping single quote in PLSQL
提问by reforrer
I want PLSQL to generate strings like:
我希望 PLSQL 生成如下字符串：
COMMENT ON COLUMN TABLE.COLUMN IS 'comment from database';
My solution is:
我的解决办法是：
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;
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?
当rec.description. 否则需要转义信。我应该如何实施它？
OK output line (It's has escape letter to preserve single qoute):
OK 输出行（它有转义字母以保留单个 qoute）：
COMMENT ON COLUMN TABLE1.COLUMN1_LV IS 'It''s secret';
NOT NOK output line because no escape letter for single quote added and doesn't compile:
NOT NOK 输出行，因为没有添加单引号的转义字母并且无法编译：
COMMENT ON COLUMN TABLE1.COLUMN1_LV IS 'It's secret';
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 ONstrings and then I ROLLBACK.
我的解决方案是不检查 description 是否包含单引号。我只是在生成COMMENT ON字符串之前用两个单引号替换源（描述）列的单引号，然后 I ROLLBACK.
Any better solution?
有什么更好的解决办法吗？
回答by Sodved
I do this sort stuff a fair bit (usually generating insert/update statements).
我做了很多这样的事情（通常生成插入/更新语句）。
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,'''','''''')||'''; ' ;
回答by schurik
You can use the Quote operator like
您可以使用 Quote 运算符，例如
str_comment:='COMMENT ON COLUMN '||rec.table_name||'.'||rec.column_name||' IS q''[' ||rec.description|| ']'';' ;
回答by dee-see
Use the REPLACEfunction in your select.
使用REPLACE您选择的功能。
declare
str_comment varchar2(4000);
begin
for rec in (SELECT table_name, column_name, REPLACE(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;
回答by Sumesh
You need to use '' in the code But Before trying it to the actual code ,
您需要在代码中使用 '' 但在尝试实际代码之前，
try the line which has quotes in the dual
尝试在双引号中有引号的行
For example:
例如：
select '''sumesh''' from dual
