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 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.

(7) Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.

(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 ;