PL/SQL developers are in huge demand these days, as the platform has nothing but great benefits to offer. For enhanced security, portability and robustness of databases, the Oracle developers prefer PL/SQL than SQL. Here are a few PL/SQL questions that can help to crack an interview:
PL/SQL Interview Questions
- 1) What is PL/SQL?
- 2) Differentiate between PL/SQL and SQL?
- 3) State few characteristics of PL/SQL?
- 4) Enlist the data types that can be used in PL/SQL?
- 5) Explain the purpose of %TYPE AND %ROWTYPE data types?
- 6) What are PL/SQL packages?
- 7) What are PL/SQL cursors?
- 8) Explain cursor types?
- 9) Why triggers are used?
- 10) What is the difference between execution of triggers and stored procedures?
- 11) Explain the difference between Triggers and Constraints?
- 12) What is a PL/SQL block?
- 13) Differentiate between syntax and runtime errors?
- 14) What are some predefined exceptions in PL/SQL?
- 15) How many types of triggers exist in PL/SQL?
- 16) What is the difference between function, procedure and package in PL/SQL?
- 17) What is PL/SQL table? Why is it used?
- 18) What is a mutating table and a constraining table?
- 19) What is the basic structure of PL/SQL?
- 20) What is the difference between ROLLBACK and ROLLBACK TO statements?
- 21) What is pl sql block?
Below are the list of Best PL/SQL Interview Questions and Answers
PL/SQL stands for Procedural Language extension of Structured Query Language (SQL). It is a block-structured language having logical blocks which are made up of three sub-blocks i.e. a declarative part, an executable part and an exception building part. PL/SQL is integrated with Oracle and the functionalities of PL/SQL extend after each release of Oracle database.
It includes procedural language elements like conditions and loops and allows declaration of constants and variables, procedures and functions. It also helps the users to develop complex database applications using control structures, procedures, modules, etc. PL/SQL is not limited by any case-sensitive letters so you are free to use lower case letters or upper case letters.
PL/SQL is Oracle’s Procedural Language SQL which allows you to write full programs to execute options like insert/ select/ updates/ delete. SQL is a query language that allows you to execute a single insert/ delete/ update. The following are the notable differences between SQL and PL/SQL:
|SQL is a data-oriented language which is very useful for interactive processing.||PL/SQL is a procedural language to create applications. It is an extension of Oracle.|
|SQL doesn’t have any procedural capabilities like condition testing, looping. It tells the database what to do and not how to do.||On the contrary, PL/SQL offers procedural capabilities like condition testing, looping, etc. it also offers high language features.|
|SQL is executed by the database one statement at a time. It is a time-consuming process.||PL/SQL is less time consuming as compared with SQL. It sends the block of codes to the database server at the same time.|
|There are very less chances of error handling in SQL.|
- PL/SQL is completely portable as code can be executed on any operating system provided Oracle is loaded in it.
- It offers extensive error checking. Users can write their own customized error handling routines.
- PL/SQL provides a built-in, integrated programming environment which enhances the performance of transactions with integration to Oracle data dictionary.
- It also allows free access and sharing of same subprograms by multiple applications.
Each constant, value and parameter has a data type that tells us the type of data used and their associated operations in the program. The following are four data types available in PL/SQL:
- Scalar data type-these are single-valued data types with no internal components. They are further classified into four categories-
- CHAR– these data types hold the fixed-length character strings. Its value ranges from 1 to 32,767 characters.
- VARCHAR2– these data types store variable-length character strings ranging from 1 to 32,767 characters.
- BOOLEAN– it is a data type with two values, true or false, intended to represent the truth values of logic and Boolean algebra.
- DATE– it is a data type for storing date and time information.
- LONG– it is an Oracle data type for storing character data of variable length.
- Composite data type- it is a combination of other data types and internal components that can be easily altered and manipulated. These include record, table and array.
- Reference data type- it is a data type that holds pointers to other data items. Ref Cursor is an example.
- Large object data type- These data types hold locators that defines the location of large objects stored out of the line. These are stored separately from other data items.
%TYPE- The %TYPE lets you declare a constant, variable, collection element, etc as previously declared variable or column. It is an attribute which is used for anchoring. Example- the variable m_empno has same data type as the column empno in table emp.
%ROWTYPE- the %Rowtype attribute lets you declare a record that represents a row in the table. The fields of the row have same name and data types as column in the view. Example- dept_rec dept%ROWTYPE . This declares a record that can store an entire row for DEPT table.
There are two types of PL/SQL cursors -
Explicit cursors– Explicit cursors are used for queries that return more than one row. It is a programmer-defined cursor for gaining more control over context area. In order to use explicit cursors, the following steps are used-
Declare the cursor,
Syntax: CURSOR <cursor_name> is
Here, cursor name is the name given to the cursor and SELECT is used for returning rows to cursor active set.
Open the cursor
Syntax: OPEN <cursor_name>
Where <cursor_name> is name of previously-defined cursor.
Fetch rows from the cursor
Syntax: FETCH <cursor_name> INTO <record_list>
Close the cursor
Syntax: CLOSE <cursor_name>
Implicit cursors- These cursors are created when any SQL statements are executed. PL/SQL uses following implicit cursors-
There are several differences between Triggers and constraints. Given below are the major ones:
|Trigger affects only those rows, which are added after it is enabled.||Constraints affects all the rows i.e. the once that existed before and the ones that were newly added.|
|Triggers unlike constraints is capable of implementing high-end business rules that are complicated||A constraint is responsible only for maintaining the integrity of the database.|
In PL/SQL, grouping the statements into units creates a ‘block’. You can include several elements in PL/SQL blocks such as variables, loops, constants, SQL statements, and conditional statements except handling. Using blocks you can develop a function, a procedure or probably a package. Generally, these blocks can be classified into two broad categories:
- Anonymous blocks: When the PL/SQL blocks do not have a header they are referred as anonymous blocks. Such blocks cannot be used for developing the triggers, function or a procedure. For example DECLARE, num NUMBER(2), sq NUMBER(3), BEGIN, num:= &Number1, sq := num*num, DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq), END.
- Named blocks: When the PL/SQL block contains a header or labels, it is referred as Named blocks. Using named blocks, subprograms such as functions, procedures, packages or triggers can be developed. For example FUNCTION sqr (num IN NUMBER), RETURN NUMBER is sq NUMBER (2), BEGIN, sq:= num*num, RETURN sq, END
Here is a list of some predefined exceptions that can be used in PL/SQL:
- TIMEOUT _ON_RESOURCE
- BEFORE ALL ROW INSERT
- BEFORE INSERT
- AFTER INSERT
- AFTER ALL ROW INSERT
Function: The PL/SQL function consists of return type and is responsible for computing so that it can return a single value. Due to a specification in return type, the value returned must be in that same type.
Procedure: Procedure is just responsible for a return statement that can stop an execution and return back to the caller. Unlike a function, the procedure does not have any specific return type and doesn’t return single but multiple values.
Package: A package, which is a schema object, is responsible for grouping PL/SQL types, subprograms and items, which are logically related. Package consists of all i.e. procedure, record type statement, function, and procedure. Being able to provide modularity, it is cable of developing an application. Using a package, you can hide information coming from an unauthorized user.
PL/SQL is a language whose programs are written in terms of blocks of codes that's why it is also known as block-structured language.
There are three sections of the structure of PL/SQL which are declarations, executable commands, and exception handling.
- Declaration: The first declaration section begins with the keyword DECLARE. This section defines cursors, variables, subprograms, and other elements. In short here, you declare all the variables you are using in the program.
- Executable commands: It is the second section which is enclosed with BEGIN and END keywords. This section is mandatory to perform as it consists of the statement of the program. It is needed that you use at least one line of code that is executable.
- Exceptional handling: This last section begins with the EXCEPTION keyword. The function of this section in the program is to handle errors.
Just like every procedural language, a block is the smallest valuable group of code. A block offers both scoping and execution boundaries for exception handling and declaring variables. PL/SQL offers you the opportunity to create blocks that have no name and blocks that have a name. These blocks might be triggers, packages, objects, procedures or function types. A PL/SQL block is made up of 4 sections. The 4 sections are declaration, exception, header and execution sections but just one is required. The header section is an optional section just like exception and declaration. Only the execution section is required or mandatory.
Related Interview Questions
DB2 Interview Questions
DBMS Interview Questions
Stored procedures Interview questions
Mysql Interview Questions
Mssql Interview Questions
MongoDB Interview Questions
Neo4j interview questions
NoSQL interview questions
Oracle Interview Questions
OrientDB interview questions
PL/SQL Interview Questions
Postgresql Interview Questions
Redis interview questions
SQLite interview questions
RavenDB Interview Questions
Couchdb Interview Questions
Weblogic Interview Questions
MariaDB Interview Questions
Cassandra Interview Questions
PouchDB Interview Questions
RDBMS Interview Questions
Subscribe Our NewsLetter
Never Miss an Articles from us.
- Most Common Interview Questions
- Python Flask Interview Questions
- NoSQL interview questions
- JQuery Interview Questions
- C programming interview questions
- AngularJS Interview Questions
- Node JS Interview Questions with Express
- Core Java interview questions
- HTML Interview Questions
- Laravel interview questions
- Wordpress Interview Questions
- PHP Interview Questions