首页案例文档
在线试玩、下载、社区等更多功能,请登陆PC访问:eco.dameng.com

关于特定情况下调用自定义包产生的BUG,会产生错误结果集,怀疑是子查询中变量作用域的BUG

DM_036821 2021/12/04 161 6

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:8
【操作系统】:CENTOS7
【CPU】:X86
【问题描述】*:
#1. # 1.创建PACKAGE

PACKAGE信息:
create or replace package test_pack is 
  function set_useId(useId varchar2) return varchar2; 
  function get_useId return varchar2;
  end test_pack;
/

create or replace package body test_pack is  
       paramuseId varchar2(50);  
       -- Param  
       function set_useId(useId varchar2) return varchar2 is  
       begin  
         paramuseId:=useId;  
         return useId;  
        end;  
       function get_useId return varchar2 is  
       begin  
         return paramuseId;  
       end;       
   end test_pack;
/

#2.创建测试用表和视图(不用关注逻辑,关注产生的结果)

create table t1(id int,c1 varchar2(100),c2 varchar2(100) );
create table t2(id int,c1 varchar2(100),c2 varchar2(100) );

insert into t1 values(1,'a','a');
insert into t2 values(1,'a','a');
insert into t1 values(2,'b','b');
insert into t2 values(2,'b','b');
insert into t1 values(3,'c','c');
insert into t2 values(3,'c','c');
commit;

create or replace view test_view as
SELECT t1.c1 from t1 inner join t2 on t1.c1 = t2.c1
where t2.c1 in (test_pack.get_useId())
or t2.c2 in (test_pack.get_useId());

#3 通过SQL进行查询:

select * from test_view where test_pack.set_useId(‘a’)=‘a’;

Server[LOCALHOST:5236]:mode is primary, state is open
login used time : 0.962(ms)
disql V8
SQL> select * from test_view where test_pack.set_useId('a')='a';
no rows

used time: 7.822(ms). Execute id is 2400.
SQL> 

此时我们手工执行一遍set_useID
SQL> select  test_pack.set_useId('a');

LINEID     TEST_PACK.SET_USEID('a')
---------- ------------------------
1          a

used time: 0.228(ms). Execute id is 2601.
SQL> select * from test_view where test_pack.set_useId('a')='a';

LINEID     C1
---------- --
1          a

used time: 1.093(ms). Execute id is 2602.
神奇的一幕发生了有结果了,但是如果在查询视图时候把set_useId改成b会发生什么。。。
SQL> select * from test_view where test_pack.set_useId('b')='b';

LINEID     C1
---------- --
1          a

used time: 2.563(ms). Execute id is 2603.
我草!错误结果集!

4.其实VIEW本质就是一个SQL文本,猜测问题在于PACKAGE在子查询中执行的过程中变量的作用域有问题

select * from (
SELECT t1.c1 from t1 inner join t2 on t1.c1 = t2.c1
where t2.c1 in (test_pack.get_useId)
or    t2.c2 in (test_pack.get_useId)
)
where test_pack.set_useId('a')='a';

no rows

used time: 0.798(ms). Execute id is 2701.
SQL> 



5.相同的实验在ORACLE 11.2.0.4中无异常。

回答 0
暂无回答
扫一扫
联系客服