CS-10337 – Applied Database Technologies� Lecture 10

By Prof. Rafael Orta

Wayground

Last class we covered

• Dynamic SQL
• Non-graded Hands-on practice

Agenda

• Stored Procedures
• User defined Functions
• Mini-Lab about Stored Procedures

Stored Procedures

Stored Procedures are powerful tool in the SQL programmer’s toolbox.

​

Definition: A stored procedure in MySQL is a pre-compiled collection of SQL statements (and optional control logic) that you store in the database under a name (via CREATE PROCEDURE) and call when needed.

​

It may accept input and/or output parameters and may perform operations (SELECTs, INSERTs, UPDATES, DELETEs, transaction control) rather than just returning a single value.

​

Stored Procedures

Why use them?

​

• Reusability: encapsulate complex logic once, call many times.

​

• Abstraction: hide implementation details behind a procedure name.

​

• Maintainability: change logic in one place instead of duplicating in many queries/apps.

​

• Performance & network efficiency: reduce client-server communication by grouping multiple statements into one call

​

• Security: Limit direct table access by granting users permission to execute procedures, rather than granting direct access to the tables.

​

Stored Procedures

Stored Procedures

Stored Procedures

Stored Procedures

Stored Procedures

What Is the SIGNAL Statement?

​

The SIGNAL statement in MySQL is used to raise a custom error or warning condition inside a stored program — such as a stored procedure, function, or trigger.

​

It’s the database equivalent of saying “⚠️ something’s wrong here — stop and tell the caller what happened.”

​

Think of it like a “raise exception” or “throw error” command in programming languages such as Java, Python, or C++.

​

Explanation:

​

• SQLSTATE is a five-character error code (ANSI standard).
• '45000' is the generic code for “unhandled user-defined exception.”
• MESSAGE_TEXT is the human-readable message that will appear when the error is raised.

​

You can also set other fields like:

​

• MYSQL_ERRNO (custom numeric code)
• CONDITION_NAME
• MESSAGE_TEXT
• CLASS_ORIGIN, etc.

​

Stored Procedures

Stored Procedures

Stored Procedures

Stored Procedures

Stored Procedures

Stored Procedures

Demo Time

Create a store procedure that given a course code as input parameter produces as output the class roster.

Supplemental Video(s)

MySQL: STORED PROCEDURES by Bro Code

Supplemental Reading

How to use stored procedures in MySQL by DigitalOcean

Knowledge Check

Which of the following statements are TRUE regarding Stored Procedures in MySQL?

Select all that apply (more than one answer may be correct)

​

A. A stored procedure in MySQL always returns a single scalar value that can be used inside a SELECT.�B. A stored procedure in MySQL can accept IN, OUT, and INOUT parameters.�C. You execute a stored procedure in MySQL using the CALL statement.�D. Stored procedures cannot perform UPDATE statements — they are only for SELECT.�E. Using stored procedures can help encapsulate business logic in the database rather than spreading it in application code.�F. You cannot define transaction control (BEGIN, COMMIT, ROLLBACK) inside a MySQL stored procedure.

​

Correct answers: B, C, E.

Functions

In MySQL, there are two types of functions

​

1. Built-in (Native) Functions

These are the functions that come pre-installed with MySQL.

�They are part of the language itself — you don’t create them; you just use them.

​

2. User-Defined Functions (UDFs)

These are custom functions you create yourself using the CREATE FUNCTION statement.

​

They allow you to:

​

• Encapsulate reusable logic (like computing tax, discounts, or commissions)
• Return a single value (unlike procedures, which perform actions)
• Use them inside queries just like built-in functions

​

Functions

Why UDFs?

​

• Reusability: Encapsulate logic once, call many times.
• Maintainability: Change logic in one place rather than many queries.
• Readability: Give your logic a meaningful name (e.g., ufn_calc_discount) rather than embedding complex expressions everywhere.
• Abstraction: Let queries remain simpler at the call site, hiding details behind a function.

​

Functions

Functions

Why do I need to provide that information?

​

You declare those characteristics because MySQL cannot always infer the behavior of your stored routine.

�These declarations help MySQL:

​

• Optimize safely
• Enforce security rules
• Support replication and recovery correctly

​

In other words — you’re giving MySQL the “flight characteristics” of your procedure so it knows what it’s allowed to do and how it might behave in complex operations like transactions, backups, and replication.

​

Functions

Functions

Functions

Functions

Functions

Functions

Demo Time

Objective: Convert a letter grade stored in enrollments.grade into grade points so you can compute GPAs in queries.

​

Function: ufn_grade_points(letter)

Input: a letter like 'A', 'B', 'C', 'D', 'F' (optionally with +/-)

Output: a numeric grade-point value (e.g., A=4.0, B=3.0, …)

Type: DETERMINISTIC NO SQL (pure calculation, no table reads)

​

Demo Time

Supplemental Video(s)

Creating a Stored Function in MySQL by MySQL

Supplemental Reading

MySQL – Creating a stored function by GeeksforGeeks

Knowledge Check

Which of the following statements are TRUE regarding User-Defined Functions (UDFs) in MySQL?

Select all that apply (more than one answer may be correct)�

A. A UDF in MySQL can return a single value and be used in a SELECT statement.�B. You can specify OUT or INOUT parameters when creating a UDF.�C. You should avoid naming your UDF the same as a built-in MySQL function.�D. A UDF can perform UPDATE statements inside its body and still be used inside a WHERE clause.�E. A UDF is always the best choice if you need to return a result set of multiple rows.

​

Correct answers: A, C

Non-graded Mini-lab

Mini-Lab: Show Student Count by Term (Stored Procedure)

​

Objective: Students will create and execute a stored procedure that displays how many students are enrolled in each academic term.

​

Tables Used: enrollments(enrollment_id, student_id, course_id, term, grade)

​

​

Non-graded Mini-lab

Attendance

Reference Material used in this presentation

• Murach’s MySQL 3^rd Edition.
• Fundamentals of Database Systems, 7^th Edition by Elmasnri
• Proprietary Material by the author.
• Material from Professor Jack Mayers.
• Material from Professor Phillip Quinn.