现象
1、select 1/0 from dual;
oracle会报除数为0的错误
2、
begin
execute immediate('select 1/0 from dual');
end;
oracle不报错
3、
begin
execute immediate('insert into test8 select 1/0 from dual');
end;
会报除数为0的错误
问题：为什么第二条语句不报错
------解决思路----------------------
你可以试试 execute immediate v_sql into 的用法应该就报错了
由于按你这种写法，无需返回结果，oracle应该是自己进行了优化，判断select子句不需要执行吧
没做过测试，个人猜想
------解决思路----------------------
2、
begin
execute immediate('select 1/0 from dual');
end;
oracle不报错
一般情况要select 后要有into的没有into字句就没有意思，但是跟踪一下sql执行发现sql可以正常解析
begin
execute immediate 'select 1/0 from dual';
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 16.03 16.03
********************************************************************************
SQL ID: 360pfbbv2s5hg
Plan Hash: 1388734953
select 1/0
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
发现Fetch 为0，猜测除数为0的错误应该在fetch报出来的