PL/SQL集合 ----- varrays

varrays可以再表，记录，对象定义中使用，类似于C中的数组。

1.定义varrays用作PL/SQL程序构造块。

declare
    type integer_varray is varray(3) of integer; --定义int型varray，含3行
    var_int integer_varray:=integer_varray(); --声明var_int的varray 变量
begin
    for i in 1..3 loop    --为var_int赋值
        var_int.extend;
        var_int(i):=10+i;
    end loop;

    DBMS_OUTPUT.PUT_LINE('Varray initalized as vaules.');
    DBMS_OUTPUT.PUT_LINE('------------------------------');

    for i in 1..3 loop
        DBMS_OUTPUT.PUT('Integer Varray ['||i||']');
        DBMS_OUTPUT.PUT_LINE('['var_int(i)||']');
    end loop;

end;
/

Varray initalized as vaules.
------------------------------
Integer Varray [1] [11]
Integer Varray [2] [12]
Integer Varray [3] [13]

 

2.定义varrarys  用PL/SQL对象类型

create or replace type integer_varray
as varray(3) of integer;
/

declare
    var_int integer_varray:=
        interger_varry(null,null,null);
    
 for i in 1..3 loop    --为var_int赋值
        var_int.extend;
        var_int(i):=10+i;
    end loop;

    DBMS_OUTPUT.PUT_LINE('Varray initalized as vaules.');
    DBMS_OUTPUT.PUT_LINE('------------------------------');

    for i in 1..3 loop
        DBMS_OUTPUT.PUT('Integer Varray ['||i||']');
        DBMS_OUTPUT.PUT_LINE('['var_int(i)||']');
    end loop;

end;
/

Varray initalized as vaules.
------------------------------
Integer Varray [1] [11]
Integer Varray [2] [12]
Integer Varray [3] [13]

3.在数据库表中使用varrays

--1.创建varrays类型
crate or replace type address_varray
as varray(3) of varchar(30);
/

--2.用varrays创建表
create table address
(
    address_id interger not null,
    individual_id interger not null,
    street_address  address_varray not null,
    city  varchar(10 char) 
)

--3.在数据库操作中使用varrays
insert
into addresses
vaules
(
    1,
    1,
    address_varray(
        'office of sentor',
        '450 street paseo',
        'suit 2000'
        )
    'Tucson'
);

--4.在数据库里查询varrays
   ->varrays无法直接查询,得不到期望结果
select   street_address
         from addresses
         where adress_id=1;


   ->varrays无法直接查询，需要借助嵌套表

create or replace type varray_nested_table
is table of varchar(30 char);
/     ---创建嵌套表


col column_vaule format a30
select column_vaule
from THE(select
         cast(street_address as varray_nested_table)
         from addresses
         where adress_id=1;
    )

column_vaule
-----------------
office of sentor
450 street paseo
suit 2000

--5.在数据库中更新varrays内容
    >更新varrays全部内容
updare addresses
set street_address=
         address_varray(
        'office of sentor',
        '450 street paseo',
        'suit 1150'
        )
where address_id=1;

 > 更新varrays部分内容
    >PL/SQL不支持直接更新varrays部分内容，需要使用PL/SQL程序才能更新部分内容
declare
    type address_type is record
    (
        address_id interger not null,
        individual_id interger not null,
        street_address  address_varray not null,
        city  varchar(10 char)   
    );
    address address_type;

    cursor get_street_address
    (address_id_in integer) is
    select *
    from address_id=address_id_in;

    open get_street_address(1);     
    
    fetch get_street_address
    into address;

    close get_street_address;

    address.street_address(1)='office of shanghai';

    update adresses
    set street_address=address.street_address;
    where address_id=1;
end;
/