oracle - 在正常的 oracle 函数中:ORA-06531:引用未初始化的集合
问题描述
我的函数执行完美,但在声明它时显示错误。代码是:
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
提前致谢。
解决方案
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;
/
但是,最简单的解决方案是使用连接并同时填充所有集合(假设在主键上从 issue 到 book 存在多对一关系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;
/
推荐阅读
- jquery - 边界半径在内部仍然是正方形
- python - 将数据从文本文件转换为 1 Excel 的处理时间
- html - 使用 HTML 和 CSS 编码创建组织结构图
- reactjs - react如何更新DOM的一部分?
- css - 如何将背景图像放置在另一个背景图像的确切中心
- ios - iOS集成SDK、包
- angular - Angular 6 BehaviourSubject.next 要求类方法的值
- httpclient - 地图运算符下的代码不适用于角度 6
- php - 在 PHP Echo 中设置下拉列表值
- android-studio - 设备未在 Android Studio 中显示抖动