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

如果一定要是动态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类型

因为这两次转换的格式和环境都一样的，所以可以自动转来转去，当然可读性不是很好