共计 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 | 是 | 是 |
11.BULK COLLECT
使用 BULK COLLECT 和关联数组的结合, 可以进行批处理操作, 如下所示:
TYPE REC IS RECORD(…,…);
定义关联数组类型:TYPE rec_table is TABLE OF rec index by PLS_INTEGER;
声明变量:rec_table_array rec_table;
OPEN cursor;
fetch cursor bucket into rec_table_array;
close cursor;
FOR counter in rec_table_array.FIRST … rec_table_array.LAST
LOOP
// 处理逻辑
END LOOP;
12.FORALL
FORALL 结构允许执行写操作时获得与 BULK COLLECT 所提供的相同效率, 这个结构封装多个写语句, 并且在单一的消息中将它们发送给 ORACLE 数据库, 从而提高该操作的总体性能。
FORALL 结构的语法如下:
FORALL index_name in lower_bound … upper_bound
sqlstatment
其中 index_name 是一个为在该 FORALL 循环中使用而隐士声明的变量。
lower_bound 和 upper_bound 限制该集合的范围, 这个循环是针对集合的。
例如: 如果想要使用 FORALL 插入位于集合中的所有所有记录:
FORALL jh_index in jh.FIRST .. jh.LAST
INSERT INTO HR.JOB_HISTORY VALUES jh(jh_index);
注意: 挡在 FORALL 循环中包含 SAVE EXCEPTIONS 关键字时, 该循环不再对个别 SQL 语句中的错误报告异常。
相反, 如果在 FORALL 循环的处理过程中出现任何异常, 则在该处理过程结束时报告错误代号为 -24381 的一个异常。
FORALL 的改进
1.INDICES OF
这是一种对稀疏集合处理的改进。该集合的记录并非连续分布 — 他们连续地分布在集合的整个范围内。
FORALL INDICES OF collection_name [between lower_bound… upper_bound]
sql_statement
以上语句告诉 FORALL 读下一个下标值而不是下一个连接的值。使用 INDICES OF 子句可以让 FORALL 结构适用于稀疏集合。
通过指定集合的上、下边界, INDICES OF 子句还允许只选择处理整个集合的一部分。
2.VALUES OF
这种改进使得 FORALL 结构的应用更具灵活性。
VALUES OF 子句允许使用另一种集合, 确定哪些记录作为 FORALL 结构的目标。
FORALL index_values VALUES OF driver_collection
使用这个子句的唯一要求是该驱动集合必须是嵌套表或关联数组。用于这种结构的关联数组的下标和元素都必须是 BINARY_INTEGER 或 PLS_INTEGER
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135218.htm
正文完
星哥玩云-微信公众号
