Calculating Tax on an Order in SQL

Question

Process the following steps to make a procedure to compute the tax on an order. The BB_TAX table includes the states which need taxes to be submitted for Internet sales. When the state is not listed in the table, then no tax must be accessed on any order. Shopper’s state and basket sub total are the inputs to the procedure whereas the tax amount must be returned.
 
A. Make a procedure called TAX_COST_SP to accomplish the tax computation task. Remember that the state and subtotal values are inputs into procedure and the procedure is to return the tax amount. Review the contents of the BB_TAX table, which contains the tax rate for each state that requires to be taxed.

B. Make a host variable named G_TAX to hold the value returned by procedure.
C. Invoke the procedure employing the values of “VA” for the state and $100 for the subtotal.
D. Exhibit the tax amount returned by the procedure (it must be $4.5).

Answer 1

create or replace PROCEDURE "TAX_COST_SP"
(TSTATE IN VARCHAR2,
SUBTOT IN NUMBER,
G_TAX OUT NUMBER)
is
TRATE NUMBER(4,3);
state_missing EXCEPTION;
begin
    SELECT TAXRATE INTO TRATE FROM BB_TAX WHERE STATE=TSTATE;
 
IF TRATE IS NULL THEN
  RAISE state_missing;
ELSE
  G_TAX := TRATE * SUBTOT;
END IF;
 
EXCEPTION
   WHEN state_missing THEN
      DBMS_OUTPUT.PUT_LINE(TSTATE || ' NOT FOUND');
   WHEN OTHERS THEN
       raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 
end;