MySQL CREATE PROCEDURE

MySQL CREATE PROCEDURE Statement

In MySQL, a stored procedure is a collection of SQL statements stored on the server and executed as a single unit. The CREATE PROCEDURE statement is used to define a stored procedure.

Stored procedures improve reusability, consistency, and performance by encapsulating logic that can be reused across multiple queries or applications.

Syntax

CREATE PROCEDURE procedure_name ([parameter1, parameter2, ...])
[characteristics]
BEGIN
    -- SQL statements
END;

Key Components:

• procedure_name: Name of the procedure (unique within a database).
• parameters: Input/output parameters (optional).
  • Each parameter must specify a mode (IN, OUT, INOUT) and a data type.
  • Example: IN param1 INT.
• characteristics: Optional attributes such as DETERMINISTIC or SQL SECURITY.
• BEGIN ... END: Block containing the SQL statements.

Parameter Modes

1. IN:
   • Passes a value into the procedure.
   • Default mode.
2. OUT:
   • Used to return a value from the procedure.
3. INOUT:
   • Passes a value in and returns a value after modification.

Examples

1. Basic Stored Procedure

A procedure without parameters.

DELIMITER $$

CREATE PROCEDURE show_all_employees()
BEGIN
    SELECT * FROM employees;
END$$

DELIMITER ;

Call the procedure:

CALL show_all_employees();

2. Stored Procedure with IN Parameter

A procedure that accepts an employee ID and retrieves their details.

DELIMITER $$

CREATE PROCEDURE get_employee_details(IN emp_id INT)
BEGIN
    SELECT * 
    FROM employees
    WHERE employee_id = emp_id;
END$$

DELIMITER ;

Call the procedure:

CALL get_employee_details(101);

3. Stored Procedure with OUT Parameter

A procedure that calculates and returns the total salary.

DELIMITER $$

CREATE PROCEDURE calculate_total_salary(OUT total_salary DECIMAL(10,2))
BEGIN
    SELECT SUM(salary) INTO total_salary
    FROM employees;
END$$

DELIMITER ;

Call the procedure:

CALL calculate_total_salary(@total);
SELECT @total AS TotalSalary;

4. Stored Procedure with INOUT Parameter

A procedure that updates a value and returns the modified result.

DELIMITER $$

CREATE PROCEDURE update_salary(INOUT salary DECIMAL(10,2))
BEGIN
    SET salary = salary * 1.10; -- Increase salary by 10%
END$$

DELIMITER ;

Call the procedure:

SET @emp_salary = 5000.00;
CALL update_salary(@emp_salary);
SELECT @emp_salary AS UpdatedSalary;

5. Stored Procedure with Multiple Parameters

A procedure that calculates a discounted price based on an input price and discount rate.

DELIMITER $$

CREATE PROCEDURE calculate_discount(
    IN original_price DECIMAL(10,2),
    IN discount_rate DECIMAL(5,2),
    OUT discounted_price DECIMAL(10,2)
)
BEGIN
    SET discounted_price = original_price - (original_price * discount_rate / 100);
END$$

DELIMITER ;

Call the procedure:

CALL calculate_discount(100.00, 10, @price);
SELECT @price AS DiscountedPrice;

Result:

DiscountedPrice
90.00

Managing Stored Procedures

1. Viewing Stored Procedures

To list all stored procedures in a database:

SHOW PROCEDURE STATUS WHERE Db = 'database_name';

To view the procedure definition:

SHOW CREATE PROCEDURE procedure_name;

2. Dropping a Stored Procedure

To delete a procedure:

DROP PROCEDURE IF EXISTS procedure_name;

Characteristics

Optional attributes for procedures:

1. DETERMINISTIC / NOT DETERMINISTIC:
   • Specifies whether the procedure always produces the same result for the same input.
2. CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA**:
   • Defines the type of SQL statements the procedure contains.
3. SQL SECURITY:
   • Determines the execution context:
     • DEFINER: Executes with the privileges of the procedure's creator.
     • INVOKER: Executes with the privileges of the user invoking the procedure.

Advantages of Stored Procedures

1. Performance:
   • Reduce network overhead by executing multiple SQL statements on the server.
2. Reusability:
   • Encapsulate logic for use in multiple applications.
3. Security:
   • Restrict direct access to tables and encapsulate complex logic.
4. Maintainability:
   • Centralized logic makes updates easier.

Common Use Cases

• Automating business logic.
• Implementing complex calculations.
• Managing data transformations.
• Triggering backend processes.

Let me know if you'd like help with specific examples or further explanations!