while cursorName%found, loop : LOOP « Cursor « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Cursor
  3. LOOP
SQL>
SQL>
SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id            integer
  4          ,register_date          date not null
  5          ,total_price            number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment                varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message                varchar2(100)
 12  );

Table created.

SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,sysdate, 123.12, sysdate+1, '12 noon', 'CA',1, null, 'Happy Birthday to you');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,sysdate, 50.98, sysdate+2, '1 pm', 'CA',7, 'name1', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,sysdate, 35.99, sysdate+3, '1 pm', 'VS',2, 'Tom', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,sysdate, 19.95, sysdate+4, '5 pm', 'CA',2, 'Mary', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,sysdate, 10.95, sysdate+5, '4:30 pm', 'VS', 2, 'Jack', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (6, 9,sysdate-3, 22.95, sysdate+6, '1 pm', 'VS', 2, 'Mike', 'Happy Birthday' );

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (7, 9,sysdate-4, 35.95, sysdate+7, '12 noon', 'VS', 2, 'Jason', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (8, 12,sysdate-6, 35.95, sysdate+8, '12 noon', 'DI',3, 'Lite', 'Happy New Year');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (9, 12,sysdate-9, 75.95, sysdate+9, '12 noon', 'CA',7, 'Sara', 'Happy New Year' );

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (10, 4, sysdate, 19.95, sysdate, '2:30 pm', 'VG',2, 'Mary', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (11, 2, sysdate, 30.00, sysdate+2, '1:30 pm', 'VG',2, 'Mary', 'Happy New Year');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (12, 7, sysdate-3, 21.95, sysdate-2, '3:30 pm', 'CA',2, 'Mary', 'Happy New Year');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (13, 7, sysdate, 21.95, sysdate, '3:30 pm', 'CA',2, 'Mary', 'Happy Birthday');

1 row created.

SQL>
SQL> create table sale(
  2           gift_id                 integer
  3          ,product_id              integer
  4          ,quantity                number(4,0)
  5          ,price              number(7,2)
  6          ,primary key (gift_id ,product_id)
  7  );

Table created.

SQL>
SQL> insert into sale(gift_id, product_id, quantity, price) values(1, 2, 10, 23.00 );

1 row created.

SQL> insert into sale(gift_id, product_id, quantity, price) values(2, 1, 1, 23.11 );

1 row created.

SQL> insert into sale(gift_id, product_id, quantity, price) values(11, 8, 1, 30.00);

1 row created.



SQL>
SQL>
SQL> --set termout on
SQL> DECLARE
  2    giftIDValue           sale.gift_id%type;
  3    totalValue            sale.price%type;
  4  cursor c1 is
  5    select gift_id, sum(price * quantity) from sale group by gift_id;
  6  begin
  7      open c1;
  8      fetch c1 into giftIDValue, totalValue;
  9      while c1%found loop
 10          update gift
 11          set total_price = totalValue
 12          where gift_id = giftIDValue;
 13          fetch c1 into giftIDValue, totalValue;
 14      end loop;
 15      close c1;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table gift;

Table dropped.

SQL> drop table sale;

Table dropped.

SQL>








25.5.LOOP
25.5.1.A loop is required is to read each row in cursor returned
25.5.2.Use For loop to output data in a PL/SQL table of cursor
25.5.3.Placing cursors in nested loops
25.5.4.Exit a LOOP when cursor%NOTFOUND
25.5.5.LOOP..END LOOP Cursor Loop
25.5.6.WHILE..LOOP Cursor Loop
25.5.7.Cursor FOR Loop
25.5.8.Loop till exit when cursorName%notfound
25.5.9.for data in ( select * from tableName )
25.5.10.while cursorName%found, loop