MySQL Stored Procedure and Stored Function – Theory and Lab.
Exercise 9
The Tables in this exercise 9 come from previous exercise 8. Hence,
you can straight away, use these tables.
CREATE TABLE contacts1 (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
created_date DATE,
created_by VARCHAR(30),
CONSTRAINT contacts1_pk PRIMARY KEY (contact_id)
);
----------------
CREATE TABLE customer (
acc_no INTEGER PRIMARY KEY,
cust_name VARCHAR(20),
avail_balance DECIMAL
);
-------------------
CREATE TABLE contacts2 (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT contacts2_pk PRIMARY KEY (contact_id)
);
-------------------
CREATE TABLE contacts3 (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
created_date DATE,
created_by VARCHAR(30),
CONSTRAINT contacts3_pk PRIMARY KEY (contact_id)
);
--------------------
1
CREATE TABLE products (
product_id INT(11) NOT NULL AUTO_INCREMENT,
product_name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2),
CONSTRAINT products_pk PRIMARY KEY (product_id)
);
This table you need to create and populate with some records like:
INSERT INTO products (product_name, price) VALUES ('Product 1',
10.99);
SELECT * FROM products;
---------------
CREATE TABLE employees (
employee_id INT(11) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50),
hire_date DATE,
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
This table you need to create and populate with some records like:
INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES ('John', 'Doe', '[Link]@[Link]', CURDATE());
SELECT * FROM employees;
---------------
2
SQL Scripts (Source Code)
DELIMITER //
-- Procedure to insert a new contact into the contacts1 table
CREATE PROCEDURE insert_contact1(
IN p_last_name VARCHAR(30),
IN p_first_name VARCHAR(25),
IN p_birthday DATE
)
BEGIN
INSERT INTO contacts1 (last_name, first_name, birthday)
VALUES (p_last_name, p_first_name, p_birthday);
END //
-- Procedure to update a customer's balance in the customer table
CREATE PROCEDURE update_customer_balance(
IN p_acc_no INTEGER,
IN p_amount DECIMAL
)
BEGIN
UPDATE customer
SET avail_balance = avail_balance + p_amount
WHERE acc_no = p_acc_no;
END //
-- Function to get the total number of contacts in the contacts2 table
CREATE FUNCTION get_total_contacts2()
RETURNS INT
READS SQL DATA
BEGIN
DECLARE total_contacts INT;
SELECT COUNT(*) INTO total_contacts FROM contacts2;
RETURN total_contacts;
END //
-- Procedure to delete a contact from the contacts3 table
CREATE PROCEDURE delete_contact3(
IN p_contact_id INT
)
BEGIN
DELETE FROM contacts3 WHERE contact_id = p_contact_id;
END //
3
-- Function to get the average price of products in the products table
CREATE FUNCTION get_average_price()
RETURNS DECIMAL(10, 2)
READS SQL DATA
BEGIN
DECLARE avg_price DECIMAL(10, 2);
SELECT AVG(price) INTO avg_price FROM products;
RETURN avg_price;
END //
-- Procedure to insert a new employee into the employees table
CREATE PROCEDURE insert_employee(
IN p_first_name VARCHAR(30),
IN p_last_name VARCHAR(30),
IN p_email VARCHAR(50),
IN p_hire_date DATE
)
BEGIN
INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES (p_first_name, p_last_name, p_email, p_hire_date);
END //
DELIMITER ;
-----------------------
4
I. Definition of a Stored Procedure:
A stored procedure is a reusable block of code that can perform a specific set of operations in a
database. It can accept input parameters and return output parameters or result sets.
The syntax for creating a stored procedure in MySQL is:
CREATE PROCEDURE procedure_name(
[parameter_list]
)
BEGIN
-- Procedure body
-- SQL statements
END;
How to Run a Stored Procedure:
There are two main ways to run a stored procedure in MySQL:
1. Interactive Mode (using variables):
- Set the values for the input parameters using user-defined variables.
- Call the procedure using the `CALL` statement and pass the variables as arguments.
Example:
SET @lastName = 'Mathew';
SET @firstName = 'John';
SET @birthDate = '1990-05-15';
CALL insert_contact1(@lastName, @firstName, @birthDate);
2. Scripting Mode (passing values directly):
- Call the procedure using the `CALL` statement and pass the parameter values directly.
Example:
CALL insert_contact1('Mathew', 'John', '1990-05-15');
5
Scripting Mode Explanation:
Scripting mode refers to executing SQL statements directly without the need for user-defined
variables or interactive input. In scripting mode, you pass the parameter values as part of the
`CALL` statement when executing a stored procedure or stored function.
For example, to call the `insert_employee` procedure in scripting mode:
CALL insert_employee('John', 'Mathew', '[Link]@[Link]',
'2023-05-01');
In this case, the parameter values ('John', 'Mathew', '[Link]@[Link]', '2023-05-
01') are provided directly as part of the `CALL` statement, without the need for user-defined
variables.
Scripting mode is commonly used when you know the parameter values in advance or when
you want to embed the procedure or function calls within a larger SQL script or application
code.
II. Definition of a Stored Function:
A function is a reusable piece of code that performs a specific task and returns a value.
Functions in MySQL are similar to functions in other programming languages.
The syntax for creating a function in MySQL is:
CREATE FUNCTION
function_name(
[parameter_list] )
RETURNS data_type
BEGIN
-- Function body
-- SQL
statements RETURN
value; END;
How to Run a Stored Function:
To run a function in MySQL, you can use the `SELECT` statement along with the function name
and any required arguments (if the function accepts parameters).
Example:
SELECT get_total_contacts2();
6
Explanation and Expected Output for Each Component:
1. Procedure: `insert_contact1`
- Explanation: This procedure inserts a new contact into the `contacts1` table with the
provided `last_name`, `first_name`, and `birthday`.
- Expected Output: A new record will be inserted into the `contacts1` table with the given
values.
- Example:
CALL insert_contact1('Mathew', 'John', '1990-05-15');
2. Procedure: `update_customer_balance`
- Explanation: This procedure updates the `avail_balance` of a customer in the
`customer` table by adding the `p_amount` to the existing balance for the given `p_acc_no`.
- Expected Output: The `avail_balance` of the customer with the specified `acc_no` will
be increased by the `p_amount` value. - Example:
CALL update_customer_balance(1234, 500.00);
3. Function: `get_total_contacts2`
- Explanation: This function returns the total number of records in the `contacts2` table.
- Expected Output: The function will return an integer value representing the total
number of contacts in the `contacts2` table. - Example:
SELECT get_total_contacts2();
If the `contacts2` table has 10 records, the function will return `10`.
4. Procedure: `delete_contact3`
- Explanation: This procedure deletes a contact record from the `contacts3` table based
on the provided `contact_id`.
- Expected Output: The record with the specified `contact_id` will be deleted from the
`contacts3` table. - Example:
CALL delete_contact3(123);
7
To provide the input for the `p_contact_id` parameter, you can use either interactive mode
(with user-defined variables) or scripting mode (passing the value directly).
- Interactive Mode:
SET @contactId = 123;
CALL delete_contact3(@contactId);
- Scripting Mode:
CALL delete_contact3(123);
[Link]: `get_average_price'
- Explanation: This function calculates and returns the average price of products in the
`products` table.
- Expected Output: The function will return a decimal value representing the average
price of products in the `products` table. - Example:
SELECT get_average_price();
If the `products` table has products with prices `10.99`, `15.50`, and `20.00`, the function will
return `15.4967` (assuming appropriate rounding).
6. `insert_employee`
- Explanation: This procedure inserts a new employee record into the `employees` table
with the provided `first_name`, `last_name`, `email`, and `hire_date`.
- Expected Output: A new record will be inserted into the `employees` table with the
given values.
- Example:
CALL insert_employee('John', 'Mathew', '[Link]@[Link]',
'2023-05-01');