啃书加更，嵌套表，游标进阶内容

最新推荐文章于 2024-10-01 17:33:05 发布

羞儿

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量95

点赞数 2

分类专栏：啃书《Oracle开发实战经典》文章标签：链表 mysql hadoop

本文链接：https://blog.csdn.net/weixin_43424450/article/details/119610033

版权

啃书《Oracle开发实战经典》专栏收录该内容

9 篇文章 1 订阅

订阅专栏

《Oracle开发实战经典》

记录类型

通过type关键字定义一种记录类型与之类似的功能
- ```
type 数据名称 is record（
	成员名称 数据类型 [[not null] [:= 默认值]表达式]，
	...
	成员名称 数据类型 [[not null] [:= 默认值]表达式]，
）；
```
- 在使用记录型直接更新操作时，定义的记录类型的成员顺序要与操作数据表的字段顺序保持一致，否则无法使用

索引表

在Oracle表中可用定义索引表与程序中的数组的区别
- 索引表不需要进行初始化，可以直接为指定索引赋值，开辟的索引表不一定必须连续
- 索引表不仅可以使用数字作为索引的下标，也可以利用，使用的数字作为索引下标也可以设置负数
- ```
type 类型名称 is table of 数据类型 [not null]
index by [pls_integer|binary_integer|varchar2(长度)]；
```
- 索引表不存在的遍历的操作

嵌套表

是一种类似于索引表的结构，也可以用于保存多个数据，而且还可以保存复合数据

创建嵌套表类型

create [or replace] type 类型名称 as[is table of] 数据类型 [not null];

is在PL/SQL中使用，如果在sqlplus中创建类型，那么使用as和is形式一样，但在pl/sql之中定义类型只能使用IS。

--创建表指定嵌套表存储空间名称
create table mytable（
	字段名称 类型，
	...
	嵌套表字段名称 嵌套表类型
）nested table store as 存储空间名称;

定义符合类型的嵌套表

create [or replace] type 类型名称 as object(
	列名称 数据类类型，
    列名称 数据类类型，
    ...
    列名称 数据类类型
)

在PL/SQL中使用嵌套表
- netsted tables 可以看成一个变长数组，可以存放任意数量的元素(只要内存足够大),通过下标来实现对元素的访问，nested tables 有点是可以在SQL中直接使用。
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qov2MBhn-1628670081292)(C:\Users\Sunlight-9Y9\AppData\Roaming\Typora\typora-user-images\image-20210722142228549.png)]
- nested tables 为了提高删除元素的效率，元素删除后并不移动后续的元素，从图中可以看nested tables 的下标是从1开始的
- “嵌套表.count”来获取数组长度

可变数组

可变数组是一个限制了操作个数的嵌套表

create [or replace] type 类型名称 as|is varray(长度) of 数据类型 [not null];

集合运算符

racle集合运算就是把多个查询结果组合成一个查询结果，oralce的集合运算包括：INTERSECT(交集)、UINION ALL(交集重复)、UINION(交集不重复)、MINUS(补集)。
- INTERSECT(交集)，返回两个查询共有的记录。
- UNION ALL(并集重复)，返回各个查询的所有记录，包括重复记录。
- UNION(并集不重复)，返回各个查询的所有记录，不包括重复记录（重复的记录只取一条）。
- MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
- 当我们使用Oracle集合运算时，要注意每个独立查询的字段名的列名尽量一致（列名不同时，取第一个查询的列名）、列的数据类型、列的个数要一致，不然会报错。

集合运算符	描述
cardinality（集合）	取得集合中所有元素的个数
变量 is [not] null empty	判断集合是否为null
变量 member of 集合	判断某一数据是否为集合的成员
集合1 multiset except 集合2	从一个集合删除另一个集合相同的数据，并返回新集合
集合1 multiset intersect 集合2	取两个集合中相同的部分并返回一个新的集合
集合1 multiset union 集合2	将两个集合合并成一个集合返回
set（集合）	删除重复元素，类似distinct
集合1 submultiset of 集合2	判断集合一是否为集合2的子集合

集合函数	描述
pls_integer count	返回集合中保存的数据长度，此函数返回一个pls_integer的数据
void delete(n [,m])	删除集合中的数据，可以删除指定的第n个索引的数据，或者删除n~m个索引范围内的数据
Boolean exists(n)	判断第n位索引数据是否存在
extend([n [,m]])	为集合扩充数据，其中参数n表示扩充的长度，参数m表示扩充时使用当前集合指定索引上的数据进行内容填充，如果不设置任何参数则列表表示在集合的结尾扩充一个空内容
数据类型 first	返回集合元素中的最小下标值，可返回数据类型有pls_integer,varchar2或long
数据类型 last	返回集合元素中的最da下标值，可返回数据类型有pls_integer,varchar2或long
数据类型 limit	返回集合中允许出现的最高的下标值，此只能被数组使用，返回的数据类型为pls_integer
数据类型 next(n)	返回集合元素中后一个下标值，如果没有数据则返回false，有数据则返回数据类型有pls_integer,varchar2或long
数据类型 prior(n)	返回集合元素中前一个下标值，如果没有数据则返回false，有数据则返回数据类型有pls_integer,varchar2或long
void trim([n])	释放集合的数据，如果没有设置参数则删除集合中最高元素，如果设置了参数，则表示删除指定个数的数据

集合异常	描述
collection_is_null	使用一个空集合变量时触发
no_data_found	在索引表使用一个已经被删除的数据或是索引值重复的触发
subscript_beyond_count	访问索引超过集合中元素个数时触发
subscript_outside_lint	范围索引超过集合最大定义长度时触发
value_error	设置不能转换为pls_integer索引时触发

使用forall,bulk collect批量绑定

使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。要往一个大表里面插入或者修改数据，这个表已经有几百万条数据了，想到了批量绑定，想到了forall。
批量绑定是指执行单次SQL操作能传递所有集合元素的数据,可以极大的加快数据处理速度,提高程序的性能，它是使用Bulk Collect子句和ForALL子句来完成的。
使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。

ForAll:该子句只适用于执行批量的DML操作,只能修饰一条语句，它包括3种语法:


ForAll index in lower_bound..upper_bound
sql_statement;


ForAll index in indices of collection
[Between lower_bound.and. upper.bound]
sql_statement;(和迭代器一样,遍历集合)


ForAll index in values of index_collection
sql_statement;(遍历集合里面的值)

游标

从表中检索出结果集，从中每次指向一条记录进行交互的机制。、
用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，以及是否能够在结果集中向前和/或向后移动（可滚动性）。
游动的光标（指针），可以指向一个结果集，通过游标的移动逐行提取每一行的记录。用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。用来存储多条查询数据的一种数据结构（结果集），它有一个指针，用来从上往下移动，从而达到遍历每条记录的作用。
游标的作用体现
1. 指定结果集中特定行的位置。
2. 基于当前的结果集位置检索一行或连续的几行。
3. 在结果集的当前位置修改行中的数据。
4. 对其他用户所做的数据更改定义不同的敏感性级别。
5. 可以以编程的方式访问数据库。
程序语言是面向记录的，一组变量一次只能存放一个变量或者一条记录，无法直接接收数据库中的查询结果集引入游标就解决了这个问题
在创建游标时，最需要考虑的事情是，“是否有办法避免使用游标？”因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
游标的类型：
- 静态游标：结果集已经确实（静态定义）的游标。在执行前，明确知道sql语句游标 ,分为隐式和显示游标。
  - 显示游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。
  - 隐式游标 ：所有DML语句（增、删、改、查询单条记录）为隐式游标，该变量名不需要用户自己声明，它由系统帮我们定义，叫sql。使用时不需要声明隐式游标，它由系统定义 . 隐式游标的使用：通过%rowcour判断是有使用了。
- 动态游标(REF游标）：在执行前不知道sql语句游标，执行时才知道sql语句的游标。动态关联结果集的临时对象。
  - **强类型游标****：规定返回类型
  - **弱类型游标****：不规定返回类型，可以获取任何结果集。
*游标的状态是通过属性来表示。*
- %Found ：用于检验游标是否成功，通常在fetch语句前使用，当游标按照条件查询一条记录是，返回true,Fetch语句（获取记录）执行情况True or False。
- %NotFound : 最后一条记录是否提取出True or False。到了游标尾部，没有记录了，就返回true
- %ISOpen ：游标是否打开True or False。
- %RowCount ：游标当前提取的行数 ,即获得影响的行数 1
****使用显示游标****：
- ****⑴声明游标：****划分存储区域，注意此时并没有执行Select 语句。
  
  CURSOR 游标名( 参数列表) [返回值类型] IS Select 语句；
- ****⑵打开游标：****执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。
  
  Open 游标名( 参数列表);
- ****⑶获取记录****：移动游标取一条记录
  
  Fetch 游标名InTo 临时记录或属性类型变量；
- ****⑷关闭游标****：将游标放入缓冲池中，没有完全释放资源。可重新打开。
Close 游标名；
```
cursor 游标名（[参数列表]）[return 返回值类型]
is 子查询
[for update [of 数据列，数据列，]][nowait]；
```
UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。
- 要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，
- 所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT…FOR UPDATE操作。
- 在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。
- 如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
声明更新或删除显示游标：
- Cursor 游标名IS SELECT 语句 For Update [ Of 更新列列名]；
- Cursor 游标名IS SELECT 语句 For Delete [ Of 更新列列名]；
使用显示游标当前记录来更新或删除：
- Update 表名 SET 更新语句 Where Current Of 游标名；
- Delete From 表名 Where Current Of 游标名；
与过程和函数相似，可以将参数传递给游标并在查询中使用。
- 参数只定义数据类型，没有大小(所有Oracle中的形参只定义数据类型，不指定大小)。
- 与过程不同的是，游标只能接受传递的值，而不能返回值。
- 可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。
- 游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。
声明带参数的显示游标：
- CURSOR 游标名 [(parameter[,parameter],…)] IS Select语句；
  - 参数形式：
  - 1，参数名数据类型
  - 2，参数名数据类型 DEFAULT 默认值
REF游标作用
- 实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。
**静态游标和REF游标的区别
1. 静态游标是静态定义，REF 游标是动态关联；
2. 使用REF 游标需REF 游标变量。
3. REF 游标能做为参数进行传递，而静态游标是不可能的。
REF游标变量：REF游标变量是一种引用REF游标类型的变量，指向动态关联的结果集。
声明REF 游标类型，确定REF 游标类型；
- 强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。
  - 语法：Type REF游标名 IS Ref Cursor Return 结果集返回记录类型；
- 弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。
  - 语法：Type REF游标名 IS Ref Cursor；
声明Ref 游标类型变量；
- 语法：变量名已声明Ref 游标类型；
打开REF游标，关联结果集；
- 语法：Open Ref 游标类型变量 For 查询语句返回结果集；
获取记录，操作记录；
- 语法：Fatch REF游标名InTo 临时记录类型变量或属性类型变量列表；
关闭游标，完全释放资源；
- 语法：Close REF游标名；
游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
游标可分为：
- 静态游标：分为显式游标和***隐式***游标。
- REF游标：是一种引用类型，类似于指针。----也称*****为动态游标*****

句返回结果集；

获取记录，操作记录；
- 语法：Fatch REF游标名InTo 临时记录类型变量或属性类型变量列表；
关闭游标，完全释放资源；
- 语法：Close REF游标名；
游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
游标可分为：
- 静态游标：分为显式游标和***隐式***游标。
- REF游标：是一种引用类型，类似于指针。----也称*****为动态游标*****