遇到一个非常奇怪的问题,我有一条关于minus的sql语句,语句如下
select COUNT(*) INTO N_COUNT FROM
(select table_name,column_name,data_type,data_length
from all_tab_cols
where owner='USER1_CHECK' AND TABLE_NAME='AC01'
MINUS
select table_name,column_name,data_type,data_length
from all_tab_cols
where owner='USER1' AND TABLE_NAME='AC01'),在pl/sql执行结果为0,但把这个语句放到存储过程中执行,结果为55,请问这到底是怎么回事,为什么会存在不同的结果,请各位大侠指教
select COUNT(*) INTO N_COUNT FROM
(select table_name,column_name,data_type,data_length
from all_tab_cols
where owner='USER1_CHECK' AND TABLE_NAME='AC01'
MINUS
select table_name,column_name,data_type,data_length
from all_tab_cols
where owner='USER1' AND TABLE_NAME='AC01'),在pl/sql执行结果为0,但把这个语句放到存储过程中执行,结果为55,请问这到底是怎么回事,为什么会存在不同的结果,请各位大侠指教
加上输出看看。SQL> select count(*) from
2 (
3 select empno,job from emp
4 minus
5 select empno,job from emp where job='CLERK'); COUNT(*)
----------
10已用时间: 00: 00: 00.20SQL> set serveroutput on;
SQL> declare
2 v_out number;
3 begin
4 select count(*) into v_out from
5 (
6 select empno,job from emp
7 minus
8 select empno,job from emp where job='CLERK');
9 dbms_output.put_line(v_out);
10 end;
11 /
10PL/SQL 过程已成功完成。已用时间: 00: 00: 00.40
SQL>
is
N_COUNT NUMBER;
begin
select COUNT(*) INTO N_COUNT FROM
((select table_name,column_name,data_type,data_length
from all_tab_cols
where owner='USER1_CHECK' AND TABLE_NAME='AC01')
MINUS
(select table_name,column_name,data_type,data_length
from all_tab_cols
where owner='USER1' AND TABLE_NAME='AC01'));
end ttt;
存储过程如上,两种执行结果不一样
返回了你就得到你想要的数了
如果一个会话 修改了表,而没有commit;其它会话 就不会查询到变化的部分。