My biggest peeve is the misuse of Functions, and when I say function in can be standalone or part of a package. But Functions and Procedures have different purposes.
First my rules simple rules on FUNCTION and PROCEDURE.
- Functions return a single meaningful value.
- Functions do not have side-effects.
- Procedures perform work, do something.
- Procedures have side-effects
These rules date back to ADA, the language Pl/Sql is derived from. They still hold, one reason is a Function can be used in a SQL statement. but if it contains DML an error will be thrown.
Functions are used to return a value that would be used by the program. Things like the sum of a set of values; or the square root of a number; the amount of tax to be applied.
CREATE OR REPLACE FUNCTION amount_of_tax( taxable number, RETURN NUMBER IS BEGIN UPDATE employees SET phone_number = '123.456.7890' WHERE employe_id = 107; RETURN 1;
I have seen code like:
CREATE OR REPLACE FUNCTION myFunc RETURN NUMBER IS BEGIN UPDATE employees SET phone_number = '123.456.7890' WHERE employe_id = 107; RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 0; END; /* And called like one of these */ SELECT myFunc FROM dual; -- var foo NUMBER EXEC foo := myFunc ;
This is wrong in so many ways.
- Returns useless data.
- Invalidates the exception handling mechanism.
- Returns zero for failure, as a long time C programmer non-zero are the error.
- Need to have a variable to capture the return
Correctly this should be
CREATE OR REPLACE PROCEDURE myProc IS BEGIN UPDATE employees SET phone_number = '123.456.7890' WHERE employe_id = 107; END; -- Called from SQL Prompt exec myProc