Oracle数据库的存储过程写法(oracle编写存储过程)

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

它们通常用于执行批量操作、数据验证或复杂的计算任务。

以下是Oracle存储过程的基本写法

步骤1：创建存储过程

CREATE OR REPLACE PROCEDURE procedure_name (
  parameter1 parameter1_type parameter1_mode,
  parameter2 parameter2_type parameter2_mode,
  ...
) AS
  -- 变量声明
  variable1 variable1_type;
  variable2 variable2_type;
  ...
BEGIN
  -- 存储过程主体（逻辑代码）
  ...
EXCEPTION
  -- 异常处理
  WHEN exception1 THEN
    ...
  WHEN exception2 THEN
    ...
  WHEN OTHERS THEN
    ...
END procedure_name;

• CREATE OR REPLACE：创建或替换现有存储过程。
• PROCEDURE procedure_name：指定存储过程的名称。
• parameter1_type parameter1_mode：定义存储过程的参数，包括类型和模式（IN、OUT、IN OUT）。
• AS或IS：用于分隔存储过程的声明部分和主体部分。
• 变量声明：在BEGIN之前声明存储过程中使用的局部变量。
• BEGIN：开始存储过程的主体。
• 存储过程主体：包含具体的业务逻辑。
• EXCEPTION：用于处理存储过程中可能出现的异常。
• END procedure_name;：结束存储过程的定义。

步骤2：调用存储过程

存储过程可以在匿名PL/SQL块、另一个存储过程或函数中调用。以下是一个在匿名PL/SQL块中调用存储过程的示例：

DECLARE
  -- 变量声明
  ...
BEGIN
  -- 调用存储过程
  procedure_name(parameter1_value, parameter2_value, ...);
  ...
END;

下面是一个简单的实例，创建一个名为print_full_name的存储过程，接收两个输入参数（姓和名），并输出完整的姓名：

CREATE OR REPLACE PROCEDURE print_full_name (
  p_first_name IN VARCHAR2,
  p_last_name IN VARCHAR2
) AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Full name: ' || p_first_name || ' ' || p_last_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred.');
END print_full_name;

调用此存储过程的示例：

DECLARE
BEGIN
  print_full_name('John', 'Doe');
END;

执行此匿名PL/SQL块将输出：

Full name: John Doe

在前面的示例中，我们创建了一个简单的存储过程print_full_name并调用了它。

现在我们将继续探讨一个更复杂的存储过程示例，该过程包含输入参数、输出参数以及异常处理。

假设我们有一个名为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_salary_stats_by_dept (
  p_department_id IN employees.department_id%TYPE,
  p_total_salary OUT NUMBER,
  p_avg_salary OUT NUMBER
) AS
BEGIN
  SELECT SUM(salary), AVG(salary)
  INTO p_total_salary, p_avg_salary
  FROM employees
  WHERE department_id = p_department_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_total_salary := 0;
    p_avg_salary := 0;
  WHEN OTHERS THEN
    RAISE;
END get_salary_stats_by_dept;

步骤2：调用存储过程

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

DECLARE
  v_department_id NUMBER := 10;
  v_total_salary NUMBER;
  v_avg_salary NUMBER;
BEGIN
  get_salary_stats_by_dept(v_department_id, v_total_salary, v_avg_salary);
  DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_department_id);
  DBMS_OUTPUT.PUT_LINE('Total salary: ' || v_total_salary);
  DBMS_OUTPUT.PUT_LINE('Average salary: ' || v_avg_salary);
END;

此示例展示了如何在Oracle数据库中创建和调用具有输入参数、输出参数和异常处理的存储过程。