Interview Questions

Shall we create procedures to fetch more than one record?

DATABASE Administrator (DBA) Interview Questions and Answers

(Continued from previous question...)

7. Shall we create procedures to fetch more than one record?

Yes. We can create procedures to fetch more than a row. By using CURSOR commands we could able to do that.
Ex:
CREATE OR REPLACE PROCEDURE myprocedure IS
CURSOR mycur IS select id from mytable;
new_id mytable.id%type;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO new_id;
exit when mycur%NOTFOUND;
–do some manipulations–
END LOOP;
CLOSE mycur;
END myprocedure;
In this example iam trying to fetch id from the table mytable. So it fetches the id from each record until EOF.

(EXIT when mycur%NOTFOUND-is used to check EOF.
For further informations, refer CURSORS.

(Continued on next question...)

Other Interview Questions