FOR UPDATE Clause
The FOR UPDATE clause locks the rows selected by the query. Once you have selected a row for update, other users cannot lock or update it until you end your transaction. The FOR UPDATE clause signals that you intend to insert, update, or delete the rows returned by the query, but does not require that you perform one of these operations. A SELECT statement with a FOR UPDATE clause is often followed by one or more UPDATE statements with WHERE clauses.
The FOR UPDATE clause cannot be used with the following other constructs: DISTINCT operator
GROUP BY clause
set operators
group functions
CURSOR operator
The tables locked by the FOR UPDATE clause must all be located on the same database. These locked tables must also be on the same database as any LONG columns and sequences referenced in the same statement.
If a row selected for update is currently locked by another user, Oracle waits until the row is available, locks it, and then returns control to you. You can use the NOWAIT option to cause Oracle to terminate the statement without waiting if such a row is already locked.
The rows returned from subqueries whose column value is a nested table or a VARRAY, not a scalar value, are not locked. Only the top-level rows of such select lists are locked.
LOB Locking
Prior to updating a LOB value, you must lock the row containing the LOB. One way to lock the row is with a SELECT... FOR UPDATE statement.
ExampleINSERT INTO t_table VALUES (1, 'abcd'); COMMIT;
DECLARE
num_var NUMBER;
clob_var CLOB;
clob_locked CLOB;
write_amount NUMBER;
write_offset NUMBER;
buffer VARCHAR2(20) := 'efg';BEGIN
SELECT clob_col INTO clob_locked FROM t_table
WHERE num_col = 1 FOR UPDATE; write_amount := 3;
dbms_lob.write(clob_locked, write_amount, write_offset, buffer);
END;
FOR UPDATE OF Clause
The columns in the OF clause only specify which tables' rows are locked. The specific columns of the table that you specify are not significant. If you omit the OF clause, Oracle locks the selected rows from all the tables in the query.
Example I
The following statement locks rows in the EMP table with clerks located in New York and locks rows in the DEPT table with departments in New York that have clerks: SELECT empno, sal, comm
FROM emp, dept
WHERE job = 'CLERK'
AND emp.deptno = dept.deptno
AND loc = 'NEW YORK'
FOR UPDATE;
Example II
The following statement locks only those rows in the EMP table with clerks located in New York; no rows are locked in the DEPT table: SELECT empno, sal, comm
FROM emp, dept
WHERE job = 'CLERK'
AND emp.deptno = dept.deptno
AND loc = 'NEW YORK'
FOR UPDATE OF emp.sal;
The FOR UPDATE clause locks the rows selected by the query. Once you have selected a row for update, other users cannot lock or update it until you end your transaction. The FOR UPDATE clause signals that you intend to insert, update, or delete the rows returned by the query, but does not require that you perform one of these operations. A SELECT statement with a FOR UPDATE clause is often followed by one or more UPDATE statements with WHERE clauses.
The FOR UPDATE clause cannot be used with the following other constructs: DISTINCT operator
GROUP BY clause
set operators
group functions
CURSOR operator
The tables locked by the FOR UPDATE clause must all be located on the same database. These locked tables must also be on the same database as any LONG columns and sequences referenced in the same statement.
If a row selected for update is currently locked by another user, Oracle waits until the row is available, locks it, and then returns control to you. You can use the NOWAIT option to cause Oracle to terminate the statement without waiting if such a row is already locked.
The rows returned from subqueries whose column value is a nested table or a VARRAY, not a scalar value, are not locked. Only the top-level rows of such select lists are locked.
LOB Locking
Prior to updating a LOB value, you must lock the row containing the LOB. One way to lock the row is with a SELECT... FOR UPDATE statement.
ExampleINSERT INTO t_table VALUES (1, 'abcd'); COMMIT;
DECLARE
num_var NUMBER;
clob_var CLOB;
clob_locked CLOB;
write_amount NUMBER;
write_offset NUMBER;
buffer VARCHAR2(20) := 'efg';BEGIN
SELECT clob_col INTO clob_locked FROM t_table
WHERE num_col = 1 FOR UPDATE; write_amount := 3;
dbms_lob.write(clob_locked, write_amount, write_offset, buffer);
END;
FOR UPDATE OF Clause
The columns in the OF clause only specify which tables' rows are locked. The specific columns of the table that you specify are not significant. If you omit the OF clause, Oracle locks the selected rows from all the tables in the query.
Example I
The following statement locks rows in the EMP table with clerks located in New York and locks rows in the DEPT table with departments in New York that have clerks: SELECT empno, sal, comm
FROM emp, dept
WHERE job = 'CLERK'
AND emp.deptno = dept.deptno
AND loc = 'NEW YORK'
FOR UPDATE;
Example II
The following statement locks only those rows in the EMP table with clerks located in New York; no rows are locked in the DEPT table: SELECT empno, sal, comm
FROM emp, dept
WHERE job = 'CLERK'
AND emp.deptno = dept.deptno
AND loc = 'NEW YORK'
FOR UPDATE OF emp.sal;
解决方案 »
- 一个很简单的问题,如何定义一个数值型变量
- EXP导出数据库,有文件大小限制么
- ora触发器能不能自动获得触发条件的记录
- 关于ORACLE的数据导出到SQL
- oracle启动错误求助
- 新手提问:“to_char(key_date,'yyyymmdd') as dt_yyyymmdd”后面的as dt_yyyymmdd是什么意思呢?
- Oracle“方案”这一名词的定义,“方案”该如何理解?怎样应用?
- 新建数据库
- 触发器?
- 救命,用VC+Oracle+ADO(网上下载的类)遇到不能使用Update语句的问题
- 关于一个对象权限的问题,在线等待
- 临时表空间创建的时候分配了1G的空间,为什么不久就占用了590M的空间,也不上涨也不回收,是为什么?
SELECT E,F,G
INTO V1,V2,V3
FROM TABLE1
WHERE A = B
AND C = D
FOR UPDATE NOWAIT;
(根据WHERE的条件能够搜出多条数据)
运行时会报错SQLCODE = -1422,-1422错误表示为:查询的结果集中返回多行。
我要在PL/SQL中锁定多条数据,要怎么做呢?
INTO V1,V2,V3
FROM TABLE1
WHERE A = B
AND C = D
FOR UPDATE NOWAIT;
这句话要求select 的结果只能有一条
和你要锁定多条应该是两回事
v1 TABLE1.e%type;
v2 TABLE1.f%type;
v3 TABLE1.g%type;
cursor c1 is SELECT E,F,G FROM TABLE1 WHERE A = B AND C = D FOR UPDATE NOWAIT;
begin
for rec in c1 loop
--do your operation here
end loop;
commit;
end;
BEGIN
SELECT E,F,G
FROM TABLE1
WHERE A = B
AND C = D
FOR UPDATE NOWAIT; DELETE……;
INSERT……;
END;
这样对表TABLE1的锁定,是一直到DELETE,INSERT运行结束END;以后才解除吗?他是什么时候解除锁定的呢?非常感谢了
FROM TABLE1
WHERE A = B
AND C = D
FOR UPDATE NOWAIT;
在pl/sql中行不通。