Mr. Cappuccino的第69杯咖啡——Oracle之存储过程

Oracle之存储过程

    • 准备数据
    • PLSQL编程
      • 概念
      • 程序结构
      • 运行程序
        • 在DataGrip中运行
          • DataGrip设置控制台输出打印内容
        • 在sqlplus中运行
          • sqlplus设置控制台输出打印内容
      • 变量
        • 普通变量
        • 引用型变量
        • 记录型变量
      • 流程控制
        • 条件分支
        • 循环
    • 游标
      • 概念
      • 语法
      • 游标的属性
      • 创建与使用
      • 带参数的游标
    • 存储过程
      • 概念
      • 作用
      • 语法
      • 无参存储过程
        • 创建
        • 调用
          • 在DataGrip中调用
          • 在sqlplus中调用
      • 有输入参数的存储过程
      • 有输出参数的存储过程

准备数据

create table EMP
(
    NO      NUMBER,
    NAME    VARCHAR2(50),
    SALARY  NUMBER,
    ADDRESS VARCHAR2(200)
);

insert into EMP
select 1, '德玛西亚', 1580, '上海市宝山区'
from dual;
insert into EMP
select 2, '诺克萨斯', 2580, '上海市徐汇区'
from dual;
insert into EMP
select 3, '艾欧尼亚', 3580, '上海市虹口区'
from dual;
insert into EMP
select 4, '峡谷之巅', 4580, '上海市闵行区'
from dual;
insert into EMP
select 5, '黑色玫瑰', 5580, '上海市静安区'
from dual;

PLSQL编程

概念

PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

程序结构

PL/SQL可以分为三个部分：声明部分、可执行部分、异常处理部分。
其中DECLARE部分用来声明变量或游标（结果集类型变量），如果程序中无变量声明可以省略掉。

DECLARE
    -- 声明变量、游标。
    I INTEGER;
BEGIN
    -- 执行语句

    -- 异常处理

END;

运行程序

在DataGrip中运行

DBMS_OUTPUT为Oracle内置程序包

BEGIN
    -- 打印hello world
    DBMS_OUTPUT.PUT_LINE('hello world');
END;

在这里插入图片描述

DataGrip设置控制台输出打印内容

双击Shift搜索“dbms”，将开关打开

在这里插入图片描述

在这里插入图片描述

在sqlplus中运行

在sqlplus中执行PLSQL程序需要在程序最后添加一个‘/’ 符号，以标识程序的结束。

sqlplus # 使用账号密码登录
BEGIN
    -- 打印hello world
    DBMS_OUTPUT.PUT_LINE('hello world');
END;
/

在这里插入图片描述

sqlplus设置控制台输出打印内容

执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的，我们需要开启一下。

set serveroutput on

在这里插入图片描述

变量

  1. 普通数据类型（char,varchar2, date, number, boolean, long）
  2. 特殊变量类型（引用型变量、记录型变量）

声明变量的方式为

变量名 变量类型（变量长度）
例如：v_name varchar2(20);

普通变量
  1. 直接赋值语句，使用:=赋值

变量 := 值
例如：v_name := ‘honey’

  1. 语句赋值，使用select … into … 赋值

select 值 into 变量
例如：select ‘honey’ into v_name

-- 打印人员个人信息，包括：姓名、薪水、地址
DECLARE
    -- 姓名
    V_NAME    VARCHAR2(20) := 'honey'; -- 声明变量直接赋值
    -- 薪水
    V_SALARY  NUMBER;
    -- 地址
    V_ADDRESS VARCHAR2(200);
BEGIN
    -- 在程序中直接赋值
    V_SALARY := 1580;
    -- 语句赋值
    SELECT '上海市徐汇区' INTO V_ADDRESS FROM DUAL;
    -- 打印变量
    DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY || '，地址：' || V_ADDRESS);
END;

在这里插入图片描述

引用型变量

变量的类型和长度取决于表中字段的类型和长度

通过表名.列名%TYPE指定变量的类型和长度
例如：v_name emp.ename%TYPE

-- 查询emp表中2号员工的个人信息，并打印姓名和薪水
DECLARE
    -- 姓名
    V_NAME   EMP.NAME%TYPE;
    -- 薪水
    V_SALARY EMP.SALARY%TYPE;
BEGIN
    -- 查询表中的姓名和薪水并赋值给变量
    SELECT NAME, SALARY INTO V_NAME, V_SALARY FROM EMP WHERE NO = 2;
    -- 打印变量
    DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END;

在这里插入图片描述

引用型变量的好处：
使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

记录型变量

接收表中的一整行记录，相当于Java中的一个对象。

变量名 表名%ROWTYPE
例如：v_emp emp%rowtype;

-- 查询emp表中3号员工的个人信息，并打印姓名和薪水
DECLARE
    -- 记录型变量
    V_EMP EMP%ROWTYPE;
BEGIN
    -- 记录型变量默认接收表中的一行数据，不能指定字段。
    SELECT * INTO V_EMP FROM EMP WHERE NO = 3;
    -- 打印变量，通过变量名.属性的方式获取变量中的值
    DBMS_OUTPUT.PUT_LINE('姓名：' || V_EMP.NAME || '，薪水：' || V_EMP.SALARY);
END;

在这里插入图片描述

流程控制

条件分支
BEGIN
    IF 条件1 THEN 执行1
    ELSIF 条件2 THEN 执行2
    ELSE 执行3
    END IF;
END;

-- 判断emp表中的记录数所在的数值范围
DECLARE
    -- emp表中的记录数
    V_COUNT NUMBER;
BEGIN
    -- 查询emp表中的记录数赋值给变量
    SELECT COUNT(1) INTO V_COUNT FROM EMP;
    -- 判断打印
    IF V_COUNT > 20 THEN
        DBMS_OUTPUT.PUT_LINE('EMP表中的记录数超过了20条为：' || V_COUNT || '条。');
    ELSIF V_COUNT >= 10 THEN
        DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10~20条之间为：' || V_COUNT || '条。');
    ELSE
        DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10条以下为：' || V_COUNT || '条。');
    END IF;
END;

在这里插入图片描述

循环
BEGIN
    LOOP
        EXIT WHEN 退出循环条件
    END LOOP;
END;

-- 打印1-10
DECLARE
    -- 声明循环变量并赋初值
    V_NUM NUMBER := 1;
BEGIN
    LOOP
        EXIT WHEN V_NUM > 10;
        DBMS_OUTPUT.PUT_LINE(V_NUM);
        -- 循环变量自增
        V_NUM := V_NUM + 1;
    END LOOP;
END;

在这里插入图片描述

游标

概念

用于临时存储一个查询返回的多行数据（结果集），通过遍历游标，可以逐行访问处理该结果集的数据。

游标的使用方式：声明—>打开—>读取—>关闭

语法

游标的声明：
CURSOR 游标名[(参数列表)] IS 查询语句;
游标的打开：
OPEN 游标名;
游标的取值：
FETCH 游标名 INTO 变量列表;
游标的关闭：
CLOSE 游标名;

游标的属性

游标的属性返回值类型说明
%ROWCOUNT整型获得FETCH语句返回的数据行数
%FOUND布尔型最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND布尔型与%FOUND属性返回值相反
%ISOPEN布尔型游标已经打开时值为真，否则为假

其中%NOTFOUND是在游标中找不到元素的时候返回TRUE，通常用来判断退出循环。

创建与使用

-- 使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来
DECLARE
    -- 声明游标
    CURSOR C_EMP IS
        SELECT NAME, SALARY
        FROM EMP;
    -- 声明变量用来接收游标中的元素
    V_NAME   EMP.NAME%TYPE;
    V_SALARY EMP.SALARY%TYPE;
BEGIN
    -- 打开游标
    OPEN C_EMP;
    -- 遍历游标中的值
    LOOP
        -- 通过FETCH语句获取游标中的值并赋值给变量
        FETCH C_EMP
            INTO V_NAME, V_SALARY;
        -- 判断是否有值，有值打印，没有则退出循环
        EXIT WHEN C_EMP%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
    END LOOP;
    -- 关闭游标
    CLOSE C_EMP;
END;

在这里插入图片描述

带参数的游标

-- 使用游标查询并打印4号员工的姓名和薪资
DECLARE
    -- 声明游标传递参数
    CURSOR C_EMP(V_NO EMP.NO%TYPE) IS
        SELECT NAME, SALARY
        FROM EMP
        WHERE NO = V_NO;
    -- 声明变量用来接收游标中的元素
    V_NAME   EMP.NAME%TYPE;
    V_SALARY EMP.SALARY%TYPE;
BEGIN
    -- 打开游标并传递参数
    OPEN C_EMP(4);
    -- 遍历游标中的值
    LOOP
        -- 通过FETCH语句获取游标中的值并赋值给变量
        FETCH C_EMP
            INTO V_NAME, V_SALARY;
        -- 判断是否有值，有值打印，没有则退出循环
        EXIT WHEN C_EMP%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
    END LOOP;
    -- 关闭游标
    CLOSE C_EMP;
END;

在这里插入图片描述

存储过程

概念

上述PLSQL程序可以进行表的操作、判断、循环逻辑处理的工作，但无法重复调用。
将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序称之为存储过程。

作用

  1. 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭（连接和关闭很耗费资源），需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务，可以大大提高效率；
  2. ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误（如果在数据库中操作数据，可以有一定的日志恢复等功能）

语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN

END [过程名称];

无参存储过程

创建
-- 通过调用存储过程打印hello world
CREATE OR REPLACE PROCEDURE P_HELLO IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello world');
END P_HELLO;

在这里插入图片描述

在这里插入图片描述

调用
在DataGrip中调用
BEGIN
    -- 直接输入调用存储过程的名称
    P_HELLO;
END;

在这里插入图片描述

在sqlplus中调用
set serveroutput on
BEGIN
    -- 直接输入调用存储过程的名称
    P_HELLO;
END;
/

在这里插入图片描述

-- 使用EXEC命令调用
exec P_HELLO;

在这里插入图片描述

有输入参数的存储过程

-- 查询并打印某个员工（如5号员工）的姓名和薪水
CREATE OR REPLACE PROCEDURE P_QUERY(I_NO IN EMP.NO%TYPE) IS
    -- 声明变量接收查询结果
    V_NAME   EMP.NAME%TYPE;
    V_SALARY EMP.SALARY%TYPE;
BEGIN
    -- 根据用户传递的员工号查询姓名和薪水
    SELECT NAME, SALARY INTO V_NAME, V_SALARY FROM EMP WHERE NO = I_NO;
    -- 打印结果
    DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END P_QUERY;

在这里插入图片描述

BEGIN
    P_QUERY(5);
END;

在这里插入图片描述

有输出参数的存储过程

-- 查询并打印某个员工（如5号员工）的姓名和薪水
CREATE OR REPLACE PROCEDURE P_QUERY_OUT(
    I_NO IN EMP.NO%TYPE,
    O_NAME OUT EMP.NAME%TYPE,
    O_SALARY OUT EMP.SALARY%TYPE
) IS
BEGIN
    SELECT NAME, SALARY INTO O_NAME, O_SALARY FROM EMP WHERE NO = I_NO;
END P_QUERY_OUT;

在这里插入图片描述

DECLARE
    --声明一个变量接受存储过程的输出参数
    V_NAME   EMP.NAME%TYPE;
    V_SALARY EMP.SALARY%TYPE;
BEGIN
    P_QUERY_OUT(5, V_NAME, V_SALARY);
    DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END;

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/328536.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【数据结构】——期末复习题题库（11）

【数据结构】——期末复习题题库（11）

🎃个人专栏： 🐬 算法设计与分析：算法设计与分析_IT闫的博客-CSDN博客 🐳Java基础：Java基础_IT闫的博客-CSDN博客 🐋c语言：c语言_IT闫的博客-CSDN博客 🐟MySQL&#xff1a…
阅读更多...
飞凌嵌入式这2款核心板已完成“矿鸿OS”适配，矿企伙伴看过来

飞凌嵌入式这2款核心板已完成“矿鸿OS”适配，矿企伙伴看过来

近日，飞凌嵌入式FETA40i-C和FETMX6ULL-S两款核心板成功完成“矿鸿OS”的系统适配，嵌入式核心板与“矿鸿”操作系统的结合与应用，将进一步推动煤矿行业的数字化、智能化进程。 矿鸿OS（矿山鸿蒙系统）是华为和国家能源集团…
阅读更多...
全自动网页制作系统流星全自动网页生成系统重构版输入网页信息即可制作

全自动网页制作系统流星全自动网页生成系统重构版输入网页信息即可制作

源码优点: 所有模板经过精心审核与修改，完美兼容小屏手机大屏手机，以及各种平板端、电脑端和360浏览器、谷歌浏览器、火狐浏览器等等各大浏览器显示。 免费制作 为用户使用方便考虑，全自动网页制作系统无需繁琐的注册与登入，直接…
阅读更多...
Go后端开发 -- 面向对象特征：结构体 继承 多态 interface

Go后端开发 -- 面向对象特征：结构体 继承 多态 interface

Go后端开发 – 面向对象特征：结构体 && 继承 && 多态 && interface 文章目录 Go后端开发 -- 面向对象特征：结构体 && 继承 && 多态 && interface一、Go的结构体1.结构体的声明和定义2.结构体传参 二、将…
阅读更多...
RXJS中Subject, BehaviorSubject, ReplaySubject, AsyncSubject的区别？

RXJS中Subject, BehaviorSubject, ReplaySubject, AsyncSubject的区别？

在RxJS（Reactive Extensions for JavaScript）中，Subject、BehaviorSubject、ReplaySubject和AsyncSubject都是Observable的变体，它们用于处理观察者模式中的不同场景。以下是它们之间的主要区别： 1、Subject: 是一种特…
阅读更多...
AGI是否应该具备基础的常识模型

AGI是否应该具备基础的常识模型

通用人工智能（AGI）是指能够理解或学习任何人类或其他动物能够做的智力任务的人工智能系统，它是人工智能领域的终极目标之一。 AGI 的研究范式是指在 AGI 领域中，研究者们所遵循的一些基本的理念、方法和目标。 AGI 是否应该存在基…
阅读更多...
使用 Picocli 开发 Java 命令行，5 分钟上手

使用 Picocli 开发 Java 命令行，5 分钟上手

大家好，我是鱼皮，对不会前端的同学来说，开发 命令行工具 是一种不错的展示系统功能的方式。在 Java 中开发命令行工具也很简单，使用框架，几分钟就能学会啦~ Picocli 入门 Picocli 是 Java 中个人认为功能最完善、最简单…
阅读更多...
Android系统开发之浅谈广播接收器回调

Android系统开发之浅谈广播接收器回调

广播接器BroadcastReceiver 广播Intent和广播接收器BroadcastReceiver，是大家android开发用的特别多的二个控件。 那如何从系统角度看待广播和广播接收器呢？ 对于静态注册BroadcastReceiver和动态注册的BroadcastReceiver是如何回调其onReceive方法呢…
阅读更多...
Docker网络配置

Docker网络配置

网络相关 子网掩码 互联网是由许多小型网络构成的，每个网络上都有许多主机，这样便构成了一个有层次的结构。 IP 地址在设计时就考虑到地址分配的层次特点，将每个 IP地址都分割成网络号和主机号两部分，以便于IP 地址的寻址操作。…
阅读更多...
从0到1：实验室设备借用小程序开发笔记

从0到1：实验室设备借用小程序开发笔记

概论 实验室设备借用小程序，适合各大高校，科技园区，大型企业集团的实验室设备借用流程, 通过数字化的手段进一步提升相关单位设备保障水平，规范实验室和设备管理，用户通过手机小程序扫描设备的二维码，可以…
阅读更多...
YOLOv5改进系列（26）——添加RFAConv注意力卷积（感受野注意力卷积运算）

YOLOv5改进系列（26）——添加RFAConv注意力卷积（感受野注意力卷积运算）

【YOLOv5改进系列】前期回顾： YOLOv5改进系列（0）——重要性能指标与训练结果评价及分析 YOLOv5改进系列（1）——添加SE注意力机制 YOLOv5改进系列（2）——添加CBAM注意力机制 YOLOv5改进系列&…
阅读更多...
Redis在Windows10中安装和配置

Redis在Windows10中安装和配置

1.首先去下载Redis 这里不给出下载地址，自己可以用去搜索一下地址 下载 下载完成后解压到D盘redis下，本人用的是3.2.100 D:\Redis\Redis-x64-3.2.100 2.解压完成后需要设置环境变量，这里新建一个系统环境变量中path 中添加一个文件所…
阅读更多...
如何用GPT 运行python？GPT4科研应用与AI绘图及论文高效写作

如何用GPT 运行python？GPT4科研应用与AI绘图及论文高效写作

详情点击链接：如何用GPT 运行python？GPT4科研应用与AI绘图及论文高效写作 一OpenAI 1.最新大模型GPT-4 Turbo 2.最新发布的高级数据分析，AI画图，图像识别，文档API 3.GPT Store 4.从0到1创建自己的GPT应用 5. 模型…
阅读更多...
计算机导论07-算法和数据结构

计算机导论07-算法和数据结构

文章目录 算法基础算法及其特性算法的概念算法与程序算法表示 算法的描述自然语言流程图盒图（N-S图）伪代码程序设计语言 算法评价算法的衡量标准算法的规模时间复杂度空间复杂度 数据结构数据结构的概念数据的逻辑结构数据的存储结构数据的基本操作 常用…
阅读更多...
6.3.4录制屏幕

6.3.4录制屏幕

6.3.4录制屏幕 除了可以进行声音录制外，Camtasia4还允许录制屏幕上的各种操作，并且在录制视频的同时还可以混入讲解，这在制作视频教程时很有用处。 1．在Camtasia Studio主程序中，单击【工具】|【Camtasia录像器】&am…
阅读更多...
抖店商家对接带货主播建议，远离头部主播保平安，附沟通话术模板

抖店商家对接带货主播建议，远离头部主播保平安，附沟通话术模板

我是王路飞。 抖店出单玩法中，商品卡属于靠天吃饭，有一定的风险，所以不建议新手选择。 我们自己包括学生做店，一直都是以达人模式为主的，主要是可控（风险可控，数据可控，流程可控&a…
阅读更多...
Qt第二周周二作业

Qt第二周周二作业

代码&#xff1a; widget.h #ifndef WIDGET_H #define WIDGET_H#include <QWidget>QT_BEGIN_NAMESPACE namespace Ui { class Widget; } QT_END_NAMESPACEclass Widget : public QWidget {Q_OBJECTpublic:Widget(QWidget *parent nullptr);~Widget();void paintEvent(…
阅读更多...
CAD 相关技巧

CAD 相关技巧

空格键&#xff1a; &#xff08;1&#xff09;确认操作 &#xff08;2&#xff09;重复上一步操作删除键&#xff1a;E直线命令&#xff1a;输入L选择方式&#xff1a;框选与点选&#xff0c;对于框选&#xff1a;左框选&#xff0c;必须全部框选完才会被选择&#xff0c;右框…
阅读更多...
FTP文件传输协议 、多种方式安装yum仓库

FTP文件传输协议 、多种方式安装yum仓库

一、网络文件共享服务 1.存储类型分三种&#xff1a; 直连式存储&#xff1a;Direct-Attached Storage&#xff0c;简称DAS 存储区域网络&#xff1a;Storage Area Network&#xff0c;简称SAN&#xff08;可以使用空间&#xff0c;管理也是你来管理&#xff09; 网络附加存储…
阅读更多...
【算法分析与设计】跳跃游戏

【算法分析与设计】跳跃游戏

题目 给定一个长度为 n 的 0 索引整数数组 nums。初始位置为 nums[0]。 每个元素 nums[i] 表示从索引 i 向前跳转的最大长度。换句话说&#xff0c;如果你在 nums[i] 处&#xff0c;你可以跳转到任意 nums[i j] 处: 0 < j < nums[i] i j < n 返回到达 nums[n - …
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站