EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB Functions
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB Functions

By Aanchal SharmaAanchal Sharma

MariaDB Functions

Definition of MariaDB Functions

  • MariaDB functions are stated as the stored program in the server which a user can pass the required parameters into and after that returning a value as output. The MariaDB functions can be created and dropped out too.
  • MariaDB includes many stored functions and procedures which consist of aggregate functions such as MIN, MAX, COUNT, SUM, AVG, and so on.
  • The MariaDB functions also comprises of Date and Time type of functions like CURDATE(), DATE(), CURTIME(), DATEDIFF(), NOW(), DATE FROMAT(), HOUR(), MINUTE(), SECOND(), etc.
  • Again, the MariaDB functions extends to have the numeric type also such as TRUNCATE(), COS(), DIV(), DEGREES(), EXP(), FLOOR(), LN(), LOG(), SQRT(), etc.
  • The string functions are included in this MariaDB functions like RIGHT(), INSTR(), LENGTH(), INSERT(), LOCATE(), LOWER(), REPLACE(), REPEAT(), SUBSTRING(), TRIM() and so on.

MariaDB Functions

The functions can be created in other languages too, so a user can create his/her own functions in MariaDB. We have the following syntax for creating a function in the MariaDB server:

CREATE [ DEFINER = {UserName | Current_User } ] FUNCTION FunctionName [ (datatype of parameter [, datatype of parameter])] RETURNS datatype_of_return [ Language SQL | Deterministic | Non Deterministic | {NO SQL | CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA} | SQL SECURITY {INVOKER | DEFINER} | COMMENT ‘value_of_comment’] BEGIN
Declaration_section
Executable_section
END;

Let us explain the terms used in the above syntax structure:

  • DEFINER: DEFINER is the clause that is optionally applied and if not present then the definer specifies the user that has created the MariaDB function. Whereas if anyone want to state the definer name as a specific one then, we must involve the clause DEFINER in the function where UserName denotes the definer.
  • FunctionName: In MariaDB, it provides a name to the function while creating it.
  • Parameter: It denotes the parameter either one or many that are passed into the MariaDB function. All parameters should be reflected to be IN parameters (INOUT or not OUT parameters) while building a function where these function parameters may be referenced by this function but cannot be overwritten by the MariaDB function.
  • Datatype_of_return: It defines the datatype of the return value of the MariaDB function.
  • Language SQL: It is present in the syntax code of the function for movability but this will cause no influence on the function.
  • Deterministic: It is responsible for returning only one result always of the function provided a set of input function parameters.
  • Non-Deterministic: It is responsible for returning an unalike result of the function provided a set of input function parameters. This output result will be affected by the data of table, server variables, or random numbers.
  • CONTAINS SQL: It is a default type and an informative clause that conveys MariaDB server that the function includes SQL but the MariaDB database may not confirm that it is true.
  • NO SQL: It is revealing clause which is not implemented and may not have any influence on the function in MariaDB.
  • READS SQL DATA: This is also a helpful clause that instructs MariaDB server that the function will perform reading of data using the SELECT query statements but cannot alter the data.
  • MODIFIES SQL DATA: It also denotes an illuminating clause which states MariaDB server that this function may change the SQL data by means of UPDATE, INSERT, DELETE, and other type of DDL statements.
  • Declaration_section: It defines the area in the function in MariaDB where we declare_local_variables.
  • Executable_section: It defines the area in the function in MariaDB where the user inserts the code for the function.

For demonstration, suppose we have the following syntax code to implement the function creation in the MariaDB server:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

DELIMITER//
CREATE FUNCTION ValueOfCal(First_value INT)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE TotalValue INT;
SET TotalValue = 0;
Label2: WHILE TotalValue <= 4000 DO
SET TotalValue =TotalValue + First_Value;
END WHILE label2;
RETURN TotalValue;
END; //
DELIMITER;

Output:

MariaDB Functions 1

Now, the user can reference the new function created as follows:

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,328 ratings)

SELECT ValueOfCal (2000);

Output:

MariaDB Functions 2

After execution, the query above will provide the result as specified.

If the user wants to view all the stored functions in MariaDB, then we need to use the SHOW statement as follows:

SHOW FUNCTION STATUS;

Output:

MariaDB Functions 3

As you can see that in the MariaDB database named books there are two functions created and active to work with. This query command provides as result all the characteristics of stored functions. But do remember that this Show Function Status; command fetches the functions which we have been granted as privileges to access.

Suppose, if we want to view any particular stored functions in the database then we will apply the WHERE clause with the query holding a search condition for function name as:

SHOW FUNCTION STATUS WHERE searching_condition;

Also, with LIKE clause one can put the search condition in the SHOW query to fetch specifically named functions using the pattern search as below:

SHOW FUNCTION STATUS LIKE ‘%Specific pattern%’;

Drop Function:

In MariaDB, once the MariaDB function is created, after some time the user might need to delete it from the database server. Then, for this the syntax will be written using the DROP keyword as follows:

DROP FUCNTION {IF EXISTS} Name_of_Function;

Here, the Name_of_Function term defines the name of the MariaDB function that was created and now the user wishes to remove it.

Therefore, let us view an example illustrating the drop of a function in MariaDB server and how to code for it as follows:

DROP FUNCTION ValueOfCal;

Output:

MariaDB Functions 4

Here, we have mentioned the function created previously and that will be dropped now when the above function will be executed. If we run the show command, then the result will be as follows:

SHOW FUNCTION STATUS;

Output:

Show Query Box

Conclusion

  • Contrasting to the procedures, a user should provide parameters to the MariaDB functions and this function should output a value as result. MariaDB maintenances the VALUES statement which makes it calmer for testing a function created as MariaDB functions in the server.
  • MariaDB functions cover stored aggregate type functions composing structured values which help to read different table rows and return values.

Recommended Articles

This is a guide to MariaDB Functions. Here we discuss the definition, MariaDB Functions, and examples with code implementation respectively. You may also have a look at the following articles to learn more –

  1. MariaDB IF
  2. MariaDB Delete User
  3. MariaDB GROUP_CONCAT
  4. MariaDB wait_timeout
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more