I am trying to understand Unit Testing in SQL*Developer. I was asked to evaluate it as a method of test automation on a integration solution we are developing.
(1.) My "A" schema has this table:
SQL> DESC emps
Name Null? Type
----------------------------------------- -------- --------------------
EMPID NOT NULL NUMBER
EMPNAME VARCHAR2(30)
SALARY NUMBER(7,2)
COMM_PCT NUMBER(2,2)
(2.) Table has these values:
SQL> SELECT * FROM emps;
EMPID EMPNAME SALARY COMM_PCT
---------- ---------- ---------- ----------
1 ROHAN 10000 .05
2 JOHN 20000
3 PETER .06
4 SMITH
(3.) I create this procedure in A:
create or replace
PROCEDURE award_bonus( p_empid NUMBER, sales_amt NUMBER)
AS
commission REAL;
comm_missing EXCEPTION;
BEGIN
SELECT comm_pct INTO commission FROM emps
WHERE empid = p_empid;
IF commission IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE emps
SET salary = salary + sales_amt*commission
WHERE empid = p_empid;
END IF;
END award_bonus;
(4.) Now I want to create a unit test IN SQL Developer to test this procedure.
(5.) I created a "unit_test_repos" user as instructed by the Tutorial.
(6.) "SELECT OPERATION": I specify the award_bonus procedure.
(7.) "SPECIFY Test Name": give a name and select "Create with single dummy".
(8.) "SPECIFY STARTUP": Add a 'Table or row copy" Startup process.
-- I give "emps" as the source table.
-- Then, I get this for the "generated query"
SELECT ROWID AS SRC$ROWID, s.* FROM emps s
-- Target table has "TMP$AWARD_BONUS".
(Q1) WHAT IS THE PURPOSE OF CREATING ANOTHER TABLE?
(Q2) WHEN THE TEST IS DONE, ARE ROWS FROM emps copied to this table?
(Q3) Why is that? Why can't the test just read from the "emps"?
(9.) "SPECIFY PARAMETERS": I give for "Input"
-- 1 for P_EMPID
-- 10000 for SALES_AMT
-- In "Expected Result" I say "Success".
(Q4) So, I should know in my mind the outcome of the proc, for the above given inputs???
(10.) "SPECIFY VALIDATION": I add a "Query Returning row(s)" validation.
-- I paste the following query: SELECT count(*) FROM emps WHERE empid=1 AND salary = 10500
-- i.e. I know that for a 10000 sales amount, employee 1 will get a commissin of 10000*.05 = 500. So, 10000 (his sal) + 500 = 10500.
(Q5) Is the above thinking correct?
(11.) "SPECIFY TEARDOOWN": Give nothing.
(12.) My unit test is created.
(13.) I now run it by clicking the beetle button, left of the green arrow. Unit test was executed.
(14.) Now, in my a.emps table, 1's salary has increased to 10,500, and in the unit test diag box, I get "Execution Results: SUCCESS".
(15.) Now, I run the unit test again.
(16.) The "emps" table salary for emp 1 has increased to 11000 (which is correct) and the execution results say 'SUCCESS".
(Q6) My question is: The query I gave for validation, SELECT count(*) FROM emps WHERE empid=1 AND salary = 10500, now gives 0. so, this means that no rows returned for this condition and hence the test should FAIL. But, how come SQL*Developer is saying that the test was SUCCESS. How does this validation system work?
(17.) I went through the tutorial, but I just don't understand these concepts>
(Q7) What is the purpose of Teardown? I undersand the "drop table" option of "teardown", where you drop the temp table, but I don't understand the others, "Table or row restore" and "user pl/sql code"?
(Q8) WHAT IS THE advantage of doing unit testing like this? I can use an Excel sheet and do dozens of unit tests faster than this method? It's easy to understand also. This method is complicated.
Your replies and advice would be greatly appreciated.