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_使用复合数据类型1(PL/SQL记录)

定义PL/SQL记录 1、自定义PL/SQL记录 当使用自定义的PL/SQL记录时,需要分别定义PL/SQL记录类型和记录变量。定义记录类型和记录变量的语法如下: TYPE type_name IS ...
  • com185272358
  • com185272358
  • 2014年03月21日 18:56
  • 647

pl/sql之定义并使用变量,复合类型

定义并使用变量  介绍 在编写pl/sql程序时,可以定义变量和常量;在pl/sql程序中包括有: 1.标量类型(scalar) 2.复合类型(composite) 3.参照类型(reference...
  • yjsuge
  • yjsuge
  • 2011年06月18日 15:02
  • 2403

Oracle基本操作八:PL/SQL特殊数据类型%type %rowtype VARRAY TABLE RECORD

--%type %rowtype %type  前面必须有前缀 定义某个变量的数据与已存在的变量数据类型、某个列的数据类型相同。 好处:1.可能不知道数据表中字段的数据类型       2.数...
  • nipanlong001
  • nipanlong001
  • 2017年05月13日 21:29
  • 255

PL/SQL定义复合数据类型

 复合类型  复合类型可以存储多个值,包括记录和集合。集合是一维的,但是可以在集合中放入其它集合,变成多维集合。集合又分为索引表、嵌套表和变长数组三种。要使用集合,我们首先要创建集合类型,然后声明该类...
  • thamsyangsw
  • thamsyangsw
  • 2009年03月25日 17:19
  • 1343

PL/SQL 数据类型和变量 -- BOOLEAN

-- Start PL/SQL 还支持布尔(BOOLEAN)数据类型。 DECLARE ISTEST BOOLEAN; BEGIN IF (ISTEST IS NULL) THEN ...
  • shangboerds
  • shangboerds
  • 2015年01月01日 10:30
  • 1054

PL/SQL_使用复合数据类型2(PL/SQL集合)

使用PL/SQL集合 PL/SQL集合类型是类似于高级语言数组的一种复合数据类型,集合类型包括索引表(PL/SQL表)、嵌套表(Netsted Table)和变长数组(VARRAY)等三种类型。 ...
  • com185272358
  • com185272358
  • 2014年03月21日 19:04
  • 659

PL/SQL 变量与常用数据类型

1.基础数据类型 以ORACLE8i为例,可以使用的基础数据类型有: 类型 子类 说     明 范   围 ORACLE限制 ...
  • lutinghuan
  • lutinghuan
  • 2012年08月06日 23:53
  • 3105

PL/SQL 数据类型和变量 -- 数值

-- Start PL/SQL 支持 SQL 的数值数据类型,除此之外,它还定义了自己的子类型。有关 SQL 数值数据类型,参见此处。 DECLARE -- 声明变量并赋值 TES...
  • shangboerds
  • shangboerds
  • 2014年12月21日 23:45
  • 1470

Oracle PL/SQL复合数据类型

drop table test purge; create table test (   id number(2),   name varchar2(60) ); insert into test v...
  • guogang83
  • guogang83
  • 2014年10月27日 11:01
  • 960

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

一、复合数据类型  存放多个字段,创建后可以多次使用 二、分类  记录 表 嵌套表 数组 三、简介 1、记录  存储一组多个字段的相关数据项,是字段的集合,主要用于从表中取出查询到的的行数据     ...
  • leovnay
  • leovnay
  • 2014年01月16日 20:04
  • 610
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:PL/SQL定义部分之二复合数据类型
举报原因:
原因补充:

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