PL / SQL IN DBMS

                                         PL / SQL

  Pl/SQL 

  state Loops in PL/SQL

  Built in Function 

   Cursor Management 

   Exception

INTRODUCTION 

 PL/SQL stands for Procedural Language/SQL. PL/SQL

extends SQL by adding constructs found in procedural languages, 

resulting in a structural language that is more powerful than 

SQL.PL/SQL is not case sensitive. ‘C’ style comments (/* ……… */) 

may be used in PL/SQL programs whenever required. 

 All PL/SQL programs are made up of blocks, each block performs 

a logical action in the program. A PL/SQL block consists of three parts

 1. Declaration section 

 2. Executable section 

 3. Exception handling section 

Only the executable section is required. The other sections are 

optional. 

A PL/SQL block has the following structure : 

 DECLARE 

 /* Declaration section */ 

 BEGIN 

 /* Executable section */ 

 EXCEPTION 

 /* Exception handling section */ 

 END; 

1. Declaration section : 

 This is first section which is start with word Declare. All the 

identifiers (constants and variables) are declared in this section before 

they are used in SELECT command. 

2. Executable section : 

 This section contain procedural and SQL statements. This is the 

only section of the block which is required. This section starts with 

‘Begin’ word. 

• The only SQL statements allowed in a PL/SQL program are 

SELECT, INSERT, UPDATE, DELETE and several other 

data manipulation statements. 

• Data definition statements like CREATE, DROP or ALTER 

are not allowed. 

• The executable section also contains constructs such as 

assignments, branches, loops, procedure calls and trigger .

3. Exception handling section : 

 This section is used to handle errors that occurs during execution 

of PL/SQL program. This section starts with ‘exception’ word . 

 The ‘End’ indicate end of PL/SQL block. 

 Oracle PL/SQL programs, can be invoke either by typing it in 

sqlplus or by putting the code in a file and invoking the file. To execute 

it use ‘/’ on SQL prompt or use ‘.’ and run. 

ARCHITECUTRE OF PL/SQL 

 The PL/SQL compilation and run-time system is a technology, not 

an independent product. Think of this technology as an engine that 

compiles and executes PL/SQL blocks and subprograms. The engine 

can be installed in an Oracle server or in an application development 

tool such as Oracle Forms or Oracle Reports. So, PL/SQL can reside 

in two environments : 

 1. The Oracle server 

 2. Oracle tools. 

 These two environments are independent. PL/SQL is bundled with 

the Oracle server but might be unavailable in some tools. In either 

environment, the PL/SQL engine accepts as input any valid PL/SQL 

block or subprogram. Fig. 4.1 shows the PL/SQL engine processing 

an anonymous block. The engine executes procedural statements but 

sends SQL statements to the SQL Statement Executor in the Oracle 

server.

 FUNDAMENTALS OF PL/SQL 

 PL/SQL DATA TYPES 

 PL/SQL and Oracle have their foundations in SQL. Most PL/SQL 

data types are native to Oracle’s data dictionary, there is a very easy 

integration of PL/SQL code with the Oracle Engine. 

 The default data types that we can declare in PL/SQL are number

(for storing numeric data), char (for storing character data), date (for 

storing date and time data) boolean (for storing TRUE, FALSE or 

NULL). number, char and date data types can have NULL values. 

Here, we explain two data types, 

 1. Variable, 2. Constant. 

1. Variables and types of declaration in PL/SQL : 

The SELECT statement has a special form in PL/SQL in which a 

single tuple is placed in variables. The information from the database 

is transferred into variables which is used in PL/SQL programs. Every 

variable has a specific type associated with it. 

 That type can be : 

 1. A generic type used in PL/SQL 

 2. A type same as used by SQL for database columns. 

 The most commonly used generic type is NUMBER. Variables of 

type NUMBER can hold either an integer or a real number.

PL SQL Expressions

Expressions are a composite of operators and operands . In the case 

of a mathematical expression ,the operand is the number and operator 

is the symbol such as + or – that acts on the operand. The expression 

value is the evaluated total of the operands using the operators. 

Operators are divided into categories that describe the way that act 

upon operands. 

-Comparison operators are binary, meaning they work with two 

operands. Examples of comparison operators are the greater than (>) 

,less than(<) and equal(=) signs ,among others. 

-Logical operators include AND,OR and NOT 

-Arithmetic operators include 

addition/positive(+),subtraction/negative(-),multiplication(*),and 

division(/). 

-The assignment operator is specific to PL/SQL and is written as 

colon-equal (:=) 

-The lone character operator is a double pipe(||) that joins two strings 

together, concatenating the operands. 

-Other basic SQL operators include IS NULL, IN and BETWEEN. 

CURSOR MANAGEMENT IN PL/SQL 

 Whenever, a SQL statement is issued the Database server opens 

an area of memory is called Private SQL area in which the command 

is processed and executed. An identifier for this area is called a 

cursor. 

 When PL/SQL block uses a select command that returns more 

than one row, Oracle displays an error message and invokes the 

TOO_MANY_ROWS exception. To resolve this problem, Oracle uses 

a mechanism called cursor. 

 There are two types of cursors. 

 1. Implicit cursors 

 2. Explicit cursors 

 PL/SQL provides some attributes which allows to evaluate what 

happened when the cursor was last used. You can use these 

attributes in PL/SQL statements like functions but you cannot use 

them within SQL statements. 

 The SQL cursor attributes are : 

1. %ROWCOUNT : The number of rows processed by a SQL 

statement. 

2. %FOUND : TRUE if at least one row was processed. 

3. %NOTFOUND : TRUE if no rows were processed. 

4. %ISOPEN : TRUE if cursor is open or FALSE if cursor has not 

been opened or has been closed. Only used with explicit cursors.

EXCEPTION (ERROR) HANDLING 

 The Exception section in PL/SQL block is used to handle an error 

that occurs during the execution of PL/SQL program. If an error occurs 

within a block PL/SQL passes control to the EXCEPTION section of 

the block. If no EXCEPTION section exists within the block or the 

EXCEPTION section does not handle the error that's occurred then 

the error is passed out to the host environment. 

 Exceptions occur when either an Oracle error occurs (this 

automatically raises an exception) or you explicitly raise an error or a 

routine that executes corrective action when detecting an error. Thus 

Exceptions are identifiers in PL/SQL that are raised during the 

execution of a block to terminate its action. 

 There are two classes of exceptions, these are : 

1. Predefined exception : 

 Oracle predefined errors which are associated with specific error 

codes. 

2. User-defined exception : 

 Declared by the user and raised when specifically requested 

within a block. You can associate a user-defined exception with an 

error code if you wish.