MySQL: Stored Procedures


-- begin

DROP DATABASE IF EXISTS `test`;

CREATE DATABASE `test`;

USE `test`;

-- DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
	`val` int
);

DROP PROCEDURE IF EXISTS `p1`;

DELIMITER //

CREATE PROCEDURE `p1` (IN v1 INT, OUT v3 INT)
BEGIN
	DECLARE v2 INT; -- declare it here, not inside else block
	IF v1 <= 0 THEN
	SELECT "no iterations";
	ELSE
		SET v2 = 0;
		WHILE v1 >= v2 DO
			INSERT INTO `test` values(v1);
			SET v1 = v1 - 1;
		END WHILE;
		SET v3 = V1;
	END IF;
END //

DELIMITER ;

SET @x = 99;
SET @y = @x;

SELECT COUNT(*) FROM `test`;

SELECT @x, @y;

CALL `p1`(@x, @y);

SELECT COUNT(*) FROM `test`;

SELECT @x, @y;

-- end

References
An Introduction to Stored Procedures in MySQL 5

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: