【DB】Oracle存储过程

devtools/2025/1/31 4:37:40/

目录

什么是存储过程？

为什么要使用存储过程？

创建存储过程

无参存储过程语法：

带参存储过程语法：

带有输入参数的存储过程

带有输出参数的存储过程

带有输入输出参数的存储过程

带有异常处理的存储过程

存储过程中游标定义使用

基本语法

示例

简单的游标使用

带有参数的游标

隐式游标属性

示例

检查UPDATE语句影响的行数

使用FOR循环遍历用户表中的所有记录

使用BEGIN和DECLARE关键字调用存储过程的区别

使用BEGIN

使用DECLARE

总结

什么是存储过程？

存储过程（Store Procedure）是一种在数据库中预先编译并存储的SQL代码合集，可以包含SQL语句和控制结构（如条件语句、循环等）,用于完成一个或一系列数据库操作（比如对查询订单然后对订单进行修改）。存储过程可以接受输入参数、返回输出参数，并且可以返回结果集。它们通常用于执行复杂的数据操作和业务逻辑。

为什么要使用存储过程？

预编译：存储过程在首次执行时会被编译并存储在数据库中，后续调用时不需要重新编译，执行速度更快。

减少连接：当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句需要等待执行结果进行后续操作，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

代码重用：存储过程可以将复杂的业务逻辑封装在一个模块中，便于重用和维护。

事务管理：存储过程可以包含多个SQL语句，并且可以作为一个事务执行，确保操作的原子性。在存储过程中可以使用事务控制语句（如COMMIT和ROLLBACK）来管理事务，确保数据的一致性。

创建存储过程

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

无参存储过程语法： 

CREATE OR REPLACE PROCEDURE NoParPro  -- NoParPro存储过程名称
AS  -- 声明部分
BEGIN  -- 执行部分-- 存储过程的主体代码
EXCEPTION  -- 存储过程异常处理部分-- 异常处理代码
END;

解释

  1. CREATE OR REPLACE PROCEDURE NoParPro：

    • CREATE OR REPLACE PROCEDURE：这是创建或替换存储过程的关键字。如果存储过程已经存在，则会替换它；如果不存在，则会创建一个新的存储过程。

    • NoParPro：这是存储过程的名称。

  2. AS：

    • 这是声明部分的开始。在这里可以声明变量、游标等。

  3. BEGIN：

    • 这是存储过程的执行部分的开始。在这里编写存储过程的主体代码，包括SQL语句和PL/SQL控制结构（如条件语句、循环等）。

  4. EXCEPTION：

    • 这是存储过程的异常处理部分的开始。在这里可以编写异常处理代码，用于捕获和处理在执行部分中可能发生的异常。

  5. END：

    • 这是存储过程的结束标志。

举例：

CREATE OR REPLACE PROCEDURE NoParPro
ASv_count NUMBER;  -- 声明一个数字变量
BEGIN-- 执行部分SELECT COUNT(*) INTO v_countFROM Users;/* dbms_output.put_line(); 相当相当于JAVA中的System.out.println,注意记住一句话的结束使用分号结束,存储过程写完一定要执行将它保存到数据库中 (F8)快捷键,或者点击左上角执行*/DBMS_OUTPUT.PUT_LINE('用户总人数为: ' || v_count);
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('执行出现异常。'|| SQLERRM);
END;
​

带参存储过程语法：

带有输入参数的存储过程

创建一个存储过程，用于插入一条记录到用户表中：

CREATE OR REPLACE PROCEDURE InsertUser (p_UserName IN VARCHAR2,p_Email IN VARCHAR2
) AS
BEGININSERT INTO Users (UserName, Email)VALUES (p_UserName, p_Email);  
END;调用存储过程：BEGINInsertUser('JohnDoe', 'john.doe@example.com');
END;
带有输出参数的存储过程

创建一个存储过程，用于查询用户的邮箱，并将结果返回给调用者：

CREATE OR REPLACE PROCEDURE GetUserEmail (p_UserName IN VARCHAR2, // 输入参数p_Email OUT VARCHAR2   // 输出参数
) AS
BEGINSELECT Email INTO p_EmailFROM UsersWHERE UserName = p_UserName;
END;

调用存储过程：

DECLAREv_Email VARCHAR2(100);
BEGINGetUserEmail('JohnDoe', v_Email);DBMS_OUTPUT.PUT_LINE('Email: ' || v_Email);
END;
带有输入输出参数的存储过程

创建一个存储过程，用于更新用户的邮箱，并返回更新后的邮箱：

CREATE OR REPLACE PROCEDURE UpdateUserEmail (p_UserName IN VARCHAR2,p_Email IN OUT VARCHAR2
) AS
BEGINUPDATE UsersSET Email = p_EmailWHERE UserName = p_UserName;
​SELECT Email INTO p_EmailFROM UsersWHERE UserName = p_UserName;
END;

调用存储过程：

​DECLAREv_Email VARCHAR2(100);
BEGINv_Email := 'new.email@example.com';UpdateUserEmail('JohnDoe', v_Email);DBMS_OUTPUT.PUT_LINE('Updated Email: ' || v_Email);
END;
带有异常处理的存储过程

创建一个存储过程，用于删除用户，并处理可能的异常：

CREATE OR REPLACE PROCEDURE DeleteUser (p_UserName IN VARCHAR2
) AS
BEGINDELETE FROM UsersWHERE UserName = p_UserName;
EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('User not found.');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGINDeleteUser('JohnDoe');
END;

存储过程中游标定义使用

在Oracle PL/SQL中，游标（Cursor）是用于逐行处理查询结果集的机制。游标允许你遍历查询结果集中的每一行，并对每一行执行特定的操作。以下是游标的基本语法和示例。

基本语法

声明游标：

CURSOR cursor_name ISSELECT column1, column2, ...FROM table_nameWHERE condition;

打开游标：

OPEN cursor_name;

获取游标数据：

FETCH cursor_name INTO variable1, variable2, ...;

关闭游标：

CLOSE cursor_name;
示例
简单的游标使用

创建一个存储过程，用于遍历用户表中的所有记录，并输出每个用户的用户名和邮箱：

CREATE OR REPLACE PROCEDURE ListUsers
AS-- 声明游标CURSOR user_cursor ISSELECT UserName, EmailFROM Users;
​-- 声明变量v_UserName/v_Email，其数据类型与表Users中的列UserName/v_Email相同v_UserName Users.UserName%TYPE;v_Email Users.Email%TYPE;
BEGIN-- 打开游标OPEN user_cursor;
​-- 循环遍历游标LOOPFETCH user_cursor INTO v_UserName, v_Email;EXIT WHEN user_cursor%NOTFOUND;
​-- 输出用户信息DBMS_OUTPUT.PUT_LINE('UserName: ' || v_UserName || ', Email: ' || v_Email);END LOOP;
​-- 关闭游标CLOSE user_cursor;
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGINListUsers;
END;
带有参数的游标

创建一个存储过程，用于根据用户名查询用户信息，并输出用户的邮箱：

CREATE OR REPLACE PROCEDURE GetUserInfo (p_UserName IN VARCHAR2
) AS-- 声明游标CURSOR user_cursor ISSELECT EmailFROM UsersWHERE UserName = p_UserName;
​-- 声明变量v_Email Users.Email%TYPE;
BEGIN-- 打开游标OPEN user_cursor;
​-- 获取游标数据FETCH user_cursor INTO v_Email;
​-- 检查是否找到记录IF user_cursor%FOUND THENDBMS_OUTPUT.PUT_LINE('Email: ' || v_Email);ELSEDBMS_OUTPUT.PUT_LINE('User not found.');END IF;
​-- 关闭游标CLOSE user_cursor;
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGINGetUserInfo('JohnDoe');
END;
隐式游标属性

在PL/SQL中，SQL%ROWCOUNT是一个隐式游标属性，用于返回最近执行的SQL语句（如INSERT、UPDATE、DELETE）影响的行数。这个属性可以用于检查SQL语句的执行结果，从而进行相应的处理。

示例

以下是一个示例，展示了如何使用SQL%ROWCOUNT属性来检查UPDATE语句影响的行数，并根据结果执行不同的操作。

检查UPDATE语句影响的行数

创建一个存储过程，用于更新用户的邮箱，并检查更新操作影响的行数：

​
CREATE OR REPLACE PROCEDURE UpdateUserEmail (p_UserName IN VARCHAR2,p_NewEmail IN VARCHAR2
) AS
BEGIN-- 更新用户的邮箱UPDATE UsersSET Email = p_NewEmailWHERE UserName = p_UserName;
​-- 检查更新操作影响的行数IF SQL%ROWCOUNT = 0 THENDBMS_OUTPUT.PUT_LINE('No rows updated. User not found.');ELSEDBMS_OUTPUT.PUT_LINE('Email updated successfully for user: ' || p_UserName);END IF;
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGINUpdateUserEmail('JohnDoe', 'new.email@example.com');
END;

在PL/SQL中，FOR循环可以用于遍历游标返回的结果集。通过使用FOR循环，可以简化游标的打开、获取和关闭操作。以下是一个示例，展示了如何使用FOR循环在存储过程中遍历游标返回的结果集。

使用FOR循环遍历用户表中的所有记录

创建一个存储过程，用于遍历用户表中的所有记录，并输出每个用户的用户名和邮箱：

CREATE OR REPLACE PROCEDURE ListUsers
AS-- 声明游标CURSOR user_cursor ISSELECT UserName, EmailFROM Users;
BEGIN-- 使用FOR循环遍历游标FOR user_record IN user_cursor LOOP-- 输出用户信息DBMS_OUTPUT.PUT_LINE('UserName: ' || user_record.UserName || ', Email: ' || user_record.Email);END LOOP;
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGINListUsers;
END;

使用BEGIN和DECLARE关键字调用存储过程的区别

BEGINUpdateOrInsertUser('JohnDoe', 'john.doe@example.com');
END;
​
DECLAREv_Email VARCHAR2(100);
BEGINGetUserEmail('JohnDoe', v_Email);DBMS_OUTPUT.PUT_LINE('Email: ' || v_Email);
END;

在调用Oracle存储过程时，使用BEGIN和DECLARE关键字的区别主要在于是否需要声明变量或处理异常。以下是详细解释：

使用BEGIN

当你只需要简单地调用存储过程，而不需要声明变量或处理异常时，可以直接使用BEGIN关键字。例如：

​BEGINNoParPro;
END;

在这个例子中，NoParPro是一个不需要参数的存储过程。BEGIN和END关键字用于标记PL/SQL代码块的开始和结束。

使用DECLARE

当你需要在调用存储过程之前声明变量或处理异常时，可以使用DECLARE关键字。DECLARE块用于声明变量、游标等，并且可以包含异常处理部分。例如：

DECLAREv_count NUMBER;  -- 声明一个数字变量
BEGINNoParPro;  -- 调用存储过程-- 其他PL/SQL代码
EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('No data found.');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

在这个例子中：

  1. DECLARE：

    • 用于声明变量、游标等。在这里，我们声明了一个数字变量v_count。

  2. BEGIN：

    • 用于标记PL/SQL代码块的开始。在这里，我们调用了存储过程NoParPro，并且可以包含其他PL/SQL代码。

  3. EXCEPTION：

    • 用于处理在BEGIN块中可能发生的异常。在这里，我们捕获了NO_DATA_FOUND异常和其他异常，并输出相应的错误消息。

总结

  • 使用BEGIN：当你只需要简单地调用存储过程，而不需要声明变量或处理异常时。

  • 使用DECLARE：当你需要在调用存储过程之前声明变量或处理异常时。


http://www.ppmy.cn/devtools/154767.html

相关文章

【实践案例】使用Dify构建文章生成工作流【在线搜索+封面图片生成+内容标题生成】

【实践案例】使用Dify构建文章生成工作流【在线搜索+封面图片生成+内容标题生成】

文章目录 概述开始节点图片封面生成关键词实时搜索主题参考生成文章详情和生成文章标题测试完整工作流运行测试结果 概述 使用Dify构建文章生成工作流，使用工具包括：使用 Tavily 执行的搜索查询，使用Flux生成封面图片，使用Stable…
阅读更多...
JavaScript正则表达式

JavaScript正则表达式

为了方便前端做一些输入验证，我们需要掌握正则表达式。 一、创建正则表达式 使用正则表达式字面量，由包含在斜杠之间的模式组成// 验证手机号 var phoneNumber /^1[3-9]\d{9}$/;调用RegExp对象的构造函数// 校验是够包含abc var re new RegExp("…
阅读更多...
小程序-视图与逻辑

小程序-视图与逻辑

前言 1. 声明式导航 open-type"switchTab"如果没有写这个，因为是tabBar所以写这个，就无法跳转。路径开始也必须为斜线 open-type"navigate"这个可以不写 现在开始实现后退的效果 现在我们就在list页面里面实现后退 2.编程式导航…
阅读更多...
Vue 3 30天精进之旅：Day 08 - 组件通信

Vue 3 30天精进之旅：Day 08 - 组件通信

在Vue 3的开发过程中，组件之间的通信是一个至关重要的概念。理解如何在父子组件、兄弟组件以及通过全局事件总线进行通信，将帮助我们构建更为灵活和可维护的应用。在今天的学习中，我们将探讨以下几个方面： 父子组件之间的通信兄弟…
阅读更多...
【MySQL】初始MySQL、库与表的操作

【MySQL】初始MySQL、库与表的操作

目录 基本使用 使用案例 SQL分类 存储引擎 库的操作 字符集和校验规则 查看系统默认字符集和校验规则 查看数据库支持的字符集 查看数据库支持的字符集校验规则 指定编码常见数据库 校验规则对数据库的影响 操纵数据库 库的备份与恢复 表的操作 创建表 查看表 …
阅读更多...
扣子平台音频功能：让声音也能“智能”起来。扣子免费系列教程（14）

扣子平台音频功能：让声音也能“智能”起来。扣子免费系列教程（14）

在数字化时代，音频内容的重要性不言而喻。无论是在线课程、有声读物，还是各种多媒体应用，音频都是传递信息、增强体验的关键元素。扣子平台的音频功能，为开发者和内容创作者提供了一个强大而灵活的工具，让音频的使用和…
阅读更多...
STM32 中断系统

STM32 中断系统

目录 中断 定义： 中断处理的过程： 1.中断申请： 2.中断响应： 3.中断处理： 4.中断向量 : 5.中断返回： 中断优先级 1.抢占优先级 2.响应优先级 3.自然优先级 中断嵌套 中断执行流程 中断源 NV…
阅读更多...
Python GUI 开发 | Qt Designer — 工具介绍

Python GUI 开发 | Qt Designer — 工具介绍

关注这个框架的其他相关笔记：Python GUI 开发 | PySide6 & PyQt6 学习手册-CSDN博客 Qt Designer 即 Qt 设计师，是一个强大、灵活的可视化 GUI 设计工具，可以帮助用户加快开发 PySide6 程序的速度。 Qt Designer 是专门用来制作 PySide6…
阅读更多...
最新文章

Copyright @ 2022~2023