mysql functions code example

Example 1: mysql create function

-- MySQL

-- example
DELIMITER $$
CREATE FUNCTION f_employee_count(p_dept_no INTEGER) RETURNS INTEGER
  DETERMINISTIC NO SQL READS SQL DATA
  BEGIN
    DECLARE v_emp_count INTEGER;

    SELECT COUNT(*)
    INTO v_emp_count
    FROM EMPLOYEES E
    WHERE E.DEPT_NO = p_dept_no
    GROUP BY DEPARTMENT_NO;

    RETURN v_emp_count;
END$$
DELIMITER ;

/* syntax:
DELIMITER $$
CREATE FUNCTION <Your-procedure-name>(<arguments>) RETURNS <date-type>
  DETERMINISTIC NO SQL READS SQL DATA
  BEGIN
      DECLARE <variable-name> <data-type>

      <Code-that-sets-the-output-variable>; 

      RETURN <variable-name>;
END$$
DELIMITER ;
*/

Example 2: mysql some functions

MySQL Miscel­laneous Functions
BIT_COUNT
DES_EN­CRYPT
DATABASE
DES_DE­CRYPT
USER
LAST_I­NSE­RT_ID
SYSTEM­_USER
FORMAT
SESSIO­N_USER
VERSION
CURREN­T_USER
CONNEC­TION_ID
PASSWORD
GET_LOCK
OLD_PA­SSWORD
RELEAS­E_LOCK
ENCRYPT
IS_FRE­E_LOCK
DECODE
BENCHMARK
MD5
INET_NTOA
SHA1
INET_ATON
AES_EN­CRYPT
FOUND_ROWS
AES_DE­CRYPT
STRCMP

Example 3: create function in mysql

// created function checkTags
CREATE FUNCTION `data-management`.checkTags(tags text)
RETURNS text
DETERMINISTIC
BEGIN
return tags ;
END


// queries  according to it
SELECT * FROM `data-management`.properties p WHERE `data-management`.checkTags(p.tags = 'tag1,absntee')