1-- 2-- ### Verification of several modifying queries 3-- 4-- 5 6 7create table a_sysres ( resid int, instid int); 8create table a_sapinst ( instid int, instname string(10)); 9create table a_sysinst ( instid int, sysid int); 10create table a_system ( sysid int, sysname string(10)); 11 12 13drop if exists procedure loadData; 14@ 15create procedure loadData() 16begin 17 18 delete from a_system; 19 insert into a_system values ( 1, 'SYSA'); 20 insert into a_system values ( 2, 'SYSB'); 21 22 delete from a_sysinst; 23 insert into a_sysinst values ( 1, 1); 24 insert into a_sysinst values ( 2, 1); 25 insert into a_sysinst values ( 3, 1); 26 insert into a_sysinst values ( 4, 1); 27 28 delete from a_sapinst; 29 insert into a_sapinst values ( 1, 'SAP1'); 30 insert into a_sapinst values ( 2, 'SAP2'); 31 insert into a_sapinst values ( 3, 'SAP3'); 32 insert into a_sapinst values ( 4, 'SAP3'); 33 34 delete from a_sysres; 35 insert into a_sysres values ( 1, 1 ); 36end; 37@ 38 39drop if exists procedure checkMod001; 40@ 41create procedure checkMod001(msg out string(20)) return string(10) 42begin 43 44 var res string(10) = 'ERROR'; 45 var sysid int; 46 47 var numinst int; 48 var numsapinst int; 49 var numsysres int; 50 var numsys int; 51 52 :sysid = 1; 53 54 -- delete with nested subselect, since one var reference, should be NOT cached 55 delete from a_sysres where instid in ( select instid from a_sysinst where sysid = :sysid ); 56 -- delete with nested static subselect, should be cached ( not really useful, just for cache testing ) 57 delete from a_sapinst where instid in ( select instid from a_sysinst where sysid = 1 ); 58 delete from a_sysinst where sysid = :sysid; 59 delete from a_system where sysid = :sysid; 60 61 :numinst = ( select count(*) from a_sysinst ); 62 :numsapinst = ( select count(*) from a_sapinst ); 63 :numsysres = ( select count(*) from a_sysres ); 64 :numsys = ( select count(*) from a_system ); 65 66 if :numinst = 0 and :numsapinst = 0 and :numsysres = 0 and :numsys = 1 67 then 68 :res = 'ok'; 69 end; 70 71 :msg = 'Delete with sub select in condition'; 72 return :res; 73end; 74@ 75 76drop if exists procedure checkMod002; 77@ 78create procedure checkMod002(msg out string(20)) return string(10) 79begin 80 81 var res string(10) = 'ERROR'; 82 var sysid int; 83 84 :sysid = 1; 85 86 update a_system set sysid = sysid + 1 where sysid = 1 return :sysid = sysid; 87 88 if :sysid = 2 89 then 90 :res = 'ok'; 91 end; 92 93 :msg = 'Update with return'; 94 return :res; 95end; 96@ 97 98 99call loadData(); 100:r = call checkMod001(:msg); 101insert into checklog values ('GATE-D', :msg, :r); 102 103call loadData(); 104:r = call checkMod002(:msg); 105insert into checklog values ('GATE-D', :msg, :r); 106 107 108drop procedure loadData; 109drop procedure checkMod001; 110drop procedure checkMod002; 111 112drop if exists table a_sysres; 113drop if exists table a_sapinst; 114drop if exists table a_sysinst; 115drop if exists table a_system; 116 117 118 119 120