当前位置：首页 > article >正文

滚雪球学Oracle[4.2讲]：PL/SQL基础语法

article 2024/10/4 7:59:39

全文目录：

- 前言
- 一、PL/SQL基础语法
- - 1.1 变量声明
  - - 变量声明示例：
- 二、记录类型与集合类型的使用
- - 2.1 记录类型
  - - 记录类型的定义与使用
  - 2.2 集合类型
- 三、PL/SQL表与关联数组
- - 3.1 PL/SQL表（嵌套表）
  - - 嵌套表的定义与使用
  - 3.2 关联数组
  - - 关联数组的定义与使用
- 四、集合操作：BULK COLLECT与FORALL
- - 4.1 BULK COLLECT
  - - 使用BULK COLLECT的示例
  - 4.2 FORALL
  - - 使用FORALL的示例
- 五、总结与下期预告

前言

在上一篇文章【PL/SQL简介与环境设置】中，我们介绍了PL/SQL语言的基本概念及其运行环境的配置。PL/SQL（Procedural Language for SQL）是Oracle数据库的扩展语言，通过将过程化编程引入SQL，开发人员可以创建更加灵活和复杂的数据库操作程序。PL/SQL为处理复杂的业务逻辑提供了强大的工具，但在编写高效代码时，理解其基本语法和数据结构至关重要。

本期内容将详细探讨PL/SQL的基础语法，并深入解析记录类型与集合类型的使用。此外，介绍PL/SQL表和关联数组，展示如何使用BULK COLLECT和FORALL进行批量数据处理，从而提升数据操作的效率。

在文章的结尾，我们将对下期内容【控制结构与循环】进行预告，继续学习PL/SQL中用于流程控制的结构化语法。

一、PL/SQL基础语法

PL/SQL是Oracle数据库独有的过程式语言，它的基本语法结构类似于其他编程语言，包含变量声明、控制语句、循环、异常处理等。PL/SQL块的基本结构如下：

DECLARE
  -- 声明部分（可选）
  v_employee_name VARCHAR2(100);
BEGIN
  -- 执行部分
  SELECT first_name INTO v_employee_name FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
  -- 异常处理部分（可选）
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found.');
END;

在此代码块中，DECLARE部分用于声明变量，BEGIN...END构成了主执行逻辑，而EXCEPTION部分处理可能发生的异常。

1.1 变量声明

PL/SQL允许使用各种数据类型声明变量。常见的数据类型包括：

NUMBER：用于表示整数和浮点数。
VARCHAR2：表示变长字符串。
DATE：表示日期和时间。

变量声明示例：

DECLARE
  v_emp_id NUMBER(6);
  v_emp_name VARCHAR2(100);
  v_hire_date DATE;
BEGIN
  -- 在这里可以使用这些变量
END;

变量必须在使用之前声明，并且每个变量都需要指定其数据类型。PL/SQL支持从表中获取数据并赋值给变量，如通过SELECT INTO语句。

二、记录类型与集合类型的使用

2.1 记录类型

记录类型（Record Type）是PL/SQL中用于存储一组不同类型数据的结构，类似于C语言中的结构体或其他编程语言中的对象。记录类型允许将表中的一行或多个列组合到一个变量中，便于操作多列数据。

记录类型的定义与使用

PL/SQL允许开发者自定义记录类型或基于表行定义记录类型：

自定义记录类型：

DECLARE
  TYPE employee_record IS RECORD (
    emp_id employees.employee_id%TYPE,
    emp_name employees.first_name%TYPE,
    hire_date employees.hire_date%TYPE
  );
  v_emp_rec employee_record;
BEGIN
  -- 使用记录类型获取一行数据
  SELECT employee_id, first_name, hire_date
  INTO v_emp_rec.emp_id, v_emp_rec.emp_name, v_emp_rec.hire_date
  FROM employees
  WHERE employee_id = 100;
  
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_rec.emp_name);
END;

在此示例中，employee_record是一个自定义记录类型，存储了员工的employee_id、first_name和hire_date，并通过SELECT INTO语句将数据存入记录。

基于表行的记录类型：
PL/SQL允许基于某个表的行定义记录类型，简化记录的声明：
```
DECLARE
  v_emp_rec employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp_rec FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_rec.first_name);
END;
```
在此示例中，v_emp_rec使用了employees%ROWTYPE，它自动匹配employees表中的列，使操作更简洁。

2.2 集合类型

集合是PL/SQL中用于存储同类型数据的结构。PL/SQL支持三种主要的集合类型：

嵌套表（Nested Table）：类似于动态数组，可以存储无序的元素，且可以动态扩展。
VARRAY（变长数组）：具有固定大小的数组，元素是有序的。
关联数组（索引表）：键值对结构，用作稀疏数组。

三、PL/SQL表与关联数组

3.1 PL/SQL表（嵌套表）

PL/SQL表（嵌套表）是一种灵活的集合类型，可以存储一组相同类型的数据。在PL/SQL中，可以使用嵌套表来处理多个数据值，尤其是在批量处理时非常有用。

嵌套表的定义与使用

DECLARE
  TYPE num_table IS TABLE OF NUMBER;
  v_numbers num_table := num_table(100, 200, 300);
BEGIN
  -- 访问嵌套表中的元素
  FOR i IN v_numbers.FIRST .. v_numbers.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Number: ' || v_numbers(i));
  END LOOP;
END;

在此示例中，定义了一个num_table类型的嵌套表，并将一些数字值存储在其中。通过FOR循环可以遍历集合中的所有元素。

3.2 关联数组

关联数组（Index-by Table）是键值对类型的集合。它使用数字或字符串作为索引，通过索引可以访问数组中的元素。

关联数组的定义与使用

DECLARE
  TYPE emp_salary_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_salaries emp_salary_table;
BEGIN
  -- 为关联数组赋值
  v_salaries(1001) := 5000;
  v_salaries(1002) := 6000;

  -- 访问关联数组中的值
  DBMS_OUTPUT.PUT_LINE('Salary of emp 1001: ' || v_salaries(1001));
END;

在此例子中，定义了一个关联数组emp_salary_table，并通过索引来存储和访问员工的薪资数据。关联数组非常适合需要根据键值快速查找数据的场景。

四、集合操作：BULK COLLECT与FORALL

4.1 BULK COLLECT

在PL/SQL中，BULK COLLECT用于将查询结果批量加载到集合中，从而减少SQL与PL/SQL之间的切换次数。这种方法非常适合大数据量的查询，能够显著提高性能。

使用BULK COLLECT的示例

DECLARE
  TYPE num_table IS TABLE OF NUMBER;
  v_emp_ids num_table;
BEGIN
  -- 使用BULK COLLECT将查询结果加载到集合中
  SELECT employee_id BULK COLLECT INTO v_emp_ids FROM employees WHERE department_id = 10;
  
  -- 遍历集合并输出结果
  FOR i IN v_emp_ids.FIRST .. v_emp_ids.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_ids(i));
  END LOOP;
END;

在此示例中，BULK COLLECT用于将员工ID批量加载到v_emp_ids集合中，从而减少逐条查询的开销。

4.2 FORALL

FORALL是一种PL/SQL的控制结构，用于批量执行INSERT、UPDATE或DELETE语句。与常规的FOR循环相比，FORALL可以显著提高批量DML操作的性能。

使用FORALL的示例

DECLARE
  TYPE num_table IS TABLE OF NUMBER;
  v_emp_ids num_table := num_table(1001, 1002, 1003);
BEGIN
  -- 使用FORALL进行批量更新
  FORALL i IN v_emp_ids.FIRST .. v_emp_ids.LAST
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_emp_ids(i);
    
  DBMS_OUTPUT.PUT_LINE('Salaries updated.');
END;