PL /SQL RECORD
----------------------------
Declare
Type r1 is record(empno number(5),
ename varchar2(10),
hiredate date);
r2 r1;
Begin
select empno,ename,hiredate into r2 from emp where empno=&Ask_Empno;
dbms_output.put_line([Link]||' '||[Link]||' '||[Link]);
Exception
when no_data_found then
raise_application_error(-20023,'Invalid Employee Number');
End;
--------------------------------------------------------------------------------
----------------------------------------------------
With Procedure
-----------------------
set serverout on;
create or replace procedure pr_plrecord (eno [Link]%type) is
Type r1 is record(empno number(5),
ename varchar2(10),
hiredate date);
r2 r1;
Begin
Select empno,ename,hiredate
into r2
from emp
where empno=eno;
dbms_output.put_line([Link]||' '||[Link]||' '||[Link]);
Exception
when no_data_found then
raise_application_error(-20023,'Invalid Employee Number');
End;
--------------------------------------------------------------------------------
----------------------------------------------------
With Cursor / Procedure
-----------------------------------
set serverout on;
create or replace procedure pr1 (dno [Link]%type) is
Type r1 is record(empno number(5),
ename varchar2(10),
hiredate date);
r2 r1;
cursor c1 is
Select empno,ename,hiredate
From emp
Where deptno=dno;
Begin
Open c1;
Loop
Exit when c1%notfound;
Fetch c1 into r2;
dbms_output.put_line([Link]||' '||[Link]||' '||
[Link]);
End Loop;
Close c1;
Exception
when no_data_found then
raise_application_error(-20023,'Invalid Department Number');
End;
/
show err
--------------------------------------------------------------------------------
----------------------------------------------------
With Cursor / Procedure -- Joins
----------------------------------------
set serverout on;
create or replace procedure pr2 (dno [Link]%type) is
Type r1 is record(empno number(5),
ename varchar2(10),
hiredate date,
loc [Link]%type);
r2 r1;
cursor c1 is
Select empno,ename,hiredate,loc
From emp,dept
Where [Link]=dno and [Link]=[Link];
Begin
Open c1;
Loop
Exit when c1%notfound;
Fetch c1 into r2;
dbms_output.put_line([Link]||' '||[Link]||' '||
[Link]||' '||[Link]);
End Loop;
Close c1;
Exception
when no_data_found then
raise_application_error(-20023,'Invalid Department Number');
End;
/
show err
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------
PL/SQL TABLE - 1
-------------------------------
set serverout on;
Declare
Type r1 is table of [Link]%type index by binary_integer;
r2 r1;
i binary_integer:=0;
Begin
For r3 in (Select ename from emp)
Loop
i:=i+1;
r2(i):=[Link];
dbms_output.put_line(r2(i));
End Loop;
End;
/
show err
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
PL/SQL TABLE - 2
-------------------------------
Declare
Type r1 is table of dept%rowtype index by Binary_Integer;
r2 r1;
Begin
for i in 1..10
Loop
select * into r2(i) from dept where deptno=10;
dbms_output.put_line(r2(i).dname ||' '||r2(i).loc||'
'||r2(i).deptno);
End Loop;
Exception
When Too_many_rows Then
Raise_Application_Error(-20023,'More than one rows');
End;
/
=========================
PL/SQL TABLE - 2A
-------------------------------
Declare
Type r1 is table of dept%rowtype index by Binary_Integer;
cursor c1 is select * from dept;
r2 r1;
i binary_integer:=0;
dname varchar2(25);
Begin
For a in c1
Loop
i:=i+1;
dbms_output.put_line((i)||' '||[Link]||' '||[Link]||'
'||[Link]);
dname:=[Link];
dbms_output.put_line(dname);
End Loop;
Exception
When Too_many_rows Then
Raise_Application_Error(-20023,'More than one rows');
End;
/
===================
PL/SQL TABLE - 3
-----------------------------
Declare
Type r1 is table of [Link]%type index by Binary_Integer;
r2 r1;
cursor c1 is select ename from emp;
i binary_integer:=0;
Begin
For a in c1
Loop
i:=i+1;
r2(i):=[Link];
dbms_output.put_line(r2(i));
End Loop;
End;
/
PL/SQL TABLE - 4
-----------------------------
set serverout on;
create or replace procedure pr_pl1 (dno [Link]%type) is
Type r1 is table of [Link]%type index by binary_integer;
r2 r1;
i binary_integer:=0;
Begin
For r3 in (Select ename from emp where deptno=dno)
Loop
i:=i+1;
r2(i):=[Link];
-- dbms_output.put_line(r2(i));
dbms_output.put_line('Mr. '||r2(i));
End Loop;
Exception
when no_data_found then
raise_application_error(-20023,'Invalid Department Number');
End;
/
exec pr_pl1(20);
exec pr_pl1(30);
REF CURSOR - 1
-----------------------------
Declare
Type re1 is Ref Cursor;
a re1;
b re1;
a1 number;
b1 varchar2(20);
c1 varchar2(20);
Begin
Open a for select empno,ename,job from emp;
Loop
fetch a into a1,b1,c1;
exit when a%notfound;
dbms_output.put_line(a1||' '||b1||' '||c1);
End Loop;
Close a;
End;
/
REF CURSOR - 2
-----------------------------
Create or Replace Procedure pref2 is
Type re1 is Ref Cursor;
sql_stmt re1;
a1 number;
b1 varchar2(20);
c1 varchar2(20);
Begin
dbms_output.put_line('==========================');
Open sql_stmt for select empno,ename,job from emp;
Loop
fetch sql_stmt into a1,b1,c1;
exit when sql_stmt%notfound;
dbms_output.put_line(a1||' '||b1||' '||c1);
End Loop;
----- Close sql_stmt;
dbms_output.put_line('==========================');
Open sql_stmt for select deptno,dname,loc from dept;
Loop
fetch sql_stmt into a1,b1,c1;
exit when sql_stmt%notfound;
dbms_output.put_line(a1||' '||b1||' '||c1);
End Loop;
------- Close sql_stmt;
dbms_output.put_line('==========================');
Open sql_stmt for select locid,loc_name,country from locmast;
dbms_output.put_line('ID LocName Country');
dbms_output.put_line('-----------------------------------------------
-');
Loop
fetch sql_stmt into a1,b1,c1;
exit when sql_stmt%notfound;
dbms_output.put_line(a1||' '||b1||' '||c1);
End Loop;
----- Close sql_stmt;
End;
/
-----------------------------------------------------------------------------
REF CURSOR - 3 [ With Procedure ]
-----------------------------
Create or Replace Procedure pref1(dno [Link]%type) is
Type re1 is Ref Cursor;
sql_stmt re1;
a1 number;
b1 varchar2(20);
c1 varchar2(20);
Begin
dbms_output.put_line('==========================');
Open sql_stmt for select deptno,dname,loc from dept where deptno=dno;
Loop
fetch sql_stmt into a1,b1,c1;
exit when sql_stmt%notfound;
dbms_output.put_line(a1||' '||b1||' '||c1);
End Loop;
------- Close sql_stmt;
dbms_output.put_line('==========================');
Open sql_stmt for select empno,ename,job from emp where deptno=dno;
Loop
fetch sql_stmt into a1,b1,c1;
exit when sql_stmt%notfound;
dbms_output.put_line(a1||' '||b1||' '||c1);
End Loop;
----- Close sql_stmt;
dbms_output.put_line('==========================');
Exception
when no_data_found then
raise_application_error(-20023,'Invalid Department Number');
End;
/
Exec pref1(10);
Exec pref1(20);