Stored Function

How to use MySQL Stored Function

0 Shares

A stored function is a database object that contains a set of T-SQL statements, accepts parameters, performs an action and returns the result of the action as value. The return value can either be a single scalar value or a result set. A function can be called from inside a statement just like any other function and can return a scalar value.

A stored function have limited scope as compared to stored procedures. You can create a function in situation when you need to implement a programming logic that does not involve any permanent changes to the database object outside the function. For example you can not modify a database table from a function.

Creating Stored function

A stored function contains the following component.

  • Function Name with optional schema name
  • Input parameter name and datatype
  • Option applicable to the input parameter.
  • Return parameter data type and optional name.
  • Options applicable to the return parameter.
  • One or more T-SQL statements

To demonstrate a basic example of stored function, let’s start by creating a function fn_helloworld and test it.

We can call the function using a select statement:

The function will return the following message:

To demonstrate a basic example of stored function, let’s Go for a scenario like we will calculate the grade  of the student from there marks lets

>80 Grade A

>60 and <80 Grade B

>30 and < 60 Grade C

< 30 Fail

let’s start by creating a database that we can use for testing purposes.

Next, create a table in the UNIVERSITY database named t_student. To do this, run the following SQL statement:

Lets Insert some data to our student table

Now we will create a function name fn_grade which will return the grade of the students

To invoke the stored function, use the following MySQL statement:

The function will return the following message:

Altering a Stored Function

A stored procedure can be modified by ALTER FUNCTION statement

Dropping a Stored Function

You can drop a stored function from  database by using DROP FUNCTION statement.

 

0 Shares

Rasmi Ranjan

A tech guy who loves to learn code. I'm open source enthusiast and a delicious coffee maker

Leave a Reply

Your email address will not be published. Required fields are marked *