plsql-复合变量

set serveroutput on
declare
v1 dept%rowtype;
begin
select * into v1 from dept where rownum=1;
dbms_output.put_line(v1.deptno);
dbms_output.put_line(v1.dname);
dbms_output.put_line(v1.loc);
end;
/

 

 

Pl/sql 表（集合），表面上看象数组，但不是，它更象一个带有主键的表，我们通过主键
来访问数据。但又不是表。
含有两要素：
主键，数据类型为 BINARY_INTEGER
成员，可以为简单变量，也可以为记录复合变量
TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table.%ROWTYPE
[INDEX BY BINARY_INTEGER];
identifier type_name;

DECLARE
TYPE t1 IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE t2 IS TABLE OF DATE INDEX BY BINARY_INTEGER;
v1 t1;
v2 t2;

BEGIN
v1(1) := 'CAMERON';
v2(8) := SYSDATE + 7;
select ename,hiredate into v1(7900),v2(7900) from emp where empno=7900;
dbms_output.put_line(v1(1)||' '||v1(7900));
dbms_output.put_line(v2(8)||' '||v2(7900));
END;
/

 

DECLARE
TYPE t2 IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER;
v2 t2;
n1 number(3);n2 number(3);n3 number(3);n4 number(3);n5 number(3);n6 number(3);
BEGIN
select * into v2(10) from dept where deptno=10;
select * into v2(20) from dept where deptno=20;
select * into v2(30) from dept where deptno=30;
select * into v2(40) from dept where deptno=40;
n1:=v2.first;--第一号元素
n2:=v2.last;--最后一个元素
n3:=v2.count;--共有多少个元素
n4:=v2.PRIOR(20);--20 号的前一个为几号
n5:=v2.NEXT(20);--20 号的后一个为几号
dbms_output.put_line(n1||'。。。'||n2||'。。。'||n3||'。。。'||n4||'。。。'||n5);
v2.delete(30);--将 30 号删除，不写几号为都删除
n6:=v2.count;
dbms_output.put_line(n6);
END;
/

 

DECLARE
TYPE t1 IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE t2 IS TABLE OF DATE INDEX BY BINARY_INTEGER;
102
v1 t1;
v2 t2;
BEGIN
v1(1) := 'CAMERON';
v2(8) := SYSDATE + 7;
select ename,hiredate into v1(7900),v2(7900) from emp where empno=7900;
for i in 1..10000 loop
if v1.exists(i) then
dbms_output.put_line('v1('||i||')= '||v1(i));
end if;
if v2.exists(i) then
dbms_output.put_line('v2('||i||')= '||v2(i));
end if;
end loop;
END;
/

成员为复合变量，每个主键访问一行数据
DECLARE
TYPE t1 IS TABLE OF emp%rowtype
INDEX BY BINARY_INTEGER;
TYPE t2 IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER;
v1 t1;
v2 t2;
BEGIN
select * into v1(7900) from emp where empno=7900;
select * into v2(10) from dept where deptno=10;
dbms_output.put_line(v1(7900).empno||v1(7900).ename);
dbms_output.put_line(v2(10).dname);
END;
/