`
java-mans
  • 浏览: 11344216 次
文章分类
社区版块
存档分类
最新评论

PL/SQL-2 复合数据类型

 
阅读更多

--------------------------------------------------------------------------
**************************************************************************
第二部分:复合数据类型
**************************************************************************
--------------------------------------------------------------------------

---------------------------------------------------------------------------
内容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;

分享到:
评论

相关推荐

    Oracle PL/SQL程序设计(第5版)(下册)第二部分

    探讨了数据类型、条件控制语句和顺序控制语句、循环、异常处理、安全特性、全球化和本地化问题, 以及PL/SQL架构。 ? 通过使用过程、函数、触发器和包,建立模块化的PL/SQL应用。 内容推荐 《Oracle PL/SQL程序...

    PL/SQL课件

    PL/SQL简介(第1~2章) PL/SQL中的SQL语句(第3章) ...复合数据类型(第15~16章) 动态SQL(第17章) 批量SQL(第18章) 子程序和包(第19~22章) Oracle中对象类型(第23章) Oracle提供的包(第24章)

    Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(二)

     第14章 使用复合数据类型  第15章 使用游标  第16章 异常处理 . 第17章 本地动态sql  第18章 pl/sql过程  第19章 pl/sql函数  第20章 pl/sql包  第21章 触发器  第22章 使用对象类型 第四部分 pl/sql系统...

    Oracle PL/SQL程序设计(第5版)(下册) 第一部分

    探讨了数据类型、条件控制语句和顺序控制语句、循环、异常处理、安全特性、全球化和本地化问题, 以及PL/SQL架构。 ? 通过使用过程、函数、触发器和包,建立模块化的PL/SQL应用。 内容推荐 《Oracle PL/SQL程序...

    Oracle PL/SQL语言初级教程

    2.PL/SQL的复合数据类型 17 使用记录 19 PL/SQL集合 23 嵌套表 24 使用集合 25 集合的方法 28 关于集合之间的比较 29 3.PL/SQL单行函数和组函数详解 29 单行字符串函数 30 单行转换函数 37 SQL中的组函数 40 嵌套...

    Oracle 11g SQL和PL SQL从入门到精通〖送源代码〗

    读者不仅可以掌握Oracle常用工具Oracle Universal Installer、Net Comfiguration Assistant、SQL Developer、SQL*Plus的作用及使用方法,而且可以掌握SQL语句和PL/SQL的各种基础知识和高级特征(记录类型、集合类型...

    Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(一)

     第14章 使用复合数据类型  第15章 使用游标  第16章 异常处理 . 第17章 本地动态sql  第18章 pl/sql过程  第19章 pl/sql函数  第20章 pl/sql包  第21章 触发器  第22章 使用对象类型 第四部分 pl/sql系统...

    PL/SQL 基础.doc

    PL/SQL 基础,一个不错的 PL/SQL 参考手册。内容预览: ---- 第一章 PL/SQL 简介 ---- 1. Oracle应用编辑方法概览 1) Pro*C/C++/... : C语言和数据库打交道的方法,比OCI更常用; 2) ODBC 3) OCI: C语言和...

    全面探讨PL SQL的复合数据类型

    全面探讨PL SQL的复合数据类型,深入了解pl sql的复合数据类型。

    pl/sql笔记/sql笔记(游标创建存储过程)

    Pl/SQL简介 控制结构 复合数据类型 游标 异常 创建存储过程 函数的概念 包的概念 触发器概念

    Oracle PL_SQL语言初级教程

    • 复合数据类型(2) • 复合数据类型(3) • 复合数据类型(4) • 复合数据类型(5) 单行函数和组函数 函数是一种有零个或多个参数并且有一个返回值的程序。在 SQL 中 Oracle 内建了一系列函数,这些函数 都可被称为 ...

    Oracle 11g SQL和PL SQL从入门到精通.part1

     第14章 使用复合数据类型  第15章 使用游标  第16章 异常处理 . 第17章 本地动态sql  第18章 pl/sql过程  第19章 pl/sql函数  第20章 pl/sql包  第21章 触发器  第22章 使用对象类型 第四部分 pl/sql系统...

    Oracle 11g SQL和PL SQL从入门到精通part2 pdf格式电子书 下载(二)

     第14章 使用复合数据类型  第15章 使用游标  第16章 异常处理 . 第17章 本地动态sql  第18章 pl/sql过程  第19章 pl/sql函数  第20章 pl/sql包  第21章 触发器  第22章 使用对象类型 第四部分 pl/sql系统...

    oracle实用教程-韩顺平

    4.oracle 表的管理(数据类型,表创建删除,数据 CRUD 操作) 5.oracle 表查询(1) 6.oracle 表查询(2) 7.java 操作 oracle 8.oracle 中事务处理 9.oracle 的函数 10.数据库管理,表的逻辑备份与恢复 11.数据字典和...

    精通Oracle.10g.PLSQL编程

    编写控制结构 7.1 条件分支语句 7.2 CASE语句 7.3 循环语句 7.4 顺序控制语句 7.5 习题 第8章 使用复合数据类型 8.1 PL/SQL记录 8.1.1 定义PL/SQL记录 8.1.2 使用PL/SQL...

    精通Oracle.10g.Pl.SQL编程

    本书包括:PL/SQL 综述、PL/SQL 开发工具、PL/SQL 基础、使用SQL语句、SQL函数、访问Oracle 、编写控制结构、使用复合数据类型、使用游标、处理例外、开发子程序等内容。 作者:王海亮 出版社:中国水利水电出版社

    深入ORACLE变量的定义与使用的详解

    在程序中定义变量、常量和参数时,则必须要为它们指定PL/SQL数据类型。在编写PL/SQL程序时,可以使用标量(Scalar)类型、复合(Composite)类型、参照(Reference)类型和LOB(LargeObject)类型等四种类型。在PL/...

    PLSQL基础教程

    §2.4.7 数据类型的转换* 13 §2.5 运算符和表达式(数据定义) 13 §2.5.1 关系运算符 13 §2.5.2 一般运算符 13 §2.5.3 逻辑运算符 13 §2.6 变量赋值 13 §2.6.1 字符及数字运算特点 13 §2.6.2 BOOLEAN ...

    PLSQL程序设计

    §2.4.7 数据类型的转换* 13 §2.5 运算符和表达式(数据定义) 13 §2.5.1 关系运算符 13 §2.5.2 一般运算符 13 §2.5.3 逻辑运算符 13 §2.6 变量赋值 13 §2.6.1 字符及数字运算特点 13 §2.6.2 BOOLEAN 赋值 13 ...

    oracle学习资料

    §2.4.7 数据类型的转换* 13 §2.5 运算符和表达式(数据定义) 13 §2.5.1 关系运算符 13 §2.5.2 一般运算符 13 §2.5.3 逻辑运算符 13 §2.6 变量赋值 13 §2.6.1 字符及数字运算特点 13 §2.6.2 BOOLEAN 赋值 13 ...

Global site tag (gtag.js) - Google Analytics