SQL statements can be defined as a group into a compound statement in a routine. Such routines are stored in the DBMS for execution as a single action without interaction with a user process. There are two types of routine; functions and procedures.

Functions

SQL functions in this context have the same properties as built in functions, but are defined for a specific task related usually to a single database. Normally referred to as user-defined functions, they are defined by SQL facilities similar to most programming languages:

CREATE FUNCTION greater (par1 integer, par2 integer)
   RETURNS INTEGER
BEGIN
   IF par1 > par2
      THEN RETURN par1;
      ELSE RETURN par2;
   END IF;
END

The compound statement exists between BEGIN and END.

Functions are incorporated into the SQL statements:

SELECT s.student_id, greater(50, 100*COUNT(course_code))
   AS fee
FROM student s LEFT OUTER JOIN enrolment e
   ON s.student_id = e.student_id
GROUP BY s.student_id

The function is evaluated for each row. It is essential that the definition of the function is treated as a single entity.

Procedures

AKA as stored, or database procedures. Like functions, they have a body containing a compound statement. A procedure does not have a RETURN statement to return a value and as such cannot be used in queries or SQL statements. Instead it is invoked by a CALL statement.

CALL <procedure name> (<argument list>)

The keywords IN, OUT and INOUT control how the data is treated. The data can be provided, returned or both of these.

CREATE PROCEDURE proc_pop (IN my_city VARCHAR(24),
   OUT my_pop DECIMAL(6,1))
BEGIN
   SELECT population
   INTO my_pop
   FROM city
   WHERE name = my_city;
END

The procedure used above has the same effect as the function used below:

CREATE FUNCTION city_pop (my_city VARCHAR(24),
   RETURNS DECIMAL(6,1))
BEGIN
   DECLARE my_pop DECIMAL(6,1);
   SELECT population
   INTO my_pop
   FROM city
   WHERE name = my_city;
   RETURN my_pop;
END

The principal differences are:

• The function declares a variable my_pop
• This variable is an SQL variable and not a host variable
• The function returns a value
• The procedure employs my_pop as a parameter and not a variable
• The procedure does not return a value

Using SQL routines

Functions provide more flexibility than procedures in where they can be used. Functions can be used in SQL statements whereas procedures require a CALL. A function can return only one value. Procedures give more options in the fact that, where multiple parameters are used, there are more values to be returned.

The creator of a routine is the routines owner. If the routine references other users tables, the routine owner must have the neccessary privileges for those tables.

For a routine to be used by another user, they must be granted the privilege EXECUTE.

GRANT EXECUTE ON city_pop TO admin

A user that is granted an EXECUTE privilege for a routine does not have to be granted any other privileges for those tables. In such circumstances users can access other users tables for a specific purpose only. This helps to maintain control over updating.

Comments, suggestions, ideas to
Stuart Banner