点击(此处)折叠或打开

定义简单类型的可变数组;
create or replace type project_varray AS VARRAY(3) OF varchar2(50);
/
drop table department purge;
create table department(
did number,
deptname varchar2(50) NOT null,
projects project_varray
);
insert into department (did,deptname,projects) values(10,\'\',
project_varray(\'aaa\',\'bbb\',\'ccc\'));
insert into department(did,deptname,projects) values(20,\'\',
project_varray(\'<<>>\',\'<>\'));
commit;
select * from table (
select projects from department where did=20
);
update department set projects = project_varray(\'\',
\'<>\',\'<<.inputColumName>>\') where
did = 20 ;
commit;
定义复合类型的可变数组;
drop type project_nested;
drop table department purge;
create or replace type project_type AS object(
projectid number,
projectname varchar2(50),
projectfunds number,
pubdate DATE
);
/
create or replace type project_varray AS VARRAY(3) OF project_type;
/
create table department(
did number,
deptname varchar2(50) NOT null,
projects project_varray,
CONSTRAINT pk_did primary key(did)
);
commit;
insert into department(did,deptname,projects) values(10,\'shanghai\',
project_varray(
project_type(10,\'ERP master System\',100,TO_DATE(\'2015-07-01\',\'yyyy-mm-dd\')),
project_type(20,\'CRM Client System\',1000,TO_DATE(\'2015-06-10\',\'yyyy-mm-dd\'))
));
commit;
insert into department(did,deptname,projects) values(20,\'beijing\',
project_varray(
project_type(11,\'<>\',1000,TO_DATE(\'2015-02-01\',\'yyyy-mm-dd\')),
project_type(12,\'<<.inputColumName>>\',1000,TO_DATE(\'2014-03-01\',\'yyyy-mm-dd\')),
project_type(13,\'<>\',10000,TO_DATE(\'2015-02-01\',\'yyyy-mm-dd\'))
));
commit;
update department set projects=project_varray(
project_type(11,\'<>\',100660,TO_DATE(\'2015-02-01\',\'yyyy-mm-dd\')),
project_type(12,\'<<.inputColumName.cn>>\',106600,TO_DATE(\'2014-03-01\',\'yyyy-mm-dd\')),
project_type(13,\'<>\',1006600,TO_DATE(\'2015-02-01\',\'yyyy-mm-dd\'))
)
where did = 20;
commit;
PL/SQL中定义简单可变数组
declare
type project_varray is VARRAY(3) OF varchar2(50);
v_projects project_varray := project_varray(null,null,null);
begin
v_projects(1) := \'java se\';
v_projects(2) := \'oracle se\';
v_projects(3) := \'ANALYST\';
for x in v_projects.first .. v_projects.last loop
dbms_output.put_line(v_projects(x));
end loop;
end;
/
PL/SQL定义复合可变数组
drop type project_varray;
drop table department purge;
create or replace type project_type AS object(
projectid number,
projectname varchar2(50),
projectfunds number,
pubdate DATE
);
/
create or replace type project_varray AS VARRAY(3) OF project_type;
/
declare
type project_varray is VARRAY(3) OF project_type not null;
v_projects project_varray := project_varray(
project_type(11,\'<>\',1000,TO_DATE(\'2015-02-01\',\'yyyy-mm-dd\')),
project_type(12,\'<<.inputColumName>>\',1000,TO_DATE(\'2014-03-01\',\'yyyy-mm-dd\')),
project_type(13,\'<>\',10000,TO_DATE(\'2015-02-01\',\'yyyy-mm-dd\'))
);
begin
for x in v_projects.first .. v_projects.last loop
dbms_output.put_line(\'id :\' ||
v_projects(x).projectid || \', name:\' ||
v_projects(x).projectname || \', money:\' ||
v_projects(x).projectfunds || \', time:\' ||
v_projects(x).pubdate);
end loop;
end;
/

ITPub博客

oracle开发学习篇可变数组

最新文章

ITPub博客

oracle开发学习篇 可变数组

最新文章

oracle开发学习篇可变数组