sql crap

gistfile1.txt

/*

The procedure should take IN a second parameter representing the amount of the commission to be applied.
This amount should be a dollar amount, but in the display should be shown as a percentage of the sales_amount_in ???
Condition of negative or zero sales should be handled as a custom exception
If the rate is not supplied, assume a 10% commission
*/
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE calc_commission 
    (sales_amount_in IN number, amountApplicableTocommission IN number DEFAULT 0) 
IS
    excep_has_no_sales EXCEPTION;
    PRAGMA EXCEPTION_INIT (excep_has_no_sales,-2292);
    v_comission number(10,2) := 0;
    percentage_of_sales_commission number := 0;
    commission number(10,2) := 0;
BEGIN
-- Calc Commission

IF amountApplicableTocommission = 0
    THEN percentage_of_sales_commission := 0.1;
ELSE
    percentage_of_sales_commission:= amountApplicableTocommission/sales_amount_in;
END IF;


--Throw exception if negative sales
    v_comission := sales_amount_in * percentage_of_sales_commission;
    commission := v_comission;
    IF commission <= 0 
        THEN
            RAISE excep_has_no_sales;
    END IF;
    -- reformat as percentage
    percentage_of_sales_commission := NVL(ROUND((commission)/10,2),0);
      
dbms_output.put_line('SalesTotal: $' || sales_amount_in||' Commission:$' || commission || ' '||     percentage_of_sales_commission|| '%' );
EXCEPTION
    WHEN excep_has_no_sales
    THEN dbms_output.put_line('Commission is only added on sales');

END;


EXEC CALC_COMMISSION(100,10);
EXEC CALC_COMMISSION(1000,880);


-- No way to test this....
CREATE OR REPLACE PROCEDURE verify_authors
IS
-- Oracle Shutdown Exception
    oracle_shutdown EXCEPTION;
-- Oracle Value Error Exception    
    VALUE_ERROR EXCEPTION; 
-- Defines the exception as the actual oracle exception ORA-06502    
    PRAGMA EXCEPTION_INIT (VALUE_ERROR,06502);
-- Defines the exception as ORA-1014    
    PRAGMA EXCEPTION_INIT (oracle_shutdown,-1014);

-- Define a cursor for authors    
    CURSOR cur_author IS SELECT AUTHOR1,AUTHOR2,AUTHOR3,"Book Name" FROM AUTHORS;
-- Does the author exist in this iteration of the loop
    author_exists boolean := false;
-- Count the number of authors in this iteration of the loop with the same name    
    count_authors NUMBER := 0;
-- Author already exists exception    
    author_already_exists EXCEPTION;
BEGIN
    -- Start going thru the cursor
    FOR current_author IN cur_author
    LOOP
        -- these get set back to default for each iteration
        count_authors := 0;
        author_exists := false;

        -- O(^2) youch.
        -- Look thru the entire table for the author
        SELECT
            COUNT(AUTHOR1) 
        INTO
            count_authors 
         FROM
            AUTHORS
        WHERE
            current_author.AUTHOR1 = AUTHOR1 
            OR current_author.AUTHOR1 = AUTHOR2              
            OR current_author.AUTHOR1 = AUTHOR3;
--- in any of the columns we should only have a total of 1 author with that name.        
        IF count_authors != 1
            THEN
            author_exists := true;
        END IF;
    END LOOP;
--- if the author exists - throw the exception
    IF author_exists = true
        THEN
             RAISE author_already_exists;
    END IF;
    --- horrible - but bad schema...
    INSERT INTO AUTHORS (AUTHOR1) VALUES(author_name);

EXCEPTION
    WHEN author_already_exists
    THEN
        INSERT INTO LOGS (DESCRIPTION) VALUES ('CS 276 PL/SQL programming has duplicate authors.');
    WHEN oracle_shutdown
    THEN
        RAISE VALUE_ERROR;
END;

Your manager has been so impressed with the improvements you made to the code on Monday that he wants additional changes
The procedure should take IN a second parameter representing the amount of the commission to be applied.

This amount should be a dollar amount, but in the display should be shown as a percentage of the sales_amount_in
Condition of negative or zero sales should be handled as a custom exception
If the rate is not supplied, assume a 10% commission