TDD Tutorial 1
|
TDD Tutorial 1A Test Driven Development Example
What follows is a very simple and contrived example of how TDD is meant to work, and will simultaneously show you how to use the unit testing features of Apollo Pro. For the example, you will need access to the SCOTT.EMP table, and have installed Apollo Pro (which is freeware available here). Alternatively, you can create the table and its data by executing the script below.
The need for code
All software development is driven by a customers need for some functionality. For this example, our customer has asked for a query that shows the percentage of commission each employee in the company made last year. You've decided that the best way to accomplish this task is to write a stored function to do the calculation for you.
First write a failing test
The first step in all test driven development is to write a test that does not pass. In essence, you are proving that you need to write the code at all. Lets write our first PL/Unit test. Open Apollo Pro, and logon to a session that has access to the EMP table used in our example. This session will also need the create package and create function database privileges. We'll need two edit pages, so create a second one by right clicking on the first tab and choosing New Page. In the first editor page, write the following code:
CREATE OR REPLACE PACKAGE test_calc_comm_percent AS
-- PL/Unit tests are implemented as packages
PROCEDURE t_call_function;
-- unit tests are public procedures that have no parameters
-- their names must begin with t_
END;
/
CREATE OR REPLACE PACKAGE BODY test_calc_comm_percent AS
PROCEDURE t_call_function IS
-- we write our test logic in the package body
BEGIN
-- our call to assert equals is taking 3 parameters.
-- The first is the value we expect, the second is the actual value (our function result)
-- the third is the optional error message to supply if the test fails.
plunit.assert_equals(0.10, calc_comm_percent(1000, 100), 'Commission percent is wrong');
END;
END;
/
Now switch to the second edit page and write the following:
CREATE OR REPLACE FUNCTION calc_comm_percent(p_salary IN number, p_comm IN number) RETURN number IS
BEGIN
RETURN 0;
END;
/
Note that the function simply returns 0. We've only written a basic function that does nothing so that our test will compile and run. Execute this code by choosing the SQL|Execute menu item. You should see the text "Function created" in the Text output page.
Now, switch back to the first edit page, and choose the SQL|Execute as PL/Unit Test menu item. If this is your first time using PL/Unit, you may see an error message stating that the PL/Unit package does not exist. Choose Yes to have the package created in your current schema. You will now be taken to the PL/Unit tab, and you should see something like the following:
What we can see is that we have run one unit test. That test has failed, and by selecting the failed test in the test browser, we can see the error message reported: Commission percent is wrong; expected <0.1> but was <0>.
This is telling us is that, because we passed in an expected value of 0.1 to plunit.assert_equals, but the result of our function call was 0, our assertion has failed; therefore, the error message we supplied was written out, along with the expected and actual values.
Now our task is to make the test pass. Switch back to the function and write the code to calculate percentage correctly:
CREATE OR REPLACE FUNCTION calc_comm_percent(p_salary IN number, p_comm IN number) RETURN number IS
BEGIN
RETURN p_comm/p_salary;
END;
/
Execute this, making sure it compiles correctly, and switch back to the PL/Unit test. Re-execute the PL/Unit test by choosing the run button on the PL/Unit page. This will rerun the currently compiled unit test again.
What you should now see is that the Red Bar has been replaced by a Green Bar, and that our test has passed. Success! However, our task is not done. What happens if we pass a zero into our function as one of the parameters? Lets find out by writing some additional tests:
CREATE OR REPLACE PACKAGE test_calc_comm_percent AS
-- PL/Unit tests are implemented as packages
PROCEDURE t_call_function;
-- unit tests are public procedures that have no parameters
-- their names must begin with t_
PROCEDURE t_zero_salary;
PROCEDURE t_zero_comm;
END;
/
CREATE OR REPLACE PACKAGE BODY test_calc_comm_percent AS
PROCEDURE t_call_function IS
-- we write our test logic in the package body
BEGIN
-- our call to assert equals is taking 3 parameters.
-- The first is the value we expect, the second is the actual value (our function result)
-- the third is the optional error message to supply if the test fails.
plunit.assert_equals(0.10, calc_comm_percent(1000, 100), 'Commission percent is wrong');
END;
PROCEDURE t_zero_salary IS
BEGIN
-- we'll assume that with no salary, the commission makes up 100% of the salary
plunit.assert_equals(1, calc_comm_percent(0, 100), 'Commission percent is wrong');
END;
PROCEDURE t_zero_comm IS
BEGIN
-- with no commission, the percentage should be zero
plunit.assert_equals(0, calc_comm_percent(1000, 0), 'Commission percent is wrong');
END;
END;
/
Since we've made some code changes to our test, we must recompile it before rerunning the test. Choosing SQL|Execute as PL/Unit Test will do this for us.
Now we see that we have three tests, and one is Erroneous. Clicking the erroneous test will tell us the reason. We have an unhandled exception: ORA-01476: divisor is equal to zero. We cannot divide our commission by a zero salary. Lets fix the code to make this test pass:
CREATE OR REPLACE FUNCTION calc_comm_percent(p_salary IN number, p_comm IN number) RETURN number IS
BEGIN
RETURN p_comm/p_salary;
EXCEPTION
WHEN ZERO_DIVIDE THEN RETURN 1;
END;
/
Compile this function and rerun the unit test using the PL/Unit run button. We now should have three passing tests. Now, what happens if both salary and commission are zero? Another test will tell us the answer:
CREATE OR REPLACE PACKAGE test_calc_comm_percent AS
-- PL/Unit tests are implemented as packages
PROCEDURE t_call_function;
-- unit tests are public procedures that have no parameters
-- their names must begin with t_
PROCEDURE t_zero_salary;
PROCEDURE t_zero_comm;
PROCEDURE t_zero_both;
END;
/
CREATE OR REPLACE PACKAGE BODY test_calc_comm_percent AS
PROCEDURE t_call_function IS
-- we write our test logic in the package body
BEGIN
-- our call to assert equals is taking 3 parameters.
-- The first is the value we expect, the second is the actual value (our function result)
-- the third is the optional error message to supply if the test fails.
plunit.assert_equals(0.10, calc_comm_percent(1000, 100), 'Commission percent is wrong');
END;
PROCEDURE t_zero_salary IS
BEGIN
-- we'll assume that with no salary, the commission makes up 100% of the salary
plunit.assert_equals(1, calc_comm_percent(0, 100), 'Commission percent is wrong');
END;
PROCEDURE t_zero_comm IS
BEGIN
-- with no commission, the percentage should be zero
plunit.assert_equals(0, calc_comm_percent(1000, 0), 'Commission percent is wrong');
END;
PROCEDURE t_zero_both IS
BEGIN
-- with no commission and no salary, the percentage should be zero
plunit.assert_equals(0, calc_comm_percent(0, 0), 'Commission percent is wrong');
END;
END;
/
Recompile and rerun this test by choosing SQL|Execute as PL/Unit Test. We now have four tests, and once again we have a failure. T_ZERO_BOTH is failing because we expected to get a 0, but instead we are returning a 1. Time to fix the code to make the test pass.
CREATE OR REPLACE FUNCTION calc_comm_percent(p_salary IN number, p_comm IN number) RETURN number IS
BEGIN
RETURN p_comm/p_salary;
EXCEPTION
WHEN ZERO_DIVIDE
THEN
IF p_comm = 0
THEN RETURN 0;
ELSE RETURN 1;
END IF;
END;
/
Run the unit tests again to make sure that we both fixed our failing test, and didn't break any old tests. Green Bar! Form here, you should write as many test cases as you can think of, running all tests after each addition, until you either can't think of any new tests, or you write a test that fails (hint: there is one other type of value that hasn't yet been tested for: NULLs). When you're done, the idea is to have a permanent record of all possible uses of your new function, so that if it ever needs to be modified, you'll be sure that it does what it used to do.
Summary
Throughout this tutorial, you should have noticed one common theme: No code was ever written without first having a failing test to prove that we needed it. This is the essence of the TDD process: Write failing test, write code, pass tests, write failing test... By writing a failing test first, you are both proving the new code is necessary, and also building up a suite of tests that will make sure you don't remove or break needed functionality as you modify.
Create the Emp tableCREATE TABLE emp (
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0));
insert into emp values (7369, 'SMITH', 'CLERK', 7902, to_date('17-DEC-80'), 800, NULL, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-FEB-81'), 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-FEB-81'), 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, to_date('02-APR-81'), 2975, NULL, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-SEP-81'), 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-MAY-81'), 2850, NULL, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-JUN-81'), 2450, NULL, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-APR-87'), 3000, NULL, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', NULL, to_date('17-NOV-81'), 5000, NULL, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-SEP-81'), 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-MAY-87'), 1100, NULL, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, to_date('03-DEC-81'), 950, NULL, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, to_date('03-DEC-81'), 3000, NULL, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, to_date('23-JAN-82'), 1300, NULL, 10);