📄 refactions1.out
字号:
20 |LILY2 |GUY |K55 18 |MONICA |GUY |K55 3 |ROBIN |ASHOK |K51 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 7 |TRUONG |HAMID |K55 ij> delete from db2test.emp where exists ( select max(mgrname) from db2test.origemp group by dno having dno in (select dno from db2test.dept D where dno in (select dno from db2test.emp E2 where D. dno = E2.dno)));20 rows inserted/updated/deletedij> select * from db2test.emp order by name, mgrname, dno;C0 |NAME |MGRNAME |DNO --------------------------------------ij> -- "END OF TESTUNIT: 05";-- *************************************************************************-- TESTUNIT : 06-- DESCRIPTION : delete on view with SQ GB Having SQ on-- : modified table -- 3 levels-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 06";-- reset to original rowsdelete from db2test.emp;0 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> create view db2test.vemp (vc0, vname, vmgrname, vdno) as select * from db2test.emp where exists ( select max(mgrname) from db2test.origemp group by dno having dno in (select dno from db2test.dept D where dno in (select dno from db2test.emp E2 where D. dno = E2.dno)));0 rows inserted/updated/deletedij> select * from db2test.vemp order by 2, 3, 4;VC0 |VNAME |VMGRNAME |VDNO--------------------------------------1 |ASHOK |NULL |K51 WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.10 |BOBBIE |HAMID |K55 13 |DAN |ROGER |K52 17 |DON |GUY |K55 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 12 |JIM |ROGER |K52 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 8 |LARRY1 |HAMID |K55 9 |LARRY2 |HAMID |K55 19 |LILY1 |GUY |K55 20 |LILY2 |GUY |K55 18 |MONICA |GUY |K55 3 |ROBIN |ASHOK |K51 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 7 |TRUONG |HAMID |K55 ij> delete from db2test.vemp;ERROR 42Y24: View 'DB2TEST.VEMP' is not updatable. (Views are currently not updatable.) ij> select * from db2test.emp order by name, mgrname, dno;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 10 |BOBBIE |HAMID |K55 13 |DAN |ROGER |K52 17 |DON |GUY |K55 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 12 |JIM |ROGER |K52 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 8 |LARRY1 |HAMID |K55 9 |LARRY2 |HAMID |K55 19 |LILY1 |GUY |K55 20 |LILY2 |GUY |K55 18 |MONICA |GUY |K55 3 |ROBIN |ASHOK |K51 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 06";-- "cleanup";drop view db2test.vemp;0 rows inserted/updated/deletedij> drop table db2test.emp;0 rows inserted/updated/deletedij> drop table db2test.origemp;0 rows inserted/updated/deletedij> drop table db2test.dept;0 rows inserted/updated/deletedij> drop table db2test.origdept;0 rows inserted/updated/deletedij> --rollback;ij> -- #########################################################################-- # TESTCASE NAME : cself302.sql-- # LINE ITEM : Self-referencing subqueries-- # DESCRIPTION : Allow use of subqueries on the same table being-- # : inserted, deleted, or updated. Cursors updated-- # : or delete where current of are now similarly-- # : unrestricted. Also allowed are subqueries-- # : on tables related to the modified table by-- # : referential relationships, either directly or-- # : indirectly.-- # : This file covers cases of delete statements-- # : where the deleted table is connected to other-- # : tables in the query by cascade on delete.-- # : shape of the RI tree is a 1 level star fanout.-- #########################################################################-- "START OF TESTCASE: cself302.sql";autocommit off;ij> -- *************************************************************************-- TESTUNIT : 01-- DESCRIPTION : Create tables db2test.emp-- : insert some data into it-- EXPECTED RESULTS : SQL commands should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 01";create table db2test.dept (c0 int, dno char(3) not null primary key, dname char(10));0 rows inserted/updated/deletedij> create table db2test.origdept (c0 int, dno char(3) not null primary key, dname char(10));0 rows inserted/updated/deletedij> insert into db2test.dept values (1, 'K55', 'DB');1 row inserted/updated/deletedij> insert into db2test.dept values (2, 'K52', 'OFC');1 row inserted/updated/deletedij> insert into db2test.dept values (3, 'K51', 'CS');1 row inserted/updated/deletedij> insert into db2test.origdept select * from db2test.dept;3 rows inserted/updated/deletedij> create table db2test.emp (c0 int, name char(10) not null primary key, mgrname char(10), dno char(3) references db2test.dept on delete cascade );0 rows inserted/updated/deletedij> insert into db2test.emp (c0, name, dno) values (1, 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (2, 'JOHN', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (3, 'ROBIN', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (4, 'JOE1', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (5, 'JOE2', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (6, 'HAMID', 'JOHN', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (7, 'TRUONG', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (8, 'LARRY1', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (9, 'LARRY2', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (10, 'BOBBIE', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (11, 'ROGER', 'ROBIN', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (12, 'JIM', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (13, 'DAN', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (14, 'SAM1', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (15, 'SAM2', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (16, 'GUY', 'JOHN', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (17, 'DON', 'GUY', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (18, 'MONICA', 'GUY', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (19, 'LILY1', 'GUY', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (20, 'LILY2', 'GUY', 'K55');1 row inserted/updated/deletedij> create table db2test.origemp (c0 int, name char(10) not null primary key, mgrname char(10), dno char(3));0 rows inserted/updated/deletedij> insert into db2test.origemp select * from db2test.emp;20 rows inserted/updated/deletedij> create table db2test.secondemp (c0 int, name char(10) not null primary key, mgrname char(10) references db2test.emp on delete cascade, dno char(3) references db2test.origdept on delete cascade );0 rows inserted/updated/deletedij> insert into db2test.secondemp select * from db2test.emp;20 rows inserted/updated/deletedij> commit;ij> -- "END OF TESTUNIT: 01";-- *************************************************************************-- TESTUNIT : 02-- DESCRIPTION : delete where SQ on same table on RI cascade col-- EXPECTED RESULTS : SQL commands should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 02";select * from db2test.dept where dno in (select dno from db2test.emp where mgrname = 'JOHN') order by 2,3;C0 |DNO|DNAME --------------------------1 |K55|DB ij> delete from db2test.dept where dno in (select dno from db2test.emp where mgrname = 'JOHN');1 row inserted/updated/deletedij> select * from db2test.dept order by dno, dname;C0 |DNO|DNAME --------------------------3 |K51|CS 2 |K52|OFC ij> select * from db2test.emp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 13 |DAN |ROGER |K52 12 |JIM |ROGER |K52 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> select * from db2test.secondemp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 13 |DAN |ROGER |K52 12 |JIM |ROGER |K52 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 ij> -- "END OF TESTUNIT: 02";-- *************************************************************************-- TESTUNIT : 03-- DESCRIPTION : delete with 2 levels of SQ and self-ref in 2nd-- : correlated to 1st SQ on foreign key-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 03";-- reset to original rowsdelete from db2test.emp;10 rows inserted/updated/deletedij> delete from db2test.dept;2 rows inserted/updated/deletedij> delete from db2test.secondemp;1 row inserted/updated/deletedij> insert into db2test.dept select * from db2test.origdept;3 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.secondemp select * from db2test.origemp;20 rows inserted/updated/deletedij> select * from db2test.dept where dno in (select dno from db2test.secondemp E where E.dno in (select dno from db2test.emp D where E.dno = D.dno and D.dno = 'K55')) order by 2, 3;C0 |DNO|DNAME --------------------------1 |K55|DB ij> delete from db2test.dept where dno in (select dno from db2test.secondemp E where E.dno in (select dno from db2test.emp D where E.dno = D.dno and D.dno = 'K55'));1 row inserted/updated/deletedij> select * from db2test.dept order by dno, dname;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -