PL/SQL FAQs

1)      What is PL/SQL and its Syntax?

PL/SQL is a Block Structured Programming language which divided and written in logical blocks of code.

Declarations:  It is an optional section and defines all variables, cursors, subprograms and other elements to be used.

Executable: It is a mandatory section. It should have at least one executable line of code, or may be just Null.

Exception Handling: It is an optional section and contains exceptions that handle errors in the program.

DECLARE

<declaration section>

BEGIN

<executable command(s)>

EXCEPTION

<exception handling>

END

2)      What are the Data types in PL/SQL

PL/SQL variables, constants and parameters must have a valid data types which specifies a storage format, constraints and valid range of values.

A)     Scalar datatypes :

a)      Numeric Datatype with Subtypes: PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, NUMBER(prec, scale), DEC(prec, scale), DECIMAL(prec, scale), NUMERIC(pre, scale), DOUBLE PRECISION, FLOAT, INT, INTEGER, SMALL INT, REAL

b)      Character Datatype with Subtypes : CHAR, VARCHAR2, RAW, NCHAR, NVARCHAR2, LONG, LONG RAW, ROWID, UROWID

c)       Boolean Datatype: It stores logical values that are used in logical operations. SQL has no data type equivalent to BOOLEAN. Therefore BOOLEAN values cannot be used in

                                                                                               i.      SQL Statements

                                                                                             ii.      Built in SQL functions such as TO_CHAR

                                                                                            iii.      PL/SQL functions invoked from SQL Statements

d)      Date time and Interval Types: This is used to store fixed length date times, which include the time of day in seconds. Valid date range from Jan 1, 4712 BC to Dec 31, 9999 AD. The default date format is set by oracle initialization parameter NLS_DATE_FORMAT. Default might be DD-MON-YY

Interval Types: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR

B)      LOB Data types : BFILE(4GB), BLOB(8 TO 128TB), CLOB(8 TO 128TB), NCLOB(8 TO 128TB)

C)      Composite: Data items that have internal components that can be accessed individually.

Ex: Collections and Records

D)     Reference : Pointers to other data items

3)      Define User Defined Subtypes

A subtype is subset of another data type, which is called its base type.

Ex: SUBTYPE CHARACTER IS CHAR

        SUBTYPE INTEGER IS NUMBER (38, 0)

4)      What is NULL in PL/SQL

PL/SQL NULL values represent missing or unknown data and they are not an integer, a character or any other data type. Note that NULL is not the same as empty data string or null character value ‘\0’. A NULL can be assigned but cannot be equated with anything, including itself.

5)      What is a variable and syntax

It is nothing but a name given to the storage area that our programs can manipulate.

Declare & Initialization Syntax :

Variable_name  [CONSTANT] data type [NOTNULL] [:=|DEFAULT Initial _Value]

Ex : PI CONSTANT double precision :=3.1415

6)      What is Local Variables & Global Variables

Local Variables: Variables declared in inner block and not accessible to outer blocks

Global Variables: Variables declared in outermost block or a package

7)      What is the purpose of SELECT INTO Statement

SELECT INTO Statement of SQL is used to assign values to PL/SQL variables.

8)      What is a Constant

A Constant holds a value that once declared, does not change in the program

9)      Explain PL/SQL Literals

A Literal is an explicit numeric, character, string or Boolean value not represented by an identifier.

Ex : ‘That’’s is the point’

10)   What is an Operator and types of an Operators

An operator is a symbol that tells the compiler to perform specific mathematical or logical manipulations.

A)     Arithmetic Operators :   +,-,*,/,**

B)      Relational Operators:    =, != or <> or ~=, >, <, >=, <=

C)      Comparison Operators: LIKE, BETWEEN,IN, ISNULL

D)     Logical Operators: AND, OR, NOT

E)      String Operators

PL/SQL Operator Precedence

Operator	Operation
**	Exponentiation
+,-	Identity, negation
*,/	Multiplication, division
+,-,||	Addition, Subtraction, Concatenation
=, <, >, <=, >=, <>, !=, ~=, ^=, ISNULL, LIKE, BETWEEN, IN	Comparison
NOT	Logical Negation
AND	Conjunction
OR	Inclusion

Operators with Highest precedence appear at top.

11)   What are Conditional Statements in PL/SQL and its types

Decision making structures require that the programmer specify one or more conditions to be evaluated or tested by the program

A)     IF-THEN Statement

IF condition THEN

S1;

END IF;

B)      IF-THEN-ELSE Statement

IF condition THEN

S1;

ELSE

S2;

END IF;

C)      IF-THEN-ELSIF Statement

·         An IF-THEN statement can be followed by an optional ELSIF… ELSE statement

·         Once an ELSIF succeeds, none of the remaining ELSIF’s or ELSE will be tested.

IF condition1 THEN

S1;

ELSIF condition2 THEN

S2;

ELSIF condition3 THEN

S3;

ELSE

S4;

END IF;

D)     Case Statement

Like IF Statement, CASE statement selects one sequence of statements to execute.

To select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions

CASE selector

WHEN ‘value1’ THEN S1;

WHEN ‘value2’ THEN S2;

WHEN ‘value3’ THEN S3;

………..

ELSE Sn;                -- default case

END CASE

E)      Searched CASE Statement

This statement has no selector, and it’s WHEN clauses contain search conditions that give Boolean values.

CASE

WHEN selector = ‘value1’ THEN S1;

WHEN selector = ‘value2’ THEN S2;

WHEN selector = ‘value3’ THEN S3;

……..

ELSE Sn;                -- default case

       END CASE;

F)      Nested IF-THEN-ELSE

IF condition1 THEN

IF condition2 THEN

S1;

END IF;

ELSE

S2;

END IF;

12)   What is a LOOP and its types

A LOOP statement allows executing a statement or group of statements multiple times

A)     Basic LOOP

LOOP

Sequence of Statements;

END LOOP;

B)      WHILE LOOP

It executes a target statement as long as a given condition is true.

WHILE condition LOOP

Sequence of Statements;

END LOOP;

C)      FOR LOOP

It is a repetitive control structure that allows efficiently writes a loop that needs to execute a specific number of times

FOR counter IN intital_value .. final_value LOOP

Sequence of Statements;

END LOOP;

D)     Nested LOOPS

LOOP

Sequence of Statements1;

        LOOP

                Sequence of Statements2;

        END LOOP;

END LOOP;

13)   What are the LOOP Control Statements

Loop control statements change execution from its normal sequence.

A)     EXIT

·         When EXIT statement is encountered inside a loop, the loop is immediately terminated and program control resumes at the next level following the loop

·         In Nested Loops, the EXIT statement will stop the execution of the innermost loop and start executing the next line of code after the block.

EXIT-WHEN statement

B)      CONTINUE

This statement causes the loop to skip the remainder of its body and immediately retest its condition prior to reiterating. In other words, it forces the next iteration of the loop to take place, skipping any code in between.

C)      GOTO

This statement provides an unconditional jump from GOTO to the labeled statement in the same program.

Note: Use of GOTO statement is highly discouraged in any programming language because it makes difficult to trace the control flow of program.

Restrictions with GOTO:

·         GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement or sub-block.

·         GOTO statement cannot branch from one IF statement clause to another, or from one CASE statement WHEN clause to another.

·         GOTO statement cannot branch from an outer block into a sub-block

·         GOTO statement cannot branch out of a sub-program. To end sub-program early, either use the RETURN statement or have GOTO branch to a place right before the end of the sub-program

·         GOTO statement cannot branch from an exception handler back into the current BEGIN-END block. However, a GOTO statement can branch from an exception handler into an enclosing block.

14)   What is a String and its types?

String is a sequence of characters with an optional size specification.

A)     Fixed-Length Strings               B) Variable-Length Strings            c) CLOBs

String Functions and Operators:

SNo	Function & Purpose
1	ASCII(X)
2	CHR(X)
3	CONCAT(X,Y)
4	INITCAP(X)
5	INSTR(X, find_string[,start][,occurance])
6	INSTRB(X)
7	LENGTH(X)
8	LENGTHB(X)
9	LOWER(X)
10	LPAD(X, Width[, pad_string])
11	LTRIM(X [, trim_string])
12	NANVL(X,value)
13	NLS_INITCAP(X)
14	NLS_LOWER(X)
15	NLS_UPPER(X)
16	NLSSORT(X)
17	NVL(X, value)
18	NVL2(X, value1, value2)
19	REPLACE(X, search_string, replace_string)
20	RPAD(X, Width[, pad_string])
21	RTRIM(X [, trim_string])
22	SOUNDEX(X)
23	SUBSTR(X, start [, length])
24	SUBSTRB(X)
25	TRIM([trim char from ] X)
26	UPPER(X)

15)   What is an Array and VARRAY

PL/SQL provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. An array is a part of collection type data and it stands for variable-size arrays. Each element in a varray has an index associated with it.

Syntax:

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Ex : CREATE OR REPLACE TYPE namearray IS VARRAY(3) of VARCHAR2(10)

Note :

·         In Oracle, starting index for varrays is always 1

·         Varrays are one dimensional arrays

·         A Varray is automatically NULL when it is declared and must be initialized before its elements can be referenced

16)   What is a subprogram

Subprogram is a program unit/module that performs a particular task. A subprogram can be invoked by another subprogram or program which is called the calling program

A subprogram can be created

·         At schema level

·         Inside a package

·         Inside a PL/SQL block

There are two kinds of subprograms are procedures and functions

Functions: These subprograms return a single value, mainly used to compute and return a value

Syntax:

CREATE [OR REPALCE] FUNCTION function_name

[(parameter_name [IN|OUT|IN OUT] type[, ….])]

RETURN return_datatype

{IS/AS}

BEGIN

<function_body>

END [function_name];

Procedures: These subprograms do not return a value directly, mainly used to perform an action

Syntax :

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN|OUT|IN OUT] type[, ….])]

{IS/AS}

BEGIN

<procedure_body>

END;

Executing a Standalone procedure:

A standalone procedure can be called in two ways:

·         Using the EXECUTE keyword

·         Calling the name of the procedure from a PL/SQL block

Syntax: EXECUTE Procedure_name;

Deleting a Standalone procedure:

DROP PROCEDURE procedure_name;

17)   What are the parameter modes in PL/SQL

There are 3 types of parameter modes in PL/SQL are

A)     IN: It is read-only parameter. IN parameter is used to pass a value to the subprogram. We can pass a constant, literal, initialized variable, or expression as an IN parameter

B)      OUT: An OUT parameter returns a value to the calling program. An OUT parameter acts like a variable. We can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.

C)      INOUT: An INOUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and it can be read.

The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value.

Methods for passing parameters

Actual parameters could be passed in 3 ways

·         Position notation

Findmin(a,b,c,d);

First actual parameter (a) is substituted for first formal parameter(x). Similarly, for second, third, fourth

·         Named notation

Findmin(x=>a, y=>b, z=>c, m=>d);

·         Mixed notation

In this, we can mix both notations. However, positional notation should precede Named notation

Findmin(a, b, c, m=>d);

18)   What is a Recursive Function

When a subprogram calls itself, it is reffered to as a recursive call and the process is known as recursion

Ex : Factorial

19)   What is a cursor and its types

A cursor is a pointer to the context area(memory area). PL/SQL controls the context area through a cursor. A cursor holds the rows(one or more) returned by a SQL statement. The set of rows the cursor holds is known as active set.

There are two types of cursors:

A)     Implicit Cursors

Implicit cursors are automatically created by oracle whenever SQL statement is executed. Whenever DML statement is issued, implicit cursor is associated with this statement.

Attribute	Description
%FOUND	Returns TRUE if a DML statement affected one or more rows or a SELECT INTO statement returns one or more rows. Otherwise, it returns FALSE
%NOTFOUND	Logical Opposite of %FOUND
%ISOPEN	Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement
%ROWCOUNT	Returns the number of rows affected  by DML statement, or returned by a SELECT INTO statement

SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use in FORALL statement.

Any SQL cursor attribute will be accessed as sql%attribute_name

B)      Explicit Cursors

Explicit cursors are programmed defined cursors for gaining more control over the context area. It is created on a SELECT statement which returns more than one row.

Syntax: CURSOR cursor_name is select_statement;

Working with an explicit cursor involves 4 steps:

·         Declaring the cursor for initializing in the memory

 CURSOR c1 is select id, name from customers;

·         Opening the cursor for allocating the memory

OPEN c1;

·         Fetching the cursor for retrieving the data

Fetching the cursor involves accessing one row at a time

FETCH c1 INTO c_id, c_name, c_address;

·         Closing the cursor to release the allocated memory

CLOSE c1;

20)   What is a record and its types

A PL/SQL record is a data structure that can hold data items of different kinds. A record consists of different fields, similar to a row of a database table.

PL/SQL can handle following types of records:

·         Table-based records

%ROWTYPE attribute enables a programmer to create Table-based and Cursor-based records

 cust_rec customers%ROWTYPE

·         Cursor-based records

CURSOR cust_cur is select * from customers;

 cust_rec cust_cur%ROWTYPE

·         User-defined records

PL/SQL provides a user-defined record type that allows defining different record structures.  Records consist of different fields.

Defining a record

Accessing Fields

Records as subprogram parameters

Syntax:

TYPE type_name is RECORD

(field_name1 datatype1 [NOTNULL] [:=DEFAULT EXPRESSION],

(field_name2 datatype2 [NOTNULL] [:=DEFAULT EXPRESSION],

……….

(field_namen datatypen [NOTNULL] [:=DEFAULT EXPRESSION],

                                      Record_name type_name;

21)   What is an Exception and its types

An error condition during a program execution is called an exception. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition.

There are 2 types of exceptions:

·         System-defined

Exceptions are raised by the database server automatically whenever there is any internal database error

Syntax:

DECLARE

        <declarations section>

BEGIN

        <executable command(s)>

EXCEPTION

WHEN exception1 THEN

Exception1-handling-statements;

WHEN exception2 THEN

Exception2-handling-statements;
END;

·         Pre-define (Raise Exceptions)

Exceptions can be raised explicitly by the programmer by using the RAISE command

Syntax:

DECLARE

        Exception_name EXCEPTION;

BEGIN

        IF condition THEN

                        RAISE exception_name;

        END IF;

EXCEPTION

WHEN exception_name THEN

Statement;

END;

·         User-defined

PL/SQL allows defining own exceptions according to the need of your program. It must be declared and raised explicitly, using either RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR

Syntax:

DECLARE

        Ex_invalid_id EXCEPTION;

BEGIN

        IF condition THEN

                        RAISE Ex_invalid_id;

        END IF;

EXCEPTION

WHEN Ex_invalid_id THEN

Statement;

END;

22)   What are pre-defined Exceptions

Exception	Oracle Error	SQLCODE	Description
ACCESS_INTO_NULL	06530	-6530	It is raised when NULL object is automatically assigned a value
CASE_NOT_FOUND	06592	-6592	It is raised when None of the choices in the WHEN clauses of a CASE statement is selected and there is no ELSE clause
COLLECTION_IS_NULL	06531	-6531	It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign the values to the elements of an uninitialized nested table or varray
DUP_VAL_ON_INDEX	00001	-1	It is raised when duplicate values of columns are attempted to stored in a column with unique index
INVALID_CURSOR	01001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor
INVALID_NUMBER	01722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number
LOGIN_DENIED	01017	-1017	It is raised when the program attempts to logon to the database with an invalid username and pwd
NO_DATA_FOUND	01403	+100	It is raised when a SELECT INTO statement returns no rows
NOT_LOGGED_ON	01012	-1012	It is raised when a database call is issued without being connected to the database
PROGRAM_ERROR	06501	-6501	It is raised when PL/SQL has internal problem
ROWTYPE_MISMATCH	06504	-6504	It is raised when a cursor fetches value in a variable having incompatible data type
STORAGE_ERROR	06500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted
TOO_MANY_ROWS	01422	-1422	When a SELECT INTO statement returns more than one row
VALUE_ERROR	06502	-6502	When an arithmetic, conversion, truncation, size-constraint error occurs
ZERO_DIVIDE	01476	1476	When an attempt is made to divide a number by zero

23)   What is a Trigger and its types

Trigger is a stored program which are automatically executed or fired when some events occur. Triggers could be defined on the table, view, schema, or database with which the event is associated

DML Triggers (DELETE, INSERT, UPDATE)

DDL Triggers (CREATE, ALTER or DROP)

Database Triggers (SERVERERROR, LOGON, STARTUP or SHUTDOWN)

Benefits of Triggers:

Triggers can be written for the following purposes:

Generating some derived column values automatically

Enforcing referential integrity

Event logging and storing information on table access

Auditing

Synchronous replication of tables

Imposing security authorizations

Preventing invalid transactions

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF}

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD  AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

BEGIN

EXCEPTION
END;