PL/SQL Structures

Using SQL statements, we can retrieve or manipulate data present in a table. Using SQL statements above it is not possible to gain the power of procedural language constructs. This aspect has been taken care of by PL/SQL, which is of a procedural extension of SQL.

• PL/SQL block can contain DML & DCL statements, but not DDL statements - but not DDL statements.

• PL/SQL block can also contain any number of SQL statements integrated with flow of control statements. Using PL/SQL we can also trap runtime errors.

What are PL/SQL Structures?

With PL/SQL, You can use SQL statements to manipulate Oracle data and flow control statements to process data to meet all your requirements. PL/SQL combines the data manipulating power of SQL with the data-processing power of procedural languages. PL/SQL provides many different structures to control the flow of your statement execution and data handling. These structures include the following:

Cursors
Procedures
Functions
Packages

Why use PL/SQL Structures?

Control structures are the most important PL/SQL extensions to SQL. Not only does PL/SQL let you manipulate data, it lets you process the data using conditional, iterative, sequential, and unconditional flow-control statements such as if-then-else, for-loop, while-loop,exit-when,and goto.

How to use PL/SQL Structures?

Control Structures
There are three types of control statements. Conditional control, Iterative control and Sequential control. The conditional control returns a boolean value. The iterative control executes a sequences of statements repeatedly. The sequential statements executes a sequence of statements in the order in which they occur.

Conditional control

The IF statement lets you execute a: series of statements conditionally. There are three forms IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

If-Then

IF associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. The statements are executed only when the condition is TRUE.

IF condition THEN
sequence of statements;
END IF;

Example:

IF Sales > Quata THEN
Combute_bonus(empid);
UPDATE payrole SET pay in pay + bonus WHERE empno_emp id;
END IF;

If-Then-Else

The second form of IF statements adds the keyword ELSE followed by alternative sequence of statements. The sequence of statements in the ELSE clause will be executed only when the condition evaluates to FALSE.

IF condition THEN
sequence of statement
ELSE
sequence of statements
END IF;

Example:

IF trans_type = 'CR' THEN UPDATE accounts SET balance balance + credit WHERE....
ELSE
UPDATE accounts SET balance balance - debit WHERE....
END IF;

If-Then-Elsif

The third form introduces the keyword ELSIF to introduce additional conditions. If any condition evaluates to TRUE then that sequence of statements get executed. We can have any number of ELSIF clause but the last one is optional.

If condition1THEN
sequence of statements;
ELSIF
sequence of statements;
ELSIF
sequence of statement;
END IF;

Example:

If sales>5000 THEN
bonus :=1500;
ELSEIF sales>35000 THEN
bonus :=500;
ELSE
bonus :=100; END IF:

Domain Name Search