Stored Procedure Programming for MySQL5 - Part 2

by Ligaya Turmelle (2006-08-08)
  Page: 1  2  3  [next]

Now that we've become familiar with the fundamentals of stored procedures it is time to start playing with the “Big Boy Toys”. This article will go over stored procedures's built in error handling, the security features available, various “extras” available, what isn't allowed in a stored procedure, and some basic administration of the stored procedures. So lets stop talking and bust open the toy box and start playing!

Let's jump right in with the sample stored procedure we will be working with. It isn't particularly elaborate but it does touch on just about everything we will be going over. So it will be a decent reference to the various sections we are talking about.

Sample Code

The CREATE statement for the table it will work with:

  1. CREATE TABLE test (
  2.   ID  INT  AUTO_INCREMENT NOT NULL,
  3.   COL1  INT NOT NULL DEFAULT 0,
  4.   PRIMARY KEY (ID));

The stored procedure itself:

  1. mysql> DROP PROCEDURE simpleMassInsert;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> DELIMITER //
  5. mysql> CREATE PROCEDURE simpleMassInsert(
  6.     ->                                  IN myTable VARCHAR(30),
  7.     ->                                  IN col VARCHAR(30),
  8.     ->                                  IN value INT,
  9.     ->                                  IN numRec INT,
  10.     ->                                  OUT errMsg VARCHAR(80))
  11.     -> LANGUAGE SQL
  12.     -> NOT DETERMINISTIC
  13.     -> MODIFIES SQL DATA
  14.     -> SQL SECURITY INVOKER
  15.     -> COMMENT 'Inserts x number of records into a given table
  16.           and column with a given value '
  17.     -> BEGIN
  18.     ->  # counter for our loop #
  19.     ->  DECLARE count INT DEFAULT 0;
  20.     ->
  21.     ->  # basic error handling if table or column doesn't exist #
  22.     ->  DECLARE unknown_column CONDITION FOR SQLSTATE '42S22';
  23.     ->  DECLARE unknown_table CONDITION FOR SQLSTATE '42S02';
  24.     ->
  25.     ->  # the specific handlers #
  26.     ->  DECLARE EXIT HANDLER FOR unknown_column
  27.     ->          SET errMsg = 'Error: Column does not exist.';
  28.     ->
  29.     ->  DECLARE EXIT HANDLER FOR unknown_table
  30.     ->          SET errMsg = 'Error: table does not exist.';
  31.     ->
  32.     ->  # Actual work being done #
  33.     ->  SET errMsg = '';
  34.     ->  WHILE count < numRec DO
  35.     ->          # bit of a work around since #
  36.     ->       # MySQL SP's don't like  variable #
  37.     ->          # interpolation in a query.  So we build #
  38.     ->          # the query, and use prepared statements #
  39.     ->          SET @query = CONCAT('INSERT INTO ', myTable,'
  40.           (', col,') VALUES (?)');
  41.     ->          PREPARE qry FROM @query;
  42.     ->          SET @v = value;
  43.     ->          # EXECUTE only works with #
  44.     ->       # user variables in this context #
  45.     ->          EXECUTE qry USING @v;
  46.     ->          SET count = count + 1;
  47.     ->  END WHILE;
  48.     -> END //
  49. Query OK, 0 rows affected (0.00 sec)
  50.  
  51. mysql> DELIMITER ;

Verification the stored procedure works:

Note: Remember that when calling a stored procedure you must provide all the parameters specified by the stored procedure. So “test', ”COL1“, 3 and 5 will go to the IN vars of the stored procedure and the @error var will hold anything that comes out of the stored procedure.
  1. mysql> CALL simpleMassInsert("test", "COL1", 3, 5, @error);
  2. Query OK, 0 rows affected (0.14 sec)
  3.  
  4. mysql> SELECT @error;
  5. +--------+
  6. | @error |
  7. +--------+
  8. |        |
  9. +--------+
  10. 1 row IN SET (0.00 sec)
  11.  
  12. mysql> SELECT * FROM test;
  13. +----+------+
  14. | ID | COL1 |
  15. +----+------+
  16. | 16 |    3 |
  17. | 17 |    3 |
  18. | 18 |    3 |
  19. | 19 |    3 |
  20. | 20 |    3 |
  21. +----+------+
  22. 5 rows IN SET (0.00 sec)
File under: art  mysql  stored procedures 
  Page: 1  2  3  [next]

Comments

There are no comments on this entry.

Visit the forum