declare type emp_record_type is record( name scott.emp.ename%type, salary scott.emp.sal%type, dno scott.emp.deptno%type ); emp_record emp_record_type; begin select ename,sal,deptno into emp_record from scott.emp where empno=&no; dbms_output.put_line(emp_record.name); end; declare type emp_record_type is record( name scott.emp.ename%type, salary scott.emp.sal%type, dno scott.emp.deptno%type ); emp_record emp_record_type; begin select ename,sal,deptno into emp_record.name,emp_record.salary,emp_record.dno from scott.emp where empno=&no; dbms_output.put_line(emp_record.name); end; declare dept_record scott.dept%rowtype; begin dept_record.deptno:=1; dept_record.dname:='administrator'; dept_record.loc:='NC'; insert into scott.dept values dept_record; end; declare dept_record scott.dept%rowtype; begin dept_record.deptno:=2; dept_record.dname:='administrator'; insert into scott.dept(deptno,dname) values(dept_record.deptno,dept_record.dname); end; declare dept_record scott.dept%rowtype; begin dept_record.deptno:=1; dept_record.dname:='HZY'; update scott.dept set row=dept_record where deptno=dept_record.deptno; end; declare dept_record scott.dept%rowtype; begin dept_record.deptno:=1; delete from scott.dept where deptno=dept_record.deptno; end; declare type ename_table_type is table of scott.emp.ename%type index by binary_integer; ename_table ename_table_type; begin select ename into ename_table(-1) from scott.emp where empno=&no; dbms_output.put_line('雇员名:'||ename_table(-1)); end; declare type area_table_type is table of number index by varchar2(10); area_table area_table_type; begin area_table('北京'):=3; area_table('上海'):=2; area_table('广州'):=1; dbms_output.put_line(area_table('北京')); dbms_output.put_line(area_table.last); end; declare type ename_table_type is table of scott.emp.ename%type; ename_table ename_table_type; begin ename_table:=ename_table_type('MARY','a','MARY'); select ename into ename_table(2) from scott.emp where empno=&no; --for i in 1..ename_table.count loop dbms_output.put_line('雇员名:'||ename_table(2)); --end loop; end; create type phone_type is table of varchar2(20); create table employee( id number(4),name varchar2(10),sal number(6,2), phone phone_type )nested table phone store as phone_table; begin insert into employee values(1,'scott',800,phone_type('0471-3456788','13804711111')); end; declare phone_table phone_type; begin select phone into phone_table from employee where id=1; for i in 1..phone_table.count loop dbms_output.put_line('电话号码:'||phone_table(i)); end loop; end; declare phone_table phone_type:=phone_type('0471-3456788','13804711111','0471-2233066','13056278568'); begin update employee set phone=phone_table where id=1; end; declare type ename_table_type is varray(20) of scott.emp.ename%type; ename_table ename_table_type:=ename_table_type('A','B','Mary'); begin select ename into ename_table(1) from scott.emp where empno=&no; -- for i in 1..ename_table.count loop dbms_output.put_line('雇员名:'||ename_table(1)); -- end loop; end; create type phone_type is varray(20) of varchar2(20); create table employee( id number(4),name varchar2(10),sal number(6,2), phone phone_type ); begin insert into employee values(1,'SCOTT',800,phone_type('0471-3456788','13804711111')); end; declare phone_table phone_type; begin select phone into phone_table from employee where id=1; for i in 1..phone_table.count loop dbms_output.put_line(phone_table(i)); end loop; end; declare type emp_table_type is table of scott.emp%rowtype index by binary_integer; emp_table emp_table_type; begin select * into emp_table(1) from scott.emp where empno=&no; dbms_output.put_line('雇员姓名:'||emp_table(1).ename); end; declare type ename_table_type is table of scott.emp.ename%type; ename_table ename_table_type:=ename_table_type('aa'); begin if ename_table.exists(1) then ename_table(1):='SCOTT'; dbms_output.put_line(ename_table(1)); else dbms_output.put_line('必须初始化集合元素'); end if; end; declare type ename_table_type is table of scott.emp.ename%type index by binary_integer; ename_table ename_table_type; begin ename_table(-5):='SCOTT'; ename_table(-2):='aa'; ename_table(1):='bb'; ename_table(0):='dd'; dbms_output.put_line('集合元素总个数:'||ename_table.count); dbms_output.put_line('第一个元素:'||ename_table.first); dbms_output.put_line('最后一个元素:'||ename_table.last); dbms_output.put_line('元素0的前一个元素:'||ename_table.prior(0)); dbms_output.put_line('元素-2的后一个元素:'||ename_table.next(-2)); end; declare type ename_table_type is varray(20) of varchar2(10); ename_table ename_table_type; begin ename_table:=ename_table_type('Mary'); ename_table.extend(5); dbms_output.put_line('元素总个数:'||ename_table.count); for i in 1..ename_table.count loop dbms_output.put_line(ename_table(i)); end loop; end; declare type ename_table_type is table of varchar2(10); ename_table ename_table_type; begin ename_table:=ename_table_type('A','B','C','D','E'); ename_table.trim; dbms_output.put_line('元素总个数:'||ename_table.count); end; declare type ename_table_type is table of scott.emp.ename%type index by binary_integer; ename_table ename_table_type; begin ename_table(-5):='SCOTT'; ename_table(-2):='aa'; ename_table(1):='bb'; ename_table(0):='dd'; ename_table.delete(-2); dbms_output.put_line('元素总个数:'||ename_table.count); end; select * from scott.dept; select ename,sal,deptno from scott.emp; select * from employee;