Oracle/PLSQL: WHERE CURRENT OF Statement
来源:互联网 发布:淘宝店进销存软件 编辑:程序博客网 时间:2024/06/05 11:44
If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.
The syntax for the Where Current Of statement is either:
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.
Updating using the WHERE CURRENT OF Statement
Here is an example where we are updating records using the Where Current Of Statement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGINopen 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:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGINopen 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;
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle/PLSQL: WHERE CURRENT OF Statement
- Oracle 游标的where current of子句
- WHERE CURRENT OF
- where current of 子句
- Oracle/PLSQL: Case Statement
- Oracle/PLSQL: Exit Statement
- Oracle/PLSQL: FETCH Statement
- Oracle/PLSQL: OPEN Statement
- Oracle / PLSQL: ALTER TABLE Statement
- PLSQL条件语句完成员工加薪功能(记录类型+游标case when+if+for update+where current of)
- Oracle游标之select for update和where current of 语句
- for update 与where current of的问题
- Secret of oracle logic IO: Current Mode
- PLSQL:Exit Statement
- 【技术支持】Oracle plsql中的having子句并非都能改写成where子句。having子句的适用场景。
- 《南方周末》新年献词:没有一个冬天不可逾越
- asp.net中Web.config文件各属性详解
- ubuntu 运行Java应用程序出现Permission denied 错误
- 利用Spring中的AOP做权限管理
- Web Service
- Oracle/PLSQL: WHERE CURRENT OF Statement
- asp+ajax在线客服系统
- 毕业设计第一步:选择自己合适的毕业设计
- 金额小写转大写
- Linux内存计数详解
- MyEclipse中配置SQL server数据源
- 从今天开始混csdn 希望各路高手照顾下我这个小菜鸟
- 照片分类管理工具
- OAF开发技术资源