forked from KenRoytman/utPLSQL
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathtestproc.html
executable file
·280 lines (237 loc) · 8.08 KB
/
testproc.html
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<!-- WARNING! This file is generated. -->
<!-- To alter documentation, edit files in src directory -->
<html><head>
<title>Test a Procedure</title>
<link rel="stylesheet" href="utplsql.css" content="text/css">
<meta name="keywords" content="utPLSQL, PL\SQL, Unit Testing, Framework, Oracle"/>
<meta name="description" content="Unit Testing PL\SQL"/>
<meta name="title" content="Test a Procedure"/>
<meta name="author" content="Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
<meta name="copyright" content="(C) 2000-2005 Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
</head><body>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p>[ <A href="index.html">Home</A>
| <A href="started.html">Getting Started</A>
| <A href="buildpack.html">Build Test Packages</A>
| <A href="examples.html">Examples</A>
| <A href="userguide.html">User Guide</A>
| <A href="release.html">Release Notes</A>
| <A href="map.html">Document Map</A> ]</p>
<p><A href="examples.html">< Previous Section: Examples</A> | <A href="testfunc.html">Next Section: Test a Function ></A></p>
<!-- Begin utPLSQL Body -->
<!-- $Id: testproc.html,v 1.3 2002/07/25 10:30:58 chrisrimmer Exp $ -->
<h1>
Test a Procedure</h1>
<p>There are a couple of scenarios to consider:
<ul>
<li>
The procedure runs some code and then passes back results through the parameter
list. In this case, I can write a unit test that analyzes the OUT and IN
OUT argument values.</li>
The procedure runs some code, which changes
other elements of the application (such as a database table or a file).
The parameter list does not contain arguments that can be analyzed for
successful execution. So to assert success, I will need to analyze/compare
the data structures that have been modified.
</ul>
<h3>
Test Success Through Parameters</h3>
We'll start with a really simple example. I
have built a procedure that accepts two dates and returns the number of
seconds between them. Here it is:
<pre>/*file calc_secs_between.sp */
CREATE OR REPLACE PROCEDURE calc_secs_between (
date1 IN DATE,
date2 IN DATE,
secs OUT NUMBER)
IS
BEGIN
-- 24 hours in a day,
-- 60 minutes in an hour,
-- 60 seconds in a minute...
secs := (date2 - date1) * 24 * 60 * 60;
END;
/</pre>
After compiling my code cleanly, I <a href="utgen.html">generate
my test package</a>:
<pre>SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL> exec utGen.testpkg ('calc_secs_between ')
CREATE OR REPLACE PACKAGE ut_calc_secs_between
IS
PROCEDURE ut_setup;
PROCEDURE ut_teardown;
-- For each program to test...
PROCEDURE ut_CALC_SECS_BETWEEN;
END ut_calc_secs_between;
/
CREATE OR REPLACE PACKAGE BODY ut_calc_secs_between
IS
PROCEDURE ut_setup
IS
BEGIN
NULL;
END;
PROCEDURE ut_teardown
IS
BEGIN
NULL;
END;
-- For each program to test...
PROCEDURE ut_CALC_SECS_BETWEEN IS
BEGIN
CALC_SECS_BETWEEN (
DATE1 => ''
,
DATE2 => ''
,
SECS => ''
);
utAssert.this (
'Test of CALC_SECS_BETWEEN',
'<boolean expression>'
);
END ut_CALC_SECS_BETWEEN;
END ut_calc_secs_between;
/</pre>
I generated the output to the screen, but it
is actually easier to deposit the code directly into two separate files
for package spec and body, ut_calc_secs_between.pks and ut_calc_secs_between.pkb,
which I do as follows:
<pre>SQL> exec utGen.testpkg ('calc_secs_between ', output_type_in => utGen.c_file)</pre>
By conforming to this standard, utPLSQL can
automatically compile this code before each test. I now edit the ut_calc_secs_between
procedure to test for various cases:
<pre><a name="useseq"></a>PROCEDURE ut_CALC_SECS_BETWEEN
IS
secs PLS_INTEGER;
BEGIN
CALC_SECS_BETWEEN (
DATE1 => SYSDATE
,
DATE2 => SYSDATE
,
SECS => secs
);
utAssert.eq (
'Same dates',
secs,
0
);
CALC_SECS_BETWEEN (
DATE1 => SYSDATE
,
DATE2 => SYSDATE+1
,
SECS => secs
);
utAssert.eq (
'Exactly one day',
secs,
24 * 60 * 60
);
END ut_CALC_SECS_BETWEEN;</pre>
and now I can run my test:
<pre>SQL> exec utplsql.test ('calc_secs_between')
.
> SSSS U U CCC CCC EEEEEEE SSSS SSSS
> S S U U C C C C E S S S S
> S U U C C C C E S S
> S U U C C E S S
> SSSS U U C C EEEE SSSS SSSS
> S U U C C E S S
> S U U C C C C E S S
> S S U U C C C C E S S S S
> SSSS UUU CCC CCC EEEEEEE SSSS SSSS
.
SUCCESS: "calc_secs_between"</pre>
Certainly, there are a variety of other conditions
to test, but this should give you a good idea of how to go about it!
<h3>
Test Success by Analyzing Impact</h3>
Now let's consider a more complicated situation.
I have a procedure that truncates all the rows in the specified table.
To do this I just use dynamic SQL, as you can see in:
<pre>/*file truncit.sp */
CREATE OR REPLACE PROCEDURE truncit (
tab IN VARCHAR2,
sch IN VARCHAR2 := NULL
)
IS
BEGIN
EXECUTE IMMEDIATE 'truncate table ' || NVL (sch, USER) || '.' || tab;
END;
/</pre>
After I run this test, I cannot simply check
the value returned by the procedure. Instead, I must check to see how many
rows are left in the table. Fortunately, I have another dynamic SQL utility
to help me out here, one that returns the count of rows in any table:
(Note that you could also use <a href="utassert.html#eqqueryvalue">utAssert.eqqueryvalue</a> here.)
<pre>/*file tabcount.sf */
CREATE OR REPLACE FUNCTION tabcount (
sch IN VARCHAR2,
tab IN VARCHAR2)
RETURN INTEGER
IS
retval INTEGER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || sch || '.' || tab
INTO retval;
RETURN retval;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/</pre>
So I will <a href="utgen.html">generate a package</a>
to test truncit and then modify the package body:
<pre>SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL> exec utGen.testpkg ('truncit', output_type_in => utGen.c_file)</pre>
To run my test, I need to truncate a table.
That is an irreversible action, so I will create a "temporary" table in
the setup procedure and drop it in the teardown procedure. Then I will
run my code and use tabCount to validate the results:
<pre>/*file ut_truncit.pkb */
CREATE OR REPLACE PACKAGE BODY ut_truncit
IS
PROCEDURE ut_setup
IS
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE temp_emp AS SELECT * FROM employee';
END;
PROCEDURE ut_teardown
IS
BEGIN
EXECUTE IMMEDIATE
'DROP TABLE temp_emp';
END;
-- For each program to test...
PROCEDURE ut_TRUNCIT IS
BEGIN
TRUNCIT (
TAB => 'temp_emp'
,
SCH => USER
);
utAssert.eq (
'Test of TRUNCIT',
tabcount (USER, 'temp_emp'),
0
);
END ut_TRUNCIT;
END ut_truncit;
/</pre>
Not quite as straightforward as checking values
returned in OUT or IN OUT arguments, but not too awful, right? Of course,
things can get considerably more complicated as your code (and the results
you must test for) grows more complex. Regardless, you will find it easier
to build and run your tests through utPLSQL than through more ad hoc and
considerably less organized approaches.
<!-- End utPLSQL Body -->
<p><A href="examples.html">< Previous Section: Examples</A> | <A href="testfunc.html">Next Section: Test a Function ></A></p>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p class="copyright">Copyright (C) 2000-2005 <A href="mailto:[email protected]">Steven Feuerstein<A>, <A href="mailto:[email protected]">Chris Rimmer<A>, <A href="mailto:[email protected]">Patrick Barel<A> All rights reserved</p>
</body></html>