SQLBoy 存储过程之历险记

最新推荐文章于 2024-09-11 23:43:57 发布

故事写在心里-

最新推荐文章于 2024-09-11 23:43:57 发布

阅读量1.7k

点赞数 16

分类专栏：数据库知识体系

本文链接：https://blog.csdn.net/weixin_46163590/article/details/104106169

版权

数据库知识体系专栏收录该内容

57 篇文章 5 订阅

订阅专栏

文章目录：

1、概述：

PL/SQL(Procedural Language/SQL)是一种过程化语言，在 PL/SQL 中可以通过 IF 语句或 LOOP 语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样 PL/SQL 语言就能够实现操控程序处理的细节过程。不像普通的 SQL 语句（如 DML 语句、DQL 语句）那样没有流程控制，也不存在变量。因此，使用 PL/SQL 语言可以实现比较复杂的业务逻辑。PL/SQL 是 Oracle 的专用语言，它是对标准 SQL 语言的扩展，它允许在其内部嵌套普通的 SQL 语句，这样就将 SQL 语句的数据操纵能力、数据查询能力和 PL/SQL 的过程处理能力结合在一起，达到各自取长补短的目的。

2、存储过程概念及语法格式:

前言：PL/SQL程序是以块（BLock）为基本单位，整个PL/SQL块分为3部分：
声明部分（用Declare 开头）
执行部分（以Begin 开头）
结束部分（以END 结束）
异常处理部分（Exception 开头）
其中执行部分是必需的，其他两个部分可选。无论 PL/SQL 程序段的代码量有多大，其基本结构都是由这 3 部分组成的。

PL/SQL 块的语法格式如下：

[DECLARE]
–声明部分，可选
BEGIN
–执行部分，必须
[EXCEPTION]
–异常处理部分，可选
END

简单案例求：100个自然数中偶数之和是多少?

— Created on 2020/4/19 星期日 by ADMINISTRATOR
declare
sum_i int:=0;
begin
for i in reverse 1…100 loop
if mod(i,2)=0 then
sum_i:=sum_i+i;
end if;
end loop;
dbms_output.put_line(‘前100个自然数中偶数之和是：’||sum_i);

end;

以下是结果部分：
在这里插入图片描述
案例二：
以下是原表

– Created on 2020/4/19 星期六 by ADMINISTRATOR
declare
name varchar2(20);
score number;
begin
select name, score into name, score from course_score where score = 50;

dbms_output.put_line(name || ‘的分数是:’ || score);
end;
在这里插入图片描述

3、数据类型与定义变量和常量

数据类型本质上是一种用于描述数据存储的内存结构，用它来决定变量中所存储数据的类型。而变量本质上是一种用名称进行识别的符号，它可以存储不同的类型的数据。根据不同的数据类型，定义不同名称的变量，这样就可以存储不同类型的数据。变量在程序运行的过程中，其值可以发生变化；与变量对应的就是常量，常量是指在程序运行的过程中，其值不会发生变化的量。

基本数据类型分为以下5种：

数值类型、字符类型、日期类型、布尔类型、特殊数据类型、

1、数值类型

数值类型主要包括 NUMBER、PLS_INTEGER 和 BINARY_INTEGER 3 种基本类型。
其中，NUMBER 类型的变量可以存储整数或浮点数；

NUMBER 类型还可以通过 NUMBER(P,S)的形式来格式化数字，其中，参数 P 表示精度，参数 S表示刻度范围。。精度是指数值中所有有效数字的个数，而刻度范围是指小数点右边小数位的个数，在这里精度和刻度范围都是可选的。

声明一个精度为 9，且刻度范围为 2 的表示金额的变量 Num_Money，代码如下。
Num_Money NUMBER(9,2);

2、字符类型
字符类型主要包括 VARCHAR2、CHAR、LONG、NCHAR 和 NVARCHAR2 等。这些类型的变量
用来存储字符串或字符数据。下面对这几种字符类型进行讲解。

（1）VARCHAR2 类型：PL/SQL 语言中的 VARCHAR2 类型和数据库类型中的 VARCHAR2 比较类似，用于存储可变长度的字符串，其语法格式为： VARCHAR2(30)

注意：
数据库类型的 VARCHAR2 的最大长度是 4000 字节，所以一个长度大于 4000 字节的PL/SQL 类型 VARCHAR2 变量不可以赋值给数据库中的一个 VARCHAR2 变量，而只能赋值给LONG 类型的数据库变量。

（2）CHAR 类型：CHAR 类型表示指定长度的字符串，其语法格式如下：
CHAR(20)

注意：数据库类型中的 CHAR 只有 2000 字节，所以如果 PL/SQL 中 CHAR 类型的变量长度大于 2000 字节，则不能赋给数据库中的 CHAR。
（3）LONG 类型：LONG 类型表示一个可变的字符串，最大长度是 32767 字节，而数据库类型的
LONG 最大长度可达 2GB，所以任何字符串变量都可以赋值给它。
（4）NCHAR 和 NVARCHAR2 类型：这两种数据类型的长度要根据各国字符集来确定，只能具体情况具体分析。
3、日期类型

日期类型只有一种——DATE 类型，用来存储日期和时间信息，DATE 类型的存储空间是 7 个字节，分别使用一个字节存储世纪、年、月、天、小时、分钟和秒。

4、布尔类型

布尔类型也只有一种——BOOLEAN 类型，主要用于程序的流程控制和业务逻辑判断，其变量值可以是 TRUE、FALSE 或 NULL 中的一种。

4、特殊数据类型之%TYPE和%ROWTYPE

为了提高用户的编程效率和解决复杂的业务逻辑需求，PL/SQL 语言除了可以使用 Oracle 规定的基本数据类型外，还提供了 3 种特殊的数据类型，但这 3 种类型仍然是建立在基本数据类型基础之上的。

5、在存储过程中%TYPE和%ROWTYPE常用来在PL/SQL中定义变量:

1．%TYPE 类型
使用%TYPE 关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。
declare
var_job emp.job%type;

2．%ROWTYPE 类型

在PL/SQL中讲一个记录声明为具有相同类型的数据库行时，使用%ROWTYPE

使用是最多的在存储过程中，DECLARE v_code oms_outbound%ROWTYPE

6、循环语句Loop 使用

当程序需要反复执行某一操作时，就必须使用循环结构。PL/SQL 中的循环语句主要包括 loop 语句、while 语句和 for 语句 3 种，

这里只讲解：loop 语句

loop 语句会先执行一次循环体，然后再判断“exit when”关键字后面的条件表达式的值是 true 还是 false，如果是 true，则程序会退出循环体，否则程序将再次执行循环体，这样就使得程序至少能够执行一次循环体，它的语法格式如下：
在这里插入图片描述
plsql_student_table：循环体中的 PL/SQL 语句，可能是一条，也可能是多条，这是循环体的核心部分，这些 PL/SQL 语句至少被执行一遍。

end_stuv_exp;：循环结束条件表达式，当该表达式的值为 true 时，则程序会退出循环体，否则程序将再次执行循环体。

7、PL/SQL 游标概念理解与使用

游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，，或是批处理、存储过程、触发器中的数据处理请求。
游标的作用就相当于指针，通过游标 PL/SQL 程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供了很大方便。
Oracle 中，通过游标操作数据主要使用显式游标和隐式游标。

8、在工作中常用的当属是《显示游标》下面来讲解：cursor

显示游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由 SELECT 语句返回的查询结果），使用它处理数据的步骤包括：声明游标、打开游标、读取游标和关闭游标 4 个步骤。其中读取游标可能是个反复操作的步骤，因为游标每次只能读取一行数据，所以对于多条记录，需要反复读取，直到游标读取不到数据为止，
在这里插入图片描述
下面是正确使用游的格式：

cursor cur_emp（var_job in varchar2 : ='salesman'
is select empno,ename,sal from emp ）
where job =var_job;

声明了一个名称为 cur_emp 的游标，并定义一个输入参数 var_job（类型为
varchar2，但不可以指定长度，如：varchar2(10)，否则程序报错），该参数用来存储雇员的职务（初始值为 SALESMAN），然后使用 SELECT 语句检索职务是销售员的结果集，以等待游标逐行读取它。

2．打开游标
open cur_name;

3．读取游标
fetch cur_name;

4.close cur_name;

5.异常处理
exception
when primary_iterant then
dbms_output.put_line(‘主键不允许重复！’); //输出异常描述信息
end;
/
commit；

9、业务逻辑异常

在实际的应用中，程序员可以根据具体的业务逻辑规则自定义一个异常。这样，当用户操作违反业务逻辑规则时，就引发一个自定义异常，从而中断程序的正常执行并转到自定义的异常处理部分。
但无论是预定义异常，还是错误编号异常，都是由Oracle系统判断的错误，但业务逻辑异常是Oracle系统本身无法知道的，这样就需要有一个引发异常的机制，引发业务逻辑异常通常使用 RAISE 语句来实现。
当引发一个异常时，控制就会转到 EXCEPTION 异常处理部分执行异常处理语句。业务逻辑异常首先在 DECLARE 部分使用 EXCEPTION 类型声明一个异常变量，然后在 BEGIN 部分根据一定的业务逻辑规则执行 RAISE 语句（在 RAISE 关键字后面跟着异常变量名），最后在 EXCEPTION 部分编写异常处理语句。