MySQL Stored Function
A stored
function is a stored program that returns a single value.
Basically specific code that performs specific operations
and then returns a result.
It is used to encapsulate common formulas or business rules.
Reusable
SQL statements or stored programs and reduce
data inconsistencies.
Different
from a stored procedure.
Improve
the readability and maintainability of the procedural code.
help to reduce network traffic in
client/server applications, without manipulating data.
Improve overall system performance.
Syntax:-
create
stored functions by the CREATE FUNCTION statement.
DELIMITER $$
CREATE FUNCTION function_name ( parameter 1, parameter2,…
)
RETURNS datatype [NOT] DETERMINISTIC
BEGIN
-- statements
END $$
DELIMITER ;
In this
syntax:
1. Define the stored function name after CREATE FUNCTION keywords.
2. list all parameters of the
stored function inside the parentheses. By default, all parameters are
the IN parameters not specify IN , OUT or INOUT modifiers to parameters)
3. Define the data type of the return value
in the RETURNS statement,
4. Define if a function is deterministic or
not by the DETERMINISTIC keyword. A deterministic function
always returns the same result for the same input parameters whereas a
non-deterministic function returns different results for the same input
parameters. MySQL uses the NOT DETERMINISTIC option by default.
5. Code write in the body of the stored
function in the BEGIN END block.
Inside the body section, need to specify at least one RETURN statement.
The RETURN the statement returns a value to the
calling programs.
6. When control reached to the RETURN statement, the execution of the
stored function is terminated immediately.
Example
DELIMITER
|
CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2
INT, n3 INT, n4 INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE avg INT;
SET avg = (n1+n2+n3*2+n4*4)/8;
RETURN avg;
END|
SELECT
WEIGHTED_AVERAGE(70,65,65,60);
Output:-
WEIGHTED_AVERAGE(70,65,65,60)
'63'
======================
Example:-
CREATE TABLE
sfdata(mark1 INT,mark2 INT,mark3 INT,mark4 INT,name VARCHAR(50));
INSERT INTO
sfdata VALUES(70,65,65,60,'ABC');
INSERT INTO
sfdata VALUES(95,94,75,50,'XYZ');
CREATE
FUNCTION WEIGHTED_AVERAGE2 (v1 VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE
i1,i2,i3,i4,avg INT;
SELECT mark1,mark2,mark3,mark4 INTO
i1,i2,i3,i4 FROM sfdata WHERE name=v1;
SET avg = (i1+i2+i3*2+i4*4)/8;
RETURN avg;
END
Execute
function
SELECT
WEIGHTED_AVERAGE2('XYZ') AS XYZ, WEIGHTED_AVERAGE2('ABC') AS ABC;
OUTPUT:-
***************************
1. row ***************************
XYZ: 67
ABC: 63
========================
Example:-
DELIMITER //
CREATE FUNCTION CalcIncome (
starting_value INT )
RETURNS INT
BEGIN
DECLARE income INT;
SET income = 0;
label1: WHILE income <= 3000 DO
SET income = income + starting_value;
END WHILE label1;
RETURN income;
END; //
DELIMITER ;
CALL FUNCTION:-
SELECT CalcIncome (500);
Output:-
3500
Types of functions (User-defined
functions)
1.
Built-in functions
These functions are already implemented in the MySQL server and allow
to perform different types of manipulations on the data.
These functions can be categorized into the following
categories.
- Strings functions –
operate on string data types
- Numeric functions –
operate on numeric data types
- Date functions –
operate on date data types
- Aggregate functions –
operate on all of the above data types and produce summarized result sets.
2.
Stored functions
Stored functions are just like built-in functions but are defined by
use for specific needs.
It can be used in SQL statements just like any other function.
DROP FUNCTION
Syntax:-
DROP FUNCTION [IF EXISTS] function_name;
DROP FUNCTION CalcIncome
;
Listing Stored Functions
to show
stored functions from databases by the SHOW FUNCTION STATUS or querying the data dictionary.
Syntax:
SHOW FUNCTION STATUS ;
The SHOW FUNCTION STATUS statement returns all characteristics of stored functions.
If show
stored functions in a particular database, use a WHERE clause in the SHOW FUNCTION STATUS :
SHOW FUNCTION STATUS WHERE search_condition;
example:- shows all stored functions in the sample
database classicmodels:
SHOW FUNCTION STATUS WHERE db = 'classicmodels';
==========================================================================
0 Comments