MySQL Functions

Creating a function

In MySQL, Function can also be created. A function always returns a value using the return statement. The function can be used in SQL queries.

Syntax

snippet

CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ] 
RETURNS return_datatype
BEGIN
Declaration_section
Executable_section
END;

Parameter:

Function_name: name of the function

Parameter: number of parameter. It can be one or more than one.

return_datatype: return value datatype of the function

declaration_section: all variables are declared.

executable_section: code for the function is written here.

Example 1

Step 1: Create database and table.

Database: employee

Table 1 : designation

Table 2 : staff

Step 2: Create a function

Function query:

snippet

DELIMITER $$ 
CREATE FUNCTION get_designation_name(d_id INT) RETURNS VARCHAR( 20 ) 
BEGIN 
DECLARE de_name VARCHAR( 20 ) DEFAULT "";
SELECT name INTO de_name FROM designation WHERE id = d_id;
RETURN de_name;
END $$

Step 3: Execute the function

Query :

SELECT id, get_designation1(`d_id`) as DESIGNATION, name FROM 'staff'

Drop a function

In MySQL Function can also be dropped. When A function id dropped, it is removed from the database.

Syntax:

snippet

Drop function [ IF EXISTS ] function_name;

Parameter

function_name: name of the function to be dropped.

Example 1:

drop function get_designation_name;