动态SQL对日期处理要特别注意

http://www.itpub.net/thread-1687181-1-1.html

看到这个帖子，把我以前写的再发上来

如果一定要是动态SQL,一定要将日期类型用||拼起来，见下列写法，红色的是错的，下面的才对，两次类型转换，但是没有什么必要
--要么用绑定变量绑定date类型，要么静态sql,不要两次转换，直接拼日期前后不加单引号还会报错。
SQL> DECLARE
  2   v_date date := sysdate;
  3   v_cnt number := 0;
  4   v_sql varchar2(100);
  5  BEGIN
  6    v_sql := 'SELECT COUNT(*) FROM USER_OBJECTS WHERE CREATED<='||v_date;  
   7    DBMS_OUTPUT.PUT_LINE(v_sql);
  8    v_sql := 'SELECT COUNT(*) FROM USER_OBJECTS WHERE CREATED<='||chr(39)||v_date||chr(39);
  9      DBMS_OUTPUT.PUT_LINE(v_sql);
10    EXECUTE IMMEDIATE v_sql INTO v_cnt;
11        DBMS_OUTPUT.PUT_LINE(v_cnt);
-- v_sql := 'SELECT COUNT(*) FROM USER_OBJECTS WHERE CREATED<=TO_DATE('''||v_date||''',''dd-mon-yy'')';
  --   DBMS_OUTPUT.PUT_LINE(v_sql);
12  END;
13  /

SELECT COUNT(*) FROM USER_OBJECTS WHERE CREATED<=04-1月 -11
SELECT COUNT(*) FROM USER_OBJECTS WHERE CREATED<='04-1月 -11'
1810

PL/SQL procedure successfully completed



DROP TABLE t;
CREATE TABLE t(p1 DATE)
PARTITION BY RANGE(p1)
(PARTITION p0 VALUES LESS THAN  ( TO_DATE ('20110816', 'YYYYMMDD'))  TABLESPACE USERS);

所以：
1.对非DDL，应该要使用绑定变量，如果绑定变量适合的话
2.DDL，用不了绑定变量，必须to_date拼凑

DECLARE
   p1   DATE := TO_DATE ('20110817', 'YYYYMMDD');
BEGIN
  EXECUTE IMMEDIATE 'alter session set nls_date_format=''yyyymmdd''';
   FOR i IN 1 .. 2
   LOOP
      EXECUTE IMMEDIATE
            'alter table t add partition p'
         || TO_CHAR (p1, 'YYYYMMDD')
         || '  VALUES LESS THAN ( TO_DATE('''
         || p1
         || ''',''yyyymmdd''))  TABLESPACE USERS';

      p1 := p1 + 1;
   END LOOP;
END;
/

--不用设nls_date_format，因为两次转换的nls_date_format一样，直接to_date,因为自动转换的字符串没有引号，需要补上引号
DECLARE
   p1   DATE := TO_DATE ('20110817', 'YYYYMMDD');
BEGIN
   FOR i IN 1 .. 2
   LOOP
      EXECUTE IMMEDIATE
            'alter table t add partition p'
         || TO_CHAR (p1, 'YYYYMMDD')
         || '  VALUES LESS THAN ( TO_DATE('''
         || p1
         || '''))  TABLESPACE USERS';

      p1 := p1 + 1;
   END LOOP;
END;
/


--甚至可以去掉to_date,自动转为目标类型，但是要补引号

DECLARE
   p1   DATE := TO_DATE ('20110817', 'YYYYMMDD');
BEGIN
   DBMS_OUTPUT.put_line(     'alter table t add partition p'
         || TO_CHAR (p1, 'YYYYMMDD')
         || '  VALUES LESS THAN ( '''
         || p1
         || ''')  TABLESPACE USERS');
END;
/


也可以对p使用显示转为字符串，和上面一样
  'alter table t add partition p'
         || TO_CHAR (p1, 'YYYYMMDD')
         || '  VALUES LESS THAN ( TO_DATE('''
         || TO_CHAR(p1,'YYYY-MM-DD')
         || ''',''YYYY-MM-DD''))  TABLESPACE USERS'


再看一个典型的拼凑日期的错误。
我常常看到别人不使用绑定变量，采用拼凑的方式传入日期类型，比如：

declare
  vname varchar2(10):='dd';
  v_sql varchar2(4000);
  vcnt number;
  v_date date:=sysdate;
begin
    v_sql:='select count(*) from test where birth='||v_date; --这里要出错
    dbms_output.put_line(v_sql);
    execute immediate v_sql into vcnt;
    dbms_output.put_line(vcnt);
end;

这里又出什么错呢？请看打印出的语句是什么？
select count(*) from test where birth=08-5月 -10
原来在运行期，如果采用||date,那么日期自动根据session日期设置参数转换为字符串，而字符串连接不自动加引号，所以就变成上面的了，丢失了引号，自动转换也失效，语句错误了，如果你非要那样做，只能

1.        将v_date转为字符串，带引号的，然后让其自动转换，如：  v_sql:='select count(*) from test where birth='''||v_date||'''';  当然也可以使用to_date,如
  v_sql:='select count(*) from test where birth=to_date('''||v_date||''')';
不需要nls_date_format设置，因为两次转换的格式一样.
也可以显示转换，代替oracle自动转换v_sql:='select count(*) from test where birth='''||to_char(v_date)||'''';

最完整的全部显示转换
v_sql:='select count(*) from test where birth=to_date('''||to_char(v_date,'yyyymmdd')||''',''yyyymmdd'')';

2.        DDL用不了绑定变量，只能使用1的方法，对非DDL，要用绑定变量，解决这个问题，一般起到软解析效果。
        以上是典型的拼凑导致的程序难以编写而且容易出错的例子，如果采用绑定变量的方式，以上问题全可迎刃而解。

也说明了一点，绑定变量不光使我们的SQL反复执行的效率更高，在存储过程的动态SQL里，绑定变量也会使我们减少错误的发生，更容易地进行编程（存储过程的静态SQL自动绑定，达到软解析或软软解析的效果，当然动态SQL也可能软或软软解析）

不加to_date,发生2次转换
第1次，v_date是date类型，但是用||运算，变为字符串
第2次，||两边加了引号，但是birth是date类型，又把字符串转为date类型
因为这两次转换的格式和环境都一样的，所以可以自动转来转去，当然可读性不是很好