Question

Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX.

Answer 1

Answer:

The procedure in SQL is created as follows

Explanation:

--creating the procedure

CREATE OR REPLACE PROCEDURE prc_inv_amounts (W_IN IN NUMBER)

AS

--defining variables

W_CK NUMBER := 0;

W_SUBT NUMBER := 0;

W_TAX NUMBER := 0;

BEGIN

--Authentication process of the invoice

SELECT COUNT(*) INTO W_CK FROM INVOICE WHERE INV_NUMBER := W_IN;

--Transaction confirmation

IF W_CK = 1 THEN

SELECT SUM(LINE_TOTAL) INTO W_SUBT FROM LINE

WHERE

--checking the invoice for the desired invoice number

LINE.INV_NUMBER = W_IN;

W_TAX :=W_SUBT * 0.08;

--updating the invoice

UPDATE INVOICE

--setting the new values

SET INV_SUBTOTAL = W_SUBT,

INV_TAX = W_TAX,

INV_TOTAL =W_SUBT + W_TAX

WHERE INV_NUMBER = W_IN;

--ending the if statement

END IF;

--ending the procedure

END;