Thursday, February 3, 2011

Parameterized cursor


DECLARE
    v_deptno NUMBER(4);
    -- department cursor declaration
     CURSOR c_dept IS
            SELECT deptno FROM dept;
     -- employee cursor declaration
     CURSOR c_emp(l_deptno NUMBER) IS
            SELECT empno, ename,sal
            FROM emp
            WHERE deptno = l_deptno;
         
BEGIN
     OPEN c_dept;
     LOOP
          FETCH c_dept INTO v_deptno;
          EXIT WHEN c_dept%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('Department No is '|| v_deptno);
         
          FOR r_emp IN c_emp(v_deptno) LOOP
              DBMS_OUTPUT.PUT_LINE('Original record '|| r_emp.empno||' - '||
              RPAD(r_emp.ename,7)||' - '||TO_CHAR(r_emp.sal,99999.99) );
              UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
          END LOOP;
         
          FOR r_emp1 IN c_emp(v_deptno) LOOP
                  DBMS_OUTPUT.PUT_LINE('Updated record- '|| r_emp1.empno||' - '||
                  RPAD(r_emp1.ename,7)||' - '||TO_CHAR(r_emp1.sal,99999.99) );
          END LOOP;
     END LOOP;
    
     CLOSE c_dept;
     ROLLBACK;
EXCEPTION
    WHEN OTHERS THEN
        IF C_DEPT%ISOPEN THEN
           CLOSE c_dept;
        END IF;
        RAISE_APPLICATION_ERROR(-20001,'An error occured  -' ||
        SQLCODE ||'- Error - '||SQLERRM);
END;
/
Department No is 10
Original record 7782 - CLARK   -   2450.00
Original record 7839 - KING    -   5000.00
Original record 7934 - MILLER  -   1300.00
Updated record- 7782 - CLARK   -   2572.50
Updated record- 7839 - KING    -   5250.00
Updated record- 7934 - MILLER  -   1365.00
Department No is 20
Original record 7369 - SMITH   -    800.00
Original record 7566 - JONES   -   2975.00
Original record 7788 - SCOTT   -   3000.00
Original record 7876 - ADAMS   -   1100.00
Original record 7902 - FORD    -   3000.00
Updated record- 7369 - SMITH   -    840.00
Updated record- 7566 - JONES   -   3123.75
Updated record- 7788 - SCOTT   -   3150.00
Updated record- 7876 - ADAMS   -   1155.00
Updated record- 7902 - FORD    -   3150.00
Department No is 30
Original record 7499 - ALLEN   -   1600.00
Original record 7521 - WARD    -   1250.00
Original record 7654 - MARTIN  -   1250.00
Original record 7698 - BLAKE   -   2850.00
Original record 7844 - TURNER  -   1500.00
Original record 7900 - JAMES   -    950.00
Updated record- 7499 - ALLEN   -   1680.00
Updated record- 7521 - WARD    -   1312.50
Updated record- 7654 - MARTIN  -   1312.50
Updated record- 7698 - BLAKE   -   2992.50
Updated record- 7844 - TURNER  -   1575.00
Updated record- 7900 - JAMES   -    997.50
Department No is 40
       
at No comments:

Nested Cursor

DECLARE
     -- local variable declaration
     v_deptno dept.deptno%TYPE;  
    
    -- department cursor declaration
     CURSOR c_dept IS
            SELECT deptno FROM dept;
     -- employee cursor declaration
     CURSOR c_emp IS
            SELECT empno, ename,sal
            FROM emp
            WHERE deptno = v_deptno;
         
BEGIN
     OPEN c_dept;
     LOOP
          FETCH c_dept INTO v_deptno;
          EXIT WHEN c_dept%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('Department No is '|| v_deptno);
          FOR r_emp IN c_emp LOOP
              DBMS_OUTPUT.PUT_LINE('Original record '|| r_emp.empno||' - '||
              RPAD(r_emp.ename,6)||' - '||TO_CHAR(r_emp.sal,99999.99) );
              UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
          END LOOP;
          FOR r_emp1 IN c_emp LOOP
                  DBMS_OUTPUT.PUT_LINE('Updated record  '|| r_emp1.empno||' - '||
                  RPAD(r_emp1.ename,6)||' - '||TO_CHAR(r_emp1.sal,99999.99) );
          END LOOP;
     END LOOP;
     CLOSE c_dept;
     ROLLBACK;
EXCEPTION
    WHEN OTHERS THEN
        IF C_DEPT%ISOPEN THEN
           CLOSE c_dept;
        END IF;
        RAISE_APPLICATION_ERROR(-20001,'An error occured  -' ||
        SQLCODE ||'- Error - '||SQLERRM);
END;
/
Department No is 10
Original record 7782 - CLARK - 2450.00
Original record 7839 - KING - 5000.00
Original record 7934 - MILLER - 1300.00
Updated record- 7782 - CLARK - 2572.50
Updated record- 7839 - KING - 5250.00
Updated record- 7934 - MILLER - 1365.00
Department No is 20
Original record 7369 - SMITH - 800.00
Original record 7566 - JONES - 2975.00
Original record 7788 - SCOTT - 3000.00
Original record 7876 - ADAMS - 1100.00
Original record 7902 - FORD - 3000.00
Updated record- 7369 - SMITH - 840.00
Updated record- 7566 - JONES - 3123.75
Updated record- 7788 - SCOTT - 3150.00
Updated record- 7876 - ADAMS - 1155.00
Updated record- 7902 - FORD - 3150.00
Department No is 30
Original record 7499 - ALLEN - 1600.00
Original record 7521 - WARD - 1250.00
Original record 7654 - MARTIN - 1250.00
Original record 7698 - BLAKE - 2850.00
Original record 7844 - TURNER - 1500.00
Original record 7900 - JAMES - 950.00
Updated record- 7499 - ALLEN - 1680.00
Updated record- 7521 - WARD - 1312.50
Updated record- 7654 - MARTIN - 1312.50
Updated record- 7698 - BLAKE - 2992.50
Updated record- 7844 - TURNER - 1575.00
Updated record- 7900 - JAMES - 997.50
Department No is 40

          
at No comments:

Cursor FOR Loop


Cursor for loop simplifies the PL/SQL program where PL/SQL itself take care most of the things which includes cursor steps and variable declaration.

Cursor for loop can be an implicit or explicit cursor. If the sole purpose to use the SQL statement once in the program then SQL statement can be specified inside the cursor “For Loop”. This form of the cursor is called Implicit Cursor for Loop.

If our requirement to use the SQL multiple time as explained in below example, then it is good to use Explicit Cursor for Loop, to avoid the re-writing of the SQL. Even through Explicit cursor is associated with a Cursor for Loop, its execution cycle is controlled by managed by Cursor for Loop internally.

Cursor "FOR LOOP" implicitly declares a loop index of %ROWTYPE record variable, which is act as a pointer to the result set processed by associated SQL statement. Value of the result set accessed by using this index variable followed by (.) dot and column name from the select list. This variable is local to the loop.

 SET SERVEROUTPUT ON
DECLARE
  CURSOR C_EMP IS
    SELECT empno,RPAD(ename,10) ename, sal
    FROM emp
    WHERE deptno = 20;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Salary before update');
  FOR r_emp in C_EMP LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.empno||' - '||r_emp.ename||' - '||TO_CHAR(r_emp.sal,99999.99));
  END LOOP;
  FOR r_emp in C_EMP LOOP
    UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'Salary after update');
  FOR r_emp in C_EMP LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.empno||' - '||r_emp.ename||' - '||TO_CHAR(r_emp.sal,99999.99));
  END LOOP;
  ROLLBACK;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception - OTHERS');
END;
/

anonymous block completed
Salary before update
7369 - SMITH      -    800.00
7566 - JONES      -   2975.00
7788 - SCOTT      -   3000.00
7876 - ADAMS      -   1100.00
7902 - FORD       -   3000.00

Salary after update
7369 - SMITH      -    840.00
7566 - JONES      -   3123.75
7788 - SCOTT      -   3150.00
7876 - ADAMS      -   1155.00
7902 - FORD       -   3150.00


at 4 comments:

Wednesday, February 2, 2011

Tips on cursor

  1. The number of variables must be equal to the number of column or expression in the SELECT list.
  2. The number of component in record must match the column or expression in the SELECT list.
  3. The scope of cursor declared in the main block extends to the sub block.
  4. PL/SQL variable , expression and SQL function can be included in the cursor SELECT List.
  5. Alias name is required for calculated column when it is referenced in the program.






at No comments:

Explicit Cursor - User Defined Data type - CASE statement

SET SERVEROUTPUT ON
DECLARE
  --Cursor declaration
  CURSOR C_EMP is
      SELECT  INITCAP(ename),
      (CASE 
          WHEN sal + nvl(comm,0) > 4000 THEN 'High Earning'
          WHEN sal + nvl(comm,0) BETWEEN 2000 AND 3999 THEN 'Medium Earning'
          ELSE 'Low Earning'
        END) Earning
        FROM emp;
 
  --TYPE declaration
  TYPE emp_salary_info IS RECORD
  (name emp.ename%TYPE,
  earning VARCHAR2(20));
  --Variable declaration
  v_emp_sal_info emp_salary_info;
BEGIN
  -- open cursor
  OPEN C_EMP;
  LOOP
   -- fetch cursor
    FETCH C_EMP INTO v_emp_sal_info;
      EXIT WHEN C_EMP%NOTFOUND;
      IF C_EMP%ROWCOUNT = 1 THEN
        DBMS_OUTPUT.PUT_LINE( 'ENAME'||'---->'||'EARNING DETAIL' );
      END IF;
      DBMS_OUTPUT.PUT_LINE( v_emp_sal_info.name||'---->'||v_emp_sal_info.earning);
  END LOOP;
  --close cursor
  CLOSE C_EMP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found exception raised');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Others Exception raised');
    IF C_EMP%ISOPEN THEN
      CLOSE C_EMP;
    END IF;
END;

anonymous block completed
ENAME---->EARNING DETAIL
Smith---->Low Earning
Allen---->Low Earning
Ward---->Low Earning
Jones---->Medium Earning
Martin---->Medium Earning
Blake---->Medium Earning
Clark---->Medium Earning
Scott---->Medium Earning
King---->High Earning
Turner---->Low Earning
Adams---->Low Earning
James---->Low Earning
Ford---->Medium Earning
Miller---->Low Earning
at No comments:

Explicit Cursor

The mean of generating an explicit cursor is to name the cursor in the declaring section of the PL/SQL block.

The only advantage of declaring explicit cursor over implicit cursor is that programmer have more control. The process of working with the explicit cursor consist of the following steps

Declaring – This initialize the cursor into memory
Open – Declared cursor is open and memory is allocated
Fetch – Now cursor can retrieve data
Close - Cursor must be closed to release the allocated memory.

SET SERVEROUTPUT ON
DECLARE
  --Cursor declaration
  CURSOR C_EMP is
      SELECT  INITCAP(ename), sal, NVL(comm,0)
      FROM emp;
  --Variable declaration
  v_ename   emp.ename%TYPE;
  v_sal     emp.sal%TYPE;
  v_comm    emp.comm%TYPE;
  v_earning BINARY_INTEGER;
BEGIN
  -- open cursor
  OPEN C_EMP;
  LOOP
   -- fetch cursor
    FETCH C_EMP INTO v_ename,v_sal,v_comm;
      EXIT WHEN C_EMP%NOTFOUND;
      IF C_EMP%ROWCOUNT = 1 THEN
        DBMS_OUTPUT.PUT_LINE( 'ENAME'||'---->'||'EARNING' );
      END IF;
      v_earning := v_sal + v_comm;
      DBMS_OUTPUT.PUT_LINE( v_ename||'---->'||v_earning);
  END LOOP;
  --close cursor
  CLOSE C_EMP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found exception raised');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Others Exception raised');
    IF C_EMP%ISOPEN THEN
      CLOSE C_EMP;
    END IF;
END;
/
anonymous block completed
ENAME---->EARNING
Smith---->800
Allen---->1900
Ward---->1750
Jones---->2975
Martin---->2650
Blake---->2850
Clark---->2450
Scott---->3000
King---->5000
Turner---->1500
Adams---->1100
James---->950
Ford---->3000
Miller---->1300



at No comments:

Implicit Cursor - SQL%ROWCOUNT attribute

An implicit cursor can tell you how many row were affected by an update statement.

SET SERVEROUTPUT ON
BEGIN
  UPDATE emp SET sal = sal * 1.05;
  DBMS_OUTPUT.PUT_LINE('No. of records updated : ' ||SQL%ROWCOUNT);
END;
anonymous block completed
No. of records updated : 14
at No comments:
Subscribe to: Posts (Atom)

External Table

Oracle External Table External tables are defined as tables that do not resides in the database allows you to access data that is stor...

  • Cursor FOR Loop
    Cursor for loop simplifies the PL/SQL program where PL/SQL itself take care most of the things which includes cursor steps and variab...
  • Autonomous Transactions
    When an autonomous transactions is called it leaves context of calling program, perform an independent task without affecting calling prog...
  • Function overloading by name of parameters
    SET SERVEROUTPUT ON DECLARE      FUNCTION getArea(v_radious NUMBER, v_precision NUMBER) RETURN NUMBER IS      l_pi NUMBER := 3.14;      ...