一、PL/SQL记录
1、自定义PL/SQL记录
declare type emp_record_type is record(
name scott.emp.ename%type,
salary scott.emp.sal%type,
dno scott.emp.empno%type
--,myemp scott.emp%rowtype(可以定义,但不能整体赋值,分开使用)
);
emp_record emp_record_type;
begin
变量赋值: selecte.ename,e.sal,e.empno into emp_record fromscott.emp e where e.empno=7369;
dbms_output.put_line(emp_record.name||' '||emp_record.dno);
成员赋值: select e.enameinto emp_record.name from scott.emp e wheree.empno=7499;
dbms_output.put_line(emp_record.name);
end;
2、INSERT语句中使用PL/SQL记录
declare dept_record scott.dept%ROWTYPE;
begin
dept_record.deptno:=50;
dept_record.dname:='123';
dept_record.loc:='456';
insert into scott.deptvalues dept_record;
insert into scott.dept values(dept_record.deptno+10,dept_record.dname,dept_record.loc);
end;
3、UPDATE语句使用PL/SQL记录
declare dept_record scott.dept%ROWTYPE;
begin
dept_record.deptno:=30;
dept_record.dname:='123';
dept_record.loc:='456';
update scott.dept setROW=dept_record wherescott.dept.deptno=dept_record.deptno;
update scott.dept set scott.dept.dname=dept_record.dname wherescott.dept.deptno=40;
end;
4、DELETE语句使用PL/SQL记录
注: 只能在DELETE语句的WHERE子句中使用记录成员。
二、PL/SQL集合(处理单列多行数据)
1、索引表( 元素连续,下标不一定连续)
特点: 元素个数没有限制,并且下标可以为负值。只能作为PL/SQL复合数据类型使用,而不能作为表列的数据类型使用
语法:
TYPE type_name IS TABLE OF 索引表元素类型
[NOT NULL] INDEX BY索引表元素下标数据类型(binary_integer、pls_integer、varchar2< 9i+=>);
索引表变量 type_name;
1.1、binary_integer、pls_integer下标:
declare type deptno_table_type is table of scott.dept.dname%typeindex by binary_integer;
deptno_table deptno_table_type;
begin
select dname into deptno_table(-5) from scott.dept wherescott.dept.deptno=30;
select dname into deptno_table(0) from scott.dept wherescott.dept.deptno=40;
dbms_output.put_line(deptno_table(-5)||' '||deptno_table(0));
end;
1.2、varchar2下标:
declare type deptno_table_type is table of NUMBER index by varchar2(50);
deptno_table deptno_table_type;
begin
deptno_table('南昌'):=1;
deptno_table('赣州'):=2;
deptno_table('宜春'):=3;
dbms_output.put_line(deptno_table('南昌'));
dbms_output.put_line(deptno_table.first||' '||deptno_table.last);
end;
注:当元素的下标为字符串时,first和last元素以26个英文字母顺序排序。
2、嵌套表( 下标连续,delete方法只能删除元素值,元素下标还在,可以为该下标赋新值)
特点: 元素下标从1开始,并且元素个数没有限制。
语法:
TYPE type_name IS TABLE OF element_type identifier type_name;
2.1、在PL/SQL块中使用嵌套表
declare type ename_table_type is table ofscott.emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('初始化');
select ename into ename_table(1) from scott.emp whereempno=7369;
dbms_output.put_line(ename_table(1));
end;
使用嵌套表变量时,必须首先使用构造方法初始化嵌套表变量。
2.2、在表列中是用嵌套表
create type phone_type is table of varchar2(50);
create table employee(
id number(11,0) not null,name varchar2(10),sal number(6,2),phonephone_type
) NESTED TABLE phone STORE ASphone_table;(指定嵌套表列或属性的表名称)
插入数据:
insert into employeevalues(1,'as',800,phone_type('123132131321','sdfsdfsdfsdf','sdfsdfsdfsdfsdfsdf'));
检索数据:
检索嵌套表数据列时,需要定义嵌套表类型的变量接收其数据。
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;
begin
phone_table:=phone_type('789634','464646311','13135436466','4654646544','123213213');
update employee set phone=phone_table where id=1;
end;
3、变长数组(VARRAY, 与嵌套表的唯一区别是VARRAY构造函数初始化不能超过size_imit设置范围)
特点: 元素下标从1开始,并且元素的最大个数是有限制的。
语法:
TYPE type_name IS VARRAY(size_limit) OF element_type[NOTNULL];
identifier type_name;
3.1、在PL/SQL块中使用VARRAY
declare type ename_table_type is varray(20) ofscott.emp.ename%type;
ename_table ename_table_type:=ename_table_type('sdfsf');
begin
select ename into ename_table(1) from scott.emp whereempno=7369;
dbms_output.put_line(ename_table(1));
end;
使用VARRAY变量时,必须首先使用构造方法初始化VARRAY变量。
3.2、在表列中使用VARRAY
create type phone_type is varray(20) of varchar2(20);
create table employee(
id number(11,0) not null,name varchar2(10),sal number(6,2),phonephone_type
);
增删改和嵌套表基本一致,但VARRAY固定了元素个数。
4、PL/SQL记录表(多行多列数据)
declare type emp_table_type is table of scott.emp%rowtype index bybinary_integer;
emp_table emp_table_type;
begin
select * into emp_table(-1) from scott.emp where empno=7369;
dbms_output.put_line(emp_table(-1).ename||' '||emp_table(-1).empno);
end;
使用记录作为类型
declare
type emp_record_type is record(
record_empno scott.emp.empno%type,
record_ename scott.emp.ename%type,
record_job scott.emp.job%type,
record_mgr scott.emp.mgr%type,
record_hiredate scott.emp.hiredate%type
);
type emp_table_type is table of emp_record_typeindex by binary_integer;
emp_table emp_table_type;
c number(2);
begin
select count(*) into c from scott.emp;
for i in 1..c loop
select t2.empno,t2.ename,t2.job,t2.mgr,t2.hiredate intoemp_table(i) from (select rownum r,t1.* from scott.emp t1)t2 where r=i;
end loop;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).record_ename);
end loop;
end;
三、集合方法
集合方法是Oracle所提供的用于操作集合变量的内置对象或过程,其中EXISTS,COUNT,LIMIT,NEXT,PRIOR是函数,而EXTEND,TRIM,DELETE则是过程。
语法: collection_name.method_name
1、 EXISTS(索引),返回TRUE或FALSE
if notename_table.exists(1) then dbms_output.put_line('该元素不存在');end if;
2、 COUNT
返回当前集合变量中的元素总个数,如果集合元素存在数值,则统计结果会包含该元素,如果集合元素为NULL,则统计结果不会包含该元素。
dbms_output.put_line(deptno_table.count);
3、 LIMIT
用于返回集合元素的最大个数,嵌套表和索引表没有元素个数限制所以返回NULL,VARRAY返回允许最大元素个数。
dbms_output.put_line(ename_table.limit);
4、 FIRST和 LAST
FIRST返回集合变量第一个元素的下标,LAST返回最后一个元素的下标。当是字符下标时,以26个英文字母排序。
dbms_output.put_line(deptno_table.first||' '||deptno_table.last);
5、 PRIOR和 NEXT
PRIOR返回当前集合元素的前一个元素的下标,而NEXT方法则用于返回当前集合元素的后一个元素下标,没有返回null。
dbms_output.put_line(deptno_table.next('g')||''||deptno_table.PRIOR('a'));
6、 EXTEND
EXTEND用于扩展集合变量的尺寸,并为他们增加元素,该方法只适用于嵌套表和VARRAY,该方法有EXTEND,EXTEND(n),EXTEND(n,i)等三种调用格式,其中EXTEND用于为集合变量添加一个null元素,EXTEND(n)用于为集合添加n个null元素,而EXTEND(n,i)则用于为集合变量添加n个元素,元素值与第i个元素的值。
ename_table.extend(10,1);
dbms_output.put_line(ename_table.count);
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
7、 TRIM
TRIM用于从集合尾部删除元素,它有TRIM和TRIM(n)两种调用格式,TRIM是从集合最后一个元素,TRIM(n)是从集合尾部删除n个元素,该方法只适用于嵌套表和VARRAY
declare type ename_table_type is table of varchar2(50);
ename_table ename_table_type;
begin
ename_table:=ename_table_type('1','2','3','4','5','6');
ename_table.trim;
for i in1..ename_table.count loop
dbms_output.put_line(ename_table(i));
endloop;
end;
8、 DELETE
DELETE用于删除集合元素,只适用于嵌套表和索引表,该方法有DELETE,DELETE(n),DELETE(m,n)三种格式。DELETE删除集合中所有元素,DELETE(n)删除集合中第n个元素,DELETE(m,n)用于删除下标从m到n之间的所有元素。(包含m和n)
declare type ename_table_type is table of varchar2(50) index bybinary_integer;
ename_table ename_table_type;
begin
ename_table(1):='a';
ename_table(4):='b';
ename_table(7):='c';
ename_table(-1):='d';
ename_table(-5):='e';
ename_table(-3):='f';
ename_table.delete(-3,4);
dbms_output.put_line(ename_table.count);
end;
只能删除嵌套表元素值,元素下标还在,可以为该下标赋新值。
1、自定义PL/SQL记录
declare type emp_record_type is record(
name scott.emp.ename%type,
salary scott.emp.sal%type,
dno scott.emp.empno%type
--,myemp scott.emp%rowtype(可以定义,但不能整体赋值,分开使用)
);
emp_record emp_record_type;
begin
变量赋值: selecte.ename,e.sal,e.empno into emp_record fromscott.emp e where e.empno=7369;
dbms_output.put_line(emp_record.name||' '||emp_record.dno);
成员赋值: select e.enameinto emp_record.name from scott.emp e wheree.empno=7499;
dbms_output.put_line(emp_record.name);
end;
2、INSERT语句中使用PL/SQL记录
declare dept_record scott.dept%ROWTYPE;
begin
dept_record.deptno:=50;
dept_record.dname:='123';
dept_record.loc:='456';
insert into scott.deptvalues dept_record;
insert into scott.dept values(dept_record.deptno+10,dept_record.dname,dept_record.loc);
end;
3、UPDATE语句使用PL/SQL记录
declare dept_record scott.dept%ROWTYPE;
begin
dept_record.deptno:=30;
dept_record.dname:='123';
dept_record.loc:='456';
update scott.dept setROW=dept_record wherescott.dept.deptno=dept_record.deptno;
update scott.dept set scott.dept.dname=dept_record.dname wherescott.dept.deptno=40;
end;
4、DELETE语句使用PL/SQL记录
注: 只能在DELETE语句的WHERE子句中使用记录成员。
二、PL/SQL集合(处理单列多行数据)
1、索引表( 元素连续,下标不一定连续)
特点: 元素个数没有限制,并且下标可以为负值。只能作为PL/SQL复合数据类型使用,而不能作为表列的数据类型使用
语法:
TYPE type_name IS TABLE OF 索引表元素类型
[NOT NULL] INDEX BY索引表元素下标数据类型(binary_integer、pls_integer、varchar2< 9i+=>);
索引表变量 type_name;
1.1、binary_integer、pls_integer下标:
declare type deptno_table_type is table of scott.dept.dname%typeindex by binary_integer;
deptno_table deptno_table_type;
begin
select dname into deptno_table(-5) from scott.dept wherescott.dept.deptno=30;
select dname into deptno_table(0) from scott.dept wherescott.dept.deptno=40;
dbms_output.put_line(deptno_table(-5)||' '||deptno_table(0));
end;
1.2、varchar2下标:
declare type deptno_table_type is table of NUMBER index by varchar2(50);
deptno_table deptno_table_type;
begin
deptno_table('南昌'):=1;
deptno_table('赣州'):=2;
deptno_table('宜春'):=3;
dbms_output.put_line(deptno_table('南昌'));
dbms_output.put_line(deptno_table.first||' '||deptno_table.last);
end;
注:当元素的下标为字符串时,first和last元素以26个英文字母顺序排序。
2、嵌套表( 下标连续,delete方法只能删除元素值,元素下标还在,可以为该下标赋新值)
特点: 元素下标从1开始,并且元素个数没有限制。
语法:
TYPE type_name IS TABLE OF element_type identifier type_name;
2.1、在PL/SQL块中使用嵌套表
declare type ename_table_type is table ofscott.emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('初始化');
select ename into ename_table(1) from scott.emp whereempno=7369;
dbms_output.put_line(ename_table(1));
end;
使用嵌套表变量时,必须首先使用构造方法初始化嵌套表变量。
2.2、在表列中是用嵌套表
create type phone_type is table of varchar2(50);
create table employee(
id number(11,0) not null,name varchar2(10),sal number(6,2),phonephone_type
) NESTED TABLE phone STORE ASphone_table;(指定嵌套表列或属性的表名称)
插入数据:
insert into employeevalues(1,'as',800,phone_type('123132131321','sdfsdfsdfsdf','sdfsdfsdfsdfsdfsdf'));
检索数据:
检索嵌套表数据列时,需要定义嵌套表类型的变量接收其数据。
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;
begin
phone_table:=phone_type('789634','464646311','13135436466','4654646544','123213213');
update employee set phone=phone_table where id=1;
end;
3、变长数组(VARRAY, 与嵌套表的唯一区别是VARRAY构造函数初始化不能超过size_imit设置范围)
特点: 元素下标从1开始,并且元素的最大个数是有限制的。
语法:
TYPE type_name IS VARRAY(size_limit) OF element_type[NOTNULL];
identifier type_name;
3.1、在PL/SQL块中使用VARRAY
declare type ename_table_type is varray(20) ofscott.emp.ename%type;
ename_table ename_table_type:=ename_table_type('sdfsf');
begin
select ename into ename_table(1) from scott.emp whereempno=7369;
dbms_output.put_line(ename_table(1));
end;
使用VARRAY变量时,必须首先使用构造方法初始化VARRAY变量。
3.2、在表列中使用VARRAY
create type phone_type is varray(20) of varchar2(20);
create table employee(
id number(11,0) not null,name varchar2(10),sal number(6,2),phonephone_type
);
增删改和嵌套表基本一致,但VARRAY固定了元素个数。
4、PL/SQL记录表(多行多列数据)
declare type emp_table_type is table of scott.emp%rowtype index bybinary_integer;
emp_table emp_table_type;
begin
select * into emp_table(-1) from scott.emp where empno=7369;
dbms_output.put_line(emp_table(-1).ename||' '||emp_table(-1).empno);
end;
使用记录作为类型
declare
type emp_record_type is record(
record_empno scott.emp.empno%type,
record_ename scott.emp.ename%type,
record_job scott.emp.job%type,
record_mgr scott.emp.mgr%type,
record_hiredate scott.emp.hiredate%type
);
type emp_table_type is table of emp_record_typeindex by binary_integer;
emp_table emp_table_type;
c number(2);
begin
select count(*) into c from scott.emp;
for i in 1..c loop
select t2.empno,t2.ename,t2.job,t2.mgr,t2.hiredate intoemp_table(i) from (select rownum r,t1.* from scott.emp t1)t2 where r=i;
end loop;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).record_ename);
end loop;
end;
三、集合方法
集合方法是Oracle所提供的用于操作集合变量的内置对象或过程,其中EXISTS,COUNT,LIMIT,NEXT,PRIOR是函数,而EXTEND,TRIM,DELETE则是过程。
语法: collection_name.method_name
1、 EXISTS(索引),返回TRUE或FALSE
if notename_table.exists(1) then dbms_output.put_line('该元素不存在');end if;
2、 COUNT
返回当前集合变量中的元素总个数,如果集合元素存在数值,则统计结果会包含该元素,如果集合元素为NULL,则统计结果不会包含该元素。
dbms_output.put_line(deptno_table.count);
3、 LIMIT
用于返回集合元素的最大个数,嵌套表和索引表没有元素个数限制所以返回NULL,VARRAY返回允许最大元素个数。
dbms_output.put_line(ename_table.limit);
4、 FIRST和 LAST
FIRST返回集合变量第一个元素的下标,LAST返回最后一个元素的下标。当是字符下标时,以26个英文字母排序。
dbms_output.put_line(deptno_table.first||' '||deptno_table.last);
5、 PRIOR和 NEXT
PRIOR返回当前集合元素的前一个元素的下标,而NEXT方法则用于返回当前集合元素的后一个元素下标,没有返回null。
dbms_output.put_line(deptno_table.next('g')||''||deptno_table.PRIOR('a'));
6、 EXTEND
EXTEND用于扩展集合变量的尺寸,并为他们增加元素,该方法只适用于嵌套表和VARRAY,该方法有EXTEND,EXTEND(n),EXTEND(n,i)等三种调用格式,其中EXTEND用于为集合变量添加一个null元素,EXTEND(n)用于为集合添加n个null元素,而EXTEND(n,i)则用于为集合变量添加n个元素,元素值与第i个元素的值。
ename_table.extend(10,1);
dbms_output.put_line(ename_table.count);
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
7、 TRIM
TRIM用于从集合尾部删除元素,它有TRIM和TRIM(n)两种调用格式,TRIM是从集合最后一个元素,TRIM(n)是从集合尾部删除n个元素,该方法只适用于嵌套表和VARRAY
declare type ename_table_type is table of varchar2(50);
ename_table ename_table_type;
begin
ename_table:=ename_table_type('1','2','3','4','5','6');
ename_table.trim;
for i in1..ename_table.count loop
dbms_output.put_line(ename_table(i));
endloop;
end;
8、 DELETE
DELETE用于删除集合元素,只适用于嵌套表和索引表,该方法有DELETE,DELETE(n),DELETE(m,n)三种格式。DELETE删除集合中所有元素,DELETE(n)删除集合中第n个元素,DELETE(m,n)用于删除下标从m到n之间的所有元素。(包含m和n)
declare type ename_table_type is table of varchar2(50) index bybinary_integer;
ename_table ename_table_type;
begin
ename_table(1):='a';
ename_table(4):='b';
ename_table(7):='c';
ename_table(-1):='d';
ename_table(-5):='e';
ename_table(-3):='f';
ename_table.delete(-3,4);
dbms_output.put_line(ename_table.count);
end;
只能删除嵌套表元素值,元素下标还在,可以为该下标赋新值。