Structure of PL/SQL

Structure of PL/SQL
Standard PL/SQL code segment is called a Block
A block consists of three parts or sections
Declaration Part
Executable Part
Exception Handling Part


Declaration Part
optional part where variables are defined
Executable Part
mandatory part which consists of executable statements
Exception Handling Part
optional part which consists of code for handling errors (runtime)


Pl/SQL Files -à
PL/SQL programs can be written in any editor and saved as files with .sql extension
Can also use “ED” command in SQL*Plus to create a PL/SQL program file
Use the “@ ” command to execute a PL/SQL program file

Variables --
Used to store results of a query for later processing, or to calculate values to be inserted into database tables
Can be used anywhere in an expression, either in SQL or PL/SQL statements
Must be declared before referencing it in other statements, including other declarative statements
Are declared by specifying the name along with the datatype
Can be declared to be of any datatype native to Oracle
Examples
oldfare NUMBER(5);
m_name VARCHAR(15);

(Note – Set Serveroutput On has to be given when a session starts for displaying the output statements_)
declare
x number;
begin
x := 67;
dbms_output.put_line(x);
dbms_output.put_line('The value of x is '|| x);
end;
Declaring variable in declare block.
Assigning value in in begin block using := .
Output statement is dbms_output.put_line
Concatenation operator is ||
Command terminator is ; after end

Declaring and initializing variables together
declare
y number := 100;
begin
dbms_output.put_line('The value of y is '|| y);
end;
-------------------------------------------------------------------------------
Taking value from the user using &


declare
z number;
a varchar2(10);
begin
z := &z;
a := '&a';
dbms_output.put_line('Z is '|| z);
dbms_output.put_line('A is '|| a);
end;
-------------------------------------------------------------------------------

/*Cannot declare or initialize more than one variable simultaneously*/
declare
a number;
b number;
c number;
begin
a := 67; b := 90; c := 87;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;

A constant number has to declared and initialized in the declare block only using
CONSTANT keyword. Value cannot be changed

declare
r CONSTANT number :=100;
begin
/* r := r + 100; Not possible*/
dbms_output.put_line(r);
end;

/*Assigning value to variable from a column of a table using select into clause*/
declare
x number;
begin

Select sal Into x from emp
where ename = 'SMITH';
dbms_output.put_line('Salary of Smith is '|| x);
end;





/* Selecting ename,sal from emp
Use of more than one columns value with Into clause*/
declare
n varchar2(50);
s number;
begin
select ename, sal Into n, s
from emp
where ename = 'SMITH';
dbms_output.put_line(n);
dbms_output.put_line(s);
end;

% Type Attribute –
Provides datatype of a variable or column
Useful when declaring a variable that refers to a column in a database
exact datatype of column need not be known
if column definition changes, variable datatype changes accordingly at runtime
Example
oldfare fare.first_fare%TYPE;
newfare oldfare%TYPE;

declare
a emp.ename%type;
b emp.sal%type;
c emp.deptno%type;
/*Using %TYPE attribute for variable data type*/
begin
select ename,sal,deptno
into a,b,c
from emp
where ename = 'KING';
dbms_output.put_line(a ||'-'|| b ||'-' || c);
end;

%RowType Attribute –
Useful when declaring a record variable having same structure as a row in a table or view, or as a row fetched from a cursor
Fields in the record have same names and datatypes as the columns in the table/view
Example
emp_rec employee%ROWTYPE;
A specific field can be referenced using
emp_rec.emp_num;

declare
E emp%rowtype;
/*rowtype attribute holds the datatype of the columns of the
entire row*/
begin
select * INTO E
from emp
where ename = 'MARTIN';
dbms_output.put_line(E.sal);
dbms_output.put_line(E.ename);
dbms_output.put_line(e.deptno);
end;

Conditional Statements – IF
The selection structure tests a condition, then executes one sequence of statements instead of another, depending on the condition
There are three forms of statements
IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
Sequence of statements is executed only if the condition evaluates to TRUE
If condition evaluates to FALSE or NULL, it does nothing
In either case control passes to next statement after the IF-THEN structure
IF THEN
statements;
END IF;
Sequence of statements in the ELSE clause is executed only if the condition evaluates to FALSE or NULL
IF THEN
statements;
ELSE
statements;
END IF;
--------------------------------------------------------------------------------











declare
/*Simple if condition */
x number;
begin
x := &x;
if x >= 35 then
dbms_output.put_line('Passed');
else
dbms_output.put_line('Failed');
end if;
end;
-----------------------------------------------------
IF-THEN-ELSIF Structure

This construct allows selection of action from several mutually exclusive alternatives
The IF statement can have any number of ELSIF clauses
The final ELSE is optional
Conditions are evaluated one by one from top to bottom


Syntax
IF THEN
statements;
ELSIF THEN
statements;
ELSIF THEN
statements;
ELSE
statements;
END IF;
Example 1 –

Declare
y number;
/*Multiple ifs */
Begin
y := &y;

if y >= 70 then
dbms_output.put_line('Distinction');
elsif y >= 60 then
dbms_output.put_line('First class');
elsif y >= 50 then
dbms_output.put_line('Second class');
elsif y >= 35 then
dbms_output.put_line('Passed');
else
dbms_output.put_line('Failed');
end if;
end;

Example 2
create table adm
(Name varchar2(30),
Marks number(3),
College varchar2(30),
Fees number(5));

/*Use of multiple if's
Accept name and marks from user.
Depending upon marks entered the college and fees should be decided
and the record should be entered in the adm table.*/




Declare
n adm.name%type;
m adm.marks%type;
c adm.college%type;
f adm.fees%type;

Begin
n := '&n';
m := &m;

if m >= 95 then
c := 'COEP';
f := 10000;
elsif m >= 90 then
c := 'MIT';
f := 15000;
elsif m >= 85 then
c := 'VIT';
f := 22000;
elsif m >= 80 then
c := 'D Y Patil';
f := 27000;
elsif m >= 75 then
c := 'Pune Vidyarthi';
f := 33000;
else
dbms_output.put_line('Cannot get admission');
end if;

if c is not null and f is not null then
dbms_output.put_line('Your College is '|| c || ' and fees are ' || f);
Insert into adm
values(n,m,c,f);
commit;
end if;

end;