oracle9i function return null,「含初始化脚本」 Oracle Function return number类型与number字段比较，结果不正确...

17:05:32 saup@TEST10G> set autotrace traceonly

17:05:37 saup@TEST10G>

select t1.charge_time,

unix_to_oracle(t1.charge_time),

t2.start_time,

oracle_to_unix(t2.start_time)

from test1 t1, test2 t2

17:05:38   6  where t1.charge_time > oracle_to_unix(t2.start_time);

7 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3190742687

----------------------------------------------------------------------------

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time           |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |         1 |        22 |         6   (0)| 00:00:01 |

|   1 |  NESTED LOOPS           |           |         1 |        22 |         6   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TEST2 |         1 |         9 |         3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| TEST1 |         1 |        13 |         3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("T1"."CHARGE_TIME">"ORACLE_TO_UNIX"("T2"."START_TIME"))

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

970  bytes sent via SQL*Net to client

492  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

7  rows processed

17:05:40 saup@TEST10G>

17:05:58 saup@TEST10G>

select t1.charge_time,

unix_to_oracle(t1.charge_time),

t2.start_time,

oracle_to_unix(t2.start_time)

from test1 t1, test2 t2

17:05:59   6  where t1.charge_time > to_number(oracle_to_unix(t2.start_time));

6 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3190742687

----------------------------------------------------------------------------

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time           |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |         1 |        22 |         6   (0)| 00:00:01 |

|   1 |  NESTED LOOPS           |           |         1 |        22 |         6   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TEST2 |         1 |         9 |         3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| TEST1 |         1 |        13 |         3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("T1"."CHARGE_TIME">TO_NUMBER(TO_CHAR("ORACLE_TO_UNIX"("T2"

."START_TIME"))))

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

47  recursive calls

0  db block gets

19  consistent gets

0  physical reads

0  redo size

950  bytes sent via SQL*Net to client

492  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

6  rows processed