Lab 4: Stored Procedures

Estimated time to complete: 3-5 hours

Prerequisites

Download files from class lab4 folder

Instructions

Warmup

Start MySQL Workbench. Execute the univdb_mysql.sql script to create a database named univ. Review the database; it should contain several tables.
Right-click the Stored Procedures section of the database and choose Create Stored Procedure. Replace the code with Figure One below. Click Apply to create the procedure.
Now, test the procedure. Execute the following commands, one at a time, using a query editor:
```
   CALL FacInDept('MS');
   CALL FacInDept('FIN');
```
Right-click the FacInDept procedure and choose Alter Stored Procedure. This creates a script to make changes to the stored procedure. Change the body of the stored procedure to look like this:
```
   SELECT * FROM Faculty WHERE FacDept LIKE CONCAT(dept, '%')
```
This select statement uses the CONCAT function to concatenate the % wildcard to the value of the dept parameter. (MySQL uses the CONCAT function to concatenate strings, instead of the || operator.) Click Apply to implement the change. Now, run the stored procedure again, this time with a parameter value of ‘M’ or ‘F’. Verify that the matching records are displayed.
Review the code in Figure Two. This stored procedure takes two numbers, adds them, and returns the result. Use MySql Workbench to create the stored procedure. Then, test it using this script:
```
   CALL AddNums(2, 3, @x);
   SELECT @x;
```
Review the code in Figure Three. This creates a stored function that will compute a sort sequence number for a given OffTerm and OffYear. The sequence number will consist of the OffYear * 10 + termsequence, where termsequence is 1 for FALL, 2 for WINTER, and 3 for SPRING, and 4 for SUMMER. Right-click the Functions section of the database and choose Create Function. Paste the code in Figure Three into the window and create the function.
Now, test the function by executing a simple SELECT:
```
   SELECT OffSOrtSeq('SPRING', 2006);
```

Next, try this SELECT query that uses it:

   select offerno, offterm, offyear, OffSortSeq(offterm, offyear) as SortSeq
   from offering
   order by SortSeq

Part 1: Implement Stored Procedure (7 points)

Create a stored procedure UpdateGrade that takes four parameters: IN Offer_No INT, IN Std_No CHAR(9), IN GRADE INT, and OUT Error_Msg VARCHAR(50).

The procedure should verify that the student with StdNo = Std_No has a record in the Enrollment table for the given OfferNo. If not, the procedure should set Error_Msg to ‘Student not enrolled in specified offering’. 1 point.
The procedure should verify that GRADE is in the range 0 .. 100 (inclusive). If not, the procedure should set Error_Msg to ‘GRADE is not in the range 0..100’. 1 point.
If all validations pass, the procedure should UPDATE the EnrGrade for the specified student and offering in the Enrollment table using the formula GRADE * 4 * .01. It should set Error_Msg to ‘SUCCESS’. 2 points.
Test the procedure comprehensively to verify that it works. Take screen shots that show the tests you ran and their results, and paste them into your lab report. Also, paste into your report the CREATE statement used to create the stored procedure. 3 points.

Part 2: Call Stored Procedure from Python (3 points)

Copy the class examples/pythondb/python-mysql-demo.py program and rename it to lab4.py. Remove the existing code that executes various SQL commands; modify the program to call your UpdateGrade stored procedure, pass in some data, and display the resulting message. Take a screen shot of the output showing the result, and put it in your lab report.

Submission

Use the following command from a command prompt to export your MySQL database to a SQL script:
```
   mysqldump -u root -ppassw0rd  -R univ >lab4.sql
```
Replace passw0rd in the above command with your database password (note there is no space between the -p and the password). Review lab4.sql to verify that it contains all of your stored procedure code (the -R switch should include stored procedures in the dump).
Save your lab report as a PDF named report.pdf.
Upload report.pdf, lab4.sql and lab4.py file to your Github Submission Repo (see link at top of this document). Review the test results to check the correctness of your submission. There is no limit on the number of test runs you may perform.

Figure One

CREATE PROCEDURE FacInDept(dept VARCHAR(10))
BEGIN
  SELECT * FROM Faculty WHERE FacDept = dept;
END

Figure Two

CREATE PROCEDURE AddNums(IN Num1 INT, IN Num2 INT, OUT Result INT)
BEGIN
  SET Result = Num1 + Num2;
END

Figure Three

CREATE FUNCTION OffSortSeq(term VARCHAR(6),  year INT) 
RETURNS int(11)
DETERMINISTIC
BEGIN
  DECLARE sortseq INT;
  IF term = 'FALL' THEN
    SET sortseq = 1;
  ELSEIF term = 'WINTER' THEN
    SET sortseq = 2;
  ELSEIF term = 'SPRING' THEN
    SET sortseq = 3;
  ELSEIF term = 'SUMMER' THEN
    SET sortseq = 4;
  END IF;
    
  RETURN year * 10 + sortseq;
END

CpS 301 Intro to Database