Oracle存储过程+游标

一. 游标

PL/SQL 是用游标来管理 SQL 的 SELECT 语句的 . 游标是为了处理这些语句而分配的一大块内存 . 它提供了对一个结果集进行逐行处理的能力 , 可看作是一种特殊的指针 . 它与某个查询结果集相关联 , 可以指向结果集的任意位置 , 以便对指定位置的数据进行处理 . 使用它可以在查询数据的同时对数据进行处理.

二. 游标的属性

1.%FOUND: 判断当前游标是否正确的指向第一行记录，如果是则返回true，不是则返回false。

2.%NOTFOUND: 与%FOUND相反

3.%ISOPEN: 判断游标是否打开，如果是的话，就返回true，不是则返回false.

4.%ROWCOUNT:判断当前游标在所指向的结果集中的提取的行数，不是所有的记录数.

SQL%FOUND和SQL%NOTFOUND
            在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：

         . TRUE :INSERT
.          TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.
.          TRUE :SELECT INTO至少返回一行
           当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。

另外，有两个变量属性

1.%TYPE: 声明变量的类型与表中的某列的数据类型完全一致.

它的好处有:一.是你不必知道某列的确切数据类型.二:当某列的数据类型改变时不用修改变量的数据类型.

不但列名可以使用%TYPE,而且变量、游标、记录，或声明的常量都可以使用%TYPE.

2.%ROWTYPE:声明变量的数据类型与表中的行记录数据类型一致. 对于自定义的记录, 则必须声明自己的域.

三.游标的类型

1.静态游标

静态游标只是数据的一个快照，用户对记录所做的修改，增加或删除记录都不会反映到记录集中。静态游标从不检测其他的更新、删除和插入情况。例如，一个静态游标提取了一行，然后另一个应用程序更新了这一行。如果应用程序从该静态游标再提取该行，它所看到的值并不发生改变，尽管另一个应用程序已经更改了这行的值。

静态游标又分为两种类型

① 显式游标

显式游标由用户定义, 并由用户来管理游标, 可返回多行记录.

语法:

declare

Cursor cur(游标名) is select * from myuser;(sql语句);

begin

open cur --打开游标

Fetch cur into [变量名或其他数据类型];

close cur --关闭游标

end;

使用显式游标一般按照如下规则

声明游标--打开游标--读取数据--关闭游标

注:FOR .. IN .. LOOP 语句已经包含OPEN，FETCH，CLOSE操作。所以除外。

例子1:

declare

no myuser.userid%TYPE ;

newuser myuser%ROWTYPE;

Cursor mycursor is select * from myuser;

begin

open mycursor;

loop

fetch mycursor into newuser; --fetch之后将移到下一行数据。

no :=newuser.userid;

if mycursor%NOTFOUND then --这个要放在fetch语句之后，不然会死循环

begin exit; end ;end if;

Dbms_Output.put_line(no);

end loop;

close mycursor;

end;

这是按照规则进行的步骤，下面用另一种方法for。。。in。。。loop。

例子2: for .... in .... loop 演示

declare

Cursor mycursor is select * from myuser;

begin

for newuser in mycursor loop -- 将每条记录放到newuser 中

begin

Dbms_Output.put_line('UserName is '||newuser.username);

end;

end loop;

end;

例子3：利用带参数的存储过程演示

create or replace procedure myproc (username in myuser.username%TYPE)

userId myuser.userid%TYPE;

Cursor mycursor is select userid from myuser where username = username;

begin

open mycursor;

fetch mycursor into userId;--先获取一行数据，游标将移到下一行

while mycursor%FOUND --此时这边返回的才不是null，不会产生死循环

loop

Dbms_Output.put_line(username||'s userId is '||userId);

fetch mycursor into userId; --如果返回的是多个参数,则在userid后用逗号隔开加入。

end loop;

close mycursor;--关闭游标

end myproc;

例子4:带参数的游标

declare
       Cursor mycursor(username myuser.username%TYPE) is
       select userId from myuser where username=username;
       usId myuser.userid%TYPE;
        begin
                open mycursor('xiaomin'); --调用游标要给其参数
                 loop
                       fetch mycursor into usId;
                      exit when mycursor%NOTFOUND;-----当数据完成之后跳出循环
                       Dbms_Output.put_line('id is '||usId);
                 end loop;\

close mycursor;
end;

例子5. 游标for循环使用查询

begin

for newuser in (select username from myuser) loop

begin

Dbms_Output.put_line('userName is ' || newuser.username);

end;

end loop;

end;

例子6. 游标中的子查询

CURSOR C1 IS SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept) WHERE dname! = 'ACCOUNTING');
可以看出与SQL中的子查询没有什么区别

②隐式游标

隐式游标的特点:

-隐式游标是由PL/SQL 来管理的, 即不需要声明游标语句, 也不需要OPEN,FETCH,CLOSE 操作

-隐式游标中必须要有select cur_name into [ 变量名或其他数据类型]. 此句完成OPEN,FETCH,CLOSE 操作.

-隐式游标只能返回一行记录, 如果无符合条件的记录将会出现NO_DATA_FOUND 异常. 如果出现多条记录将出现TOO_MANY_ROWS 异常.

-隐式游标只能用SQL% 判断其游标属性

-对于任何位置使用SQL%ISOPEN 结果都是FALSE, 隐式游标由PL/SQL 管理对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭

-对于在隐式游标位置前使用SQL%FOUND 或SQL%NOTFOUND, SQL%ROWCOUNT 结果值是NULL( 不确定值)

例子：验证隐式游标的属性

declare
uname myuser.username%TYPE;
upass myuser.userpass%TYPE;
userid myuser.userid%TYPE;

begin

if sql%isopen then
    -- 判断游标是否打开

    dbms_output.put_line('游标处于开启状态');

else

    dbms_output.put_line('游标处于关闭状态');

end if;
if sql%notfound then
    -- 判断游标是否指向有效的行

    dbms_output.put_line('游标没有指向有效行');

else

    dbms_output.put_line('游标指向有效行');

end if;

dbms_output.put_line(sql%rowcount);

dbms_output.put_line('---------------');

/** 去掉where 条件时, 将会出现too_many_rows 异常**/

select userid, username, userpass
    into userid, uname, upass
    from myuser
   where userid = 1; -- 隐式游标必-- 须使用INTO

dbms_output.put_line(sql%rowcount);

exception

when no_data_found then

    dbms_output.put_line('no value');

when too_many_rows then

    dbms_output.put_line('too many rows');

end;

输出结果为:

游标处于关闭状态
游标指向有效行

---------------
1
游标处于关闭状态
游标指向有效行

二.动态游标

首先 , 游标变量和游标是两个不同的概念 . 与游标相似 , 游标变量是指向多行查游标询的结果集的当前行,游标是静态的 , 游标变量是动态的 ,. 同时游标变量并不参与与特定的查询绑定,可以修改查询的sql语句, 所以可以为任何兼容的查询打开游标变量 , 从而提高灵活性 . 而且 , 还可以将新的值赋予游标变量 , 将它作为参数传递给本地和存储过程 . 游标变量针对每个 PL/SQL 用户都是可用的 , 可以在客户端完全使用游标变量 .ORACLE 服务器同样包含 PL/SQL 引擎 , 可以将游标变量在应用和服务器之间进行传递 .

. 游标变量 :

声明游标实际上是创建一个指针 , 指针具有数据类型 REF X.REF 是 REFERENCE ,X 是表示类对象 . 因此 , 游标变量具有数据类型 REF CURSOR.

注 : 游标总是指向相同的查询工作区 , 游标变量能够指向不同的工作区 , 因此游标和游标变量不能互操作 .

1 定义 REF CURSOR 类型 , 创建游标变量有两个步骤 :

1) 定义 REF CURSOR 类型

语法格式 :

TYPE ref_type_name

REF CURSOR [RETURN return_type]

说明 :

ref_type_name 是游标变量中使用的类型 ;return_type 必须是一个记录 (record) 或者数据库表中的一行 .(rowtype)

下面定义一个 REF CURSOR 类型游标

DELARE

TYPE xs_cur

REF CURSOR RETURN xs%ROWTYPE;

注意 :

REF CURSOR 类型既可以是强类型 , 也可以是弱类型 , 区别是强类型有返回类型 , 弱类型没有 . 如下所示

DECLARE

TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;-- 强类型

TYPE mycur IS REF CURSOR;-- 弱类型

2) 声明这种类型的游标变量 : 一旦定义了 REF CURSOR 类型就可以在 PL/SQL 块或子程序中声明这个游标变量 . 如 :

DECARE

TYPE xs_cur REF CURSOR RETURN xs%ROWTYPE;

xscur xs_cur;

当然 , 在 RETURN 子句中也可以定义自定义的 RECORD 类型 , 如 :

DECLARE

TYPE kc_cj IS RECORD

(

kch number (4),

kcm number(10),

cj number(4,2)

);

TYPE kc_cjcur IS REF CURSOR RETURN kc_cj;

此外 , 也可以声明游标变量作为函数和过程的参数 . 例如 :

DECLARE

TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;

PROCEDURE open_xs (xscur IN OUT xs_cur)IS

......................

3. 控制游标变量

使用游标变量时 , 要遵循以下步骤 :OPEN-FETCH-CLOSE

OPEN 语句与多行查询的游标变量相关联 , 它执行查询 , 标志结果集

语法格式 :

OPEN {cursor_variable|:host_cursor_variable }FOR

{

select_statement|dynamic_string[USING bind_argument[,......]]

}

如 :

IF NOT xscur%ISOPEN THEN

OPEN xscur FOR SELECT * FROM xs;

END IF ;

游标变量同样可以使用游标属性 :%FOUND,%ISOPEN,%ROWTYPE

在使用过程中 , 其他的 OPEN 语句可以为不同的查询打开相同的游标变量 . 因此 , 在重新打开之前 , 不要关闭游标变量 . 可以打开游标 , 并作为参数传递给存储过程 . 如 :

CREATE PACKAGE xs_data AS

...

TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;

RROCEDURE open_xs(xscur IN OUT xs_cur);

END xs_data;

CREATE PACKAGE BODY xs_data AS

...

PROCEDURE open_xs(xscur IN OUT xs_cur)

BEGIN

OPEN xscur FOR SELECT * FROM xs;

END open_xs;

END xs_data;

当声明一个游标变量作为打开游标变量子程序的参数时 , 必须定义 IN OUT 模式 . 也就是说 , 子程序可以将一个打开的游标变量传递给调用者 .

例子1：弱类型游标变量

declare
　　 type RefEmpCur　 is ref cursor;　 --声明引用游标类型游标返回的类型没有限制
　　 EmpCur RefEmpCur;　 --游标变量
　　 EmpRow emp%rowtype; --存储游标查询到得结果
　　 flag int:=0;
begin
　　 flag :=&flag;
　　 if flag=0 then
　　　　　 open EmpCur　 for select * from emp where sal>500 and sal<2000;
　　 elsif　 flag=1 then
　　　　　 open EmpCur for select * from　 emp where sal>=2000;
　　 elsif　 flag=2 then
　　　　　 open EmpCur for select * from　 dept;　 --弱类型游标对目标表没有限制，数据可以使来自任何表
　　 else
　　　　　 open EmpCur for select * from emp;
　　 end if;
　 /* for循环不能用于Ref游标，因为它是自动打开游标
　　 for EmpRow in EmpCur　 loop
　　　 DBMS_output.put_line('empno='||EmpRow.empno);
　　 end loop;
　　 */
　　 loop
　　　　　 exit when EmpCur%notfound;　 --如果没有查询到数据就退出
　　　　　 fetch EmpCur into EmpRow;
　　　　　 DBMS_output.put_line('empno='||EmpRow.empno);
　　 end loop;
　　 close EmpCur;
end;
/

例子2：强类型游标变量

declare
　　 type RefEmpCur　 is ref cursor return emp%rowtype;　 --游标仅能打开emp表的数据
　　 EmpCur RefEmpCur;　 --游标变量
　　 EmpRow emp%rowtype; --存储游标查询到得结果
　　 flag int:=0;
begin
　　 flag :=&flag;
　　 if flag=0 then
　　　　　 open EmpCur　 for select * from emp where sal>500 and sal<2000;
　　 elsif　 flag=1 then
　　　　　 open EmpCur for select * from emp where sal>=2000;
　　 else
　　　　　 open EmpCur for select * from emp;
　　 end if;

　　 loop
　　　　　 exit when EmpCur%notfound;　 --如果没有查询到数据就退出
　　　　　 fetch EmpCur into EmpRow;
　　　　　 DBMS_output.put_line('empno='||EmpRow.empno);
　　 end loop;
　　 close EmpCur;
end;
/

限制

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

2.远程子程序不能接受游标变量的值

3.不能使用比较操作符对游标变量进行相等或不相等测试

4.不能将空值赋予游标变量

5.表不能存储游标变量的值

posted @ 2012-11-06 11:41 老王博客阅读(1712) 评论(0) 编辑收藏举报

刷新页面返回顶部

f204eng

.NET 系列

Oracle存储过程+游标

公告