PLSQL:
Procedural Structured Query Language.
It is one of the comprehensive tools of the oracle that
is helpful in adding the procedural language features such as
Decision making, looping, exception handling etc... to
the powerful SQL.
PLSQL Programs contains 3 sections.
1)Declaration
section: In this section
we declare the variables ,cursors etc…
2)Executable
section: In this section we do the looping, decision making
and to the sq. statements…
3)Exception
section: In this section we handle the exceptions raised
while using the sq. statements.
Every plsql statement should be terminated with semi
colon(;)…
Variable:
A variable is a named location in memory to which a data can be assigned or
from which the data can be
read.
Variable
declaration: A variable is declared in the declaration
section for the process of telling the compiler,the name memory location its
type and size.
Variable
Declaration Syntax:
Variable_name datatype(size);
Ex: ln_num number;
Ln_flag char(1);
Ln_msg varchar2(10000);
Assigment
Operator (:=)
This operator is used to assign the value on its right to
the variable on its left.
EX: ln_num:= 0;
Ln_falg:=
‘N’;
Intialization:
This is process of assigning a variable an intial value
at the time of declaration of the variable.
Ex: ln_num NUMBER:= 0;
Ln_falg char(1):= ‘N’;
Server
output:
It is an sql environment variable that tells whether the
output of the plsql program has to be shown, by default it is OFF;
Inordet to ON please run the below command at the
beginning of every session.
SET SERVEROUTPUT ON;
DBMS_OUTPUT.PUT_LINE:
This is one of the built in package.function of the plsql
that is used to print the output….
BLOCKS:
BLOCKS
|
Reusability
|
Stogare
|
Scope
|
Anonymous
|
Not there
|
NO
|
Until the session
|
Named
|
Yes
|
Yes
|
Permanently
|
Let’s write some anonymous blocks to get practiced of how
to print a message and use the variables and assign a value to it and also
assign an intial value to it….
1)
To
Print a Message.
begin
dbms_output.put_line('Welcome to Plsql');
end;
/
declare
ln_msg varchar2(1000);
begin
dbms_output.put_line('Start');
ln_msg:= 'Welcome to Plsql';
dbms_output.put_line(ln_msg);
dbms_output.put_line('End');
end;
/
declare
ln_num number:=¶meter1;
begin
dbms_output.put_line('Start');
dbms_output.put_line(ln_num);
dbms_output.put_line('End');
end;
/
2)
Addtion
of 2 numbers.
declare
ln_num number:=0;
ln_num1 number:=¶meter1;
ln_num2 number:=¶meter2;
begin
dbms_output.put_line('Start');
ln_num := ln_num1 + ln_num2;
dbms_output.put_line('The sum of
'||ln_num1||' and '||ln_num2||' is '||chr(10)||ln_num);
dbms_output.put_line('End');
end;
3)
Substraction
2 numbers.
4)
Addition
of 2 numbers and substracting the 3 number.
5)
Multiplication
of 2 numbers.
Decision
Making:
If condition Syntax:
IF condition1
then
Statement1;
ELSIF condition2
then
Statement2;
ELSIF condition3
then
Statement3;
ELSE
Statement n;
END IF;
6)
Display Max of 2 numbers
declare
ln_num1 number:=¶meter1;
ln_num2 number:=¶meter2;
begin
dbms_output.put_line('Start');
IF ln_num1 > ln_num2 then
dbms_output.put_line('The smax number is
'||ln_num1);
else
dbms_output.put_line('The smax number is
'||ln_num2);
end if;
dbms_output.put_line('End');
end;
/
7) Give input as 3 numbers and print the max number
8) Give input as 3 numbers and print the
max number when added 2 numbers.
LOOPING:
Syntax: FOR variable in start..end
LOOP
--any action to be taken place
END LOOP;
EX: 1) Print the numbers from 1 to 100/.
declare
i number:= 0;
begin
for i in 1..100
loop
dbms_output.put_line('The number is ' ||i);
end loop;
end;
2) Print the
odd numbers from 1 to 20.
declare
i number:= 0;
begin
for i in 1..20
loop
if mod(i,2)!= 0 then
dbms_output.put_line('The odd number is '
||i);
end if;
end loop;
end;
/
3) print the
even numbers from 1 to 20.
declare
i number:= 0;
begin
for i in 1..20
loop
if mod(i,2) = 0 then
dbms_output.put_line('The odd number is '
||i);
end if;
end loop;
end;
/
EXIT:
This is an oracle provided plsql keyword using which we
can come out of the loop.
EX: Print the odd numbers from 1 to 50 and exit when ever
you find the number 26 in the loop..
declare
i number:= 0;
begin
for i in 1..50
loop
if i = 26 then
exit;
else
if mod(i,2) != 0
then
dbms_output.put_line('The odd number is ' ||i);
end if;
end if;
end loop;
dbms_output.put_line('The end ');
end;
/
NOTE:
Whenever we write a sql statement in a plsql block we
cannot use the column name directly for any manipulation purpose etc….
We should fetch the values in to the local variables
using INTO Clause.
Total No of column in the select clause should be equal
to total number of variables in the into clause.
The order of selected columns data type and size sequence
should be same as the order of variables sequence data type and size in the
into clause.
EX: Write a Simple
plsql block to display employee name,number,salary.
declare
ln_empno number:=0;
ln_ename varchar2(100);
ln_sal number;
begin
select
empno,ename,sal
into ln_empno,ln_ename,ln_sal
from emp
where empno =
¶meter1;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
end;
/
%TYPE: This attribute is used to declare the plsql
variables with the same definition as column definition of a table.
Syntax: variable Name Table_Name.Column_Name%type;
EX: Write a Simple plsql block to display employee
name,number,salary.
declare
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal
emp.sal%type;
begin
select
empno,ename,sal
into ln_empno,ln_ename,ln_sal
from emp
where empno =
¶meter1;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
end;
/
Write a Simple
plsql block to display employee name,number,salary, department name and
location.
declare
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal
emp.sal%type;
ln_dname dept.dname%type;
ln_loc
dept.loc%type;
begin
select
empno,ename,sal,dname,loc
into ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where empno =
¶meter1
and e.deptno =
d.deptno;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is
--'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
end;
/
Write a
simple plsql block to display maximum salary from the emp table.
declare
ln_sal emp.sal%type;
begin
select max(sal)
into ln_sal
from emp;
dbms_output.put_line('The max sal is --'||ln_sal);
end;
/
Procedure:
Create or replace procedure p_name(parameter1 datatype, parameter2
datatype)
Is
BEGIN
NULL;
END;
Ex: create or
replace procedure xx_max_sal
is
ln_sal emp.sal%type;
begin
select
max(sal),deptno
into ln_sal
from emp;
dbms_output.put_line('The max sal is --'||ln_sal);
end;
/
Execution :
begin
xx_max_sal;
end;
/
Exec xx_max_sal;
EX: Write a Simple proceudre to display employee
name,number,salary, department name and location.
create or replace procedure xxx_emp_dtls(p_empno number)
is
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal
emp.sal%type;
ln_dname dept.dname%type;
ln_loc
dept.loc%type;
begin
select
empno,ename,sal,dname,loc
into ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where empno =
p_empno
and e.deptno =
d.deptno;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is
--'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
end xxx_emp_dtls;
/
Exercise:
Write a procedure to print the student information and
their total marks and grade.
show student marks,result,grade even if the student
information doesnot exists
by passing input parameter as sno and grade.
Write a procedure to print the student information and
their total marks and grade.
show student marks,result,grade even if the student
information doesnot exists
by passing input parameter as sno and result.
Write a procedure to print the student information and
their total marks and grade.
show student marks,result,grade even if the student
information doesnot exists
by passing input parameter as sno and marks. The student
marks should be greater then marks parameter value.
No comments:
Post a Comment