Oracle PL/SQL: Alias and RowType - This block finds all employees whose monthly wages (salary plus commission) are higher than $2000.<b

Quick Search:

Oracle PL/SQL: Alias and RowType

Jump to:

Category: >> Oracle PL/SQL >> Alias and RowType

<< last

next >>

Snippet Name: Alias and RowType

Description: This block finds all employees whose monthly wages (salary plus commission) are higher than $2000.

An alias is used in the cursor declaration so that the subsequent use of %ROWTYPE is allowed. (Column names in a cursor declaration must have aliases if they are not simple names.)

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 27th, 2009

DECLARE
    CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename 
        FROM emp;
    my_rec  my_cursor%ROWTYPE;
BEGIN
    OPEN my_cursor;
    LOOP
        FETCH my_cursor INTO my_rec;
        EXIT WHEN my_cursor%NOTFOUND;
        IF my_rec.wages > 2000 THEN
            INSERT INTO temp VALUES (NULL, my_rec.wages,
                my_rec.ename);
        END IF;
    END LOOP;
    CLOSE my_cursor;
END;
/