SQL语言定义函数

• 代码中通常会存在很多相似部分，维护不方便，且容易出错。如果引入UDF并编写代码后，您还需要进行代码编译（Java）、创建资源和创建函数操作，过程比较繁琐，且性能不如内建函数。示例如下。

  SELECT
      NVL(STR_TO_MAP(GET_JSON_OBJECT(col, '$.key1')), 'default') AS key1,
      NVL(STR_TO_MAP(GET_JSON_OBJECT(col, '$.key2')), 'default') AS key2,
      ...
      NVL(STR_TO_MAP(GET_JSON_OBJECT(col, '$.keyN')), 'default') AS keyN
  FROM t;

• 使用类似Java中Lambda表达式的功能，把函数作为参数传给另一个函数。

• 支持在SQL脚本中使用SQL定义的UDF，并调用UDF。
• 支持函数类型的参数，调用时可以传入内置函数、UDF或SQL语言定义函数。
• 函数类型的参数可以为匿名函数，调用时可以传入匿名函数。

CREATE SQL FUNCTION function_name(@parameter_in1 datatype[, @parameter_in2 datatype...]) 
[RETURNS @parameter_out datatype] 
AS [BEGIN] 
function_expression 
[END];

• function_name：新建SQL语言定义函数的名称。函数名称需要唯一，同名函数只能注册一次，不能与系统内建函数同名。
• parameter_in：函数的输入参数。
• RETURNS：函数返回值变量。如果不指定，默认返回function_name的同名变量。
• parameter_out：函数返回参数。
• function_expression：函数表达式。

CREATE SQL FUNCTION MY_ADD(@a BIGINT) AS @a + 1;

CREATE SQL FUNCTION MY_SUM(@a BIGINT, @b BIGINT, @c BIGINT) RETURNS @my_sum BIGINT
AS BEGIN 
    @temp := @a + @b;
    @my_sum := @temp + @c;
END;

DESC FUNCTION my_add;

DROP FUNCTION my_add;

SELECT my_sum(col1, col2 ,col3) from t;

FUNCTION MY_ADD(@a BIGINT) AS @a + 1;
SELECT MY_ADD(key), MY_ADD(value) FROM src;

FUNCTION ADD(@a BIGINT) AS @a + 1;
FUNCTION OP(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);
SELECT OP(key, ADD), OP(key, ABS) FROM VALUES (1),(2) AS t (key);

--返回结果如下。
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 2          | 1          |
| 3          | 2          |
+------------+------------+

FUNCTION OP(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);
SELECT OP(key, FUNCTION (@a) AS @a + 1) FROM VALUES (1),(2) AS t (key);

• 方案1：该方案存在重复调用函数的问题，代码复用率较低，不推荐使用。命令示例如下：

  SELECT concat(lpad(split_part(d, '-', 1), 4, '0'), lpad(split_part(d, '-', 2), 2, '0'), lpad(split_part(d, '-', 3), 2, '0')) from values('2020-11-21'),('2020-1-01'), ('2019-5-1'), ('19-12-1') t (d);

• 方案2：通过创建SQL语言定义函数实现。推荐使用。命令示例如下：

  CREATE SQL FUNCTION y_m_d2yyyymmdd(@y_m_d string) RETURNS @yyyymmdd string
  AS BEGIN
      @yyyymmdd := concat(lpad(split_part(@y_m_d, '-', 1), 4, '0'), lpad(split_part(@y_m_d, '-', 2), 2, '0'), lpad(split_part(@y_m_d, '-', 3), 2, '0')) ;
  END;
  
  SELECT y_m_d2yyyymmdd(d) from values('2020-11-21'),('2020-1-01'), ('2019-5-1'), ('19-12-1') t (d);

  返回结果如下：

  +------------+
  | _c0        |
  +------------+
  | 20201121   |
  | 20200101   |
  | 20190501   |
  | 00191201   |
  +------------+