chapter12_数据库编程_4_用户定义函数

• 函数

  (1) 函数也是一种存储过程，只不过它可以返回值

  (2) 返回值可以是__单个标量值__或__结果集__(MYSQL中不能返回结果集，用存储过程才可以返回结果集)，返回标量值的函数称为__标量函数__，返回结果集的函数称为__表值函数__

  (3) 由于函数可以返回值，所以可以直接使用

  示例

    SELECT BOCHEN_FUNCTION();
  

  (4) 函数可以分为 系统函数 和 用户定义函数UDF(User Defined Function)

  (5) 使用函数的__优点__

  1° 模块化程序设计

  2° 执行速度更快。通过缓存计划并在重复执行时重用它来降低编译开销，使用时无需重新解析和优化

  3° 减少网络流量

• MYSQL的函数

  (1) 语法

    CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
  

  其中

    func_parameter: param_name type
  
    type: Any valid MySQL data type
  
    characteristic:
        COMMENT 'string'
       | LANGUAGE SQL
       | [NOT] DETERMINISTIC
       | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
       | SQL SECURITY { DEFINER | INVOKER }
  
    routine_body:
        Valid SQL routine statement
  

  (2) By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as dbname.spname when you create it.

  (3) The CREATE FUNCTION statement is also used in MySQL to support UDFs (user-defined functions). A UDF can be regarded as an external stored function.

  (4) 调用函数：To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.

  (5) 创建函数需要CREATE ROUTINE权限。CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege.

  (6) The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.

  (8) 函数的输入参数只能是IN类型。Note:Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.

  (9) 示例1

    CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC
        RETURN CONCAT('Hello, ',s,'!');
  
    mysql> SELECT hello('world');
           +----------------+
           | hello('world') |
           +----------------+
           | Hello, world!  |
           +----------------+
  
    __示例2__
  
    USE temp;
  
    DELIMITER //
  
    CREATE FUNCTION GetEmployeeInformationByID(input_id INT)
    RETURNS VARCHAR(300)
  
    BEGIN
        RETURN(SELECT CONCAT('data:', data) FROM temp.t1 WHERE id = input_id);
    END;//
  
    DELIMITER ;
  
    mysql> SELECT GetEmployeeInformationByID(1) AS data;
    +--------+
    | data   |
    +--------+
    | data:2 |
    +--------+
  

  (10) 返回结果集的可以用在存储过程中，不能用在函数中Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can’t return a result set in the given context error occurs (ER_SP_BADSELECT).

  (11) 卸载函数 DROP FUNCTION xxx;

• MYSQL用户定义函数示例

  function_GetEmployeeInformationByID.sql

    USE temp;
  
    /* CREATE TABLE `t1` (
        `id` int(11) DEFAULT NULL,
        `data` int(11) DEFAULT NULL
    ) */
  
    DELIMITER //
  
    CREATE FUNCTION GetEmployeeInformationByID(input_id INT) RETURNS VARCHAR(300)
    BEGIN
        RETURN(SELECT CONCAT('data:', data) FROM temp.t1 WHERE id = input_id);
    END;//
  
    DELIMITER ;
  

  function_loop_and_cursor_test.sql

    USE temp;
  
    DROP FUNCTION IF EXISTS looptest;
  
    DELIMITER //
  
    CREATE FUNCTION looptest() RETURNS INT
    BEGIN
        DECLARE v_total INT;
        DECLARE v_counter INT;
        DECLARE done INT DEFAULT FALSE;
        DECLARE csr CURSOR FOR SELECT data FROM t1;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
        SET v_total = 0;
        OPEN csr;
  
        read_loop: LOOP
            FETCH csr INTO v_counter;
  
            IF done THEN
                LEAVE read_loop;
            END IF;
  
            SET v_total = v_total + v_counter;
        END LOOP;
  
        CLOSE csr;
  
        RETURN v_total;
    END;//
  
    DELIMITER ;