PL/SQL每日一题

PL/SQL每日一题(一)

以下标识符哪些有效、哪些无效？为什么？

a. my_variable2
b. my-variable2
c. my^variable
d. MyVariable
e. my_variable_for_many_many_of_usages
f. 123myvariable
g. “123myvariable”

各变量的有效性及原因说明

a. 有效。my_variable2 以字母开头，长度不足31个字符，且仅包含字母、数字以及 $、#、_（下划线）这几类合法字符。

b. 无效。my-variable2 中包含短横线（-），该字符不属于标识符允许的合法字符。

c. 无效。my^variable 中包含特殊字符 ^，此类字符不允许出现在标识符中。

d. 有效。MyVariable 以字母开头，长度不足31个字符，且仅包含字母、数字以及 $、#、_ 这几类合法字符（注：PL/SQL 标识符默认不区分大小写，此处大小写混合不影响有效性）。

e. 无效。my_variable_for_many_many_of_usages 的长度超过了30个字符（PL/SQL 标识符的最大允许长度为30个字符）。

f. 无效。123myvariable 以数字开头，而标识符的首字符必须是字母、$、# 或 _，不能以数字开头。

g. 有效。"123myvariable" 被双引号包裹。在 PL/SQL 中，若用双引号将标识符括起（注意：与两个连续的单引号 '' 完全不同），则除“最大长度30个字符”这一规则外，标识符的其他所有限制规则均会失效（例如允许以数字开头、包含特殊字符等）。

PL/SQL每日一题(二)

以下代码块哪些能编译通过，哪些编译失败？原因是什么？

a.

1
2
3
4
5
6

DECLARE
    lastdate DATE;
    lastDate NUMBER;
BEGIN
    NULL;
END;

b.

1
2
3
4
5
6
7

DECLARE
    lastdate DATE := sysdate;    
    lastDate NUMBER := 50;
BEGIN
    dbms_output.put_line(lastdate);
    dbms_output.put_line(lastDate);
END;

奇怪的是，(a能编译通过)，而 (b编译失败)，并会报出如下错误信息：
PLS-00371: at most one declaration for 'LASTDATE' is permitted（最多只允许对“LASTDATE”进行一次声明）

PL/SQL 是一门不区分大小写的语言（字符串常量的内容除外）。因此，在上述两个代码块中，你本质上都是在尝试声明两个同名变量——这原本是不被允许的。

但实际情况是，只有当你尝试使用其中一个变量时，编译器才会拒绝这种重复声明的行为；如果只是声明而不使用（如代码块a），编译器则不会报错。

PL/SQL每日一题(三)

请解释以下每条声明语句存在的问题

1
2
3
4
5
6

DECLARE
    INTEGER year_count;
    VARCHAR2(100) company_name, employee_name;
    delimiters VARCHAR2;
    curr_year CONSTANT INTEGER;
    invalid_date EXCEPTION := VALUE_ERROR;

1. INTEGER year_count;

问题：数据类型（INTEGER）的位置错误。
原因：在 PL/SQL 变量声明中，必须遵循“标识符（变量名）在前，数据类型在后”的语法规则，正确格式应为 year_count INTEGER;。

2. VARCHAR2(100) company_name, employee_name;

问题：存在两处错误：

• 数据类型（VARCHAR2(100)）位置错误，应放在变量名之后；
• 试图在一条声明语句中定义两个变量，不符合语法规范。
  原因：PL/SQL 要求“每条逻辑声明语句只能定义一个变量”，且需遵循“变量名+数据类型”的顺序。正确格式应为：
  company_name VARCHAR2(100);
employee_name VARCHAR2(100);

3. delimiters VARCHAR2;

问题：VARCHAR2 类型声明缺少长度约束。
原因：VARCHAR2 是变长字符串类型，声明时必须指定最大字符长度（如 VARCHAR2(20)），仅当声明参数（非变量）时可省略长度约束。正确格式应为 delimiters VARCHAR2(XX);（XX 为具体长度，如 10）。

4. curr_year CONSTANT INTEGER;

问题：常量（CONSTANT）声明缺少默认值。
原因：CONSTANT 用于定义常量，其值在声明时必须确定且后续不可修改，因此声明时必须通过 := 或 DEFAULT 指定初始值。正确格式应为 curr_year CONSTANT INTEGER := 2024;（2024 为示例初始值）。

5. invalid_date EXCEPTION := VALUE_ERROR;

问题：试图为自定义异常赋值，不符合异常声明规则。
原因：EXCEPTION 用于定义自定义异常，其作用是标识异常类型，而非存储值，因此不能通过 := 赋予初始值（包括系统预定义异常 VALUE_ERROR）。正确格式应为 invalid_date EXCEPTION;（如需关联系统异常，需通过 RAISE_APPLICATION_ERROR 等方式在逻辑中处理，而非声明时赋值）。

PL/SQL每日一题(四)

存储以下数据项时，最适合使用哪种数据类型？

• ‘This is a test’
• Values from 1 to 10
• 5.987
• ‘This is a test '
• A string that is always nine characters long
• January 10, 2000
• A binary file
• TRUE or FALSE
• The internal identifier of a row in a table

a. ‘This is a test’
VARCHAR2 or CHAR
（字符串“这是一个测试”——适合用 VARCHAR2 或 CHAR 类型）

b. Values from 1 to 10
PLS_INTEGER (best performance), INTEGER, BINARY_INTEGER, NATURAL, POSITIVE
（1到10之间的值——适合用 PLS_INTEGER（性能最优）、INTEGER、BINARY_INTEGER、NATURAL 或 POSITIVE 类型）

c. 5.987
NUMBER
（小数5.987——适合用 NUMBER 类型）

d. ‘This is a test ’
CHAR; you need a fixed-length declaration to preserve the spaces
（末尾带空格的字符串“这是一个测试 ”——适合用 CHAR 类型；需通过固定长度声明保留空格）

e. A string that is always nine characters long
CHAR
（始终为9个字符长度的字符串——适合用 CHAR 类型）

f. January 10, 2000
DATE
（2000年1月10日——适合用 DATE 类型）

g. A binary file
BFILE (after Oracle8); prior to Oracle8, LONG or LONG RAW
（二进制文件——Oracle8 及之后版本适合用 BFILE 类型；Oracle8 之前版本适合用 LONG 或 LONG RAW 类型）

h. TRUE or FALSE
BOOLEAN
（布尔值 TRUE 或 FALSE——适合用 BOOLEAN 类型）

i. The internal identifier of a row in a table
ROWID
（表中某一行的内部标识符——适合用 ROWID 类型）

PL/SQL每日一题(五)

请选择适合实现以下需求的循环类型（FOR 循环、WHILE 循环、简单循环）

在贷款处理周期的20年中，每年都要计算指定客户的未偿还贷款余额。如果该客户是优先客户，则计算12年后停止。

初看之下，你可能会认为 FOR 循环是最适合的（因为需求中提到“每年都要……”）。但在这个场景下，使用 FOR 循环是错误的——因为需求中存在条件退出逻辑（在特定条件下，即客户为优先客户时，需在12年后停止循环）。

只有当你需要无条件遍历从下限到上限的所有值时，才应该使用 FOR 循环。因此，要满足该需求，应使用简单循环（simple loop）或 WHILE 循环。

简单循环（Simple Loop） 是一种最基础、最灵活的循环结构，它没有预设的 “循环次数上限” 或 “初始条件判断”，仅通过循环体内部的 EXIT 或 EXIT WHEN 语句主动控制循环的退出时机。

1
2
3
4

LOOP  -- 循环开始标记（无任何条件判断）
    -- 循环体：需要重复执行的逻辑（如计算贷款余额）
    EXIT WHEN 条件;  -- 关键：当“条件为真”时，立即退出循环
END LOOP;  -- 循环结束标记

PL/SQL每日一题(六)

以下循环存在什么问题（或有哪些可改进之处）？如何修改循环以优化代码？

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 打开游标（emp_cur 为已定义的游标）
OPEN emp_cur;

-- 第一次从游标中获取一条记录，存入 emp_rec（emp_rec 为与游标结构匹配的记录类型）
FETCH emp_cur INTO emp_rec;

-- 当游标中存在可获取的记录时（%FOUND 为 TRUE），进入循环
WHILE emp_cur%FOUND
LOOP
    -- 调用 calc_totals 过程，传入当前记录的 salary 字段作为参数
    calc_totals (emp_rec.salary);
    
    -- 再次从游标中获取下一条记录，更新 emp_rec
    FETCH emp_cur INTO emp_rec;
    
    -- 条件退出：若当前获取的记录中 salary 大于 100000，则立即退出循环
    EXIT WHEN emp_rec.salary > 100000;
END LOOP;

-- 循环结束后，关闭游标释放资源
CLOSE emp_cur;

问题分析

这个循环中存在两处 FETCH 语句，导致代码冗余。这看似影响不大，但存在潜在问题：

• 如果循环条件变得更复杂，或者在计算总和前需要执行多个函数转换数据，就必须在两处 FETCH 之后重复编写这些逻辑；
• 循环中存在两种终止条件（WHILE 子句的 emp_cur%FOUND 和循环内部的 EXIT WHEN），不符合 WHILE 循环的设计原则——WHILE 循环应仅依赖边界条件终止，而非在循环内部使用 EXIT。

改进方案

更好的做法是使用简单循环（simple loop），并在循环内部只保留一个 FETCH 语句，将所有终止条件集中处理：

1
2
3
4
5
6
7
8
9

OPEN emp_cur;
LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN 
        emp_cur%NOTFOUND OR  -- 游标无更多数据时退出
        emp_rec.salary > 100000;  -- 薪资超过100000时退出
    calc_totals (emp_rec.salary);
END LOOP;
CLOSE emp_cur;

补充说明

本例不适合使用游标 FOR 循环，因为存在条件退出逻辑（当薪资 > 100000 时终止）。游标 FOR 循环仅适用于无条件检索并处理游标返回的每一行数据的场景。

PL/SQL每日一题(七)

以下循环存在什么问题（或有哪些可改进之处）？如何修改循环以优化代码？

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

DECLARE
    -- 声明游标，查询已结账的宠物入住记录
    CURSOR checked_out_cur IS
        SELECT pet_id, name, checkout_date
        FROM occupancy
        WHERE checkout_date IS NOT NULL;
BEGIN
    -- 使用游标FOR循环遍历查询结果
    FOR checked_out_rec IN checked_out_cur
    LOOP
        -- 逐条插入记录到历史表
        INSERT INTO occupancy_history (
                            pet_id,
                            name,
                            checkout_date)
        VALUES (
                            checked_out_rec.pet_id,
                            checked_out_rec.name,
                            checked_out_rec.checkout_date);
    END LOOP;
END;

问题分析：

此处完全不需要使用PL/SQL循环。因为循环中仅执行简单的插入操作，且没有为INSERT语句添加异常处理，逐条插入不会带来任何好处，反而会降低性能。

改进方案：

为获得更好的性能，应直接使用SQL语句完成批量插入，避免PL/SQL循环的开销：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 直接通过 SELECT 语句批量插入数据：
-- 从 occupancy 表筛选出 checkout_date 非空的记录，批量插入到 occupancy_history 表
INSERT INTO occupancy_history (
    pet_id,        -- 目标表字段1：宠物ID
    name,          -- 目标表字段2：宠物名称
    checkout_date  -- 目标表字段3：退房日期
)
SELECT 
    pet_id,        -- 源表字段1：与目标表 pet_id 对应
    name,          -- 源表字段2：与目标表 name 对应
    checkout_date  -- 源表字段3：与目标表 checkout_date 对应（筛选非空记录）
FROM occupancy  -- 数据来源表：occupancy
WHERE checkout_date IS NOT NULL;  -- 筛选条件：仅取退房日期非空的记录

这种方式利用了SQL的批量处理能力，执行效率远高于PL/SQL循环逐条插入。

PL/SQL每日一题(八)

编写一段代码，打印出1到100之间所有能被3整除的数字。进阶要求：不使用MOD函数

以下是解决方案：

a.

1
2
3
4
5
6
7
8
9

BEGIN
    FOR indx IN 1..100
    LOOP
        IF MOD(indx, 3) = 0  -- 使用MOD函数判断是否能被3整除
        THEN
            DBMS_OUTPUT.PUT_LINE(indx);
        END IF;
    END LOOP;
END;

b.

1
2
3
4
5
6
7
8
9

DECLARE
    indx INTEGER := 3;  -- 从3开始，直接取能被3整除的数字
BEGIN
    WHILE indx < 100
    LOOP
        DBMS_OUTPUT.PUT_LINE(indx);
        indx := indx + 3;  -- 每次加3，确保下一个数字仍能被3整除
    END LOOP;
END;

PL/SQL每日一题(九)

以下哪个函数可以调用以获取最近发生错误的文本描述？

a. SHOWERR
b. SQLERRM
c. SQL_ERROR_MESSAGE

内置函数 SQLERRM（选项b） 用于显示特定错误对应的错误文本。

• 若调用时不传入任何参数，它会返回“最近触发的错误”的描述；
• 若传入错误代码作为参数，它会返回该错误代码对应的描述，示例如下：

1
2

SQL> EXEC DBMS_OUTPUT.PUT_LINE (SQLERRM (-1855))
ORA-01855: 需要 AM/A.M. 或 PM/P.M.

PL/SQL每日一题(十)

除了SQLERRM，还有哪些函数可以获取错误信息？

除了SQLERRM，还可以使用SQLCODE函数获取错误代码。SQLCODE返回与最近发生的错误相关联的数值错误代码，而SQLERRM返回对应的错误消息文本。

修改以下代码块，使其能够捕获NO_DATA_FOUND异常并显示当前错误的描述

1
2
3
4
5
6
7
8
9

DECLARE
    my_flavor ice_cream.fav_flavor%TYPE;
BEGIN
    SELECT fav_flavor
    INTO my_flavor
    FROM ice_cream
    WHERE name = USER;
    DBMS_OUTPUT.PUT_LINE('I love ' || my_flavor || '!');
END;

解决此问题需要

1. 在代码块末尾添加异常处理部分
2. 包含NO_DATA_FOUND异常的处理程序
3. 调用SQLERRM函数检索错误消息

修改后的代码如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DECLARE
    my_flavor ice_cream.fav_flavor%TYPE;
BEGIN
    SELECT fav_flavor
    INTO my_flavor
    FROM ice_cream
    WHERE name = USER;
    DBMS_OUTPUT.PUT_LINE('I love ' || my_flavor || '!');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

PL/SQL每日一题(十一)

以下哪些命名异常是由PL/SQL定义的？

a. DATA_NOT_FOUND
b. VALUE_ERROR
c. NO_DATA_FOUND
d. DIVIDE_BY_ZERO
e. INVALID_NUMBER
f. TOO_MANY_ROWS
g. DUP_KEY_IN_INDEX
h. CURSOR_OPEN
i. VALUE_TOO_LARGE

以下异常是Oracle在STANDARD包中预定义的：
VALUE_ERROR、NO_DATA_FOUND、INVALID_NUMBER、TOO_MANY_ROWS

以下标识符并非预定义异常的名称（正确名称如下）：

• DATA_NOT_FOUND（正确名称应为NO_DATA_FOUND）
• DIVIDE_BY_ZERO（正确名称应为ZERO_DIVIDE）
• DUP_KEY_IN_INDEX（正确名称应为DUP_VAL_ON_INDEX）
• CURSOR_OPEN（正确名称应为CURSOR_ALREADY_OPEN）
• VALUE_TOO_LARGE（正确名称应为VALUE_ERROR）

PL/SQL每日一题(十二)

如何在PL/SQL代码中处理这些命名异常？

可以提供什么特殊的异常处理器来捕获代码块中引发的任何错误？

在异常处理块中，可以通过异常名称来捕获特定异常，也可以使用一个名为OTHERS的通用处理器处理器来捕获所有异常。

如果声明了WHEN OTHERS处理器，它必须是异常块中指定的最后一个处理器。代码块中引发的任何异常（且未被其他异常处理器捕获的）都会被OTHERS处理器捕获。

示例如下：

1
2
3
4
5

-- 异常处理块：捕获所有未被显式处理的异常
EXCEPTION
    WHEN OTHERS THEN  -- OTHERS 表示捕获所有类型的异常（兜底处理）
        log_error(SQLCODE);  -- 调用 log_error 过程，传入当前异常的错误代码（SQLCODE）
END;  -- 结束异常处理块及对应的程序单元（如存储过程、函数、PL/SQL 块）

PL/SQL每日一题(十三)

以下关于PL/SQL记录（record）的陈述哪些是正确的，哪些是错误的？

a. 记录是一种标量数据类型。
b. 记录是一种复合数据类型，因为可以将多个信息与记录相关联。
c. 记录包含一段声音的录音，用于存储在Oracle数据库中。
d. 可以定义一个与游标、表或表空间具有相同结构的记录。
e. 可以定义一个记录来存储任意组合的有效PL/SQL标量数据结构。

各陈述的判断如下：
a. 错误。记录不是标量数据类型（标量类型仅包含单一信息）。记录的核心作用是包含多个信息：它是一种复合数据类型，其结构类似于数据库表中的一行。

b. 正确。

c. 错误。记录并非专门设计用于存储声音，但在Oracle8及以上版本中，你可以轻松定义一个包含LOB（大对象）字段的记录来存储声音文件。

d. 错误。基于表空间定义记录是没有意义的。不过，你可以定义一个类似于表中的行或游标提取的行的记录。

e. 正确。

PL/SQL每日一题(十四)

以下哪行代码展示了引用记录（record）中名为“favorite_flavor”的字符串字段的正确方法？该记录名为“ice_cream”

a. ice_cream (favorite_flavor)
b. ice_cream-favorite_flavor
c. ice_cream.favorite_flavor
d. favorite_flavor.ice_cream

只有选项（c）所示的格式（称为点表示法）可以引用记录中的字段。
通常，引用记录中的单个字段的格式为 record_name.field_name（记录名.字段名）。
引用字段时，必须始终使用字段的完全限定名。

PL/SQL每日一题(十五)

以下关于基于表的记录（table-based record）的陈述哪些是正确的，哪些是错误的？

a. 基于表的记录包含表中每个非数字列对应的字段。
b. 可以使用%TYPE或%ROWTYPE属性定义基于表的记录。
c. 基于表的记录中的每个字段必须与数据库表中对应列的名称和类型相同。
d. 可以基于数据库表或视图定义基于表的记录。
e. 声明基于表的记录时，可以使用列别名更改记录字段的名称。
f. 大量使用基于表的记录会降低应用程序速度，因为引擎必须在数据字典中查找表结构。
g. 基于表的记录中字段的数据类型可以是任何有效的PL/SQL数据类型。

各陈述的判断如下：
a. 错误。基于表的记录的定义来源于表的结构：每个字段与关联表中每一列（不仅是非数字字段）具有相同的名称和数据类型。定义基于表的记录时，需使用%ROWTYPE属性声明（%TYPE仅用于标量变量声明）。
假设emp表定义如下：

1
2
3
4
5

CREATE TABLE emp (
    empno NUMBER,
    ename VARCHAR2(30),
    hiredate DATE
);

b. 错误。要定义一个基于emp表的名为emp_rec的记录，需使用以下声明：

1
2

DECLARE
    emp_rec emp%ROWTYPE;

c. 正确。

d. 正确。

e. 错误。上述emp_rec包含三个字段：empno、ename和hiredate，每个字段的数据类型与emp表中对应列一致。除非基于游标声明记录，否则不能通过列别名更改字段名称。

f. 错误。<表名>%ROWTYPE引用的解析在编译时进行，不会以任何方式影响代码的运行时性能。

g. 错误。基于表的记录中字段的数据类型只能是有效的SQL数据类型。例如，不能在基于表的记录中包含BOOLEAN类型的字段。

小结

在数据库时代，特别是Oracle数据库时代，PL/SQL已经成为了数据库应用程序开发的标准语言。