Examples for business, study, careers, love, and more...
Examples :: MYSQL Examples :: example of create procedure and create function mysql

Example Of CREATE Procedure AND CREATE Function Mysql Syntax:




CREATE PROCEDURE Example CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE Function Example CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type




characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL PROCEDURE statement

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;