Scribd
Upload
4 views14 pages

PL/SQL Code Examples and Concepts

The document contains various examples of PL/SQL code demonstrating different programming concepts such as blocks, variables, constants, control structures (if-else, loops), cursors, exceptions, procedures, functions, packages, dynamic SQL, and triggers. Each section provides code snippets that illustrate the usage of these concepts in a database context, particularly with employee and department data. The examples are structured to show both simple and advanced features of PL/SQL programming.

Uploaded by

kadamaniket1301
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views14 pages

PL/SQL Code Examples and Concepts

The document contains various examples of PL/SQL code demonstrating different programming concepts such as blocks, variables, constants, control structures (if-else, loops), cursors, exceptions, procedures, functions, packages, dynamic SQL, and triggers. Each section provides code snippets that illustrate the usage of these concepts in a database context, particularly with employee and department data. The examples are structured to show both simple and advanced features of PL/SQL programming.

Uploaded by

kadamaniket1301
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

--simple block:=

set serveroutput on
declare
n1 number(30):=100;
n2 number(30);
begin
n2:=n1+n1;
dbms_output.put_line(n2);
end;

--nested block:=
set serveroutput on
declare
n1 number(30):=100;
n2 number(30);
begin
declare
n1 number(30):=40;
v2 number(30);
begin
v2:=n1+17;
dbms_output.put_line(v2||'iiner');
end;
n2:=n1+n1;
dbms_output.put_line(n2||'outer');
end;

--naming of block:=
set serveroutput on
begin <<v>>
declare
n1 number(30):=100;
n2 number(30);
begin
declare
n1 number(30):=40;
v1 number(30);
begin
v1:=v.n1+17;
dbms_output.put_line(v1||'iiner');
end;
n2:=n1+n1;
dbms_output.put_line(n2||'outer');
end;
end v;

--variable:=
--local variable:=
set serveroutput on
declare
n1 number(30):=12;
n2 number(30);
begin
n2:=n1;
dbms_output.put_line(n2);
end;

--constant:=
set serveroutput on
declare
n1 constant number(30):=12;
begin
dbms_output.put_line(n1);
end;

--not null:=
set serveroutput on
declare
n1 number (30)not null:=12;
begin
n1:=n1+1;
dbms_output.put_line(n1);
end;

--constant notnull:=
set serveroutput on
declare
n1 constant number (30)not null:=12;
begin
dbms_output.put_line(n1);
end;

--ifelse:=
set serveroutput on
declare
n1 number(30);
begin
for i in 100..120
loop
select employee_id into n1 from employees
where employee_id=i;
if
mod(n1,2)=0 then
dbms_output.put_line(n1||' even ');
else
dbms_output.put_line(n1||' odd ');
end if;
end loop;
end;
--nested ifelse:=
set serveroutput on
declare
n1 number(30);
n2 varchar2(30);
begin
for i in 100..140
loop
select salary,first_name into n1,n2 from employees
where employee_id=i;
if
mod(n1,5)=0 then
dbms_output.put_line('divide by 5');
if mod(n1,70)=0
then
dbms_output.put_line('divide by 70');
end if;
else
dbms_output.put_line('not divide by 5');
end if;
end loop;
end;

--ladder ifelse:=
set serveroutput on
declare
choice varchar2(30):='&x';
n1 number(30);
n2 varchar2(30);
begin
if
choice='select' then
select employee_id,first_name into n1,n2 from employees
where employee_id=120;
dbms_output.put_line(n1||n2);
elsif
choice='insert' then
insert into nyn values (99,'df',65478);
dbms_output.put_line('data insert');
elsif
choice='delete' then
delete from nyn
where id=99;
end if;
end;

select * from nyn


--loop:=
set serveroutput on
declare
begin
for r in 1..12
loop
for c in 1..r
loop
dbms_output.put('*');
end loop;
dbms_output.put_line(' ');
end loop;
end;

set serveroutput on
declare
begin
for r in reverse 1..12
loop
for c in 1..r
loop
dbms_output.put('*');
end loop;
dbms_output.put_line(' ');
end loop;
end;
--while:=
set serveroutput on
declare
v1 employees%rowtype;
n1 number(30):=100;
begin
while (n1<=130)
loop
select * into v1 from employees
where employee_id=n1;
n1:=n1+1;
dbms_output.put_line(v1.employee_id||v1.first_name);
end loop;
end;

--dowhile:=
set serveroutput on
declare
v1 employees%rowtype;
n1 number(30):=100;
begin
loop
select * into v1 from employees
where employee_id=n1;
n1:=n1+1;
dbms_output.put_line(v1.employee_id||v1.first_name);
exit when n1<130;
end loop;
end;

--for loop:=
set serveroutput on
declare
v1 employees%rowtype;
begin
for i in 100..120
loop
select * into v1 from employees
where employee_id=i;
dbms_output.put_line(v1.employee_id||v1.first_name);
end loop;
end;

--nested loop:=
set serveroutput on
declare
n1 number(30):=1;
begin
for r in 1..3
loop
for c in 1..5
loop
if
mod(c,2)=0
then
dbms_output.put(n1);
n1:=n1+1;
else
dbms_output.put('*');
end if;
end loop;
dbms_output.put_line(' ');
end loop;
end;

set serveroutput on
declare
n1 number(30):=1;
begin
for i in 1..3
loop
for j in 1..5
loop

if
mod(j,2)=0
then
dbms_output.put('*');
else
dbms_output.put(n1);
n1:=n1+1;
end if;

end loop;
dbms_output.put_line(' ');
end loop;
end;

-- composite data type:-


--record:=
set serveroutput on
declare
type p is record(n1 number(30),n2 varchar2(30));
v1 p;
begin
for i in 100..120
loop
select employee_id,first_name into v1 from employees
where employee_id=i;
dbms_output.put_line(v1.n1||v1.n2);
end loop;
end;

--COLLECTION:=
--%rowtype:=
set serveroutput on
declare
v1 employees%rowtype;
begin
for i in 100..120
loop
select * into v1 from employees
where employee_id=i;
dbms_output.put_line(v1.first_name);
end loop;
end;

--cursor:=
--implicit cursor:
--sql%rowcount;

set serveroutput on
declare
n1 number(30);
begin
delete from d9;
n1:=sql%rowcount;
dbms_output.put_line(n1||'row deletd');
end;
--sql%found:=
set serveroutput on
declare
n1 number(30);
begin
delete from d9;
if
sql%found then
n1:=sql%rowcount;
dbms_output.put_line(n1||'row deletd');
else
dbms_output.put_line('table is empty');
end if;
end;

--sql%notfound;
set serveroutput on
declare
n1 number(30);
begin
delete from d9;
if
sql%notfound then
dbms_output.put_line('table is empty');
else
n1:=sql%rowcount;
dbms_output.put_line(n1||'row deletd');
end if;
end;

select * from d9
insert into d9 values(10,'hdjs');

--explicit cursor:=r
SET SERVERoutput on
declare
cursor c1 is select * from (select distinct salary,rank()over(order by salary desc)as jf from
employees)
where jf=3;
v1 c1%rowtype;
begin
for v1 in c1
loop
dbms_output.put_line([Link]);
exit when c1%notfound;
end loop;
close c1;
end;
--parameterise cursor:=r
set serveroutput on
declare
cursor c1(n1 number) is select * from employees
where department_id=n1;
v1 c1%rowtype;
v2 c1%rowtype;
begin
open c1(80);
loop
fetch c1 into v1;
dbms_output.put_line(v1.first_name||v1.department_id);
exit when c1%notfound;
end loop;
close c1;
open c1(50);
loop
fetch c1 into v2;
dbms_output.put_line([Link]||v2.department_id);
exit when c1%notfound;
end loop;
close c1;
end;

--for loop cursor:=r


set serveroutput on
declare
cursor c1 is select * from employees;
begin
for v1 in c1
loop
dbms_output.put_line(v1.employee_id||v1.first_name);
end loop;
end;

--unnamed cursor:=
set serveroutput on
declare
begin
for v1 in (select * from employees)
loop
dbms_output.put_line(v1.employee_id||v1.first_name);
end loop;
end;

--reference cursor
set serveroutput on
declare
c1 sys_refcursor;
v1 employees%rowtype;
v2 departments%rowtype;
begin
open c1 for select * from employees;
loop
fetch c1 into v1;
dbms_output.put_line(v1.employee_id||v1.first_name);
exit when c1%notfound;
end loop;
close c1;
dbms_output.put_line('<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<departments>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>');
open c1 for select * from departments;
loop
fetch c1 into v2;
dbms_output.put_line(v2.department_id||v2.department_name);
exit when c1%notfound;
end loop;
close c1;
end;

--pridefine exception:
set serveroutput on
declare
v1 employees%rowtype;
begin
select * into v1 from employees
where first_name='Lex';
dbms_output.put_line(v1.first_name);
exception
when too_many_rows then
dbms_output.put_line('its writen more riows');
end ;

select * from employees


--non-predifine exception:=
set serveroutput on
declare
x1 exception;
pragma exception_init(x1,-00001);
begin
insert into em values(20,'sd',581,52);
dbms_output.put_line('data is insert');
exception
when x1 then
dbms_output.put_line('dupplication');
end;

select * from em
alter table em
add constraints hfk primary key(eno);
insert into em values(10,'sd',5326781,52)

set serveroutput on
declare
x1 exception;
pragma exception_init(x1,-02292);
begin
delete from user2
where u_id=100;
dbms_output.put_line('data deleted');
exception
when x1 then
dbms_output.put_line('dependency');
end;

--user define exception:=


set serveroutput on
declare
x1 exception;
age number(30):=16;
begin
if
age<18 then
dbms_output.put_line('valid age');
else
raise x1;
end if;
dbms_output.put_line('go fot kyc');
exception
when x1 then
dbms_output.put_line('invalid age');
end ;

--raise application error:=


set serveroutput on
declare
n1 number(30):=16;
begin
if
n1<19 then
raise_application_error(-20004,'valid age');
end if;
end;

--procedure
create or replace procedure fdj
is
v1 employees%rowtype;
begin
for i in 100..120
loop
select * into v1 from employees
where employee_id=i;
dbms_output.put_line(v1.employee_id||v1.first_name);
end loop;
end;

set serveroutput on
execute fdj

-- nested procedure
create or replace procedure fdj
is
v1 employees%rowtype;
procedure fdj1
is
v2 departments%rowtype;
begin
for l in 40..80
loop
select * into v2 from departments
where department_id=l;
dbms_output.put_line(v2.department_id||v2.department_name);
end loop;
end;
begin
fdj1;
for i in 40..180
loop
select * into v1 from employees
where employee_id=i;
dbms_output.put_line(v1.employee_id||v1.first_name);
end loop;
end;
set serveroutput on
execute fdj

--function:=
--nested function:=

create or replace function grh (n1 number )


return varchar2
is
cursor k is select * from employees e join departments d on
(d.department_id=e.department_id)
where e.employee_id=n1;
v1 employees%rowtype;
v2 departments%rowtype;
begin
open k;
fetch k into v1;
fetch k into v2;

return v1.first_name ;
end;

create or replace function grh (n1 number,v1 out employees%rowtype )


return varchar2
is

begin
select * into v1 from employees
where employee_id =n1;
return v1.first_name ;
end;

variable k varchar2
select grh (100,:k) from daul

set serveroutput on
declare
begin
grh(100);
end;

show error

select grh (100) from dual

show error
select grh(100) from dual

--advance procedure and function


--package:=
create or replace package ker
is
procedure pr1(n1 in number);
v1 number(30);
v2 varchar2(30);
procedure pr1(n1 in number,n2 out number);
end;

create or replace package body ker


is
procedure pr1(n1 in number)
is
begin
select employee_id,first_name into v1,v2 from employees
where employee_id=n1;
dbms_output.put_line(v1||v2);
end;
procedure pr1(n1 in number,n2 out number)
is
begin
select employee_id,first_name,salary into v1,v2,n2 from employees
where employee_id=n1;
dbms_output.put_line(v1||v2||n2);
end;
end;

variable v number
set serveroutput on
execute ker.pr1(100)
set serveroutput on
execute ker.pr1(100,:v)

--body less package:=


create or replace package jhd
is
n1 number(30,10):=0.25;
end;

set serveroutput on
declare
v1 number(30):=100;
begin
v1:=v1*jhd.n1;
dbms_output.put_line(v1);
end;

drop package hjue


---dynamic sql:=
create or replace procedure hj1(name in varchar2)
is
begin
execute immediate 'drop table '||name;
dbms_output.put_line('table is drop');
end;

set serveroutput on
execute hj1('a43')
--swaping:=
set serveroutput on
declare
n1 varchar2(30):='delli';
n2 varchar2(30):='Austrai';
n3 varchar2(30);
begin
n3:=n1;
n1:=n2;
n2:=n3;
dbms_output.put_line('n1 ='||n1);
dbms_output.put_line('n2 ='||n2);
end;

--trigger:=
--instead off:=
create or replace trigger hjd
instead of insert on pooja1
begin
dbms_output.put_line('data insert');
end;
select * from pooja
insert into pooja values(200,'hdj',5673);

You might also like