sql隐式转换的

发布网友 发布时间：2022-04-10 13:30

我来回答

共3个回答

懂视网 时间：2022-04-10 17:51

10; OBJECT_ID CREATED ---------- ------------------ 20 25-JUL-18 46 25-JUL-18 28 25-JUL-18 15 25-JUL-18 29 25-JUL-18 3 25-JUL-18 25 25-JUL-18 41 25-JUL-18 54 25-JUL-18 9 rows selected. select object_name from function where to_char(created,‘yyyymmdd hh24:mi‘)=‘20180725 12:51‘; 20180725 12:54 drop table tab; create table tab(id number(20),datetime date); declare i number; dd date; BEGIN dd := sysdate; i:= 0; for x in 1..5000 loop --if mod(i,100)=0 then insert into tab(id,datetime) values(i,dd); dd := dd+1; i := i+1; end loop; END; / create table tab(id varchar2(20),datetime date); 1289 20220429 13:58 29-APR-22 ************************ 测试1 ************************ select * from tab where id=1289 and datetime=‘29-APR-22‘; select * from tab where id=1289 and datetime=‘29-APR-22‘;SQL> no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER("ID")=1289 AND "DATETIME"=‘29-APR-22‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed ************************ 测试2 ************************ select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘; select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘; SQL> no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=‘1289‘ AND "DATETIME"=‘29-APR-22‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed 刷新cache之后再执行 SQL> select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=‘1289‘ AND "DATETIME"=‘29-APR-22‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16 consistent gets 14 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed ************************ 测试3 ************************ select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME -------------------- -------------- 1289 29-04-22 13:58 Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 13 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyy ymmdd‘)=‘20220429‘) Statistics ---------------------------------------------------------- 62 recursive calls 0 db block gets 51 consistent gets 0 physical reads 0 redo size 600 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试4 ************************ create index tab_index on tab(id,to_char(datetime,‘yyyymmdd‘)); SQL> / ID DATETIME -------------------- -------------- 1289 29-04-22 13:58 Execution Plan ---------------------------------------------------------- Plan hash value: 4028735706 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 13 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘ 20220429‘) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 3 physical reads 0 redo size 600 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试5 ************************ 新创建的表 select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> / ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 22 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyym mdd‘)=‘20220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 14 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试5 ************************ create index tab_index on tab(id,to_char(datetime,‘yyyymmdd‘)); SQL> select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 4028735706 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 28 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20 220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 10 recursive calls 0 db block gets 27 consistent gets 3 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试6 ************************ select * from tab where id=1289 and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> select * from tab where id=1289 and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 4028735706 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 28 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20 220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试6 ************************ select * from tab where to_char(id)=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> select * from tab where to_char(id)=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 28 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_CHAR("ID")=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 35 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

 

SQL执行异常系列之——隐式转换

标签：pre   exec   statistic   drop   row   orm   fun   obj   ips   

热心网友 时间：2022-04-10 14:59

是的 比如 int.parse("1");
insert into a(sid,sname) values('1',1)
可以运行成功，但是
insert into a(sid,sname) values('a1',1)
将 varchar 值 'a1' 转换为数据类型为 int 的列时发生语法错误。
insert into a(sid,sname）values(a1,1)
在此上下文中不允许使用 'a1'。此处只允许使用常量、表达式或变量。

热心网友 时间：2022-04-10 16:17

从int转成varchar型..只要你的int是正确的..都可以转换

把varchar 转成int 如果varchar中不带字符,只有数字.也是可以转换成功的.但是有字符就不行