STORED PROCEDURES AND FUNCTION

Answer»

Procedures are reusable SQL codes that we store in a database. We can directly call procedures instead of writing the query again and again.

Functions are reusable code, which runs certain SQL commands and returns an appropriate value.

DELIMITER $$
CREATE PROCEDURE procedurename(parameterlist)
BEGIN
body;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION functionname(parameterlist)
RETURNS datatype
NOT DETERMINISTIC
BEGIN
%statements%
END $$

DELIMITER ;

COMMAND	FUNCTION	SYNTAX
drop procedure	Deletes an existing procedure.	> DROP PROCEDURE PROCEDURENAME;
show all procedures	Displays all the stored procedures in the database.	> SHOW PROCEDURE STATUS LIKE ‘%PATTERN’ \| WHERE CONDITION;
drop function	Deletes an existing stored function.	> DROP FUNCTION FUNCTIONNAME;
show stored functions	Displays all the stored functions.	> SHOW FUNCTION STATUS LIKE ‘%PATTERN’ \| WHERE CONDITION;

Discussion