PL / SQL面试问答

Here are 50+ frequently asked PL/SQL interview questions and answers which will definitely help you out to crack one of the toughest interviews.

这里有50多个PL / SQL面试常见问题和答案，它们肯定会帮助您解决最艰难的面试之一。

Q1. How can you Rollback a particular part of a procedure or any PL/SQL Program?
It can be done using Savepoints during definition of a PL/SQL program.

Q1。 如何回滚过程或任何PL / SQL程序的特定部分？
可以在定义PL / SQL程序时使用保存点来完成。

Q2. Is there a PL/SQL Engine in SQL*Plus?
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