Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Stored Functions

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Stored Functions

      MySQL Stored Functions

      A Stored Function is a set of SQL statements that perform a specific operation and then return a single value. Similar to built-in functions in MySQL, a stored function can be called from within any MySQL statement. The MySQL CREATE FUNCTION statement is used to create both stored functions and user-defined functions.
      By default, a stored function is associated with the default database. In order to use the CREATE FUNCTION statement, the user must have the CREATE ROUTINE database privilege.

      Syntax

      Following is the syntax for creating a new stored function 
      CREATE FUNCTION function_name(
         parameters...
      )
      RETURN datatype [characteristics]
      func_body;
      where,
      • function_name: It is the name of the function that we are creating. The name must not be same as the MySQL built-in function names.
      • parameters: These are the list of all parameters for the function. All the parameters are IN parameters by default. We cannot specify the IN, OUT or INOUT modifiers to the parameters.
      • datatype: This is the datatype of the value returned by the function.
      • characteristics: The CREATE FUNCTION statement will only be accepted if at least one of the characteristics (DETERMINISTIC, NO SQL, or READS SQL DATA) are specified in it's declaration.
      • fun_body: This contains set of MySQL statements that defines the behaviour of the function between the BEGIN and END commands.

      Example

      First, let us create a table with the name CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY(ID)
      );
      Here, we are inserting rows into the CUSTOMERS table 
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
      (2, 'Khilan', 25, 'Delhi', 1500.00),
      (3, 'Kaushik', 23, 'Kota', 2000.00),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00),
      (5, 'Hardik', 27, 'Bhopal', 8500.00),
      (6, 'Komal', 22, 'Hyderabad', 4500.00),
      (7, 'Muffy', 24, 'Indore', 10000.00);
      The table is displayed as
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      Creating a Function
      With the following CREATE FUNCTION query, we are creating a function that returns the year of birth of the customers based on their AGE 
      DELIMITER $$
      CREATE FUNCTION DATE_OF_BIRTH(AGE INT) RETURNS INT DETERMINISTIC
      BEGIN
         DECLARE currentdate DATE;
         SELECT CURDATE() INTO currentdate;
         RETURN year(currentdate)-AGE;
      END $$
      DELIMITER ;
      Now, we are calling the DATE_OF_BIRTH function using the following query 
      SELECT ID, NAME, DATE_OF_BIRTH(AGE) 
      AS 'YEAR_OF_BIRTH'
      FROM CUSTOMERS;

      Output

      The output for the above query is produced as given below
      ID
      NAME
      YEAR_OF_BIRTH
      1
      Ramesh
      1991
      2
      Khilan
      1998
      3
      Kaushik
      2000
      4
      Chaitali
      1998
      5
      Hardik
      1996
      6
      Komal
      2001
      7
      Muffy
      1999

      Calling Stored Function From Stored Procedure

      In MySQL, we can call a stored function from a stored procedure. The following statement creates a stored procedure with the name StudentDetails() that calls the DATE_OF_BIRTH() stored function.
      DELIMITER $$
      CREATE PROCEDURE CustomerDetails()
      BEGIN
      SELECT ID, NAME, DATE_OF_BIRTH(AGE) AS 'YEAR_OF_BIRTH'
      FROM CUSTOMERS;
      END $$
      DELIMITER ;
      Here, we are calling the CustomerDetails() stored procedure using CALL keyword 
      CALL CustomerDetails();

      Output

      The output for the above query is produced as given below
      ID
      NAME
      YEAR_OF_BIRTH
      1
      Ramesh
      1991
      2
      Khilan
      1998
      3
      Kaushik
      2000
      4
      Chaitali
      1998
      5
      Hardik
      1996
      6
      Komal
      2001
      7
      Muffy
      1999