mhpreiman
12/6/2016 - 11:05 AM

mysql procedures

mysql procedure examples.md
content_copyfile_download
  • Rendered
  • Source

Example 1

CREATE PROCEDURE person_data
AS (or linebreak)
SELECT * FROM person



Example 2

delimiter //
CREATE PROCEDURE myprocedure (OUT param1 INT) -- selected count nr below will be used as OUT param1
BEGIN
SELECT count(*) INTO param1 FROM mytable;
END //
delimiter ;


Run the procedure and output:
CALL myprocedure(@a)

  @a  
  3  



Example 3
Procedure with several SQL sentences



Example 4

CREATE PROCEDURE leiatud (tudvoti CHAR(10))
RETURNING CHAR(20),CHAR(15),INTEGER;
DEFINE perek CHAR(20);
DEFINE eesnimi CHAR(15);
DEFINE kurs INTEGER;

SELECT pnimi,enimi,kursus INTO perek,eesnimi,kurs FROM tudeng WHERE tkood = tudvoti;
RETURN perek,eesnimi,kurs; END PROCEDURE;

mysql procedure syntax.md
content_copyfile_download
  • Rendered
  • Source

Procedure parameters

myprocedure (IN | OUT | INOUT param_name datatype)

  IN     param is used by procedure itself  
  OUT     param used by procedure caller  
  INOUT     param used by procedure but is available for caller after the end of procedure  

DETERMINISTIC   - output is always the same (deterministic) with the same input;
NOT DETERMINISTIC   - if proc contains functions like now(), rand()



RETURNING keyword

CREATE PROCEDURE a () RETURNING int;
DEFINE x INT;
LET x=20;
RETURN x;
END PROCEDURE


Only-advisory info (no functionality, just gives info)

  CONTAINS SQL     includes *NO* **R**ead or **W**rite statements  
  NO SQL     *NO* SQL statements  
  READS SQL DATA     includes **R**ead but **NO** **W**rite statements  
  MODIFIES SQL DATA     includes **W**rite statements  


Call a stored procedure (s):

CALL myprocedure(@param1, @param2)


Show how existing procedure was created

SHOW CREATE PROCEDURE myprocedure


Show variables and functions of a concrete procedure:
show FUNCTION code fname   -   used for showing functions

SHOW PROCEDURE CODE myprocedure


Alter procedure (s):

ALTER PROCEDURE myprocedure


Alter procedure (s):
drop FUNCTION fname   -   used for dropping functions

DROP PROCEDURE myprocedure