MySQL - Stored Procedures

Part:   1  2  

(Continued from previous part...)

Here is a simple test on stored procedure the MySQL client tool:

mysql> USE test; 
mysql> DELIMITER '/';
mysql> CREATE PROCEDURE Msg()
    -> BEGIN
    ->    SELECT CURRENT_TIME() AS Time;
    ->    SELECT 'Hello world!' As Message;
    -> END/
Query OK, 0 rows affected (0.75 sec)

mysql> DELIMITER ';'/
mysql> CALL Msg();
+----------+
| Time     |
+----------+
| 17:35:51 |
+----------+
1 row in set (0.07 sec)

+--------------+
| Message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.08 sec)

Query OK, 0 rows affected (0.09 sec)

Stored Procedure Parameters

A stored procedure parameter is play the same role as a function paramter in many other languages. It can be used to pass data in to and/or out of the stored procedure. Here is how to specify a parameter in the create procedure statement:

parameter_type parameter_name data_type

where parameter_type can be one the following:

• IN - To indicate that this parameter is for input data only.
• OUT - To indicate that this parameter is for output data only.
• INOUT - To indicate that this parameter is for input and output data.
• If not specified, default to IN.

When calling a procedure that requires parameters, expressions must be provided in the calling statement:

• The number of expressions must be equal to the number of parameters.
• For an input type parameter, the resulting value of the expression will be passed into the procedure.
• For an output type parameter, the expression should be a variable where the procedure can store the output data.

Here is a sample SQL code creating and calling a stored procedure with parameters, ProcedureTest.sql:

-- ProcedureTest.sql
-- Copyright (c) 2004 by Dr. Herong Yang
--
DROP DATABASE IF EXISTS Test;
CREATE DATABASE Test;
USE Test;
DROP TABLE IF EXISTS Map;
CREATE TABLE Map (C REAL, F REAL);
--
DROP PROCEDURE IF EXISTS C2F;
DELIMITER '/';
CREATE PROCEDURE C2F(IN C REAL, OUT F REAL)
BEGIN
  INSERT INTO Map VALUES (C, 1.8*C+32.0);
  SELECT 1.8*C+32.0 INTO F;
END/
DELIMITER ';'/
--
CALL C2F(0.0, @F0);
CALL C2F(1.0, @F1);
CALL C2F(22.0, @F2);
CALL C2F(40.0, 100.0);
--
SELECT 'Output values:' AS '---';
SELECT @F0, @F1, @F2;
SELECT 'C2F map table:' AS '---';
SELECT * FROM Map;

Output:

---
Output values:
@F0     @F1     @F2
32      33.8    71.6
---
C2F map table:
C       F
0       32
1       33.8
22      71.6
40      104

Observe that if you provide a data literal to an output type parameter, the output data will be stored no where.

Part:   1  2