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.
/* 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.