What is Cursor Advantages and Disadvantages?

Advantages of using Cursor: 

1. Using Cursor we can perform row by row processing so we can perform row wise validation or operations on each row.

2.Cursors can provide the first few rows before the whole result set is assembled. Without using cursors, the entire result set must be delivered before any rows are displayed by the application. So using cursor, better response time is achieved. 

3. If we make updates to our without using cursors in your application then we must send separate SQL statements to the database server to apply the changes. This can cause the possibility of concurrency problems if the result set has changed since it was queried by the client. In turn, this raises the possibility of lost updates. So using cursor, better concurrency Control can be achieved.

4.Cursors can be faster than a while loop but at the cost of more overhead.

Disadvantages of using Cursor: 

Cursor in SQL is temporary work area created in the system memory, thus it occupies memory from your system that may be available for other processes. So occupies more resources and temporary storage. 

Each time when a row is fetched from the cursor it may result in a network round trip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE etc that makes only one round trip. 

Repeated network round trips can degrade the speed of the operation using the cursor.

What is the difference between implicit and explicit cursors in Oracle?

Implicit Cursor:

Implicit cursors are automatically created and used by Oracle each time a select statement is issued. If an implicit cursor is used, the Database Management System (DBMS) will perform the open, fetch and close operations automatically. Implicit cursors should be used only with SQL statements that return a single row. If the SQL statement returns more than one row, using an implicit cursor will introduce an error. An implicit cursor is automatically associated with each Data Manipulation Language (DML) statements, namely INSERT, UPDATE and DELETE statements. Also, an implicit cursor is used to process SELECT INTO statements. When fetching data using implicit cursors NO_DATA_FOUND exception can be raised when the SQL statement returns no data. Furthermore, implicit cursors can raise TOO_MANY_ROWS exceptions when the SQL statement returns more than one row.

Explicit Cursor:

 An explicit cursor can be thought of as a pointer to a set of records and the pointer can be moved forward within the set of records. Explicit cursors provide the user the complete control over opening, closing and fetching data. Also, multiple rows can be fetched using an explicit cursor. Explicit cursors can also take parameters just like any function or procedure so that the variables in the cursor can be changed each time it is executed. In addition, explicit cursors allow you to fetch a whole row in to a PL/SQL record variable. When using an explicit cursor, first it needs to be declared using a name. Cursor attributes can be accessed using the name given to cursor. After declaring, cursor needs to be opened first. Then fetching can be started. If multiple rows need to be fetched, the fetching operation needs to be done inside a loop. Finally, the cursor needs to be closed.

Difference Between Explicit Cursor and Implicit Cursor

The main difference between the implicit cursor and explicit cursor is that an explicit cursor needs to be defined explicitly by providing a name while implicit cursors are automatically created when you issue a select statement. Furthermore, multiple rows can be fetched using explicit cursors while implicit cursors can only fetch a single row. Also NO_DATA_FOUND and TOO_MANY_ROWS exceptions are not raised when using explicit cursors, as opposed to implicit cursors. In essence, implicit cursors are more vulnerable to data errors and provide less programmatic control than explicit cursors. Also, implicit cursors are considered less efficient than explicit cursors.

Difference between SYS_REFCURSOR and REF CURSOR

SYS_REFCURSOR is Weak reference cursor whereas ref cursor is strong reference cursor.

SYS_REFCURSOR do not have return type whereas ref cursor have return type.

We need to delcare variable of that type in ref cursor .In sys_refcursor no need of variable delclaration.

Example of Weak Ref Cursor

declare    

    c1 SYS_REFCURSOR;

    ename varchar2(10);

    sal number;

begin

    open c1 for select ename, sal from scott.emp;

    LOOP 

        FETCH c1 into ename, sal;

            EXIT WHEN c1%NOTFOUND;

        dbms_output.put_line('Ename: ' || ename || ', Salary: ' || sal);

    END LOOP;

    close c1;    

end;

/

Example of Strong Ref Cursor

Declare

Type Refcursor is ref cursor return emp%rowtype;

C1 refcursor;

E Emp%rowtype;

Begin

 Open C1 for select * from emp

 loop

 fetch c1 ino  E;

 exit when c1%NOTFOUND;

 dbms_output_put_line(e.first_name||e.sal);

 end loop;

END;

Difference Between Cursor And Ref Cursor

Lets first check the basic example of Cursor and Ref Cursors. In this post Cursor means PL/SQL Cursors only.

Example of Cursor:

declare    

    cursor c1 is select ename, sal from scott.emp;

begin    

    for c in c1 

    loop

        dbms_output.put_line('Ename: ' || c.ename || ', Salary: ' || c.sal);

    end loop;

end;

/

Example of Ref Cursor

declare    

    c1 SYS_REFCURSOR;

    ename varchar2(10);

    sal number;

begin

    open c1 for select ename, sal from scott.emp;

    LOOP 

        FETCH c1 into ename, sal;

            EXIT WHEN c1%NOTFOUND;

        dbms_output.put_line('Ename: ' || ename || ', Salary: ' || sal);

    END LOOP;

    close c1;    

end;

/

Technically, They are both cursors and can be processed in the same fashion and at the most basic level, they both are same. There are some important differences between regular cursors and ref cursors which are following: 

1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.

2) A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.

3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.

4) A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.

5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.

6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.

My recommendation on ref cursors:

Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.

Explain briefly about Cursor :

Cursor is  private area which hold information of SQL statement.

A cursor can hold more than    one row, but can process only one row at a time. The set of rows the cursor holds is called the active    set.For processing SQL statement ,oracle create area of memory know as context area. A cursor in nothing  but pointer to active data set. When Cursor is declared ,a pointer is returned which initially point to nothing. when cursor is opened , appropriate memory is allocated.When cursor is closed the memory allocated for cursor is released.

Difference between cursor for loop and for loop, which is faster?

Cursor for loop is faster than for loop.

Cursor for loop implicitly declare %rowtype variables,its used index,

open cursor ,fetch records from active dataset and load records into loop variables and close cursor

automatically.

In For we need to explicitly declare %rowtype,open cursor,fetch reocords and load records into loop variables

How can you declare the cursor in proc.?

Cursor <Cursor_Name> Is

Select <Column_Name)  From <Table_Name>

How do we process records in cursor?

We Declare cursor,Open ,Fetch cursor records into Variable and Close the cursor

How to find how many records it fetched?

%rowcount attribute of cursor tells how many records fteched.

Tell me some restrictions on cursor variables?

Cursor variables are subject to the following restrictions; note that Oracle may remove some of these in future releases.

Cursor variables cannot be declared in a package because they do not have a persistent state.

You cannot use RPCs (remote procedure calls) to pass cursor variables from one server to another.

If you pass a cursor variable as a bind variable or host variable to PL/SQL, you will not be able to fetch from it from within the server unless you also open it in that same server call.

The query you associate with a cursor variable in an OPEN FOR statement cannot use the FOR UPDATE clause. (This is allowed in Oracle9i and above.)

You cannot test for cursor variable equality, inequality, or nullity using comparison operators.

You cannot assign NULLs to a cursor variable. Attempts to do so will result in a PLS-00382 Expression is of wrong type error message.

Database columns cannot store cursor variable values. You will not be able to use REF CURSOR types to specify column types in statements to CREATE TABLEs.

The elements in a nested table, associative array, or VARRAY cannot store the values of cursor variables. You will not be able to use REF CURSOR types to specify the element type of a collection.

What are cursor attributes?

1.%Found

2.%notfound.

3.%isopen

4.%rowcount

What is the purpose of FOR UPDATE?

The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set.

You are not required to make changes to the records in order to use this statement. 

The record locks are released when the next commit or rollback statement is issued.

CURSOR cursor_name

IS

   select_statement

   FOR UPDATE [OF column_list] [NOWAIT];

   

   WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the FOR UPDATE clause to use this feature.

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:

WHERE [CURRENT OF cursor_name | search_condition]

The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.