PL/SQL Interview Question

• Data types are NUMBER, CHAR/VARCHAR2, DATE & BOOLEAN.

• Arrays are not allowed & only one identifier per line is allowed.

• Attributes of PL/SQL objects are %TYPE, %ROWTYPE.

• PL/SQL Block is a standard PL/SQL code segment. Block consists of three parts.

• Declarative Section for variables, constants & exceptions. This section is optional.

• Executable Section which is mandatory.

• Exception Handlers which is optional.

• PL/SQL supports only DML i.e. INSERT, UPDATE, DELETE & SELECT...INTO.

• SQL Functions can be referenced within a SQL statement i.e. Numeric (SQRT,ROUND,POWER),

• Character (LENGTH,UPPER), DATE (ADD_MONTHS,MONTHS_BETWEEN) &Group (AVG,MAX,COUNT). Most SQL functions are available outside SQL statement except for group functions.

Code Simple Loops repeats a sequence of statements multiple times.
Syntax : LOOP
<Sequence of Statements>
END LOOP;
Code Numeric FOR Loops repeat a sequence of statements a fixed number of times.
Syntax : FOR <index> IN [[ REVERSE ]] <integer>..<integer> LOOP
<sequence of statements>
END LOOP;
<index> is implicitly of type number. Defined only within the loop & Value can be referenced in an expression, but a new value cannot be assigned to the index within the loop.
Code While Loops repeats a sequence of statements until a specific condition is no longer TRUE.
Syntax : WHILE <condition> LOOP
<sequence of statements>
END LOOP;
<condition> can be any legal PL/SQL condition & statements will be repeated as long as condition evaluates to TRUE.

Code GOTO Statements jumps to a different place in the PL/SQL block.
Syntax : GOTO label_name;
Legally use a GOTO a statement that is in the same sequence of statements as the GOTO.
In the sequence of statements that encloses the GOTO statement (outer block).
Labels can label any statement. Used as targets for GOTO statements, use labels for blocks and loops, Label a block to allow referencing of DECLAREd objects that would otherwise not be visible because of scoping rules, Label a block to allow a variable to be referenced that might be hidden by a column name, Label a loop to allow an object to be reference that would otherwise not be visible because of scoping rules & Label an EXIT as a convenient way to specify exits from outer loops.

Cursors are associated with every SQL DML statement processed by PL/SQL. Two types are Explicit i.e. Multiple row SELECT statements & Implicit i.e. INSERT, UPDATE, DELETE & SELECT...INTO statements. Implicit cursor is called the SQL cursor-it stores info concerning the processing of the last SQL statement not associated with an explicit cursor. OPEN, FETCH & CLOSE do not apply. All cursor attributes apply.

Cursor has to be explicitly defined when a query returns multiple rows to process beyond the first row returned by the query & to keep track of which row is currently being processed.

Declare the cursor to associate its name with a SELECT statement.
Syntax : DECLARE
CURSOR <cursor_name>
IS <regular_select_statement>;
Open the cursor to process the SELECT statement and store the returned rows in the cursor.
Syntax : OPEN <cursor_name>;
Fetch data from the cursor and store it in specified variables.
Syntax : FETCH <cursor_name> INTO <var1, var2...>;
Close the cursor to free up resources. Cursors must be closed before they can be reopened.
Syntax : CLOSE <cursor_name>
Explicit Cursor Attributes are %NOTFOUND, %FOUND, %ROWCOUNT & %ISOPEN.
Reference the current cursor row with the WHERE CURRENT OF statement. The cursor must be declared with a FOR UPDATE OF clause.

Syntax : WHERE CURRENT OF <cursor_name>
Reference Cursors FOR Loops to specify a sequence of statements to be repeated once for each row that is returned by the cursor with the Cursor FOR Loop.
Syntax : FOR <record_name> IN <cursor_name> LOOP
--statements to be repeated go here
END LOOP;

Cursor FOR loops (CFL) are similar to Numeric For Loops(NFL). CFL specify a set of rows from a table using the cursor's name. NFL specify an integer range. CFL record takes on vales of each row. NFL index takes on each value in the range. Record_name is implicitly declared as
record_name cursor_name%ROWTYPE

When a CFL is initiated, an implicit OPEN cursor_name is initiated.

For each row that satisfies the query associated with the cursor, an implicit FETCH is executed into the components of record_name.

When there are no more rows left to FETCH, an implicit CLOSE cursor_name is executed and the loop is exited.
Declare cursors to use parameters
Syntax : DECLARE
CURSOR <cursor_name> [[(param_name param_type)]]
IS <regular select statement>;

Exception Handlers : In PL/SQL, errors are called exceptions. When an exception is raised, processing jumps to the exception handlers. An exception handler is a sequence of statements to be processed when a certain exception occurs. When an exception handler is complete, processing of the block terminates. Two types are Predefined Internal Exceptions which corresponds to approximately 20 common ORACLE errors & Raised automatically by PL/SQL in response to an ORACLE error.

Eg.too_many_rows,no_data_found,invalid_cursor,value_errori.e. arithmetic,numeric,string,conversion or constraint error occurred, zero_divide, dup_val_on_index,cursor_already_open etc.

User-Defined Exceptions must be declared & must be RAISEd explicitly.

Only one handler per block may be active at a time & If an exception is raised in a handler, the search for a handler for the new exception begins in the enclosing block of the current block.

Exception-Init : Exceptions may only be handled by name not ORACLE error number. So, name an ORACLE error so that a handler can be provided specifically for that error.

Syntax : PRAGMA EXCEPTION_INIT (<user_defined_exception_name>, <ORACLE_error_number>);
SQLCODE & SQLERRM provides info on the exception currently being handled & especially useful in the OTHERS handler.

• SQLCODE returns the ORACLE error number of the exception, or 1 if it was a user-defined exception.

• SQLERRM returns the ORACLE error message associated with the current value of SQLCODE & can also use any ORACLE error number as an argument.

• SQLCODE & SQLERRM cannot be used within a SQL statement. If no exception is active SQLCODE = 0 & SQLERRM = 'normal, successful completion'.