-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
PL/ SQL:
There are two types cursors 1) Implicit Cursors 1) Implicit Cursors: Implicit Cursor Attributes: 1) SQL%ISOPEN: 2) SQL%FOUND: 3) SQL%NOTFOUND: 4) SQL%ROWCOUNT: Using SQL%FOUND: Output: By looking at the above message, we cannot know whether your update command is effecting the data or not. To overcome this problem, we have SQL%FOUND attribute. Begin Output: PL/SQL Procedure successfully completed. Using SQL%NOTFOUND: We rewrite the above program using SQL%NOTFOUND Begin PL/SQL Procedure successfully completed. Using SQL%ROWCOUNT: SQL%ROWCOUNT attribute is used to find the no of rows effected by SQL command. begin Output: Note: As a developer, we cannot control the implicit cursor. Explicit Cursors: Steps to use Explicit cursors: Step 1: Declare the cursor Syntax of the above four steps: Step 1: Declaring the cursor cursor < cursor_name> step 2: Open the cursor open < cursor_name >; step 3: Fetch the data from the cursor to the local variables fetch < cursor_name > into < var1 > , < var2> , ....., < varn >;; step 4: close the cursor close < cursor_name>; Explicit cursor attributes: There are four explicit cursor attributes 1) %ISOPEN 1) %ISOPEN: 2) %FOUND: 3) %NOTFOUND: Note: 1) It is exactly opposite to %FOUND attribute 4) %ROWCOUNT: Example of Explicit cursor: Write a PL/SQL block to display ename and sal of employees working in deptno no Declare l_ename emp.ename%type; begin loop end; Output: CLARK 2450 Pl/SQL Proceudure successfully completed. Ex2: Write a PL/SQL procedure to display dname , loc from dept table Declare l_dname dept.dname%type; begin open c1; end loop; end; Output: Pl/SQL Procedure successfully completed.
Cursor For loops: Ex: Declare for emp_rec in c1 loop end; Output: CLARK 2450 Pl/SQL Proceudure successfully completed. Note: In the above program emp_rec in implicitly declared record variable,
|