你的位置:首页 > Java教程

[Java教程]在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   接收游标数据的变量 ……;

  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语句字符串'