You need to install utPLSQL package. It is a great project which will make all of this much easier. There is an Install guide, a lot of Examples and of course a Documentation on projects GitHub. On my DEV environment I use headless installation to "UT3" schema and I suggest you to start with that.
There is just few patterns you need.
- test single value (including NULLs)
- test multiple values (whole data sets - multiple rows and columns)
- test if exception was raised
Let's go thru first example:
CREATE OR REPLACE PACKAGE BODY player_ut AS test_player_name players.player_name%TYPE := 'PLAYER_NAME'; PROCEDURE test_create_player AS curr_player_name players.player_name%TYPE; BEGIN -- delete player just to be sure player.delete_player ( in_player_id => player.get_id(test_player_name), in_silent => TRUE ); -- player.create_player ( in_player_name => test_player_name ); -- player is deleted in created during before_each procedure SELECT MIN(player_name) INTO curr_player_name FROM players WHERE player_id = test_player_name AND is_active = 'Y'; -- ut.expect(curr_player_name).to_equal(test_player_name); END; END; /
So you make sure player don't exist, then run the procedure you want to test (create_player) and then check the result, specifically if correct value exist in Player table. That was not that hard, wasn't it?
Here are some of the checks you can do instead of to_equal(...):
As you see, checking single value is easy. There are plenty options/checks available and you should explore Expectation concepts for the exhaustive list and details.
Multiple values (data sets, cursors)
Let's go thru another example:
CREATE OR REPLACE PACKAGE BODY player_ut AS test_player_name players.player_name%TYPE := 'PLAYER_NAME'; PROCEDURE test_notifications AS r_current SYS_REFCURSOR; r_expected SYS_REFCURSOR; BEGIN -- cleanup first DELETE FROM notifications WHERE player_id = test_player_name AND notification_id LIKE 'TEST%'; -- issue 2 notifications player.trigger_notification('TEST 1'); player.trigger_notification('TEST 2'); -- create cursors to compare before and after data OPEN r_current FOR SELECT notification_id, counter FROM notifications WHERE player_id = test_player_name AND notification_id LIKE 'TEST%' AND delivered_at IS NULL; -- OPEN r_expected FOR SELECT 'TEST 1' AS notification_id, 1 AS counter FROM DUAL UNION ALL SELECT 'TEST 2' AS notification_id, 2 AS counter FROM DUAL; -- ut.expect(r_current).to_equal(r_expected); END; END; /
Goal here is to check if user notifications works. We delete existing test notifications first, then issue two new ones. Then we compare current data set with the expected results as two cursors. Is this complicated? You can check pretty much anything with this concept.
There are more comparing options available:
To catch exceptions you must mark your test procedure:
CREATE OR REPLACE PACKAGE player_ut AS -- %test -- %throws(PROGRAM_ERROR) PROCEDURE test_create_player#duplicite_name; END; /
Let's try to raise exception and catch it in test:
CREATE OR REPLACE PACKAGE BODY player_ut AS test_player_name players.player_name%TYPE := 'PLAYER_NAME'; PROCEDURE test_create_player#duplicite_name AS BEGIN -- create player to be sure it exists player.create_player ( in_player_name => test_player_name ); -- create player with existing name must fail player.create_player ( in_player_name => test_player_name ); END; END; /
Thats all. If it raises PROGRAM_ERROR exception (specified in test package specification, the throws(...) line) then test will pass. Otherwise the test will fail.
Now it is your turn. You should play with these on your own.
See you on the next part.