MySQL Stored Procedures Examples

-- 8-1: Stored Procedure Intro
-- http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html

-- stored procedures
-- paste this script into the mysql client

USE test;
DELIMITER |                      -- // permit semi-colons within SP

DROP PROCEDURE IF EXISTS worldmesgproc |
CREATE PROCEDURE worldmesgproc( IN s CHAR( 10 ) )
SELECT CONCAT_WS( ' ', s, 'world!' );
|

DROP FUNCTION IF EXISTS worldmesgfunc |
CREATE FUNCTION worldmesgfunc( s CHAR( 10 ) ) RETURNS CHAR( 20 )
RETURN CONCAT_WS( s, 'world!' );
|

DELIMITER ;                     -- // restore semi-colon as delimiter

CALL worldmesgproc( 'Hello' );
Hello world!
SELECT worldmesgfunc( 'Hello' );
Hello world!

CALL worldmesgfunc( 'Hi' );     -- // funcs cannot be called
ERROR 1289 at line 18: PROCEDURE worldmesgfunc does not exist

DROP PROCEDURE worldmesgproc;
DROP FUNCTION worldmesgfunc;

-- # EOF

-- 8-2: DECLARE ... HANDLER in an SP

-- # Here,  because DECLARE EXIT HANDLER … instructs MySQL to exit the SP
-- # if  SQLSTATE = 23000, the statement SET @err=-1 never executes. Notice that
-- # in order to be able to mark the end of the SP, the code has to set the delimiter to
-- # something other than a semi-colon; this is best done just before creating the SP,
-- # and reset immediately after. The DELIMITER command does not need a second
-- # terminator.

USE test ;
SET @err = 0 ;
SELECT 'Before running errhandlerdemo:', @err ;
CREATE TABLE IF NOT EXISTS testhandler (i INT, PRIMARY KEY(i)) ;

DELIMITER |
DROP PROCEDURE IF EXISTS errhandlerdemo ;
CREATE PROCEDURE errhandlerdemo()
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000;
    INSERT INTO testhandler VALUES( NULL) ;
    SET @err=-1 ;
  END ;
|
DELIMITER ;

CALL errhandlerdemo() |
DROP TABLE testhandler |
SELECT 'After running errhandlerdemo:', @err;

-- # EOF

-- 8-3:IF...THEN... in an SP

USE test;
DROP FUNCTION IF EXISTS is_even;
DELIMITER |
CREATE FUNCTION is_even( x INT ) RETURNS INT
BEGIN
  DECLARE iRet INT DEFAULT 0;
  IF x/2 = 0 THEN
    SET iRet = 1;
  END IF;
  RETURN iRet;
END
|
DELIMITER ;

SELECT is_even( 3 );

-- #EOF

-- 8-4: CASE...WHEN...ENDCASE in an SP

USE test;

DROP PROCEDURE IF EXISTS case1proc;
DELIMITER |
CREATE PROCEDURE case1proc( IN x INT )
BEGIN
  CASE x
    WHEN 'string' THEN SELECT 'non-matching value will not execute';
    WHEN 0 THEN SELECT 'matching value executes';
    WHEN 17 THEN SELECT 'non-matching value will not execute';
  END CASE;
END;
|

DROP PROCEDURE IF EXISTS case2proc |
CREATE PROCEDURE case2proc( IN x INT )
BEGIN
  DECLARE s CHAR( 10 );
  CASE
    WHEN x < 0 THEN SET s = 'less than zero';
    WHEN x < 10 THEN SET s = 'units';
    WHEN x < 100 THEN SET s = 'tens';
    WHEN x < 1000 THEN SET s = 'hundreds';
    ELSE SET s = 'a thousand or more';
  END CASE;
  SELECT CONCAT( 'range is ', s );
END;
|

DELIMITER ;

CALL case1proc( 0 );           -- # output: matching value executes
CALL case2proc( 100 );         -- # output: range is hundreds

-- # EOF

-- 8-5: DO .. REPEAT

DELIMITER |

DROP PROCEDURE IF EXISTS dorepeat;
CREATE PROCEDURE dorepeat( IN imax INT )
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s CHAR( 20 ) DEFAULT 'Loops:';
REPEAT
BEGIN
SET s = CONCAT_WS( ' ', s, i );
SET i = i + 1;
END;
UNTIL i > imax END REPEAT;
SELECT s;
END
|

DELIMITER ;
CALL dorepeat( 5 );

-- #EOF

-- 8-6: DO ... WHILE ...

USE test;

DROP PROCEDURE IF EXISTS whileproc;
DELIMITER |
CREATE PROCEDURE whileproc( IN x INT )
BEGIN
   DECLARE i INT DEFAULT 0;
   DECLARE s CHAR( 20 ) DEFAULT "Loops:";
   WHILE i-5 DO
     BEGIN
       SET i = i + 1;
       SET s = CONCAT_WS( ' ', s, i );
     END;
   END WHILE;
   SELECT s;
END;
|

DELIMITER ;

CALL whileproc( 5 ); -- output: Loops: 1 2 3 4 5

-- #EOF

-- 8-7: Cursor, handler, loop and iteration in an SP

USE test;
DROP TABLE IF EXISTS curtest1;
DROP TABLE IF EXISTS curtest2;
CREATE TABLE curtest1( i INT PRIMARY KEY, j INT, name CHAR(10) );
CREATE TABLE curtest2( x INT );
INSERT INTO curtest1 VALUES (0,0,'first'),(1,2,'second'),(2,1,'third');

DROP PROCEDURE IF EXISTS cursxmpl;
DELIMITER |
CREATE PROCEDURE cursxmpl()
BEGIN
  DECLARE mi INT;
  DECLARE mj INT;
  DECLARE done INT DEFAULT 0;
  DECLARE curs CURSOR FOR SELECT i, j FROM test.curtest1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN curs;
  loop0: WHILE NOT done DO
    FETCH curs INTO mi, mj;
    IF NOT done THEN
       IF mi < mj THEN
          INSERT INTO test.curtest2 VALUES (mi);
       ELSEIF mi > mj THEN
          INSERT INTO test.curtest2 VALUES (mj);
       ELSE
          ITERATE loop0;
       END IF;
    END IF;
  END WHILE loop0;
  CLOSE curs;
END;
|
DELIMITER ;
CALL cursxmpl();
SELECT * FROM curtest2;

-- # EOF