PL/SQL三种集合类型的比较
集合是指在一个程序变量中包含多个值。PL/SQL提供的集合类型如下:
Associative Array:
|
TYPE t IS TABLE OF something INDEX BY PLS_INTEGER;
|
Nested Table:
|
TYPE t IS TABLE OF something;
|
VARRAY:
|
TYPE t IS VARRAY (123) OF something;
|
三种集合的各自使用范围
前提是在PL/SQL中希望某种类型的多个值,并且希望使用myarray[n]的形式访问。
范围:SQL or PL/SQL
最大的区别是首先需要确定使用范围,SQL和PL/SQL时两种不同的语言。PL/SQL是SQL的过程性语言。Associative Arrays仅存在于PL/SQL中。因此:
·只能在PL/SQL中使用的是:Associative Array;
·可以在SQL中使用:Nested Table, VARRAY;
同时需要注意的是:在SQL中创建一个类型和PL/SQL中声明一个类型是不同的。
SQL中创建的集合类型
集合是SQL的一部分,不需要到PL/SQL中就可以使用它们:
CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(100)
/
SELECT column_value AS val
FROM TABLE(VARCHAR2_TT('Apple','Banana','Apricot'))
WHERE column_value NOT LIKE 'A%';
VAL
--------------------
Banana
在以上的例子中,我们创建了一个集合类型,并在查询中使用,完全在SQL中。这种形式创建的类型可以在任何查询和PL/SQL程序中使用。其他用户使用需要GRANT EXECUTE ON type_name TO other_account授权。
PL/SQL中声明的集合类型
在PL/SQL中,可以使用SQL中创建的集合类型,或者本地声明自己的类型。在PL/SQL中声明可以更加灵活的自定义包中专用的类型,允许声明他们使用%TYPE和%ROWTYPE引用特定的表的列,如TABLE OF emp.empno%TYPE。不过这仅仅是PL/SQL中的特征,不能在SQL中使用,甚至可以定义自己的类型,然后生命那种类型的集合。如下:
DECLARE
TYPE stats_rec IS RECORD
( batch_step batch_log.step_name%TYPE
, elapsed_time INTERVAL DAY(0) TO SECOND(0)
, rows_processed PLS_INTEGER );
TYPE stats_tt IS TABLE OF stats_rec; -- a locally-defined nested table collection type
由于仅在PL/SQL中使用, SQL并不知道:
CREATE OR REPLACE PACKAGE testtypes AS
TYPE varchar2_tt IS TABLE OF VARCHAR2(100);
END testtypes;
/
Package created.
SELECT column_value AS val
FROM TABLE(testtypes.VARCHAR2_TT('Apple','Banana','Apricot'))
WHERE column_value NOT LIKE 'A%';
FROM TABLE(testtypes.VARCHAR2_TT('Apple','Banana','Apricot'))
*
ERROR at line 2:
ORA-22905: cannot access rows from a non-nested table item
可以使用以下查询查看所有类型:
SELECT ct.owner, ct.type_name, ct.elem_type_name, ct.length
FROM all_coll_types ct
, all_types ot
WHERE ct.coll_type = 'TABLE'
AND ot.type_name(+) = ct.elem_type_name
AND ot.owner(+) = ct.elem_type_owner
AND ot.type_name IS NULL
ORDER BY ct.owner, ct.type_name;
Associative Arrays特征
如果在PL/SQL中只需要声明和填充,那么关联数组将是一种方便的方法。不需要独立的CREATE OR REPLACE TYPE,不需要初始化,不需要扩展,可以随意的放入各种值,如下:
DECLARE
TYPE number_index_by_string IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
TYPE string_index_by_number IS TABLE OF dept.loc%TYPE INDEX BY PLS_INTEGER;
v_country_codes NUMBER_INDEX_BY_STRING;
v_countries STRING_INDEX_BY_NUMBER;
BEGIN
v_country_codes('Ukraine') := 380;
v_country_codes('UAE') := 971;
v_country_codes('UK') := 44;
v_country_codes('USA') := 1;
v_countries(380) := 'Ukraine';
v_countries(971) := 'UAE';
v_countries(44) := 'UK';
v_countries(1) := 'USA';
END;
需要注意的是,在number_index_by_string中使用了INDEX BY VARCHAR2(30),这使我们通过v_country_codes('UK')引用数组的UK元素。这是数组的唯一特征。关联数组没有构造体。也就是不能使用myarray := mytype(2,4,6,37)进行一次性填充四个值。
PL/SQL集合类型相对于SQL集合类型
可以在PL/SQL中声明任何类型。
主要区别如下:
范围
|
意义
|
集合类型
|
PL/SQL
|
只能在PL/SQL中声明,没有"CREATE OR REPLACE TYPE". SQL 中不能使用。
不需要初始化和扩展,只需要分配值给特定的元素,甚至不需要元素顺序。
可以选择"index by" - PLS_INTEGER, BINARY_INTEGER或 VARCHAR2.
没有构造体,必须显示分配值。不能在查询中作为一个表处理,如SELECT * FROM TABLE(myarray)
|
Associative Array
|
SQL ,PL/SQL
|
可以在PL/SQL中声明或者使用"CREATE OR REPLACE TYPE";
使用前必须初始化:myarray mytype := mytype();
具有构造体,可以使用mytype('x','y','z');分配值;
必须进行扩展以增加元素,myarray.EXTEND;
在查询中可以作为表处理SELECT * FROM TABLE(myarray) (如果使用CREATE TYPE创建).
|
Nested Table
|
VARRAY
|
VARRAYs的缺点
可以看到,VARRAYs和Nested Tables的描述基本相同,并且两种类型都可以在SQL和PL/SQL的TABLE()中使用。VARRAYs的一些不具有的特征在nested table中可以使用的包括:
·集合函数,如CARDINALITY,COLLECT,POWERMULTISET,等;
·多重集条件,如IS A SET, MEMBER OF,SUBMULTISET;
·多重集操作符,如MULTISET INTERSECT;
如:
DECLARE
my_array VARCHAR2_TT :=
VARCHAR2_TT('Apple','Apple','Orange','Banana');
BEGIN
IF my_array IS A SET THEN
DBMS_OUTPUT.PUT_LINE('No duplicates found');
ELSE
DBMS_OUTPUT.PUT_LINE('Collection contains duplicates');
END IF;
END;
Collection contains duplicates
再如:
DECLARE
my_array VARCHAR2_TT :=
VARCHAR2_TT('Apple','Apple','Orange','Banana');
BEGIN
IF 'Orange' MEMBER OF my_array THEN
DBMS_OUTPUT.PUT_LINE('"Orange" exists in the collection');
ELSE
DBMS_OUTPUT.PUT_LINE('"Orange" does not exist in the collection');
END IF;
END;
"Orange" exists in the collection
DECLARE
my_array1 VARCHAR2_TT :=
VARCHAR2_TT('Apple','Orange','Cherry','Banana');
my_array2 VARCHAR2_TT :=
VARCHAR2_TT('Orange','Kumquat','Grape','Banana');
my_array3 VARCHAR2_TT := my_array1 MULTISET INTERSECT my_array2;
BEGIN
FOR i IN my_array3.FIRST..my_array3.LAST LOOP
DBMS_OUTPUT.PUT_LINE(my_array3(i));
END LOOP;
END;
分享到:
相关推荐
6. **复合类型**:PL/SQL支持数组、集合和记录等复合类型,这些使得处理复杂数据结构变得可能。手册会详细介绍这些类型的创建和使用。 7. **包(Packages)**:包是将相关的过程、函数和变量封装在一起的一种方式,...
Oracle PL/SQL是一种强大的编程语言,它结合了SQL(结构化查询语言)的数据库操作功能与PL/SQL的程序设计特性,广泛应用于Oracle数据库的开发和管理。这本书"Oracle PL/SQL从入门到精通"的配套源代码,显然是为了...
循环语句是PL/SQL编程中用来重复执行一组语句的结构,包括loop、while、for三种形式,它们各自有不同的使用场景和语法。 分支条件语句在PL/SQL中用来根据不同的条件执行不同的代码分支,主要包括CASE表达式和if-...
- **集合与记录**:学习如何使用PL/SQL中的集合和记录类型,这些结构可以帮助更高效地处理数据集。 - **游标**:介绍游标的使用方法,包括隐式游标和显式游标,以及如何利用游标遍历查询结果。 #### 四、PL/SQL中的...
PL/SQL是Oracle数据库系统中的一个关键组成部分,它是一种结合了SQL与过程编程的语言,用于在Oracle环境中开发数据库应用程序。PL代表Procedural Language,SQL则代表Structured Query Language。这个"PL/SQL安装包...
PL/SQL 是一种块结构的语言,组成 PL/SQL 程序的单元是逻辑块,一个 PL/SQL 程序包含了一个或多个逻辑块,每个块都可以划分为三个部分: * 声明部分(Declaration section):包含了变量和常量的数据类型和初始值。...
- **程序包**:集合了一系列相关的子程序、类型定义和其他PL/SQL元素,便于管理和复用。 **2.3 标识符规则** PL/SQL中的标识符遵循以下规则: - 标识符长度不能超过30个字符。 - 第一个字符必须是字母。 - 不区分...
5. **集合类型**:讨论PL/SQL中的集合类型,如数组、关联数组、表类型和索引表,以及它们在处理大量数据时的优势。 6. **包(PACKAGE)**:解释如何创建和使用包,包括包规范(PACKAGE SPECIFICATION)和包体...
5. **过程与函数**:过程是一组PL/SQL语句的集合,用于执行特定任务。函数与过程类似,但返回一个值。它们可以提高代码的复用性和模块化。 6. **异常处理**:PL/SQL提供了一个强大的异常处理机制,允许你定义和捕获...
2. **复合数据类型**:介绍PL/TABLE、记录类型(%ROWTYPE)以及集合类型(VARRAY和NESTED TABLE),这些都是处理大量数据或构建复杂数据结构的关键工具。 3. **游标**:讲解如何使用显式和隐式游标进行查询和遍历...
5. **集合类型**:包括数组(PLS_INTEGER索引的PL/SQL表)和关联数组(索引由非数字类型的值定义),以及如何在集合中插入、删除和迭代元素。 6. **包(PACKAGE)**:创建和使用包来组织相关的过程和函数,实现模块...
- 表类型:使用PL/SQL表类型进行集合操作。 -记录类型:定义和使用记录类型,类似于结构或对象。 5. **程序单元**: - 包(PACKAGE):定义公共接口和私有实现,提高代码复用性。 -存储过程和函数:创建存储在...
【PL/SQL编程】是Oracle数据库中用于数据库管理和应用程序开发的一种高级过程语言,它结合了SQL查询语言的威力和高级编程语言的灵活性。本章详细介绍了PL/SQL的基础知识,帮助初学者快速掌握该语言。 **4.1 PL/SQL...
PL/SQL是Oracle公司开发的一种过程化SQL扩展,它是Oracle数据库的重要组成部分,用于在数据库服务器上编写存储过程、函数、触发器、包等可执行代码。PL/SQL可以处理复杂的数据操作和业务逻辑,支持编程结构如循环、...
集合类型 对象类型等 本书不仅适合于pl/sql初学者 而且也适合于有经验的pl/sql编程人员 本书还可以作为oracle应用开发培训班的教材或者辅助材料 ">sql是关系数据库的基本操作语言 它是应用程序与数据库进行交互...
2. **变量和数据类型**:PL/SQL支持多种内置数据类型,如NUMBER、VARCHAR2、DATE等,以及PL/SQL特有的记录和集合类型。 3. **流程控制语句**:包括IF-THEN-ELSIF-ELSE、CASE、FOR循环、WHILE循环等,用于控制程序的...
Oracle中的PL/SQL是一种强大的编程语言,它是Oracle数据库与应用程序之间的桥梁,专为数据库操作而设计。PL代表“过程化语言”(Procedural Language),SQL则是“结构化查询语言”(Structured Query Language)。...
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...
4. **集合**:PL/SQL支持数组和记录等集合类型,允许存储多个同类型的数据项。 5. **游标**:游标是处理查询结果集的一种方式,可以逐行访问SELECT语句返回的结果。这对于迭代处理大量数据非常有用。 6. **存储...
7. **记录和指针**:理解PL/SQL中的记录类型,以及如何使用指针在记录和集合中移动。 8. **动态SQL**:了解如何在PL/SQL中构建和执行动态SQL语句,以适应不断变化的查询需求。 9. **事务管理**:学习如何控制事务...