Parameterized Cursor(Passing parameter to the cursor)

             Cursors can use variables to adjust which rows they select when opened. Instead of hard-coding a value into the WHERE clause of a query, you can use a variable as a placeholder for a literal value. The variable placeholder will substituted with the value of the variable when the cursor is opened. This makes a query more flexible.

DECLARE
  v_deptno NUMBER;
  v_job VARCHAR2(15);
  v_sum_sal NUMBER;

/* Since v_deptno and v_job are declared above, they are in scope,
 * and can be referenced in the cursor body.  They will be used as
 * placeholders until the cursor is opened, at which
 * point the values of the variables will be substituted (bound)
 * into the query as literals.
 */
  CURSOR emp_stats_cursor IS
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=v_deptno
        AND job=v_job;     
BEGIN
  v_deptno:=10;    
  v_job:='MANAGER';

  OPEN emp_stats_cursor;
  /* When the cursor is opened, the values of the PL/SQL
   * variables are bound into the query. 
   * In this example, the cursor would return the
   * result set using the following query:
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=10       --current value of v_deptno is 10
        AND job='MANAGER';  --current value of v_job is 'MANAGER'     
    */

  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 
  DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal);

  v_deptno:=30;    
  v_job:='SALESMAN';

  OPEN emp_stats_cursor;
   /* In this example, the cursor would
    * return the result set using the following query:

    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=30       --current value of v_deptno is 30
        AND job='SALESMAN';  --current value of v_job is 'SALESMAN'     
    */  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 
  DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal);
END;
/

This method works, but there is a better way.  You can declare a cursor using parameters; then whenever you open the cursor, you pass in appropriate parameters.  This technique is just as flexible, but is easier to maintain and debug. The above example adapted to use a parameterized cursor:

DECLARE

  v_sum_sal NUMBER;

/* The parameters are declared in the cursor declaration. 
 * Parameters have a datatype, but NO SIZE; that is, you
 * can declare a parameter of datatype VARCHAR2, but never
 * VARCHAR2(20).
 * As above, the parameters will be placeholders (that is,
 * formal parameters) until the cursor is opened, at which
 * point the actual parameter values will be substituted
 * (bound) into the query as literals.
 * The parameters are in scope (that is, they can be referenced)
 * only inside the cursor body.
 */

  CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=cp_deptno
        AND job=cp_job;     

BEGIN

  OPEN emp_stats_cursor(10,'MANAGER');
  /* When the cursor is opened, the values of the parameters
   * are bound into the query. In other words, the actual parameters
   * (values) will replace the formal parameters (placeholders).
   * In this example, the cursor would return the result set using
   * the following query:

    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=10      
        AND job='MANAGER';       
    */

  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 

  DBMS_OUTPUT.PUT_LINE('10 : MANAGER : '||v_sum_sal);

  OPEN emp_stats_cursor(30,'SALESMAN');
   /* In this example, the cursor would return the result set
    * using the following query:

    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=30      
        AND job='SALESMAN'; 
    */

  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 
  DBMS_OUTPUT.PUT_LINE('30 : SALESMAN : '||v_sum_sal);
END;
/

0         Parameterized cursors are open easier to debug in larger PL/SQL blocks.  This is because the the declaration of the cursor body  is often far from where the cursor is opened, but processing of the cursor's result set is usually close to where the cursor is opened. 

• When opening a cursor which uses variables, you must assign appropriate values to those variables before opening the cursor.  So when you are debugging how the cursor is opened, you must confirm the appropriate variable names where the cursor is declared .  This is often inconvenient. 
• When using PL/SQL variables, it's difficult to confirm the values of the variables when the cursor is opened because the values could be set at an any point from the declaration on.  
• Parameterized cursors eliminate both these problems because the values used in the cursor can be determined in one place, the OPEN statement.  And you don't need to know the names of the cursor parameters.  (Though you do need to know the order of the parameters.)

·An example which combines a cursor FOR loop with a parameterized query:

DECLARE
  v_sum_sal NUMBER;
  CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=cp_deptno
        AND job=cp_job;  

BEGIN
  FOR dept_job_rec IN (SELECT DISTINCT deptno,job FROM emp) LOOP
    OPEN emp_stats_cursor(dept_job_rec.deptno, dept_job_rec.job);
    FETCH emp_stats_cursor INTO v_sum_sal;
    CLOSE emp_stats_cursor; 
    DBMS_OUTPUT.PUT_LINE(dept_job_rec.deptno ||' : '||dept_job_rec.job||' : '||v_sum_sal);
  END LOOP;
END;
/
 

Cursor FOR Loops

The cursor FOR loop provides an elegant, simple syntax to to iterate over a result set. To underscore the advantages of cursor FOR loops, consider the following PL/SQL block which uses a basic loop.

 SET SERVEROUTPUT ON
DECLARE
  -- EMP_CURSOR will retrieve all columns and all rows from the EMP table
  CURSOR emp_cursor IS
    SELECT *
      FROM emp;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    --Advance the pointer in the result set, assign row values to EMP_RECORD
    FETCH emp_cursor INTO emp_record;
    --Test to see if no more results  
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_record.ename||' [' ||emp_record.empno||']');
  END LOOP;
  CLOSE emp_cursor;
END;
/

Now examine the same query using a cursor FOR loop:

DECLARE
  -- EMP_CURSOR will retrieve all columns and all rows from the EMP table
  CURSOR emp_cursor IS
    SELECT *
      FROM emp;
BEGIN
  FOR emp_record IN emp_cursor LOOP     
    DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']');
  END LOOP;
END;
/

You can use a cursor for loop without a declared cursor by including a query in the FOR statement.  This can enable very compact code.

BEGIN
  FOR emp_record IN (SELECT * FROM emp) LOOP     
    DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']');
  END LOOP;
END;
/

While you can use EXIT statement within a FOR cursor loop, you should not use a cursor FOR loop if you may need to exit the LOOP prematurely.  Use a basic or WHILE loop instead.

PLSQL Nested Tables

Index-By-Table

An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string.
Syntax :
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
Example :
Following example how to create a table to store integer values along with names and later it prints the same list of names.

DECLARE
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
   salary_list salary;
   name   VARCHAR2(20);
BEGIN
   -- adding elements to the table
   salary_list('Rajnish')  := 62000;
   salary_list('Minakshi')  := 75000;
   salary_list('Martin') := 100000;
   salary_list('James') := 78000;

   -- printing the table
   name := salary_list.FIRST;
   WHILE name IS NOT null LOOP
      dbms_output.put_line
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
      name := salary_list.NEXT(name);
   END LOOP;
END;
/

When the above code is executed at SQL prompt, it produces following result:

Salary of Rajnish is 62000
Salary of Minakshi is 75000
Salary of Martin is 100000
Salary of James is 78000
PL/SQL procedure successfully completed.

PLSQL Table:

PL/SQL tables are PL/SQL’s way of providing arrays. Arrays are like temporary 

tables in memory and thus are processed very quickly. It is important for you to

realize that they are not database tables, and DML statements cannot be issued 

against them. This type of table is indexed by a binary integer counter (it cannot 

be indexed by another type of number) whose value can be referenced using the 

number of the index.

Remember that PL/SQL tables exist in memory only, and therefore don’t exist in

any persistent way, disappearing after the session ends. 

Example showing First and Next method of PL/SQL tables

 Declare
 Type my_student_table is table of varchar2(20)
 Index by binary_integer;
 Var_of_student my_student_table;
 Index_value number;
 Begin
 For student_rec in (select * from student) 
 loop
  Var_of_student(student_rec.rollno) := student_rec.student_name;
 End loop;

 Index_value := var_of_student.first;
 Loop
 Exit when index_value is null;
  Dbms_output.put_line (index_value || ' ' ||var_of_student(index_value));
  Index_value := var_of_student.next(index_value);
 End loop;
End;
/ 

second example of PL/SQL Table


--last_name_type TYPE is declared based on the column LAST_NAME of the STUDENT table.
--last_name_tab is the actual index-by table declared as LAST_NAME_TYPE.
DECLARE
CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <=10;
TYPE last_name_type IS TABLE OF student.last_name%TYPE
INDEX BY BINARY_INTEGER;
last_name_tab last_name_type;
v_counter INTEGER := 0;
BEGIN
For name_rec IN name_cur LOOP
v_counter := v_counter + 1;
last_name_tab(v_counter) := name_rec.last_name;
DBMS_OUTPUT.PUT_LINE('last_name('||v_counter||'): '||

last_name_tab(v_counter));
END LOOP;
END;
OUTPUT will be like:
last_name(1): Roy
last_name(2): Brit 

 

PL/SQL Collection Concepts

Collections :

                    A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

RECORD 

A record is a group of related data items stored in fields, each with its own name and datatype. Suppose you have various data about an employee such as name, salary, and hire date. These items are logically related but dissimilar in type. A record containing a field for each item lets you treat the data as a logical unit. Thus, records make it easier to organize and represent information.

                   The attribute %ROWTYPE lets you declare a record that represents a row in a database table. However, you cannot specify the datatypes of fields in the record or declare fields of your own. The datatype RECORD lifts those restrictions and lets you define your own records

In the example below, you fetch rows from database table flights into record flight_info. That way, you can treat all the information about a flight, including its passenger list, as a logical unit.

DECLARE

TYPE FlightRec IS RECORD (

flight_no NUMBER(3), gate CHAR(5), departure CHAR(15), arrival CHAR(15),

passengers PassengerList);

flight_info FlightRec;

CURSOR c1 IS SELECT * FROM flights;

seat_not_available EXCEPTION;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO flight_info;

EXIT WHEN c1%NOTFOUND;

FOR i IN 1...FLIGHT_info.passengers.LAST LOOP

IF flight_INFO.PASSENGERS (i).seat = ’NA’ THEN

dbms_output.put_LINE (flight_INFO.PASSENGERS (i).name);

RAISE seat_not_available;

END IF;

...

END LOOP;

END LOOP;

CLOSE c1;

EXCEPTION

WHEN seat_not_available THEN

...

END;