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

429

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）。
  • AS或IS：用于分隔存储过程的声明部分和主体部分。
  • 变量声明：在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
数据库
# 数据库# 存储过程# 写法# 创建存储
相关推荐
文章目录
paypal官网登录首页入口（网页版入口网址链接）-不念博客

1paypal官网登录首页入口（网页版入口网址链接）

不念的头像-不念博客23小时前
2752
拳头官网登录入口（riot games网页版登录教程）-不念博客

2拳头官网登录入口（riot games网页版登录教程）

不念的头像-不念博客23小时前
1W+
oppo官网登录入口（官网首页地址链接）-不念博客

3oppo官网登录入口（官网首页地址链接）

不念的头像-不念博客23小时前
703
汽车之家官网入口（官方网页版网址）-不念博客

4汽车之家官网入口（官方网页版网址）

不念的头像-不念博客23小时前
1382
Netflix官网入口网址（奈飞/网飞网页版入口）-不念博客

5Netflix官网入口网址（奈飞/网飞网页版入口）

不念的头像-不念博客23小时前
388
如何使用VLC录制屏幕-不念博客

1如何使用VLC录制屏幕

不念的头像-不念博客2年前
461
WordPress美化标签-不念博客

2WordPress美化标签

不念的头像-不念博客4年前
431
什么是Combo端口？-不念博客

3什么是Combo端口？

不念的头像-不念博客2年前
1241
密码学入门：深入理解非对称加密算法-不念博客

4密码学入门：深入理解非对称加密算法

不念的头像-不念博客2年前
320
电脑怎么下载微信到桌面？（详细下载安装步骤）-不念博客

5电脑怎么下载微信到桌面？（详细下载安装步骤）

不念的头像-不念博客11个月前
411
文章目录
标签云
鼠标默认值默认黑树黑帽麻辣烫麒麟鸟哥魔力魔兽高防高速高级ACL高精度高端高斯高性能高带宽高密度高密高可靠性高可用性骨干验证码验证驱逐驱动程序驱动器驱动马赛克马斯克香港站香港首部饿汉饥荒餐具飞行飞秋风河风格颜色代码题目题库颗粒频谱频率