WITH FUNCTION/PROCEDURE
1. 目的
本文档解释 IvorySQL 中 WITH FUNCTION 和 WITH PROCEDURE 的用途，实现 Oracle 风格的 SQL 内嵌 PL/SQL 函数和过程功能。
WITH FUNCTION/PROCEDURE 是 Oracle 数据库的 Subquery Factoring with PL/SQL Declarations 特性，允许在 SQL 的 WITH 子句（公共表表达式，CTE）中直接定义 PL/SQL 函数和过程。
2. 功能说明
2.1. 基本语法
在 Oracle 兼容模式（compatible_db = ORA_PARSER）下，WITH 子句支持以下扩展语法：
WITH
FUNCTION func_name ( [ param_list ] ) RETURN return_type
{ IS | AS }
[ declare_section ]
BEGIN
statements
END [ func_name ] ;
PROCEDURE proc_name ( [ param_list ] )
{ IS | AS }
[ declare_section ]
BEGIN
statements
END [ proc_name ] ;
cte_name AS ( SELECT ... )
SELECT ...3. 支持的语句类型
WITH 内嵌函数/过程仅允许出现在以下顶层语句中：
-
SELECT语句（最常见） -
INSERT … SELECT语句（Oracle 兼容形式，WITH FUNCTION 位于 INSERT INTO 之后）
以下语句不支持（Oracle 不允许，报 ERRCODE_FEATURE_NOT_SUPPORTED）：
-
WITH FUNCTION … UPDATE …：WITH FUNCTION 不能位于 UPDATE 之前 -
WITH FUNCTION … DELETE …：WITH FUNCTION 不能位于 DELETE 之前 -
WITH FUNCTION … MERGE …：WITH FUNCTION 不能位于 MERGE 之前
如需在 DML 中使用可复用逻辑，应定义 schema 级别的函数（CREATE FUNCTION）。
4. 语法示例
4.1. 最简单的内嵌函数
WITH
FUNCTION double_it(n NUMBER) RETURN NUMBER AS
BEGIN RETURN n * 2; END;
SELECT double_it(5) FROM dual;
-- 期望输出：104.2. 函数与 CTE 混合
WITH
FUNCTION tax(amt NUMBER) RETURN NUMBER AS
BEGIN RETURN amt * 0.1; END;
orders AS (SELECT 100 AS amount)
SELECT amount, tax(amount) FROM orders;
-- 期望输出：100 | 104.3. 多个内嵌函数
WITH
FUNCTION add1(n NUMBER) RETURN NUMBER AS BEGIN RETURN n+1; END;
FUNCTION mul2(n NUMBER) RETURN NUMBER AS BEGIN RETURN n*2; END;
SELECT mul2(add1(3)) FROM dual;
-- 期望输出：84.4. 递归函数
WITH
FUNCTION factorial(n NUMBER) RETURN NUMBER AS
BEGIN
IF n <= 1 THEN RETURN 1; END IF;
RETURN n * factorial(n-1);
END;
SELECT factorial(5) FROM dual;
-- 期望输出：1204.5. OUT 参数（仅限 PROCEDURE）
WITH FUNCTION 不允许声明 OUT / IN OUT 参数（与 Oracle ORA-06572 行为一致）；
仅 WITH PROCEDURE 允许 OUT / IN OUT 参数。
-- 正确：PROCEDURE 可声明 OUT 参数
WITH
PROCEDURE swap(val IN NUMBER, result OUT NUMBER) AS
BEGIN
result := val * 10;
END;
SELECT 1 FROM dual; -- 过程由同一 WITH 块内的其他子程序调用，不直接出现在 SELECT 表达式中
-- 错误：FUNCTION 不允许 OUT 参数
WITH
FUNCTION bad_func(val NUMBER, result OUT NUMBER) RETURN NUMBER AS
BEGIN RETURN val; END;
SELECT bad_func(5) FROM dual;
-- 期望输出：ERROR: WITH FUNCTION "bad_func" cannot declare OUT or IN OUT parameters4.6. 默认参数值
WITH
FUNCTION calc(n NUMBER DEFAULT 10) RETURN NUMBER AS
BEGIN RETURN n * 2; END;
SELECT calc() FROM dual;
-- 期望输出：204.7. 异常处理
WITH
FUNCTION safe_div(a NUMBER, b NUMBER) RETURN NUMBER AS
BEGIN
RETURN a / b;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END;
SELECT safe_div(1, 0) FROM dual;
-- 期望输出：NULL4.8. 与 DML 集成
-- 允许：INSERT INTO ... WITH FUNCTION ... SELECT ...（Oracle 兼容形式）
WITH
FUNCTION get_bonus(sal NUMBER) RETURN NUMBER AS
BEGIN RETURN sal * 1.2; END;
INSERT INTO emp_bonus (empno, bonus)
SELECT empno, get_bonus(sal) FROM emp WHERE deptno = 10;
Oracle 不允许 WITH FUNCTION 位于 UPDATE、DELETE、MERGE 之前。
IvorySQL 遵循相同限制，此类用法会报 ERRCODE_FEATURE_NOT_SUPPORTED 错误。
|
6. 与现有功能的关系
| 现有功能 | 关系 |
|---------|------|
| PL/iSQL 嵌套子程序 | 直接复用：利用现有编译/执行基础设施 |
| 标准 CTE（WITH…AS (SELECT …)） | 共存：在同一 WITH 子句中混用 |
| RECURSIVE CTE | 共存：WITH RECURSIVE 与内嵌函数可同时使用 |
| Oracle Package | 类似：Package 的过程/函数也是 session 级临时注册 |
| PL/iSQL CREATE FUNCTION | 不同：WITH 内嵌函数不持久化，不写入系统目录 |
7. 错误处理
7.1. 重复定义
WITH
FUNCTION dup(n NUMBER) RETURN NUMBER AS BEGIN RETURN n; END;
FUNCTION dup(n NUMBER) RETURN NUMBER AS BEGIN RETURN n * 2; END;
SELECT dup(1) FROM dual;
-- 期望输出：ERROR: WITH clause function "dup" is defined more than once with the same argument types7.2. 在 PG_PARSER 模式下使用
-- 在 PG_PARSER 模式下尝试使用 WITH FUNCTION 语法
SET compatible_db = PG_PARSER;
WITH FUNCTION foo(n NUMBER) RETURN NUMBER AS BEGIN RETURN n; END;
SELECT foo(1);
-- 期望输出：ERROR: syntax error at or near "FUNCTION"7.3. 函数体语法错误
WITH
FUNCTION broken(n NUMBER) RETURN NUMBER AS
BEGIN
RETRUN n; -- 拼写错误
END;
SELECT broken(1) FROM dual;
-- 期望输出：ERROR: syntax error at or near "RETRUN"