Oracle PL/SQL - Cursor FOR Loop References Virtual Columns

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursor FOR LOOP Statement

Introduction

In the following code, the implicit cursor FOR LOOP references virtual columns by their aliases, full_name and dream_salary.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  ww . j  av a 2s .  c o m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-2000', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-2010', 'dd-MON-yyyy'), 'CODER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> BEGIN
  2    FOR item IN (
  3      SELECT first_name || ' ' || last_name AS full_name,
  4             salary * 10  AS dream_salary
  5      FROM emp
  6      WHERE ROWNUM <= 5
  7      ORDER BY dream_salary DESC, last_name ASC
  8    ) LOOP
  9      DBMS_OUTPUT.PUT_LINE
 10        (item.full_name || ' dreams of making ' || item.dream_salary);
 11    END LOOP;
 12  END;
 13  /
Joe Lee dreams of making 250000
Steven King dreams of making 240000

PL/SQL procedure successfully completed.

SQL>

Related Topic