oracle 11g cursor new
1) explicit cursor - use sql%found or sql%notfound with select statement...
Note- cursor is just like place holder. it keeps the the value of your sql query and if you want to view those value u need varible to view that result.
======================================================================================
what is cursor ?
Ans- When an SQL statement is processed, Oracle creates a memory area known as context area. A cursor is a pointer to this context area. It contains all information needed for processing the statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on a select statement and the rows of data accessed by it.
There are two types of cursors:
Implicit Cursors -- These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed.
Explicit Cursors -- The Explicit cursors are defined by the programmers to gain more control over the context area
Attribute Description
%FOUND Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at least one row or more rows or a SELECT INTO statement returned one or more rows. Otherwise it returns FALSE.
%NOTFOUND Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise it returns FALSE. It is a just opposite of %FOUND.
%ISOPEN It always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements.
%ROWCOUNT It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.
implicit cursor--
---------------------
set serveroutput on;
declare
total_row number(2);
BEGIN
delete from emp where sal=0;
if sql%notfound then
dbms_output.put_line('no record');
elsif sql%found then
total_row := sql%rowcount;
dbms_output.put_line( total_row || ' customers selected ');
END IF;
END;
explicit cursor --
----------------------
DECLARE
c_empno emp.empno%type;
c_name emp.ename%type;
c_addr emp.job%type;
CURSOR cur_emp is
SELECT empno, ename, job FROM emp;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp into c_empno, c_name, c_addr;
EXIT WHEN sql%notfound;
dbms_output.put_line(c_empno || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE cur_emp;
END;
/
CLOSE cursor
This stage destroys the cursor’s life in the current session and flushes out the context area. After the context area is freed up, this memory is then released back to the PGA and is readily available for future use. Beware of closing all the available explicit cursors after their usage, as the memory occupied by them may not be released back until the lifespan of their underlying program if this stage is not properly handled.