Tutorial by Examples

The following (trivial) example function simply returns the constant INT value 12. DELIMITER || CREATE FUNCTION functionname() RETURNS INT BEGIN RETURN 12; END; || DELIMITER ; The first line defines what the delimiter character(DELIMITER ||) is to be changed to, this is needed to be s...

DROP PROCEDURE if exists displayNext100WithName; DELIMITER $$ CREATE PROCEDURE displayNext100WithName ( nStart int, tblName varchar(100) ) BEGIN DECLARE thesql varchar(500); -- holds the constructed sql string to execute -- expands the sizing of the output buffer to accomoda...

DELIMITER $$ DROP PROCEDURE IF EXISTS sp_nested_loop$$ CREATE PROCEDURE sp_nested_loop(IN i INT, IN j INT, OUT x INT, OUT y INT, INOUT z INT) BEGIN DECLARE a INTEGER DEFAULT 0; DECLARE b INTEGER DEFAULT 0; DECLARE c INTEGER DEFAULT 0; WHILE a < i DO WHILE b <...

Cursors enable you to itterate results of query one by line. DECLARE command is used to init cursor and associate it with a specific SQL query: DECLARE student CURSOR FOR SELECT name FROM studend; Let's say we sell products of some types. We want to count how many products of each type are exis...

Unlike a SELECT statement, a Stored Procedure returns multiple result sets. The requires different code to be used for gathering the results of a CALL in Perl, PHP, etc. (Need specific code here or elsewhere!)

DELIMITER $$ CREATE DEFINER=`db_username`@`hostname_or_IP` FUNCTION `function_name`(optional_param data_type(length_if_applicable)) RETURNS data_type BEGIN /* SQL Statements goes here */ END$$ DELIMITER ; The RETURNS data_type is any MySQL datatype.