OraclePLSQL--uses of Cursors

PL/SQL Cursors

declare

v_COUNTRY_NAME varchar2(80);

cursor get_data is

    

select COUNTRY_NAME

from COUNTRIES;

begin

open get_data;

fetch get_data into v_COUNTRY_NAME ;

dbms_output.put_line(v_COUNTRY_NAME );

close get_data;

end;

anonymous block completed

United States of America

declare

--- v_prod_name varchar2(80);  -----NO NEED

cursor cur_get_data is

select COUNTRY_NAME

from COUNTRIES

WHERE COUNTRY_NAME LIKE 'S%';

begin

for i in cur_get_data

LOOP

dbms_output.put_line(i.COUNTRY_NAME);

END LOOP;

end;

anonymous block completed

Spain

South Africa

Saudi Arabia

Singapore

declare

cursor get_data is

select PROD_ID,UNIT_COST,UNIT_PRICE

from COSTS

WHERE UNIT_COST = 48.99 AND UNIT_PRICE = 62.91;

Begin

for i in get_data

LOOP

dbms_output.put_line( 'PROD_ID:' || i.PROD_ID ||' UNIT_COST: '|| i.UNIT_COST        

                      ||' UNIT_PRICE: '|| i.UNIT_PRICE );

END LOOP;

end;

anonymous block completed

PROD_ID:37 UNIT_COST: 48.99 UNIT_PRICE: 62.91

PROD_ID:37 UNIT_COST: 48.99 UNIT_PRICE: 62.91

PL/SQL CursorsAND If

Declare

cursor get_data is

select PROD_ID,UNIT_COST,UNIT_PRICE

from COSTS ;

Begin

for i in get_data

LOOP

if i.UNIT_COST > 48.99 then

dbms_output.put_line( 'PROD_ID:' || i.PROD_ID ||' UNIT_COST: '|| i.UNIT_COST ||' UNIT_PRICE: '|| i.UNIT_PRICE );

else

dbms_output.put_line( 'PROD_ID:' || i.PROD_ID ||' not_order: '|| i.UNIT_COST ||' not_order: '|| i.UNIT_PRICE );

end if;

END LOOP;

end;

anonymous block completed

PROD_ID:148 not_order: 18.64 not_order: 28.76

PROD_ID:148 not_order: 18.64 not_order: 29.39

PROD_ID:14 UNIT_COST: 904.18 UNIT_PRICE: 1159.99

PROD_ID:17 UNIT_COST: 988.79 UNIT_PRICE: 1516.93

declare

v_countries_count number;

begin

select count(*) into v_countries_count from COUNTRIES;

FOR i IN 1 ..v_countries_count

LOOP

dbms_output.put_line('COUNTRIES ' || i);

END LOOP;

end;

anonymous block completed

COUNTRIES 1

COUNTRIES 2

COUNTRIES 3

COUNTRIES 4

COUNTRIES 5

COUNTRIES 6

COUNTRIES 7

COUNTRIES 8

COUNTRIES 9

COUNTRIES 10

COUNTRIES 11

COUNTRIES 12

COUNTRIES 13

COUNTRIES 14

COUNTRIES 15

COUNTRIES 16

COUNTRIES 17

COUNTRIES 18

COUNTRIES 19

COUNTRIES 20

COUNTRIES 21

COUNTRIES 22

COUNTRIES 23