Stored Procedure Examples in MySQL
· Stored procedure is a piece of code (procedure) consisting of declarative and procedural SQL statements stored in the catalog of a database that can be activated by calling it from a program, a trigger, or another stored procedure.
Examples:
use tennis;
drop procedure delete_matches;
· Create a stored procedure that removes all matches played by a specific player.
delimiter //
CREATE PROCEDURE DELETE_MATCHES
(IN P_PLAYERNO INTEGER)
BEGIN
DELETE
FROM MATCHES
WHERE PLAYERNO = P_PLAYERNO;
END//
delimiter ;
select * from matches;
CALL DELETE_MATCHES (8);
· Create a stored procedure in which an initial value is assigned to a local variable. Next call this stored procedure.
delimiter //
CREATE PROCEDURE TEST
(OUT NUMBER1 INTEGER)
BEGIN
DECLARE NUMBER2 INTEGER DEFAULT 100;
SET NUMBER1 = NUMBER2;
END//
delimiter ;
CALL TEST (@NUMBER);
SELECT @NUMBER;
· Create a stored procedure that determines which of the following two input parameters is highest.
delimiter //
CREATE PROCEDURE DIFFERENCE
(IN P1 INTEGER,
IN P2 INTEGER,
OUT P3 INTEGER)
BEGIN
IF P1 > P2 THEN
SET P3 = 1;
ELSEIF P1 = P2 THEN
SET P3 = 2;
ELSE
SET P3 = 3;
END IF;
END//
delimiter ;
CALL DIFFERENCE (3, 5, @NUMBER);
Select @NUMBER;
· Create a stored procedure that indicates which table, PLAYERS or PENALTIES, has the largest number of rows.
delimiter //
CREATE PROCEDURE LARGEST
(OUT T CHAR(10))
BEGIN
IF (SELECT COUNT(*) FROM PLAYERS) >
(SELECT COUNT(*) FROM PENALTIES) THEN
SET T = 'PLAYERS';
ELSEIF (SELECT COUNT(*) FROM PLAYERS) =
(SELECT COUNT(*) FROM PENALTIES) THEN
SET T = 'EQUAL';
ELSE
SET T = 'PENALTIES';
END IF;
END//
delimiter ;
CALL LARGEST (@table);
select @table;
· Create a stored procedure that calculates the number of years, months, and days between two dates.
delimiter //
CREATE PROCEDURE AGE
(IN START_DATE DATE,
IN END_DATE DATE,
OUT YEARS INTEGER,
OUT MONTHS INTEGER,
OUT DAYS INTEGER)
BEGIN
DECLARE NEXT_DATE, PREVIOUS_DATE DATE;
SET YEARS = 0;
SET PREVIOUS_DATE = START_DATE;
SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR;
WHILE NEXT_DATE < END_DATE DO
SET YEARS = YEARS + 1;
SET PREVIOUS_DATE = NEXT_DATE;
SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR;
END WHILE;
SET MONTHS = 0;
SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 MONTH;
WHILE NEXT_DATE < END_DATE DO
SET MONTHS = MONTHS + 1;
SET PREVIOUS_DATE = NEXT_DATE;
SET NEXT_DATE = NEXT_DATE + INTERVAL 1 MONTH;
END WHILE;
SET DAYS = 0;
SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 DAY;
WHILE NEXT_DATE <= END_DATE DO
SET DAYS = DAYS + 1;
SET PREVIOUS_DATE = NEXT_DATE;
SET NEXT_DATE = NEXT_DATE + INTERVAL 1 DAY;
END WHILE;
END//
delimiter ;
SET @START = '1991-01-12'
;
SET @END = '1999-07-09'
;
CALL AGE (@START, @END, @YEAR, @MONTH, @DAY)
;
SELECT @START, @END, @YEAR, @MONTH, @DAY;
SHOW PROCEDURE STATUS LIKE ‘AGE’;
SHOW CREATE PROCEDURE AGE;
· Creating index examples
use tennis;
CREATE TABLE T1
(COL1 INTEGER NOT NULL,
COL2 DATE NOT NULL UNIQUE,
COL3 INTEGER NOT NULL,
COL4 INTEGER NOT NULL,
PRIMARY KEY (COL1, COL4),
UNIQUE (COL3, COL4),
UNIQUE (COL3, COL1))
;
CREATE UNIQUE INDEX COL1_4 USING BTREE
ON T1 (COL1, COL4)
;
CREATE UNIQUE INDEX COL2 USING BTREE
ON T1 (COL2)
;
CREATE UNIQUE INDEX COL3 USING BTREE
ON T1 (COL3, COL4)
;
CREATE UNIQUE INDEX COL3_2 USING BTREE
ON T1 (COL3, COL1);
CREATE INDEX PLAY_TOWN USING HASH
ON PLAYERS (TOWN);
use information_schema;
SELECT INDEX_SCHEMA, INDEX_NAME, COUNT(*)
FROM STATISTICS
GROUP BY INDEX_SCHEMA, INDEX_NAME
HAVING COUNT(*) > 1;