Three exercises in writing stored procedures are available in the samples\sql\course_exercises folder in the solidDB installation directory.
You can run the sample answers to the exercises by running the samples\sql\course_exercises\runme script (runme.bat on Windows).
To create a prerequisite environment for the exercises, complete the following steps:
1 Start a solidDB server.
2 Create the required tables by using solsql to run the create_tables.sql script.
The script creates the following tables:
▪ CUSTOMER - to hold customer details
▪ ORDER - to hold customer orders
▪ ORDER_LINE - to hold line items for each order
▪ PRODUCT - to hold product details
▪ ALERTS - to hold alerts
▪ DAILY_ORDER_REPORT - to hold records of the number of each product ordered on each day
See the create_tables.sql script for details of the fields in each table.
Exercise 1
Create procedures by using the following rules:
▪ Use sequencers to provide IDs (primary keys) for the inserted rows.
▪ Commit transactions outside the procedure.
▪ Perform error handling for all procedures:
– Make sure that success of each SQL statement is checked.
– Return an error code as an output parameter (-1 = logical error).
– If an SQL error occurs, return the error code of a failed statement as the error code of the procedure.
Create the following procedures:
1 Create a procedure (INSERT_CUSTOMER), that adds customer details to the CUSTOMER table.
▪ Check for possible duplicate records (customers who have the same name and address) but allow the check to be overridden by using an input parameter.
2 Create a procedure (INSERT_ORDER), that adds customer orders to the ORDER table.
▪ Check that the customer exists (in the CUSTOMER table).
▪ Check that the credit limit of the customer is not exceeded but allow the check to be overridden.
▪ Update the used credit of the associated customer.
3 Create a procedure (INSERT_ORDER_LINE), that adds line items to the customer order.
▪ Check that the product and order exist.
▪ Generate an event if the amount of stock falls below the alert limit.
▪ Deduct the ordered amount of stock from the current stock.
See samples\sql\course_exercises\answer1.sql.
Exercise 2
Complete the following tasks:
1 Create an event that has two varchar parameters, alert_type and alert_text.
2 Launch the event when the stock amount for a product goes below the alert limit (as defined in the INSERT_ORDER_LINE procedure.
3 Create a procedure (LISTEN_INVENTORY_ALERTS), that listens for the event and does the following steps:
▪ makes an entry in the ALERTS table whenever an event is received,
▪ returns the event type and event text to the application as return values of the procedure.
See samples\sql\course_exercises\answer2.sql.
Exercise 3
Complete the following task:
Create a procedure (MAKE_DAILY_ORDER_REPORT) to report on products that need restocking:
▪ Use the DAILY_ORDER_REPORT table.
▪ For each product, work out the amount ordered today and whether the product needs restocking
▪ Return a result set that is sorted by the amount ordered today in descending order.