在普通oracle函数中: ORA-06531:对未初始化集合的引用
在普通oracle函数中: ORA-06531:对未初始化集合的引用
提问于 2018-05-24 07:26:09
我的函数执行得很好，但在声明时却显示了错误。守则是：
set serveroutput on
create or replace type myarray is varray(10000) of number;
create or replace function T_Bill (cus_id in integer) return number as
t_cost number:=0;
n integer;
bk myarray;
pr myarray;
q myarray;
begin
select count(book_id) into n from issue where cust_id=cus_id;
select book_id,quantity BULK COLLECT into bk,q from issue where cust_id=cus_id;
for i in 1..n
loop
select price into pr(i) from book where ISBN=bk(i);
DBMS_OUTPUT.PUT_LINE(bk(i)||' '||q(i));
t_cost:=pr(i);
end loop;
return t_cost;
end;
/我宣布如下：
declare
x integer:=1;
begin
DBMS_OUTPUT.PUT_LINE(T_Bill(x));
end;
/这些错误是：
ORA-06531: Reference to uninitialized collection
ORA-06512: at "R1507090.T_BILL", line 12
ORA-06512: at line 4提前谢谢。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-05-24 10:36:28
当尚未初始化集合时，您正在尝试设置pr(i)。而且，每次循环的迭代都要覆盖t_cost。
create or replace function T_Bill (
cus_id in integer
)
return number
as
t_cost number:=0;
bk myarray;
pr myarray;
q myarray;
begin
select book_id,quantity
BULK COLLECT into bk,q
from issue
where cust_id=cus_id;
pr := myarray(); -- Initialise the collection
pr.EXTEND( bk.COUNT ); -- Set the size of the collection
FOR i IN 1..bk.COUNT LOOP
select price into pr(i) from book where ISBN=bk(i);
DBMS_OUTPUT.PUT_LINE(bk(i)||' '||q(i));
t_cost:= t_cost + pr(i);
END LOOP;
return t_cost;
end;
/但是，最简单的解决方案是同时使用一个连接并填充所有集合(假设从一个问题到另一个ISBN主键之间存在着多对一的关系)：
create function T_Bill (
cus_id in ISSUE.CUST_ID%TYPE
)
return number
as
t_cost number :=0;
bk myarray;
pr myarray;
q myarray;
begin
select i.book_id,
i.quantity,
b.price
BULK COLLECT into
bk,
q,
pr
from issue i
LEFT OUTER JOIN book b -- INNER JOIN?
ON ( i.book_id = b.ISBN )
where i.cust_id = cus_id;
FOR i IN 1..n LOOP
DBMS_OUTPUT.PUT_LINE(bk(i)||' '||q(i));
t_cost:= t_cost + pr(i);
END LOOP;
return t_cost;
end;
/页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50503436
复制相关文章
相似问题
腾讯云开发者
