跳到内容

Oracle 数据库/PL/SQL

来自维基教科书，开放世界中的开放书籍

PL/SQL

[编辑 | 编辑源代码]

简介

[编辑 | 编辑源代码]

PL/SQL 代表 SQL 的过程语言扩展。它是 SQL 与编程语言的过程功能的结合，它通过将过程功能（如条件或循环语句）注入面向集合的 SQL 结构来增强 SQL 的功能。

PL/SQL 的优势

[编辑 | 编辑源代码]
  • 过程语言功能：PL/SQL 包含过程语言结构，如条件语句（if else 语句）和循环，如（FOR 循环）。
  • 块结构：PL/SQL 由代码块组成，这些代码块可以相互嵌套。每个块构成一个任务或逻辑模块的单元。PL/SQL 块可以存储在数据库中并重复使用。
  • 更好的性能：PL/SQL 引擎将多个 SQL 语句同时处理为单个块，从而减少网络流量。
  • 异常处理：PL/SQL 在 PL/SQL 程序执行期间有效地处理异常（或错误）。一旦捕获到异常，可以根据异常类型采取特定操作，也可以向用户显示错误消息。

限制

[编辑 | 编辑源代码]

PL/SQL 只能使用 SELECT、DML（INSERT、UPDATE、DELETE）和 TC（COMMIT、ROLLBACK、SAVEPOINT）语句。DDL（CREATE、ALTER、DROP）和 DCL（GRANT、REVOKE）不能直接使用。但是，任何 DDL/DCL 都可以从 PL/SQL 中执行，前提是它们嵌入在 EXECUTE IMMEDIATE 语句中。

基本结构

[编辑 | 编辑源代码]

每个 PL/SQL 程序都包含构成 PL/SQL 块的 SQL 和 PL/SQL 语句。PL/SQL 块包含三个部分

声明部分：此部分是可选的，它以保留关键字 DECLARE 开始。此部分用于声明任何占位符，如变量、常量、记录和游标，这些占位符用于在执行部分中操作数据。

执行部分：此部分是必需的，它以保留关键字 BEGIN 开始，并以 END 结束。此部分是编写程序逻辑以执行任何任务的地方。循环、条件语句和 SQL 语句等编程结构构成执行部分的一部分。

异常部分：此部分是可选的，它以保留关键字 EXCEPTION 开始。程序中的任何异常都可以在此部分处理，以便 PL/SQL 块正常终止。如果 PL/SQL 块包含无法处理的异常，则该块将以错误突然终止。

以上三个部分中的每个语句都必须以 ;（分号）结束。PL/SQL 块可以嵌套在其他 PL/SQL 块中。可以使用注释来记录代码。

PL/SQL 的外观如下。

/* multi-lines comments */
-- single line comments
DECLARE 
    Variable declaration
BEGIN 
    Program Execution 
EXCEPTION 
    Exception handling
END;

PL/SQL 占位符

[编辑 | 编辑源代码]

占位符是临时存储区域。占位符可以是变量、常量和记录中的任何一个。Oracle 定义占位符来临时存储数据，这些数据用于在 PL SQL 块执行期间操作数据。

根据要存储的数据类型，可以使用名称和数据类型来定义占位符。下面列出了一些用于定义占位符的数据类型。

Number(n,m), Char(n), Varchar2(n), Date, Long, Long Raw, Raw, Blob, Clob, Nclob, Bfile

存储值的占位符可以在 PL/SQL 块中更改。

PL/SQL 变量

[编辑 | 编辑源代码]

声明变量的通用语法为

variable_name datatype [NOT NULL := value ];
  • variable_name 是变量的名称。
  • datatype 是有效的 PL/SQL 数据类型。
  • NOT NULL 是变量上的可选规范。如果指定了 NOT NULL，则必须提供初始值。
  • value 或 DEFAULT value 也是可选规范，您可以在其中初始化变量。
  • 每个变量声明都是一个单独的语句，必须以分号结束。

以下示例声明了两个变量，其中一个是非空的。

DECLARE 
  emp_id varchar2(10);
  salary number(9,2) NOT NULL := 1000.00;

变量的值可以在 PL/SQL 块的执行或异常部分中更改。我们可以通过以下两种方式之一将值分配给变量。

1) 直接将值分配给变量。

variable_name:=  value;

2) 直接从数据库列中将值分配给变量。

SELECT column_name
INTO variable_name 
FROM table_name
[WHERE condition];

以下示例将获取 ID 为 '12345' 的员工的薪水，并将其显示在屏幕上。

DECLARE
  var_emp_id varchar2(10) = 'A12345'; 
  var_salary number(9,2);  
BEGIN 
  SELECT salary
  INTO var_salary
  FROM employee
  WHERE emp_id = var_emp_id;
  dbms_output.put_line(var_salary);
  dbms_output.put_line('Employee ' || var_emp_id || ' earns salary ' || var_salary); 
END; 
/

注意：斜杠 '/' 表示执行上面的 PL/SQL 块。

PL/SQL 记录

[编辑 | 编辑源代码]

记录是复合数据类型，它包含不同标量数据类型的组合，如 char、varchar、number 等。记录中的每个标量数据类型都保存一个值。记录可以存储表中一行的值。

TYPE record_name IS RECORD 
  (col_name_1 datatype, 
   col_name_2 table_name.column_name%type);

数据类型可以在声明表的过程中进行声明，例如 col_name_1。如果字段基于数据库表中的列，则可以将数据类型定义为 col_name_2。还可以使用 %type 方法声明变量和常量的类型。与 %type 类似，如果记录的所有字段都基于表的列，则可以使用 %rowtype 方法来声明它们。

record_name table_name%ROWTYPE;

例如

DECLARE 
  TYPE rec_employee IS RECORD 
   (emp_id          varchar2(10), 
    emp_last_name   employees.last_name%type, 
    emp_dept        employees.dept%type,
    salary          number(9,2)
   );

DECLARE 
  rec_employee employees%ROWTYPE;

将记录声明为 ROWTYPE 的优点：1) 无需为表中的所有列显式声明变量。2) 如果数据库表中的列规范发生了更改，则代码无需更新。

缺点：1) 当记录创建为 ROWTYPE 时，将为表中的所有列创建字段，并将使用内存为所有字段创建数据类型。

将值分配给记录与变量类似，您可以通过直接分配或通过 SELECT 语句将值分配给记录

record_name.col_name := value;

SELECT col_1, col_2 
INTO record_name.col_name_1, record_name.col_name_2 
FROM table_name 
[WHERE condition];

如果记录声明为 ROWTYPE，则可以使用 SELECT * 来分配值。

SELECT * INTO record_name
FROM table_name 
[WHERE condition];

可以按照以下语法检索记录的列值

var_name := record_name.col_name;

变量和记录的范围

[编辑 | 编辑源代码]

PL/SQL 允许在块中嵌套块，即外部块的执行部分可以包含内部块。外部块可访问的变量也可以被所有嵌套的内部块访问；但是，在内部块中声明的变量无法被外部块访问。

根据其声明，我们可以将变量分为两种类型。

  • 局部变量 - 这些变量是在内部块中声明的，外部块无法引用它们。
  • 全局变量 - 这些变量是在外部块中声明的，可以被它自身及其内部块引用。

在下面的示例中，在外部块中创建了两个变量，并将它们的乘积分配给在内部块中创建的第三个变量。变量 'var_num1' 和 'var_num2' 可以被块中的任何地方访问；但是，变量 'var_result' 是在内部块中声明的，因此无法在外部块中访问。

DECLARE
  var_num1 number;
  var_num2 number;
BEGIN 
  var_num1 := 100; 
  var_num2 := 200; 
  DECLARE 
    var_result number;
  BEGIN
    var_result := var_num1 * var_num2; 
  END;
  /* var_result is not accessible to here */ 
END; 
/

PL/SQL 常量

[编辑 | 编辑源代码]

顾名思义，常量是在 PL/SQL 块中使用的值，在整个程序中保持不变。常量是用户定义的文字值。您可以声明一个常量并使用它来代替实际值。

constant_name CONSTANT datatype := VALUE;

例如

DECLARE 
  comm_pct CONSTANT number(3) := 10;

您必须在声明常量时为其分配一个值。如果您稍后为常量分配一个值，Oracle 将提示异常。

PL/SQL 条件语句

[编辑 | 编辑源代码]

PL/SQL 支持编程语言特性，如条件语句、迭代语句。

条件语句的语法

IF condition_1 THEN 
 statement_1; 
 statement_2; 
[ELSIF condition_2 THEN 
 statement_3;] 
[ELSE 
 statement_4;] 
END IF;

注意：请注意关键字 ELSIF，它没有 'E' 在 'IF' 之前。

PL/SQL 迭代语句

[编辑 | 编辑源代码]

当您希望多次重复执行一个或多个语句时，使用迭代语句。PL/SQL 中有三种类型的循环

1. 简单循环 简单循环用于当一组语句至少要执行一次才能终止循环时。循环中必须指定 EXIT 条件，否则循环将进入无限次迭代。当 EXIT 条件满足时，进程将退出循环。

LOOP 
  statements; 
  EXIT; 
  {or EXIT WHEN condition;}
END LOOP;

注意：a) 在循环体之前初始化一个变量。b) 在循环中递增变量。c) 使用 EXIT WHEN 语句退出循环。如果您使用没有 WHEN 条件的 EXIT 语句，则循环中的语句只执行一次。

2. WHILE 循环 当一组语句必须在条件为真时执行时，使用 WHILE 循环。条件在每次迭代开始时评估。迭代将一直持续，直到条件变为假。

WHILE <condition> 
 LOOP statements; 
END LOOP;

注意：a) 在循环体之前初始化一个变量。b) 在循环中递增变量。c) EXIT WHEN 语句和 EXIT 语句可以在 while 循环中使用，但很少使用。

3. FOR 循环 FOR 循环用于执行一组语句预定的次数。迭代发生在给定的开始和结束整数值之间。计数器始终递增 1。当计数器达到结束整数值时，循环退出。

FOR counter IN start_val..end_val 
  LOOP statements; 
END LOOP;

注意：a) 计数器变量在声明部分隐式声明，因此不需要显式声明它。b) 计数器变量递增 1，不需要显式递增。c) EXIT WHEN 语句和 EXIT 语句可以在 FOR 循环中使用，但很少使用。

PL/SQL 游标

[编辑 | 编辑源代码]

游标包含有关 select 语句和它访问的数据行的信息。这个临时工作区用于存储从数据库检索的数据，并操作这些数据。一个游标可以包含多行，但一次只能处理一行。游标包含的一组行称为活动集。

PL/SQL 中有两种类型的游标

隐式游标

[编辑 | 编辑源代码]

当您执行 DML 语句（如 DELETE、INSERT、UPDATE 和 SELECT..INTO 语句）时，会创建隐式语句来处理这些语句。

Oracle 提供了一些称为隐式游标属性的属性，用于检查 DML 操作的状态。可用的游标属性有 %FOUND、%NOTFOUND、%ROWCOUNT 和 %ISOPEN。

例如，当您执行 INSERT、UPDATE 或 DELETE 语句时，游标属性会告诉我们是否影响了任何行以及影响了多少行。当在 PL/SQL 块中执行 SELECT... INTO 语句时，可以使用隐式游标属性来确定 SELECT 语句是否返回了任何行。当没有选择数据时，PL/SQL 会返回错误。

下表定义了每个属性的游标状态。

属性 返回值
SQL%FOUND 如果 DML 语句（如 INSERT、DELETE 和 UPDATE）影响了至少一行，并且如果 SELECT ….INTO 语句返回了至少一行，则返回值为 TRUE。

如果 DML 语句（如 INSERT、DELETE 和 UPDATE）未影响任何行，并且如果 SELECT ….INTO 语句未返回任何行，则返回值为 FALSE。

SQL%NOTFOUND 如果 DML 语句（如 INSERT、DELETE 和 UPDATE）影响了至少一行，并且如果 SELECT ….INTO 语句返回了至少一行，则返回值为 FALSE。

如果 DML 语句（如 INSERT、DELETE 和 UPDATE）未影响任何一行，并且如果 SELECT ….INTO 语句未返回任何行，则返回值为 TRUE。

SQL%ROWCOUNT 返回 DML 操作 INSERT、DELETE、UPDATE、SELECT 影响的行数
SQL%ISOPEN 始终返回 FALSE

使用隐式游标属性

DECLARE  var_rows number(5);
BEGIN
  UPDATE employee 
  SET salary = salary + 2000;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('None of the salaries where updated');
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
  END IF; 
END;

在上面的 PL/SQL 块中，更新了 'employee' 表中所有员工的薪水。如果没有任何员工的薪水被更新，我们将得到一条消息 'None of the salaries where updated'。否则，我们将得到一条消息，例如，'Salaries for 100 employees are updated'，如果 'employee' 表中有 100 行。

显式游标

[编辑 | 编辑源代码]

显式游标在 PL/SQL 块的声明部分定义。当您执行返回多行的 SELECT 语句时，必须创建它。即使游标存储多个记录，但一次只能处理一条记录，称为当前行。当您获取一行时，当前行位置会移动到下一行。

使用显式游标有四个步骤。

  • 在声明部分声明游标。
  • 在执行部分打开游标。
  • 在执行部分从游标中获取数据到 PL/SQL 变量或记录中。
  • 在结束 PL/SQL 块之前，在执行部分关闭游标。

声明

CURSOR cursor_name IS select_statement;

例如

DECLARE
   CURSOR cur_emp IS 
   SELECT * 
   FROM employees
   WHERE salary > 10000;

使用游标 当游标打开时，第一行成为当前行。当数据被获取时，它被复制到记录或变量中，逻辑指针移动到下一行，它成为当前行。在每个 fetch 语句上，指针都会移动到下一行。如果您希望在最后一行之后获取，程序将抛出一个错误。当游标中有多行时，我们可以使用循环和显式游标属性来获取所有记录。

OPEN cursor_name;
FETCH cursor_name INTO record_name|variable_list;
CLOSE cursor_name;

注意

  • 我们可以将游标中的行获取到 PL/SQL 记录或在 PL/SQL 块中创建的变量列表中。
  • 如果您将游标获取到 PL/SQL 记录中，则记录应该与游标具有相同的结构。
  • 如果您将游标获取到变量列表中，则变量应该在 fetch 语句中按照与游标中存在的列相同的顺序排列。
  • 当我们尝试打开一个在先前操作中没有关闭的游标时，它会抛出异常。
  • 当我们尝试在最后一次操作后获取游标时，它会抛出异常。

例如

DECLARE 
  rec_emp employees%rowtype;
  CURSOR cur_emp IS 
  SELECT *
  FROM employees
  WHERE salary > 10000; 
BEGIN 
  OPEN cur_emp; 
  FETCH cur_emp INTO rec_emp; 
    dbms_output.put_line (rec_emp.first_name || '  ' 
                       || rec_emp.last_name); 
  CLOSE emp_cur; 
END;

Oracle 提供了一些称为显式游标属性的属性，用于控制使用游标时的数据处理。我们使用这些属性来避免通过 OPEN、FETCH 和 CLOSE 语句访问游标时出现错误。

属性 返回值
Cursor_name%FOUND TRUE，如果 fetch 语句返回了至少一行。

FALSE，如果 fetch 语句没有返回任何行。

Cursor_name%NOTFOUND TRUE，如果 fetch 语句没有返回任何行。

FALSE，如果 fetch 语句返回了至少一行。

Cursor_name%ROWCOUNT fetch 语句获取的行数。

如果没有返回任何行，PL/SQL 语句将返回一个错误。

Cursor_name%ISOPEN TRUE，如果游标已在程序中打开。

FALSE，如果游标未在程序中打开。

带简单循环的游标

DECLARE 
  CURSOR cur_emp IS 
  SELECT first_name, last_name, salary FROM employees; 
  rec_emp cur_emp%rowtype; 
BEGIN 
  IF NOT cur_emp%ISOPEN THEN 
    OPEN cur_emp; 
  END IF; 
  LOOP 
    FETCH cur_emp INTO rec_emp; 
    EXIT WHEN cur_emp%NOTFOUND; 
    dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
      || ' ' ||cur_emp.salary); 
  END LOOP; 
END; 
/

游标属性 %ISOPEN 用于检查游标是否打开，如果条件为真，则程序不会再次打开游标。游标属性 %NOTFOUND 用于检查 fetch 是否返回了任何行。如果没有找到任何行，程序将退出。通常，当游标到达最后一行时，就不能再获取任何行了。

带 WHILE 循环的游标

DECLARE 
  CURSOR cur_emp IS 
  SELECT first_name, last_name, salary FROM employees; 
  rec_emp cur_emp%rowtype; 
BEGIN 
  IF NOT cur_emp%ISOPEN THEN 
    OPEN cur_emp; 
  END IF; 
  FETCH cur_emp INTO sales_rec;  
 WHILE cur_emp%FOUND THEN  
 LOOP 
   dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
   || ' ' ||cur_emp.salary); 
   FETCH cur_emp INTO sales_rec; 
 END LOOP; 
END; 
/

使用 %FOUND 评估第一个 fetch 语句是否返回了一行，如果为 TRUE，则程序将进入 while 循环。在循环内部，再次使用 fetch 语句处理下一行。如果 fetch 语句在 while 循环之前没有执行一次，则 while 条件将在第一次实例中返回 false，而 while 循环将被跳过。

带 FOR 循环的游标：使用 FOR 循环时，您不需要声明记录或变量来存储游标值，也不需要打开、获取和关闭游标。这些功能由 FOR 循环自动完成。

DECLARE 
 CURSOR cur_emp IS 
   SELECT first_name, last_name, salary FROM employees; 
 rec_emp cur_emp%rowtype; 
BEGIN 
  FOR rec_emp in cur_emp 
  LOOP  
    dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
    || ' ' ||cur_emp.salary);  
  END LOOP; 
END;
/

当 FOR 循环被处理时，将创建一个结构为 'cur_emp' 的记录 'rec_emp'，游标被打开，行被获取到记录 'rec_emp' 中，并且在处理完最后一行后游标被关闭。通过使用 FOR 循环，您可以减少程序中的行数。

PL/SQL 异常处理

[编辑 | 编辑源代码]

PL/SQL 过程

[编辑 | 编辑源代码]

从 PL/SQL 块调用存储过程时，只需使用存储过程名称进行调用。如果在存储过程名称前加上 “EXECUTE” 关键字，则会收到错误信息。

my_sproc;

EXECUTE my_sproc;

PLS-00103: Encountered the symbol "my_sproc" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "my_sproc" to continue.

EXECUTE IMMEDIATE my_sproc;

PLS-00222: no function with name exists in this scope

PL/SQL 函数

[编辑 | 编辑源代码]
 一位 Wikibookian 建议将本书或本章与 Oracle 编程/SQL 速查表 合并。
请在 讨论页面 上讨论是否应该进行合并。
本节内容比较简短。
您可以通过 扩展 来帮助 Wikibooks。

参数 - 过程，函数

[编辑 | 编辑源代码]
 本节内容比较简短。
您可以通过 扩展 来帮助 Wikibooks。

PL/SQL 触发器

[编辑 | 编辑源代码]
 本节内容比较简短。
您可以通过 扩展 来帮助 Wikibooks。
检索自 "https://wikibooks.cn/w/index.php?title=Oracle_Database/PL/SQL&oldid=4429039"
华夏公益教科书