March 14, 2007

Assertions

Using assertions in sourcecodes is well known in Java and other programming-languages, but not in PL/SQL. Why?

That's a good question and I solved it for myself through using this technique:


DECLARE
e_Assertion EXCEPTION;
BEGIN
IF condition1 = 'value'
OR boolean = TRUE
OR something_else THEN
RAISE e_Assertion;
END IF;

-- your code:
...
EXCEPTION
WHEN e_Assertion THEN
NULL;
WHEN OTHERS THEN
-- when-others-exception-handling
END;

In this example you write all your negative assertions under each other and raise the assertion-exception, which does nothing in the exception-handling.

e.g.

PROCEDURE Double_Manager_Salary (P_EMPNO IN NUMBER, P_JOB IN VARCHAR2) IS
e_Assertion EXCEPTION;
BEGIN
IF P_Job != 'MGR' THEN
RAISE e_Assertion;
END IF;

UPDATE EMP SET
SAL = SAL * 2
WHERE EMPNO = P_EMPNO;

EXCEPTION
WHEN e_Assertion THEN
NULL;
END;

what we see here is very simple: If you assert, that only manager get doubled salaries, then you cancel the procedure directly after it starts. You jump into the e_Assertion-Exception and do nothing.

Try and use it
Gerd