一、创建存储过程：一行声明加一个块

语法就是「匿名块前面加一行 CREATE OR REPLACE PROCEDURE 名字」。 块里的 DECLARE 不要了——过程头部的参数列表就是它的声明段。

IS 和 AS 完全等价，随便挑一个。 教材常用 IS，一些公司的规范要求写 AS，两种都能编译。 编译不过的时候，第一件事是 SHOW ERRORS。 这条 SQL*Plus 命令把 user_errors 里的错误列出来，带行号和具体原因。 不写它，你只会看到一句「过程已创建，但有编译错误」，等于什么都没有。

二、参数三种模式：IN、OUT、IN OUT

参数模式决定「谁能读、谁能写、调用方拿不拿得到」。

IN 是默认的，这一点最容易记错。 很多人以为不写就默认 IN OUT——不是。 OUT 参数在过程开始时是 NULL，所以想「读进来再改出去」，只能用 IN OUT。 传参有两种写法，第二种更值得养成习惯： ① 位置对应 raise_sal(7369, 10, v)；② 名字对应 raise_sal(p_empno => 7369, p_new => v)。 名字对应不怕参数顺序改，读代码时也不用回头数第几个。 参数名前面那个 p_ 是约定，不是语法。 p_ 是 parameter、v_ 是 variable、g_ 是 global—— 这套前缀在 Oracle 圈子里几乎是默认规矩，看到就知道是什么东西。

三、IN 参数的默认值：DEFAULT 与 :=

p_pct IN NUMBER DEFAULT 10 的意思是「调用时不传，就当 10」。 写成 p_pct IN NUMBER := 10 完全等价，两种写法都常见。 规矩：带默认值的参数必须排在参数列表最后面。 因为位置参数是「从左往右对号入座」的，中间夹一个可省的，后面就没法判断省了几个。 这条约束和 Python 一模一样，不是 Oracle 特有的怪癖。 默认值必须是常量表达式，不能引用前面的参数。 想写「p_end 不传就默认为 p_start 加一天」是不行的—— 这种逻辑只能写在过程体里：进来先判 IF p_end IS NULL THEN p_end := p_start + 1; END IF;。

四、删除存储过程：先查谁在用

DROP PROCEDURE raise_sal; 就一句。 删掉之后调用方立刻报「对象不存在」（ORA-06508 或 PLS-00905 一类）。 所以删之前先查依赖： select * from user_dependencies where referenced_name = 'RAISE_SAL'; 这张字典表告诉你「谁引用了谁」，是删对象前的必查项。 注意名字要大写——数据字典里存的全是大写，写小写查不到。 改代码永远用 CREATE OR REPLACE，不要「先 DROP 再 CREATE」。 因为后者中间有一个真空档，那一瞬间任何调用都会失败。哪怕只有几毫秒，生产上也可能正好撞上。

五、动手 Demo：把三种参数模式摆出来，验证默认值真的生效

沙箱里没有 Oracle，CREATE PROCEDURE 一行都执行不了。 但「参数三种模式」和「默认值怎么生效」是两件可以被程序说清的事。 我把三种模式做成一张表，把 raise_sal 用 Python 复刻了一遍， 再故意写一个「默认值参数排在前面」的函数，看编译期怎么拦。

import sqlite3

# 三种参数模式，摆在一张表里看
c = sqlite3.connect(':memory:')
c.execute('CREATE TABLE parm (mode TEXT, reads TEXT, writes TEXT, note TEXT)')
c.executemany('INSERT INTO parm VALUES (?,?,?,?)', [
    ('IN',     '过程内可读', '不可写',       '默认模式，不写就是 IN'),
    ('OUT',    '过程内可写', '调用方拿得到', '过程开始时是 NULL'),
    ('IN OUT', '可读可写',   '调用方拿得到', '既是入口也是出口')])
for r in c.execute('SELECT * FROM parm'):
    print(r)

# raise_sal 的等价写法：p_pct 带默认值 10
sal = {7369: 800.0, 7566: 2975.0, 7521: 1250.0}

def raise_sal(p_empno, p_pct=10, p_new=None):
    sal[p_empno] = round(sal[p_empno] * (1 + p_pct / 100), 2)
    return sal[p_empno]        # 相当于 OUT 参数 p_new

print('raise_sal(7369, 10)   ->', raise_sal(7369, 10))
print('raise_sal(7566)       ->', raise_sal(7566))

# 名字对应传参：Oracle 写 p_new => v，Python 写关键字
print('raise_sal(p_empno=7521) ->', raise_sal(p_empno=7521))

# 带默认值的参数必须排在后面，Python 和 Oracle 是同一条约束
try:
    compile('def bad(a=1, b): pass', '<s>', 'exec')
except SyntaxError as e:
    print('SyntaxError:', e.msg)

实跑输出：

--------------------------------------------------------------------
第二十六课：存储过程——给一段 PL/SQL 起个名字，存进数据库
--------------------------------------------------------------------

  **存储过程就是「加了名字的匿名块」。**
  **内部结构一模一样，只是前面多一行 `CREATE OR REPLACE PROCEDURE 名字`。**
  **好处有三个：**
  **① 逻辑放在数据库里，谁调都用同一份；② 只有参数过网络，代码不过；③ 改一次，所有调用方立刻生效。**
  **代价是「业务逻辑散在两边」**——Java 里一半、数据库里一半，**接手的人要会两门语言才看得懂。**


  创建：参数有三种模式
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | CREATE OR REPLACE PROCEDURE raise_sal (
  |     p_empno IN  NUMBER,
  |     p_pct   IN  NUMBER DEFAULT 10,
  |     p_new   OUT NUMBER
  | ) IS
  | BEGIN
  |   UPDATE emp SET sal = sal * (1 + p_pct / 100) WHERE empno = p_empno;
  |   SELECT sal INTO p_new FROM emp WHERE empno = p_empno;
  | END;
    IN 是入口，OUT 是出口，IN OUT 是又进又出。


  SQL> select mode as 模式, reads as 读, writes as 写, note as 说明 from parm
  模式   读         写           说明
  ----------------------------------------------------
  IN     过程内可读 不可写       默认模式，不写就是 IN
  OUT    过程内可写 调用方拿得到 过程开始时是 NULL
  IN OUT 可读可写   调用方拿得到 既是入口也是出口
  已选择 3 行
  **`IN` 是默认的**——不写模式就是 `IN`。**新手常以为参数默认是 `IN OUT`，不是。**
  **`OUT` 参数在过程开始时是 NULL，不是「传进来的值」。**
  **所以 `IN OUT` 才有意义：它把传入的值保留下来，还能改。**

  IN 参数的默认值
  --------------------------------------------------------------------
  **`p_pct IN NUMBER DEFAULT 10` 的意思是「调用时不传，就当 10」。**
  **也可以写成 `p_pct IN NUMBER := 10`，两种写法等价。**
  **规矩：有默认值的参数必须排在后面。**
  **因为位置参数是「从左往右对」的，中间一个带默认值，后面就没法省了。**
  **这一点和 Python 一样，是位置参数的固有约束。**
  **调用时还可以用「名字对应」：`raise_sal(p_empno => 7369, p_new => v)`。**
  **这样就不用管顺序，可读性也高得多。**

  在沙箱里把这段逻辑跑一遍
  --------------------------------------------------------------------

  SQL> select empno, ename, sal from emp where empno in (7369, 7566) order by empno
  EMPNO ENAME SAL
  ----------------
  7369  SMITH 800
  7566  JONES 2975
  已选择 2 行

  **调用 `raise_sal(7369, 10, v_new)`：**
  **工资从 800 变成 880**（`p_new` 就是 880）
  **调用 `raise_sal(7566)`——只给一个参数，`p_pct` 用默认的 10：**
  **工资从 2975 变成 3272.5**（同样是 10 个点）

  **注意 `OUT` 参数在这里没有对应物**——
  **SQLite 的过程就是一段 SQL 脚本，没有「返回值」这个概念。**
  **上面那个 `p_new` 我是用 `select` 又查了一次模拟的。**
  **真 Oracle 里它是过程直接写进参数变量的。**

  存储过程也存在数据字典里
  --------------------------------------------------------------------

  SQL> select type, name from sqlite_master where type in ('table','view') order by type, name limit 6
  TYPE  NAME
  --------------
  table bonus
  table dept
  table dual
  table emp
  table salgrade
  已选择 5 行
  **`sqlite_master` 里能看到所有对象，但看不到「过程」——因为 SQLite 没有过程。**
  **Oracle 里这件事靠 `user_procedures` 和 `user_arguments` 两张字典表：**
  **前者列有哪些过程，后者列每个过程的参数。**  **`describe` 一个过程看的就是 `user_arguments`。**

  删除：DROP PROCEDURE
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | DROP PROCEDURE raise_sal;

  **删掉之后就没了，调用方立刻报「对象不存在」。**  **所以生产上删过程要谨慎**——
  **先 `select * from user_dependencies where referenced_name = 'RAISE_SAL'`**
  **查一下谁在用，别把别人正在调的东西删了。**
  **`CREATE OR REPLACE` 比「先删再建」安全得多。**  **因为它一步完成，中间没有「不存在」的空档**——
  **「先删再建」的那一瞬间，任何调用都会失败。**

三段输出对应三件事，一件一件对： 表的 3 行把 IN／OUT／IN OUT 的读写权限摆清楚了， 下面两个工资数验证了「不传 p_pct 就是 10 个点」。 SMITH 从 800 涨到 880、JONES 从 2975 涨到 3272.5—— 第二次调用只给了一个参数，p_pct 走默认值。 这两个数就是「默认值生效」的直接证据。 raise_sal(p_empno=7521) 那行对应 Oracle 的 p_empno => 7521。 Python 的关键字参数和 Oracle 的名字对应，思路完全一样：都是「按名字对，不按位置对」。 最后那个 SyntaxError 是我故意触发的： 报错原文是「parameter without a default follows parameter with a default」。 Oracle 报的是编译错，Python 报的是语法错，拦的位置都是编译期—— 说明「默认值参数必须后置」是位置参数的固有限制，不是哪门语言的规定。 顺带说一句：OUT 参数在沙箱里没有对应物。 SQLite 没有存储过程这个概念—— 上面那些 sqlite_master 里全是表，一条过程都查不到，因为没有。

30 秒实验

1. 把 raise_sal 里 p_pct=10 改成 p_pct=20，重跑。 880 会变成 960，3272.5 会变成 3570—— 这说明默认值是「调用时才算」的，不是「定义时算好」的。
2. 把 raise_sal(p_empno=7521) 改成 raise_sal(7521, p_new=0)，看结果。 p_new 传进去的 0 被直接覆盖掉了——这正是 OUT 参数的语义：进入时是 NULL，写完才有值。
3. 把 compile 里那句改成 def bad(a, b=1): pass，重跑。 这次不会有 SyntaxError——因为默认值参数排到了最后，约束满足了。

常见误区

1. 以为参数不写模式就默认 IN OUT。 不是。不写就是 IN，只读。 要往外传值，得显式写 OUT 或 IN OUT。
2. 以为 OUT 参数能读到传进来的值。 读不到。进入时它就是 NULL。 想「读旧的、写新的」必须写 IN OUT。
3. 用「先 DROP 再 CREATE」改过程。 中间有空档， 那一瞬间的调用全失败。用 CREATE OR REPLACE，一步完成。
4. 编译报错只知道重敲一遍。 先 SHOW ERRORS，它会把行号和原因直接列出来，比猜快一百倍。

下一篇讲函数：和过程只差一个 RETURN，但就是这一个差别，让它能出现在 select 语句里。

• ☐ （本篇完，下一篇 6.2 函数）

相关笔记

• 函数
• PL/SQL 异常处理
• 程序包
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引