前段时间把一个查询效率很低的视图给改成了 带一个日期参数的函数,输入一个日期,返回相应的结果集。但是这样没能完整地复制视图原有的功能,比如给定一个日期区间的查询就做不到了。于是今天给扩充了一下,然后就遇到了这个该死的莫名其妙的PLS-00801.
代码呢是这样的:
create table stockstructure (
oc_date NUMBER(10) ,
exchange_type CHAR(4) ,
stock_code CHAR(12) ,
total_amount NUMBER(20,3),
turnover_amount NUMBER(20,3) ,
remark VARCHAR2(2000 )
);
create or replace type rec_stockstructure is OBJECT(
oc_date NUMBER(10 ) ,
exchange_type CHAR(4 ) ,
stock_code CHAR(12 ) ,
total_amount NUMBER(20 ,3),
turnover_amount NUMBER(20 ,3) ,
remark VARCHAR2(2000 )
);
create or replace type ty_stockstructure is table of rec_stockstructure;
create or replace function fn_get_stockstructure(
p_oc_date hstype.HsDate,
p_end_date hstype.hsdate default 0
)
return ty_stockstructure
is
tmp_stockstructure ty_stockstructure := ty_stockstructure();
rs_stockstructure ty_stockstructure := ty_stockstructure();
begin
if p_end_date = 0 then
select rec_stockstructure(p_oc_date,a.exchange_type,a.stock_code,a.total_amount,a.turnover_amount,a.remark)
bulk collect into rs_stockstructure
from stockstructure a
where (a.oc_date,a.exchange_type,a.stock_code) in
( select max (t.oc_date),t.exchange_type,t.stock_code from stockstructure t
where oc_date<=p_oc_date
group by t.exchange_type,t.stock_code
)
;
else
for xdate in 0..(to_date(p_end_date,'yyyymmdd' )-to_date(p_oc_date,'yyyymmdd')) loop
select rec_stockstructure(to_char(to_date(p_oc_date,'yyyymmdd' )+xdate,'yyyymmdd'),a.exchange_type,a.stock_code,a.total_amount,a.turnover_amount,a.remark)
bulk collect into tmp_stockstructure
from stockstructure a
where (a.oc_date,a.exchange_type,a.stock_code) in
( select max (t.oc_date),t.exchange_type,t.stock_code from stockstructure t
where oc_date<=to_char(to_date(p_oc_date,'yyyymmdd' )+xdate,'yyyymmdd')
group by t.exchange_type,t.stock_code
)
;
rs_stockstructure := rs_stockstructure multiset union tmp_stockstructure; --------------------- 报错位置
end loop ;
end if;
return rs_stockstructure;
end;
然后就在我标着的位置给抛出了一个错误。错误原文如此:
Error: PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2079; Type 0x0x2b89f96a66e0 has no MAP method.;
于是我就纠结了,这到底说的是啥呢?这几个关键字我连搜索都没法搜。而且更纠结的是,在Oracle 11g 上不报错,在 Oracle 10g 上报错。那么是版本问题嘛?我查了下,10g multiset 的支持完全没问题啊。我又在报错的这个数据库上做了个这样的测试:
select * from table(
ty_stockstructure(rec_stockstructure(1234,'1','1234',1,1,'1'))
multiset union
ty_stockstructure(rec_stockstructure(2234,'2','2234',2,2,'2'))
)
结果也是好好的,完全符合预期。真是无厘头啊,这都什么情况啊……