EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

MariaDB Functions

By Aanchal SharmaAanchal Sharma

Home » Data Science » Data Science Tutorials » Database Management Tutorial » MariaDB Functions

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;

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

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:

SELECT ValueOfCal (2000);

Output:

MariaDB Functions 2

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

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,282 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management 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? 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 List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • 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
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Databricks CLI
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Mapping Constraints in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

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
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & 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
Free Data Science Course

Hadoop, Data Science, Statistics & 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 Login

Forgot Password?

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.

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.

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

Independence Day Offer - SQL Certification Course Learn More