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

Below is a stored procedure named prc_inv_amounts that updates the INV_SUBTOTAL, INV_TAX, and INV_TOTAL based on the provided invoice number:

The Procedure

CREATE PROCEDURE prc_inv_amounts invoice_number INT

AS

BEGIN

UPDATE Invoices

SET

INV_SUBTOTAL = (

SELECT SUM(LINE_TOTAL)

FROM Invoice_Lines

WHERE Invoice_Lines.INVOICE_NUM = invoice_number

),

INV_TAX = (

SELECT SUM(LINE_TOTAL) * 0.08

FROM Invoice_Lines

WHERE Invoice_Lines.INVOICE_NUM = invoice_number

),

INV_TOTAL = (

SELECT SUM(LINE_TOTAL) + SUM(LINE_TOTAL) * 0.08

FROM Invoice_Lines

WHERE Invoice_Lines.INVOICE_NUM = invoice_number

)

WHERE INVOICE_NUM = invoice_number

END;

This stored procedure calculates and updates the INV_SUBTOTAL, INV_TAX (at 8% rate), and INV_TOTAL for a specified invoice number by summing the LINE_TOTAL amounts from the Invoice_Lines table.

Answer 2

The procedure in SQL is created as follows

Step-by-step 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;