PL/SQL定义部分之二复合数据类型

转载 2012年03月29日 14:29:56

本文详细介绍PL/SQL定义部分之二复合数据类型

  复合类型  复合类型可以存储多个值,包括记录和集合。集合是一维的,但是可以在集合中放入其它集合,变成多维集合。集合又分为索引表、嵌套表和变长数组三种。要使用集合,我们首先要创建集合类型,然后声明该类型的变量。我们可以在任何PL/SQL块、子程序或包的声明部分使用TABLE和VARRAY关键字来声明集合类型。集合的作用域和同其他类型变量一样,在一个块或子程序中,当程序进入块或子程序时有效,退出时失效。在包中,集合在我们第一次引用包的时候有效,直至会话终止时才失效。

  一、记录   

   记录可以包括多个成员,从而可以保存多个值。记录中的成员的数据类型可以不同。记录在使用时必须先声明记录类型,然后定义记录变量,最后才能在PL/SQL中使用。

  示例1:

DECLARE
 TYPE MyRecord IS RECORD(
 name VARCHAR2(10),
 age  SMALLINT
 );
 mr MyRecord;
 BEGIN
 mr.name:='zhangsan';
 mr.age:=20;
 DBMS_OUTPUT.PUT_LINE('姓名:'||mr.name||'    年龄:'||mr.age);
 END;

  示例2: DECLARE
TYPE MyRecord IS RECORD(
  no    NUMBER(2),
  name  VARCHAR2(14),
  city  scott.dept.loc%TYPE
);
deptRecord MyRecord ;
BEGIN
--SELECT 语句后列的数目与记录中的变量数目相同
SELECT deptno,dname,loc into deptRecord FROM DEPT WHERE DEPTNO=30;
DBMS_OUTPUT.PUT_LINE(deptRecord.no||':'||deptRecord.name||':'||deptRecord.city);
END;

关键字: 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

  示例3:

DECLARE
TYPE MyRecord IS RECORD(
  no    NUMBER(2),
  name  VARCHAR2(14),
  city  scott.dept.loc%TYPE
);
deptRecord MyRecord;
BEGIN
--SELECT 语句后列的数目少于记录中的变量数目
SELECT dname,loc INTO deptRecord.name,deptRecord.city FROM DEPT WHERE DEPTNO=30;
deptRecord.no:=30;
DBMS_OUTPUT.PUT_LINE(deptRecord.no||':'||deptRecord.name||':'||deptRecord.city);
END;

  示例4:

  DECLARE

  --特殊定义记录方式,记录中变量的类型与dept表行的类型相同

deptRecord dept%ROWTYPE;
BEGIN
SELECT * INTO deptRecord FROM DEPT WHERE DEPTNO=30;
DBMS_OUTPUT.PUT_LINE(deptRecord.deptNo||':'||deptRecord.dname||':'||deptRecord.loc);
END;

  二、索引表(关联数组)

   索引表就是键值集合,键是唯一的,用于查找对应的值。键可以是整数或字符串。第一次使用键来指派一个对应的值就是添加元素,而后续这样的操作就是更新元素。

DECLARE
TYPE MyTableTypeName IS TABLE OF ElementType [NOT NULL
INDEX BY Key_Type;
myName MyTableTypeName;

  其中:MyTableTypeName是表类型的名称,ElementType是元素的数据类型,NOT NULL表示不允许引用NULL值,Key_Type是下标的数据类型,BINARY_INTEGER PLS_INTEGER或VARCHAR2(Oracle9i及以后版本可用)。myName是表类型变量名。

  示例1

DECLARE
TYPE MyTabelType IS TABLE OF dept.dname%TYPE
NOT NULL INDEX BY BINARY_INTEGER;
my MyTabelType;
BEGIN
SELECT DNAME INTO my(-3) FROM DEPT WHERE  deptno=20;
SELECT DNAME INTO my(-1) FROM DEPT WHERE  deptno=40;
DBMS_OUTPUT.PUT_LINE('my(-3):'||my(-3));
DBMS_OUTPUT.PUT_LINE('my(-1):'||my(-1));
END;

关键字: 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

  示例2:

DECLARE
TYPE MyTableType IS TABLE OF NUMBER
NOT NULL INDEX BY VARCHAR2(20);
my MyTableType;
BEGIN
  my('China'):=1;
  my('Japan'):=2;
  my('USA'):=3;
  DBMS_OUTPUT.PUT_LINE(my.first);
  DBMS_OUTPUT.PUT_LINE(my.last);
  DBMS_OUTPUT.PUT_LINE(my('China'));
 DBMS_OUTPUT.PUT_LINE(my(my.last));
END;

  三、嵌套表

   要使用嵌套表,首先要创建嵌套表类型,其次声明嵌套表类型变量,然后初始化嵌套表变量,最后引用嵌套表变量元素值。

  1、定义嵌套表类型  

TYPE MyNestedTableType IS TABLE OF ElementType [NOT NULL];

  其中:

  MyNestedTableType是嵌套表类型的名称。ElementType是嵌套表元素的类型,它是可以除了REF CURSOR类型之外的任何PL/SQL类型,但是对于全局嵌套表(CREATE TYPE创建)来说,以下类型也是不允许的:BINARY_INTEGER  PLS_INTEGER  BOOLEAN  LONG  LONG RAW  NATURAL   NATURALN POSITIVE  POSITIVEN  REF CURSOR  SIGNTYPE  STRING 。NOT NULL表示元素值不能为NULL。

  

  示例:

DECLARE
--定义记录
TYPE DeptRecord IS RECORD(
  no    NUMBER(2),
  name  VARCHAR2(14),
  city  scott.dept.loc%TYPE
);
--定义游标
CURSOR  DeptCursor IS SELECT * FROM DEPT;
--定义嵌套表元素类型时使用%TYPE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
--定义嵌套表元素类型时使用表的%ROWTYPE
TYPE DeptList1  IS TABLE OF DEPT%ROWTYPE;
--定义嵌套表元素类型时使用游标的%ROWTYPE
TYPE DeptList2  IS TABLE OF DeptCursor%ROWTYPE;
--定义嵌套表元素类型时使用记录类型
TYPE DeptList3  IS TABLE OF DeptRecord ;
--在SQL语句中定义嵌套表类型
CREATE TYPE PhoneList IS TABLE OF VARCHAR2(10);

关键字: 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

  2、声明嵌套表类型变量

  示例:

DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
nameList1 DNameList;  
nameList2 nameList1%TYPE;   --使用%TYPE声明嵌套表类型变量 
--在存储过程参数中定义嵌套表类型变量
CREATE OR REPLACE PROCEDURE proc_name(nameList  IN DNameList)

  注意:不能将嵌套表类型和嵌套表类型变量同名,大小写不同也不可,因PL/SQL不区分大小写

  3、初始化嵌套表类型变量

   嵌套表必须先通过构造方法初始化(索引表不需要通过构造方法进行初始化),否则嵌套表变量为NULL,在初始化中元素个数没有限制。

  示例:

DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
my DNameList ;
BEGIN
my:=DNameList('CORESUN','CORESUN','CORESUN'); 

  注意:my变量在初始化时传入了3个参数,那么嵌套表长度就是3,以后使用时长度不够,必须通过extend函数进行扩展长度。如果在构造方法中没有传入任何参数,则嵌套表长度为0。

  也可以在声明声明嵌套表类型变量的同时进行初始化,如:

my DNameList:=DNameList('CORESUN','CORESUN','CORESUN');

  如果定义元素类型时没有指定NOT NULL,在构造方法中也可以传入NULL,如:

my:=DNameList('CORESUN',NULL,'CORESUN');

  4、引用嵌套表类型变量

   通过嵌套表变量和圆括号里的索引值来引用嵌套表中的元素,嵌套表的下标是从1开始编号,最大至231 ,下标可以是一个整数,也可以是整数表达式。

关键字: 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

  示例:

DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
my DNameList ;
BEGIN
my:=DNameList(NULL,'CORESUN','CORESUN');  
SELECT DNAME INTO my(2) FROM dept where deptno=20;
my(1):='CORESUN';
--my(4):='CORESUN';
DBMS_OUTPUT.PUT_LINE(my(1));
DBMS_OUTPUT.PUT_LINE(my(2));
END;

  四、嵌套表应用示例

  示例1:创建记录类型的嵌套表

DECLARE
TYPE DeptRecord IS RECORD(
  no    NUMBER(2),
  name  VARCHAR2(14),
  city  scott.dept.loc%TYPE
);
TYPE DeptRecordList IS TABLE OF DeptRecord;

  示例2:嵌套表用在另一个表的内部

  1、创建全局嵌套表类型

  --当嵌套表作为表列的类型时,要先创建,这样的嵌套表我们称为全局嵌套表

CREATE OR REPLACE TYPE PHONE_TYPE IS TABLE OF VARCHAR2(20);
/
CREATE TABLE person
(
  id NUMBER,
  name VARCHAR2(10),
  sal NUMBER(6,2),
  phone PHONE_TYPE
) NESTED TABLE phone STORE AS PHONE_TABLE;

  2、向person表插入数据:

INSERT INTO person VALUES(1,'SCOTT',800,PHONE_TYPE('13256789876','031198765432'));
INSERT INTO PERSON VALUES(20,'CORESUN',2000,PHONE_TYPE('13999999999','13098765345','01064890987'));

  3、查询嵌套表列的数据:

关键字: 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

  查询全部的列信息

SELECT * FROM PERSON;

  在isql*plus中结果如下:



  通过嵌套表变量显示信息

DECLARE
pTable PHONE_TYPE;
BEGIN
SELECT phone into pTable FROM person WHERE name='SCOTT';
FOR   i   IN 1..pTable.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE('号码'||i||':'||pTable(i));
END LOOP;

  结果如下:

  号码1:13256789876

  号码2:031198765432

  4、更新嵌套表数据:

DECLARE
pTable PHONE_TYPE:=PHONE_TYPE('034223455432','13888888888');
BEGIN
UPDATE person SET phone=pTable WHERE name='SCOTT';
END;

  四、可变数组(VARRAY数组)

   VARRAY数组是一种集合数据类型,也可以作为表的列来使用,该集合的下标从1开始,并且元素个数是有限制的。语法如下:

DECLARE 
TYPE MyArrayTypeName IS VARRAY(size) OF ELEMENT_TYPE [NOT NULL];
my MyArrayTypeName

  其中MyArrayTypeName 是数组类型的名字,size表示数组最多存储多少个元素,ELEMENT_TYPE是元素的类型,NOT NULL不允许使用NULL值,my是数组变量的名称。

  示例1:

DECLARE
TYPE MyVArrayType IS VARRAY(10) OF dept.dname%TYPE NOT NULL;

  --构造方法中最多可以有10(size)个元素

  --既是没有定义NOT NULL,也不会自动为4-10元素赋NULL值

nameArray MyVArrayType:=MyVArrayType('1','2','3');
BEGIN
SELECT dname into nameArray(1) from dept where deptno=20;
SELECT dname into nameArray(2) from dept where deptno=30;
DBMS_OUTPUT.PUT_LINE(nameArray(1));
DBMS_OUTPUT.PUT_LINE(nameArray(2));
DBMS_OUTPUT.PUT_LINE(nameArray(3));
END;

  结果如下: RESEARCH
SALES
3



本文转载自    http://doc.chinaunix.net/oracle/200903/208315.shtml

相关文章推荐

PL/SQL_使用复合数据类型5(批量绑定)

批量绑定 批量绑定是Oracle9i新增加的特征,是指执行单次SQL操作能传递所有集合元素的数据。当在SELECT,INSERT,UPDATE,DELETE语句上处理批量数据时,通过批量绑定,可以极...

Oracle PL/SQL复合数据类型

drop table test purge; create table test (   id number(2),   name varchar2(60) ); insert into test v...

PL/SQL(三):复合数据类型

一、复合数据类型  存放多个字段,创建后可以多次使用 二、分类  记录 表 嵌套表 数组 三、简介 1、记录  存储一组多个字段的相关数据项,是字段的集合,主要用于从表中取出查询到的的...

PL/SQL-2 复合数据类型

-------------------------------------------------------------------------- ************************...

PL/SQL_使用复合数据类型3(多级集合)

多级集合 多级集合是指嵌套了集合类型的集合类型。从Oracle9i开始,允许集合类型嵌套另一种集合类型,从而在PL/SQL中实现了多维数组。 DECLARE --定义一维VARRAY TY...

PL/SQL使用复合数据类型4(集合操作符、比较集合)

使用集合操作符给嵌套表赋值 从Oracle 10g开始,在编写PL/SQL程序时允许将多个嵌套表的结果组合到某个嵌套表中,这项任务是使用ANSI集合操作符(SET,MULTISET UNION,MUL...

PL/SQL学习(复合数据类型与游标)

PL/SQL中复合数据类型与游标 一.复合数据类型 1.定义: 复合变量类型不是数据库中已经存在的数据类型,因此复合变量在声明类型之前,首先要创建复合类型,复合类型创建后可以多次被使用。...

oracle PL/SQL的基本构成,块结构和基本语法要求,数据类型,变量定义,运算符和函数

键字: oracle pl/sql 基本构成 块结构 基本 语法 要求 数据 类型 变量 定义 运算符 函数 特点 PL/SQL语言是SQL语言的扩展,具有为程序开...
  • lxxlql
  • lxxlql
  • 2011年03月17日 17:58
  • 1560

oracle PL/SQL的基本构成,块结构和基本语法要求,数据类型,变量定义,运算符和函数

特点 PL/SQL语言是SQL语言的扩展,具有为程序开发而设计的特性,如数据封装、异常处理、面向对象等特性。PL/SQL是嵌入到Oracle服务器和开发工具中的,所以具有很高的执行效率和同Oracl...

oracle的学习六:pl/sql基础(数据类型、定义变量)、游标、过程、函数、包、触发器(系统触发器)

1.PL/SQL的介绍 pl/sql是什么 pl/sql是oracle在标准sql语言上的扩展。pl/sql不仅允许嵌入sql语言,还可以定义变量和常量,允许使用条件语句和循环语句...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:PL/SQL定义部分之二复合数据类型
举报原因:
原因补充:

(最多只允许输入30个字)