资讯专栏INFORMATION COLUMN

探索Oracle自动类型转换(下)

IT那活儿 / 3173人阅读
探索Oracle自动类型转换(下)
点击上方“IT那活儿”公众号,关注后了解更多内容,不管IT什么活儿,干就完了!!!

自动类型转换常见错误

下面选择一个在PL/SQL程序中的常犯错误说明自动类型转换容易导致的问题,特别是DATE类型自动转换导致问题,希望给予读者一些警示,不要使用自动类型转换,上篇文章已经说了这么多缺点,这里就不重复叙述了。
请看例子,为了简单说明只建了表未插入数据,有兴趣的可以自己插入数据测试一下:
DINGJUN123>DROP TABLE t;

表已删除。

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月 -21
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月 -21而不是12-6月 -21,很多人经常拼凑字符串的时候少了引号,当然这里拼凑加上引号是可以的,那么就有多次自动类型转换,将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> 2021-06-12

PL/SQL 过程已成功完成。

DINGJUN123>
PRINT o_count

O_COUNT
----------
0
首先SYSDATE转为字符串,将SQL拼凑好,结果就是上面打印出的语句,然后执行SQL,右边的2021-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


本文作者:丁 俊(上海新炬王翦团队)

本文来源:“IT那活儿”公众号

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/129290.html

相关文章

  • 一次慢查询暴露的隐蔽的问题

    摘要:最近解决了一个生产慢查询的问题,排查问题之后发现一些比较隐匿且容易忽略的问题。所以实际在数据库查询如下可能这里发生一次隐式转换。这次查询走的是索引。 showImg(https://segmentfault.com/img/bVbmJNK?w=6000&h=4000); Photo by Iga Palacz on Unsplash 最近解决了一个生产 SQL 慢查询的问题,排查问题之...

    missonce 评论0 收藏0
  • 深度学习即将攻陷的一个领域:NLP ACL2016优秀论文解

    摘要:而自然语言处理被视为深度学习即将攻陷的下一个技术领域,在今年全球较高级的学术会议上,我们也看到大量的在深度学习引入方面的探索研究。 深度学习的出现让很多人工智能相关技术取得了大幅度的进展,比如语音识别已经逼近临界点,即将达到Game Changer水平;机器视觉也已经在安防、机器人、自动驾驶等多个领域得到应用。 而自然语言处理(NLP)被视为深度学习即将攻陷的下一个技术领域,在今年全球较高级...

    alphahans 评论0 收藏0

发表评论

0条评论

IT那活儿

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<