Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Wage and Commission Calculation Jump to:  
Category: >> Oracle PL/SQL >> Wage and Commission Calculation Bookmark and Share

<< lastnext >>

Snippet Name: Wage and Commission Calculation

Description: This block calculates the total wages (salary plus commission)
paid to employees in the SERVICE department. It also determines how
many of the employees have salaries higher than $1000, and how
many have commissions larger than their salaries.

Comment: (none)

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

DECLARE
    CURSOR emp_cursor(dnum NUMBER) IS
        SELECT sal, comm FROM emp WHERE deptno = dnum;
    total_wages   NUMBER(11,2) := 0;
    high_paid       NUMBER(4) := 0;
    higher_comm       NUMBER(4) := 0;
BEGIN
    /* The number of iterations will equal the number of rows *
     * returned by emp_cursor.                                */
    FOR emp_record IN emp_cursor(SERVICE) LOOP
        emp_record.comm := NVL(emp_record.comm, 0);
        total_wages := total_wages + emp_record.sal +
            emp_record.comm;
        IF emp_record.sal > 1000.00 THEN
            high_paid := high_paid + 1;
        END IF;
        IF emp_record.comm > emp_record.sal THEN
            higher_comm := higher_comm + 1;
        END IF;
    END LOOP;
    INSERT INTO temp VALUES (high_paid, higher_comm,
        'Total Wages: ' || TO_CHAR(total_wages));
    COMMIT;
END;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 145 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?