📄 positioneddelupd.out
字号:
ij> -- ** insert positionedDelete.sql---- tests for positioned delete---- 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 errordelete;ERROR 42X01: Syntax error: Encountered "<EOF>" at line 5, column 6.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> delete from t1 where current of c0;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------ij> close c0;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> -- .same table name-- .cursor before 1st rowget cursor c1 as 'select * from t2 for update';ij> -- 'cursor not on a row' expecteddelete from t2 where current of c1;ERROR XCL08: Cursor 'C1' is not on a row.ij> -- .different table namedelete from t1 where current of c1;ERROR 42X28: Delete table 'T1' is not target of cursor 'C1'.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> -- .non-existant tabledelete from not_exists where current of c1;ERROR 42X28: Delete table 'NOT_EXISTS' is not target of cursor 'C1'.ij> close c1;ij> -- .delete from base table, not exposed table name-- (this one should work, since base table)get cursor c2 as 'select * from t2 asdf for update';ij> delete from t2 where current of c2;ERROR XCL08: Cursor 'C2' is not on a row.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> -- .match correlation name-- (this one should fail, since correlation name)delete from asdf where current of c2;ERROR 42X28: Delete table 'ASDF' is not 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> delete from t2 where current of c3;ERROR 42X23: Cursor C3 is not updatable.ij> close c3;ij> -- .target cursor does not existdelete from t2 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> delete from t1 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> delete from t1 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> delete from t1 where current of c6;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------ij> close c6;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 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> delete from t1 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> delete from t1 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> -- delete 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> delete from t1 where current of c10;1 row inserted/updated/deletedij> select * from t1;I |V |D |T ------------------------------------------------------ij> close c10;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> -- delete 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> delete from t1 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> -- delete 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> delete from t1 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> -- delete 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;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -