文章取自http://www.itpub.net/forum.php?mod=viewthread&tid=2065276&extra=page%3D1%26filter%3Dtypeid%26typeid%3D1808

以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：
http://www.plsqlchallenge.com/

作者：Steven Feuerstein 

运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品 

哪些选项会显示 "O'Reilly Media" ?

(这个世界上我最喜爱的出版商 注：steven的书都是在这里发行的)

(A) 
DECLARE
   PROCEDURE plch_show_name (NAME_IN IN VARCHAR2)
   IS
      c_template   CONSTANT VARCHAR2 (1000)
         := 'begin dbms_output.put_line ('':myname''); end;' ;
   BEGIN
      EXECUTE IMMEDIATE REPLACE (c_template, ':myname', NAME_IN);
   END;
BEGIN
   plch_show_name ('O''Reilly Media');
END;
/

(B) 
DECLARE
   PROCEDURE plch_show_name (NAME_IN IN VARCHAR2)
   IS
      c_template   CONSTANT VARCHAR2 (1000)
         := 'begin dbms_output.put_line ('':myname''); end;' ;
   BEGIN
      EXECUTE IMMEDIATE
         REPLACE (REPLACE (c_template, '''', NULL), ':myname', NAME_IN);
   END;
BEGIN
   plch_show_name ('O''Reilly Media');
END;
/

(C) 
DECLARE
   PROCEDURE plch_show_name (NAME_IN IN VARCHAR2)
   IS
      c_template   CONSTANT VARCHAR2 (1000)
         := 'begin dbms_output.put_line (:myname); end;' ;
   BEGIN
      EXECUTE IMMEDIATE
         REPLACE (REPLACE (c_template, '''', NULL), ':myname', NAME_IN);
   END;
BEGIN
   plch_show_name ('O''Reilly Media');
END;
/

(D) 
DECLARE
   PROCEDURE plch_show_name (NAME_IN IN VARCHAR2)
   IS
      c_template   CONSTANT VARCHAR2 (1000)
         := 'begin dbms_output.put_line ('':myname''); end;' ;
   BEGIN
      EXECUTE IMMEDIATE
         REPLACE (c_template, ':myname', REPLACE (NAME_IN, '''', ''''''));
   END;
BEGIN
   plch_show_name ('O''Reilly Media');
END;
/

(E) 
DECLARE
   PROCEDURE plch_show_name (NAME_IN IN VARCHAR2)
   IS
      c_template   CONSTANT VARCHAR2 (1000)
         := q'[begin dbms_output.put_line (':myname'); end;]' ;
   BEGIN
      EXECUTE IMMEDIATE REPLACE (c_template, ':myname', NAME_IN);
   END;
BEGIN
   plch_show_name ('O''Reilly Media');
END;
/

(F)
DECLARE
   PROCEDURE plch_show_name (NAME_IN IN VARCHAR2)
   IS
      c_template   CONSTANT VARCHAR2 (1000)
         := q'[begin dbms_output.put_line (q'<:myname>'); end;]' ;
   BEGIN
      EXECUTE IMMEDIATE REPLACE (c_template, ':myname', NAME_IN);
   END;
BEGIN
   plch_show_name ('O''Reilly Media');
END;
/

答案：DF

A，

最后输出

begin dbms_output.put_line ('O'Reilly Media'); end;

由于O后面的单引号，执行会报错会报错
ORA-06550: line 1, column 32: PLS-00103: PLS-00103: Encountered the symbol "REILLY" 
问题在于，动态构造的PL/SQL块中有一个单引号。结果是EXECUTE IMMEDIATE试图执行这个无效代码：

B: 

这个选项会报错：

ORA-06550: line 1, column 30: PLS-00103: Encountered the symbol "Reilly Media); end;" 
如同选项A, 在动态构造的PL/SQL块中有一个单引号。结果是EXECUTE IMMEDIATE试图执行这个无效代码：
begin dbms_output.put_line (O'Reilly Media); end;
你可能会说：等等，难道REPLACE不是把单引号去掉了吗？不是的。它去除了模版字符串中的单引号，而不是传递给EXECUTE IMMEDIATE的字符串!

C: 

这个选项会显示：OReilly Media

D: 

老式的但是精准正确的选项！我将所有出现的单引号取代为两个单引号，字符串被正确地求值了。

E: 

用了Q引用，试图用"[" 和 "]" 作为字符串的开始和终止符，使得"O'Reilly Media"中的单引号不会作为字符串的终止。然而这个方法行不通，因为Q的威力被应用于模版常量的缺省值，而不是最终传递给EXECUTE IMMEDIATE的字符串。

F: 

这回用了不同的括号来将Q引用字符进行嵌套，这是可行的。

CONSTANT（常量） VARCHAR2：指定初始值并且之后不能修改。
EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE代替了以前Oracle中DBMS_SQL package包。         它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块。动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码。尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE，因为它获的收益在包之上。

　　-- 使用技巧

1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交

如果通过EXECUTE IMMEDIATE处理DML命令，那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分。如果通过EXECUTE IMMEDIATE处理DDL命令，它提交所有以前改变的数据

2. 不支持返回多行的查询，这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.

3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号。

4. 在Oracle手册中，未详细覆盖这些功能。

5. 对于Forms开发者，当在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能。

官方文档以及其他参考文档：

http://docs.oracle.com/database/121/LNPLS/executeimmediate_statement.htm#LNPLS01317

http://jingyan.baidu.com/article/11c17a2c133f0ef446e39d39.html

 

oracle之q字符

从10g开始引入该特性。q'后面的字符可以是：

•     !
•     [ ]
•     { }
•     ( )
•     < >

前提是这些字符不会出现在后续的SQL中。

oracle本身默认的是单引号，但是在大家写存储过程或者写SQL语句时，有时候需要拼SQL或者是SQL的值里需要传入含单引号的值，此时就需要使用两个单引号"''"来进行转义，其实oracle本身提供了这种转换默认单引号为其他标识的方法那就是——"q"

q [Oracle's quote operator]

q'c text-to-be-quoted c'

c is a single character (called the quote delimiter).

With the ?quote operator? apostrophes don't have to

be doubled:

 

SQL> select q'#Oracle''s quote operator#' from dual;

Q'#ORACLE''SQUOTEOPERATO

------------------------

Oracle''s quote operator