📄 positioneddelupd.out
字号:
I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> delete from t1 where current of c12;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------ij> close c12;ij> -- restore t1delete from t1;0 rows 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> -- positioned delete on table with index (#724)create table t5 (c1 int, c2 int);0 rows inserted/updated/deletedij> insert into t5 values (1, 1), (2, 2), (3, 3), (4, 4);4 rows inserted/updated/deletedij> commit;ij> create index i5 on t5(c1);0 rows inserted/updated/deletedij> get cursor c1 as 'select * from t5 where c1 > 1 for update of c2';ij> next c1;C1 |C2 -----------------------2 |2 ij> delete from t5 where current of c1;1 row inserted/updated/deletedij> next c1;C1 |C2 -----------------------3 |3 ij> next c1;C1 |C2 -----------------------4 |4 ij> delete from t5 where current of c1;1 row inserted/updated/deletedij> select * from t5;C1 |C2 -----------------------1 |1 3 |3 ij> close c1;ij> rollback;ij> create index i5 on t5(c2);0 rows inserted/updated/deletedij> get cursor c1 as 'select * from t5 where c1 > 1 for update of c2';ij> next c1;C1 |C2 -----------------------2 |2 ij> delete from t5 where current of c1;1 row inserted/updated/deletedij> next c1;C1 |C2 -----------------------3 |3 ij> next c1;C1 |C2 -----------------------4 |4 ij> delete from t5 where current of c1;1 row inserted/updated/deletedij> select * from t5;C1 |C2 -----------------------1 |1 3 |3 ij> close c1;ij> rollback;ij> -- reset autocommitautocommit on;ij> -- drop the tablesdrop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t5;0 rows inserted/updated/deletedij> drop table t1_copy;0 rows inserted/updated/deletedij> -- ** insert positionedUpdate.sql---- tests for positioned update---- note that comments that begin '-- .' are test cases from the test plan-- assumed available in queries at time of initial writing:-- subqueries. Additional tests will be needed once we have:-- order by, group by, having, aggregates, distinct, views ...-- setup some tables for use in the testscreate table t1 ( i int, v varchar(10), d double precision, t time );0 rows inserted/updated/deletedij> create table t1_copy ( i int, v varchar(10), d double precision, t time );0 rows inserted/updated/deletedij> create table t2 ( s smallint, c char(10), r real, ts timestamp );0 rows inserted/updated/deletedij> -- populate the first table and copyinsert into t1 values (1, '1111111111', 11e11, time('11:11:11'));1 row inserted/updated/deletedij> insert into t1_copy select * from t1;1 row inserted/updated/deletedij> -- we need to turn autocommit off so that cursors aren't closed before-- the positioned statements against them.autocommit off;ij> -- empty table tests-- .no table name given-- this should fail with a syntax errorupdate set c1 = c1;ERROR 42X01: Syntax error: Encountered "set" at line 5, column 8.ij> -- this should succeedget cursor c0 as 'select * from t1 for update';ij> next c0;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> update t1 set i = 999 where current of c0;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------999 |1111111111|1.1E12 |11:11:11ij> update t1 set i = 1 where current of c0;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> close c0;ij> -- .same table name-- .cursor before 1st rowget cursor c1 as 'select * from t2 for update';ij> -- 'cursor not on a row' expectedupdate t2 set s = s where current of c1;ERROR XCL08: Cursor 'C1' is not on a row.ij> -- .different table nameupdate t1 set i = i where current of c1;ERROR 42X29: Update table 'T1' is not the target of cursor 'C1'.ij> -- .non-existant tableupdate not_exists set i = i where current of c1;ERROR 42X29: Update table 'NOT_EXISTS' is not the target of cursor 'C1'.ij> close c1;ij> -- .update base table, not exposed table name-- (this one should work, since base table)get cursor c2 as 'select * from t2 asdf for update';ij> update t2 set s = s where current of c2;ERROR XCL08: Cursor 'C2' is not on a row.ij> -- .match correlation name-- (this one should fail, since correlation name)update asdf set s = s where current of c2;ERROR 42X29: Update table 'ASDF' is not the target of cursor 'C2'.ij> close c2;ij> -- .non-updatable cursor-- NOTE - forupdate responsible for extensive testsget cursor c3 as 'select * from t2 for read only';ij> update t2 set s = s where current of c3;ERROR 42X23: Cursor C3 is not updatable.ij> close c3;ij> -- .target cursor does not existupdate t2 set s = s where current of c44;ERROR 42X30: Cursor 'C44' not found. Verify that autocommit is OFF.ij> -- .target cursor after last rowget cursor c4 as 'select * from t1 for update';ij> next c4;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> next c4;No current rowij> next c4;No current rowij> update t1 set i = i where current of c4;ERROR XCL08: Cursor 'C4' is not on a row.ij> close c4;ij> -- .target cursor exists, closedget cursor c5 as 'select * from t1';ij> close c5;ij> update t1 set i = i where current of c5;ERROR 42X30: Cursor 'C5' not found. Verify that autocommit is OFF.ij> -- .target cursor on rowget cursor c6 as 'select * from t1 for update';ij> next c6;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> update t1 set i = i + 1 where current of c6;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------2 |1111111111|1.1E12 |11:11:11ij> -- .consecutive updates to same row in cursor, keeping it in the cursor qualupdate t1 set i = i + 1 where current of c6;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------3 |1111111111|1.1E12 |11:11:11ij> close c6;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> -- .target cursor on row deleted by another cursorget cursor c7 as 'select * from t1 for update';ij> next c7;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> get cursor c8 as 'select * from t1 for update';ij> next c8;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> delete from t1 where current of c7;1 row inserted/updated/deletedij> update t1 set i = i + 1 where current of c8;ERROR XCL08: Cursor 'C8' is not on a row.ij> select * from t1;I |V |D |T ------------------------------------------------------ij> close c7;ij> close c8;ij> -- restore t1delete from t1;0 rows 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> -- .target cursor on already deleted rowget cursor c9 as 'select * from t1 for update';ij> next c9;I |V |D |T ------------------------------------------------------1 |1111111111|1.1E12 |11:11:11ij> delete from t1 where current of c9;1 row inserted/updated/deletedij> update t1 set i = i + 1 where current of c9;ERROR XCL08: Cursor 'C9' is not on a row.ij> select * from t1;I |V |D |T ------------------------------------------------------ij> close c9;ij> -- restore t1delete from t1;0 rows 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 searched update-- (row still within cursor qualification)get cursor c10 as 'select * from t1 for update';ij> next c10;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 c10;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------4 |1111111111|1.1E12 |11:11:11ij> close c10;ij> -- restore t1delete from t1;1 row inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -