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

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.