Oracle中的PL/SQL

太过爱你忘了你带给我的痛 2024-03-16 15:02 56阅读 0赞

**目录**

一、简介

二、基本结构

Ⅰ、仅包含执行部分的结构

Ⅱ、如果一个块带有声明和执行部分，但是没有异常处理部分

三、注释

3.1. 单行注释：

3.2. 多行注释：

3.3. 文档注释：

四、字符集

五、数据类型

六、常量与变量

6.1. 定义常量的格式：

6.2. 定义变量的格式：

6.3.变量初始化

--------------------

## 一、简介 ##

PL/SQL是**过程化语言**（Procedural Language/Structured Query Language）的缩写，是Oracle中的一种编程语言，它**集成了SQL的优点并添加了过程和循环结构、条件判断和异常处理**等编程元素。PL/SQL被**广泛应用于开发存储过程、触发器和函数等数据库对象**，这些对象可以进一步封装业务逻辑、提高数据库性能和减少网络流量等方面。

## 二、基本结构 ##

它的基本结构包括声明区、执行区和异常区，如下所示：

DECLARE
    -- 此处进行变量或者常量的声明：常声明一些变量、常量、用户定义的数据类型以及游标等
    -- 这一部分可选，如不需要可以不写
    
    BEGIN
    -- 主程序体，在这里可以加入各种合法语句
    -- 此处编写 PL/SQL 代码进行业务逻辑处理
    
    EXCEPTION
    -- 此处对可能发生的异常进行处理，当程序中出现错误时执行这一部分
    
    END;--主程序体结束

其中，DECLARE（可选）用于声明在程序中使用的所有变量、常量、游标、子程序和异常等。BEGIN和END之间的语句是程序的主体，用于实现业务逻辑。在EXCEPTION块中，可以捕获或处理引发程序错误或其他问题的事件。

因此从上面这个结构就可以看出，它包含3个基本部分：**声明部分(declarative section)、执行部分(executable section)和异常处理部分(exception section)**。其中，**只有执行部分是必须的，其他两个部分都是可选的**。

> **注意：**
> 
> 该结构最后的分号是必需的。

如果没有声明部分，结构就以BEGN关键字开头，如果没有异常处理部分，关键字EXCEPTION将被省略，END关键字后面紧跟着一个分号结束该块的定义。

### Ⅰ、仅包含执行部分的结构 ###

定义如下所示：

BEGIN
    *执行部分*/
    END;

其实在PL/SQL中，我们还可以***使用匿名块和有名块来组织和管理代码***。

*匿名块是指没有为其命名的PL/SQL代码块，它可以直接在SQL\*Plus命令窗口或其他工具中输入并执行*。上述即为匿名块表达方式，例如下面的示例：

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, world!');
    END;

此处，BEGIN和END界定了一个匿名块，其中的PL/SQL代码只是简单地输出一条消息。

### Ⅱ、如果一个块带有声明和执行部分，但是没有异常处理部分 ###

其定义如下：

DECLARE
    /体声明部分*/
    BEGIN
    /*执行部分*/
    END;

下面的示例演示一个PL/SQL代码块带有声明和执行部分，但没有异常处理部分的情况：

DECLARE
       v_emp_id employees.employee_id%TYPE := 100; 
       v_salary employees.salary%TYPE;
    BEGIN
       SELECT salary INTO v_salary FROM employees WHERE employee_id = v_emp_id;
       dbms_output.put_line('The salary of the employee with ID ' || v_emp_id || ' is ' || v_salary);
    END;
    /

上例中，我创建了一个名为v\_emp\_id的变量，它持有员工ID。然后，我使用SELECT语句从employees表中查找具有给定ID的员工的薪资，并将结果存储在v\_salary变量中。最后，使用DBMS\_OUTPUT包中的PUT\_LINE方法输出结果到屏幕上。

> **备注：**
> 
> 在实际编程过程中，异常处理部分对于保障程序的稳定运行和数据安全非常重要，因此建议不要省略。

在上面描述仅包含执行部分的结构定义时提到了有匿名块，这里简单描述下有名块！

***有名块则是具有名称的PL/SQL代码块，通常用于存储复杂的业务逻辑和可重复使用的程序段***。有名块可以通过***CREATE PROCEDURE或CREATE FUNCTION语句创建***。例如：

CREATE OR REPLACE PROCEDURE show_salary (EMP_ID NUMBER) IS
       v_salary employees.salary%TYPE;
    BEGIN
       SELECT salary INTO v_salary FROM employees WHERE employee_id = EMP_ID;
       dbms_output.put_line('The salary of the employee with ID ' || EMP_ID || ' is ' || v_salary);
    END;

这里我使用CREATE PROCEDURE创建了一个名为show\_salary的块，用于查询指定员工的薪资信息并输出结果。有名块通常具有更复杂的结构和功能，例如参数、返回值、游标等。

> **提示：**
> 
> 匿名块和有名块注意区分定义即可，能够清晰分辨出来它们。在实际工作应用中，大多数情况都是使用有名块，即我们用来创建函数，存过等。

## 三、注释 ##

PL/SQL代码可以使用三种不同类型的注释：

### 3.1. 单行注释： ###

以两个短横线（--）开头，直到行尾的所有内容都被视为注释。例如：

-- This is a single-line comment

假设有如下PL/SQL块：

DECLARE
    V_Department CHAR(3);
    V_Course     NUMBER;
    BEGIN
        INSERT INTO classes (department,course)
             VALUES(V_Department,V_Course);
    END;

为使用户此块更加容易理解，给他们加上注释，如下所示：

DECLARE
    V_Department  CHAR(3);   --保存3个字符的变量
                             --系代码
    V_Course NUMBER;         --保存课程号的变量
    BEGIN
    --插入一条记录
       INSERT INTO classes(department,course)
            VALUES (V_Department,V_Course);
    END;

> **提示：**
> 
> 如果注释超过一行，就必须在每一行的开头使用双连字符(--) 。

### 3.2. 多行注释： ###

以/\*开头，以\*/结尾，之间的所有内容都被视为注释。它可以跨越多行。这和C语言用法一样。例如：

/* This is a multi-line comment, which can extend over 
          multiple lines until it is closed with a */ symbol. */
    
    --如下PL/SQL块多行注释应用
    DECLARE
    v_Department CHAR(3);  /*体保存三个字符的变量，系代码*/
    V_course     NUMBER;  /*体保存课程号的变量。*/
    BEGIN
       /*插入一条记录*/
    INSERT INTO classes (department,course)
         VALUES(V_Department,V_Course);
    END;

### 3.3. 文档注释： ###

以/\*\*开头，可以跨越多行，主要用于文档生成工具。例如：

/**
        * This is a documentation comment for a PL/SQL program.
        **/

> **备注：**
> 
> 在Oracle中，文档注释（Document Comment）是从PL/SQL 9.0版本开始引入的，并且目前在所有主流的Oracle数据库版本中都支持。因此，从Oracle 9i到最新的版本（例如Oracle 19c），都可以在PL/SQL程序中使用文档注释。

上述几种注释可以用于记录PL/SQL程序中的重要信息，例如程序目的、作者、创建日期等，并提高代码可读性和维护性。应用注释的方法是在需要添加注释的地方插入相应的注释符号并紧跟着注释内容。例如，在以下PL/SQL程序段中，我添加注释：

DECLARE
       -- 声明一个变量来保存员工 ID
       v_emp_id employees.employee_id%TYPE := 100;
       -- 声明一个变量来保存员工工资
       v_salary employees.salary%TYPE;
    
    BEGIN
       -- 使用给定 ID 检索员工的工资
       SELECT salary INTO v_salary FROM employees
       WHERE employee_id = v_emp_id;
    
       -- 将工资信息打印到输出控制台
       dbms_output.put_line('The salary of the employee with ID ' || v_emp_id || ' is ' || v_salary);
    END;

我用单行注释将变量声明和查询语句解释了一下。这些注释可以帮助其他同事更好地理解代码的工作原理，并且在以后需要修改或扩展代码时，它们会提供有用的提示和说明。

> **建议：**
> 
> 养成写代码同时添加注释的好习惯，特别是自己开发的代码，再没有完全上线前，要经历代码调试和环境测试，如果发现问题可以找到相应位置，及时更改，为后续的工作提供了良好的帮助，特别是特殊字段添加，脚本优化，注释是很有必要的！

## 四、字符集 ##

PL/SQL的字符集包括以下几种类型：

*  1. ASCII字符集：这是PL/SQL中最基本的字符集，包括所有常见的英文字母、数字和符号。
 *  2. Unicode字符集：Unicode是一种国际编码标准，它支持世界上几乎所有的字母、符号和文字，包括中文、日文、韩文等。在Oracle中，PL/SQL可以使用Unicode字符集来表示和处理各种语言的文本数据。
 *  3. 操作系统本地字符集：这是操作系统默认使用的字符集，它通常以本地语言为主导，例如在中国大陆系统上可能是GB18030或UTF-8，在台湾或香港则可能是Big5等。PL/SQL程序可以使用操作系统本地字符集与操作系统交互，并进行文件输入输出等操作。
 *  4. 用户定义的字符集：在某些情况下，PL/SQL程序需要使用特定的字符集来处理文本数据，例如处理特殊的XML或JSON数据格式。Oracle数据库提供了一些工具和接口，允许用户自定义字符集并进行相关操作。

Oracle中PL/SQL的字符集是非常丰富的，可以满足各种语言和应用场景的需求。

## 五、数据类型 ##

PL/SQL的数据类型包括以下几种类型：

*  1. 数值型（Numeric）：包括整数、实数、带精度的十进制数等。其中最常用的是NUMBER类型，用于表示任意精度的数值。
 *  2. 字符型（Character）：包括固定长度的CHAR类型和可变长度的VARCHAR2类型。其中CHAR类型需要指定具体的长度，而VARCHAR2类型可以指定在一定范围内的任意长度。
 *  3. 日期与时间型（Date and Time）：用于表示日期、时间和日期与时间组合的数据类型。其中最常见的是DATE类型，可以存储从公元前4712年到公元9999年的日期和时间。
 *  4. 布尔型（Boolean）：用于表示逻辑真或假的数据类型。仅有两个取值：TRUE和FALSE。
 *  5. 大对象型（Large Object）：用于表示大型二进制数据和文本数据。包括BLOB和CLOB两种类型，BLOB用于存储二进制数据，CLOB用于存储Unicode文本数据。
 *  6. PL/SQL表型（PL/SQL Tables）：是PL/SQL语言的特殊数据类型之一，它是一种类似于数组的集合类型，可以存储多个同类型的数据项。PL/SQL表分为索引表和关联表两种类型，分别对应于基于数字下标和基于字符串下标的数组。
 *  7. 记录型（Record）：用于表示类似于结构体的复杂数据类型。记录类型是由一组字段组成的数据结构，每个字段可以有自己的数据类型。
 *  8. 其他数据类型：包括REFCURSOR类型，可以用于返回一个游标对象；BINARY\_INTEGER类型，用于高性能数值运算等。

其实*PL/SQL的数据类型与SQL语句的数据类型基本上是一样*的，都包括数值型（Numeric）、字符型（Character）、日期和时间型（Date and Time）等。但是在PL/SQL中还有一些特殊的数据类型，例如*PL/SQL表型（PL/SQL Tables）和记录型（Record）*，它们是PL/SQL语言的特殊数据类型之一，而在SQL语句中并不存在。

此外，虽然*PL/SQL的数据类型与SQL语句的数据类型大体相同，但它们的用法和行为可能会存在差异*。例如，在SQL语句中，对于带小数的数值类型，使用ROUND函数进行四舍五入时可能会向偶数取整（也就是“银行家舍入”规则），而在PL/SQL中则可以通过指定ROUND函数的第二个参数来控制四舍五入的方式。

因此，虽然Oracle中PL/SQL的数据类型与SQL语句的数据类型基本上是一致的，但在具体使用时需要注意它们的细微差别和不同的行为特点。

## 六、常量与变量 ##

通常PL/SQL中的常量和变量可以使用DECLARE关键字来定义。其格式如下：

### 6.1. 定义常量的格式： ###

DECLARE
          常量名 CONSTANT 数据类型 [:= 初始值];

常量一旦定义，在以后的使用中其值将不再改变。一些固定的大小为了防止有人改变，最好定义成常量。 如下示例所示：

DECLARE
          PI CONSTANT NUMBER(10,2) := 3.14;
    BEGIN
          DBMS_OUTPUT.PUT_LINE('PI的值为：' || PI);
    END;

在示例中，我定义了一个名为PI的常量，它的数据类型是NUMBER(10,2)，也就是一个小数点前最多有10位数字，小数点后最多有2位数字的数值类型。同时我们还给它赋予了初始值3.14。在BEGIN和END之间，我使用DBMS\_OUTPUT.PUT\_LINE函数输出了PI的值。

### 6.2. 定义变量的格式： ###

DECLARE
          变量名 数据类型 [:= 初始值];

如下示例所示：

DECLARE
          name VARCHAR2(20) := 'John';
          age NUMBER(3) := 30;
    BEGIN
          DBMS_OUTPUT.PUT_LINE(name || '的年龄是' || age || '岁。');
    END;

在上述示例中，我定义了两个变量name和age，它们的数据类型分别是VARCHAR2(20)和NUMBER(3)，也就是一个长度最大为20的字符串类型和一个数值类型，同时给它们赋予了初始值'John'和30。在BEGIN和END之间，我使用DBMS\_OUTPUT.PUT\_LINE函数输出了"name 的年龄是 age岁。"这个字符串，其中引用了name和age这两个变量的值。

> **注意：**
> 
> 在Oracle中，所有的常量和变量必须先进行定义才能使用。同时也可以在声明部分直接指定默认值，如果不指定默认值，则系统自动赋予它们NULL值。

### 6.3.变量初始化 ###

PL/SQL变量可以通过初始化来为其赋初始值。*变量初始化可以在变量声明的时候进行，也可以在变量赋值之前进行*。一般情况下，*在初始化变量之前需要先声明变量和指定变量的数据类型*。下面是一个简单的示例：

DECLARE
       name VARCHAR2(20);
    BEGIN
       name := 'John';
       DBMS_OUTPUT.PUT_LINE(name);
    END;

我定义了一个名为name的字符串类型变量，并将它的数据类型设置为VARCHAR2(20)。在变量声明之后，我使用赋值语句将该变量初始化为'John'，然后使用DBMS\_OUTPUT.PUT\_LINE函数输出变量的值。

但是在有些情况下，当变量被声明的时候就必须要初始化，否则会引发错误或者逻辑问题。比如下面是一个演示只能在初始化阶段赋值的常量变量的示例：

DECLARE
       PI CONSTANT NUMBER(10,2) := 3.14;
    BEGIN
       DBMS_OUTPUT.PUT_LINE(PI);
    END;

在示例中，我定义了一个名为PI的常量，它的数据类型是NUMBER(10,2)，并且将其中的初始值设置为3.14。由于PI是常量，在声明时就必须初始化，否则会导致编译错误。

> **总结：**
> 
> 变量初始化是PL/SQL中非常基础且常见的操作。它能够保证程序中变量的稳定性和可靠性，提高代码的执行效率和质量。