dcsimg

MySQL Stored Procedures: Part 1 - Page 2

August 9, 2005

Getting information about existing stored procedures

It is clearly necessary to be able to get more information about any stored procedures later, such as a list of procedures available, and the definitions. There are MySQL-specific ways to do this, and the syntax should be familiar to experienced MySQL users. SHOW PROCEDURE STATUS returns a list of stored procedures, and some metadata about them, while SHOW CREATE PROCEDURE returns the definition of a particular procedure.

mysql> SHOW PROCEDURE STATUS\G
************* 1. row ************
           Db: test
         Name: molo
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-07-29 19:20:27
      Created: 2005-07-29 19:20:27
Security_type: DEFINER
      Comment:
************* 2. row ************
           Db: test
         Name: sp_in
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-08-02 11:58:34
      Created: 2005-08-02 11:58:34
Security_type: DEFINER
      Comment:
************* 3. row ************
           Db: test
         Name: sp_inout
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-08-02 12:16:18
      Created: 2005-08-02 12:16:18
Security_type: DEFINER
      Comment:
************* 4. row ************
           Db: test
         Name: sp_out
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-08-02 12:01:56
      Created: 2005-08-02 12:01:56
Security_type: DEFINER
      Comment:
4 rows in set (0.00 sec)

It will become clear what all these fields mean as we progress through the rest of this tutorial series.

mysql> SHOW CREATE PROCEDURE molo\G
*************************** 1. row ***************************
       Procedure: molo
        sql_mode:
Create Procedure: CREATE PROCEDURE 'test'.'molo'()
SELECT 'Molo'
1 row in set (0.00 sec)

There is also an ANSI-standard way of doing it, which will be more familiar to other users.

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G
*************************** 1. row ***************************
     SPECIFIC_NAME: molo
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: molo
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SELECT 'Molo'
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-07-29 19:20:27
      LAST_ALTERED: 2005-07-29 19:20:27
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
*************************** 2. row ***************************
     SPECIFIC_NAME: sp_in
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: sp_in
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x = P
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-08-02 11:58:34
      LAST_ALTERED: 2005-08-02 11:58:34
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
*************************** 3. row ***************************
     SPECIFIC_NAME: sp_inout
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: sp_inout
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x=P*2
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-08-02 12:16:18
      LAST_ALTERED: 2005-08-02 12:16:18
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
*************************** 4. row ***************************
     SPECIFIC_NAME: sp_out
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: sp_out
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET P='molo'
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-08-02 12:01:56
      LAST_ALTERED: 2005-08-02 12:01:56
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
4 rows in set (0.01 sec)

Let's introduce some more complex examples. First, we will create a sample table.

mysql> CREATE table sp1 (id INT, txt VARCHAR(10), PRIMARY KEY(id));
Query OK, 0 rows affected (0.11 sec)
 

Delimiters, and multi-statement procedures

Stored procedures of course are not that useful if they are just one statement. The effects of all the procedures we have looked at so far could have had been duplicated much more easily with a single SQL statement. Useful procedures are much longer than that. Those of you who are on the ball may be thinking of a complication. How can we differentiate between multiple statements inside the procedure, and the end of the procedure? We have to create a different delimiter to end the CREATE PROCEDURE statement. Here is how:

mysql> DELIMITER | 

Note that there is no semicolon after the '|' symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.

mysql> CREATE PROCEDURE sp_ins (P VARCHAR(10))
    -> BEGIN
    ->  SET @x=CHAR_LENGTH(P);
    ->  SET @y = HEX(P);
    ->  INSERT INTO sp1(id,txt) VALUES(@x,@y);
    -> END|
Query OK, 0 rows affected (0.05 sec)
 
mysql> CALL sp_ins('ABC');
    -> |
Query OK, 1 row affected (0.00 sec)
 
mysql> DELIMITER ;
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
 id: 3
txt: 414243
1 row in set (0.00 sec)

Note what happened when we tried to call the procedure. Because MySQL was still using the | symbol as a delimiter, and not the semicolon, the statement did not run after the semicolon. We first needed to close it with the piping symbol. Afterwards, we reset the delimiter back to normal, and test that the records were correctly added to the sp1 table.

Procedure variables

Stored procedures do not only make use of the standard SQL statements. You can also DECLARE variables that exist only inside the procedure, as well as assign values to them with the SET statement without using the '@' symbol, required for session variables. Here is an example.

mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_declare (P INT)
    -> BEGIN
    ->  DECLARE x INT;
    ->  DECLARE y INT DEFAULT 10;
    ->  SET x = P*y;
    ->  INSERT INTO sp1(id,txt) VALUES(x,HEX('DEF'));
    -> END|
Query OK, 0 rows affected (0.00 sec)
 
mysql> DELIMITER ;
mysql> CALL sp_declare(4);
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
 id: 3
txt: 414243
*************************** 2. row ***************************
 id: 40
txt: 444546
2 rows in set (0.00 sec)

Variables that are declared without a default, such as x above, are set to NULL.

Populating variables from a pre-existing table

Now that you have seen how to INSERT records into a table from within a procedure, you may be wondering how we get values out of an existing table. Here is a simple example.

mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_select ()
    -> BEGIN
    ->  DECLARE x INT;
    ->  DECLARE y VARCHAR(10);
    ->  SELECT id,txt INTO x,y FROM sp1 LIMIT 1;
    ->  SELECT x,y;
    -> END|
Query OK, 0 rows affected (0.00 sec)
 
mysql> DELIMITER ;
mysql> CALL sp_select()\G
*************************** 1. row ***************************
x: 3
y: 414243
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

For now, we just returned the first record, to introduce the syntax. In next month's tutorial, we will look at iterations, which allow us to loop through multiple records.

Conclusion

I hope that you will be feeling comfortable with the syntax, and will be able to create your own stored procedures. None of the examples this month have been particularly useful, but next month's continuation will introduce some more advanced topics, and show you some of the real power of stored procedures.

» See All Articles by Columnist Ian Gilfillan








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers