oracle存储过程实例详解(oracle存储过程语法详解)

Oracle数据库中的存储过程是一种用于封装特定业务逻辑的可重用数据库对象。

存储过程可以有输入参数、输出参数，甚至可以返回结果集。

以下是一个Oracle存储过程的实例和详解：

假设我们有一个名为employees的表，表结构如下：

CREATE TABLE employees (
  id NUMBER(10) PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER(10, 2),
  department_id NUMBER(10)
);

现在我们要创建一个存储过程，用于根据部门ID查询员工的总薪资。

步骤1：创建存储过程

CREATE OR REPLACE PROCEDURE get_total_salary_by_dept (
  p_department_id IN employees.department_id%TYPE,
  p_total_salary OUT NUMBER
) AS
BEGIN
  SELECT SUM(salary)
  INTO p_total_salary
  FROM employees
  WHERE department_id = p_department_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_total_salary := 0;
  WHEN OTHERS THEN
    RAISE;
END get_total_salary_by_dept;

详解：

• CREATE OR REPLACE PROCEDURE用于创建或替换一个存储过程，后面跟存储过程的名称。
• p_department_id IN employees.department_id%TYPE定义了一个输入参数，类型与employees.department_id相同。
• p_total_salary OUT NUMBER定义了一个输出参数，类型为NUMBER。
• AS或IS关键字表示存储过程的声明部分结束，开始编写存储过程的主体。
• BEGIN和END之间是存储过程的主体，包含具体的业务逻辑。
• 使用SELECT INTO将查询结果赋值给输出参数p_total_salary。
• EXCEPTION部分用于处理异常。这里处理了NO_DATA_FOUND（未找到数据）和OTHERS（其他异常）两种情况。

步骤2：调用存储过程

为了调用此存储过程，你可以使用匿名PL/SQL块或其他PL/SQL程序单元。以下是一个调用示例：

DECLARE
  v_department_id NUMBER := 10;
  v_total_salary NUMBER;
BEGIN
  get_total_salary_by_dept(v_department_id, v_total_salary);
  DBMS_OUTPUT.PUT_LINE('Total salary for department ' || v_department_id || ' is: ' || v_total_salary);
END;

详解：

• DECLARE部分声明了两个局部变量：v_department_id和v_total_salary。
• BEGIN和END之间是匿名PL/SQL块的主体。
• 调用get_total_salary_by_dept存储过程，并传入参数v_department_id和v_total_salary。
• 使用DBMS_OUTPUT.PUT_LINE输出结果。

这个示例展示了如何在Oracle数据库中创建和调用一个简单的存储过程。