Oracle PL/SQL - When Explicit Cursor Queries Need Column Aliases

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursors

Introduction

When an explicit cursor query includes a virtual column, like an expression, that column must have an alias for you to reference.

In the following code, the virtual column in the explicit cursor needs an alias for both of the preceding reasons.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w ww  . j  ava2  s  . co 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-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 80);
SQL>
SQL>
SQL> --Explicit Cursor with Virtual Column that Needs Alias
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT empid,
  4              (salary * .05) raise
  5      FROM emp;
  6    emp_rec c1%ROWTYPE;
  7  BEGIN
  8    OPEN c1;
  9    LOOP
 10      FETCH c1 INTO emp_rec;
 11      EXIT WHEN c1%NOTFOUND;
 12      DBMS_OUTPUT.PUT_LINE ('Raise for employee #' || emp_rec.empid ||' is $' || emp_rec.raise
 13      );
 14    END LOOP;
 15    CLOSE c1;
 16  END;
 17  /
Raise for employee #100 is $1200
Raise for employee #200 is $1250

PL/SQL procedure successfully completed.

SQL>

Related Topic