这篇博客总结了PL/SQL面试中常见的52个问题，涵盖异常类型、游标用途、过程与函数的区别、参数模式、触发器组件、并发控制、数据库属性等核心概念。还讨论了如何处理和避免死锁，以及与数据库交互的相关操作，如批量收集数据和错误处理。此外，提到了Oracle提供的开发者包和SQL与SQL *PLUS的差异。
It can be done using Savepoints during definition of a PL/SQL program.
可以在定义PL / SQL程序时使用保存点来完成。
Unlike Oracle Forms, SQL*Plus does not have an SQL Engine. Thus, all your PL/SQL are sent directly to the database engine for execution. This makes it comparatively more efficient as SQL statements are not uncovered and sent to the database individually.
Q3. What are the different types of Exceptions? User-Defined and System Exceptions.
Q4. What is the basic use of a Cursor? A Cursor is basically used to access values from multiple records in the database or a table.
Q5. What is the basic difference between a Procedure and a Function? Both Procedures and Functions have the capability of accessing parameters but Functions return a value to the PL/SQL Block whereas Procedures do not return any value.
Q6. What are the modes of parameters in a PL/SQL Procedure? In, Out and In-Out are different modes of a PL/SQL Procedure.
Q7. What are the different components of a PL/SQL trigger? Trigger Action, Trigger Restriction and Trigger Action are the different components of a PL/SQL Trigger.
Q8. Describe the disadvantage of Database Trigger over Stored Procedures? We cannot control the execution of a Trigger whereas a Stored Procedure Execution can be controlled by the programmer.
Q9. What are the properties of a Database? The properties of a Database are also known as ACID Properties. These are Atomicity, Consistency, Isolation and Durability.
Q10. How can you take an Input from a User of the Database? You can take it using the Input Operator. This is as follow: val1:=&val1;
Q11. What is %rowtype used for? This is used if you want to create new variable which needs to fetch in a variable value from the database and you don’t know the Datatype of that variable. %rowtype will automatically change its datatype to the one in the database.
Q12. What are the Parameters for raise_application_errror()? The parameters are: Error Code and an Error Message. The Syntax is: raise_application_error(Error Code, Error Message);
Q13. What is the difference between a Rollback Command and a Commit Command? A Commit command is used to save the current transaction in the database in which modification is done to the database using Database Manipulation Language (DML) commands. A Rollback command is however used to undo the modifications done by the DML commands previously.
Q14. What is a Statement Level Trigger? A Statement Level trigger is executed when a statement or a command affects the whole table which is independent of a row in any table.
Q15. What are the different parts of an Explicit Cursor? The different parts in the process of making an Explicit Cursor are as follows: 1. Declaring the Cursor 2. Opening the Cursor 3. Fetching the Cursor 4. Closing the Cursor
Q16. Enlist various types of PL/SQL Exceptions? The PL/SQL Exceptions are as follows: 1. Zero_Divide 2. No_Data_Found 3. Cursor_Already_Open 4. Login_Denied
Q17. How can you enable or disable a trigger? To Enable a Trigger, the Syntax is: Alter Trigger Trigger_Name Enable; To Disable a Trigger, the Syntax is: Alter Trigger Trigger_Name Disable;
Q18. How can you assign a Name to an Un-Named PL/SQL Exception Block? You can assign a name to an Un-Named Exception using Pragma, also known as Exception_init.
Q19. What is the range of the Error Codes in PL/SQL Exceptions? The range of Error Code in PL/SQL Exception is between -20000 and -20899.
Q20. What is Context Area in PL/SQL? Oracle processes the executed SQL Statement in a separate memory zone called as Context Area. It contains information regarding SQL Query, number of rows accessed by it, information retrieved from database tables/records and many more.
Q21. Which is the Default Cursor in Oracle PL/SQL? Implicit Cursors are the Default Cursor in PL/SQL. These cursors are automatically activated when DML statements are encountered such as Insert, Delete or Update.
Q22. Why is closing the Cursor required during explicit cursor development? It is important because it will consume system memory while it is in active state and if it is not closed or terminated then it won’t let the other things in the memory as memory will be occupied and later on it will be full. Hence, deletion is necessary.
Q23. Enlist various loops in PL/SQL Database. The various loops used in PL/SQL are as follows: 1. Simple Loop 2. For Loop 3. Nested Loop 4. While Loop
Q24. Explain about SQLERRM and SQLCODE and their importance. SQLERRM Returns the Error Message for the Last Error that came across. SQLERRM is useful for WHEN OTHERS Exception. They can be used for Reporting purpose in Error Handling. They can also store the Error in the Code and store it in the Error Log Table. SQLCODE Returns the Value of the Error Number for the previous error.
Q25. What rules are to be taken care of when doing comparisons using NULL? 1. A NULL should never be TRUE or FALSE. 2. If a value in an expression is NULL, then the expression itself evaluates to NULL except for Concatenation Operator. 3. A NULL is never equal or unequal to other values. 4.NULL is not a value, it represents absence of data. So NULL is something UNIQUE
Q26. What is the Difference between Runtime Errors and Syntax Errors? A Runtime Error is handled with the help of Exception Handling Mechanism in a PL/SQL Block whereas a Syntax Error such as a spelling mistake is efficiently detected by the SQL Compiler.
Q27. Explain about Pragma Exception_Init. It allows us to handle Oracle Pre Defined Messages wherein we can replace our own Message. We can therefore instruct the compiler to link the user specified message to Oracle Pre Defined Message during Compilation Time. Syntax: Pragma Exception_Init (Exception_Name, Error_Code)
Q28. What is Mutating Table Error? It occurs when a Trigger tries to update a row that is currently in execution stage. So it is solved out by using temporary tables and views.
Q29. What is the maximum limit of applying Triggers to a Table? The maximum number of Triggers that can be applied to one table is 12.
Q30. What is the method to find out whether a Cursor is open or not? The Cursor Status Variable can be used to find out whether the Cursor is open or not. It is %ISOPEN.
Q31. What is a Row Level Trigger? A Statement Level Trigger is executed whenever a statement or a command affects a row in a table by Database Manipulation Command (DML) statements like Delete, Insert or Update.
Q32. What is an Active Set? The set of rows that a Cursor holds at a single point of time is called as an Active Set.
Q33. What are the different Loop Control Structures used in PL/SQL? The different Loop Control Structures in PL/SQL are as follows: 1. Exit 2. Exit-When 3. Continue 4. Goto
Q34. What is set serveroutput function used for in PL/SQL? In PL/SQL, we frequently require to produce the Output on the console. We generally do it using dbms_output.put_line() function. For this to work properly and display the output on the console screen, we need to first set the server output to ON state. For this, the command is: set serveroutput on;
Q35. Which Datatypes are available in PL/SQL? There are mainly two main Datatypes available in PL/SQL which are further sub-divided into many datatypes and these are: 1. Composite Datatypes: Record, Table, etc. 2. Scalar Datatypes: Date,Number, Boolean, Varchar, Long, Varchar2, etc.
Q36. Explain the difference between Truncate and Delete? Truncate is much faster than Delete Command. It basically resets the Memory Blocks after Execution. Delete is a Database Manipulation Language (DML) Command whereas Truncate is a Data Definition Language (DDL) Command and it is comparatively slower.
Q37. Explain about Package in short. A Package is a Schema Object which assembles logically relate PL/SQL Datatypes and Sub-Programs. It is actually a combination of Procedures, Functions, Record Type and Variables. It enhances Application Development and this provides Modular Programs. It also provides Encapsulation which hides data from Unauthorized Users.
Q38. What are the disadvantages of Cursors and is there any alternative to it? The processing of Cursors is very slow as compared to Joins. Hence, Joins can be an alternative to Cursors.
Q39. What is the method to display messages in Output Files and Log Files? The Output Files and Log Files can be used to display messages by using the following: Fnd_file.put_line.
Q40. What is the difference between Grant command and Revoke command? A Grant command permits the End-User to perform certain activities onto the database whereas a Revoke command prevents the End-User from making any changes to the Database.
Q41. Enlist the Attributes of a Cursor in PL/SQL. %Rowcount: This attribute checks the number of rows that are updated, deleted or fetched. %Isopen: This attribute checks whether the Cursor you want to access is currently open or closed. %Found: This attribute checks if the Cursor fetched a row. It returns a TRUE if any row is fetched. %NotFound: This attribute checks if the Cursor fetched any row or not. It returns a TRUE value if any row is not fetched.
Q42. What is raise_application_error? It is a Procedure which is included in the Dbms_Standard Package that allows User-Defined Messages to be issued from the Database Stored Sub-program or a Trigger.
Q43. Explain the difference between Varchar and Char? Varchar doesn’t sets aside memory location during declaration of a variable. It stores the value only after a variable is defined or assigned a value. Its storage capacity is 32767 Bytes.
Char however preserves the memory location mentioned in the variable declaration even if it is not used. The maximum storage capacity for a Character variable is 255 Bytes.
Q44. Explain Union, Union All, Intersect and Minus in PL/SQL. Union: It returns all the distinct rows selected by either of the Queries. Union All: It returns all the rows selected by one of the queries which includes all the duplicates. Intersect: It returns all the distinct rows selected by both the queries. Minus: It returns all the distinct rows selected by the first query and not by the second one.
Q45. What is the difference between Varchar2 and Varchar? Varchar2 Datatype is memory efficient as it variable memory storage datatype whereas a Varchar Datatype variable is not memory efficient as it has fixed memory storage. Varchar occupies space for NULL values whereas Varchar2 variable does not. Varchar can store upto 2KB whereas a Varchar2 Datatype can store upto 4KB.
Q46. What is a Mutating Table Error and how can you solve it? It occurs if the Trigger tries to update a row that is currently being used. This is solved either by using the Temporary Tables or by the Views.
Q47. Enlist the packages provided by the Oracle for use by the Developers? Oracle provides the packages such as Dbms_Transaction, Dbms_Alert, Dbms_Job, Dbms_Ddl, Dbms_Output, Dbms_Utility, Dbms_Lock, Dbms_Sql, Dbms_Pipe and Utl_File.
Q48. Explain the difference between SQL and SQL *PLUS. SQL represents Structured Query Language and is used to manage the database. It is a Non Procedural Programming Language and a Standard Language for Relational Database Management System (RDBMS). However, SQL *PLUS is an Oracle specific program that executes SQL commands using PL/SQL blocks.
Q49. Explain Bulk Collect. It is a way of fetching a very big collection of data. With the help of Oracle Bulk Collect, the PL/SQL Engine indicates the SQL Engine to collect more than one row at a single point of time and stores them into a collection. Then it switches back to the PL/SQL Engine. During the Bulk Collect, Context Switch at one point. The performance improvement would be better with the more number of rows fetched into the collection.
Q50. Enlist the Types of Triggers and its combinations. There can be various types of combinations used in Triggers which are After, Before, Insert, Update, Delete, Row, Table and other such combinations.
Q51. Enlist the methods to recover from Deadlock Selection of a Victim, Rollback and Starvation are the methods to recover from a Deadlock.
Q52. Enlist Concurrency Control Schemes. The various Concurrency Control Schemes are Lock Based Protocol, Validation Base Protocol, Time Stamp Based Protocol and Multi-version Schemes.
So this was the list of all the important PL/SQL interview questions and answers that are very frequently asked in the interviews. If you found any information incorrect or missing in above list then please mention it by commenting below.
与Oracle Forms不同，SQL * Plus没有SQL引擎。 因此，所有PL / SQL都直接发送到数据库引擎以执行。 由于不会发现SQL语句并将其单独发送到数据库，因此这使其效率相对更高。
Q3。 有哪些不同类型的例外？ 用户定义和系统异常。
Q4。 游标的基本用途是什么？ 游标基本上用于访问数据库或表中多个记录的值。
Q5。 过程和函数之间的基本区别是什么？ 过程和函数都具有访问参数的能力，但是函数将值返回到PL / SQL块，而过程不返回任何值。
Q6。 PL / SQL过程中的参数模式是什么？ In，Out和In-Out是PL / SQL过程的不同模式。
Q7。 PL / SQL触发器的不同组件是什么？ 触发器动作，触发器限制和触发器动作是PL / SQL触发器的不同组件。
Q8。 描述数据库触发器比存储过程的缺点？ 我们不能控制触发器的执行，而程序员可以控制存储过程的执行。
Q9。 数据库的属性是什么？ 数据库的属性也称为ACID属性。 这些是原子性，一致性，隔离性和耐久性。
Q10。 如何从数据库用户那里获取输入？ 您可以使用输入运算符使用它。 如下所示： val1：=＆val1;
Q11。 ％rowtype用于什么？ 如果要创建需要从数据库获取变量值的新变量，而又不知道该变量的数据类型，则使用此方法。 ％rowtype将自动将其数据类型更改为数据库中的一种。
Q12。 raise_application_errror()的参数是什么？ 参数为：错误代码和错误消息。 语法为：raise_application_error(错误代码，错误消息)；
Q13。 回滚命令和提交命令有什么区别？ 提交命令用于将当前事务保存在数据库中，并使用数据库操作语言(DML)命令对数据库进行修改。 但是，回滚命令用于撤消先前DML命令所做的修改。
Q14。 什么是语句级别触发器？ 当一条语句或命令影响整个表时，将执行一个语句级触发器，该表独立于任何表中的一行。
Q15。 显式游标有哪些不同部分？ 制作显式游标的过程中的不同部分如下： 1.声明游标 2.打开游标 3.取出游标 4.关闭游标
Q16。 征募各种类型的PL / SQL异常？ PL / SQL异常如下： 1. Zero_Divide 2. No_Data_Found 3. Cursor_Already_Open 4. Login_Denied
Q17。 如何启用或禁用触发器？ 要启用触发器，语法为：Alter Trigger Trigger_Name Enable； 要禁用触发器，语法为：Alter Trigger Trigger_Name Disable;
Q18。 如何为未命名的PL / SQL异常块分配名称？ 您可以使用Pragma(也称为Exception_init)将名称分配给未命名的异常。
Q19。 PL / SQL异常中的错误代码范围是多少？ PL / SQL异常中的错误代码范围在-20000到-20899之间。
Q20。 PL / SQL中的上下文区域是什么？ Oracle在称为“上下文区域”的单独内存区域中处理已执行SQL语句。 它包含有关SQL查询，它访问的行数，从数据库表/记录中检索到的信息等信息。
Q21。 Oracle PL / SQL中的哪个默认光标？ 隐式游标是PL / SQL中的默认游标。 当遇到DML语句(如插入，删除或更新)时，将自动激活这些游标。
Q22。 为什么在显式游标开发过程中需要关闭Cursor？ 这很重要，因为它会在处于活动状态时消耗系统内存，并且如果未关闭或终止它，则它将不会占用内存中的其他内容，因为内存将被占用，以后将被占用。 因此，删除是必要的。
Q23。 在PL / SQL数据库中注册各种循环。 PL / SQL中使用的各种循环如下： 1.简单循环 2.对于循环 3.嵌套循环 4. While循环
Q24。 解释一下SQLERRM和SQLCODE及其重要性。 SQLERRM返回遇到的最后一个错误的错误消息。 SQLERRM对于“其他人异常”很有用。 它们可用于错误处理中的报告目的。 他们还可以将错误存储在代码中，并将其存储在错误日志表中。 SQLCODE返回上一个错误的错误号的值。
Q25。 使用NULL进行比较时应注意哪些规则？ 1. NULL绝对不能为TRUE或FALSE。 2.如果表达式中的值为NULL，则表达式本身的计算结果为NULL，但连接运算符除外。 3.空值永远不会等于或不等于其他值。 4.NULL不是一个值，它表示没有数据。 所以NULL是唯一的
Q26。 运行时错误和语法错误之间有什么区别？ 通过PL / SQL块中的异常处理机制可以处理运行时错误，而SQL编译器可以有效地检测到语法错误(例如拼写错误)。
Q27。 说明一下Pragma Exception_Init。 它使我们能够处理Oracle预定义消息，其中我们可以替换自己的消息。 因此，我们可以指示编译器在编译期间将用户指定的消息链接到Oracle预定义消息。 语法：语法Exception_Init(Exception_Name，Error_Code)
Q28。 什么是突变表错误？ 当触发器尝试更新当前处于执行阶段的行时，会发生这种情况。 因此可以通过使用临时表和视图来解决。
Q29。 将触发器应用于表的最大限制是多少？ 一张表最多可以使用12个触发器。
Q30。 找出游标是否打开的方法是什么？ 光标状态变量可用于确定光标是否打开。 这是％ISOPEN。
Q31。 什么是行级触发器？ 每当语句或命令通过数据库操作命令(DML)语句(如Delete，Insert或Update)影响表中的一行时，就会执行语句级别触发器。
Q32。 什么是活动集？ 游标在单个时间点上保存的行集称为“活动集”。
Q33。 PL / SQL中使用哪些不同的循环控制结构？ PL / SQL中不同的循环控制结构如下： 1.退出 2.退出时 3.继续 4.转到
Q34。 在PL / SQL中使用什么设置服务器输出功能？ 在PL / SQL中，我们经常需要在控制台上生成输出。 我们通常使用dbms_output.put_line()函数来完成此操作。 为了使其正常工作并在控制台屏幕上显示输出，我们需要首先将服务器输出设置为ON状态。 为此，该命令是：set serveroutput on;
Q35。 PL / SQL提供哪些数据类型？ PL / SQL中主要有两种主要的数据类型，它们又细分为许多数据类型，分别是： 1.复合数据类型：Record，Table等 。2.标量数据类型：Date，Number，Boolean，Varchar，Long， Varchar2等
Q36。 解释“截断”和“删除”之间的区别？ 截断比删除命令快得多。 它基本上在执行后重置存储块。 删除是一种数据库操作语言(DML)命令，而截断是一种数据定义语言(DDL)命令，它相对较慢。
Q37。 简短说明一下Package。 包是一个模式对象，它在逻辑上组装相关的PL / SQL数据类型和子程序。 它实际上是过程，函数，记录类型和变量的组合。 它增强了应用程序开发，并提供了模块化程序。 它还提供了封装，可对未经授权的用户隐藏数据。
Q38。 游标的缺点是什么？有没有其他选择？ 与连接相比，游标的处理非常慢。 因此，联接可以替代游标。
Q39。 在输出文件和日志文件中显示消息的方法是什么？ 通过使用以下命令，可以使用输出文件和日志文件显示消息：Fnd_file.put_line。
Q40。 Grant命令和Revoke命令有什么区别？ 授权命令允许最终用户对数据库执行某些活动，而撤消命令则阻止最终用户对数据库进行任何更改。
Q41。 在PL / SQL中注册游标的属性。 ％Rowcount：此属性检查已更新，删除或获取的行数。 ％Isopen：此属性检查您要访问的游标当前处于打开还是关闭状态。 找到的百分比：此属性检查游标是否获取了一行。 如果获取任何行，则返回TRUE。 ％NotFound：此属性检查Cursor是否获取了任何行。 如果未获取任何行，它将返回TRUE值。
Q42。 什么是raise_application_error？ 它是Dbms_Standard包中包含的过程，该过程允许从数据库存储的子程序或触发器发出用户定义的消息。
Q43。 请解释一下Varchar和Char之间的区别？ 在声明变量期间，Varchar不会保留内存位置。 仅在定义或分配了变量后才存储该值。 其存储容量为32767字节。
但是，即使不使用Char，Char也会保留变量声明中提到的内存位置。 字符变量的最大存储容量为255字节。
Q44。 说明PL / SQL中的Union，Union All，Intersect和Minus。 联合：返回由任一查询选择的所有不同行。 全部合并：返回由查询之一选择的所有行，其中包括所有重复项。 相交：返回两个查询选择的所有不同行。 减号：它返回第一个查询而不是第二个查询选择的所有不同行。
Q45。 Varchar2和Varchar有什么区别？ Varchar2数据类型具有可变存储存储数据类型，因此具有存储效率，而Varchar数据类型变量具有固定的存储空间，因此存储效率不高。 Varchar占用NULL值的空间，而Varchar2变量不占用。 Varchar最多可以存储2KB，而Varchar2数据类型最多可以存储4KB。
Q46。 什么是变异表错误，您该如何解决？ 如果触发器尝试更新当前正在使用的行，则会发生这种情况。 这可以通过使用临时表或通过视图来解决。
Q47。 征集Oracle提供的供开发人员使用的软件包？ Oracle提供了Dbms_Transaction，Dbms_Alert，Dbms_Job，Dbms_Ddl，Dbms_Output，Dbms_Utility，Dbms_Lock，Dbms_Sql，Dbms_Pipe和Utl_File等软件包。
Q48。 解释SQL和SQL * PLUS之间的区别。 SQL表示结构化查询语言，用于管理数据库。 它是非过程编程语言和关系数据库管理系统(RDBMS)的标准语言。 但是，SQL * PLUS是Oracle特定的程序，它使用PL / SQL块执行SQL命令。
Q49。 解释批量收集。 这是一种获取大量数据的方法。 在Oracle Bulk Collect的帮助下，PL / SQL引擎指示SQL引擎在单个时间点收集多个行并将它们存储到一个集合中。 然后，它切换回PL / SQL引擎。 在批量收集期间，上下文切换为一点。 随着获取到集合中的行数越多，性能的提高越好。
Q50。 征集触发器类型及其组合。 触发器中可以使用各种类型的组合，包括之后，之前，插入，更新，删除，行，表和其他此类组合。
Q51。 争取从死锁中恢复的方法 选择受害者，回滚和饥饿是从死锁中恢复的方法。
Q52。 征集并发控制方案。 各种并发控制方案是基于锁的协议，基于验证的协议，基于时间戳的协议和多版本方案。
因此，这是所有在访谈中经常问到的重要的PL / SQL访谈问题和答案的列表。 如果您在上面的列表中发现任何不正确或缺失的信息，请在下面的评论中提及。
翻译自: https://www.thecrazyprogrammer.com/2015/07/plsql-interview-questions-and-answers.html
扫一扫
1048
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
