Saturday, March 12, 2016

cursor

Cursors.


Cursors:

A cursor is a memory area in to which the rows of a select statement are loaded and processed.

Records Pointer: When a cursor is created oracle creates a pointer called  record pointer that points to the current  record among the set of the records in the cursor

Cursor attributes

A part from the creating a record pointer after the cursor is created oracle also creates some variables
called cursor attributes which are used in knowing the status of the cursor like to know whether the cursor is open,close,found,not found

1)%Open =  Return the status of the cursor

2)%Found = Determines whether any records  exists of that select  stmt assigned to the cursor

3)%NOT Found = Determines whether any records  not exists of that select  stmt assigned to the cursor


4)%ROWCOUNT = gives the no of rows in the cursor...
----------------------------------------------------------------------


There are 2 types of cursors

1) Implicit cursor:
 If the cursor is created by oracle then it is said to be Implicit Cursor
  The Name of the Implicit cursor is always be SQL

Plsql Block to demostrate Implicit cursor....

Ex1: 
Create or replace procedure p_upd_emp(p_deptno number)
Is
begin 
   UPDATE EMP
   SET sal = sal+100
   where deptno = p_deptno;

        IF SQL%FOUND THEN
            dbms_output.put_line('Cursor found');
       END IF;
           
        IF SQL%NOTFOUND THEN
            dbms_output.put_line('Cursor not found ');
      END IF;
exception
 when others then
 dbms_output.put_line('Error in the p_upd_emp procedure'||sqlerrm);    
End;
-----------------------------------

plsql block to demostrate to kow how many records got deleted
or updated...


Create or replace procedure p_upd_emp(p_deptno number)
Is
begin 
   UPDATE EMP
   SET sal = sal+100
   where deptno = p_deptno;
        IF SQL%FOUND THEN
            dbms_output.put_line('Cursor found');
       END IF;
        IF SQL%NOTFOUND THEN
            dbms_output.put_line('Cursor not found ');
      END IF;
dbms_output.put_line('no of records updated are '||sql%rowcount);
exception
 when others then
 dbms_output.put_line('Error in the p_upd_emp procedure'||sqlerrm);    
End;
/
--
Create or replace procedure p_upd_emp(p_deptno number)
Is
ln_cnt number;
begin 
   UPDATE EMP
   SET sal = sal+100
   where deptno = p_deptno;
        IF SQL%FOUND THEN
            dbms_output.put_line('Cursor found');
       END IF;
        IF SQL%NOTFOUND THEN
            dbms_output.put_line('Cursor not found ');
      END IF;
ln_cnt:= sql%rowcount;
dbms_output.put_line('no of records updated are '||ln_cnt);
exception
 when others then
 dbms_output.put_line('Error in the p_upd_emp procedure'||sqlerrm);    
End;
/

--------------------------------------------------------------------------------

2) Explicit cursor:
 If it is created by the programmer with the help of cursor keyword in the declaration sections , it is called
explicit cursor...

The Name of the explicit cursor is user defined.....


Steps for creating the Explicit Cursor....

1) Declare the cursor in the declaration section via using the CURSOR Keyword...

Syntax: CURSOR CURSORNAME
             IS Select Statement;

Ex: Cursor c_emp
       is select * from emp;

When the above statement is executed then the pl/sql engine just makes a note of the cursor name(c_emp) with its associated select statement but doesnot associate memory for the o/p if the
select statement.

Memory allocation is done only when the cursor is opened.

OIpen the cursor in the executable part...this is associated via the OPEN Keyword.

Syntax: Open cursor_name;
 Ex: Open C_emp;

When the above select statement is executed then the pl/sql engine executes the select statement
associated with the cursor c_emp and then allocates memory for the output of the select statement.

Start the loop

Syntax :LOOP

Fetch the values of the current record from the cursor in to the variables which are already declared.

SYNTAX: Fetch Cursor name into var1,var2....

Ex: Fetch c_emp into v_empno,v_ename
The Number of variables in the fetch stmt into clause must be same with the no of columns present in the cursor.... in terms of both datatype and size.

EXIT when the cursor finds no records left to be    processed in the loop

End the loop and CLOSE the Cursor
Ex: END LOOP;
EX: Close c_emp;

After close of the cursor the records in the cursor memory is released.
-----------------------------------------------

EX:  Print the empname,sal,comm,job,dname,locations for those whose
       salary is greater then the min sal of the deptno 20;
Create or replace procedure xx_emp_dtls(p_deptno number)
is
cursor c_emp is
select      e.ename
           ,e.sal
              -- ,nvl(e.comm,0) comm
              --  ,e.job
              --  ,d.dname
               ,d.loc
from emp e
    ,dept d
where d.deptno(+) = e.deptno
and sal >(select min(sal) from emp where deptno = p_deptno);
v_ename emp.ename%type;
v_sal number;
v_loc dept.loc%type;
begin
  open c_emp;
   LOOP
    FETCH c_emp into v_ename,v_sal,v_loc;
            EXIT WHEN c_emp%notfound;
dbms_output.put_line('ename'||v_ename);
dbms_output.put_line('salary'||v_sal);
dbms_output.put_line('location'||v_loc);
   END LOOP;
--
   close c_emp;
  END xx_emp_dtls;
/





EX:  Print the empname,sal, locations by passing the deptno as parameter.
      If no records found for the given deptno value then print invalid deptno and if deptno is valid then print the emp details and if the deptno is passed as null then print the data for all the deptno satisfying the query….

Create or replace procedure xx_emp_dtls(p_deptno number)
is
cursor c_emp is
select      e.ename
           ,e.sal
               ,d.loc
from emp e
    ,dept d
where d.deptno(+) = e.deptno
and  e.deptno = nvl(p_deptno,e.deptno);
v_ename emp.ename%type;
v_sal number;
v_loc dept.loc%type;
ln_flag char(1):= 'N';
begin
--
  open c_emp;
   LOOP
      FETCH c_emp into v_ename,v_sal,v_loc;
            EXIT WHEN c_emp%notfound;
        ln_flag:= 'Y';
      dbms_output.put_line('emp detials are '||v_ename||'----'||v_sal||'----'||v_loc);
   END LOOP;
--
if ln_flag = 'N' then
 dbms_output.put_line('Invalid deptno passed '||p_deptno);
end if;
   close c_emp;
  END xx_emp_dtls;
/













2) write a plsql procedure  to increase the salary of the employee of the emp
table by 10% if job is manager
             6% if job is clerk
             5% is job is salesman
             2% is any other.
Pass deptno and empno as parameters and do the updation for all the records if the deptno and empno is passed as null and  for the passed combination of deptno,empno values if there are no records in the query then print the message no data exists for the passed input values

Create or replace procedure xx_upate_sal(p_deptno number,p_empno number)
is
cursor c_emp is
select ename,job,empno,sal
from emp
where deptno  = nvl(p_deptno,deptno)
and      empno  = nvl(p_empno,empno);
v_ename emp.ename%type;
v_job   emp.job%type;
v_empno emp.empno%type;
v_sal   emp.sal%type;
ln_flag char(1):= 'N';
begin
  open c_emp;
   LOOP
    FETCH c_emp into v_ename,v_job,v_empno,v_sal;
            EXIT WHEN c_emp%notfound;
            ln_flag:= 'Y';
            dbms_output.put_line('old sal  '||v_sal||' for empno '||v_empno);
            IF upper(v_job) = 'MANAGER' THEN
             UPDATE  emp
             set sal = sal + (sal * 10/100)
             where empno = v_empno;
            ELSIF upper(v_job) = 'CLERK' THEN
            UPDATE  emp
             set sal = sal + (sal * 6/100)
             where empno = v_empno;
            ELSIF upper(v_job) = 'SALESMAN' THEN
            UPDATE  emp
             set sal = sal + (sal * 5/100)
             where empno = v_empno;
            ELSE
            UPDATE  emp
             set sal = sal + (sal * 2/100)
             where empno = v_empno;
            END IF;
   END LOOP;
  close c_emp;
  COMMIT;
if ln_flag = 'N' then
 dbms_output.put_line('Invalid input parameters');
end if;
  END;
/

How to declare the cursor variable....

%ROWTYPE:
It is used to assign the cursor records structure to a variable. Instead of fetching the values in to independent local variable we can dump them in to one local variable by declaring the variable with the data type as cursor%rowtype.
---
Execises:

write a plsql procedure  to increase the salary of the employee of the emp
table by 10% if job is manager
             6% if job is clerk
             5% is job is salesman
             2% is any other.
Pass deptno and empno as parameters and do the updation for all the records if the deptno and empno is passed as null and  for the passed combination of deptno,empno values if there are no records in the query then print the message no data exists for the passed input values.
Print the old salary and as well new updated salary laong with the employee name and job.
Also include no of records getting printed.

EX:
create or replace procedure xx_rk_sal(p_deptno number,p_empno number)
is
cursor c_emp is
select ename,job,empno,sal
from emp
where deptno =nvl(p_deptno,deptno)
   and empno =nvl(p_empno,empno);
        ln_flag char(1):='N';
        v_cnt number:=0;
        v_newsal number;
        r_emp c_emp%rowtype;
begin
  open c_emp;
   LOOP
    FETCH c_emp into r_emp;
      EXIT WHEN c_emp%notfound;
      ln_flag:='y';
IF upper(r_emp.job)='MANAGER'THEN
     UPDATE emp
     SET sal=sal+(sal*10/100)
  WHERE empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*10/100);
ELSIF upper(r_emp.job)='CLERK'THEN
UPDATE emp
set sal= sal+(sal*6/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*6/100);
ELSIF upper(r_emp.job)='SALESMAN' THEN
UPDATE emp
set sal= sal+(sal*5/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*5/100);
ELSE
UPDATE emp
set sal=sal+(sal*2/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*2/100);
end if;
v_cnt:=v_cnt+sql%rowcount;
dbms_output.put_line('old sal was '||r_emp.sal||'for empno is '||r_emp.empno||'and new salary is'||v_newsal);
end loop;
close c_emp;
commit;
dbms_output.put_line('No of rows updated = '||v_cnt);
IF ln_flag ='N' then
dbms_output.put_line('invalid input parameters');
end if;
END;
/


write a plsql procedure to display all student marks,grade,name,sex,age
 details by passing sno as paramter.
if the passed sno is invalid
print the message as Invalid sno passed,if sno is passed as null print all the detials

--
write a plsql procedure to display all transaction details by passing acctno as paramter.
if the passed acctno is invalid or
if there are no tansactions for the passed acctno then
print the message as Invalid acctno or no transactions for the [passed
account no...









Cursor For LOOP
------------------------

With the help of the cursor for loop the different steps of opening, fetching, closing
of the cursor are automatic, also the memory variables are not required to store the cursor columns
instead they are dumped in to the cursor variable as one unit which are individually accessed by giving the column name after the dot which is proceeded by the cursor variable name

Syntax: FOR C_varc in cursor_name
 loop
  End loop;


-----------------------------------------------
Ex:
Write a plsql procedure  to increase the salary of the employee of the emp
table by 10% if job is manager
             6% if job is clerk
             5% is job is salesman
             2% is any other.
Pass deptno and empno as parameters and do the updation for all the records if the deptno and empno is passed as null and  for the passed combination of deptno,empno values if there are no records in the query then print the message no data exists for the passes input values.

Exercise:

create or replace procedure xx_rk_sal(p_deptno number,p_empno number)
is
cursor c_emp is
select ename,job,empno,sal
from emp
where deptno =nvl(p_deptno,deptno)
   and empno =nvl(p_empno,empno);
        ln_flag char(1):='N';
        v_cnt number:=0;
        v_newsal number;
        r_emp c_emp%rowtype;
begin

FOR r_emp in c_emp

LOOP

ln_flag:='Y';

IF upper(r_emp.job)='MANAGER'THEN
UPDATE emp
SET sal=sal+(sal*10/100)
WHERE empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*10/100);
ELSIF upper(r_emp.job)='CLERK'THEN
UPDATE emp
set sal= sal+(sal*6/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*6/100);
ELSIF upper(r_emp.job)='SALESMAN' THEN
UPDATE emp
set sal= sal+(sal*5/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*5/100);
ELSE
UPDATE emp
set sal=sal+(sal*2/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*2/100);
end if;
v_cnt:=v_cnt+sql%rowcount;
dbms_output.put_line('old sal was '||r_emp.sal||'for empno is '||r_emp.empno||'and new salary is'||v_newsal);

END LOOP;

commit;
dbms_output.put_line('No of rows updated = '||v_cnt);
IF ln_flag ='N' then
dbms_output.put_line('invalid input parameters');
end if;
END;
/

-----------------------------------------
Write a plsql proceudre to increase the salary of the employee of the emp
table by 10% if job is manager
             6% if job is clerk
             5% is job is salesman
             2% is any other
If no of records updated is more then that means
the data will repeatedly updated which is wrong...
whenever this happens donot do any updation just rollback everything...

create or replace procedure xx_rk_sal(p_deptno number,p_empno number)
is
        ln_flag char(1):='N';
        v_cnt number:=0;
        v_newsal number;
        u_exp exception;
begin
FOR r_emp in (select ename,job,empno,sal,deptno
                        from emp
                        where deptno =nvl(p_deptno,deptno)
                        and empno =nvl(p_empno,empno))
LOOP
ln_flag:='Y';
IF upper(r_emp.job)='MANAGER'THEN
UPDATE emp
SET sal=sal+(sal*10/100)
WHERE empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*10/100);
ELSIF upper(r_emp.job)='CLERK'THEN
UPDATE emp
set sal= sal+(sal*6/100)
where deptno = r_emp.deptno;
v_newsal:=r_emp.sal+(r_emp.sal*6/100);
ELSIF upper(r_emp.job)='SALESMAN' THEN
UPDATE emp
set sal= sal+(sal*5/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*5/100);
ELSE
UPDATE emp
set sal=sal+(sal*2/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*2/100);
end if;
if sql%rowcount > 1 then
 raise u_exp;
end if;
v_cnt:=v_cnt+sql%rowcount;
dbms_output.put_line('old sal was '||r_emp.sal||'for empno is '||r_emp.empno||'and new salary is'||v_newsal);
END LOOP;
commit;
dbms_output.put_line('No of rows updated = '||v_cnt);
IF ln_flag ='N' then
dbms_output.put_line('invalid input parameters');
end if;
exception
 when u_exp then
 rollback;
 dbms_output.put_line('no of records updation is greater then 1, please contact IT suppprt team');
 when others then
 dbms_output.put_line('Error in the main procedure '||sqlerrm);
END;
/


write a plsql procedure  to increase the salary of the employee of the emp
table by 10% passing the salgrade as the parameter.Print the emp names ,empno, old salary and new salary and no of records being updated.if the passed salgrade is invalid print the appropriate message.
If the value of parameter is null then update all the records…
------------------------------------------------------------------------

Parameterized cursors

Ex: print the dept name and the employees for those deptnames

create or replace procedure xx_dtls(p_deptno number)
is
cursor c_dept is select * from dept where deptno = nvl(p_deptno,deptno);
cursor c_emp(v_deptno number) 
is select * from emp where deptno = v_deptno;
begin
for r_dept in c_dept
 loop
  dbms_output.put_line('----------------------');
  dbms_output.put_line(r_dept.dname);
  for r_emp in c_emp(r_dept.deptno)
  loop
    dbms_output.put_line(r_emp.ename);
  end loop;
end loop;
exception
 when others then
 dbms_output.put_line('Error ins the xx_dtls procedure');
END;
/

exception
 when others then
 dbms_output.put_line('Error ins the xx_dtls procedure');
END;
/

----




Print the deptno details and then print the emp details for each deptno and no of employees working in that deptno.If no employees working for them then print it as no employees exists for that deptno.
Take the deptno as parameter and if the value is passed as null then pick all deptno information and if the value passed is invalid then print as invalid deptno passed…
Print the subtotal salary for each deptno
Print the total salary for all the deptno’s

o/p :

 ----------------------Start of the report-----------------------------------------------
  The Deptno is                     10
  The Department Name is Accounting
  The Location is                    Newyork

     The ename is rakesh patel
     The empno  is 1234
     The salary    is 1000
     ------
     The ename is ankul
     The empno  is 123
     The salary    is 1000

 The total salary for deptno 10 is 2000
  The total no of employees working in the deptno 10 is 2


The total salary for all deptno’s is 10000
--------------------------End of the Report -------------------------------------------------


  
Output :

create or replace procedure dept_dtls(p_deptno number)
is
cursor c_dept
       is select *
        from dept_ank
        where deptno = nvl(p_deptno,deptno);
cursor c_emp(v_deptno number)   /*Parameterised Cursor*/
is  select *
    from emp_ank
    where deptno = v_deptno;
/*Variables*/
dept_tot_sal number:=0;
emp_cnt number:=0;
tot_sal number:=0;
v_flag number:=0;
/*Execution Section*/
begin
dbms_output.put_line('----------------------Start of the report-------------------------');
for r_dept in c_dept
 loop
  v_flag:=1;
  dept_tot_sal:=0;
  emp_cnt:=0;
  dbms_output.put_line('Department No is:   '||r_dept.deptno);
  dbms_output.put_line('Department Name is:         '||r_dept.dname);
  dbms_output.put_line('Department Location is:     '||r_dept.loc);
  dbms_output.put_line('------------------------------------');
 for r_emp in c_emp(r_dept.deptno)
  loop
    dbms_output.put_line('Employee Number:           '||r_emp.empno);
    dbms_output.put_line('Employee Name:   '||r_emp.ename);
    dbms_output.put_line('Salary:                   '||r_emp.sal);
    dbms_output.put_line('---------------------------------');
    dept_tot_sal:=dept_tot_sal+r_emp.sal;
    emp_cnt:=emp_cnt+1;
  end loop;
  if emp_cnt=0 then
  dbms_output.put_line('No employee working for department no' ||r_dept.deptno);
  else
  dbms_output.put_line('The total no of employees working in deptno'||r_dept.deptno|| ' is ' ||emp_cnt); 
  dbms_output.put_line('The total salary for deptno'||r_dept.deptno|| ' is ' ||dept_tot_sal);
  end if; 
dbms_output.put_line('*****************************************************');
tot_sal:=tot_sal+dept_tot_sal;
end loop;
if v_flag=0 then
dbms_output.put_line('Invalid department number. Please enter the valid department number.');
else
dbms_output.put_line('The total salary of all the departments is ' ||tot_sal);
end if;
dbms_output.put_line('--------------------------End of the Report------------------------');
exception
 when others then
 dbms_output.put_line('Error ins the dept_dtls procedure'||sqlerrm);
END;
/









Using a loop in a loop,….
  You can use either parameterized cursors or we can use below way as well…

create or replace procedure dept_dtls(p_deptno number)
is

/*Variables*/
dept_tot_sal number:=0;
emp_cnt number:=0;
tot_sal number:=0;
v_flag number:=0;
/*Execution Section*/
begin
dbms_output.put_line('----------------------Start of the report-------------------------');
for r_dept in (select *
            from dept_ank
            where deptno = nvl(p_deptno,deptno))
 loop
  v_flag:=1;
  dept_tot_sal:=0;
  emp_cnt:=0;
  dbms_output.put_line('Department No is:   '||r_dept.deptno);
  dbms_output.put_line('Department Name is:         '||r_dept.dname);
  dbms_output.put_line('Department Location is:     '||r_dept.loc);
  dbms_output.put_line('------------------------------------');
 for r_emp in (select *
                        from emp_ank
                        where deptno = r_dept.deptno)
  loop
    dbms_output.put_line('Employee Number:           '||r_emp.empno);
    dbms_output.put_line('Employee Name:   '||r_emp.ename);
    dbms_output.put_line('Salary:                   '||r_emp.sal);
    dbms_output.put_line('---------------------------------');
    dept_tot_sal:=dept_tot_sal+r_emp.sal;
    emp_cnt:=emp_cnt+1;
  end loop;
  if emp_cnt=0 then
  dbms_output.put_line('No employee working for department no' ||r_dept.deptno);
  else
  dbms_output.put_line('The total no of employees working in deptno'||r_dept.deptno|| ' is ' ||emp_cnt); 
  dbms_output.put_line('The total salary for deptno'||r_dept.deptno|| ' is ' ||dept_tot_sal);
  end if; 
dbms_output.put_line('*****************************************************');
tot_sal:=tot_sal+dept_tot_sal;
end loop;
if v_flag=0 then
dbms_output.put_line('Invalid department number. Please enter the valid department number.');
else
dbms_output.put_line('The total salary of all the departments is ' ||tot_sal);
end if;
dbms_output.put_line('--------------------------End of the Report------------------------');
exception
 when others then
 dbms_output.put_line('Error ins the dept_dtls procedure'||sqlerrm);
END;
/
---
Ex:
-----------------------------

2)Print the account details and print the transaction details for each accno and no of transactions takes palce for each accno. Pass the accno as parameter and if the accno parameter value is passed as  null then print for all accno’s else if it is passed and if that accno is invalid then print the appro[riate message as invalid parameter value.


Different modes of parameters:

IN   : The default parameter is IN. the value should be passed in to procedure mandatory.
OUT: Need to pass some variable where using out mode we can fetch the value from the procedure
INOUT: Pass the input values and assign some other value in the procedure and print it outside the procedure by fetching it in to variable.


create or replace procedure p_emp_out(p_empno number
                                                                                                              ,p_ename out varchar2
                                                                                                              ,p_sal   out number)
is
BEGIN
 BEGIN
  select ename,sal
  into   p_ename,p_sal
  from   emp
  where  empno = p_empno;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
   p_ename := null;
   p_sal := null;
  WHEN OTHERS THEN
   dbms_output.put_line('Error in the select statement'||SQLERRM);
 END;

EXCEPTION
  WHEN OTHERS THEN
   dbms_output.put_line('Error in the main proceudre'||SQLERRM);
END p_emp_out;



declare
 ln_ename emp.ename%type;
 ln_sal emp.sal%type;
begin
p_emp_out(7369,ln_ename,ln_sal);
dbms_output.put_line('Ename is -->'||ln_ename);
dbms_output.put_line('Salary is -->'||ln_sal);
end;

Inout example
---------------------------
create or replace procedure p_emp_out(p_empno in out number                                                                                                    ,p_ename out varchar2
                                      ,p_sal   out number)
is
BEGIN
 BEGIN
  select ename,sal
  into   p_ename,p_sal
  from   emp
  where  empno = p_empno;
p_empno := 9999;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
  p_empno := 0000;
   p_ename := null;
   p_sal := null;
  WHEN OTHERS THEN
   dbms_output.put_line('Error in the select statement'||SQLERRM);
 END;
 EXCEPTION
  WHEN OTHERS THEN
   dbms_output.put_line('Error in the main proceudre'||SQLERRM);
END p_emp_out;

declare
 ln_ename emp.ename%type;
 ln_sal emp.sal%type;
 ln_empno number := 7369;
begin
p_emp_out(ln_empno,ln_ename,ln_sal);
dbms_output.put_line('Ename is -->'||ln_ename);
dbms_output.put_line('Salary is -->'||ln_sal);
dbms_output.put_line('empno -->'||ln_empno);

end;

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...