oracle 如何在oracle表中存储sql语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2274381/
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
How can I store sql statements in an oracle table?
提问by jonasespelita
We need to store a select statement in a table
我们需要在一个表中存储一个 select 语句
select * from table where col = 'col'
But the single quotes messes the insert statement up.
但是单引号将插入语句弄乱了。
Is it possible to do this somehow?
有可能以某种方式做到这一点吗？
回答by Tony Andrews
From Oracle 10G on there is an alternative to doubling up the single quotes:
从 Oracle 10G 开始，有一种替代方法可以将单引号加倍：
insert into mytable (mycol) values (q'"select * from table where col = 'col'"');
I used a double-quote character ("), but you can specify a different one e.g.:
我使用了双引号字符 (")，但您可以指定不同的字符，例如：
insert into mytable (mycol) values (q'@select * from table where col = 'col'@');
The syntax of the literal is:
文字的语法是：
q'<special character><your string><special character>'
It isn't obviously more readable in a small example like this, but it pays off with large quantities of text e.g.
在这样的小例子中，它显然不是更易读，但它在大量文本中得到了回报，例如
insert into mytable (mycol) values (
q'"select empno, ename, 'Hello' message
from emp
where job = 'Manager'
and name like 'K%'"'
);
回答by casperOne
How are you performing the insert? If you are using any sort of provider on the front end, then it should format the string for you so that quotes aren't an issue.
你是如何执行插入的？如果您在前端使用任何类型的提供程序，那么它应该为您格式化字符串，以便引号不成问题。
Basically, create a parameterized query and assign the value of the SQL statement to the parameter class instance, and let the db layer take care of it for you.
基本上，创建一个参数化查询并将SQL语句的值赋给参数类实例，让db层为你处理。
回答by Vincent Malgrat
you can either use two quotes ''to represent a single quote 'or (with 10g+) you can also use a new notation:
您可以使用两个引号''来表示单引号，'或者（使用 10g+）您也可以使用新的符号：
SQL> select ' ''foo'' ' txt from dual;
TXT
-------
'foo'
SQL> select q'$ 'bar' $' txt from dual;
TXT
-------
'bar'
回答by Evan Carroll
Don't store SQL statements in a database!!
不要将 SQL 语句存储在数据库中！！
Store SQL Viewsin a database. Put them in a schemaif you have to make them cleaner. There is nothing good that will happen ever if you store SQL Statements in a database, short of logging this is categorically a bad idea.
将SQL 视图存储在数据库中。如果您必须使它们更干净，请将它们放入模式中。如果您将 SQL 语句存储在数据库中，则不会发生任何好事，如果没有记录，这绝对是一个坏主意。
Also if you're using 10g, and you must do this: do it right! Per the FAQ
此外，如果您使用的是 10g，您必须这样做：做对了！根据常见问题
Use the 10g Quoting mechanism:
Syntax
q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
Make sure that the QUOTE_CHAR doesnt exist in the text.
SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;
回答by Pablo Santa Cruz
If you are using a programming language such as JAVA or C#, you can use prepared (parametrized) statements to put your values in and retrieve them.
如果您使用的是 JAVA 或 C# 等编程语言，则可以使用准备好的（参数化的）语句将值放入并检索它们。
If you are in SQLPlus you can escape the apostrophe like this:
如果您在 SQLPlus 中，您可以像这样转义撇号：
insert into my_sql_table (sql_command)
values ('select * from table where col = ''col''');
回答by álvaro González
Single quotes are escaped by duplicating them:
单引号通过复制它们来转义：
INSERT INTO foo (sql) VALUES ('select * from table where col = ''col''')
However, most database libraries provide bind parameters so you don't need to care about these details:
但是，大多数数据库库都提供绑定参数，因此您无需关心这些细节：
INSERT INTO foo (sql) VALUES (:sql)
... and then you assign a value to :sql.
...然后你给 :sql 赋值。
