--------------------------------------------------------------------------
**************************************************************************
第二部分：复合数据类型
**************************************************************************
--------------------------------------------------------------------------
---------------------------------------------------------------------------
内容1：标量变量的使用：为了处理单行单列的数据
---------------------------------------------------------------------------
-----------1.标量数据类型案例
--案例02：使用标量变量
declare
v_name varchar2(5);
v_sal number(6,2);
c_tax_rate constant number(3,2):=0.03;
v_tax_sal number(6,2);
begin
select ename, sal into v_name, v_sal
from emp where empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line ('雇员姓名:'||v_name);
dbms_output.put_line ('雇员工资:'||v_sal);
dbms_output.put_line ('所得税:'||v_tax_sal);
end;
--案例03：使用%type属性
declare
v_name emp.ename%type;
v_sal emp.sal%type;
c_tax_rate constant number(3,2):=0.03;
v_tax_sal v_sal%type;
begin
select ename, sal into v_name, v_sal
from emp where empno=&eno;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line ('雇员姓名:'||v_name);
dbms_output.put_line ('雇员工资:'||v_sal);
dbms_output.put_line ('所得税:'||v_tax_sal);
end;
/*
标量变量带来的问题？
为了处理单行单列的数据，可以使用标量变量；如果使用标量变量则需要定义多个变量接受列数据。为了简化单行多列的数据应尽量使用PL/SQL记录。
*/
---------------------------------------------------------------------------
内容2：PL/SQL记录：为了处理单行多列数据可以使用PL/SQL记录；
---------------------------------------------------------------------------
1.使用PL/SQL记录方式
--案例01：PL/SQL记录(即record)01－－常规使用
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
emp_record emp_record_type;
begin
select ename, sal, job into emp_record
from emp where empno=&eno;
dbms_output.put_line ('姓名:'||emp_record.name);
dbms_output.put_line ('工资:'||emp_record.salary);
dbms_output.put_line ('岗位:'||emp_record.title);
end;
----案例02：PL/SQL记录(即record)01－－使用变量子表
/
*可以具体指明具体的值插入到PL/SQL记录中具体的变量中
*/
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
emp_record emp_record_type;
begin
select sal,ename, job into emp_record.salary, emp_record.name, emp_record.title
from emp where empno=&eno;
dbms_output.put_line ('姓名:'||emp_record.name);
dbms_output.put_line ('工资:'||emp_record.salary);
dbms_output.put_line ('岗位:'||emp_record.title);
end;
/
2.在select into子句中使用PL/SQL记录
--案例01：在select子句中使用记录变量－－常规使用
set serverouput on
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
dno emp.deptno%type);
emp_record emp_record_type;
begin
select ename,sal, deptno into emp_record from emp where empno=&number;
dbms_output.put_line('插入的值是：' ||emp_record.name || ';' ||emp_record.salary ||';' ||emp_record.dno);
end;
--案例02：在select into子句中使用记录成员变量－－使用变量子表
set serverouput on
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
dno emp.deptno%type);
emp_record emp_record_type;
begin
select ename,deptno,sal into emp_record.name, emp_record.dno, emp_record.salary from emp where empno=&number;
dbms_output.put_line('插入的值是：' ||emp_record.name || ';' ||emp_record.salary ||';' ||emp_record.dno);
end;
3.在insert子句中使用PL/SQL记录
--案例01：在values子句中使用记录变量
/*
在values子句中使用记录变量的时候列的顺序、个数、类型必须要与记录成员的顺序、个数、类型完全匹配。
*/
create table tt01
(sid int,
sno int);
declare
tt01_record tt01%rowtype;
begin
tt01_record.sid:=&sid;
tt01_record.sno:=&sno;
insert into tt01 values tt01_record;
dbms_output.put_line('插入的数据是：' ||'SID是'||tt01_record.sid ||' ; ' ||'SNO是'||tt01_record.sno);
end;
--案例02：在values子句中使用记录成员
/*
在values子句中使用记录变量的时候列的顺序、个数、类型可以任意指定。
*/
declare
tt01_record tt01%rowtype;
begin
tt01_record.sid:=&sid;
tt01_record.sno:=&sno;
insert into tt01(sid, sno) values (tt01_record.sid,tt01_record.sno);
dbms_output.put_line('插入的数据是：' ||'SID是'||tt01_record.sid ||' ; ' ||'SNO是'||tt01_record.sno);
end;
4.在update语句中使用PL/SQL记录
--案例01：在set子句中使用记录变量
/*
在values子句中使用记录变量的时候列的顺序、个数、类型必须要与记录成员的顺序、个数、类型完全匹配。
*/
create table tt02
(sid int,
sno int);
insert into tt02 values (101,201);
insert into tt02 values (102,202);
declare
tt02_record tt02%rowtype;
begin
tt02_record.sid:=101;
tt02_record.sno:=2001;
update tt02 set row=tt02_record where sid=101; --这里使用的是set row,row是关键字
dbms_output.put_line('表tt02数据被更新！');
end;
--案例02：在set子句中使用记录变量成员
declare
tt02_record tt02%rowtype;
begin
tt02_record.sno:=2008;
update tt02 set sno=tt02_record.sno where sid=101; --这里使用的是set sno，sno是字段名
dbms_output.put_line('表tt02数据被更新！');
end;
5.在delete语句中使用PL/SQL记录
/*
在使用PL/SQL记录删除数据时只能在where子句中使用记录成员！
*/
declare
tt02_record tt02%rowtype;
begin
tt02_record.sid:=101; --这个值要和表中的数据一致
delete from tt02 where sid=tt02_record.sid;
dbms_output.put_line('数据已经删除！'||tt02_record.sid );
end;
---------------------------------------------------------------------------
内容3：PL/SQL集合：为了处理单列多行数据
---------------------------------------------------------------------------
/*
为了处理单行单列的数据，可以使用标量变量；
为了处理单行多列数据可以使用PL/SQL记录；
为了处理单列多行数据应该使用PL/SQL集合；
PL/SQL集合包括：pl/sql表(Index_by表)、varry、nested table三种类型
三者之间的异同点：
1.pl/sql表的下标可以为负值并且元素个数没有限制，不能作为表列的数据类型
2.嵌套表用于处理PL/SQL集合的数据类型，元素下标以1开始并且元素个数没有限制，可以作为表列的数据类型使用
3.varry用于处理PL/SQL集合的数据类型，元素下标以1开始并且元素个数有限制，可以作为表列的数据类型使用
*/
1.符合变量（pl/sql表即索引表）
--案例01：索引表中使用binary_integer和pls_integer
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
select ename into ename_table(-1) from emp
where empno=&no;
dbms_output.put_line('雇员名'||ename_table(-1));
select ename into ename_table(-2) from emp
where empno=&no;
dbms_output.put_line('雇员名'||ename_table(-2));
end;
/*
index_by表的下标是两位的整数，可以为正也可以为负，范围是：-2147483647--2147483647
索引表的元素个数没有限制并且下标可以为负值。索引表只能作为PL/SQL符合数据类型使用不能作为表列的数据类型使用
*/
--案例02：索引表中使用varchar2
set serveroutput on
declare
type v01_index is table of varchar2(100) index by varchar2(10);
v01_in v01_index;
begin
v01_in(1):='苏州';
v01_in(2):='南京';
v01_in(3):='乌鲁木齐';
dbms_output.put_line('第一个元素是：' ||v01_in(1));
dbms_output.put_line('第二个元素是：' ||v01_in(2));
dbms_output.put_line('第三个元素是：' ||v01_in(3));
end;
--案例03：索引表中使用varchar2
set serveroutput on
declare
type v01_index is table of number index by varchar2(10);
v01_in v01_index;
begin
v01_in('苏州'):=1;
v01_in('上海'):=2;
v01_in('乌鲁木齐'):=3;
dbms_output.put_line('第一个元素是：' ||v01_in.first);
dbms_output.put_line('第三个元素是：' ||v01_in.last);
end;
/*
因为元素的数据类型位字符串所以确定第一个和最后一个元素时是以汉语拼音格式进行排序。
*/
************************************************************************************
2. nested table嵌套表
2.1 在PL/SQL块中使用嵌套表
--案例01：在PL/SQL块中使用嵌套表
/*
在PL/SQL块中使用嵌套表变量时必须
先申明嵌套表类型；
然后使用构造方法初始化嵌套表变量；
再才能在PL/SQL块内引用嵌套表元素
*/
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('MARY','KILL','SHUT'); /*使用其构造方法初始化嵌套表，即需要几个下标的数目即格式*/
select ename into ename_table(2) from emp where empno=&no;
dbms_output.put_line('雇员名：' ||ename_table(2));
end;
--如：
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('MARY','MARY','MARY','WIND');
/*使用其构造方法初始化嵌套表，即需要几个下标的数目即格式,这里是可以填写4个字段*/
select ename into ename_table(4) from emp where empno=&no;
dbms_output.put_line('雇员名：' ||ename_table(4));
end;
--------------------------------------------------------------------------------
2.2 在表列中使用嵌套表
--案例01：
/*
如果在表列中使用嵌套类型时必须
先create type命令建立嵌套表类型；
并且当使用嵌套类型作为表列的数据类型时必须要为嵌套表列指定专门的存储表;
*/
--第一步：
/*新建类型*/
create type phone_type is table of varchar2(30);
/
--第二步:
/*新建表*/
create table emp_phone
(
sid int,
sname varchar2(10),
phone phone_type
) nested table phone store as phone_table;
--下面通过实例说明在PL/SQL块中操作嵌套表列的方法
/*
当create type命令建立嵌套类型时数据库会自动为类型生成对应的构造方法！
*/
示例1：在PL/SQL块中位嵌套表列插入值
begin
insert into emp_phone values (&id, '&name',phone_type('&self_mobile_phone','&home_phone', '&parent_phone'));
/*这里是数据库根据用户输入的值自动构造格式*/
end;
/
示例2：在PL/SQL块中检索嵌套表列的数据
/*
在PL/SQL块中检索嵌套表列的数据需要定义嵌套表类型的变量接受其数据。
*/
declare
phone_table phone_type;
begin
select phone into phone_table from emp_phone where sid=101;
for i in 1..phone_table.count loop
dbms_output.put_line('电话号码是:' ||phone_table(i));
end loop;
end;
/
示例3：在PL/SQL块中更新嵌套表列的数据
/*
在PL/SQL块中更新嵌套表列的数据时需要定义嵌套表变量;
然后使用构造方法初始化变量然后才能在可执行部分使用update语句更新其数据
*/
declare
phone_table phone_type;
begin
phone_table:=phone_type('0512-68296950','13656218596'); /*构造*/
update emp_phone set phone=phone_table where sid=101;
end;
/
--案例03：嵌套于对象
create or replace type emp_type as object(
name varchar2(10),
salary number(6,2),
hiredate date);
create or replace type emp_arry is table of emp_type;
create table department(
deptno number(2),
dname varchar2(10),
employee emp_arry)
nested table employee store as emplo; --employee数据将存储在另外一个表employee中
**********************************************************************************************
3. 变长数组类型varray
--案例01：在PL/SQL块中使用varray
/*
在PL/SQL块中使用varray变量时必须：
先申明一个varrary类型
然后使用构造的方法初始化varray变量
最后才能在PL/SQL块中使用*/
declare
type ename_table_type is varray(20) of emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('kill'); --构造
select ename into ename_table(1) from emp where empno=&no;
dbms_output.put_line('员工的名字：' ||ename_table(1));
end;
也可以写成：
declare
type ename_table_type is varray(20) of emp.ename%type;
ename_table ename_table_type:=ename_table_type('kill');
begin
select ename into ename_table(1) from emp where empno=&no;
dbms_output.put_line('员工的名字：' ||ename_table(1));
end;
--案例02：在表列中使用varray
/*
表列中使用步骤：
1.create type建立一个varray类型
2.在表中调用
3.插入值
和嵌套表不一样的是：嵌套表元素个数没有限制；而varray有个数限制；*/
步骤01：新建一个varray类型
create type stu_phone_type is varray(10) of varchar2(20);
/
步骤02:新建表调用varray类型
create table stu_phone
(
sid int,
name varchar2(10),
phone stu_phone_type
);
步骤03:插入数据
insert into stu_phone values (&id, '&name', stu_phone_type('&fir','&secon'));
insert into stu_phone values (&id, '&name', stu_phone_type('&fir','&secon', '&thir'));
--这里stu_phone_type中的值只要不超过10个都是可以插入的！
--案例03：变长数组类型varray01
--步骤01：新建
create type tool as object
(toolname varchar2(25));
/
create or replace type tools_va as varray(5) of varchar2(25);
/
create table borrower
(
name varchar2(10),
tools tools_va,
constraint pk01 primary key(name)
);
--步骤2：向表中插入值、
insert into borrower values ('wind', tools_va('j01', 'j02', 'j03'));
insert into borrower values ('snow', tools_va('mary', 'lucy', 'apple'));
insert into borrower values ('snow', tools_va('baby', 'coll', 'star', 'shell','mery', 'snow'));
--查看是否报错
ERROR 位于第 1 行:
ORA-22909: 超出最大的 VARRAY 限制
insert into borrower values ('snow', tools_va('null', 'lucy', 'apple')); --违反唯一约束
--查看数据
select * from borrower; --结果不会显示objects内部的内容
select b.name, n.* from borrower b , table(b.tools) n;
--案例04：变长数组类型varray02
create type article_type as object(
title varchar2(30),
pubdate date
);
create type article_array is varray(20) of article_type;
create table author
(id number(6),
name varchar2(10),
article article_array
);
-------------------3.参照变量(rf)------------------------------------
--案例01：使用REF CURSOR
declare
type c1 is ref cursor; --申明游标类型
dyn_cursor c1; --游标变量
col1 varchar(20);
col2 varchar(20);
begin
open dyn_cursor for select &col1, &col2 from &tab where &con;
fetch dyn_cursor into col1,col2;
dbms_output.put_line('col1: '||col1);
dbms_output.put_line('col2: '||col2);
close dyn_cursor;
end;
