----we are buliding a libariay catalog as an example--
first we created the following two tables:
CREATE TABLE books (
isbn VARCHAR2(13) NOT NULL PRIMARY KEY,
title VARCHAR2(200),
summary VARCHAR2(2000),
author VARCHAR2(200)
date_published DATE,
page_count NUMBER
);
CREATE TABLE book_copies(
barcode_id VARCHAR2(100) NOT NULL PRIMARY KEY,
isbn VARCHAR2(13) FOREIGN KEY REFERENCES books (isbn)
);
then we created some fuction and procedure
barcode_id_in IN VARCHAR2, title_in IN VARCHAR2, author_in IN VARCHAR2,
page_count_in IN NUMBER, summary_in IN VARCHAR2 DEFAULT NULL,
date_published_in IN DATE DEFAULT NULL)
AS
BEGIN
/* check for reasonable inputs */
IF isbn_in IS NULL
THEN
RAISE VALUE_ERROR;
END IF;
/* put a record in the "books" table */
INSERT INTO books (isbn, title, summary, author, date_published, page_count)
VALUES (isbn_in, title_in, summary_in, author_in, date_published_in,
page_count_in);
/* if supplied, put a record in the "book_copies" table */
IF barcode_id_in IS NOT NULL
THEN
INSERT INTO book_copies (isbn, barcode_id)
VALUES (isbn_in, barcode_id_in);
END IF;
END add_book;
/
CREATE OR REPLACE FUNCTION book_copy_qty(isbn_in IN VARCHAR2)
RETURN NUMBER
AS
number_o_copies NUMBER := 0;
CURSOR bc_cur IS
SELECT COUNT(*)
FROM book_copies
WHERE isbn = isbn_in;
BEGIN
IF isbn_in IS NOT NULL
THEN
OPEN bc_cur;
FETCH bc_cur INTO number_o_copies;
CLOSE bc_cur;
END IF;
RETURN number_o_copies;
END;
/
-----------------------then here comes the testing issue---------------------------------
3.4 Make Your Code Resilient
You are probably eager to get going on making our sample application do more—so am I! But it is important to first make sure the code we've written so far works as flawlessly as it possibly can. That's why I'm going to take what might seem like a digression.
You've probably heard the expression garbage in, garbage out (GIGO). Maybe you've even uttered this phrase from time to time, or heard it over the phone from support staff; it's supposed to "explain" some nonsensical result (garbage out) by blaming faulty input (garbage in).
But is GIGO an inevitable state of affairs? Most programmers are incorrigible optimists when it comes to thinking about how their programs will be used. The assumption is tidy in, tidy out. Nobody wants to plan for inputs they consider to be "abnormal."
To avoid unanticipated digital squalor, we have to run test cases. To run good test cases means dreaming up various combinations of input data that we hope will break the program. Then we note the expected results...run the program...compare the output...fix the program...rerun the tests. Yep, that's a lot of bookkeeping that I would certainly prefer to avoid.
Hmm, lots of tedious executions of code with different inputs; this sounds like a good opportunity to write some utilities, doesn't it? Let's create one ourselves and see if we can make this testing stuff more fun—or at least automate the tiresome bits.
3.4.1 A Results-Checking Utility
First, I'd like to create a simple way that will compare two values and print out a "pass" message if they're the same, or a "fail" message if they differ. This test is enormously useful, since the basis of testing is comparing the actual output to the expected response. We'll probably use such a procedure every time we run a test. While we're at it, let's throw in a description for the test, so that when we call this a bunch of times back-to-back we can keep up with which tests have failed. Have a look at this "report equality" (reporteq) procedure:
CREATE OR REPLACE PROCEDURE reporteq (description IN VARCHAR2,
expected_value IN VARCHAR2, actual_value IN VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT(description || ': ');
IF expected_value = actual_value
OR (expected_value IS NULL AND actual_value IS NULL)
THEN
DBMS_OUTPUT.PUT_LINE('PASSED');
ELSE
DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value
| '; got ' |
actual_value);
END IF;
END;
/
Note that this version of the procedure compares strings (VARCHAR2s); we can also create similar procedures to handle numbers, dates, and Boolean values.
3.4.2 A "Unit Tester" for add_book
Armed with our first handy-dandy test utility, we will now write a program that will call the add_book procedure in a variety of ways—yes, even sending garbage to it. The hope is that add_book will take out the garbage "properly," and our reporteq program is going to help. I will present the rather long program (87 lines) in pieces to make it easier to understand. Also, I've annotated the code with line numbers to aid in making references to specific lines:
1 DECLARE
2 l_isbn VARCHAR2(13) := '1-56592-335-9';
3 l_title VARCHAR2(200) := 'Oracle PL/SQL Programming';
4 l_summary VARCHAR2(2000) := 'Reference for PL/SQL developers, '
|
5 'including examples and best practice recommendations.';
6 l_author varchar2(200) := 'Feuerstein, Steven, and Bill Pribyl';
7 l_date_published DATE := TO_DATE('01-SEP-1997', 'DD-MON-YYYY');
8 l_page_count NUMBER := 987;
9 l_barcode_id VARCHAR2(100) := '100000001';
10
11 CURSOR bookCountCur IS
12 SELECT COUNT(*) FROM books;
13
14 CURSOR copiesCountCur IS
15 SELECT COUNT(*) FROM book_copies;
16
17 CURSOR bookMatchCur IS
18 SELECT COUNT(*) FROM books
19 WHERE isbn = l_isbn AND title = l_title AND summary = l_summary
20 AND author = l_author AND date_published = l_date_published
21 AND page_count = l_page_count;
22
23 CURSOR copiesMatchCur IS
24 SELECT COUNT(*) FROM book_copies
25 WHERE isbn = l_isbn AND barcode_id = l_barcode_id;
26
27 how_many NUMBER;
28 l_sqlcode NUMBER;
Ah, let me interrupt here to comment a bit.
Lines 2-9. Here are declarations of local variables that hold normal values we can use in various tests. Storing them in variables makes our life a bit easier because we can just reuse the variables. The l_ prefix is a reminder that these are local variables.
Lines 11-12. This is the declaration of the program's first cursor. A cursor enables us to fetch values from the database via a SQL SELECT statement. This particular statement counts how many total records exist in the books table.
Lines 14-15. Similarly, this cursor lets us count the total number of book copies.
Lines 17-21. This cursor counts the number of books whose column values exactly match the values of the local variables.
Line 27. The how_many local variable temporarily stores the result of those "count" queries.
Line 28. The l_sqlcode variable temporarily stores the output from PL/SQL's built-in SQLCODE function; we'll explain that function a bit later in this section.
The execution section, shown in the following code, begins with a deletion of everything in our two database tables. This ensures that any table counts that we do are only counting the new test data, and not data that happens to be lying around from other runs. Obviously, you want to run this on your "scratch" database, not the real thing!
29 BEGIN
30 DELETE book_copies;
31 DELETE books;
32
33 add_book(isbn_in => l_isbn, barcode_id_in => l_barcode_id,
34 title_in => l_title, summary_in => l_summary, author_in => l_author,
35 date_published_in => l_date_published, page_count_in => l_page_count);
36
37 OPEN bookMatchCur;
38 FETCH bookMatchCur INTO how_many;
39 reporteqbool('add procedure, book fetch matches insert',
40 expected_value => TRUE, actual_value => bookMatchCur%FOUND);
41 CLOSE bookMatchCur;
42
Lines 33-41. Now we come to the first actual run of the add_book routine, which supplies all nominal inputs, and which we expect to work. This begins the test to determine if the book added properly. By opening the cursor and fetching from it, we can check to see if the record is present as expected. In lines 39-40 is a call to reporteqbool, a version of reporteq that operates on Boolean rather than string values. If the fetch was successful, bookMatchCur%FOUND will be true (you'll read more about this sort of test in Chapter 5).As line 41 illustrates, it's good practice to close cursors as soon as the program is through with them.
43 BEGIN
44 add_book(isbn_in => NULL, barcode_id_in => 'foo', title_in => 'foo',
45 summary_in => 'foo', author_in => 'foo',
46 date_published_in => SYSDATE, page_count_in => 0);
47 l_sqlcode := SQLCODE;
48 EXCEPTION
49 WHEN OTHERS THEN
50 l_sqlcode := SQLCODE;
51 END;
52
53 reporteq('add procedure, detection of NULL input',
54 expected_value => '-6502', actual_value => TO_CHAR(l_sqlcode));
55
Lines 43-54. Next test: let's try a null isbn to see if the input error detection works. If it does, the procedure is supposed to raise a NO_DATA_FOUND exception. Since we expect to see an exception, we want to put the text in a nested block. That way, we can handle the exception as the very next operation, rather than jumping to the end of the main block.
To be consistent with the other tests, we want to identify a single result variable to compare with the expected result. PL/SQL provides a special built-in function called SQLCODE that will have a non-zero value inside any exception handler. Since we want to use the result code outside the exception handler, line 50 assigns its value to l_sqlcode, which communicates the value as an argument to reporteq in lines 53 and 54.
Line 54 shows that we expect the result code to be -6502. This is the value PL/SQL assigns to SQLCODE when a NO_DATA_FOUND exception occurs.
56 OPEN bookCountCur;
57 FETCH bookCountCur INTO how_many;
58 reporteq('add procedure, book_record count', expected_value => '1',
59 actual_value => how_many);
60 CLOSE bookCountCur;
61
62 OPEN copiesCountCur;
63 FETCH copiesCountCur INTO how_many;
64 reporteq('add procedure, book_copy record count', expected_value => '1',
65 actual_value => how_many);
66 CLOSE copiesCountCur;
67
68 OPEN copiesMatchCur;
69 FETCH copiesMatchCur INTO how_many;
70 reporteqbool('add procedure, book copy fetch matches insert',
71 expected_value => TRUE, actual_value => copiesMatchCur%FOUND);
72 CLOSE copiesMatchCur;
73
Lines 56-72. More tests. These just determine whether the expected number of records exist in the tables.
74 BEGIN
75 add_book(isbn_in => l_isbn, barcode_id_in => l_barcode_id,
76 title_in => l_title, summary_in => l_summary, author_in => l_author,
77 date_published_in => l_date_published,
78 page_count_in => l_page_count);
79 l_sqlcode := SQLCODE;
80 EXCEPTION
81 WHEN OTHERS THEN
82 l_sqlcode := SQLCODE;
83 END;
84 reporteq('add procedure, detection of duplicate isbn',
85 expected_value => '-1', actual_value => l_sqlcode);
86 END;
87 /
Lines 74-85. Now let's test to ensure that attempting to add the same isbn a second time will raise an exception. We expect Oracle to set a SQLCODE of -1, which is what you get when you attempt to insert a record with the same primary key as an existing record. (This is really a test of the database design, but we might as well test it somewhere.)
That's the end of the test. Whew! Now, assuming that we have enabled SERVEROUTPUT (see Chapter 2), running this program from within SQL*Plus yields:
add procedure, book fetch matches insert: PASSED
add procedure, detection of NULL input: PASSED
add procedure, book_record count: PASSED
add procedure, book_copy record count: PASSED
add procedure, book copy fetch matches insert: PASSED
add procedure, detection of duplicate isbn: PASSED
As you may know, this block serves as a unit test.[4] It also serves as a permanent, recorded example of how to call the program, which is always a generous gift to leave to future generations of programmers. (By the way, "future generations" includes you, six months from now, after you've written another 75 programs and completely forgotten about this one!)
[4] The word "unit" refers to the individual program unit; it contrasts with other tests such as integrated tests, which help ensure that program units behave properly when assembled into an application.
3.4.3 Testing the book_copy_qty Function
This next routine is a unit tester for the book_copy_qty function. The principle of operation is the same as the previous unit testing program:
1 DECLARE
2 l_isbn VARCHAR2(13) := '1-56592-335-9';
3 l_isbn2 VARCHAR2(13) := '2-56592-335-9';
4 l_title VARCHAR2(200) := 'Oracle PL/SQL Programming';
5 l_summary VARCHAR2(2000) := 'Reference for PL/SQL developers, ' ||
6 'including examples and best practice recommendations.';
7 l_author varchar2(200) := 'Feuerstein, Steven, and Bill Pribyl';
8 l_date_published DATE := TO_DATE('01-SEP-1997', 'DD-MON-YYYY');
9 l_page_count NUMBER := 987;
10 l_barcode_id VARCHAR2(100) := '100000001';
11 l_barcode_id2 VARCHAR2(100) := '100000002';
12 l_barcode_id3 VARCHAR2(100) := '100000003';
13
14 how_many NUMBER;
15 BEGIN
16 DELETE book_copies;
17 DELETE books;
18
19 reporteq('book_copy_qty function, zero count', '0',
20 TO_CHAR(book_copy_qty(l_isbn)));
21
22 /* Lets assume that add_book is working properly */
23 add_book(isbn_in => l_isbn, barcode_id_in => l_barcode_id,
24 title_in => l_title, summary_in => l_summary, author_in => l_author,
25 date_published_in => l_date_published, page_count_in => l_page_count);
26
27 reporteq('book_copy_qty function, unit count', '1',
28 TO_CHAR(book_copy_qty(l_isbn)));
29
30 add_book_copy(isbn_in => l_isbn, barcode_id_in => l_barcode_id2);
31 add_book_copy(isbn_in => l_isbn, barcode_id_in => l_barcode_id3);
32
33 reporteq('book_copy_qty function, multi count', '3',
34 TO_CHAR(book_copy_qty(l_isbn)));
35
36 reporteq('book_copy_qty function, null ISBN', '0',
37 TO_CHAR(book_copy_qty(NULL)));
38 END;
39 /
Lines 30-31. These are calls to a procedure that I haven't illustrated. All they do is insert a record into the book_copies table.
Running the unit test results in:
book_copy_qty function, zero count: PASSED
book_copy_qty function, non-zero count: PASSED
book_copy_qty function, null ISBN: PASSED
which, of course, is what we had hoped to see.
3.4.4 Why So Much Trouble?
At this point some readers will be wondering why I've gone to so much trouble. Can't your average programmer just have a quick read of the code and see that it will work?
Well, that's the sort of thinking that makes someone an average programmer. That last check for a NULL value in lines 36-37 is a case in point. My final version of the add_book program passed the test, but to tell you the truth, I hadn't thought about this possibility in the original version (not shown in this book). Only when I started writing the unit test did it occur to me that I needed to consider at least three input cases: good, bad, and the eternal troublemaker, NULL. And only by forcing my mind to consider what the test should cover did I realize my omission; I just got lucky that it worked. Thinking about the test helps you traverse a different set of mental pathways, where you can often get a better angle into your code.
Much has been written about the psychology of software testing, but once you start writing your own unit tests, you may get enough insight to write your own book. Well, maybe you won't write a book, but there is something about testing that you just can't internalize until you've been through your own "Aha!" experiences.
Credits: Learning Oracle PL/SQL
Bill Pribyl
Steven Feuerstein
Publisher: O'Reilly
First Edition December 2001
ISBN: 0-596-00180-0, 424 pages
