PL/SQL-2 复合数据类型
--------------------------------------------------------------------------
**************************************************************************
第二部分：復(fù)合數(shù)據(jù)類型
**************************************************************************
--------------------------------------------------------------------------
---------------------------------------------------------------------------
內(nèi)容1：標(biāo)量變量的使用：為了處理單行單列的數(shù)據(jù)
---------------------------------------------------------------------------
-----------1.標(biāo)量數(shù)據(jù)類型案例
--案例02：使用標(biāo)量變量
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;
/*
標(biāo)量變量帶來的問題？
為了處理單行單列的數(shù)據(jù)，可以使用標(biāo)量變量；如果使用標(biāo)量變量則需要定義多個變量接受列數(shù)據(jù)。為了簡化單行多列的數(shù)據(jù)應(yīng)盡量使用PL/SQL記錄。
*/
?
---------------------------------------------------------------------------
內(nèi)容2：PL/SQL記錄：為了處理單行多列數(shù)據(jù)可以使用PL/SQL記錄；
---------------------------------------------------------------------------
1.使用PL/SQL記錄方式
--案例01：PL/SQL記錄(即record)01－－常規(guī)使用
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子句中使用記錄變量－－常規(guī)使用
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子句中使用記錄變量的時候列的順序、個數(shù)、類型必須要與記錄成員的順序、個數(shù)、類型完全匹配。
*/
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('插入的數(shù)據(jù)是：' ||'SID是'||tt01_record.sid ||' ; ' ||'SNO是'||tt01_record.sno);
end;
?
--案例02：在values子句中使用記錄成員
/*
在values子句中使用記錄變量的時候列的順序、個數(shù)、類型可以任意指定。
*/
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('插入的數(shù)據(jù)是：' ||'SID是'||tt01_record.sid ||' ; ' ||'SNO是'||tt01_record.sno);
end;
4.在update語句中使用PL/SQL記錄
--案例01：在set子句中使用記錄變量
/*
在values子句中使用記錄變量的時候列的順序、個數(shù)、類型必須要與記錄成員的順序、個數(shù)、類型完全匹配。
*/
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是關(guān)鍵字
?dbms_output.put_line('表tt02數(shù)據(jù)被更新！');
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數(shù)據(jù)被更新！');
end;
5.在delete語句中使用PL/SQL記錄
/*
在使用PL/SQL記錄刪除數(shù)據(jù)時只能在where子句中使用記錄成員！
*/
declare
? tt02_record tt02%rowtype;
?begin
?? tt02_record.sid:=101;? --這個值要和表中的數(shù)據(jù)一致
?? delete from tt02 where sid=tt02_record.sid;
?dbms_output.put_line('數(shù)據(jù)已經(jīng)刪除！'||tt02_record.sid );
?end;
?
?
---------------------------------------------------------------------------
內(nèi)容3：PL/SQL集合：為了處理單列多行數(shù)據(jù)
---------------------------------------------------------------------------
/*
為了處理單行單列的數(shù)據(jù)，可以使用標(biāo)量變量；
為了處理單行多列數(shù)據(jù)可以使用PL/SQL記錄；
為了處理單列多行數(shù)據(jù)應(yīng)該使用PL/SQL集合；
?
PL/SQL集合包括：pl/sql表(Index_by表)、varry、nested table三種類型
三者之間的異同點(diǎn)：
1.pl/sql表的下標(biāo)可以為負(fù)值并且元素個數(shù)沒有限制，不能作為表列的數(shù)據(jù)類型
2.嵌套表用于處理PL/SQL集合的數(shù)據(jù)類型，元素下標(biāo)以1開始并且元素個數(shù)沒有限制，可以作為表列的數(shù)據(jù)類型使用
3.varry用于處理PL/SQL集合的數(shù)據(jù)類型，元素下標(biāo)以1開始并且元素個數(shù)有限制，可以作為表列的數(shù)據(jù)類型使用
*/
?
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表的下標(biāo)是兩位的整數(shù)，可以為正也可以為負(fù)，范圍是：-2147483647--2147483647
索引表的元素個數(shù)沒有限制并且下標(biāo)可以為負(fù)值。索引表只能作為PL/SQL符合數(shù)據(jù)類型使用不能作為表列的數(shù)據(jù)類型使用
*/
--案例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;
/*
因為元素的數(shù)據(jù)類型位字符串所以確定第一個和最后一個元素時是以漢語拼音格式進(jìn)行排序。
*/
************************************************************************************
?
2. nested table嵌套表
2.1 在PL/SQL塊中使用嵌套表
--案例01：在PL/SQL塊中使用嵌套表
/*
在PL/SQL塊中使用嵌套表變量時必須
先申明嵌套表類型；
然后使用構(gòu)造方法初始化嵌套表變量；
再才能在PL/SQL塊內(nèi)引用嵌套表元素
*/
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');? /*使用其構(gòu)造方法初始化嵌套表，即需要幾個下標(biāo)的數(shù)目即格式*/
?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');
/*使用其構(gòu)造方法初始化嵌套表，即需要幾個下標(biāo)的數(shù)目即格式,這里是可以填寫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命令建立嵌套表類型；
并且當(dāng)使用嵌套類型作為表列的數(shù)據(jù)類型時必須要為嵌套表列指定專門的存儲表;
*/
--第一步：
/*新建類型*/
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塊中操作嵌套表列的方法
/*
當(dāng)create type命令建立嵌套類型時數(shù)據(jù)庫會自動為類型生成對應(yīng)的構(gòu)造方法！
*/
示例1：在PL/SQL塊中位嵌套表列插入值
begin
insert into emp_phone values (&id, '&name',phone_type('&self_mobile_phone','&home_phone', '&parent_phone'));
/*這里是數(shù)據(jù)庫根據(jù)用戶輸入的值自動構(gòu)造格式*/
end;
/
?
示例2：在PL/SQL塊中檢索嵌套表列的數(shù)據(jù)
/*
在PL/SQL塊中檢索嵌套表列的數(shù)據(jù)需要定義嵌套表類型的變量接受其數(shù)據(jù)。
*/
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塊中更新嵌套表列的數(shù)據(jù)
/*
在PL/SQL塊中更新嵌套表列的數(shù)據(jù)時需要定義嵌套表變量;
然后使用構(gòu)造方法初始化變量然后才能在可執(zhí)行部分使用update語句更新其數(shù)據(jù)
*/
declare
phone_table phone_type;
begin
phone_table:=phone_type('0512-68296950','13656218596'); /*構(gòu)造*/
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數(shù)據(jù)將存儲在另外一個表employee中
**********************************************************************************************
3. 變長數(shù)組類型varray
--案例01：在PL/SQL塊中使用varray
/*
在PL/SQL塊中使用varray變量時必須：
先申明一個varrary類型
然后使用構(gòu)造的方法初始化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'); --構(gòu)造
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.在表中調(diào)用
3.插入值
和嵌套表不一樣的是：嵌套表元素個數(shù)沒有限制；而varray有個數(shù)限制；*/
步驟01：新建一個varray類型
create type stu_phone_type is varray(10) of varchar2(20);
/
步驟02:新建表調(diào)用varray類型
create table stu_phone
(
sid int,
name varchar2(10),
phone stu_phone_type
);
步驟03:插入數(shù)據(jù)
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：變長數(shù)組類型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'));? --違反唯一約束
--查看數(shù)據(jù)
select * from borrower; --結(jié)果不會顯示objects內(nèi)部的內(nèi)容
select b.name, n.* from borrower b , table(b.tools) n;
?
--案例04：變長數(shù)組類型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;? --申明游標(biāo)類型
dyn_cursor c1;? --游標(biāo)變量
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;
轉(zhuǎn)載于:https://www.cnblogs.com/javatec03/archive/2012/03/09/2404859.html
總結(jié)
以上是生活随笔為你收集整理的PL/SQL-2 复合数据类型的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 一次性删除数据库中某库的所有存储过程的方
- 下一篇： C# 结构体 简明介绍