Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

WHERE CURRENT OF 有什么作用

Posted on 2010-05-10 22:59 Prayer 阅读(1351) 评论(0)  编辑 收藏 引用 所属分类: DB2
如果你想删除或者更新被Select For Update引用的记录,你可以使用Where Current Of语句。

UPDATE table_name
    SET set_clause
    WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;

Where Current Of语句允许你更新或者是删除最后由cursor取的记录。

下面一个使用Where Current Of更新记录的例子:
CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;
    CURSOR c1
    IS
       SELECT course_number, instructor
        from courses_tbl
        where course_name = name_in
        FOR UPDATE of instructor;

BEGIN
open c1;
fetch c1 into cnumber;

if c1%notfound then
     cnumber := 9999;
else
     UPDATE courses_tbl
        SET instructor = 'SMITH'
        WHERE CURRENT OF c1;
    COMMIT;
end if;

close c1;
RETURN cnumber;
END;

Deleting using the WHERE CURRENT OF Statement
Here is an example where we are deleting records using the Where Current Of Statement:
译:下面一个使用Where Current Of删除记录的例子:
CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;
    CURSOR c1
    IS
       SELECT course_number, instructor
        from courses_tbl
        where course_name = name_in
        FOR UPDATE of instructor;

BEGIN
open c1;
fetch c1 into cnumber;

if c1%notfound then
     cnumber := 9999;
else
     DELETE FROM courses_tbl
        WHERE CURRENT OF c1;
    COMMIT;
end if;

close c1;
RETURN cnumber;
END;

只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理