当前位置：首页 > 问答 > 正文

Oracle存储过程怎么写代码，带点实际操作和用法讲解

瞿欣合
问答
2026-01-09 05:13:34
1

要理解Oracle存储过程怎么写,我们可以把它想象成在数据库内部创建一个可以重复使用的“工具”或者“小程序”，你不需要每次都在应用程序里写一大堆复杂的SQL语句，只需要调用这个存储过程的名字，它就会帮你把一系列操作都完成，这在处理复杂业务逻辑或者需要保证数据操作一致性的场景下特别有用。

第一部分：一个最简单的入门例子

我们先来看一个最基础的存储过程,它什么都不做，就打个招呼，这能让你看清它的基本结构。

（参考Oracle官方文档中关于CREATE PROCEDURE的基本语法）

CREATE OR REPLACE PROCEDURE say_hello
IS
BEGIN
   -- 这是在屏幕上输出一行文字，主要用于调试
   DBMS_OUTPUT.PUT_LINE('你好，世界！我是一个存储过程。');
END say_hello;
/

我们来拆解一下这个“小程序”：

CREATE OR REPLACE PROCEDURE say_hello：这句话是“命令”，意思是“创建或者替换一个名叫say_hello的存储过程”。OR REPLACE非常方便，如果已经存在同名的，就覆盖掉，不用先删除。
IS：这是一个关键字，表示声明部分开始了，虽然这里我们没有任何声明。
BEGIN：这是“开始”的标记，表示可执行代码从这里开始。
DBMS_OUTPUT.PUT_LINE(...)：这相当于程序里的print语句，能把结果输出到屏幕，要看到它的输出，你需要先在SQLPlus或者SQL Developer等工具里执行SET SERVEROUTPUT ON。
END say_hello;：表示存储过程体结束。
：单独一行的斜杠是告诉SQL工具“可以执行我前面的代码了”。

怎么使用它呢？ 创建成功后，你只需要调用它的名字：

BEGIN
   say_hello; -- 调用存储过程
END;
/

执行后,如果一切正常，你就能看到输出结果：“你好，世界！我是一个存储过程。”

第二部分：带点实际操作——一个有输入参数的例子

光打招呼没用,存储过程得能干活，现在我们做一个实用的：根据员工编号，给这个员工涨工资。

（参考常见的业务逻辑处理示例）

CREATE OR REPLACE PROCEDURE raise_salary (
   p_emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE, -- 参数1：员工ID，类型和EMPLOYEES表的EMPLOYEE_ID字段一样
   p_amount IN NUMBER -- 参数2：涨薪的金额
)
IS
BEGIN
   -- 更新员工表，将指定ID的员工的薪水增加指定的金额
   UPDATE employees
   SET salary = salary + p_amount
   WHERE employee_id = p_emp_id;
   -- 判断到底有没有更新成功（即这个员工存不存在）
   IF SQL%ROWCOUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('错误：找不到员工ID为 ' || p_emp_id || ' 的员工。');
   ELSE
      DBMS_OUTPUT.PUT_LINE('员工 ' || p_emp_id || ' 的薪水已成功增加 ' || p_amount || '。');
   END IF;
   COMMIT; -- 提交事务，让更新生效
EXCEPTION
   -- 异常处理部分，如果上面更新出错了，比如金额是负数导致薪水变负，会跳到这里
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生错误：' || SQLERRM);
      ROLLBACK; -- 回滚事务，撤销刚才的更新操作
END raise_salary;
/

这个例子就丰满多了：

参数：我们定义了两个输入参数（IN），p_emp_id和p_amount。p_是个人习惯，表示它是参数（Parameter），方便区分。EMPLOYEES.EMPLOYEE_ID%TYPE是一种好习惯，表示这个参数的数据类型和表里的那个字段完全一致，以后表结构变了，这个类型也会自动变，很省心。
核心逻辑：就是一句UPDATE语句，使用了传入的参数。
流程控制：SQL%ROWCOUNT是Oracle的一个魔法变量，它记录了上一条SQL语句影响的行数，我们用它来判断是否真的更新了一个员工，如果没有（=0），就说明员工ID可能输错了。
事务控制：COMMIT是提交，让更新永久保存，在存储过程里显式地提交或回滚是一个好习惯。
异常处理：EXCEPTION部分是用来抓错误的。WHEN OTHERS THEN会捕获所有未预期的错误。SQLERRM包含了错误的详细信息，一旦出错，我们先打印错误，然后ROLLBACK回滚，确保数据不会处于一个半更新的混乱状态。

调用这个存储过程：

BEGIN
   raise_salary(105, 1000); -- 给ID为105的员工涨1000块钱工资
END;
/

第三部分：再进一步——有输入也有输出的例子

我们不仅想往里传参数,还想让存储过程给我们返回点结果，我们写一个存储过程，根据部门编号，返回这个部门的员工数量和平均工资。

（参考存储过程IN OUT参数的使用）

CREATE OR REPLACE PROCEDURE get_dept_stats (
   p_dept_id IN EMPLOYEES.DEPARTMENT_ID%TYPE, -- 输入：部门ID
   p_emp_count OUT NUMBER, -- 输出：员工数量
   p_avg_salary OUT NUMBER -- 输出：平均工资
)
IS
BEGIN
   -- 通过一条查询，将结果直接赋值给两个输出参数
   SELECT COUNT(*), AVG(salary)
   INTO p_emp_count, p_avg_salary
   FROM employees
   WHERE department_id = p_dept_id;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- 如果该部门没有员工，SELECT...INTO会抛出NO_DATA_FOUND异常
      p_emp_count := 0;
      p_avg_salary := 0;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('错误：' || SQLERRM);
END get_dept_stats;
/

这里的新知识点是OUT参数和SELECT ... INTO ...语法。

OUT：表示这个参数是用于输出结果的。
SELECT ... INTO ...：这是PL/SQL中把查询结果赋值给变量的标准方式，这里我们把查到的统计值直接赋给了两个输出参数。

调用这个存储过程有点不同，因为我们要接收返回值：

DECLARE
   v_count NUMBER;
   v_avg_sal NUMBER;
BEGIN
   get_dept_stats(60, v_count, v_avg_sal); -- 查询60部门的统计信息
   DBMS_OUTPUT.PUT_LINE('部门60有 ' || v_count || ' 名员工，平均工资为：' || v_avg_sal);
END;
/

我们在一个匿名块（DECLARE-BEGIN-END）里调用它，先声明两个变量v_count和v_avg_sal，然后把他们传给存储过程，存储过程执行后，结果会“填充”到这两个变量里，我们再把它打印出来。

写Oracle存储过程，核心就是理解它的结构：定义参数、写执行逻辑、处理异常，从最简单的开始，逐步增加参数、控制流和错误处理，你就能创建出功能强大、易于维护的数据库程序，它把业务逻辑封装在数据库层面，提高了执行效率和代码的复用性。

Oracle存储过程怎么写代码，带点实际操作和用法讲解