v_deptinfo scott.dept%rowtype;
v1 scott.dept.deptno%type,
v2 scott.dept.dname%type,
v3 scott.dept%rowtype--可以声明ROWTYPE类型
);
v_deptrecord dept_record;
begin
end;
--索引表1
declare
type my_index_table1 is table of scott.dept.dname%type
my1 my_index_table1;
c number(2);
begin
select count(*) into c from dept;
select dname into my1(i) from
(select rownum rn,t.* from dept t) x
where x.rn=i;
end loop;
for i in 1..my1.count loop
dbms_output.put_line(my1(i));
end loop;
end;
--索引表2
declare
type my_index_table1 is table of scott.dept.dname%type
index by varchar2(20);
my1 my_index_table1;
begin
select loc into my1('南昌') from dept where deptno=10;
dbms_output.put_line(my1('南昌'));
end;
--嵌套表1
declare
type my_index_table1 is table of scott.dept.dname%type;
begin
select dname into my1(1) from dept where deptno=10;
select dname into my1(2) from dept where deptno=20;
select dname into my1(3) from dept where deptno=30;
select dname into my1(4) from dept where deptno=40;
my1.delete(3);
dbms_output.put_line(my1.count);
select dname into my1(3) from dept where deptno=30;
dbms_output.put_line(my1.count);
for i in 1..my1.count loop
dbms_output.put_line(my1(i));
end loop;
end;
--嵌套表2
create table employee (
eid number(4),
ename varchar2(10),
phone phone_type
insert into employee
values(1,'xx',phone_type('0791-111','123454545'));
insert into employee
values(2,'xx',phone_type('0791-111','123454545','saaasf'));
--变长数组
declare
type my_index_table1 is varray(3) of scott.dept.dname%type;
my1 my_index_table1:=my_index_table1('a','b','c');
begin
select dname into my1(1) from dept where deptno=10;
select dname into my1(3) from dept where deptno=20;
for i in 1..my1.count loop
dbms_output.put_line(my1(i));
end loop;
end;
--记录表2
declare
type dept_record is RECORD(
v1 scott.dept.deptno%type,
v2 scott.dept.dname%type,
v3 scott.dept.loc%type
);
type my_index_table1 is table of dept_record
index by binary_integer;
my1 my_index_table1;
c number(2);
begin
select count(*) into c from dept;
for i in 1..c loop
select x.deptno,x.dname,x.loc into my1(i) from
(select rownum rn,t.* from dept t) x
where x.rn=i;
end loop;
for i in 1..my1.count loop
dbms_output.put_line(my1(i).v2);
end loop;
end;