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

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

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

图片[1]-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 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 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)。
  • ASIS:用于分隔存储过程的声明部分和主体部分。
  • 变量声明:在BEGIN之前声明存储过程中使用的局部变量。
  • BEGIN:开始存储过程的主体。
  • 存储过程主体:包含具体的业务逻辑。
  • EXCEPTION:用于处理存储过程中可能出现的异常。
  • END procedure_name;:结束存储过程的定义。

步骤2:调用存储过程

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

DECLARE
-- 变量声明
...
BEGIN
-- 调用存储过程
procedure_name(parameter1_value, parameter2_value, ...);
...
END;
DECLARE
  -- 变量声明
  ...
BEGIN
  -- 调用存储过程
  procedure_name(parameter1_value, parameter2_value, ...);
  ...
END;
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;
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;
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;
DECLARE
BEGIN
  print_full_name('John', 'Doe');
END;
DECLARE BEGIN print_full_name('John', 'Doe'); END;

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

Full name: John Doe
Full name: John Doe
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)
);
CREATE TABLE employees (
  id NUMBER(10) PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER(10, 2),
  department_id NUMBER(10)
);
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;
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;
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;
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;
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数据库中创建和调用具有输入参数、输出参数和异常处理的存储过程。

© 版权声明
THE END