-- Listing 7-7. PL/SQL Code to Handle a Solo Commission
declare
    v_amount int := :P18_SALE_AMOUNT;
    v_saleCommission number(7,2);
    v_prevCommission number(7,2);
    v_newCommission  number(7,2);
begin
    -- Step 1: Calculate the new commission as 15% of the first $100 
    -- and then 10% of the remaining sale price.

    if v_amount > 100 then
            v_amount := v_amount - 100;
            v_saleCommission := 15 + (v_amount * 0.1); 
    else
            v_saleCommission := v_amount * 0.15; 
    end if;

    -- Step 2: Retrieve the previous commission from EMP 
    -- and calculate the new commission.

    select Comm
    into v_prevCommission
    from EMP
    where EmpNo = :P18_EMPNO;

    v_newCommission := v_prevCommission + v_saleCommission;

    -- Step 3: Update the employee's record.

    update EMP
    set Comm = v_newCommission
    where EmpNo = :P18_EMPNO;

    -- Step 4: Compute the item values.

    :P18_SALE_COMMISSION := v_saleCommission;
    :P18_PREV_COMMISSION := v_prevCommission;
    :P18_NEW_COMMISSION  := v_newCommission;
end;

