Function of Procedure

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
Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>