Cursors

Cursors: You can define Cursor Statements in Declaration section. Cursor statements let you bring data from tables and views into your PL/SQL Programs.

Why use cursors? Well when you retrieve subset of data from table (or whole table), then that data remains stored in SGA (Shared memory) until cursor is closed, so in this way you cache data and caching on database is good idea.

Choosing explicit or implicit cursor in your PL/SQL program?

Implicit cursors are used when you have a simple SELECT ... INTO single row of data into local program variables. It's the easiest path to your data, but it can often lead to coding the same or similar SELECTs in multiple places in your code.

Explicit cursors are defined in declaration section (package or block) and in this way, you can open and fetch from cursor in one or more places.

Implicit cursor will run more efficient than equivalent explicit cursor (from Oracle 8 Database onwards). So is there reasons to use explicit cursors at all? Off course. Explicit cursor can still be more efficient and they off course offer much programmatic control.

Implicit cursor

Implicit cursors are used when you need to retrieve single row from database. If you want to retrieve more than one row, then you must use either an explicit cursor or bulk collect.

Here one example of implicit cursor usage:

view plainprint?

1. SET serveroutput on;  
2.   
3. DECLARE  
4.   
5.    PROCEDURE find_employee (employee_id_v employees.employee_id%TYPE)  
6.    IS  
7.       --Record in which we will fetch entire row.    
8.       emp_rec   employees%ROWTYPE;  
9.    BEGIN  
10.       --Begining of implicit cursor statement.  
11.       SELECT *  
12.         INTO emp_rec --Fetch into record.  
13.         FROM employees  
14.        WHERE employee_id = employee_id_v;  
15.        --Write result.  
16.       DBMS_OUTPUT.put_line (emp_rec.employee_id || ' ' || emp_rec.first_name);  
17.    --Catch exception when there is no such employee.  
18.    EXCEPTION  
19.       WHEN NO_DATA_FOUND  
20.       THEN           
21.          DBMS_OUTPUT.put_line ('Unknown employee with id: ' || employee_id_v);  
22.    END find_employee;  
23. BEGIN  
24.    find_employee (101);  
25.    find_employee (102);  
26.    --This one will produce exeption (OK, only if you do not have employee  with id 1021).  
27.    find_employee (1021);  
28. END;  

SET serveroutput on;

DECLARE

   PROCEDURE find_employee (employee_id_v employees.employee_id%TYPE)
   IS
      --Record in which we will fetch entire row.  
      emp_rec   employees%ROWTYPE;
   BEGIN
      --Begining of implicit cursor statement.
      SELECT *
        INTO emp_rec --Fetch into record.
        FROM employees
       WHERE employee_id = employee_id_v;
       --Write result.
      DBMS_OUTPUT.put_line (emp_rec.employee_id || ' ' || emp_rec.first_name);
   --Catch exception when there is no such employee.
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN         
         DBMS_OUTPUT.put_line ('Unknown employee with id: ' || employee_id_v);
   END find_employee;
BEGIN
   find_employee (101);
   find_employee (102);
   --This one will produce exeption (OK, only if you do not have employee  with id 1021).
   find_employee (1021);
END;

We encapsulate query with function (this is _aways_ a good idea). This function print employee information from database to output. Also we introduce some exception handling (when
no employee is found).

Because PL/SQL is so tightly integrated with the Oracle database, you can easily retrieve complex datatypes (entire row for example - as we did in our example).

You can see that using implicit cursor is quite simple (with basic understanding of SQL) we just create simple select statement and insert rowset into record (that we declared as local variable).

Explicit cursor

Explicit cursor is explicitly defined in the declaration section. With explicit cursor, you have complete control over the different PL/SQL steps involved in retrieving information from the database. You decide when to open, when fetch and how many records and when to close cursor. Information about the current state of cursor is available through examination of cursor attributes.

Example:

view plainprint?

1. SET SERVEROUTPUT on;  
2.   
3. DECLARE  
4.    PROCEDURE get_all_employees  
5.    IS  
6.       --Employee record variable.  
7.       employee_rec   employees%ROWTYPE;  
8.       --Cursor variable for explicit use.  
9.       CURSOR employee_cur  
10.       IS  
11.          SELECT *  
12.            FROM employees;  
13.    BEGIN  
14.       --Open cursor so you can use it.        
15.       OPEN employee_cur;  
16.       --Go through all employees.  
17.       LOOP  
18.          --Load current row from cursor into employee record.   
19.          FETCH employee_cur  
20.           INTO employee_rec;  
21.          --Loop until cursor attribute signals that no rows are found.  
22.          EXIT WHEN employee_cur%NOTFOUND;  
23.          DBMS_OUTPUT.put_line (   employee_rec.employee_id  
24.                                || ', '  
25.                                || employee_rec.first_name  
26.                               );  
27.       END LOOP;  
28.   
29.       CLOSE employee_cur;  
30.    EXCEPTION  
31.       --Remember to close cursor even if there was some error.  
32.       WHEN OTHERS  
33.       THEN  
34.          IF employee_cur%ISOPEN  
35.          THEN  
36.             CLOSE employee_cur;  
37.          END IF;  
38.    END get_all_employees;  
39. BEGIN  
40.    get_all_employees ();  
41. END;  

SET SERVEROUTPUT on;

DECLARE
   PROCEDURE get_all_employees
   IS
      --Employee record variable.
      employee_rec   employees%ROWTYPE;
      --Cursor variable for explicit use.
      CURSOR employee_cur
      IS
         SELECT *
           FROM employees;
   BEGIN
      --Open cursor so you can use it.      
      OPEN employee_cur;
      --Go through all employees.
      LOOP
         --Load current row from cursor into employee record. 
         FETCH employee_cur
          INTO employee_rec;
         --Loop until cursor attribute signals that no rows are found.
         EXIT WHEN employee_cur%NOTFOUND;
         DBMS_OUTPUT.put_line (   employee_rec.employee_id
                               || ', '
                               || employee_rec.first_name
                              );
      END LOOP;

      CLOSE employee_cur;
   EXCEPTION
      --Remember to close cursor even if there was some error.
      WHEN OTHERS
      THEN
         IF employee_cur%ISOPEN
         THEN
            CLOSE employee_cur;
         END IF;
   END get_all_employees;
BEGIN
   get_all_employees ();
END;

This PL/SQL block performs following:
Declare the cursor.
Declare а record based on that cursor.
Open the cursor.
Fetch rows until there are no rows left.
Close cursor.
Handle exception and close cursor if it is not closed.

Copied from link

Simple PL/SQL Procedure

create table emp (Acolumn number(8,2),Bcolumn varchar2(50))
create table emp_bkp as select * from emp
insert into emp (acolumn) values (1);
select * from emp_bkp
truncate table emp_bkp


There are two types of cursors
1. Implicit Cursor
2. Explicit Cursor

Implicit Cursor is used by Oracle Server to pass and execute SQL Statements.
SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.

Explicit Cursors are explicitly declared by programmer.

Boolean data type means the o/p will be TRUE or FALSE.

IF and Case statements
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSEIF-END IF

IF Syntax:
IF Condition THEN
Statements;
ELSEIF Condition THEN
Statements;
ELSE
Statements;
END IF;

Case Syntax:
CASE Column name or CASE
When ‘A’ then ‘AJ’
WHEN ‘B’ then ‘BD’
ELSE ‘NO SUCH VALUE’
END;

:variable_name is called as bind variable
/* Procedure 1 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE := 10;
begin
select Acolumn into v_evnt_ky from emp where Acolumn=1;
insert into emp_bkp (Acolumn) values (v_evnt_ky);
commit;
end;

/* Procedure 2 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
--select Acolumn into v_evnt_ky from emp where Acolumn=1;
insert into emp_bkp (Acolumn) values (v_bonus);
commit;
END IF;
end;

/* Procedure 3 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
DBMS_OUTPUT.PUT_LINE('This is my first PL/SQL Procedure');
commit;
END IF;
end;

/*Procedure for Informatica*/
create or replace procedure dummy_emp (
v_evnt_ky IN number,
v_bonus IN emp.acolumn%TYPE )
IS
begin
IF v_bonus = 10 THEN
insert into emp_bkp (Acolumn) values (v_evnt_ky);
commit;
END IF;
end dummy_emp;

/*Display mailing address*/
declare
v_name varchar2(50) :='Aj';
v_address varchar2(30) :='Veedhi';
v_state varchar2(50) :='SJC';
v_m_address varchar2(50);
begin
v_m_address:=v_name||chr(10)||v_state||chr(10)||v_address;
DBMS_OUTPUT.PUT_LINE(v_m_address);
END;
o/p:Aj
SJC
Veedhi

/*Qualify an Identifier: By using Block label Prefix*/

< < OUTER > >
declare
birthdate date;
begin
    declare
    birthdate date;
    Begin
    OUTER.birthdate:=to_date('03/01/2011','mm/dd/yyyy');
    END;
    DBMS_OUTPUT.PUT_LINE(birthdate);
    END;
   -- O/P=01-MAR-11

/* Raise the error Using raise application error message*/

declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
select Acolumn into v_evnt_ky from emp where Acolumn=2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('HISTORY');
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-'||SQLERRM);
end;

O/P:

ORA-20001: An error was encountered - 100 -ERROR- ORA-01403: no data found

ORA-06512: at line 14


/* Using Define in PL/SQL */
DEFINE P_NUM1=9;
DEFINE P_NUM2=3;
DECLARE
    V_NUM1 number :=&P_NUM1;
    V_NUM2 number :=&P_NUM2;
     V_RESULT number;
BEGIN
    V_RESULT := (V_NUM1/V_NUM2)+V_NUM2;
    /*Printing PL/SQL Variable*/
    DBMS_OUTPUT.PUT_LINE(V_RESULT);
    END;

 /*Using Merge function*/

DECLARE
   v_bonus   emp.acolumn%TYPE := 1;
BEGIN
MERGE INTO EMP_BKP BKP
USING EMP E
ON
(BKP.ACOLUMN=v_bonus)
WHEN MATCHED THEN
UPDATE
SET
--BKP.ACOLUMN = E.ACOLUMN,
BKP.BCOLUMN =E.BCOLUMN
WHEN NOT MATCHED THEN
INSERT VALUES(E.ACOLUMN,E.BCOLUMN);
   COMMIT;
END;