Email Us : [email protected]
Free Training > SQL PL / SQL Forms Reports Oracle Applications
< Previous Next >

 
PL / SQL Introduction
PL / SQL:


1) What is Pl/SQL ?
It is extension to SQL language.
PL/SQL = SQL + Programming features.

The following are the advantages of PL/SQL
1) We can use programming features like If stmt, loops, branching etc;

2) We can have user definied error messages by using the concept of exception handling.

3) We can perform related actions by using the concept of triggers.

4) Pl/SQL helps in reducing the network traffic.

PL/SQL Block structure:
----------------------

declare
........... -- Declare section
...........
...........
begin
........... -- Executable section
..........
...........
...........
...........
exception
.......... -- Exception section
..........
end;
/

A Pl/SQL block contains 3 sections.

1) Declare section
2) Executable section
3) Exception Section

1) Declare section:
-------------------
It is used to declare local variables, Cursor , exceptions etc;. All the lines between declare and begin is called declare section.This section is optional.

2) Executable Section:
----------------------------

The actual task which should be done is written in the executable section. All the lines between Begin and exception keywords is called as Executable section.
This section is mandatory

3) Exception Section:
-----------------------
If an exception is raised in the executable section,
control enters into exception section.
All the lines between exception and end is called exception section. This section is optional.

Ex1:
----

Write a PL/SQL block to display 'Hello World'.

For this program, we do not need any local variables.
So, we can start the program by using keyword begin.

Before the running this program, we need to make the environment variable serveroutput to ON.

To command to make the serveroutput to ON]

SQL> Set serveroutput on

Begin
dbms_output.put_line('Hello World');
end;
/
Hello World

Pl/SQL procedure successfully completed.

Ex 2:
-------
Write a PL/SQL block to calculate sum of two numbers.

For this program, we need 3 variables, so we need declare section.

Syntax to declare variable:
----------------------------

<variable> <datatype>(size);

Declare
a number(3);
b number(3);
c number(4);

begin
a :=10;
b :=20;
c := a+b;

dbms_output.put_line ( ' The sum is ...'||c);
end;
/
The sum is ...30

Pl/SQL procedure successfully completed.

In the above program, there are two important points to learn.

i) := is assignment operator, which is used to assign value from the right hand side to the variable in the left hand side.

ii) || (pipe) is concatenation operator.

We can initilize at the time of declaration.
declare
a number(3) :=10;
b number(3) :=20;

In the abvoe program, we have hard coded the value 10 and 20 in the program. Instead of hard coding the value, we can accept the values from the user.

Ex 3:
---

Write a program to accept two values from the user and display its sum.

Declare
a number(3);
b number(3);
c number(4);

begin

a := &a;
b := &b;
c := a+b;

dbms_output.put_line('The sum is ...'||c);
end;
/
Enter a value for A:40

Enter a value for B:30

The sum is ...70

Pl/SQL procedure successfully completed.

Note: & operator is used to accept value from the user.

Ex 4:
------
Write a PL/SQL block to accept empno and increments his salary by 1000.

Note: To increment the salary (change the value) in a table, we need to use update command.

Declare
l_empno number(4);

begin
l_empno := &empno;

update emp set sal = sal+1000
where empno = l_empno;

end;
/
Enter a value for empno: 7900

Procedure successfully completed.

To make the above update command permanent, we can use commit after update command in PL/SQL block.

ex:
---

Declare
l_empno number(4);

begin
l_empno := &empno;

update emp set sal = sal+1000
where empno = l_empno;

commit;

end;
/

Writing a select stmt in a PL/SQL Block:
-------------------------------------------

Write a pl/SQL block which accepts empno and display ename and salary.

As ename and sal are the values present in the emp table, to get those values we need to write a select stmt.

Note: Every select stmt in a PL/SQL block should have into clause.

Declare
l_empno number(4);
l_ename varchar2(20);
l_sal number(5);
begin

l_empno := &empno;

select ename,sal into l_ename, l_sal from emp
where empno = l_empno;

dbms_output.put_line(l_ename||'....'||l_sal);
end;
/

Note:
--------
As the above select stmt selects two columns, we need two local variable to catch the value returned by the select stmt.

Using %TYPE attribute:
------------------------
%TYPE attribute is used to declare the local variables.

Instead of hardcoding the datatype and size for local variable, we can use %TYPE attribute.

Ex:
l_ename varchar2(20); -- we are hard coding datatype and size

l_ename emp.ename%TYPE; --- The datatype of ename column
of emp table is applicable to the local variable.

The above program, i use %TYPE attribute to declare local variables.

Declare
l_empno emp.empno%TYPE;
l_ename emp.ename%TYPE;
l_sal emp.sal%TYPE;

begin

l_empno := &empno;

select ename,sal into l_ename, l_sal from emp
where empno = l_empno;

dbms_output.put_line(l_ename||'....'||l_sal);
end;
/

Using %ROWTYPE Attribute:
--------------------------

A ROWTYPE variable is capable of holding complete row
of table.

Ex:
------

Write a PL/SQL Block which accepts an empno and display ename, sal, hiredate and job.

declare
l_empno emp.empno%TYPE;
l_row emp%ROWTYPE;
begin

l_empno := &empno;
select * into l_row from emp
where empno = l_empno;

dbms_output.put_line(l_row.ename);
dbms_output.put_line(l_row.sal);
dbms_output.put_line(l_row.hiredate);
dbms_output.put_line(l_row.job);

end;
/

Note: we cannot print a ROWTYPE variable, we can print a value of a ROWTYPE variable.


< Previous Next >