Question

How can I create the following stored procedure in MySQL Workbench?:

Create a stored procedure named prc_new_rental to insert new rows in the RENTAL table. The procedure should satisfy the following conditions:

a. The membership number will be provided as a parameter.

b. Use a Count() function to verify that the membership number exists in the MEMBERSHIP table. If it does not exist, then a message should be displayed that the membership does not exist and no data should be written to the database.

c. If the membership does exist, then retrieve the membership balance and display a message that the balance amount is the previous balance. (For example, if the membership has a balance of $5.00, then display “Previous balance: $5.00”.)

d. Insert a new row in the rental table using the sequence created in Case 51 to generate the value for RENT_NUM, the current system date for the RENT_DATE value, and the membership number provided as the value for MEM_NUM.

Answer 1

You can create a stored procedure to insert new rows in a table, given it satisfies the provided conditions in the following way:

-- the stored procedure to be created is called prc_new_rental

-- a membership number is provided, which is passed as a parameter to the stored procedure

CREATE OR REPLACE PROCEDURE prc_new_rental (MEM_NUM_TEMP IN MEMBERSHIP.MEM_NUM%TYPE) AS COUNT_MEMBER NUMBER;

PREV_MEM_BALANCE MEMBERSHIP.MEM_BALANCE%TYPE;

BEGIN -- start of procedure

SELECT COUNT(*) INTO COUNT_MEMBER FROM MEMBERSHIP

-- count membership number into variable COUNT_MEMBER

WHERE MEM_NUM = MEM_NUM_TEMP;

IF COUNT_MEMBER = 0 THEN

dbms_output.put_line('A membership with number ' || MEM_NUM_TEMP || ' does not exist.');

ELSE

/* - if membership number count is not zero, then display previous balance and insert values in the table */

SELECT MEM_BALANCE INTO PREV_MEM_BALANCE FROM MEMBERSHIP

WHERE MEM_NUM = MEM_NUM_TEMP;

dbms_output.put_line('Previous Balance is: '|| To_Char(PREV_MEM_BALANCE, '$999,999,990.99'));

INSERT INTO RENTAL (RENT_NUM, RENT_DATE, MEM_NUM)

VALUES (RENT_NUM_SEQ.NEXTVAL, SYSDATE, MEM_NUM_TEMP);

END IF; -- end of if statement

END; -- end of procedure