Home > SQL Developer: Unit Testing > Example of Unit Testing (Tu...
This section presents a simplified example in which you create a table and a PL/SQL procedure, create unit tests with test cases for valid and invalid input data, run the unit tests, and create and run a unit test suite. It assumes that you have a table of employee data that includes salary information, and that you need to create a procedure to award bonuses to sales representatives, whose pay consists of a base salary plus a commission-based bonus.
The EMPLOYEES table includes the following columns, all of type NUMBER:
EMPLOYEE_ID: Employee identification (badge) number.
COMMISSION_PCT: Commission percentage for the employee: a decimal fraction representing the percentage of the amount of sales by the employee, to be used to compute a bonus that will be added to the employee's base salary to determine the total salary. For example, 0.2 or .2 indicates a 20 percent commission, or 0.2 times the amount of sales.
Only employees in the Sales department have numeric COMMISSION_PCT values. Other employees (not "on commission") have null COMMISSION_PCT values.
SALARY: Salary amount for the employee; includes base salary plus any bonus (which will be calculated by an award_bonus
procedure, to be created during this example).
Assume that the following data exists in these columns in the EMPLOYEES table:
EMPLOYEE_ID | COMMISSION_PCT | SALARY |
---|---|---|
1001 | 0.2 | 8400 |
1002 | 0.25 | 6000 |
1003 | 0.3 | 5000 |
1004 | (null) | 10000 |
You create a procedure named AWARD_BONUS, which has two input parameters:
emp_id
: The employee ID of an employee.
sales_amt
: The amount of sales with which the employee is credited for the period in question.
This amount is calculated using the COMMISSION_PCT value for the specified employee, and the result is added to the SALARY value for that employee.
If the COMMISSION_PCT is null for the employee, no commission or bonus can be calculated, and an exception is raised. This scenario occurs if an attempt is made to add a commission-based bonus to the salary of an employee who is not in the Sales department.
The rest of this example involves the following major steps:
This tutorial uses a table named EMPLOYEES, which must exist before you run any unit tests of the AWARD_BONUS procedure. This table contains some of the columns used in the HR.EMPLOYEES table that is included in the Oracle-supplied sample schemas, but it does not contain all of the columns, and it contains fewer rows and different data.
You can create this EMPLOYEES table in an existing schema and using an existing database connection, or you can create a new schema and connection for the table. To create and populate this table, enter the following statements in a SQL Worksheet or a SQL*Plus command window:
-- Connect as the database user that will be used to run the unit tests. -- Then, enter the following statements: CREATE TABLE employees (employee_id NUMBER PRIMARY KEY, commission_pct NUMBER, salary NUMBER); INSERT INTO employees VALUES (1001, 0.2, 8400); INSERT INTO employees VALUES (1002, 0.25, 6000); INSERT INTO employees VALUES (1003, 0.3, 5000); -- Next employee is not in the Sales department, thus is not on commission. INSERT INTO employees VALUES (1004, null, 10000); commit;
Create the AWARD_BONUS procedure in the same schema as the EMPLOYEES table. In a SQL Worksheet using the appropriate database connection, enter the following text:
create or replace PROCEDURE award_bonus ( emp_id NUMBER, sales_amt NUMBER) AS commission REAL; comm_missing EXCEPTION; BEGIN SELECT commission_pct INTO commission FROM employees WHERE employee_id = emp_id; IF commission IS NULL THEN RAISE comm_missing; ELSE UPDATE employees SET salary = salary + sales_amt*commission WHERE employee_id = emp_id; END IF; END award_bonus; /
Click the Run Script icon (or press F5) to create the AWARD_BONUS procedure.
You will need a unit testing repository in the database to hold schema objects that you create and that SQL Developer will maintain. You can create a separate database user for this repository or use the schema of an existing database user; however, to simplify your learning and any possible debugging you may need to do later, it is recommended that you use a separate schema for the unit testing repository, and the instructions in this section reflect this approach.
Create a database user (for example, UNIT_TEST_REPOS) for the unit testing repository. Using a database connection with DBA privileges, right-click Other Users in the Connections navigator and select Create User. Specify UNIT_TEST_REPOS as the user name, and complete any other required information.
For Default Tablespace, specify USERS; for Temporary Tablespace, specify TEMP.
For System Privileges, enable CREATE SESSION; then click Apply, then Close.
Create a database connection for the unit testing repository user that you created, as follows. Click Tools, then Unit Test, then Manage Users. In the Select Connection dialog box, click the plus (+) icon to create a new database connection (for example, unit_test_repos) for the unit testing repository user.
Click Save to save the connection, then Cancel to close the dialog box.
Create the repository in the schema of the user that you created, as follows. Click Tools, then Unit Test, then Select Current Repository. Specify the database connection (for example, unit_test_repos) for the unit testing repository user. When you see a message that no repository exists for that connection, follow the prompts to create a new repository.
SQL Developer will display several prompts so it can execute commands that grant the necessary privileges to the unit test repository user. In each case, click Yes, and enter the SYS account password when prompted.
To create the first unit test, use the Unit Test navigator. If this navigator is not visible on the left side, click View, then Unit Test. The Unit Test navigator is described in SQL Developer User Interface for Unit Testing.
In the Unit Test navigator, right-click the Tests node and select Create Test.
The Unit Testing: Create Unit Test wizard is displayed. In the remaining steps, click Next to go from each step to the next; and when you are finished specifying the unit test, click Finish.
In Select Operation, select the database connection for the schema that you used to create the AWARD_BONUS procedure; then expand the Procedures node and select AWARD_BONUS.
In Specify Test Name, for Test Name specify AWARD_BONUS (same as the procedure name), and select Create with single dummy representation.
In Specify Startup, click the plus (+) icon to add a startup action; and for the action select Table or Row Copy because you want to save the current data values in the EMPLOYEES table before any data is modified by the unit test.
When prompted, for Source Table specify EMPLOYEES, and for Target Table accept the default name provided for a temporary table that will be automatically created when it is needed and deleted when it is no longer needed. (The target table will be created; and if a table already exists with the name that you specify as the target table, it will be overwritten.)
In Specify Parameters, change the values in the Input column to the following:
For Parameter EMP_ID: 1001
For Parameter SALES_AMT: 5000
For Expected Result, leave the value as Success
.
In Specify Validations, click the plus (+) icon and select Query returning row(s).
For the query, replace the SELECT statement in the Process Validation box with the following (any semicolon at the end of the statement is ignored):
SELECT * FROM employees WHERE employee_id = 1001 AND salary = 9400
That is, because employee 1001 has a 20 percent (0.2) commission and because the sales amount was specified as 5000, the bonus is 1000 (5000 * 0.2), and the new salary for this employee is 9400 (8400 base salary plus 1000 bonus). In this case, the query returns one row, and therefore the result of the validation action is success.
Note that you could have instead specified the SELECT statement in this step using variable replacement (explained in Using Variable Substitution in Validation Actions), as follows:
SELECT * FROM employees WHERE employee_id = {EMP_ID} AND salary = 9400
However, in this specific example scenario, using variable substitution would provide no significant advantage.
In Specify Teardown, select Table or Row Restore because you want to restore the original data values in the EMPLOYEES table before any data was modified by the unit test. When prompted, accept the supplied values for Target Table (EMPLOYEES) and Source Table (the name of the temporary table).
In Summary, review the information. If you need to change anything, click Back as needed and make the changes, then proceed to this Summary page. When you are ready to complete the unit test definition, click Finish.
To run the unit test, use the Unit Test navigator. If this navigator is not visible on the left side, click View, then Unit Test. The Unit Test navigator is described in SQL Developer User Interface for Unit Testing.
In the Unit Test navigator, expand the Tests node and click the AWARD_BONUS test.
A pane for the AWARD_BONUS test is displayed, with Details and Results tabs.
On the Details tab, near the top-right corner, select the database connection for the schema that you used to create the AWARD_BONUS procedure.
Do not change any other values. (However, if you later want to run the unit test with different specifications or data values, you can click the Edit (pencil) icon in the Code Editor toolbar at the top of the pane.)
Click the Run Test (green arrowhead) icon in the Code Editor toolbar (or press F9).
At this point, focus is shifted to the Results tab, where you can soon see that the AWARD_BONUS ran successfully.
If you want to check the EMPLOYEES table data, you will see that the salary for employee 1001 is the same as it was before (8400), because the startup action for the unit test copied the original data to the temporary table and the teardown action restored the original data to the EMPLOYEES table.
Create another unit test for the exception condition where the COMMISSSION_PCT value is null for the employee, and therefore no commission or bonus can be calculated. For this tutorial, the test data includes employee 1004 with a null commission percentage. (This condition could result from several possible scenarios, the most likely being an attempt to run the procedure on a salaried employee who is not eligible for commissions.)
The steps for creating this exception unit test are similar to those in Create a Unit Test, except there are no startup or teardown steps because this test should not modify any table data, and there is no need for any validation action.
In the Unit Test navigator, right-click the Tests node and select Create Test.
The Unit Testing: Create Unit Test wizard is displayed. Click Next to go from each step to the next; and when you are finished specifying the unit test, click Finish.
In Select Operation, select the database connection for the schema that you used to create the AWARD_BONUS procedure; then expand the Procedures node and select AWARD_BONUS.
In Specify Test Name, for Test Name specify AWARD_BONUS_NO_COMM_EXC, and select Create with single dummy representation.
In Specify Startup, click Next to go to the next page.
In Specify Parameters, change the values in the Input column to the following:
EMP_ID: 1004
SALES_AMT: 5000
For Expected Result, change the value to Exception
and leave the expected error number as ANY
.
In Specify Validations, click Next to go to the next page.
In Specify Teardown, click Next to go to the next page.
In Summary, review the information. If you need to change anything, click Back as needed and make the changes, then proceed to this Summary page. When you are ready to complete the unit test definition, click Finish.
To run this unit test, follow the steps in Run the Unit Test, except specify AWARD_BONUS_NO_COMM_EXC instead of AWARD_BONUS.
On the Results tab, you will see that the AWARD_BONUS_NO_COMM_EXC test ran successfully; and if you check the EMPLOYEES table data, you will see that the information for employee 1004 (and all the other employees) was not changed.
Design Consideration: As an alternative to creating a separate unit test for the exception condition, you could add it as an implementation to the AWARD_BONUS test (right-click AWARD_BONUS and select Add Implementation). Thus, the AWARD_BONUS unit test would have two implementations: the "Default" implementation using employee 1001, and the AWARD_BONUS_NO_COMM_EXC implementation using employee 1004.The approach in this tutorial enables you to create a simple unit test suite using the two unit tests (see Create a Unit Test Suite). However, in more realistic unit testing scenarios, it is probably better to use a unit test for each procedure, add implementations for each test case for a procedure, and group multiple unit tests (for individual procedures) into one or more test suites. |
Create a unit test suite that groups together the two unit tests of the AWARD_BONUS procedure. If the Unit Test navigator is not visible on the left side, click View, then Unit Test. The Unit Test navigator is described in SQL Developer User Interface for Unit Testing.
In the Unit Test navigator, right-click the Suites node and select Add Suite.
In the Unit Testing: Add Test Suite dialog box, specify AWARD_BONUS_SUITE
as the suite name.
In the Unit Test navigator, under Suites, click the AWARD_BONUS_SUITE node.
An pane for the AWARD_BONUS_SUITE test suite is displayed.
Do not specify a Startup Process or Teardown Process, because neither is needed for this test suite.
Click the Add (+) icon to add the first test to the suite.
In the Unit Testing: Add Tests or Suites to a Suite dialog box, click (select) AWARD_BONUS, check (select) Run Test Startups and Run Test Teardowns so that the startup and teardown actions for that unit test will be run, and click OK.
Click the Add (+) icon to add the next test to the suite.
In the Unit Testing: Add Tests or Suites to a Suite dialog box, click (select) AWARD_BONUS_NO_COMM_EXC, and click OK. (The check Run Test Startups and Run Test Teardowns options are irrelevant here because the AWARD_BONUS_NO_COMM_EXC test does not perform any startup and teardown actions.)
Click the Commit Changes icon in the Code Editor toolbar at the top of the pane (or press F11).
To run the unit test suite, use the Unit Test navigator. If you are in the editing pane for the AWARD_BONUS_SUITE test suite, run the suite by clicking the Run Suite (green arrowhead) icon in the Code Editor toolbar. Otherwise, perform the following steps:
In the Unit Test navigator, expand the Suites node and click the AWARD_BONUS_SUITE test suite.
A pane for the AWARD_BONUS_SUITE test is displayed, with Details and Results tabs.
In the Details tab, near the top-right corner, select the database connection for the schema that you used to create the AWARD_BONUS procedure.
Do not change any other values. (However, if you later want to run the unit test suite with different specifications, you can click the Edit (pencil) icon in the Code Editor toolbar at the top of the pane.)
Click the Run Suite (green arrowhead) icon in the Code Editor toolbar (or press F9).
After the suite is run, focus is shifted to the Results tab, where you can soon see that the AWARD_BONUS_SUITE test suite ran successfully.
Related Topics