A routine is either defined as a function or as a procedure. Essentially the same constructs may be used in both functions and procedures.
A routine can be created by declaring it in a module definition, see Modules, or be created on its own by executing the
CREATE PROCEDUREstatement. A routine created on its own cannot be subsequently added to a module.
A routine belongs to the schema in which it was created and the routine name may be qualified in the normal way with the name of the schema. Only the ident with the same name as the schema to which a routine belongs may refer to it by its unqualified name, all other idents must use the fully qualified routine name.
It is possible to have multiple functions and procedures with the same name within a schema as long as they differ with regard to either the number of parameters or the data type for the parameters. This is called parameter overloading.
To distinguish between routines with the same name it is possible to give a specific name when creating a routine. This specific name can used when granting or revoking execute privilege for the routine or when dropping the routine.
It is possible for a function to have the same qualified name as a procedure, because the invocation of a function is distinct from that of a procedure.
In order to invoke a routine, the ident invoking it must have been granted
EXECUTEprivilege on the routine. Routines may be recursively invoked.
Note: When routines and modules are created, the CREATE statement must be executed as one single statement. For example, using BSQL, the create statement must be delimited by the @ character, see the Mimer SQL User's Manual, Creating Functions, Procedures, Triggers and Modules, for details and examples.
The following points should be noted for procedures:
- they are invoked by using the
- any result from a procedure must be returned via one of the output parameters, except in the special case of a result set procedure, which can return rows of a result set to a cursor, see Result Set Procedures.
- they are invoked from an SQL statement where a value is required. Certain restrictions apply, see Invoking Functions. For example:SET :isbn = mimer_store_book.format_isbn('1558604618');
- the parameters of a function provide input only and the function result is returned as the value of the function invocation.
A routine essentially consists of static SQL source that is stored in the data dictionary and which may be invoked by name whenever it is to be executed.
The SQL source for a routine comprises a definition of various routine components, see Syntactic Components of a Routine Definition for details, followed by the routine body.
The routine body consists of a single executable SQL statement - typically a compound SQL statement, i.e. local declarations and a number of SQL statements delimited by a
END. See Scope in Routines - the Compound SQL Statement.
Note: It is recommended that a compound SQL statement always be used for the body of a routine, as this offers the greatest flexibility and results in a consistent structure for all routines.
It is possible to declare exception handlers within a compound SQL statement to handle specific exceptions or classes of conditions, see Declaring Exception Handlers.
A function is invoked by specifying the function invocation where a value expression would normally be used. The parameters of a function are used to provide input only, values cannot be passed back to the calling environment through the parameters of a function.
A function always returns a single value and the data type of the return value is defined in the returns clause, which is specified after the parameter definition part of the function definition.
The function returns its value when a
RETURNstatement is executed within the body of the function. The data type of the value expression in the
RETURNstatement must be assignment-compatible with the data type specified in the returns clause of the function.
A function can be created with the same name as a predefined function (e.g.
SIGN). If such a function also has the same number of parameters as the predefined function and each parameter has a data type that is assignment-compatible with the corresponding parameter of the predefined function, the invocations of the two functions cannot be distinguished. In this situation, any unqualified reference to the function name will be taken to be a reference to the predefined function.
Functions and SQL Statements
The SQL statements that apply to a function are:
creates a function that exists on its own, see the Mimer SQL Reference Manual, CREATE FUNCTION drops a function that exists on its own, see the Mimer SQL Reference Manual, DROP grants the privilege to invoke a function, see the Mimer SQL Reference Manual, GRANT OBJECT PRIVILEGE revokes the privilege to invoke a function, see the Mimer SQL Reference Manual, REVOKE OBJECT PRIVILEGE defines a comment on a function, see the Mimer SQL Reference Manual, COMMENT.
Example 1CREATE FUNCTION SQUARE_INTEGER(p_root INTEGER) RETURNS INTEGER CONTAINS SQL BEGIN RETURN p_root * p_root; END
Example 2CREATE FUNCTION mimer_store_web.session_expiration_period() RETURNS INTERVAL HOUR TO MINUTE -- Defines the period that a session can be unused DETERMINISTIC RETURN INTERVAL '10' MINUTE(3); -- Intentionally very short
Example 3CREATE FUNCTION mimer_store_book.keyword_id(p_keyword VARCHAR(48)) RETURNS INTEGER -- Inserts a word in the KEYWORDS table -- and returns the identifier with which the keyword is associated MODIFIES SQL DATA BEGIN DECLARE v_keyword_id INTEGER; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN INSERT INTO mimer_store_book.keywords(keyword) VALUES (UPPER(TRIM(p_keyword))); SET v_keyword_id = CURRENT_VALUE OF mimer_store_book.keyword_id_seq; END; -- of not found handler SELECT keyword_id INTO v_keyword_id FROM mimer_store_book.keywords WHERE keyword = TRIM(p_keyword); RETURN v_keyword_id; END -- of routine mimer_store_book.keyword_id
A procedure is normally invoked explicitly by executing the
CALLstatement and does not return a value. The parameters of a procedure can be used to provide input and may be used to pass values back to the calling environment.
There is a special type of procedure, called a result set procedure, which returns rows of a result set to a cursor when it is invoked by executing the
FETCHstatement in that context.
A result set procedure is distinguished from a normal procedure by having a values clause specified after the parameter definition part of the procedure definition, see Result Set Procedures for a detailed description of result set procedures.
Procedures and SQL Statements
The SQL statements that apply to a procedure are:
creates a procedure that exists on its own, see the Mimer SQL Reference Manual, CREATE PROCEDURE drops a procedure that exists on its own, see the Mimer SQL Reference Manual, DROP grants the privilege to invoke a procedure, see the Mimer SQL Reference Manual, GRANT OBJECT PRIVILEGE revokes the privilege to invoke a procedure, see the Mimer SQL Reference Manual, REVOKE OBJECT PRIVILEGE invokes a procedure, see the Mimer SQL Reference Manual, CALL defines a comment on a procedure, see the Mimer SQL Reference Manual, COMMENT.
Example 1CREATE PROCEDURE mimer_store_web.delete_basket(p_session_no VARCHAR(16)) -- Deletes expired baskets MODIFIES SQL DATA BEGIN IF p_session_no = '*' THEN -- '*' indicates that all expired sessions should be deleted DELETE FROM mimer_store.orders WHERE order_id IN (SELECT order_id FROM mimer_store_web.sessions WHERE last_accessed < LOCALTIMESTAMP - mimer_store_web.session_expiration_period()); ELSE -- Delete the specified session DELETE FROM mimer_store.orders WHERE order_id = (SELECT order_id FROM mimer_store_web.sessions WHERE session_no = p_session_no); END IF; END -- of routine mimer_store_web.delete_basket CALL mimer_store_web.delete_basket( '*' ); COMMENT ON PROCEDURE mimer_store_web.delete_basket IS 'Deletes expired baskets'; DROP PROCEDURE mimer_store_web.delete_basket;
Example 2CREATE PROCEDURE mimer_store_book.catalogue_authors(IN p_item_id INTEGER, IN p_authors_list VARCHAR(128)) -- Stores author names as keywords and forms a link between a book -- and the keywords MODIFIES SQL DATA BEGIN DECLARE v_author VARCHAR(50); DECLARE v_authors VARCHAR(130); DECLARE v_offset, v_length INTEGER; SET v_authors = REPLACE(' ' || p_authors_list || ' ', ' and ', ';'); SET v_authors = REPLACE(v_authors, ' & ', ';'); SET v_authors = TRIM(v_authors); extract_authors: LOOP IF v_authors = '' THEN LEAVE extract_authors; END IF; SET v_offset = POSITION(';' IN v_authors); IF v_offset <> 1 THEN IF v_offset = 0 OR v_offset > 49 THEN SET v_length = 48; ELSE SET v_length = v_offset - 1; END IF; SET v_author = mimer_store_book.authors_name( SUBSTRING(v_authors FROM 1 FOR v_length)); BEGIN DECLARE v_keyword_id INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN -- Ignore all SQL errors END; -- of sqlexception handler SET v_keyword_id = mimer_store_book.keyword_id(v_author); INSERT INTO mimer_store_book.authors(keyword_id, item_id) VALUES (v_keyword_id, p_item_id); END; END IF; IF v_offset = 0 THEN LEAVE extract_authors; END IF; SET v_authors = TRIM(SUBSTRING(v_authors FROM v_offset+1)); END LOOP extract_authors; END -- of routine mimer_store_book.catalogue_authors
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40