We already looked at utPLSQL’s cursor comparison with user-defined types. If we have to deal with updatable views, we can use an even simpler approach and create nested table collections from %ROWTYPEs.
The scenario is similar to the previous one: we have a number of planets with or without garrisons. Now we want to be able to update the view with a garrison-ID which then automatically creates a new garrison-entry.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | /* Setup base data objects */ create table planets ( id integer primary key , name varchar (256) ); create table garrisons ( id integer primary key , fk_planet integer not null , constraint garrisons_fk_planet foreign key ( fk_planet ) references planets( id ) ); /* This is the view we want to test, a list of all planets and their garrisons (if they have some) */ create or replace view v_planet_garrisons as select p.id planet_id, p. name planet_name, count (g.id) over (partition by p.id) planet_num_of_garrisons, g.id garrison_id from planets p left outer join garrisons g on p.id = g.fk_planet ; /* Lets first create our test */ create or replace package ut_garrisons as -- %suite(Garrisons) -- %test(Update V_PLANET_GARRISONS: Add garrison to a planet without assigned garrisons) procedure update_view_set_garrison; /* We can define a nested table of a %ROWTYPE */ type t_v_planet_garrisons is table of v_planet_garrisons%rowtype; end ; / create or replace package body ut_garrisons as /* Helper-Function to get a cursor of the view by Planet-ID */ function cursor_v_garrisons( i_id simple_integer) return sys_refcursor as c_result sys_refcursor; begin open c_result for select * from v_planet_garrisons where planet_id = i_id; return c_result; end ; /* Helper-Function to get a cursor from a given ROWTYPE-object */ function cursor_from_row( i_row v_planet_garrisons%rowtype ) return sys_refcursor as /* We first need an initialized collection */ l_table t_v_planet_garrisons := t_v_planet_garrisons(); c_result sys_refcursor; begin /* We can then put our given row inside that collection */ l_table.extend; l_table(1) := i_row; /* And then create a cursor on it */ open c_result for select * from table (l_table); return c_result; end ; procedure update_view_set_garrison as l_expected_row v_planet_garrisons%rowtype; begin -- Arrange insert into planets values (-1, 'Dromund Kaas' ); /* We store the current row-value in a variable we can manipulate */ select * into l_expected_row from v_planet_garrisons where planet_id = -1; -- Act: Update the view, setting a garrison-ID update v_planet_garrisons /* In a realistic scenario garrison_id could be populated by a sequence */ set garrison_id = -2 where planet_id = -1; -- Assert /* We now define how our expected row should look like */ l_expected_row.garrison_id := -2; l_expected_row.planet_num_of_garrisons := 1; ut.expect(cursor_v_garrisons(-1)) .to_equal(cursor_from_row(l_expected_row)); end ; end ; / /* It fails because of a missing trigger */ call ut.run( 'ut_garrisons' ); /* which we can easily add */ create or replace trigger trg_save_planet_garrisons instead of update or insert or delete on v_planet_garrisons for each row declare begin if ( updating ) then if ( :old.garrison_id is null and :new.garrison_id is not null ) then insert into garrisons (id, fk_planet) values ( :new.garrison_id, :new.planet_id); end if; end if; end ; / /* It doesnt fail anymore */ call ut.run( 'ut_garrisons' ); |
Unfortunately, utPLSQL is still not running in LiveSQL, but you can get the whole example as always from my GitHub repository.
Why I learned this
For updatable views are a major part of the public API at my work, I have to deal a lot with them. Having an easy way to compare single rows of a view with an expected outcome is a great enhancement.
0 Comments