Saturday, 3 February 2018

PLSQL Program Examples

                                 PLSQL Program

Example :- Smallest program of PLSQL

begin
null;
end;
/

Example :-Program to print "HELLO PLSQL WORLD !!" 

begin
dbms_output.put_line('HELLO PLSQL WORLD !!');
end;
/

Example :-Program to print the value of variable 

declare 
a number;
begin
a:=10;
dbms_output.put_line(a);
end;
/

declare 
a number :=10 ;
begin
dbms_output.put_line(a);
end;
/

declare
a varchar2(20);
begin
a:='Hello World';
dbms_output.put_line(a);
end;
/

Example :- Program that take input from Keyboard and display the value

declare
a number;
begin
a:=&c;
dbms_output.put_line(a);
end;
/

Example :- Program that take input from Keyboard  and print that value
                   with some text

declare
a number;
begin
a:=&c;
dbms_output.put_line('Entered number is :-'||a);
end;
/


Example :-Program to add two numbers 

declare 
a number :=10 ;
b number :=30 ;
c number;
begin
c:=a+b;
dbms_output.put_line(c);
end;
/

Example :-Program to add two numbers given by Keyboard

declare 
a number :=&a ;
b number :=&c ;
c number;
begin
c:=a+b;
dbms_output.put_line(c);
end;
/


declare
a number;
b number;
sum1 number;
begin
a:=&a;
b:=&b;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare 
a static number :=10 ;
begin
dbms_output.put_line(a);
end;
/


Example:- Program to print the SALARY of EMPLOYEE

DECLARE
vsal number;
begin
select sal into vsal from emp where empno=7499;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
end;
/

DECLARE
vsal number;
begin
select sal into vsal from emp where empno=&empno;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
end;
/

Example:- Program to print the SALARY and JOB of EMPLOYEE

DECLARE
vsal emp.sal%type;
vjob emp.job%type; 
begin
select sal,job into vsal,vjob from emp where empno=7499;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
dbms_output.put_line('EMPLOYEE JOB IS :-'||vjob);
end;
/

DECLARE
vsal emp.sal%type;
vjob emp.job%type; 
begin
select sal,job into vsal,vjob from emp where empno=&empno;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
dbms_output.put_line('EMPLOYEE JOB IS :-'||vjob);
end;
/

DML Operation through PL/SQL Block


Example :-Increase the employee salary by 100

begin
update emp1 set sal=sal+100 where empno=&empno;
end;
/

Example :-Increase the employee salary by 10% of given department

begin
update emp1 set sal=sal+(sal*0.1) where deptno=&deptno;
end;
/


Example:-Program to display EVEN or ODD for entered number

declare
a number :=&a;
begin
if (mod(a,2)!=0) then
dbms_output.put_line('Number :-'||a||' is ODD');
else
dbms_output.put_line('Number :-'||a||' is EVEN');
end if;
end;
/


declare
a number;
b number;--global variable
sum1 number;
begin
a:=&a;
b:=&b;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||sum1);
begin
dbms_output.put_line('Sum of given numbers is :'||b);
end;
dbms_output.put_line('Sum of given numbers is :'||a);
end;
/


declare
a number:=&a;
b number:=&b;
sum1 number;
begin
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a number:=12;
b number:=13;
sum1 number;
begin
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a number default 12;
b number default 13;
sum1 number;
begin
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a number default 12;
b number default 13;
sum1 number;
begin
a:=16;
b:=18;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a constant number:=12;
b number default 13;
sum1 number;
begin
b:=18;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a  number not null:=12 ;
b number default 13;
sum1 number;
begin
a:=18;
b:=18;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/

LOOPING:-


begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/


begin
for i in REVERSE 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/


Harry

Author & Editor

A technology enthusiast and addictive blogger who likes to hacking tricks and wish to be the best White Hacket Hacker of the World.

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.