Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Create and Call Deterministic Functions in MySQL – Querychat

In the article, we learn how to create the Deterministic function in MySQL. Providing examples of how to perform global settings and call the function.

MySQL allows us to create stored functions and call them. It contains a collection of SQL commands/expressions in the form of routines that are stored in a database. We can call a stored function by specifying its name and parameter list wherever an expression of the appropriate data type may be used.

MySQL Deterministic Functions

A stored function specified with DETERMINISTIC will return the same value for the same input parameters every time we call it. But when we do not specify it, the function will be considered as NON-DETERMINISTIC by default, where it can return different results for the same input parameters.

The function can be used explicitly by providing global settings in MySQL server, which is set as NOT DETERMINISTIC by default. The syntax to set it on is as follows:

SET GLOBAL log_bin_trust_function_creators = 1;

Syntax to create a stored function specified with DETERMINISTIC

See the following syntax and description for creating a deterministic function in MySQL:

CREATE FUNCTION [FunctionName] ( [ParameterName] [DataType] )

RETURNS [DataType] [DETERMINISTIC]
  • CREATE FUNCTION statement is a header to create functions.
  • [FunctionName] contains the name of the function to be created.
  • [ParameterName] contains the name of parameters as the input value to the function according to the given data type.
  • RETURNS [DataType] is to determine the data type that will be returned by the function.
  • [DETERMINISTIC] is to determine which users can use/execute this function.

Examples for the Deterministic function

Here is a table named student that contains columns such as ID and name of the students. The table is created in the school database. See the following syntax to create them and fill the table with some sample records:

CREATE DATABASE school;

 

USE school;

 

CREATE TABLE student (ID INT, student_name VARCHAR(25));

 

INSERT INTO student VALUES 

(1,'Zein'), 

(2, 'Xavier');

We can see the data in the student table using the command “SELECT * FROM student;” and the results are as follows:

id student_name
1 Zein
2 Xavier
3 Kirk

Table 1. An example of the student table

Example #1 Create a DETERMINISTIC function with a parameter input

We create a stored function specified with DETERMINISTIC named getStudentName with the student_id as the input parameter.  The function contains the SELECT statement to get a student’s name by an ID. The syntax to create the function is as follows:

DELIMITER ##

CREATE FUNCTION getStudentName (student_ID INT)

RETURNS VARCHAR(25) DETERMINISTIC

BEGIN

   DECLARE nm VARCHAR(25);

   SELECT student_name INTO nm

   FROM student WHERE ID = student_ID;

   RETURN nm;

END##

The DELIMITER is used to tell MySQL the end of the statement. By default, we use “;“, but we can also use something else like “##” to end the statement (see in the example above ). The getStudentName function is specified with DETERMINISTIC because it returns the same student name when we call it with the same student_ID value.

Example #2 Call the getStudentName function

By the example functions above that have been created, afterward, we can call the functions using the SELECT statement. Suppose we want to get the name of the students in the student table by using the getStudentName function with an ID of 2. 

See the following syntax to call the function.

SELECT getStudentName(2) AS "Name"##

The result will appear:

Name
Xavier

Table 2. Execution of the getStudentName function

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc