Question

1. Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the customer, and the invoice date for the invoice whose number currently is stored in I_INVOICE_NUM. Place these values in the variables I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE respectively. When the procedure is called it should output the contents of I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE.

2. Create a procedure to add a row to the INVOICES table.
3. Create the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in I_INVOICE_NUM to the date currently found in I_INVOICE_DATE
INVOICE NUM INVOICE DATE CUST ID 14216 2021-11-15 125 14219 2021-11-15 227 14222 2021-11-16 294 14224 2021-11-16 182 14228 2021-11-18 435 14231 2021-11-18 125 14233 2021-11-18 435 14237 2021-11-19 616

Answer 1

1. ```sql

GET_INVOICE_DATE(I_INVOICE_NUM, I_CUST_ID, I_CUST_NAME, I_INVOICE_DATE);

```

2.```sql

ADD_INVOICE(I_INVOICE_NUM, I_CUST_ID, I_INVOICE_DATE);

```

3. ```sql

UPDATE_INVOICE(I_INVOICE_NUM, I_INVOICE_DATE);

```

Step-by-step explanation:

In the first step, the `GET_INVOICE_DATE` procedure is invoked to retrieve information about a specific invoice. This procedure takes the invoice number (`I_INVOICE_NUM`) as input and returns the corresponding customer ID (`I_CUST_ID`), customer name (`I_CUST_NAME`), and invoice date (`I_INVOICE_DATE`). The purpose is to encapsulate the logic for fetching these details, providing a modular and organized approach to retrieving specific invoice information.

Moving to the second step, the `ADD_INVOICE` procedure is designed to insert a new row into the INVOICES table. This procedure accepts the invoice number (`I_INVOICE_NUM`), customer ID (`I_CUST_ID`), and invoice date (`I_INVOICE_DATE`) as parameters, streamlining the process of adding new invoice records to the database. In the final step, the `UPDATE_INVOICE` procedure is utilized to modify the date of a specific invoice. The procedure takes the invoice number (`I_INVOICE_NUM`) and the new date (`I_INVOICE_DATE`) as inputs, allowing for the efficient update of invoice information without the need for complex and repetitive SQL statements.

These procedures enhance code reusability, maintainability, and readability by encapsulating specific functionalities related to invoices. They contribute to a more modular and organized database management system, facilitating easier maintenance and updates in the long run.

Answer 2

Create the GET_INVOICE_DATE Procedure

**Here's an example of how the procedure should be implemented in PL/SQL:

CREATE OR REPLACE PROCEDURE GET_INVOICE_DATE AS I_CUST_ID INVOICES.CUST_ID%TYPE; I_CUST_NAME INVOICES.CUST_NAME%TYPE; I_INVOICE_DATE INVOICES.INVOICE_DATE%TYPE; BEGIN SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, INVOICE_DATE INTO I_CUST_ID, I_CUST_NAME, I_INVOICE_DATE FROM INVOICES WHERE INVOICE_NUM = I_INVOICE_NUM; DBMS_OUTPUT.PUT_LINE('Customer ID: ' || I_CUST_ID); DBMS_OUTPUT.PUT_LINE('Customer Name: ' || I_CUST_NAME); DBMS_OUTPUT.PUT_LINE('Invoice Date: ' || I_INVOICE_DATE); END; /

**Create a Procedure to Add a Row to the INVOICES Table

Here's an example of how the procedure should be implemented:

CREATE OR REPLACE PROCEDURE ADD_INVOICE( I_INVOICE_NUM INVOICES.INVOICE_NUM%TYPE, I_INVOICE_DATE INVOICES.INVOICE_DATE%TYPE, I_CUST_ID INVOICES.CUST_ID%TYPE ) AS BEGIN INSERT INTO INVOICES(INVOICE_NUM, INVOICE_DATE, CUST_ID) VALUES(I_INVOICE_NUM, I_INVOICE_DATE, I_CUST_ID); COMMIT; DBMS_OUTPUT.PUT_LINE('Row added to INVOICES table.'); END; /

**Create the UPDATE_INVOICE Procedure

Here's an example of how the procedure should be implemented:

CREATE OR REPLACE PROCEDURE UPDATE_INVOICE AS BEGIN UPDATE INVOICES SET INVOICE_DATE = I_INVOICE_DATE WHERE INVOICE_NUM = I_INVOICE_NUM; COMMIT; DBMS_OUTPUT.PUT_LINE('Date of invoice ' || I_INVOICE_NUM || ' updated to ' || I_INVOICE_DATE); END; /

Step-by-step explanation:

The GET_INVOICE_DATE procedure retrieves the customer ID, first and last names of the customer, and the invoice date for a specific invoice number. Here's a clear and concise answer to the question:

1. The `GET_INVOICE_DATE` procedure is designed to retrieve specific information related to an invoice. It takes an invoice number as input and retrieves the customer ID, first and last names of the customer, and the invoice date associated with that invoice number. This procedure allows you to easily access and display these details whenever needed.

2. The procedure to add a row to the `INVOICES` table allows you to insert new data into the table. It takes the necessary values as input parameters, such as the invoice number, invoice date, and customer ID. By providing these values, you can easily add a new row to the `INVOICES` table, ensuring that the necessary information is stored accurately and conveniently.

3. The `UPDATE_INVOICE` procedure is specifically designed to modify the date of a particular invoice. It takes the invoice number and the desired date as input parameters. By specifying the invoice number and the new date, you can easily update the invoice date in the `INVOICES` table, ensuring that the information is up-to-date and reflects any changes or corrections made.

These procedures are helpful in managing and manipulating invoice data within the database. The `GET_INVOICE_DATE` procedure allows you to retrieve specific information, the procedure to add a row helps in inserting new data, and the `UPDATE_INVOICE` procedure allows for modifying existing invoice dates. By utilizing these procedures, you can efficiently handle and maintain invoice-related information in your database system.