Oracle的特殊数据类型

181次阅读

没有评论

共计 5048 个字符，预计需要花费 13 分钟才能阅读完成。

Oracle 的特殊数据类型

1.%TYPE

允许用户动态地将数据库中某一列的数据类型与 PL/SQL 中某个变量关联。语法如下:

variable_name table.column%TYPE

2.%ROWTYPE

允许用户定义单一的变量, 该变量包含数据库表的每一列的多个变量。语法规则如下:

variable_name table%ROWTYPE

一旦定义了这种变量, 就可以使用如下的方式来访问各个列:

row_variable.column_name

另外: 可以使用 %ROWTYPE 类型的变量向数据库表中插入或更新整个记录, 方法如下:

EmpRec scott.emp%ROWTYPE;

insert into scott.emp values EmpRec;

3.FOR 游标, 语法如下

FOR record_variable in cursor

Loop

// 处理逻辑

END Loop;

借助于 %ROWTYPE,record_variable 自动定义为一个变量。该循环打开游标, 再循环中为每个记录执行逻辑操作, 当不再存在记录时终止循环并关闭游标。

4. 隐式游标, 为 FOR 游标的一种特殊形式, 其通过直接使用 SQL 语句而不需要显示声明游标

FOR record_variable in (select 语句)

Loop

// 处理逻辑

END Loop;

注意:select 语句一定要使用括号把它包起来

5.REF 游标

游标引用一个结果集。REF CURSOR 允许 PL/SQL 程序单元之间传递一个游标引用。

也即是它允许用户创建一个变量, 该变量接收一个游标并且能够访问其结果集。

使用方式:

声明一个 TYPE: TYPE ref_cursor_name IS REF CURSOR [RETURN_TYPE];return_type 子句是可省的, 可以指定或不指定 REF_CURSOR 返回的数据类型。如果不指定, 则游标是弱类型的。如果指定, 则游标是强类型的。PL/SQL 提供了一个预定义的称为 SYS_REFCURSOR 的弱 REF CURSOR。
然后创建该类型的一个实例: cursor_variable ref_cursor_name ; sys_cursor SYS_REFCURSOR

注意: 由于弱 REF CURSOR 容易导致在 REF CURSOR 返回的数据和应用程序所使用的变量之间的不匹配, 因此更有可能产生 bug。

在创建强类型的 REF CURSOR 时, 既可以使用 %ROWTYPE , 如

TYPE rc_employees HR.EMPLOYEES%ROWTYPE – 声明 rc_employees 类型

TYPE rc_emp IS REF CURSOR rc_employees%TYPE – 声明 REF CURSOR 类型, 并制定返回类型为 rc_employees

也可以使用包含 %TYPE 的 PL/SQL RECORD, 如

TYPE jr_rec is RECORD (

employee_id JOB_HISTORY.EMPLOYEE%TYPE,

job_id JOB_HISTORY.JOB_ID%TYPE,

speed_of_promotion VARCHAR2(4)

);

type rc_jr is REF CURSOR jr_rec%TYPE;

6.RECORD

RECORD 是一种复合数据类型 – 单个记录可以拥有多个分量。RECORD 的典型用法是与 %ROWTYPE 一起配套使用, 如:

record_name table%ROWTYPE

在这个声明之后, 可以使用 record_name.column_name 来访问单个列。

还可以显示的定义 RECORD 包含多个列, 语法如下:

TYPE record_name IS RECORD(col_name dataType[,…]); – 声明类型

rec record_name; – 定义变量

当定义 Record 类型的变量后, 可以

接收 select 语句的结果:select employee_id, employee_name from employee into rec;
向数据库表中 insert:insert into employee values rec;
更新表记录 update: update empee set row rec where employee_id = rec .employee_id;
可以在 delete 语句中使用: DELETE FROM HR.JOB_HISTORY WHERE DEPART_ID =1 RETURN TYPE EMPLOEE_ID,EMPLOEE_NAME into rec;

7. 关联数组

关联数组是按照某个值索引的数据的集合, 它类似于某些其他程序设计语言中的散列表。

语法如下:

TYPE array_name IS TABLE OF DATATYPE INDEX BY VALUE;

声明后, 即可定义一个该类型的变量: v_arr array_name ;

使用循环为关联数组设值:

FOR temp in (select 语句)

LOOP

v_arr[temp .key] = temp.value;

END LOOP;

从关联数组中索引值:

v_temp := v_arr(v_key);

8. 嵌套表

嵌套表使用顺序整数作为集合的索引。尽管数组也使用整数索引数组的内容, 但是用于索引数据的正式是顺序和连续的。

通过创建一种称为稀疏索引, 嵌套表可以使用不连续的数字作为索引。

声明嵌套表的语法如下所示:

TYPE collection_name is TABLE OF datatype [NOT NULL];

这里 datatype 可以是任务有效的 PL/SQL 数据类型,REF CURSOR 除外。NOT NULL 关键字表名, 该集合不能包含 NULL 值的元素。

借助对象类型可以将嵌套表保存在某个数据列中, 而且可以访问这种被存储的表中的单个元素。

9. 可变数组

可变数组又称为 VARRAY。这种稽核采用整数索引, 而且可以保存在数据库中的某列中。

VARRAY 的定义又包含了一个特定的索引上边界。

定义 VARRAY 的语法如下所示:

TYPE collection_name IS VARRAY(size) OF dataType [NOT NULL];

其中 size 是 VARRAY 大小的上限。

10. 使用集合

PLSQL 中的集合是包含单一数据块或由单个变量构成的复合数据的多个实例。

集合的类型: 关联数组、嵌套表和可变数组

可以按照两种方式给稽核赋值。对于嵌套表和可变数组, 可以在单一的语句中赋所有的值:

TYPE number_array IS TABLE OF NUMBER;

number_collection number_array := (1,2,3,4,5,6,7,8,9);

使用索引值, 一次只能附一个值:number_collection(1) := 1;

还可以使用一个集合给另一个集合赋值, 只要这两个集合拥有相同的数据类型。

集合可以用于两种类型的逻辑比较:

判断两个集合是否相等: IF collection1 = collection2

判断集合是否为空: IF collection1 IS NULL;

集合操作:

操作	含义	语法
EXISTS	检查集合中是否存在某个元素	collection.EXISTS(index)
COUNT	返回集合中元素的个数	collection.COUNT
LIMIT	返回 VARRAY 中的最大条目数	collection.LIMIT
FIRST	返回集合中的第一个元素	collection.FIRST
LAST	返回集合中最后一个元素	collection.LAST
NEXT	返回集合中下一个元素	collection.NEXT
PRIOR	返回集合中的上一个元素, 如果不存在, 则返回 NULL	collection.PRIOR
EXTEND	扩展集合中元素的个数。不能用于关联数组或其他未被初始化的集合	collection.EXTEND(给集合增加单个元素) collection.EXTEND(n) : 给集合增加 n 个元素 collection.EXTEND(n,I): 通过复制下标 I 的值, 给集合增加 n 个元素
TRIM	从集合结尾处删除元素	collection.TRIM; 从结尾处删除单个元素 collection.TRIM(n): 从集合结尾处删除 n 个元素
DELETE	从集合中删除元素	collection.DELETE: 从集合中删除所有元素 collection.DELETE(n): 从集合中删除下标为 n 的元素 collection.DELETE(m,n): 从集合中删除下标值位于 m,n 之间的元素

集合使用注意事项:

EXTEND、TRIM 和 DELETE 以外其他操作都返回值
根据环境所定义的语言, 包含 VARCHAR2 下标的关联数组按字母顺序排列
如果集合为空(NULL), 则 FIRST 和 LAST 返回 NULL, 它不包含于任何元素
如果集合中不存在下一个或上一个元素, 则 NEXT 和 PRIOR 返回 NULL
EXTEND 和 TRIM 不能用于关联数组
LIMIT 可以用于限制添加至集合的值的个数, 通常的语法是 l select… into

集合的适用性: 集合类之间存在的差异

	关联数组	嵌套表	VARRAY
下标类型	数字或字符串	数字	数字
声明为固定大小	否	否	是
允许稀疏下标	是	是	否
在数据库中存储	否	是	是
访问存储在集合中的各个元素	N/A	是	是