探索Oracle自动类型转换_PART3

自动类型转换常见错误

    下面选择一个在PL/SQL程序中的常犯错误说明自动类型转换容易导致的问题，特别是DATE类型自动转换导致问题，希望给予读者一些警示。请看例子，为了简单说明只建了表未插入数据，有兴趣的可以自己插入数据测试一下，这里测试不需要数据就可以说明问题了：

表已删除。

DINGJUN123>CREATE TABLE t

  2  (id NUMBER,

  3  done_date DATE

  4  );

表已创建。

--建立过程的时候并没有报错，我加了打印语句，方便下面看错误原因

DINGJUN123>CREATE OR REPLACE PROCEDURE

  2  sp_t (i_tabname VARCHAR2,i_date DATE,o_count OUT NUMBER)

  3  /***********************************************************************

  4  ||desc:根据传入的表名和日期查询大于此日期的记录数

  5  ||此函数应用的表都有相同的统计日期列done_date

  6  ************************************************************************/

  7  AS

  8  v_sql VARCHAR2(1000);

  9  BEGIN

 10   v_sql := 'SELECT COUNT(*) FROM '

 11               || i_tabname || ' WHERE done_date> '

 12               || i_date;

 13   DBMS_OUTPUT.PUT_LINE(v_sql);

 14   EXECUTE IMMEDIATE v_sql INTO o_count;

 15  END;

 16  /

过程已创建。

DINGJUN123>VAR o_count NUMBER

DINGJUN123>SET SERVEROUTPUT ON

DINGJUN123>EXEC sp_t ('t',SYSDATE,:o_count);

SELECT COUNT(*) FROM t WHERE done_date> 12-6月 -10

BEGIN sp_t ('t', SYSDATE,:o_count); END;

*

第 1 行出现错误:

ORA-00933: SQL 命令未正确结束

ORA-06512: 在 "DINGJUN123.SP_T", line 14

ORA-06512: 在 line 1

    看到错误了吧，日期类型发生了类型转换，根据NLS_DATE_FORMAT和NLS_DATE_LANGUAGE参数设置自动变为字符串了，而且这个字符串还不是Oracle中的表示的字符串，看到了没有?是12-6月 -10而不是'12-6月 -10'，很多人经常拼凑字符串的时候少了引号，当然这里拼凑加上引号是可以的，那么就有多次自动类型转换，将v_sql改为：

v_sql := 'SELECT COUNT(*) FROM '

        ||i_tabname || ' WHERE done_date> '

        ||chr(39)

        ||i_date

        ||chr(39);

    上面的chr(39)就是单引号，因为很多人认为 ，所以用chr(39)代替。然后重新编译过程并测试：

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

会话已更改。

DINGJUN123>EXEC sp_t ('t', SYSDATE,:o_count);

SELECT COUNT(*) FROM t WHERE done_date> '2010-06-12'

PL/SQL 过程已成功完成。

DINGJUN123>PRINT o_count

   O_COUNT

----------

         0   

   

首先SYSDATE转为字符串，将SQL拼凑好，结果就是上面打印出的语句，然后执行SQL，右边的'2010-06-12'遇到了左值是DATE类型，又进过一次类型转换，将字符串转为DATE类型，转换的过程很复杂。这种拼凑SQL的方式也是不可取的。

   

自动类型转换特别是在日期类型的处理过程中，经常发生类似的错误，如果有很好的编码习惯，这种错误是完全可以避免的，类似这样的查询，不应该使用常量直接拼凑，一般应使用绑定变量的 ：

DINGJUN123>CREATE OR REPLACE PROCEDURE

  2  sp_t (i_tabname VARCHAR2,i_date DATE,o_count OUT NUMBER)

  3  /***********************************************************************

  4  ||desc:根据传入的表名和日期查询大于此日期的记录数

  5  ||此函数所有的表都有共同的统计日期列done_date

  6  ************************************************************************/

  7  AS

  8  v_sql VARCHAR2(1000);

  9  BEGIN

 10   v_sql := 'SELECT COUNT(*) FROM '

 11               || i_tabname || ' WHERE done_date> : i_date ';

 12   DBMS_OUTPUT.PUT_LINE(v_sql);

 13   EXECUTE IMMEDIATE v_sql INTO o_count

 14   USING i_date;

 15  END;

 16  /

过程已创建。

DINGJUN123>EXEC sp_t ('t',sysdate,:o_count);

SELECT COUNT(*) FROM t WHERE done_date> : i_date

PL/SQL 过程已成功完成。

DINGJUN123>PRINT o_count

O_COUNT

----------

         0

    这一章说了很多自动类型转换的规则和问题，的确，自动类型转换是容易被Oracle技术人员忽略的重要知识点，在实际开发中，经常会遇到各种各样的自动类型转换问题，通过上面相关内容的描述，我想，你应该对自动类型转换的规则有所了解了。当然，最好就是杜绝自动类型转换，这样才能避免类似问题的发生。