Question

FIGURE P8.16 CH08_SIMPLECO DATABASE TABLES Database name: Ch08_SimpleCo Table name: INVOICE Table name: CUSTOMER CUST NUM CUST LNAME CUST FNAME CUST BALANCE 1000 Smith Jeanne 1050.11 1001 Ortega Juan 840.92 INV_NUM CUST_NUM INV_DATE INV_AMOUNT 8000 1000 23-Mar-16 235.89 8001 1001 23-Mar-16 312.82 8002 1001 30-Mar-16 528.10 8003 1000 12-Apr-16 194.78 8004 1000 23-Apr-16 619.44

Write a procedure named prc_cust_add to add a new customer to the CUST_MYSQL table. Use the following values in the new record:

CUIST_NUM	CUST_LNAME	CUST_FNAME	CUST_BALANCE
2003	Rauthor	Peter	0.00

(You should execute the procedure and verify that the new customer was added to ensure your code is correct.)

Answer 1

Short Summary:

• The stored procedure accepts the valuess as input parameter and inserts into CUSTOMER table

CREATE TABLE:
CREATE TABLE CUSTOMER(
   CUST_NUM INTEGER PRIMARY KEY,
CUST_LNAME VARCHAR(50),
CUST_FNAME VARCHAR(50),
CUST_BALANCE DECIMAL(18, 2)
);

Stored Procedure:

DELIMITER //

CREATE PROCEDURE prc_cust_add(
   IN CustNum INTEGER,
IN CustLname VARCHAR(50),
IN CustFname VARCHAR(50),
IN CustBalance DECIMAL(18, 2))
BEGIN
   INSERT INTO CUSTOMER (CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE)
       VALUES(CustNum, CustLname, CustFname, CustBalance);
END //

DELIMITER ;

To Execute the stored procedure:

CALL prc_cust_add(2003,   'Rauthor',   'Peter', 0.00);

Verify the Customer Table:

SELECT * FROM CUSTOMER;

25. SELECT FROM CUSTOMER; Result Grid Filter Rows: Edit: CUST_NUM CUST_LNAME CUST_FNAME CUST_BALANCE ► 2003 Rauthor Peter 0.00 NULL NULL NULL NULL

**************************************************************************************

Feel free to rate the answer and comment your questions, if you have any.

Please upvote the answer and appreciate our time.

Happy Studying!!!

**************************************************************************************