一、创建函数：RETURN 要写两次

函数比过程多一个 RETURN 子句，位置在参数列表后面、IS 前面。 而且这个 RETURN 会出现两次，作用完全不同。

头部不写是语法错，编译就过不去。 体内漏了是运行时错——报 ORA-06503（函数未返回值）。 这个错很阴：编译能过，跑到那条分支才炸，所以函数体里每条分支都要有 RETURN。 参数模式也能写 OUT，但强烈不建议。 因为函数一旦出现在 SQL 里，就没有「调用方变量」这个概念了，OUT 参数根本无处安放。 函数就老老实实「进参数、出返回值」。

二、调用函数：三种场合，限制各不同

函数有三种调法，能力从窄到宽：

SQL 里调用函数的两条硬限制，各有各的错误码： 一是「查询里做 DML」，报 ORA-14551（cannot perform a DML operation inside a query）。 二是「改正在被改的那张表」，报 ORA-04091（table is mutating）。 为什么禁得这么死？因为调用次数会被放大。 select get_grade(sal) from emp 查一万行，函数就跑一万次。 里面但凡有一次 update，就是一万次写操作，性能和一致性都撑不住。 这也解释了那条经验：函数体要短、要快、别在里面查表。 要查表就先 join 出来，用现成的列参与计算—— 而不是在函数里对着每一行再查一次（那叫 N+1 次查询，是典型的性能坑）。 函数也能当聚合用，但那是另一套机制。 sum、avg 这些是「逐行喂进来、最后一次返回」， Oracle 里要写 CREATE TYPE ... OBJECT 加 ODCIAggregate 接口， 比写普通函数复杂得多。日常几乎不用，但知道原理有好处—— 它让你明白聚合函数不神秘，就是「攒着，最后交一次」。

三、删除函数：弱依赖与「无效」状态

DROP FUNCTION get_grade; 也是一句。 但函数比过程更容易牵连别人——视图、别的函数、触发器都可能引用它。 Oracle 不会阻止你删，只会让依赖它的对象变成 INVALID。 这时候访问那些对象会报 ORA-04063（对象有错误）。 修法是重新编译：ALTER VIEW v COMPILE; 或 ALTER FUNCTION f COMPILE;。 这套机制叫「弱依赖」，是 Oracle 一个很有特点的设计。 好处是改一个函数不用把所有依赖它的对象一起重建；代价是「失效状态」这件事得有人盯着。 巡检该看的一句： select object_name, object_type from user_objects where status = 'INVALID'; 正常应该是 0 行。 有行就说明有东西坏了，趁着还没人报错赶紧编译。

四、动手 Demo：把 Python 函数注册成「SQL 能调的函数」

沙箱里没有 Oracle，CREATE FUNCTION 执行不了。 但「函数能出现在 SQL 里」这件事，SQLite 是支持的—— 它有个 create_function 接口，能把任意一门语言的函数注册进去。 我用它把 get_grade 注册成 SQL 函数，再顺手数了数它被调了几次， 最后还注册了一个聚合函数 my_avg，看它和普通函数的机制差在哪。

import sqlite3

c = sqlite3.connect(':memory:')
c.execute('CREATE TABLE emp (empno INT, ename TEXT, sal REAL, deptno INT)')
c.executemany('INSERT INTO emp VALUES (?,?,?,?)', [
    (7369,'SMITH',800,20), (7499,'ALLEN',1600,30), (7521,'WARD',1250,30),
    (7566,'JONES',2975,20), (7654,'MARTIN',1250,30), (7698,'BLAKE',2850,30),
    (7782,'CLARK',2450,10), (7788,'SCOTT',3000,20), (7839,'KING',5000,10),
    (7844,'TURNER',1500,30), (7876,'ADAMS',1100,20), (7900,'JAMES',950,30),
    (7902,'FORD',3000,20), (7934,'MILLER',1300,10)])

# 把 Python 函数注册成「SQL 能调的函数」
calls = [0]
def get_grade(p_sal):
    calls[0] += 1
    if p_sal < 1000:
        return '低'
    if p_sal < 3000:
        return '中'
    return '高'

c.create_function('get_grade', 1, get_grade)
for r in c.execute('select ename, sal, get_grade(sal) from emp '
                   'where deptno = 20 order by sal desc'):
    print(r)
print('函数被调用了', calls[0], '次')

# 聚合函数：一行一行喂进来，最后给一个值
class MyAvg:
    def __init__(self):
        self.s, self.n = 0.0, 0
    def step(self, v):
        if v is not None:
            self.s += v
            self.n += 1
    def finalize(self):
        return round(self.s / self.n, 2) if self.n else None

c.create_aggregate('my_avg', 1, MyAvg)
for r in c.execute('select deptno, my_avg(sal) from emp '
                   'group by deptno order by deptno'):
    print(r)

实跑输出：

--------------------------------------------------------------------
第二十七课：函数——必须 RETURN，而且能出现在 SQL 里
--------------------------------------------------------------------

  **函数和过程的差别只有一条：函数必须 `RETURN` 一个值。**
  **但就是这一条，带来一个巨大的能力差别：函数能出现在 SQL 语句里。**
  **`SELECT f(x) FROM t` 里那个 `f` 就是函数。过程做不到这一点**——
  **过程是「执行」，函数是「求值」。**


  创建：多一个 RETURN 子句
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | CREATE OR REPLACE FUNCTION get_grade (
  |     p_sal IN NUMBER
  | ) RETURN VARCHAR2 IS
  |   v_grade VARCHAR2(10);
  | BEGIN
  |   IF p_sal < 1000 THEN
  |     v_grade := '低';
  |   ELSIF p_sal < 3000 THEN
  |     v_grade := '中';
  |   ELSE
  |     v_grade := '高';
  |   END IF;
  |   RETURN v_grade;
  | END;
    RETURN 有两处：头部声明类型，体内返回实际值。

  **注意 `RETURN` 出现了两次，作用完全不同：**
  **头部那个是「声明返回什么类型」，体内那个是「现在把值交出去」。**
  **头部不写是语法错，体内漏了是运行时错（`ORA-06503`）。**
  **函数里不能有 DML 吗？可以有，但不建议。**
  **函数会在 SQL 里被调用，一条查一万行的语句就调一万次**——
  **每次都去改表，性能和一致性都会出事。**
  **Oracle 还真的用两条错误码把这个「不建议」变成了「很多时候干脆不行」：**
  **一是「查询里做 DML」——**
  **`select f(x) from t` 里的 `f` 又去 `insert`／`update`，报 `ORA-14551`**
  （cannot perform a DML operation inside a query）。
  **二是「改自己这张表」——**  **对 T 表做 `update` 时触发器或函数又去改 T 表，报 `ORA-04091`**
  （table is mutating）。

  在沙箱里把它变成真能调用的函数
  --------------------------------------------------------------------

  SQL> select ename, sal, get_grade(sal) as 档 from emp where deptno = 20 order by sal desc
  ENAME SAL  档
  -------------
  SCOTT 3000 高
  FORD  3000 高
  JONES 2975 中
  ADAMS 1100 中
  SMITH 800  低
  已选择 5 行
  **这一列「档」是真的算出来的**——
  **我用 `create_function` 把 Python 函数注册进 SQLite，它就成了「能被 SQL 调的函数」。**
  **这正是 Oracle 里函数最有价值的地方：它让 SQL 有了「自定义表达式」。**
  **注意这里调用了 5 次**（5 行数据各一次）。
  **如果这是一百万行的表，就调用一百万次。**  **所以函数体要短、要快，别在里面查表。**

  函数还能当聚合用
  --------------------------------------------------------------------

  SQL> select deptno, my_avg(sal) as 平均 from emp group by deptno order by deptno
  DEPTNO 平均
  --------------
  10     2916.67
  20     2175
  30     1566.67
  已选择 3 行
  **`create_aggregate` 注册的是「聚合函数」**——
  **它按行喂进来，最后给一个值。**
  **Oracle 里对应的是 `CREATE TYPE ... OBJECT` 加 `ODCIAggregate` 接口**，
  **写起来复杂得多，但机制一样：一行一行积累，最后一次性返回。**
  **这一段的价值是让你看清「聚合函数」到底是什么**——
  **`sum`、`avg`、`count` 一点都不神秘，它们就是「逐行累加、最后一次返回」的函数。**

  删除：DROP FUNCTION，以及依赖问题
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | DROP FUNCTION get_grade;

  **如果某个视图或者别的对象引用了这个函数，删掉它会让那些对象变成「无效」。**
  **Oracle 不会阻止你删，只会让依赖它的对象失效**（报 `ORA-04063`）。
  **失效之后要重新编译：`ALTER VIEW v COMPILE`。**
  **这是 Oracle 一个很有特点的设计：对象之间是「弱依赖」。**
  **好处是改一个函数不用把所有依赖它的对象都重建；代价是「失效状态」这件事要人盯着。**
  **`select * from user_objects where status = 'INVALID'` 是每天巡检该看的一句。**

先看那五行的「档」列：3000 是「高」、2975 是「中」、800 是「低」。 这一列不是表里的数据，是每次查询现算出来的—— 这正是函数最有价值的地方：它让 SQL 有了「自定义表达式」。 函数被调用了 5 次 这个数，是这一篇最该记住的一句。 5 行数据，5 次调用。 如果表里有一百万行，就是一百万次调用——函数体里每一句多余的操作，都会被乘上一百万。 聚合那段 my_avg 给出的是 2916.67、2175.0、1566.67。 三个部门分别是 3 行、5 行、6 行数据， step 被调了 3 次、5 次、6 次，finalize 各调 1 次。 「逐行累加、最后交一次」——这就是 avg 的全部秘密。 注意 2916.67 是 8750 / 3 保留两位的结果（部门 10 的三个人是 2450、5000、1300）。 这里我特意用了 round(..., 2)， 因为 Oracle 的 avg 返回的是高精度数，不 round 会打出一长串小数。 Oracle 里写聚合函数要用 CREATE TYPE 加 ODCIAggregate 接口， 要自己实现 ODCIAggregateInitialize、ODCIAggregateIterate、 ODCIAggregateTerminate 四个方法。 沙箱里那 4 个方法被压成了 __init__、step、finalize 三个， 机制一模一样：初始化 → 逐行喂 → 收尾出值。

30 秒实验

1. 把 calls = [0] 改成在 get_grade 里 print 一行，看它到底调了几次、按什么顺序调。 你会看到它是「一行一次、按查询结果顺序」——这就是行级调用的直接证据。
2. 把 where deptno = 20 去掉，重跑。调用次数会从 5 变成 14（全表 14 行）。 去掉 where 不会让函数「变聪明」，只会让它多跑 9 次。
3. 把 MyAvg.finalize 里的 round(..., 2) 删掉，看 2916.67 变成什么。 会变成 2916.6666666666665 这种一串—— 真实 Oracle 里 avg 也是高精度返回，展示时自己控制。

常见误区

1. 以为函数和过程「只是返回值有无的区别」。 不止。 函数能进 SQL，过程不能——这一条带来的用法差别远大于「返回值」本身。
2. 在函数里做 update，然后放进 select 里用。 直接报 ORA-14551。 查询是「读」的场合，Oracle 不许它偷偷写。
3. 函数体只在一处写了 RETURN。 编译能过，跑到别的分支报 ORA-06503。 每条出口都要有 RETURN。
4. 看到「对象无效」就重建。 先 ALTER ... COMPILE，大多数 INVALID 编译一下就恢复了， 不用重建。

下一篇讲触发器：没人调用它，insert／update／delete 一发生它自己就跑。 这是数据库里「看不见的代码」，也是排查怪事的第一嫌疑对象。

• ☐ （本篇完，下一篇 6.3 触发器）

相关笔记

• 触发器
• 存储过程
• 检索数据
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引