Learn PL/SQL CONCEPTS:-
After learning SQL Concepts, Its time to move on PL/SQL,
Oracle
added a procedural programming language known, as PL/SQL (Procedural
Language/SQL).
PL/SQL is a third generation language and contains the standard
programming
constructs.
 |
| PL/SQL |
Anonymous
Block:
An Unnamed block which will not store any where in the database is
know
as Anonymous block.
Block
Structure: PL/SQL programs are divided up into structures known as blocks, with
each
block containing the PL/SQL and SQL statements. The syntax for the structure isgiven
below.
Syntax:
DECLARE
Variable
declaration
Begin
Executable_statements
[Exception
Exception_handling_statements]
End;
Ex:
SQL> SET SERVEROUT ON;
SQL>
Declare
v_1
number;
v_2
number;
v_3
number;
Begin
v_1:=&v_1;
v_2:=&v_2;
v_3:=&v_3;
if
v_1 < v_2 and v_1 < v_3 then
dbms_output.put_line('1st
is small');
elsif
v_2 < v_3 then
dbms_output.put_line('2nd
is small');
else
dbms_output.put_line('3rd
is small');
end
if;
Exception
When
zero_divide then
DBMS_OUTPUT.PUT_LINE(‘Division
by ZERO’);
End;
O/p:
It will ask for the three input values and will display the smallest number in
that.
Difference
between PL/SQL and SQL: In PL/SQL there is no buffer to hold the values
so
we pass into variables. Here in SQL there is buffer to hold the data
temporarily.
Ex:
SQL> Select ename from emp where is empno = 7788;
SQL>
Declare
V_name
varchar2(10);
Begin
Select
ename into v_name from emp where empno = 7788;
Oracle
Applications 11.5.9 CS – Software Solutions
7
End;
