📄 positioneddelupd.out
字号:
ij> execute u1;ERROR 42X23: Cursor C1 is not updatable.ij> select * from t3;C1 |C2 -----------------------2 |1 2 |1 3 |3 ij> close c1;ij> -- positioned update 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> update t5 set c2 = 9 where current of c1;1 row inserted/updated/deletedij> next c1;C1 |C2 -----------------------3 |3 ij> next c1;C1 |C2 -----------------------4 |4 ij> update t5 set c2 = 9 where current of c1;1 row inserted/updated/deletedij> select * from t5;C1 |C2 -----------------------1 |1 2 |9 3 |3 4 |9 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> update t5 set c2 = 9 where current of c1;1 row inserted/updated/deletedij> next c1;C1 |C2 -----------------------3 |3 ij> next c1;C1 |C2 -----------------------4 |4 ij> update t5 set c2 = 9 where current of c1;1 row inserted/updated/deletedij> select * from t5;C1 |C2 -----------------------1 |1 2 |9 3 |3 4 |9 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 t3;0 rows inserted/updated/deletedij> drop table t4;0 rows inserted/updated/deletedij> drop table t5;0 rows inserted/updated/deletedij> drop table t1_copy;0 rows inserted/updated/deletedij> -- tests for beetle 4417, schema and correlation name not working with-- current ofcreate schema ejb;0 rows inserted/updated/deletedij> create table ejb.test1 (primarykey varchar(41) not null primary key, name varchar(200), parentkey varchar(41));0 rows inserted/updated/deletedij> insert into ejb.test1 values('0','jack','jill');1 row inserted/updated/deletedij> autocommit off;ij> -- test update with schema nameget cursor c1 as 'select primarykey, parentkey, name from ejb.test1 where primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |jack ij> prepare p1 as 'update ejb.test1 set name = ''john'' where current of c1';ij> execute p1;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |john ij> close c1;ij> -- test update with schema name and correlation nameget cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |john ij> prepare p1 as 'update ejb.test1 set name = ''joe'' where current of c1';ij> execute p1;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |joe ij> close c1;ij> -- test update with set schemaset schema ejb;0 rows inserted/updated/deletedij> get cursor c1 as 'select primarykey, parentkey, name from test1 where primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |joe ij> prepare p1 as 'update test1 set name = ''john'' where current of c1';ij> execute p1;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |john ij> close c1;ij> -- test update with set schema and correlation nameget cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |john ij> prepare p1 as 'update test1 set name = ''joe'' where current of c1';ij> execute p1;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |joe ij> close c1;ij> -- test update with set schema and correlation name and schema nameget cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |joe ij> prepare p1 as 'update ejb.test1 set name = ''joe'' where current of c1';ij> execute p1;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |joe ij> close c1;ij> -- -- reset schema nameset schema app;0 rows inserted/updated/deletedij> -- test delete with schema name get cursor c1 as 'select primarykey, parentkey, name from ejb.test1 where primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |joe ij> prepare p2 as 'delete from ejb.test1 where current of c1';ij> execute p2;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> close c1;ij> -- test delete with schema name and correlation nameinsert into ejb.test1 values('0','jack','jill');1 row inserted/updated/deletedij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |jack ij> prepare p2 as 'delete from ejb.test1 where current of c1';ij> execute p2;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> close c1;ij> -- test delete with set schema set schema ejb;0 rows inserted/updated/deletedij> insert into test1 values('0','jack','jill');1 row inserted/updated/deletedij> get cursor c1 as 'select primarykey, parentkey, name from test1 where primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |jack ij> prepare p2 as 'delete from test1 where current of c1';ij> execute p2;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> close c1;ij> -- test delete with set schema and correlation nameinsert into test1 values('0','jack','jill');1 row inserted/updated/deletedij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |jack ij> prepare p2 as 'delete from test1 where current of c1';ij> execute p2;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> close c1;ij> -- test delete with set schema and correlation name and schema nameinsert into test1 values('0','jack','jill');1 row inserted/updated/deletedij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |jack ij> prepare p2 as 'delete from ejb.test1 where current of c1';ij> execute p2;1 row inserted/updated/deletedij> select primarykey, parentkey, name from ejb.test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> close c1;ij> commit;ij> -- clean upautocommit on;ij> set schema app;0 rows inserted/updated/deletedij> drop table ejb.test1;0 rows inserted/updated/deletedij> --drop schema ejb restrict; - can't drop this because it will fail SPS tests since--statements are created and would need to be dropped-- test correlation on select in current of cursor in current schema-- this was also brokencreate table test1 (primarykey varchar(41) not null primary key, name varchar(200), parentkey varchar(41));0 rows inserted/updated/deletedij> -- make sure a cursor will work fine in this situationinsert into test1 values('0','jack','jill');1 row inserted/updated/deletedij> autocommit off;ij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update';ij> next c1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |jill |jack ij> prepare p2 as 'delete from test1 where current of c1';ij> execute p2;1 row inserted/updated/deletedij> select primarykey, parentkey, name from test1;PRIMARYKEY |PARENTKEY |NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> close c1;ij> commit;ij> -- clean upautocommit on;ij> drop table test1;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -