BK BIRLA INSTITUE OF ENGINEERING & TECHNOLGY (BKBIET)
DBMS LAB SHEET VI
PROBLEM STATEMENT
Functions in MySQL
What is a function in MySQL?
In MySQL, a function is a stored program that you can pass parameters into and then return a value.
Create Function
Just as you can create functions in other languages, you can create your own functions in MySQL. Let's
take a closer look.
Syntax
The syntax to create a function in MySQL is:
CREATE FUNCTION function_name [ (parameter datatype [, parameter
datatype]) ]
RETURNS return_datatype
BEGIN
declaration_section
executable_section
END;
function_name
The name to assign to this function in MySQL.
parameter
One or more parameters passed into the function. When creating a function, all parameters are
considered to be IN parameters (not OUT or INOUT parameters) where the parameters can be
referenced by the function but can not be overwritten by the function.
return_datatype
DBMS LAB VI 1 VIPIN KUMAR SARAOGI
The data type of the function's return value.
declaration_section
The place in the function where you declare local variables.
executable_section
The place in the function where you enter the code for the function.
Drop Function
Once you have created your function in MySQL, you might find that you need to remove it from the
database.
Syntax
The syntax to a drop a function in MySQL is:
DROP FUNCTION [ IF EXISTS ] function_name;
function_name
The name of the function that you wish to drop.
PRACTICAL EXERCISE
Let's look at an example that shows how to create a function in MySQL:
DELIMITER //
CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT
BEGIN
DECLARE income INT;
DBMS LAB VI 2 VIPIN KUMAR SARAOGI
SET income = 0;
label1: WHILE income <= 3000 DO
SET income = income + starting_value;
END WHILE label1;
RETURN income;
END; //
DELIMITER ;
You could then reference your new function as follows:
SELECT CalcIncome (1000);
A very basic CREATE FUNCTION example which will produced the famed 'Hello World' output:
DELIMITER $$
CREATE FUNCTION hello_world()
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN 'Hello World';
END;
$$
DELIMITER ;
PRACTICAL PROBLEM
DBMS LAB VI 3 VIPIN KUMAR SARAOGI
Create Table “Customer” with following schema.
Cid CHAR(4) Primary Key
Name VARCHAR(30) NOT NULL
Creditlimit INTEGER
Insert following values in Customer table.
Cid Name Creditlimit
1001 Aditya 20000
1002 Deepak 30000
1003 Harsh 40000
1004 Sunita 15000
1005 Vineeta 50000
Create function to determine customer level in basis of following scale
If creditlimit >= 40000 Customer level = “Platinum”
If creditlimit >= 20000 and < 40000 Customer level = “Gold”
If creditlimit < 20000 Customer level = “Silver”
SOLUTION
Query to create table “Customer”
CREATE TABLE CUSTOMER
( Cid CHAR(4) PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Creditlimit INTEGER );
Insert values in customer table
INSERT INTO CUSTOMER VALUES (‘1001’,’Aditya’,20000);
INSERT INTO CUSTOMER VALUES (‘1002’,’Deepak’,30000);
INSERT INTO CUSTOMER VALUES (‘1003’,’Harsh’,40000);
INSERT INTO CUSTOMER VALUES (‘1004’,’Sunita’,15000);
INSERT INTO CUSTOMER VALUES (‘1005’,’Vineeta’,50000);
DBMS LAB VI 4 VIPIN KUMAR SARAOGI
Function to determine customer level
DELIMITER $$
CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE level varchar(10);
IF p_creditLimit >= 40000 THEN
SET level = 'PLATINUM';
ELSEIF (p_creditLimit < 40000 AND p_creditLimit >= 20000) THEN
SET level = 'GOLD';
ELSEIF p_creditLimit < 20000 THEN
SET level = 'SILVER';
END IF;
RETURN (level);
END ;
$$
DELIMITER ;
Query to use the function
SELECT Name, CustomerLevel(Creditlimit)
FROM
Customer
ORDER BY
Name;
DBMS LAB VI 5 VIPIN KUMAR SARAOGI
