在PL/SQL使用游标获取数据及动态SQL

1.游标概念：

当在PL/SQL块中执行DML(增删改)时，Oracle会为其分配上下文区(Context Area)，游标是指向上下文区的指针

2. 游标分类：

A. 隐式游标

a. 在PL/SQL中使用DML语句时自动创建隐式游标

b. 隐式游标自动声明、打开和关闭，其名为 SQL

c. 通过检查隐式游标的属性可以获得最近执行的DML 语句的信息

d. 隐式游标的属性有：

%FOUND – SQL 语句影响了一行或多行时为 TRUE

%NOTFOUND – SQL 语句没有影响任何行时为TRUE

%ROWCOUNT – SQL 语句影响的行数

%ISOPEN - 游标是否打开，始终为FALSE

e. 隐式游标的错误处理：NO_DATA_FOUND：没有查询到任何行。TOO_MANY_ROWS：返回多行。

B. 显式游标

a. 显式 游标的使用步骤：

1) 声明显式游标语法：

CURSOR 游标名[（游标参数列表）] [RETURN 返回值规范] IS SELECT 语句 [FOR UPDATE OF [列名列表]];

2) 打开显式游标语法：

OPEN 显式游标名 （参数列表）;

3) 提取数据语法：

FETCH 游标名 INTO 记录或变量列表;

4) 关闭游标语法：

CLOSE 游标名;

b. 显式游标属性

显式游标名%属性

%FOUND – SQL 语句影响了一行或多行时为 TRUE

%NOTFOUND – SQL 语句没有影响任何行时为TRUE

%ROWCOUNT – SQL 语句影响的行数

%ISOPEN - 游标是否打开，始终为FALSE

3. 循环游标 ：

用于简化游标处理代码、当用户需要从游标中提取所有记录时使用

循环游标的语法如下：

FOR <record_index> IN <cursor_name>

LOOP

<executable statements>

END LOOP;

4. BULK COLLECT

为什么要使用BULK COLLECT？

提交PL/SQL中SELECT语句的查询效率。

原理：减少了PL/SQL与SQL引擎之间的开关次数，因而加速了数据获取的速度

语法：

... BULK COLLECT into 集合

5. REF 游标和游标变量：用于处理运行时动态执行的 SQL 查询

使用游标变量的步骤：

A. 创建游标变量需要两个步骤：

a. 声明 REF 游标类型

b. 声明 REF 游标类型的变量

用于声明 REF 游标类型的语法为：

TYPE <ref_cursor_name> IS REF CURSOR [RETURN <return_type>];

用于声明REF 游标类型变量的语法：变量名 游标类型名;

B. 打开游标变量的语法如下：

OPEN cursor_name FOR select_statement;

C. 提取数据语法：

a. FETCH 游标变量名 INTO 接收游标数据的变量 &hellip;…;

b. FETCH 游标变量名 INTO 接收游标结果的集合变量 ….[LIMIT rows];

D. 关闭游标变量语法：CLOSE 游标变量名 ;

6. 游标变量的优点和限制

A. 游标变量的优点有：

a. 可从不同的 SELECT 语句中提取结果集

b. 可以作为过程的参数进行传递

c. 可以引用游标的所有属性

d. 可以进行赋值运算

B. 使用游标变量的限制：

a. 不能在程序包中声明游标变量

b. FOR UPDATE子句不能与游标变量一起使用

c. 不能使用比较运算符

7. 动态SQL概述

A. 绑定

a. 编译器识别程序中的标识符存储位置的过程。

b. 分类：早期绑定与晚期绑定

B. 动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句

C. DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行

D. 两种动态SQL技术

a. DBMS_SQL包：Oracle 8i以前使用。

b. 本地动态SQL：Oracle 9i以后使用。

对比：本地动态SQL更简单、执行更快。

E. 执行动态SQL

a. 语法1：execute immediate 'sql语句字符串'

b. 语法2：open x for 'sql语句字符串'

8. 绑定变量

A. 为什么使用绑定变量：减少编译SQL的次数，提交执行效率

B. 绑定变量说明

execute immediate

'insert into t1(f1,f2) values(:a,:b)' using x,y;

注意： using后面的变量与绑定变量根据位置一一对应。 x-->a、y-->b

C. 绑定变量的使用

a. 在SQL*PLUS(命令窗口) 中使用绑定变量

只能在SQL*PLUS交互环境下使用。

b. 在PL/SQL(存储过程和函数)中使用绑定变量

参数引用的已经是绑定变量。

c. 在动态SQL中使用绑定变量

只能绑定字面量(替换具体的值)

不能替换数据库对象名称

d. 在JDBC中使用绑定变量

PReparedStatement对象

9. 动态SQL语法

语法1：execute immediate 'sql语句字符串'

语法2：open for 'sql语句字符串'