create or replace type Varchar2Varray as VARRAY(100) of VARCHAR2(40);...
userIds Varchar2Varray := Varchar2Varray(NULL);
...
select dp.department_id into dpIds from department dp where dp.long_id like concat(longId, '%');//肯定不对我想实现上面这条sql的查询记录放入到userIds里面,请问如何操作?谢谢!
userIds Varchar2Varray := Varchar2Varray(NULL);
...
select dp.department_id into dpIds from department dp where dp.long_id like concat(longId, '%');//肯定不对我想实现上面这条sql的查询记录放入到userIds里面,请问如何操作?谢谢!
userIds Varchar2Varray := Varchar2Varray(NULL);
select dp.department_id bulk collect into dpIds from department...
Create Or Replace Type MingXiType As Object
(
goodsId varchar(15),
InCount int,
ProviderID varchar(10)
)
B.建立嵌套项类型的可变数组:
格式: Create Or Replace 可变数组类型名 As Varray(最大行数) Of 可变数组的基类型;
实例:
Create Or Replace Type arrMingXiType As Varray(100) Of MingXiType;
C.创建一个主表
Create Table InStockOrder
(
OrderID Varchar(15) Not Null Primary Key,
InDate Date,
OperatorID varchar(10),
MingXi arrMingXiType
);
2.操作可变数组
A.插入数据
Insert Into InStockorder
Values('200208070001',To_Date('2002-08-07','YYYY-MM-DD'),'0002',
arrMingXiType(MingXiType('s001',100,'10009'),
MingXiType('T002',400,'10003')))
B.查询数据
使用普通的Select 不能显示Varray中的数据。要使用带有游标的PL/SQL块来操纵Varray.
但可以使用Table()函数来查询集合列
select * from table(select t.MingXi from instockorder t
where t.orderid ='200208070001')
C.修改数据
实例:把编号为200708070001的入库单的货物编号为S001的入库数量改为200
Update InStockorder
Set MingXi= arrMingXiType(MingXiType('S001',200,'10009'),
MingXype('T002',400,'10003')
)
Where OrderID='200208070001'
注意:不能更新Varray中的单个元素,必须更新整个Varray,
所以可变数组应用的时机应当是用户很少更改或根本不去修改的数据