|

• In pma (PhpMyAdmin) GUI, it is quite simple:
  After the tables displayed, there is a routines link. Click it, the procedure details are displayed.

• From command line:

  mysql> select name, db, type from mysql.proc; mysql> select name from mysql.proc where db = ‘db_name’ and `type` = ‘PROCEDURE’ mysql> show procedure status;

  to get the list of stored procedures; then, to show individual procedure:

  mysql> show create procedure proc_name; mysql> show create function func_name;

MySQL’s procedures and functions are not as powerful as PL/SQL or T-SQL. However, for a repeated or complex insert/update process which involve transaction, multi-statements, multi-tables, using procedure is a primary choice.
| | 2. Extract/restore procedures/functions | | To dump a database, we use mysqldump; to restore the database, we use mysql command:

$ mysqldump -u test -p > database.sql $ mysql —verbose —user=test —password=test < database.sql

The issue is: mysqldump will backup by default all the triggers but NOT the stored procedures/functions. Suppose we have some of procedures/functions, the above mysqldump/mysql are not enough to dump all.

To solve it, there are 2 mysqldump parameters could control this behavior:

• – routines – FALSE by default
  routines mean procedures/functions, it remarks that by default, all the procedures and functions do not dump.
• – triggers – TRUE by default
  by defult, all triggers do dump with schema and data.

This means that if we want to dump ALL, we only need to add the –routines command line parameter:

mysqldump —user=test —password=test --routines > procedures.sql

The above will dump all database structure/data as well as procedures/functions.

Let’s assume we ONLY want to backup the stored procedures/functions, not the mysql tables and data (this can be useful to import these into another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:

$ mysqldump --routines —no-create-info —no-data —no-create-db —skip-opt —user=test —password=test —verbose > procedures.sql

By using this, we can operate the stored procedures and functions alone or with whole database schema and data for the purpose of database transfer.

|