Stored procedures



Basic MySQL Stored procedures 


DELIMITER //

CREATE PROCEDURE sp_name(parameter_list)
BEGIN
   statements;
END //

DELIMITER ;

SP Parameter types


[IN | OUT | INOUT] parameter_name datatype[(length)]
  1. IN : default | calling program must pass an argument | protected, changing value within SP does not change original value, work with a copy of the value.
  2. OUT : can be modified | updated value is then passed back to the calling program | cannot access the initial value of the OUT parameter when they begin.
  3. INOUT : calling program may pass the argument | can modify | pass the new value back to the calling program. 
#pass a session variable ( @total ) to receive the return value.
CALL GetOrderCountByStatus('Shipped',@total); #1st IN 2nd OUT
SELECT @total;

#INOUT
DELIMITER $$

CREATE PROCEDURE SetCounter(
	INOUT counter INT,
    IN inc INT
)
BEGIN
	SET counter = counter + inc;
END$$

DELIMITER ;

SET @counter = 1;
CALL SetCounter(@counter,1); -- 2
CALL SetCounter(@counter,1); -- 3
CALL SetCounter(@counter,5); -- 8
SELECT @counter; -- 8

Variables


DECLARE variable_name datatype(size) [DEFAULT default_value];
DECLARE totalSale DEC(10,2) DEFAULT 0.0;

#Assigning variables
SET variable_name = value;
SELECT COUNT(*) INTO productCount FROM products;

Listing Stored Procedures


SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
SHOW PROCEDURE STATUS LIKE '%Order%'

Conditional Statements


DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  pCustomerNumber INT, 
    OUT pCustomerLevel  VARCHAR(20))
BEGIN
    DECLARE credit DECIMAL(10,2) DEFAULT 0;

    SELECT creditLimit 
    INTO credit
    FROM customers
    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN
        SET pCustomerLevel = 'PLATINUM';
    ELSEIF credit <= 50000 AND credit > 10000 THEN
        SET pCustomerLevel = 'GOLD';
    ELSE
        SET pCustomerLevel = 'SILVER';
    END IF;
END$$

DELIMITER ;

CALL GetCustomerLevel(141, @level);
SELECT @level; #PLATINUM

Simple CASE statement


CASE case_value
    WHEN when_value1 THEN ...
    WHEN when_value2 THEN ...
    ELSE 
        BEGIN
        END;
END CASE;

Searched CASE statement



Loops


#LOOP

[label]: LOOP
    IF condition THEN
        LEAVE [label]; //The loop exits when the LEAVE statement is reached.
        #ITERATE [label]; //to skip the current iteration
    END IF;
END LOOP;

#WHILE

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

# REPEAT

[begin_label:] REPEAT
    statement;
UNTIL condition
END REPEAT [end_label]

Error Handling



Show Warnings


SHOW WARNINGS;
SHOW WARNINGS [LIMIT [offset,] row_count] --> SHOW WARNINGS LIMIT 2;
SHOW COUNT(*) WARNINGS;
SELECT @@warning_count; #system variable:

SHOW VARIABLES LIKE 'max_error_count';
SET max_error_count=2048;

Show ERRORS


SHOW ERRORS;
SHOW ERRORS [LIMIT [offset,] row_count];
SHOW COUNT(*) ERRORS;
SELECT @@error_count; # system variable

DECLARE … HANDLER


DECLARE { EXIT | CONTINUE } HANDLER
    FOR condition_value [, condition_value] ...
    statement

#Example
DELIMITER //

CREATE PROCEDURE insert_user(
	IN p_username VARCHAR(50), 
    IN p_email VARCHAR(50)
)
BEGIN
  -- SQLSTATE for unique constraint violation
  DECLARE EXIT HANDLER FOR SQLSTATE '23000'
  BEGIN
    -- Handler actions when a duplicate username is detected
    SELECT 'Error: Duplicate username. Please choose a different username.' AS Message;
  END;

  -- Attempt to insert the user into the table
  INSERT INTO users (username, email) VALUES (p_username, p_email);

  -- If the insertion was successful, display a success message
  SELECT 'User inserted successfully' AS Message;

END //

DELIMITER ;

#call
CALL insert_user('jane','jane@example.com');

DECLARE ... CONDITION


DECLARE unknown_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR unknown_table 
  BEGIN
    -- body of handler
  END;

SIGNALs


DELIMITER //

CREATE PROCEDURE update_salary(
	IN p_employee_id INT,
    IN p_salary DECIMAL
)
BEGIN 
	DECLARE employee_count INT;
    
    -- check if employee exists
    SELECT COUNT(*) INTO employee_count 
    FROM employees
    WHERE id = p_employee_id;
    
    IF employee_count = 0 THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Employee not found';
    END IF;
    
    -- validate salary
    IF p_salary < 0 THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
    
    -- if every is fine, update the salary
    UPDATE employees
    SET salary = p_salary
    WHERE id = p_employee_id;    

END //

DELIMITER ;

RESIGNAL


#1) Using MySQL RESIGNAL to re-raise the same exception
DELIMITER //

CREATE PROCEDURE DropTableXYZ()
BEGIN
  -- reraise the error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL;
  END;
  
  -- drop a table that doesn't exist
  DROP TABLE XYZ;
END//

DELIMITER ;

-- ERROR 1051 (42S02): Unknown table 'classicmodels.xyz'

#2) Using RESIGNAL statement with new signal information	
BEGIN
  -- reraise the error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL SET MYSQL_ERRNO = 5;
  END;
  
  -- drop a table that doesn't exist
  DROP TABLE XYZ;
END//

-- ERROR 5 (42S02): Unknown table 'classicmodels.xyz'

#3) Re-rasing exception with new condition value and signal information:
BEGIN
  -- reraise the error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 5;
  END;
  
  -- drop a table that doesn't exist
  DROP TABLE XYZ;
END//

-- ERROR 5 (45000): Unknown table 'classicmodels.xyz'

Cursor


DELIMITER $$

CREATE PROCEDURE create_email_list (
	INOUT email_list TEXT
)
BEGIN
	DECLARE done BOOL DEFAULT false;
	DECLARE email_address VARCHAR(100) DEFAULT "";
    
	-- declare cursor for employee email
	DECLARE cur CURSOR FOR SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET done = true;
	
    -- open the cursor
	OPEN cur;
	
    SET email_list = '';
	
    process_email: LOOP
		
        FETCH cur INTO email_address;
        
		IF done = true THEN 
			LEAVE process_email;
		END IF;
		
        -- concatenate the email into the emailList
		SET email_list = CONCAT(email_address,";",email_list);
	END LOOP;
    
    -- close the cursor
	CLOSE cur;

END$$

DELIMITER ;

Prepared Statement


PREPARE stmt_name FROM preparable_stmt;
SET @var_name1 = value1;
SET @var_name2 = value2;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

PREPARE insert_user FROM 'INSERT INTO users (username, email) VALUES (?, ?)';
SET @username = 'john_doe';
SET @email = 'jone@example.com';
EXECUTE insert_user USING @username, @email;

#reuse, This time, MySQL will use the precompiled statement:
SET @username = 'jane_doe';
SET @email = 'jane@example.com';
EXECUTE insert_user USING @username, @email;
DEALLOCATE PREPARE insert_user;

Stored Functions


DELIMITER $$

CREATE FUNCTION CustomerLevel(
	credit DECIMAL(10,2)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE customerLevel VARCHAR(20);

    IF credit > 50000 THEN
		SET customerLevel = 'PLATINUM';
    ELSEIF (credit >= 50000 AND 
			credit <= 10000) THEN
        SET customerLevel = 'GOLD';
    ELSEIF credit < 10000 THEN
        SET customerLevel = 'SILVER';
    END IF;
	-- return the customer level
	RETURN (customerLevel);
END$$

DELIMITER ;

SHOW FUNCTION STATUS WHERE db = 'classicmodels';

SELECT customerName, CustomerLevel(creditLimit)
FROM customers ORDER BY customerName;

DROP FUNCTION


DROP FUNCTION [IF EXISTS] function_name;

Listing Stored Functions


SHOW FUNCTION STATUS [LIKE 'pattern' | WHERE search_condition];
SHOW FUNCTION STATUS; # shows all stored functions in the current MySQL server
SHOW FUNCTION STATUS WHERE db = 'classicmodels';
SHOW FUNCTION STATUS LIKE '%pattern%';
#Note that the SHOW FUNCTION STATUS only shows the function that you have a privilege to access.

Stored Program Security



Transactions


SET autocommit = OFF; #or
SET autocommit = 0;

SET autocommit = 1; #or
SET autocommit = ON;

CREATE DATABASE banks;
USE banks;
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255)
);

START TRANSACTION;
INSERT INTO users (id, username) 
VALUES (1, 'john');


UPDATE users 
SET email = 'john.doe@example.com' 
WHERE id = 1;

# SELECT * FROM users; shows the inserted data only for current session since it didn't commit yet
COMMIT;

# ROLLBACK; - undoes all the changes made during the transaction, reverting the database to its state before the transaction started

Triggers


DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger body (SQL statements)
END;
//

DELIMITER ;

# to drop a trigger
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

# to show
SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

SHOW TRIGGERS FROM database_name LIKE 'pattern'; # similar to SHOW TRIGGERS IN database_name LIKE 'pattern';

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE|AFTER}{INSERT|UPDATE|DELETE} 
ON table_name FOR EACH ROW 
{FOLLOWS|PRECEDES} existing_trigger_name
BEGIN
    -- statements
END$$

DELIMITER ;

How To Call a Stored Procedure From a Trigger 


DELIMITER $$

CREATE TRIGGER before_accounts_update
BEFORE UPDATE
ON accounts FOR EACH ROW
BEGIN
    CALL CheckWithdrawal (
        OLD.accountId, 
        OLD.amount - NEW.amount
    );
END$$

DELIMITER ;

Events



lifecycle


SHOW PROCESSLIST; #view the status of the event scheduler thread
SET GLOBAL event_scheduler = ON; #to enable and start
SET GLOBAL event_scheduler = OFF;

Create an Event


CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE <schedule> #this can be vary based on the schedule
DO
event_body

#If the event is a one-time event, you use the syntax:
AT timestamp [+ INTERVAL]

#If the event is a recurring event, you use the EVERY clause:
EVERY interval 
STARTS timestamp [+INTERVAL] 
ENDS timestamp [+INTERVAL]

SHOW EVENTS FROM mydb; #To show all events in the mydb database

CREATE EVENT one_time_log
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
   INSERT INTO messages(message)
   VALUES('Preserved One-time event');

ALTER EVENT


ALTER EVENT [IF EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
DO event_body

#Changing the schedule
ALTER EVENT test_event
ON SCHEDULE EVERY 2 MINUTE;

#Changing the event body
ALTER EVENT test_event
DO
   INSERT INTO messages(message)
   VALUES('New message');
#Disabling an event
ALTER EVENT test_event
DISABLE;

#Enabling an event
ALTER EVENT test_event
ENABLE;

#Renaming an event
ALTER EVENT test_event
RENAME TO sample_event;

#Moving an event to another database
ALTER EVENT mydb.sample_event
RENAME TO newdb.test_event;

DROP EVENT


DROP EVENT [IF EXISTS] event_name [, event_name] ...;

SHOW EVENTS


SHOW EVENTS [FROM db_name] 
[LIKE 'pattern' | WHERE expr];

Views



CREATE VIEW


CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)]
AS
  select-statement;

SHOW TABLES; #gives all the tables and viwes since both shred same nemespace
SHOW FULL TABLES; #To know which object is a view or table

View Processing Algorithms


#CREATE VIEW
CREATE [OR REPLACE][ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW 
   view_name[(column_list)]
AS 
   select-statement;

#ALTER VIEW
CREATE [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW 
   view_name[(column_list)] 
AS 
   select-statement;

Updatable views


Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
DISTINCT
GROUP BY clause.
HAVING clause.
UNION or UNION ALL clause.
Left join or outer join.
Subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause.
Reference non-updatable views in the FROM clause.
Use literal values.
Multiple references to any column of the base table.

WITH CHECK OPTION


CREATE OR REPLACE VIEW view_name 
AS
  select_statement
WITH CHECK OPTION;

Show View


SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = 'VIEW';

SHOW FULL TABLES
[{FROM | IN } database_name]
LIKE pattern;

#Query data from the table information_schema.tables to get the views in a database.
SELECT * FROM information_schema.tables;

SHOW CREATE VIEW


SHOW CREATE VIEW view_name;

Rename View


RENAME TABLE original_view_name TO new_view_name;

DROP VIEW


DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;