Added award_bonus example (based on https://docs.oracle.com/database/sql-developer-4.2/RPTUG/sql-developer-unit-testing.htm#RPTUG45068)

jgebal · jgebal · commit ede9fa11f9df · 2016-12-13T01:24:18.000Z
diff --git a/examples/award_bonus/award_bonus.sql b/examples/award_bonus/award_bonus.sql
@@ -0,0 +1,20 @@
+--https://docs.oracle.com/database/sql-developer-4.2/RPTUG/sql-developer-unit-testing.htm#RPTUG45065
+
+
+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_test
+      where employee_id = emp_id;
+
+  if commission is null then
+    raise comm_missing;
+  else
+    update employees_test
+      set salary = nvl(salary,0) + sales_amt*commission
+        where employee_id = emp_id;
+  end if;
+end;
+/
diff --git a/examples/award_bonus/employees_test.sql b/examples/award_bonus/employees_test.sql
@@ -0,0 +1,7 @@
+create table employees_test (employee_id number primary key, commission_pct number, salary number);
+insert into employees_test values (1001, 0.2, 8400);
+insert into employees_test values (1002, 0.25, 6000);
+insert into employees_test values (1003, 0.3, 5000);
+-- next employee is not in the sales department, thus is not on commission.
+insert into employees_test values (1004, null, 10000);
+commit;
diff --git a/examples/award_bonus/run_award_bonus_test.sql b/examples/award_bonus/run_award_bonus_test.sql
@@ -0,0 +1,10 @@
+@@employees.sql
+@@award_bonus.sql
+@@test_award_bonus.pkg
+
+set serveroutput on
+exec ut.run(user||'.test_award_bonus',ut_documentation_reporter());
+
+drop package test_award_bonus;
+drop procedure award_bonus;
+drop table employees_test;
diff --git a/examples/award_bonus/test_award_bonus.pkg b/examples/award_bonus/test_award_bonus.pkg
@@ -0,0 +1,82 @@
+create or replace package test_award_bonus as
+
+  -- %suite(Award bonus)
+
+  -- %test(Sets new salary as pct commision * sales amount)
+  -- %testsetup(add_test_employee)
+  procedure update_employee_salary;
+
+  -- %test(Raises exception if null bonus is passed)
+  -- %testsetup(add_employee_with_null_comm)
+  procedure fail_on_null_bonus;
+
+  procedure add_test_employee;
+
+  procedure add_employee_with_null_comm;
+
+end;
+/
+
+create or replace package body test_award_bonus as
+
+  gc_test_employee constant integer := -1;
+  gc_salary        constant number  := 4500;
+  gc_commision_pct constant number  := 0.2;
+
+  procedure update_employee_salary is
+    results      sys_refcursor;
+    expected     sys_refcursor;
+    not_affected sys_refcursor;
+    c_sales_amount constant number := 1000;
+  begin
+    --arrange
+    open expected for
+      select (salary + c_sales_amount * gc_commision_pct) as new_salary
+        from employees_test where employee_id = gc_test_employee;
+
+    open not_affected for
+      select * from employees where employee_id <> gc_test_employee;
+
+    --act
+    award_bonus(emp_id => gc_test_employee, sales_amt => c_sales_amount);
+
+    --assert
+    open results  for
+      select salary as new_salary
+        from employees_test where employee_id = gc_test_employee;
+
+    ut.expect( results ).to_( equal( expected ) );
+
+    open results for
+      select * from employees_test where employee_id != gc_test_employee;
+
+    ut.expect( results ).to_( equal( not_affected ) );
+  end;
+
+  procedure fail_on_null_bonus is
+  begin
+    award_bonus(emp_id => gc_test_employee, sales_amt => null);
+    ut.expect( sqlcode ).not_to( equal( 0 ) );
+  exception
+    when others then
+      ut.expect( sqlcode ).not_to( equal( 0 ) );
+  end;
+
+  procedure add_employee( emp_id number, comm_pct number, sal number ) is
+  begin
+    insert into employees_test (employee_id, commission_pct, salary)
+    values (emp_id, comm_pct, sal);
+  end;
+
+  procedure add_test_employee is
+  begin
+    add_employee(gc_test_employee, 0.2, gc_salary);
+  end;
+
+  procedure add_employee_with_null_comm is
+  begin
+    add_employee(gc_test_employee, null, gc_salary);
+  end;
+
+end;
+/
