PL SQL常见面试题和答案合集：求职面试必备

2021年12月12日22:16:53 发表评论 2,455 次浏览

PL/SQL 代表 SQL（结构化查询语言）的过程语言扩展。它是由 Oracle 创建的，目的是克服 SQL 的缺点，以便以全面的方式更轻松地构建和处理关键应用程序。
以下是 SQL 的缺点：

  • SQL 中没有提供决策、循环和分支。
  • 由于 SQL 语句同时传递到 Oracle 引擎，因此由于流量增加的性质，执行速度会降低。
  • 操作数据时没有错误检查功能。

PL/SQL 的引入是为了通过保留 SQL 的强大功能并将其与过程语句相结合来克服上述缺点。它是作为一种块结构语言开发的，块的语句被传递到 oracle 引擎，这有助于由于流量的减少而提高处理速度。

基础的PL SQL常见面试题和答案合集

1、PL/SQL的特点是什么？

PL SQL面试题解析：以下是PL/SQL的特点：

  • PL/SQL 利用其过程性质提供了决策、循环和分支的特性。
  • 通过使用 PL/SQL 的单个命令，可以在一个块中处理多个查询。
  • PL/SQL 代码可以被应用程序重用，因为它们可以作为 PL/SQL 单元（如函数、过程、包、触发器和类型）进行分组和存储在数据库中。
  • PL/SQL 通过使用异常处理块来支持异常处理。
  • 除了异常处理，PL/SQL 还支持数据操作前的错误检查和数据验证。
  • 使用 PL/SQL 开发的应用程序可以跨计算机硬件或具有 Oracle 引擎的操作系统移植。

2、PL SQL常见面试题有哪些：你对PL/SQL表的理解是什么？

  • PL/SQL 表只不过是建模为数据库表的表类型的对象。它们是一种提供数组的方法，这些数组只不过是内存中的临时表，以加快处理速度。
  • 这些表对于移动批量数据很有用，从而简化了过程。

3.解释PL/SQL遵循的基本结构？

  • PL/SQL 的基本结构遵循 BLOCK 结构。每个 PL/SQL 代码包含构成 PL/SQL 块的 SQL 和 PL/SQL 语句。
  • 每个 PL/SQL 块由 3 个部分组成：
    • 可选的声明部分
    • 强制执行部分
    • 可选的异常处理部分
[DECLARE]
--declaration statements (optional)
BEGIN
--execution statements
[EXCEPTION]
--exception handling statements (optional)
END;

4. 什么是 PL/SQL 游标？

  • PL/SQL 游标只不过是指向包含 SQL 语句和语句处理信息的内存区域的指针。该内存区域称为上下文区域。这个特殊区域利用称为游标的特殊功能来检索和处理多行。
  • 简而言之，游标从数据库中选择多行，这些选定的行在程序中单独处理。
  • 有两种类型的游标：
    • 隐式光标：
      • Oracle 在运行任何命令时自动创建一个游标 - SELECT INTO、INSERT、DELETE 或 UPDATE 隐式。
      • 这些游标的执行周期由 Oracle 内部处理，并通过使用游标属性 - ROWCOUNT、ISOPEN、FOUND、NOTFOUND 返回游标的信息和状态。
    • 显式光标：
      • 此游标是在声明块中显式声明的 SELECT 语句。
      • 程序员必须控制这些游标的执行周期，从 OPEN 到 FETCH 再到关闭。
      • 执行 SQL 语句时的执行周期由 Oracle 定义，并为其关联一个游标。
  • 显式游标执行周期：
    • 由于定义我们自己的执行周期的灵活性，在许多情况下使用显式游标。下图表示显式游标的执行流程：
  • 游标声明：
    • 使用显式游标的第一步是它的声明。
    • 声明可以在包或块中完成。
    • 语法： CURSOR cursor_name IS query; 其中 cursor_name 是游标的名称，查询是从任何表中获取数据的查询。
  • 打开光标：
    • 在从游标中获取行的过程之前，必须打开游标。
    • 打开游标的语法：OPEN cursor_name;
    • 当游标打开时，查询和绑定变量由 Oracle 解析并执行 SQL 语句。
    • 执行计划由 Oracle 确定，结果集是在将游标参数和宿主变量关联并发布后确定的，游标设置为指向结果集的第一行。
  • 从游标中获取：
    • FETCH 语句用于将当前行的内容放入变量中。
    • 句法： FETCH cursor_name INTO variable_list;
    • 为了获取结果集的所有行，需要获取每一行。
  • 关闭光标：
    • 获取所有行后，需要使用 CLOSE 语句关闭游标。
    • 语法：CLOSE cursor_name;
    • 这些指令告诉 Oracle 释放分配给游标的内存。
      • 在过程或匿名块中声明的游标在执行后默认关闭。
      • 包中声明的游标需要明确关闭，因为范围是全局的。
      • 关闭未打开的游标将导致 INVALID_CURSOR 异常。

5、游标中的WHERE CURRENT OF有什么用？

  • 我们在从显式游标引用当前行时使用此子句。该子句允许在不显式引用行 ID 的情况下应用当前正在考虑的行的更新和删除。
  • 句法：
    UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name

6. 如何为未命名的 PL/SQL 异常块分配名称？

  • 这可以通过使用名为EXCEPTION_INIT 的Pragma 来完成。
  • 这为程序员提供了灵活性，可以通过在编译期间覆盖预定义的消息来指示编译器根据业务逻辑提供自定义错误消息。
  • 句法：
DECLARE 
  exception_name EXCEPTION; 
  PRAGMA EXCEPTION_INIT (exception_name, error_code); 
BEGIN 
// PL/SQL Logic
EXCEPTION
 WHEN exception_name THEN
    // Steps to handle exception
END;

7. 什么是触发器？命名一些可以使用“触发器”的实例。

  • 顾名思义，“触发器”的意思是“激活”某物。在 PL/SQL 的情况下，触发器是一个存储过程，它指定在执行与数据库相关的事件时数据库必须采取的操作。
  • 句法：
TRIGGER trigger_name
 trigger_event
 [ restrictions ]
BEGIN
 actions_of_trigger;
END;

在上面的语法中，如果trigger_name触发器处于启用状态，则trigger_event导致数据库actions_of_trigger在restrictionsTRUE 或不可用时触发。

  • 它们主要用于以下场景：
    • 为了保持复杂的完整性约束。
    • 用于审计任何表信息。
    • 每当对表进行更改时，如果我们需要在更改完成后向其他操作发出信号，那么我们使用触发器。
    • 为了执行复杂的业务规则。
    • 它还可用于防止无效交易。
  • 你可以参考https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm了解有关触发器的更多信息。

8. DECLARE 块何时成为强制性的？

  • 该语句由 PL/SQL 的匿名块使用，例如非存储过程和独立过程。当它们被使用时，该语句应该出现在独立文件中。

9. 如何在 PL/SQL 代码中编写注释？

  • 注释是那些对功能没有影响的句子，用于增强代码的可读性。它们有两种类型：
    • 单行注释：这可以通过使用符号--并在其旁边写下我们想要提及的注释来创建。
    • 多行注释：这些注释可以在多行中指定，语法如下/* comment information */
  • 例子：
SET SERVEROUTPUT ON; 
DECLARE 
 
-- Hi There! I am a single line comment.
var_name varchar2(40) := 'I love PL/SQL' ;  
BEGIN 
 /* 
 Hi! I am a multi line
 comment. I span across
 multiple lines
 */
dbms_output.put_line(var_name);
END; 
/
Output:
I love PL/SQL

10.触发器中WHEN子句的目的是什么？

  • WHEN 子句指定触发器必须被触发的条件。

中级PL SQL常见面试题和答案合集

11.你能解释一下PL/SQL的执行架构吗？

PL SQL面试题解析：PL/SQL 引擎完成 PL/SQL 块和程序的编译和执行过程，并且只有安装在 Oracle 服务器或任何支持 Oracle 的应用程序工具（如 Oracle Forms）上才能工作。

  • PL/SQL 是 Oracle RDBMS 的组成部分之一，重要的是要知道大多数 Oracle 应用程序是使用客户端-服务器架构开发的。Oracle 数据库构成服务器端，对数据库的请求构成客户端的一部分。
  • 所以基于以上事实以及PL/SQL 不是一种独立的编程语言这一事实​​，我们必须意识到PL/SQL 引擎可以驻留在客户端环境或服务器环境中。这使得在服务器端和客户端应用程序之间移动 PL/SQL 模块和子程序变得容易。
  • 基于下图所示的架构，我们可以了解到PL/SQL引擎在这个过程中扮演着重要的角色，执行PL/SQL语句，遇到SQL语句，就会被送到SQL Statement Processor。
  • 情况 1： PL/SQL 引擎在服务器上：在这种情况下，整个 PL/SQL 块被传递到 Oracle 服务器上的 PL/SQL 引擎，然后进行处理并发送响应。
  • 案例 2：PL/SQL 引擎在客户端：这里引擎位于 Oracle Developer 工具中，PL/SQL 语句的处理在客户端完成。
    • 如果PL/SQL 块中有任何SQL 语句，则将它们发送到Oracle 服务器进行SQL 处理。
    • 当没有 SQL 语句时，整个块处理发生在客户端。

12、PL SQL常见面试题有哪些：为什么要使用SYSDATE和USER关键字？

  • SYSDATE：
    • 此关键字返回本地数据库服务器上的当前时间和日期。
    • 语法是 SYSDATE。
    • 为了提取部分日期，我们在 SYSDATE 上使用 TO_CHAR 函数并指定我们需要的格式。
    • 用法：
      • SELECT SYSDATE FROM dual;
      • SELECT id, TO_CHAR(SYSDATE, 'yyyy/mm/dd') from InterviewBitEmployeeTable where customer_id < 200;
  • USER：
    • 此关键字返回当前会话的用户 ID。
    • 用法：
      • SELECT USER FROM dual;

13、隐式游标和显式游标的区别。

隐式光标 显式光标
当查询返回单行值时使用隐式游标。当子查询返回多行时，使用显式游标。这些行称为活动集。
这用于所有 DML 操作，如 DECLARE、OPEN、FETCH、CLOSE。这用于处理多行 SELECT 语句。
NO_DATA_FOUND 异常在这里处理。此处无法处理 NO_DATA_FOUND。

14.区分SQL和PL/SQL。

SQLPL/SQL
SQL 是一种自然语言，用于交互式处理数据库中的数据。PL/SQL 是 SQL 的过程扩展。
SQL 中不允许进行决策和循环。PL/SQL 支持过程语言的所有特性，例如条件和循环语句。
所有 SQL 语句都由数据库服务器一次执行，这就是为什么它成为一个耗时的过程。PL/SQL 语句一次执行一个块，从而减少网络流量。
SQL 中没有错误处理机制。这支持错误处理机制。

15. %TYPE 和 %ROWTYPE 数据类型在 PL/SQL 中的重要性是什么？

  • %TYPE：此声明用于通过提供任何变量、列或常量的数据类型来进行锚定。在声明与表列具有相同数据类型的变量时，它很有用。
    • 考虑声明一个名为 name 的变量的示例，该变量ib_employeeid的数据类型和大小与employeeidtable中的列的数据类型和大小相同ib_employee。 
      语法是：ib_employeeid ib_employee.employeeid%TYPE;
  • %ROWTYPE：用于声明与表中行具有相同数据类型和大小的变量。表的行称为记录，其字段与表中定义的列具有相同的数据类型和名称。
    • 例如：为了在名为ib_emprecord的表中声明一个以存储整行命名的记录ib_employee，语法是：
      ib_emprecord ib_employee%ROWTYPE;

16. 可用于操作字符数据的各种函数有哪些？

  • 用于操作字符数据的函数称为字符串函数。
    • LEFT：此函数从字符串的左侧返回指定数量的字符。
      • 语法：LEFT(string_value, numberOfCharacters)。
      • 例如，LEFT('InterviewBit', 9) 将返回'Interview'。
    • RIGHT：此函数从字符串的右侧返回定义的字符数。
      • 语法：RIGHT(string_value, numberOfCharacters)
      • 例如，RIGHT('InterviewBit',3) 将返回 'Bit'。
    • SUBSTRING：此函数将从指定的起始位置通过从字符串的任何部分定义的字符数选择数据。
      • 语法：SUBSTRING(string_value, start_position, numberOfCharacters)
      • 例如，SUBSTRING('InterviewBit',2,4) 将返回 'terv'。
    • LTRIM：此函数将修剪字符串左侧的所有空格。
      • 语法：LTRIM(string_value)
      • 例如，LTRIM('InterviewBit') 将返回'InterviewBit'。
    • RTRIM：此函数将修剪字符串右侧的所有空格。
      • 语法：RTRIM(string_value)
      • 例如，RTRIM('InterviewBit') 将返回'InterviewBit'。
    • UPPER：该函数用于将字符串中的所有字符转换为大写。
      • 语法：UPPER(string_variable)
      • 例如，UPPER('interviewBit') 将返回 'INTERVIEWBIT'。
    • LOWER：该函数用于将字符串的所有字符转换为小写。
      • 语法：LOWER(string_variable)
      • 例如，LOWER('INterviewBit') 将返回'interviewbit'。

17. PL/SQL 中的 ROLLBACK 和 ROLLBACK TO 语句有什么区别？

  • ROLLBACK 命令用于回滚从事务开始的所有更改。
  • ROLLBACK TO 命令用于仅在 SAVEPOINT 之前撤消事务。在 SAVEPOINT 之前不能回滚事务，因此即使在指定命令之前事务仍保持活动状态。

18、SYS.ALL_DEPENDENCIES有什么用？

  • SYS.ALL_DEPENDENCIES 用于描述当前用户可访问的过程、包、触发器、函数之间的所有依赖关系。它返回名称、dependency_type、type、referenced_owner 等列。

19、数据库触发器执行过程中可用的虚拟表有哪些？

  • THEN 和NOW 表是在数据库触发器执行期间可用的虚拟表。表列分别称为 THEN.column 和 NOW.column。
  • 只有 NOW.column 可用于与插入相关的触发器。
  • 只有 THEN.column 值可用于 DELETE 相关触发器。
  • 两个虚拟表列都可用于 UPDATE 触发器。

20. 区分过程中声明的游标和包规范中声明的游标。

  • 在过程中声明的游标将具有本地作用域，因此它们不能被其他过程使用。
  • 在包规范中声明的游标被视为全局范围，因此它们可以被其他过程使用和访问。

PL/SQL 高级面试题

21. PL/SQL 中的 COMMIT、ROLLBACK 和 SAVEPOINT 语句是什么？

  • 这些是 PL/SQL 中可用的三个事务规范。
  • COMMIT：每当执行任何 DML 操作时，数据只会在数据库缓冲区中而不是在实际数据库中进行操作。为了将这些 DML 事务保存到数据库中，需要 COMMIT 这些事务。
    • COMMIT 事务操作会保存自上次提交以来所有未完成的更改，并执行以下步骤：
      • 受影响行的释放。
      • 交易被标记为完成。
      • 交易的详细信息将存储在数据字典中。
    • 语法： COMMIT;
  • ROLLBACK：为了撤消或删除在当前事务中所做的更改，需要回滚这些更改。ROLLBACK 语句会擦除自上次 COMMIT 以来的所有更改。
    • 语法：回滚；
  • SAVEPOINT：此语句给出名称并定义当前事务过程中的一个点，在该点之前发生的任何更改都将被保留，而在该点之后的所有更改都将被释放。
    • 语法： SAVEPOINT <savepoint_name>;

22. 如何调试 PL/SQL 代码？

  • 我们可以使用 DBMS_OUTPUT 和 DBMS_DEBUG 语句来调试我们的代码：
    • DBMS_OUTPUT 将输出打印到标准控制台。
    • DBMS_DEBUG 将输出打印到日志文件。

23. 变异表和约束表有什么区别？

  • 当前使用 DML 语句修改的表称为变异表。它也可以是一个定义了触发器的表。
  • 出于参照完整性约束的目的而用于读取的表称为约束表。

24. PL SQL常见面试题有哪些：DML语句执行的结果保存在哪些游标属性中？

  • DML语句的执行结果保存在以下4个游标属性中：
    • SQL%FOUND：如果至少已处理一行，则返回 TRUE。
    • SQL%NOTFOUND：如果没有处理任何行，则返回 TRUE。
    • SQL%ISOPEN：检查游标是否打开，如果打开则返回 TRUE。
    • SQL%ROWCOUNT：这将返回 DML 语句处理的行数。

25. 是否可以声明数字数据类型和规模大于精度的列？例如定义列，如：列名 NUMBER (10,100)，列名 NUMBER (10,-84)

  • 是的，这些类型的声明是可能的。
  • 数字(9, 12)表示小数点后有12位。但由于最大精度是 9，其余的都是 0 填充，如 0.000999999999。
  • 数字 (9, -12) 表示小数点前有 21 位数字，其中有 9 位可能的数字，其余为 0 填充，如 999999999000000000000.0

编程问题：PL SQL常见面试题和答案合集

26. 使用 WHILE 循环编写一个 PL/SQL 程序来计算用户输入数字的平均值。每当用户输入数字 0 时停止输入数字。

DECLARE
   n NUMBER;
   average NUMBER :=0 ;
   sum NUMBER :=0 ;
   count NUMBER :=0 ;
BEGIN
   -- Take input from user
   n := &input_number;
   WHILE(n<>0)
       LOOP
           -- Increment count to find total elements
           count := count+1;
           -- Sum of elements entered
           sum := sum+n;
           -- Take input from user
           n := &input_number;
       END LOOP;
   -- Average calculation
   average := sum/count;
   DBMS_OUTPUT.PUT_LINE(‘Average of entered numbers is ’||average);
END;

27. 编写一个 PL/SQL 过程，使用一些参数作为过滤器从数据库中选择一些记录。

  • PL SQL面试题解析：考虑到我们正在从 ib_employee 表中获取员工的详细信息，其中薪水是过滤器的参数。
CREATE PROCEDURE get_employee_details @salary nvarchar(30)
AS
BEGIN
   SELECT * FROM ib_employee WHERE salary = @salary;
END;

28. 编写一个 PL/SQL 代码来计算两个输入日期之间的星期日数。

--declare 2 dates of type Date
DECLARE
   start_date Date; 
   end_date Date; 
   sundays_count Number:=0; 
BEGIN
   -- input 2 dates
   start_date:='&input_start_date'; 
   end_date:='&input_end_date';
   /* 
   Returns the date of the first day after the mentioned date 
   and matching the day specified in second parameter.
   */
   start_date:=NEXT_DAY(start_date-1, 'SUNDAY');
   --check the condition of dates by using while loop. 
   while(start_date<=end_date) 
   LOOP 
       sundays_count:=sundays_count+1; 
       start_date:=start_date+7; 
   END LOOP; 
   
   -- print the count of sundays
   dbms_output.put_line('Total number of Sundays between the two dates:'||sundays_count); 
END; 
/

输入：
start_date = '01-SEP-19'
end_date = '29-SEP-19'

输出：
两个日期之间的星期日总数：5

29. 编写PL/SQL 代码块，将下表中employee_id 为102 的雇员的薪水增加1000。

EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAIL_IDPHONE_NUMBERJOIN_DATEJOB_ID SALARY
100ABCDEFabef98765432102020-06-06 AD_PRES24000.00
101GHIJKLghkl9876543211 2021-02-08AD_VP17000.00
102MNO PQRmnqr98765432122016-05-14AD_VP17000.00
103STUVWXstwx98765432132019-06-24IT_PROG9000.00
DECLARE
 employee_salary  NUMBER(8,2);

 PROCEDURE update_salary (
   emp        NUMBER, 
   salary IN OUT NUMBER
 ) IS
 BEGIN
   salary := salary + 1000;
 END;

BEGIN
 SELECT salary INTO employee_salary
 FROM ib_employee
 WHERE employee_id = 102;

 DBMS_OUTPUT.PUT_LINE
  ('Before update_salary procedure, salary is: ' || employee_salary);

 update_salary (100, employee_salary);

 DBMS_OUTPUT.PUT_LINE
  ('After update_salary procedure, salary is: ' || employee_salary);
END;
/

结果：

Before update_salary procedure, salary is: 17000
After update_salary procedure, salary is: 18000

30. 编写一个 PL/SQL 代码来查找给定的字符串是否是回文。

DECLARE
-- Declared variables string, letter, reverse_string where string is the original string. 
string VARCHAR2(10) := 'abccba'; 
letter VARCHAR2(20); 
reverse_string VARCHAR2(10); 
BEGIN
FOR i IN REVERSE 1..LENGTH(string) LOOP 
 letter := SUBSTR(string, i, 1);
 -- concatenate letter to reverse_string variable
 reverse_string := reverse_string ||''||letter; 
END LOOP;
IF reverse_string = string THEN
       dbms_output.Put_line(reverse_string||''||' is palindrome'); 
ELSE
       dbms_output.Put_line(reverse_string ||'' ||' is not palindrome'); 
END IF; 
END;

31. 编写 PL/SQL 程序，将给定数字的每个数字转换为其对应的字格式。

DECLARE
-- declare necessary variables 
   -- num represents the given number
   -- number_to_word represents the word format of the number
   -- str, len and digit are the intermediate variables used for program execution
num   INTEGER; 
number_to_word VARCHAR2(100); 
digit_str   VARCHAR2(100); 
len   INTEGER; 
digit   INTEGER; 
BEGIN
   num := 123456; 
   len := LENGTH(num); 
   dbms_output.PUT_LINE('Input: ' ||num);
   -- Iterate through the number one by one
   FOR i IN 1..len LOOP 
       digit := SUBSTR(num, i, 1);
       -- Using DECODE, get the str representation of the digit
       SELECT Decode(digit, 0, 'Zero ', 
                       1, 'One ', 
                       2, 'Two ', 
                       3, 'Three ', 
                       4, 'Four ', 
                       5, 'Five ', 
                       6, 'Six ', 
                       7, 'Seven ', 
                       8, 'Eight ', 
                       9, 'Nine ') 
       INTO digit_str 
       FROM dual;
       -- Append the str representation of digit to final result.
       number_to_word := number_to_word || digit_str; 
   END LOOP;
   dbms_output.PUT_LINE('Output: ' ||number_to_word); 
END;

输入： 12345
输出：一二三四五

32. PL SQL常见面试题有哪些：编写 PL/SQL 程序来计算一个数的数字之和。

DECLARE
--Declare variables num, sum_of_digits and remainder of datatype Integer 
num  INTEGER; 
sum_of_digits INTEGER; 
remainder  INTEGER; 
BEGIN
num := 123456; 
sum_of_digits := 0;
-- Find the sum of digits until original number doesnt become null 
WHILE num <> 0 LOOP 
 remainder := MOD(num, 10); 
 sum_of_digits := sum_of_digits + remainder; 
 num := TRUNC(num / 10); 
END LOOP; 
dbms_output.PUT_LINE('Sum of digits is '|| sum_of_digits); 
END;

输入： 9874
输出： 28

PL SQL常见面试题和答案合集结论

33.PL SQL面试

  • PL/SQL 是 Oracle 开发的 SQL 的编程扩展，它结合了 SQL 在数据操作领域的强大功能和过程语言的强大功能，可以更快、更有效地处理数据，从而创建强大的查询。
  • PL/SQL通过分别使用SQL和过程形式指示编译器“做什么”和“怎么做”，增强了安全性，增加了平台的可移植性，并使其更加健壮。
  • 最后，由于决策、过滤和循环功能的特性，它为程序员提供了更多的数据库权力，从而使其成为程序员处理数据库的更方便和可靠的手段。

参考资料：https://oracle-base.com/articles/misc/introduction-to-plsql

木子山

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: