Q

Cursors: %type and %rowtype explained

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 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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close