Requires Free Membership to View
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 editor@searchDatabase.com 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.
This was first published in February 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation