pl sql面试题_PL SQL面试问答

最新推荐文章于 2025-03-26 17:19:41 发布

cunchi4221

最新推荐文章于 2025-03-26 17:19:41 发布

阅读量5k

点赞数

文章标签：数据库 python mysql 面试 java

原文链接：https://www.journaldev.com/17786/pl-sql-interview-questions-answers

版权

本文详细介绍了PL/SQL的关键概念，包括其与SQL的区别、基本结构、过程、函数、触发器、游标等。还讨论了PL/SQL的数据类型、异常处理、游标使用、存储过程和函数的调用方式，以及如何处理事务。此外，文章涵盖了PL/SQL在面试中的常见问题，如回滚和提交操作、数据类型、游标和异常处理。对于数据库开发者和面试者来说，这是一个全面的PL/SQL面试准备指南。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pl sql面试题

If you have worked on Oracle database and going for an interview, you should go through PL SQL interview questions and answers.

如果您曾经在Oracle数据库上工作过并且要进行面试，则应该阅读PL SQL面试问题和答案。

PLSQL stands for Procedural Language extension to Structured Query Language. As the definition suggests it’s a Block Structured programming language that extends the capabilities of SQL to provide a more versatile query and update solutions regarding relational databases.

PLSQL代表对结构化查询语言的过程语言扩展。顾名思义，它是一种块结构化编程语言，它扩展了SQL的功能，以提供有关关系数据库的更通用的查询和更新解决方案。

A few of the many key features that P/L SQL provides are procedures, functions, triggers, cursors etc. A portion of P/L SQL is framed from the SQL programming syntax with added procedural features. For prerequisites, it is highly recommended to have fundamental knowledge in SQL (Structured Query Language).

P / L SQL提供的许多关键功能中的一些是过程，函数，触发器，游标等。P/ L SQL的一部分由SQL编程语法构成，并带有附加的过程功能。对于先决条件，强烈建议您具有SQL（结构化查询语言）的基础知识。

PL SQL面试问题 (PL SQL Interview Questions)

PL SQL Interview Questions and Answers, Oracle PL SQL interview questions

Listed below are various PL SQL interview questions that will help you in your upcoming interview. You should also go through SQL Interview Questions.

下面列出了各种PL SQL面试问题，这些问题将在您即将进行的面试中为您提供帮助。您还应该阅读SQL面试问题。

什么是PL SQL？ (What is PL SQL?)
PL SQL is a Block Structured programming language created by Oracle in the 1990s in hoping to provide additional procedural programming solutions to SQL.

PL SQL是Oracle在1990年代创建的一种块结构编程语言，希望为SQL提供其他过程编程解决方案。
SQL和PL SQL之间的主要区别是什么？ (What are the key differences between SQL and PL SQL?)
SQL is a Structured Query Language as opposed to P/L SQL which is a full procedural language. SQL code is processed one statement block at a time, while P/L SQL code is executed as a single program at one time. SQL can be within P/L SQL, but P/L SQL cannot be within SQL.

SQL是一种结构化查询语言，而P / L SQL是一种完整的过程语言。 SQL代码一次处理一个语句块，而P / L SQL代码一次作为一个程序执行。 SQL可以在P / L SQL内，但P / L SQL不能在SQL内。
PL / SQL的基本结构是什么？ (What is the basic structure of PL/SQL?)
PL SQL, as much as any other procedural language, contains blocks. These blocks which are the basic unit of sensible code are primarily categorized by two types: anonymous blocks and named blocks.

与其他任何过程语言一样，PL SQL包含块。这些块是明智代码的基本单元，主要分为两种类型：匿名块和命名块。
```
[DECLARE]
   Declaration statements;
BEGIN
   Execution statements;
  [EXCEPTION]
      Exception handling statements;
END;
```
They are called anonymous because they have no names and are not saved in an Oracle Database.

它们之所以称为匿名的，是因为它们没有名称并且没有保存在Oracle数据库中。

The figure below shows a detailed PL SQL block structure:

下图显示了详细的PL SQL块结构：

Header Section is for naming/labeling named blocks. This may or may not be used.

标题部分用于命名/标记命名块。可能会或可能不会使用。

Declaration Section is for declaring variables, cursors, or sub-blocks that will be used in the Execution Section and Exception Section. This may or may not be used.

声明部分用于声明将在执行部分和异常部分中使用的变量，游标或子块。可能会或可能不会使用。

Execution Section block is where runtime code is placed. Statements in this section are required to exist for the structure to run.

执行节块是放置运行时代码的位置。要运行该结构，必须存在本节中的语句。

Exception Section contains the exception and error handling of the code.

异常部分包含代码的异常和错误处理。

Essential keywords such as IS, BEGIN, EXCEPTION, and END are vital in the program for the runtime engine to distinguish each block sections.

IS，BEGIN，EXCEPTION和END等基本关键字在程序中对于运行时引擎区分每个块节至关重要。
触发器及其用途是什么？ (What are triggers and its uses?)
Triggers are blocks of code which are run whenever the criteria for a specific event is satisfied. They are hardcoded within the PL SQL program and listens to events such as: DML(database manipulation), DDL(database definition), and database operation. They can be coded within a view, table, database, or scheme for which the mentioned event belongs.

触发器是代码块，只要满足特定事件的条件，就会运行该代码块。它们在PL SQL程序中进行了硬编码，并侦听以下事件：DML（数据库操作），DDL（数据库定义）和数据库操作。它们可以在提到的事件所属的视图，表，数据库或方案中进行编码。

There are many uses of triggers. They can be used to generate column values upon activating. For event logging within the table activities, auditing, and creating table duplicates. For security, they can implement security authorization, and handle invalid transactions.

触发器有很多用途。它们可用于在激活时生成列值。对于表活动中的事件记录，审核和创建表重复项。为了安全，他们可以实施安全授权，并处理无效的交易。

General Structure of creating a Trigger:

创建触发器的一般结构：
```
CREATE [OR REPLACE ] TRIGGER triggerName  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF colName]  
ON tableName  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;
```
如何编译PL / SQL代码？ (How is a PL/SQL code compiled?)
Firstly, PL/SQL code is transferred to the server and is compiled to byte code. This process takes place prior to program execution. To increase the performance of the procedural code, the procedures are converted to native code shared libraries which are conveniently linked to the kernel. Note that increase in performance still greatly depends on the code structure. This is independent to database calls, and affects performance only on loops, calculations, etc.

首先，PL / SQL代码被传输到服务器并被编译为字节码。该过程在程序执行之前进行。为了提高过程代码的性能，将过程转换为方便地链接到内核的本机代码共享库。请注意，性能的提高仍然很大程度上取决于代码结构。这与数据库调用无关，并且仅影响循环，计算等性能。
使用PL / SQL创建的模式对象有哪些？ (What are few of the schema objects that are created using PL/SQL?)
A schema is a user-owned set of schema objects, or logical data structures. These schema objects types are as follows:

模式是用户拥有的一组模式对象或逻辑数据结构。这些架构对象类型如下：