A complete example of using the cursor variable using a WHILE LOOP : While « PL SQL Statements « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. PL SQL Statements
  3. While
SQL>
SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );

Table created.

SQL>
SQL> insert into product values (1,'Java');

1 row created.

SQL> insert into product values (2,'Oracle');

1 row created.

SQL> insert into product values (3,'C#');

1 row created.

SQL> insert into product values (4,'Javascript');

1 row created.

SQL> insert into product values (5,'Python');

1 row created.

SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );

Table created.

SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.');

1 row created.

SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.');

1 row created.

SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.');

1 row created.

SQL> insert into company values(2,1004,'D Inc.','Long Name D Inc.');

1 row created.

SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.');

1 row created.

SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.');

1 row created.

SQL>
SQL>
SQL>
SQL> DECLARE
  2    CURSOR cursorValue IS
  3      SELECT h.product_description,o.company_short_name
  4      FROM company o,product h
  5      WHERE o.product_id =h.product_id
  6      ORDER by 2;
  7    v_company_rec cursorValue%ROWTYPE;
  8  BEGIN
  9    OPEN cursorValue;
 10
 11    FETCH cursorValue INTO v_company_rec;
 12
 13    WHILE (cursorValue%FOUND) LOOP
 14      dbms_output.put_line(rpad(v_company_rec.product_description,20,' ')||' '||
 15      rpad(v_company_rec.company_short_name,30,' '));
 16      FETCH cursorValue INTO v_company_rec;
 17    END LOOP;
 18    CLOSE cursorValue;
 19  END;
 20  /
Java                 A Inc.
Java                 B Inc.
Java                 C Inc.
Oracle               D Inc.
Oracle               E Inc.
Oracle               F Inc.

PL/SQL procedure successfully completed.

SQL>
SQL> drop table product;

Table dropped.

SQL>
SQL> drop table company;

Table dropped.

SQL>
SQL>








22.7.While
22.7.1.WHILE Loops
22.7.2.While loop
22.7.3.WHILE LOOP with counter
22.7.4.Calculate circle Area in WHILE loop
22.7.5.Example of a WHILE loop that never executes.
22.7.6.The EXIT and EXIT WHEN Statements
22.7.7.EXIT a WHILE LOOP with exit command
22.7.8.While Counter is initialized to NULL
22.7.9.Nested WHILE Loop
22.7.10.Use WHILE Loop to insert data to table
22.7.11.A complete example of using the cursor variable using a WHILE LOOP