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

MySQL Functions

Table 1 : designation

MySQL Functions

Table 2 : staff

MySQL Functions

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 $$
MySQL Functions

Step 3: Execute the function

Query :

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

MySQL Functions

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;

MySQL Functions
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents +