PL/SQL PROGRAM USING CURSOR | CURSOR IN SQL | WITH CURSOR WITHOUT CURSOR IN SQL

AIM :
To write and execute program using cursor.

CURSOR:
Cursor can be used to retrieve set of rows returned by a query.

SYNTAX:
Declaration
Cursor<cursor name> is <select statement>

ALGORITHM:
Algorithm for program without cursor:
Declare the variable customer name and Customer Street of customer table as -name and street with the data type of table.
Select name and street from customer table where customer city is given.
Display the name and street.

If too many rows occur then display using cursor.
If no data found occur, then display no values.
Stop the program.

Algorithm for program with cursor:
Declare the variable customer name and Customer Street of customer table as -name and street with the data type of table.
Declare the cursor c.
Select name and street from customer table where customer city is given.
Open the cursor pointer c.
Fetch data into cursor c.
Display the name and street.
Close the cursor pointer c.
Stop the program.

PROGRAM:

Without cursor:

SQL> set serveroutput on
SQL> declare
2 name customer.custmername %type;
3 street customer.customerstreet %type;
4 begin
5 select custmername,customerstreet into name,street from customer where customercity='&c';
6 dbms_output.put_line('customer name ='name);
7 dbms_output.put_line('customer street ='street);
8 exception
9 when too_many_rows then
10 dbms_output.put_line('using cursor');
11 when no_data_found then
12 dbms_output.put_line('no values');
13 end;
14 /



With cursor:

SQL> set serveroutput on
SQL> declare
2 name customer.custmername %type;
3 street customer.customerstreet %type;
4 cursor c is select custmername,customerstreet from customer where customercity='&c';
5 begin
6 open c;
7 loop
8 fetch c into name,street;
9 exit when c % notfound;
10 dbms_output.put_line('customer name ='name);
11 dbms_output.put_line('customer street ='street);
12 end loop;
13 close c;
14 end;
15 /