📄 positioneddelupd.out
字号:
ij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- update to row which was subject to searched update-- (row becomes outside of cursor qualification)get cursor c10a as 'select * from t1 where i = 1 for update';ij> next c10a;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> update t1 set i = i + 1;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------2 |1111111111|1.1E12 |11:11:11ij> update t1 set i = i + 2 where current of c10a;ERROR XCL08: Cursor 'C10A' is not on a row.ij> select * from t1;I |V |D |T ------------------------------------------------------2 |1111111111|1.1E12 |11:11:11ij> close c10a;ij> -- restore t1delete from t1;1 row inserted/updated/deletedij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- update to row which was subject to positioned update-- (row becomes outside of cursor qualification)get cursor c11 as 'select * from t1 where i = 1 for update';ij> next c11;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> update t1 set i = i + 1 where current of c11;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------2 |1111111111|1.1E12 |11:11:11ij> update t1 set i = i + 2 where current of c11;ERROR XCL08: Cursor 'C11' is not on a row.ij> select * from t1;I |V |D |T ------------------------------------------------------2 |1111111111|1.1E12 |11:11:11ij> close c11;ij> -- restore t1delete from t1;1 row inserted/updated/deletedij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- update to row which was subject to 2 searched updates-- (1st puts row outside of cursor qualification, 2nd restores it)get cursor c12 as 'select * from t1 where i = 1 for update';ij> next c12;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> update t1 set i = i + 1;1 row inserted/updated/deletedij> update t1 set i = 1;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> update t1 set i = i + 2 where current of c12;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------3 |1111111111|1.1E12 |11:11:11ij> -- negative test - try to update a non-existant columnupdate t1 set notacolumn = i + 1 where current of c12;ERROR 42X14: 'NOTACOLUMN' is not a column in table or VTI 'APP.T1'.ij> close c12;ij> -- restore t1delete from t1;1 row inserted/updated/deletedij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- update column not in SELECT list, but in FOR UPDATE OF listget cursor c13 as 'select i from t1 for update of v';ij> next c13;I -----------1 ij> update t1 set v = '999' where current of c13;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |999 |1.1E12 |11:11:11ij> -- update column not in FOR UPDATE OF list (negative test)update t1 set i = 999 where current of c13;ERROR 42X31: Column 'I' is not in the FOR UPDATE list of cursor 'C13'.ij> select * from t1;I |V |D |T ------------------------------------------------------1 |999 |1.1E12 |11:11:11ij> close c13;ij> -- restore t1delete from t1;1 row inserted/updated/deletedij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- update a non-referenced columnget cursor c14 as 'select i from t1 for update';ij> next c14;I -----------1 ij> update t1 set v = '999' where current of c14;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |999 |1.1E12 |11:11:11ij> close c14;ij> -- restore t1delete from t1;1 row inserted/updated/deletedij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- .update columns in list in order different from the list'sget cursor c15 as 'select i, v from t1 for update of i, v';ij> next c15;I |V ----------------------1 |1111111111ij> update t1 set v = '999', i = 888 where current of c15;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------888 |999 |1.1E12 |11:11:11ij> -- . show that target table name must be used as qualifier, other names not allowedupdate t1 set t1.v = '998' where current of c15;1 row inserted/updated/deletedij> update t1 set t2.v = '997' where current of c15;ERROR 42X55: Table name 'T2' should be the same as 'T1'.ij> select * from t1;I |V |D |T ------------------------------------------------------888 |998 |1.1E12 |11:11:11ij> close c15;ij> -- restore t1delete from t1;1 row inserted/updated/deletedij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- .update only 1 column in the listget cursor c16 as 'select i, v from t1 for update of i, v';ij> next c16;I |V ----------------------1 |1111111111ij> update t1 set v = '999' where current of c16;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |999 |1.1E12 |11:11:11ij> close c16;ij> -- restore t1delete from t1;1 row inserted/updated/deletedij> insert into t1 select * from t1_copy;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- .try to update through a closed cursorget cursor c17 as 'select i, v from t1 for update of i, v';ij> next c17;I |V ----------------------1 |1111111111ij> close c17;ij> update t1 set v = '999' where current of c17;ERROR 42X30: Cursor 'C17' not found. Verify that autocommit is OFF.ij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> -- a positioned update requires a named target table.-- if we prepare the positioned update, close the underlying cursor-- and reopen it on a different table, then the positioned update-- should fail create table t3(c1 int, c2 int);0 rows inserted/updated/deletedij> insert into t3 values (1,1), (2, 1), (3,3);3 rows inserted/updated/deletedij> create table t4(c1 int, c2 int);0 rows inserted/updated/deletedij> insert into t4 select * from t3;3 rows inserted/updated/deletedij> get cursor c1 as 'select c1 from t3 for update of c1';ij> next c1;C1 -----------1 ij> prepare u1 as 'update t3 set c1 = c1 + 1 where current of c1';ij> execute u1;1 row inserted/updated/deletedij> next c1;C1 -----------2 ij> select * from t3;C1 |C2 -----------------------2 |1 2 |1 3 |3 ij> close c1;ij> get cursor c1 as 'select c1 from t4 for update of c1';ij> next c1;C1 -----------1 ij> execute u1;ERROR 42X29: Update table 'T3' is not the target of cursor 'C1'.ij> select * from t4;C1 |C2 -----------------------1 |1 2 |1 3 |3 ij> select * from t3;C1 |C2 -----------------------2 |1 2 |1 3 |3 ij> close c1;ij> -- now, reopen c1 on a table without column c1 and see-- what happens on an attempted positioned updateget cursor c1 as 'select * from t1 for update';ij> next c1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> execute u1;ERROR 42X29: Update table 'T3' is not the target of cursor 'C1'.ij> close c1;ij> -- now, reopen c1 on t3, but as a read only cursorselect * from t3;C1 |C2 -----------------------2 |1 2 |1 3 |3 ij> get cursor c1 as 'select c1 from t3 ';ij> next c1;C1 -----------2 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -