PL/SQL Interview Questions- Part 3

Answer:

Data types in PL/SQL define the ways to identify a data type & its associated operations. There are mainly four types of predefined data types as follows:

• Scalar Data Types: It is an atomic data type that has no internal components.
• Composite Data Types: It is made up of other data types & internal components that can be easily manipulated & used.
• Reference Data Types: It holds values, called pointers, that designate other data items or program items.
• Large Object Data Types: A Large Object datatype or LOB holds values, called locators, that define the large objects’ location, such as graphic image, video clips, etc., stored out of line.

Answer:

1. PL/SQL deploys BLOCK structure as its basic structure. Each PL/SQL program has an SQL & PL/SQL statement that forms a PL/SQL block.
   PL/SQL block consist of three sections:
   • It is optional & can be skipped if no declarations are required.
   • If present, Declaration should be the first section in the PL/SQL block.
   • The Declaration section starts with the keyword ‘DECLARE’ for anonymous block & triggers. For other subprograms, the DECLARE keyword will not be present. The part after the subprogram name definition marks the declaration section.
   • The Execution section should always follow the declaration section.
2. Execution Section– It is the main & mandatory part of the PL/SQL block that actually executes code written inside it. As the PL/SQL expects an executable statement from this block, it cannot be an empty block, so it must have at least one valid executable code line in it. Below are some characteristics of the Execution section.
   • The Execution section can have both SQL code & PL/SQL code.
   • As a nested block, it contains one or multiple blocks.
   • The execution section starts with a ‘BEGIN’ keyword.
   • This section should be followed either by the Exception-Handling section (if present) or ‘END.’
3. Exception-Handling Section: An exception is unavoidable in the program, which occurs during runtime. To handle it, Oracle has provided an Exception-handling section in the PL/SQL block. This section can also have PL/SQL statements. It is also an optional section.
   • In the Exception-Handling section, the exception raised in the execution block is handled.
   • It is the last part of the PL/SQL block.
   • Control from the Exception-Handling section can never return to the execution block.
   • It starts with the ‘EXCEPTION’ keyword.
   • It should always be followed by the ‘END’ keyword.

Answer:

• Function: PL/SQL main function is to compute & return only one value. A function in PL/SQL has a return type in its specification & it must return the value specified in that type.
• Procedure: A procedure in PL/SQL does not have a return type & should not return a value, but it can have one return statement that stops its execution & returns to the caller. A procedure helps to return multiple values; else, it is usually similar to a function.
• Package: A package in PL/SQL is a schema object that logically groups the related items, types, & subprograms. It is like a group of variables, functions, record type statement & procedure that provides modularity, due to which it aids app development. The package is used to hide information from unauthorized users.

Answer:

Answer:

Answer:

• NO_DATA_FOUND: It is raised WHEN the “SELECT INTO” statement does not return any rows.
• TOO_MANY_ROWS: It is raised WHEN the “SELECT INTO” statement returns morethan a row.
• VALUE_ERROR: It is raised WHEN an executed statement resulted in numeric, arithmetic, conversion, string, or constraint error. It mainly occurs due to the programmer’s error or invalid data input.
• ZERO_DIVIDE: It raises exceptions WHEN divided with zero.

Answer:

PL/SQL provides several predefined exceptions, executed when the program violates any database rule. For instance, a predefined exception NO_DATA_FOUND is raised when the “SELECT INTO” statement returns no or zero rows. The below table lists some predefined exceptions:

Answer:

A trigger is a PL/SQL program stored in a database. It is executed immediately after or before the execution of INSERT, UPDATE, & DELETE commands.

Answer:

• BEFORE Trigger: This type of trigger executes before the triggering DML statement (INSERT, UPDATE, DELETE) execution. Triggering SQL statement may or may not execute, based on the BEFORE trigger conditions block.
• AFTER Trigger: It executes after the triggering DML statement (INSERT, UPDATE, DELETE) has been executed. Triggering SQL statement execute followed by the code of trigger before performing the Database operation.
• ROW Trigger: It fires for each & every record performing INSERT, UPDATE, DELETE from a database table.
• Statement Trigger: It fires only once for every statement.

Answer:

The stored procedures are explicitly executed by issuing a procedure call statement from another block through a procedure call with arguments. A trigger is implicitly executed whenever any triggering event happens, such as the occurrence of DML statements.

Answer:

A stored procedure refers to a sequence of statements or a named PL/SQL block that performs one or multiple specific functions. It is just like a procedure in other programming languages. The stored procedure is stored in the database as a schema object & can be executed repeatedly. It can also be invoked, nested, & parameterized.

Answer:

PL/SQL needs a special resource to process & retrieve multiple rows; that resource is called a cursor. A cursor means a pointer to a context area or an area of memory containing SQL statements & information for processing them. It is a mechanism where multiple data rows from the database are selected & then each row is processed individually inside the program.

Answer:

The cursor attribute SQL%ROWCOUNT returns a number of rows processed by the SQL statement.

Answer:

A cursor attribute SQL%FOUND returns a Boolean value TRUE if at least one row was processed.

Answer:

A cursor attribute SQL%NOTFOUND would return a Boolean value TRUE if no rows were processed.

Answer:

PL/SQL packages are schema objects that group stored procedures, functions, variables & cursors in one place. It mainly consists of two mandatory parts:

• Package Specifications
• Package body

Answer:

Packages are schema objects that logically group related PL/SQL variables, types, & subprograms. A package consists of two mandatory parts:

• Package Specification: The specification is an interface to a package. It DECLARES the variables, types, cursors, constants, subprograms, & exceptions that can be referenced from outside a package. It contains all information about the package content but excludes codes for subprograms. All objects placed in a specification are called public objects. Any subprogram not in a package specification but coded in the package body is known as a private object.
• Package Body: The package body has codes for several methods declared in the package specification & other private declarations hidden from the code outside the package. The CREATE PACKAGE BODY Statement helps to create the package body.

Answer:

• %ISOPEN: It verifies whether the cursor is open or not.
• %ROWCOUNT: It returns the no. of rows affected by the DML operations: INSERT, DELETE, UPDATE, & SELECT.
• %FOUND: It checks whether a cursor has fetched any row; if yes – TRUE.
• %NOTFOUND: It checks whether a cursor has fetched any row; if no – TRUE.

Answer:

Answer:

• %TYPE is used to declare a variable with the same type as a database table column. TYPE is used with a column name preceded by a table name to determine the datatype & variable length at the runtime.
• %ROWTYPE is used to declare a variable having the same no. of variables inside it as no. of columns in the table. In that case, columns selected with a SELECT statement must match with variables inside a ROWTYPE variable; if not, then individually refer to the variables inside the ROWTYPE variables.