Raj,
This may help... It is from a PL/SQL tutorial that I modified to remove the parts that you don't need yet and would likely confuse the situation for you. Remember %TYPE and %ROWTYPE are used just to avoid data type mismatches ... you can declare your variable to be VARCHAR2(15) or NUMBER(12,2), but if the column data type changes, length changes, precision, scale changes you will have to modify your program to avoid a data type mismatch. Using %TYPE and %ROWTYPE insulates you from those type of changes.
----------------
A cursor is a variable that runs through the rows of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each row of the relation, we can write a program to read and process the value of each such row.
1) DECLARE
/* Output variables to hold the result of the query: */
2) a myTable.e%TYPE; -- a assigned the same data type as e column in myTable.
3) b myTable.f%TYPE; b assigned the same data type as f column in myTable
/* Cursor declaration: */
4) CURSOR myTableCursor IS
5) SELECT e, f
6) FROM myTable
7) WHERE e < f ;
8) BEGIN
9) OPEN myTableCursor; -- This is an explicit cursor as opposed to the implicit cursor in your example.
10) LOOP
/* Retrieve each row of the result of the above query
into PL/SQL variables: */
11) FETCH myTableCursor INTO a, b;
/* If there are no more rows to fetch, exit the loop: */
12) EXIT WHEN myTableCursor%NOTFOUND;
13) END LOOP;
/* Free cursor used by the query. */
14) CLOSE myTableCursor;
15) END;
Here are explanations for the various lines of this program:
* Line (1) introduces the declaration section.
* Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation myTable. Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER).
* Lines (4) through (7) define the cursor myTableCursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those rows of myTable whose first component is less than the second component.
* Line (8) begins the executable section of the program.
* Line (9) opens the cursor, an essential step.
* Lines (10) through (13) are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find:
o On Line (11), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the row retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.
o On Line (12), a
test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more rows.
* Line (14) closes the cursor.
* Line (15) ends the PL/SQL program.
