Implicit Cursor

Oracle uses implicit cursors for its internal processing. Even if we execute a SELECT statement Oracle reserves a private SQL area in memory called cursor.

We update the employee 'Mitesh' department is update to 'Program Developer'.

Example Code:

Emp Table 




 


  

  

EMP_NO

  

  

EMP_NAME

  

  

EMP_DEPT

  

  

EMP_SALARY

 

  

  

1

  

  

Amruta

  

  

Web Developer

  

  

25000

 

  

  

2

  

  

Meena

  

  

Program Developer

  

  

38000

 

  

  

3

  

  

Mitesh
Web Developer
  
  

  

30000

 

  

  

4

  

  

Mayank

  

  

Web Developer

  

  

40000

 

SQL>set serveroutput on
SQL>edit implicit_cursor
BEGIN
 UPDATE emp SET emp_dept='Web Developer'
  WHERE emp_name='Mitsh';

IF SQL%FOUND THEN
 dbms_output.put_line('Updated - If Found');
END IF;

IF SQL%NOTFOUND THEN
 dbms_output.put_line('NOT Updated - If NOT Found');
END IF; 

IF SQL%ROWCOUNT>0 THEN
 dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');
ELSE
 dbms_output.put_line('NO Rows Updated Found');
END;
/
SAVE: implicit_cursor.sql

Read More

Explicit Cursor

Explicit Cursor which declared by user are called Explicit Cursor. User is declare and open the cursor to reserve 

the memory and fetch the populated data from the active data set one at a time, and last close the cursor.

Step for Using Explicit Cursor

Declare Cursor

Open Cursor

Loop

Fetch Record into Cursor

Exit Loop

Close Cursor

Explicit Cursor variabl

Cursor Attribute	Cursor Variable	Description
%ISOPEN	c%ISOPEN	Oracle engine automatically open the cursor If cursor open return true otherwise return false.
%FOUND	c%FOUND	If selected return one or more than one row INSERT, UPDATE, DELETE operation affect If affect return true otherwise return false.
%NOTFOUND	c%NOTFOUND	If selected return one or more than one row INSERT, UPDATE, DELETE operation not affect If not affect the row return true otherwise return false.
%ROWCOUNT	c%ROWCOUNT	Return the number of rows affected by an insert, update, delete or select statement.

Step for Using Explicit Cursor:

Explicit Cursor Example

MP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Hema	Java  Developer	20000
2	Meena	Program Developer	30000
3	Girish	Program Developer	30000
4	Nasim	Web Developer	45000

We update the employee 'Girish' department is update to 'web developer'.

SQL>set serveroutput on

SQL>edit emp_cursor

DECLARE

         cursor c is select * from emp_information

         where emp_name='Girish';

         emp_information%rowtype;

BEGIN 

         OPEN c;

         Loop exit when c%NOTFOUND;

         FETCH c into temp;

         update emp_information set emp_dept='Web Developer'

         where tmp.emp_name='Girish';

         END Loop;

IF c%ROWCOUNT>0 THEN

         dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');

ELSE

         dbms_output.put_line('NO Rows Updated Found');

END IF;

CLOSE c;

END;     

/

SAVE: emp_cursor.sql

Read More

Example Of Explicit Cursor

%ROWCOUNT and %NOTFOUND: Example

This Example  retrieves the first  5 employees one by one. This example shows how 

%ROWCOUNT and %NOTFOUND attributes can be used for exit conditions in a loop.

SET SERVEROUTPUT ON

DECLARE

  empno  employees.employee_id%TYPE;

  ename  employees.last_name%TYPE;

  CURSOR emp_cursor IS SELECT employee_id,

  last_name FROM employees;

BEGIN

  OPEN emp_cursor;

  LOOP

   FETCH emp_cursor INTO empno, ename;

   EXIT WHEN emp_cursor%ROWCOUNT > 5 OR 

                     emp_cursor%NOTFOUND;       

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno)

                       ||' '|| ename);

  END LOOP;

  CLOSE emp_cursor;

END ;

Read More

Examples Of implicit cursor

What is implicit cursor in Oracle? -

A session contains a single implicit cursor which is defined automatically by PL/SQL.
The cursor gets assigned to represent the execution of a statement whenever it is executed.

Following are the attributes of the implicit cursor:

Attribute                                Status                    Condition
SQL%FOUND                      TRUE                     if the SQL statement has changed any rows
SQL%NOTFOUND              TRUE                     if the SQL statement has not changed any rows              
SQL%ROWCOUNT            -                                 The number of rows affected by the SQL statement
%ISOPEN                             TRUE                     if there is a SQL statement being associated to the cursor. 


DECLARE  var_rows number(5);

BEGIN

  UPDATE employees

  SET salary = salary + 1000;

  IF SQL%NOTFOUND THEN

    dbms_output.put_line('None of the salaries where updated');

  ELSIF SQL%FOUND THEN

    var_rows := SQL%ROWCOUNT;

    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

  END IF;

END;


Output:-

Salaries for 107 employees are updated

PL/SQL procedure successfully completed.

In the above PL/SQL Block, the salaries of all the employees in the ‘employee’
table are updated. If none of the employee’s salary are updated
we get a message 'None of the salaries where updated'.
Else we get a message like for example, 'Salaries for 107 employees are updated' if there are 107 rows in ‘employee’ table.

Read More

Cursors with Parameters

Cursors with Parameters

You can pass parameters to a cursor in a cursor FOR loop. This means that you can open and close an

explicit cursor several times in a block, returning a different active set on each occasion. For each

execution, the previous cursor is closed and reopened with a new set of parameters.
Each formal parameter in the cursor declaration must have a corresponding actual parameter in the

OPEN statement. Parameter data types are the same as those for scalar variables, but you do not

give them sizes. The parameter names are for references in the query expression of the cursor.


In the syntax:
cursor_name Is a PL/SQL identifier for the declared cursor
parameter_name Is the name of a parameter
datatype Is the scalar data type of the parameter
select_statement Is a SELECT statement without the INTO clause


Example


DECLARE
  V_NAME EMPLOYEES.LAST_NAME%TYPE;
  V_SAL  EMPLOYEES.SALARY%TYPE;
  -- DECLARATION OF CURSOR
  CURSOR EMPCUR(DID number)
   IS
   SELECT LAST_NAME,SALARY
   FROM EMPLOYEES
   WHERE department_ID=DID;
BEGIN
  OPEN EMPCUR(10);
  LOOP
     -- CURSOR WILL FETCH A ROW FROM ACTIVE SET.
     -- AFTER FETCHING IT WILL POINT TO NEXT ROW.
     FETCH EMPCUR INTO V_NAME,V_SAL;
     DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
     -- EXIT THE LOOP IF NO MORE ROWS TO FETCH
     EXIT WHEN EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
  OPEN EMPCUR(20);
  LOOP
     -- CURSOR WILL FETCH A ROW FROM ACTIVE SET.
     -- AFTER FETCHING IT WILL POINT TO NEXT ROW.
     FETCH EMPCUR INTO V_NAME,V_SAL;
     DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
     -- EXIT THE LOOP IF NO MORE ROWS TO FETCH
     EXIT WHEN EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
  OPEN EMPCUR(50);
  LOOP
     FETCH EMPCUR INTO V_NAME,V_SAL;
     DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
     -- EXIT THE LOOP IF NO MORE ROWS TO FETCH
     EXIT WHEN EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
END;
/

Read More

What is a Cursor ?

What is a Cursor ?


A cursor is a pointer to the private memory area allocated by the Oracle server.

There are two types of cursors:
Implicit: Created and managed internally by the Oracle server to process SQL statements
Explicit: Explicitly declared by the programmer

The Oracle server allocates a private memory area called the context area for processing SQL statements. The SQL statement is parsed and

processed in this area. Information required for processing and information retrieved after processing is all stored in this area. You have no control

over this area because it is internally managed by the Oracle server.
A cursor is a pointer to the context area. However, this cursor is an implicit cursor and is automatically managed by the Oracle server. When the

executable block issues a SQL statement, PL/SQL creates an implicit cursor.
There are two types of cursors:
Implicit: Implicit cursors are created and managed by the Oracle server. You do not have access to them. The Oracle server creates such a cursor

when it has to execute a SQL statement.

Read More

Loops In Pl-Sql

Iterative Statements in PL/SQL

An iterative control Statements are used when we want to repeat the execution of one or more statements for specified number of times. These are similar to those in

There are three types of loops in PL/SQL:
• Simple Loop
• While Loop
• For Loop

1) Simple Loop

A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

The General Syntax to write a Simple Loop is:

LOOP 

   statements; 

   EXIT; 

   {or EXIT WHEN condition;}

END LOOP; 

These are the important steps to be followed while using Simple Loop.

1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without WHEN condition, the statements in the loop is executed only once.

DECLARE

     counter INTEGER := 2;

 BEGIN

     counter := 0;

     WHILE counter < 6 LOOP

       counter := counter + 1;

       DBMS_OUTPUT.PUT_LINE(counter);

     END LOOP;

 END;

Output :-

1

2

3

4

5

6

The PL/SQL WHILE Loop

The WHILE loop, also called a conditional loop, evaluates a condition before each loop executes, and if false, the loop is terminated.  If the expression is false when the program reaches the WHILE loop, the loop code is jumped and never executed.  Use a WHILE loop when the condition test is required at the start of the loop.  The next example contains three WHILE loops.

DECLARE

  CURSOR emp_cursor IS

   SELECT employee_id, last_name FROM employees

   WHERE department_id =30;

  empno employees.employee_id%TYPE;

  lname employees.last_name%TYPE;

BEGIN

  OPEN emp_cursor;

  FETCH emp_cursor INTO empno, lname;

  WHILE  emp_cursor%FOUND LOOP

    DBMS_OUTPUT.PUT_LINE( empno ||'  '||lname);

    FETCH emp_cursor INTO empno, lname;

  END LOOP;

END;

/

Output:-

114  Raphaely

115  Khoo

116  Baida

117  Tobias

118  Himuro

119  Colmenares

FOR Loop

A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reachs the value of the end integer.
The General Syntax to write a FOR LOOP is:

FOR counter IN val1..val2 

  LOOP statements; 

END LOOP; 

• val1 - Start integer value.
• val2 - End integer value.

begin

    for idx in 2..5 loop

      dbms_output.put_line (idx);

    end loop;

  end;

Output :-

2

3

4

5

Read More