Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

I.1. Restrictions on Stored Routines and Triggers

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of restrictions apply only to stored functions, and not to stored procedures.

All of the restrictions for stored functions also apply to triggers. In addition, triggers currently are not activated by foreign key actions.

Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:

  • The locking statements LOCK TABLES, UNLOCK TABLES.

  • LOAD DATA and LOAD TABLE.

  • SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE). Implication: You cannot use dynamic SQL within stored routines (where you construct dynamically statements as strings and then execute them). This restriction is lifted as of MySQL 5.0.13 for stored procedures; it still applies to stored functions and triggers.

For stored functions (but not stored procedures), the following additional statements or operations are disallowed:

  • Statements that do explicit or implicit commit or rollback.

  • Statements that return a result set. This includes SELECT statements that do not have an INTO var_list clause and SHOW statements. A function can process a result set either with SELECT ... INTO var_list or by using a cursor and FETCH statements. See Section 19.2.7.3, “SELECT ... INTO Statement”.

  • FLUSH statements.

  • Recursive statements. That is, stored functions cannot be used recursively.

  • Within a stored function or trigger, it is not allowable to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Note that although some restrictions normally apply to stored functions and triggers but not to stored procedures, those restrictions do apply to stored procedures if they are invoked from within a stored function or trigger. For example, although you can use FLUSH in a stored procedure, such a stored procedure cannot be called from a stored function or trigger.

It is possible for the same identifier to be used for a routine parameter, a local variable, and a table column. Also, the same local variable name can be used in nested blocks. For example:

CREATE PROCEDURE p (i INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SELECT i FROM t;
  BEGIN
    DECLARE i INT DEFAULT 1;
    SELECT i FROM t;
  END;
END;

In such cases the identifier is ambiguous and the following precedence rules apply:

  • A local variable takes precedence over a routine parameter or table column

  • A routine parameter takes precedence over a table column

  • A local variable in an inner block takes precedence over a local variable in an outer block

The behavior that table columns do not take precedence over variables is non-standard.

Use of stored routines can cause replication problems. This issue is discussed further in Section 19.4, “Binary Logging of Stored Routines and Triggers”.

INFORMATION_SCHEMA does not yet have a PARAMETERS table, so applications that need to acquire routine parameter information at runtime must use workarounds such as parsing the output of SHOW CREATE statements.

There are no stored routine debugging facilities.

CALL statements cannot be prepared. This true both for server-side prepared statements and for SQL prepared statements.

UNDO handlers are not supported.

FOR loops are not supported.

To prevent problems of interaction between server threads, when a client issues a statement, the server uses a snapshot of routines and triggers available for execution of the statement. That is, the server calculates a list of procedures, functions, and triggers that may be used during execution of the statement, loads them, and then proceeds to execute the statement. This means that while the statement executes, it will not see changes to routines performed by other threads.

The RETURN statement is disallowed in triggers, which cannot return a value. To exit a trigger immediately, use the LEAVE statement.

The RENAME DATABASE statement does not migrate stored routines to the new schema name. See Section 13.1.9, “RENAME DATABASE Syntax”.


 
 
  Published under the terms of the GNU General Public License Design by Interspire