Getting Information About Stored Programs

This section describes ways you can retrieve information about the stored programs that exist in your database.

In releases of MySQL prior to 5.0, extracting information about objects in the database was achieved by issuing SHOW statements. MySQL has extended the SHOW statement in version 5 to include information about stored programs.

However, in 5.0, MySQL also introduced the INFORMATION_SCHEMA database, which contains various tables that provide information about the objects that exist within the server. These tables are typically referred to as the data dictionary or as server metadata.

If you are a long-time user of the MySQL server, then using SHOW statements may seem a more natural approach to obtaining information about stored programs. However, the INFORMATION_SCHEMA tablesin addition to being ANSI standardhave the advantage of being amenable to various handy SELECT operations, such as grouping, counting, joining, and advanced filtering operations. You can also use INFORMATION_SCHEMA tables within your stored program codesomething that is not practical with SHOW statement output.


The SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS statements return information about the stored programs within the server. The syntax of this form of the SHOW statement is:


Figure 7-11 provides an example of SHOW PROCEDURE status output.




The SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements return the CREATE statement necessary to re-create a particular stored program. Figure 7-12 shows the output of this version of SHOW. Note that we used the "View Field pop-up editor" right-click option to load the text output returned by this statement into a more readable Field Viewer window.


The INFORMATION_SCHEMA.ROUTINES table returns a variety of information about stored procedures and functions. You can use the WHERE clause and column lists within the SELECT statement to format this output in various interesting ways.


This table does not contain information about triggers, but you can retrieve trigger information from the INFORMATION_SCHEMA.TRIGGERS table described in the next section.

Figure 7-13 shows the structure of the INFORMATION_SCHEMA.ROUTINES table.

You can use INFORMATION_SCHEMA.ROUTINES to return any of the data returned by the SHOW PROCEDURE STATUS, SHOW FUNCTION STATUS, SHOW CREATE PROCEDURE, and SHOW CREATE FUNCTION statements. For instance, in Figure 7-14, we produce a report that includes both the procedure/function definitions and other information about these programs.


The INFORMATION_SCHEMA.TRIGGERS table contains details about all of the triggers that are defined on the MySQL server. Figure 7-15 shows the output from a query against this table (using the "View Field pop-up editor" right-click option to view the contents of the action_statement column).

Figure 7-13. Structure of the INFORMATION_SCHEMA.ROUTINES table

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208 © 2008-2020.
If you may any questions please contact us: