使用to_number,to_char转换的连接列易造成错误的执行计划

最新推荐文章于 2024-08-23 08:00:00 发布
最新推荐文章于 2024-08-23 08:00:00 发布
阅读量1.1k 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/launch_225/article/details/25656691
版权 
场景：偶尔会遇到一些表的列由于历史原因，本来都是数据的却是varchar2类型，结果只能通过建立to_char函数来进行连接，其结果易造成执行计划的不稳定以及错误的产生，
写SQL以及表设计时需注意；

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t1 as select * from dba_objects  where 1=2;

Table created.

SQL> create table t2 as select * from dba_objects where 1=2;

Table created.

SQL> create table t3 as select * from dba_objects where 1=2;

Table created.

SQL> alter table t3 modify(object_id varchar2(40));

Table altered.

SQL> insert into t1 select * from dba_objects;

75253 rows created.

SQL> l
  1* insert into t1 select * from dba_objects
SQL> c/t1/t2
  1* insert into t2 select * from dba_objects
SQL> /

75253 rows created.

SQL> c/t2/t3
  1* insert into t3 select * from dba_objects
SQL> /

75253 rows created.

SQL> commit;

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> desc t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> desc t3
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          VARCHAR2(40)               --object_id类型设为varchar2
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> create index idx_t1_object_id on t1(object_id);

Index created.

SQL> create index idx_t2_object_id on t2(object_id);

Index created.

SQL> create index idx_t3_object_id on t3(to_number(object_id));  --t3表列object_id为函数索引

Index created.

SQL> CREATE TABLE T4 AS SELECT * FROM DBA_OBJECTS;

Table created.

SQL> CREATE INDEX IDX_T4_OBJECT_ID ON T4(OBJECT_ID);

Index created.

exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);
exec dbms_stats.gather_table_stats('AIKI','T2',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);
exec dbms_stats.gather_table_stats('AIKI','T3',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);
exec dbms_stats.gather_table_stats('AIKI','T4',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent=>100);

--没有使用转换函数列的正确执行计划
SQL_ID  279zrj0d22082, child number 0
-------------------------------------
select t1.object_id, t1.object_name, T4.status, t2.owner from t1,T4,t2
where t1.object_id=T4.object_id and t2.object_id=T4.object_id and (
t1.object_id in (108,1080,10800) or t2.object_id in(108,1080,10800))

Plan hash value: 1275242484

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   1 |  CONCATENATION                   |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   2 |   NESTED LOOPS                   |                  |      1 |        |      3 |00:00:00.01 |      27 |
|   3 |    NESTED LOOPS                  |                  |      1 |      3 |      3 |00:00:00.01 |      24 |
|   4 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      18 |
|   5 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       9 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T2               |      3 |      3 |      3 |00:00:00.01 |       9 |
|*  7 |        INDEX RANGE SCAN          | IDX_T2_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       6 |
|   8 |      TABLE ACCESS BY INDEX ROWID | T4               |      3 |      1 |      3 |00:00:00.01 |       9 |
|*  9 |       INDEX RANGE SCAN           | IDX_T4_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|* 10 |     INDEX RANGE SCAN             | IDX_T1_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|  11 |    TABLE ACCESS BY INDEX ROWID   | T1               |      3 |      1 |      3 |00:00:00.01 |       3 |
|  12 |   NESTED LOOPS                   |                  |      1 |        |      0 |00:00:00.01 |      21 |
|  13 |    NESTED LOOPS                  |                  |      1 |      3 |      0 |00:00:00.01 |      21 |
|  14 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      16 |
|  15 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       8 |
|  16 |       TABLE ACCESS BY INDEX ROWID| T1               |      3 |      3 |      3 |00:00:00.01 |       8 |
|* 17 |        INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       5 |
|  18 |      TABLE ACCESS BY INDEX ROWID | T4               |      3 |      1 |      3 |00:00:00.01 |       8 |
|* 19 |       INDEX RANGE SCAN           | IDX_T4_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |
|* 20 |     INDEX RANGE SCAN             | IDX_T2_OBJECT_ID |      3 |      1 |      0 |00:00:00.01 |       5 |
|  21 |    TABLE ACCESS BY INDEX ROWID   | T2               |      0 |      1 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access(("T2"."OBJECT_ID"=108 OR "T2"."OBJECT_ID"=1080 OR "T2"."OBJECT_ID"=10800))
   9 - access("T2"."OBJECT_ID"="T4"."OBJECT_ID")
  10 - access("T1"."OBJECT_ID"="T4"."OBJECT_ID")
  17 - access(("T1"."OBJECT_ID"=108 OR "T1"."OBJECT_ID"=1080 OR "T1"."OBJECT_ID"=10800))
  19 - access("T1"."OBJECT_ID"="T4"."OBJECT_ID")
  20 - access("T2"."OBJECT_ID"="T4"."OBJECT_ID")
       filter((LNNVL("T2"."OBJECT_ID"=108) AND LNNVL("T2"."OBJECT_ID"=1080) AND
              LNNVL("T2"."OBJECT_ID"=10800)))


47 rows selected.

--使用转换函数to_number造成的错误执行计划
SQL> 
SQL> 
SQL> select  
  2  t1.object_id,
  3  t1.object_name,
  4  t3.status,
  5  t2.owner
  6  from t1,t3,t2
  7  where t1.object_id=to_number(t3.object_id)
  8  and t2.object_id=to_number(t3.object_id)
  9  and ( t1.object_id in (108,1080,10800) or t2.object_id in(108,1080,10800)) ;

 OBJECT_ID OBJECT_NAME                                                                                                                      STATUS  OWNER
---------- -------------------------------------------------------------------------------------------------------------------------------- ------- ------------------------------
       108 I_ACCESS1                                                                                                                        VALID   SYS
      1080 I_METAPATHMAP$                                                                                                                   VALID   SYS
     10800 KU$_PLUGTS_CHECKPL_VIEW                                                                                                          VALID   SYS

SQL> @allstat

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0pm99r7nvp84r, child number 0
-------------------------------------
select t1.object_id, t1.object_name, t3.status, t2.owner from t1,t3,t2
where t1.object_id=to_number(t3.object_id) and
t2.object_id=to_number(t3.object_id) and ( t1.object_id in
(108,1080,10800) or t2.object_id in(108,1080,10800))

Plan hash value: 3864638661
--CBO的表T1错误选择全表扫描
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |        |      3 |00:00:00.07 |    2268 |       |       |          |
|   1 |  CONCATENATION                  |                  |      1 |        |      3 |00:00:00.07 |    2268 |       |       |          |
|*  2 |   HASH JOIN                     |                  |      1 |      3 |      3 |00:00:00.06 |    1136 |   843K|   843K|  611K (0)|
|   3 |    NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      16 |       |       |          |
|   4 |     INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       8 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| T2               |      3 |      3 |      3 |00:00:00.01 |       8 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | IDX_T2_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       5 |       |       |          |
|   7 |     TABLE ACCESS BY INDEX ROWID | T3               |      3 |      1 |      3 |00:00:00.01 |       8 |       |       |          |
|*  8 |      INDEX RANGE SCAN           | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |       |       |          |
|   9 |    TABLE ACCESS FULL            | T1               |      1 |  81018 |  75253 |00:00:00.02 |    1120 |       |       |          |
|  10 |   NESTED LOOPS                  |                  |      1 |        |      0 |00:00:00.01 |    1132 |       |       |          |
|  11 |    NESTED LOOPS                 |                  |      1 |      3 |      0 |00:00:00.01 |    1132 |       |       |          |
|  12 |     NESTED LOOPS                |                  |      1 |      3 |      3 |00:00:00.01 |    1127 |       |       |          |
|* 13 |      TABLE ACCESS FULL          | T1               |      1 |      3 |      3 |00:00:00.01 |    1119 |       |       |          |
|  14 |      TABLE ACCESS BY INDEX ROWID| T3               |      3 |      1 |      3 |00:00:00.01 |       8 |       |       |          |
|* 15 |       INDEX RANGE SCAN          | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |       |       |          |
|* 16 |     INDEX RANGE SCAN            | IDX_T2_OBJECT_ID |      3 |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  17 |    TABLE ACCESS BY INDEX ROWID  | T2               |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."OBJECT_ID"=TO_NUMBER("OBJECT_ID"))
   6 - access(("T2"."OBJECT_ID"=108 OR "T2"."OBJECT_ID"=1080 OR "T2"."OBJECT_ID"=10800))
   8 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
  13 - filter(("T1"."OBJECT_ID"=108 OR "T1"."OBJECT_ID"=1080 OR "T1"."OBJECT_ID"=10800))
  15 - access("T1"."OBJECT_ID"="T3"."SYS_NC00016$")
  16 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
       filter((LNNVL("T2"."OBJECT_ID"=108) AND LNNVL("T2"."OBJECT_ID"=1080) AND LNNVL("T2"."OBJECT_ID"=10800)))

Note
-----
   - dynamic sampling used for this statement (level=2)


47 rows selected.


--由于对表T1到T4的列object_id分布我们都清楚，都中唯一值，过滤性很强
所以可以采用人工HINT去干预，也预示着我们在用to_char,to_number对列值进行转换连接的SQL，
需谨慎，极易造成执行计划的不稳定以及耗能过高；
--加入T1表的OBJECT_ID索引HINT纠正
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2qfqkjg1fzy0k, child number 0
-------------------------------------
select   /*+ index(t1(object_id) */ t1.object_id, t1.object_name,
t3.status, t2.owner from t1,t3,t2 where
t1.object_id=to_number(t3.object_id) and
t2.object_id=to_number(t3.object_id) and ( t1.object_id in
(108,1080,10800) or t2.object_id in(108,1080,10800))

Plan hash value: 3376884351

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   1 |  CONCATENATION                   |                  |      1 |        |      3 |00:00:00.01 |      48 |
|   2 |   NESTED LOOPS                   |                  |      1 |        |      3 |00:00:00.01 |      27 |
|   3 |    NESTED LOOPS                  |                  |      1 |      3 |      3 |00:00:00.01 |      24 |
|   4 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      18 |
|   5 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       9 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T2               |      3 |      3 |      3 |00:00:00.01 |       9 |
|*  7 |        INDEX RANGE SCAN          | IDX_T2_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       6 |
|   8 |      TABLE ACCESS BY INDEX ROWID | T3               |      3 |      1 |      3 |00:00:00.01 |       9 |
|*  9 |       INDEX RANGE SCAN           | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|* 10 |     INDEX RANGE SCAN             | IDX_T1_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       6 |
|  11 |    TABLE ACCESS BY INDEX ROWID   | T1               |      3 |      1 |      3 |00:00:00.01 |       3 |
|  12 |   NESTED LOOPS                   |                  |      1 |        |      0 |00:00:00.01 |      21 |
|  13 |    NESTED LOOPS                  |                  |      1 |      3 |      0 |00:00:00.01 |      21 |
|  14 |     NESTED LOOPS                 |                  |      1 |      3 |      3 |00:00:00.01 |      16 |
|  15 |      INLIST ITERATOR             |                  |      1 |        |      3 |00:00:00.01 |       8 |
|  16 |       TABLE ACCESS BY INDEX ROWID| T1               |      3 |      3 |      3 |00:00:00.01 |       8 |
|* 17 |        INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |      3 |      3 |      3 |00:00:00.01 |       5 |
|  18 |      TABLE ACCESS BY INDEX ROWID | T3               |      3 |      1 |      3 |00:00:00.01 |       8 |
|* 19 |       INDEX RANGE SCAN           | IDX_T3_OBJECT_ID |      3 |      1 |      3 |00:00:00.01 |       5 |
|* 20 |     INDEX RANGE SCAN             | IDX_T2_OBJECT_ID |      3 |      1 |      0 |00:00:00.01 |       5 |
|  21 |    TABLE ACCESS BY INDEX ROWID   | T2               |      0 |      1 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access(("T2"."OBJECT_ID"=108 OR "T2"."OBJECT_ID"=1080 OR "T2"."OBJECT_ID"=10800))
   9 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
  10 - access("T1"."OBJECT_ID"="T3"."SYS_NC00016$")
  17 - access(("T1"."OBJECT_ID"=108 OR "T1"."OBJECT_ID"=1080 OR "T1"."OBJECT_ID"=10800))
  19 - access("T1"."OBJECT_ID"="T3"."SYS_NC00016$")
  20 - access("T2"."OBJECT_ID"="T3"."SYS_NC00016$")
       filter((LNNVL("T2"."OBJECT_ID"=108) AND LNNVL("T2"."OBJECT_ID"=1080) AND
              LNNVL("T2"."OBJECT_ID"=10800)))


49 rows selected.

SQL>

launch_225
关注
【微服务】mybatis typehandler使用详解
congge
02-25 6110
mybatis typehandler 使用详解
PostgreSQL的学习心得和知识总结（一百二十六）|深入理解PostgreSQL数据库SQL执行时间计算 \timing 和 log_duration的使用和原理
孤傲小二~阿沐的博客
10-24 224
1、参考书籍：《PostgreSQL数据库内核分析》2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》3、
Oracle number to char 研究
专栏
08-15 208
解决 number to char 补零问题 由于字段在DB中number类型 可以保存 int ,float ,double 数据. 当int 类型 时 直接转换 to_char 或者 || 不会出问题.但是当为float 或者 double 时 会出转换问题(0.25 --> .25 类似问题). 此Sql语句 decode(length(REPLACE(to_char(ACT...
to_number, to_char用法简介
静渊
11-24 923
（1）to_number [url]http://hi.baidu.com/sunhuaj/blog/item/73bef9f44d745c64dcc47436.html[/url] Converts a string to the NUMBER data type（将字符串转换为数字数据类型） TO_NUMBER([, , ]) RETURN NUMBER select to_num...
oracle number 少0,Oracle中对number类型数据to_char()出现诸位少0,或者值为#的处理
weixin_35816287的博客
04-08 1522
Oracle中对number类型数据to_char()出现各位少0,或者值为###的处理问题描述:在Oracle中使用to_char()函数时当number值为小数时,常常个位0不显示比如:select to_char(0.02) from dual,结果为.02改进为select to_char(0.02,'FM0.9999') from dual,发现个位的0出来了当然这里还有问题,当参数为...
数据库学习（三） sql语句中添加函数 to_char,round,连接符||
weixin_30755709的博客
01-03 156
**　to char 是把日期或数字转换为字符串　　to date 是把字符串转换为数据库中得日期类型　　 　　参考资料:https://www.cnblogs.com/hllnj2008/p/5332962.html **　|| 字符串连接符　 　　参考资料：https://www.cnblogs.com/forget406/p/5506394.html **　round...
统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟
莫等闲
07-11 1589
一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。 优化前： SELECT 20131023, "A2"."ORG_ID", COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'DP' THEN
出现 ORA-01439: column to be modified must be empty to change datatype 解决方法
最新发布
码农研究僧的博客
08-23 206
oracle修改字段的时候类型的时候，出现如下问题： ORA-01439: column to be modified must be empty to change datatype
《Python基础教程》内容总览篇（持续更新中）
热门推荐
weixin_43178406的博客
08-26 20万+
大家好，我是爱编程的喵喵。双985硕士毕业，现担任全栈工程师一职，热衷于将数据思维应用到工作与生活中。从事机器学习以及相关的前后端开发工作。曾在阿里云、科大讯飞、CCF等比赛获得多次Top名次。现为CSDN博客专家、人工智能领域优质创作者。喜欢通过博客创作的方式对所学的知识进行总结与归纳，不仅形成深入且独到的理解，而且能够帮助新手快速入门。个人精心开设的《Python基础课程》专栏订阅量接近900，帮助不少同学解决了Bug。
使用PL_SQL进行数据转换和处理
PL/SQL是一种结构化查询语言（Structured Query Language），它是Oracle数据库的编程语言扩展，用于在数据库中执行数据操作、数据处理和业务逻辑。PL/SQL结合了SQL语句的强大操作能力和通用编程语言的灵活性，提供了...
ORACLE函数TO_CHAR以及数字转换格式
妞妞的专栏
01-05 1028
<br /><br />参考<<Oracle Database SQL Reference>>.<br />      关于nls可有取值，请注意视图sys.V_$NLS_VALID_VALUES<br />     一，TO_CHAR(NUMBER)<br />本函数把参数N转为一个VARCHAR2类型的数值。N可以是NUMBER,BINARY_FLOAT,或者BINARY_DOUBLE。如果不带格式，那么函数会把N转换为足以表示N的VARCHAR2字符串。<br />      格式表参考：<br
Oracle中to_number()函数的用法
qq_499514925的博客
12-28 8937
Oracle中to_number()函数的用法 to_number()函数是oracle中常用的类型转换函数之一，是将一些处理过的按一定格式编排过的字符串变回数值型的格式。 1、to_number()函数可以将char或varchar2类型的string转换为一个number类型的数值； 2、需要注意的是，被转换的字符串必须符合数值类型格式,如果被转换的字符串不符合数值型格式，Oracle将抛出...
sql 时间函数
qq_41755706的博客
10-18 484
参考：oracle时间函数 1、date_format函数(MYSQL) DATE_FORMAT(date,format)函数用于以不同的格式显示日期/时间数据。 date是日期列,format是格式 select * from sign_event where date_format(start_time,'%m') > '08'; -- 查询八月份以后的数据（不包含八月份） 结果： 2、oralce to_char和to_date函数 to_char 把时间按格式输出为字符串 to_date
oracle和mysql判断空值替换函数
中森明菜的博客
09-10 1265
Mysql IFNULL(expression, alt_value) 如果expression不为空，则为expression的值，如果expression为空则为alt_value的值 Oracle NVL(eExpression1, eExpression2) 如果eExpression1和eExpression2的值都为null，则返回null，其余与mysql一致 Oracle其他函数 decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 如果条件=值1，则返回
to_number() ----函数的用法
fu_manxing的专栏
08-10 3万+
1 用法简介 TO_NUMBER函数()是Oracle中常用的类型转换函数之一，主要是将字符串转换为数值型的格式，与TO_CHAR()函数的作用正好相反。 To_number函数的格式如下： To_number（varchar2 or char,’format model’）1 To_number函数中也有很多预定义的固定格式： 格式值 含义
Oracle与Access在开发中的一些不同之处
da.wang的博客
09-27 5898
Oracle与Access在开发中的一些不同之处1.      函数名称的不同 功能 Oracle Access
Oracle中TO_NUMBER()函数的用法
干勾鱼的CSDN博客
10-08 19万+
1 用法简介TO_NUMBER函数()是Oracle中常用的类型转换函数之一，主要是将字符串转换为数值型的格式，与TO_CHAR()函数的作用正好相反。To_number函数的格式如下：To_number（varchar2 or char,’format model’）To_number函数中也有很多预定义的固定格式： 格式值 含义 9 代表一个数字 0 强迫0显示 $
oracle检索字符位置instr及字符串转数字to_number
l_degege的专栏
01-15 803
字符串转数字函数to_number select * from ( select null bh from dual union all select 2 bh from dual )aa order by to_number(bh) desc nulls last https://blog.csdn.net/elite_1989/article/details/13016599 字段若为null赋值函数nvl NVL(expr1,expr2) expr1：可为空的字段或者..
Oracle数据库转换函数与SQL基础：数据类型转换与SELECT语句详解
**显式数据类型转换**相比之下，显式转换则需要用户明确指定，使用CAST()或TO_XXX函数（如TO_NUMBER()、TO_DATE()等）来强制执行转换。这种方式提供了更大的控制，但也可能导致精度损失或错误，因此在处理敏感数据时...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值