Controlling Flow of Execution in plsql

Controlling Flow of Execution

IF Statements


Syntax:

IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;


Simple IF Statement

DECLARE
myage numeric:=31;
BEGIN
IF myage < 11
THEN
raise notice ' I am a child ';
END IF;
END;


IF THEN ELSE Statement

DECLARE
myage numeric:=31;
BEGIN
IF myage < 11
THEN
raise notice ' I am a child ';
ELSE
raise notice ' I am not a child ';
END IF;
END;


CASE Expressions: Example

DECLARE
grade varchar := UPPER('a');
appraisal varchar(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
END;

The %TYPE Attribute in PL/pgSQL

The %TYPE attribute

• Is used to declare a variable according to : 
• A database column definition
• Another declared variable
• Is prefixed with :
• The database table and column
• The name of the declared variable

Declaring Variables with the %TYPE Attribute


Syntax :
identifier table.column_name%TYPE;


Examples :
emp_name emp.ename%TYPE;
balance NUMERIC;
min_balance balance%TYPE := 1000;