Scribd
Upload
2 views3 pages

4 MySQL_Control_Structures_and_Exception_Handling

The document discusses MySQL's procedural SQL capabilities, focusing on control structures such as IF statements, CASE statements, and iterative controls like LOOP and WHILE. It also covers basic exception handling using DECLARE ... HANDLER statements, providing examples for practical applications in various domains like banking and inventory systems. Additionally, it highlights key differences between MySQL and PL/SQL in terms of syntax and exception handling features.

Uploaded by

Amardip Yadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views3 pages

4 MySQL_Control_Structures_and_Exception_Handling

The document discusses MySQL's procedural SQL capabilities, focusing on control structures such as IF statements, CASE statements, and iterative controls like LOOP and WHILE. It also covers basic exception handling using DECLARE ... HANDLER statements, providing examples for practical applications in various domains like banking and inventory systems. Additionally, it highlights key differences between MySQL and PL/SQL in terms of syntax and exception handling features.

Uploaded by

Amardip Yadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Assignment No 4

Unnamed PL/SQL code block: Use of Control structure and Exception handling

MySQL Control Structures and Exception Handling

Introduction to MySQL Procedural SQL


MySQL provides procedural programming capabilities using stored procedures and functions,
which allow developers to write complex logic within the database server. These procedural
extensions include control flow statements (like IF, CASE, LOOP) and limited exception handling
using DECLARE ... HANDLER. This is similar in concept to PL/SQL in Oracle but differs in syntax
and capabilities.

1. Control Structures in MySQL


Control structures determine the logical flow of a MySQL stored procedure or function.

a. Conditional Control (IF Statement)


IF condition THEN
-- statements
ELSEIF condition THEN
-- statements
ELSE
-- default statements
END IF;

b. CASE Statement
CASE expression
WHEN value1 THEN
-- statements
WHEN value2 THEN
-- statements
ELSE
-- default statements
END CASE;

c. Iterative Control (LOOP, WHILE, REPEAT)


i. LOOP
label: LOOP
-- statements
LEAVE label;
END LOOP;
ii. WHILE
WHILE condition DO
-- statements
END WHILE;

iii. REPEAT
REPEAT
-- statements
UNTIL condition
END REPEAT;

2. Exception Handling in MySQL


MySQL provides basic exception handling using DECLARE ... HANDLER statements inside
procedures or functions.

a. Declaring Exception Handlers


DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
-- statements to handle any SQL error

b. Example of Error Handling


DELIMITER $$

CREATE PROCEDURE withdraw(IN p_amount DECIMAL(10,2))


BEGIN
DECLARE v_balance DECIMAL(10,2) DEFAULT 10000;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'An error occurred during withdrawal.' AS message;
END;

IF p_amount <= 0 THEN


SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid withdrawal amount';
ELSEIF p_amount > v_balance THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
ELSE
SET v_balance = v_balance - p_amount;
SELECT CONCAT('Withdrawal successful. Remaining balance: ', v_balance) AS message;
END IF;
END$$

DELIMITER ;
3. Real-World Applications
- Banking: Ensuring withdrawals don’t exceed balances.
- Inventory Systems: Rejecting orders if stock is insufficient.
- Student Portals: Validating grades, attendance, etc.
- Billing Systems: Checking for duplicate invoices or invalid entries.

4. Key Differences from PL/SQL (Oracle)


Feature | MySQL | PL/SQL (Oracle)
---------------------------|-------------------------------|-----------------------------
Syntax | Simpler procedural SQL | Full-featured procedural SQL
Exception Handling | Limited (handlers + SIGNAL) | Rich (WHEN OTHERS, user-defined)
Triggers, Cursors | Supported | Supported
Looping Structures | LOOP, WHILE, REPEAT | LOOP, WHILE, FOR

Conclusion
MySQL’s procedural SQL capabilities — including control structures and exception handling —
empower developers to build reliable, modular, and logically consistent applications directly
within the database.

You might also like