ORACLE-PL/SQL基础(二):编写程序(游标、存储过程、函数、包...)

本篇为ORACLE数据库系列,PL/SQL语言的第二讲,对PL/SQL语言结构、数据类型等前置知识并不熟悉的uu可参考笔者上一篇Blog,进行简单了解~

指路:PL-SQL基础(一):变量赋值、数据类型

在基本了解了PL/SQL结构之后,我们来看看,在PL/SQL中支持的更多“花招”。本文会详细介绍PL/SQL中的常见程序设计操作,内容如下:

  1. 伪列
  2. 游标
  3. 存储过程
  4. 函数
  5. 自定义函数

在存储过程与函数部分,我们将简单介绍如何在PL/SQL的程序中设置异常处理。

值得注意的是:

在ORACLE等数据库中,所有可以CREATE创建的,都是数据库对象,都支持DELETE、ALTER、DROP、GRANT等关键字的操作。


1. 伪列

伪列也是一种SQL元素,他不由用户创建的字段,也不是数据库创建的表中的一列,但是我们可以直接使用伪列,来获取如:行号、查询结果行号等数据,它主要包括:

​ ROWID:表中一条记录的行号

​ ROWNUM:SQL查询结果的行号(通过指定ROWNUM,可以对结果进行筛选,如只保留前三行结果等)

​ LEVEL:当在使用SQL语句进行类似CONNETCT BY类操作时,表示查询结果间树形的关系即每行的层次

2. 游标

PL/SQL的游标分为隐式和显式两种。显示即传统地声明一个游标,之后打开,通过LOOP循环和FETCH(next)使用;隐式游标则更类似使用了一个for循环,由ORACLE隐式创建一个游标对象来完成循环。

使用游标的操作步骤为:

  1. 声明游标
  2. 打开游标
  3. 提取和处理游标数据
  4. 关闭游标

PS:游标只有打开,才真正开始使用

fetch 每次执行,相当于是 fetch next 即获取了下一个符合SQL条件的数据

游标示例代码:
--定义游标名称与可选的参数
--参数可在下面的SQL语句中使用
CURSOR cursor_name[(parameter[,parameter]...)]
--游标fetch返回数据类型
[RETURN return_type]
--SQL语句
IS select_statement;
--打开游标
OPEN cursor_name;
--开启循环
LOOP
	--获取next数据,存入变量中
	FETCH cursor_name INTO ver_1[,ver_2...]
	--使用获取的数据进行一些操作
	...
--结束循环
END LOOP

在使用游标时,如果需要修改或删除表中指定数据行的数据,需要在声明游标时指出:

--游标声明时的SQL语句
IS select_statement
--指定修改的字段,nowait直接将修改数据写入磁盘
FOR UPDATE[OF column[,column]...][NOWAIT]

同时,除了显式的游标外,ORACLE也为我们提供了类似FOR循环的操作,隐式的打开了游标进行操作。

可以理解为,系统在做查询的表上定义了一个类型为**%ROWTYPE**的变量(获取遍历表每个字段的类型),并以此为循环,自动从游标工作区中提取数据并放到该变量中,同时也由系统在结束循环时关闭游标。

使用样例如下:

DECLARE
	CURSOR c_HistoryStudent IS
		SELECT id,first_name,last_name
		FROM students
		WHERE major = 'HISTORY';
BEGIN
	--使用FOR的关键字,隐式的开启游标,查询到的结果就显示在v_StudentData中
	--v_StudentData的数据类型就如游标SQL查询的字段结果
  FOR v_StudentData IN c_HistoryStudent LOOP
    ...
  END LOOP;
  COMMIT;
END;

2. 存储过程

ps:我们定义的存储过程的代码,是存放在服务器端的,也是数据库的一个对象,数据对象的所有操作都可以作用在存储过程上。只有当我们调用一个存储过程的时候,他才接受参数并被执行。

存储过程的一切操作同其他关系型数据库操作基本一致:

创建存储过程:
CREATE [or REPLACE] PROCEDURE PROCEDURE_NAME
--IN|OUT|IN OUT为三种参数模式
[(PARAMETER_1[{IN | OUT | IN OUT}] datatype [{:=|DEFAULT}expression]...)]
{IS | AS}
--可在代码部分前端声明一些之后需要使用的变量
[declarations]
BEGIN
code
--可在存储过程中设置该程序执行遇到问题时的异常处理
[EXCEPTION exception_handlers]
END;

调用时,存储过程的参数支持两种参数传递方式:参数位置对应法、参数命名传递法。

3. 包

包是对一组相关过程、函数、变量、常量、游标等PL/SQL程序设计元素的封装

包内程序元素分为私有及公有,从而实现程序设计的模块化,对外隐藏包内的结构、信息。

创建包分为创建包定义和**创建包主体(BODY)**两步。

创建包定义时,包内元素,如过程,仅做声明,当创建包主体时,再对各个元素进行定义。

4. 自定义函数

在ORACLE中,使用PL/SQL自定义函数实现了对PL/SQL语言功能的进一步扩展。但同时,为了保证数据库的信息安全,自定义函数也存在很多的限制条件。

  1. 被SELECT语句使用的自定义函数不能修改任何数据库表
  2. 被DML语句调用的函数,不能查询或修改被DML语句影响的表
  3. 不能执行事务控制语句
  4. 只能有IN模式的参数
  5. 形参只能使用数据库类型,不能为PL/SQL类型。
    ps:所有的数据库类型都是PL/SQL语言支持的数据类型,而不是所有的PL/SQL类型都是数据库类型!例如:RECORD
  6. 函数的返回类型必须是一个数据库类型(考虑到函数将被SELECT语句或DML语句调用,若使用非数据库类型,则在此情况下无法识别)
CREATE OR REPLACE
FUNCTION get_registered_courses_num(p_ID students:ID%TYPE)
AS
v_registered_courses_num NUMBER(3);
BEGIN
	SELECT count(*) into v_registered_courses_num
	FROM registeres_students
	WHERE student_id = p_ID;
	RETURN v_registeres_courses_num;
	EXCEPTION
		WHEn NO_DATA_FOUND THEN
		RETURN -1;
END;

函数可以在SQL语句中使用,将函数得到的返回值也作为查询结果的一列进行输出。

热门文章

暂无图片
编程学习 ·

exe4j详细使用教程(附下载安装链接)

一、exe4j介绍 ​ exe4j是一个帮助你集成Java应用程序到Windows操作环境的java可执行文件生成工具,无论这些应用是用于服务器,还是图形用户界面(GUI)或命令行的应用程序。如果你想在任务管理器中及Windows XP分组的用户友好任务栏…
暂无图片
编程学习 ·

AUTOSAR从入门到精通100讲(126)-浅谈车载充电系统通信方案

01 引言 本文深入研究车载充电系统策略,设计出一套基于电动汽车电池管理系统与车载充电机的CAN通信协议,可供电动汽车设计人员参考借鉴。 02 电动汽车充电系统通讯网络 电动汽车整车控制系统中采用的是CAN总线通信方式,由一个整车内部高速CAN网络、内部低速CAN网络和一个充电…
暂无图片
编程学习 ·

CMake(九):生成器表达式

当运行CMake时,开发人员倾向于认为它是一个简单的步骤,需要读取项目的CMakeLists.txt文件,并生成相关的特定于生成器的项目文件集(例如Visual Studio解决方案和项目文件,Xcode项目,Unix Makefiles或Ninja输入文件)。然…
暂无图片
编程学习 ·

47.第十章 网络协议和管理配置 -- 网络配置(八)

4.3.3 route 命令 路由表管理命令 路由表主要构成: Destination: 目标网络ID,表示可以到达的目标网络ID,0.0.0.0/0 表示所有未知网络,又称为默认路由,优先级最低Genmask:目标网络对应的netmaskIface: 到达对应网络,应该从当前主机哪个网卡发送出来Gateway: 到达非直连的网络,…
暂无图片
编程学习 ·

元宇宙技术基础

请看图: 1、通过AR、VR等交互技术提升游戏的沉浸感 回顾游戏的发展历程,沉浸感的提升一直是技术突破的主要方向。从《愤怒的小鸟》到CSGO,游戏建模方式从2D到3D的提升使游戏中的物体呈现立体感。玩家在游戏中可以只有切换视角,进而提升沉浸…
暂无图片
编程学习 ·

flink的伪分布式搭建

一 flink的伪分布式搭建 1.1 执行架构图 1.Flink程序需要提交给 Job Client2.Job Client将作业提交给 Job Manager3.Job Manager负责协调资源分配和作业执行。 资源分配完成后,任务将提交给相应的 Task Manage。4.Task Manager启动一个线程以开始执行。Task Manage…
暂无图片
编程学习 ·

十进制正整数与二进制字符串的转换(C++)

Function one: //十进制数字转成二进制字符串 string Binary(int x) {string s "";while(x){if(x % 2 0) s 0 s;else s 1 s;x / 2;}return s; } Function two: //二进制字符串变为十进制数字 int Decimal(string s) {int num 0, …
暂无图片
编程学习 ·

[含lw+源码等]微信小程序校园辩论管理平台+后台管理系统[包运行成功]Java毕业设计计算机毕设

项目功能简介: 《微信小程序校园辩论管理平台后台管理系统》该项目含有源码、论文等资料、配套开发软件、软件安装教程、项目发布教程等 本系统包含微信小程序做的辩论管理前台和Java做的后台管理系统: 微信小程序——辩论管理前台涉及技术:WXML 和 WXS…
暂无图片
编程学习 ·

树莓派驱动DHT11温湿度传感器

1,直接使用python库 代码如下 import RPi.GPIO as GPIO import dht11 import time import datetimeGPIO.setwarnings(True) GPIO.setmode(GPIO.BCM)instance dht11.DHT11(pin14)try:while True:result instance.read()if result.is_valid():print(ok)print(&quo…
暂无图片
编程学习 ·

ELK简介

ELK简介 ELK是三个开源软件的缩写,Elasticsearch、Logstash、Kibana。它们都是开源软件。不过现在还新增了一个 Beats,它是一个轻量级的日志收集处理工具(Agent),Beats 占用资源少,适合于在各个服务器上搜集日志后传输给 Logstas…
暂无图片
编程学习 ·

Linux 基础

通常大数据框架都部署在 Linux 服务器上,所以需要具备一定的 Linux 知识。Linux 书籍当中比较著名的是 《鸟哥私房菜》系列,这个系列很全面也很经典。但如果你希望能够快速地入门,这里推荐《Linux 就该这么学》,其网站上有免费的电…
暂无图片
编程学习 ·

Windows2022 无线网卡装不上驱动

想来 Windows2022 和 windows10/11 的驱动应该差不多通用的,但是死活装不上呢? 搜一下,有人提到 “默认安装时‘无线LAN服务’是关闭的,如果需要开启,只需要在“添加角色和功能”中,选择开启“无线LAN服务…
暂无图片
编程学习 ·

【嵌入式面试宝典】版本控制工具Git常用命令总结

目录 创建仓库 查看信息 版本回退 版本检出 远程库 Git 创建仓库 git initgit add <file> 可反复多次使用&#xff0c;添加多个文件git commit -m <message> 查看信息 git status 仓库当前的状态git diff 差异对比git log 历史记录&#xff0c;提交日志--pret…
暂无图片
编程学习 ·

用Postman生成测试报告

newman newman是一款基于nodejs开发的可以运行postman脚本的工具&#xff0c;使用Newman&#xff0c;可以直接从命令运行和测试postman集合。 安装nodejs 下载地址&#xff1a;https://nodejs.org/en/download/ 选择自己系统相对应的版本内容进行下载&#xff0c;然后傻瓜式安…
暂无图片
编程学习 ·

Java面向对象之多态、向上转型和向下转型

文章目录前言一、多态二、引用类型之间的转换Ⅰ.向上转型Ⅱ.向下转型总结前言 今天继续Java面向对象的学习&#xff0c;学习面向对象的第三大特征&#xff1a;多态&#xff0c;了解多态的意义&#xff0c;以及两种引用类型之间的转换&#xff1a;向上转型、向下转型。  希望能…