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.
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;