Please explain in detail cursors and the method to be used to fetch information with respect to %TYPE and %ROWTYPE variables.
A cursor is simply a returned result set from a query. In PL/SQL, you can step through each row of a that result set and perform some operations on that data. The %TYPE directive will be used to declare a variable of the same type as the column of the table. This is useful when the table's column changes its datatype. You don't have to change your code to match! The %ROWTYPE directive is similar, except that it declares a record that corresponds to one row of the table. Hopefully, a simple example will help.
DECLARE -- Declare two variables as the first and -- last name of the students. This can be -- found in the STUDENTS table. first students.first%TYPE; last students.last%TYPE; -- Declare a record variable which will -- correspond to the columns of one row of the -- STUDENTS table. student students%ROWTYPE; -- Declare a cursor to fetch first & last CURSOR c1 IS select first,last from students; -- Declare a cursor to fetch all columns CURSOR c2 IS select * from students; BEGIN -- open c1 cursor OPEN c1; -- loop through the result set and display output LOOP EXIT WHEN c1%NOTFOUND; FETCH c1 INTO first,last; DBMS_OUTPUT.PUT_LINE('First: ' || first); DBMS_OUTPUT.PUT_LINE('Last: ' || last); END LOOP; -- close c1 cursor CLOSE c1; -- open c2 cursor OPEN c2; -- loop through the result set and display output LOOP EXIT WHEN c2%NOTFOUND; FETCH c2 INTO student; DBMS_OUTPUT.PUT_LINE('First: ' || student.first); DBMS_OUTPUT.PUT_LINE('Last: ' || student.last); END LOOP; -- close c2 cursor CLOSE c2; END; /
For More Information
- What do you think about this answer? E-mail the editors at [email protected] with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.