How To Create Function In Mysql

Technical Questions and Answers

Selected Reading
UPSC IAS Exams Notes
Developer's Best Practices
Questions and Answers
Effective Resume Writing
HR Interview Questions
Computer Glossary
Who is Who

How to create a simple MySQL function?

You can create a function using create function command. The syntax is as follows −

delimiter // DROP FUNCTION if exists yourFunctionName; CREATE FUNCTION yourFunctionName(Parameter1,...N) returns type BEGIN # declaring variables; # MySQL statementns END // delimiter ;

First, here we will create a table and add some records in the table. After that, a simple function will be created. The following is the query to create a table −

mysql> create table ViewDemo    −> (    −> Id int,    −> Name varchar(200),    −> Age int    −> ); Query OK, 0 rows affected (0.58 sec)

Insert records in the table using insert command. The query is as follows −

mysql> insert into ViewDemo values(1,'John',23); Query OK, 1 row affected (0.15 sec)  mysql> insert into ViewDemo values(2,'Sam',24); Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from ViewDemo;

The following is the output −

+------+------+------+ | Id   | Name | Age  | +------+------+------+ |    1 | John | 23   | |    2 | Sam  | 24   | +------+------+------+ 2 rows in set (0.00 sec)

Now we will create a function that takes on integer parameters and returns strings. The purpose of this function is to search records with given id. If the given id matches with table id then it returns the name otherwise it will give an error message like not found.

The function is as follows −

mysql> SET GLOBAL log_bin_trust_function_creators = 1; Query OK, 0 rows affected (0.00 sec) mysql> drop function if exists searchRecord;    ->    -> create function searchRecord(yourId int) returns char(100)    -> begin    -> declare Name1 char(100) default "No Name Found For This Id";    -> select Name into Name1 from ViewDemo where Id =yourId;    -> return Name1;    -> end // Query OK, 0 rows affected (0.21 sec) Query OK, 0 rows affected (0.33 sec) mysql> delimiter ;

Now to check the function is working with given id.

Case 1 − When the given id is present.

The query is as follows −

mysql> select searchRecord(2) as Found;

The following is the output −

+-------+ | Found | +-------+ | Sam   | +-------+ 1 row in set (0.00 sec)

Case 2 − When the given id is not present.

The query is as follows −

mysql> select searchRecord(100) as Found;

The following is the output displaing the record isn't there −

+---------------------------+ | Found                     | +---------------------------+ | No Name Found For This Id | +---------------------------+ 1 row in set (0.00 sec)

Published on 16-Jan-2019 09:45:02

Related Questions & Answers
How to Create a Simple Program in C++?
How to create a simple screen using Tkinter?
C# Program to create a Simple Thread
Create a simple calculator using Java Swing
How can I create a simple message box in Tkinter?
How to use ArrayAdapter in android to create simple ListView?
How to create a simple "star rating" look with CSS?
Create a simple Numpy Jupyter Notebook using Docker
How to use ArrayAdapter in android to create a simple listview in Kotlin?
How to rotate a simple matplotlib Axes?
Which PHP function is used to create a MySQL table?
How to create a MySQL view?
How to Create a MySQL Sequence?
How to create a simple alert dialog with Ok and cancel buttons using Kotlin?
Simple way to toggle a value of an int field in MySQL