store procedures

 1. Create a Stored Procedure or Function

First, create your stored procedure or function in MySQL. For example, to create a stored procedure:

DELIMITER //

CREATE PROCEDURE my_procedure(IN input_param INT)
BEGIN
    SELECT input_param;
END //

DELIMITER ;

This procedure, my_procedure, will remain in the database system after you restart the MySQL server.

2. Check if the Procedure/Function Exists

After creating the procedure or function, you can verify its existence in MySQL by querying the information_schema:

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

Or for functions:

SHOW FUNCTION STATUS WHERE Db = 'your_database_name';

Call procedure
CALL my_procedure(10);

--------

Here's how you can create both a stored procedure and a function in MySQL.
1. Creating a Stored Procedure
Let's create a stored procedure that takes two integer parameters, adds them together, and returns the result as an output parameter.

DELIMITER //

CREATE PROCEDURE add_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END //

DELIMITER ;
IN num1 and IN num2 are the input parameters.
OUT result is the output parameter, which will store the result of the addition.
The procedure uses the SET statement to calculate the sum and assign it to the result.
How to Call the Stored Procedure:

CALL add_numbers(10, 5, @output);
SELECT @output; -- This will display the result (15)
2. Creating a Function
Let's create a function that also takes two integers, adds them, and returns the sum.

DELIMITER //

CREATE FUNCTION add_two_numbers(num1 INT, num2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN num1 + num2;
END //

DELIMITER ;
num1 and num2 are the input parameters.
RETURNS INT specifies that the function will return an integer value.
DETERMINISTIC indicates that the function always returns the same result for the same input parameters.
How to Call the Function:

SELECT add_two_numbers(10, 5) AS result; -- This will return 15
Key Differences:
Stored Procedure: More versatile, can handle multiple result sets and output parameters.
Function: Returns a single value and can be used directly in SQL queries (like SELECT, WHERE, etc.).
Both the stored procedure and function will remain available on the MySQL server even after it restarts.


Explanation of the Code:

CREATE PROCEDURE GetLowestCost (OUT LowestCost DECIMAL(6,2))
SELECT MIN(Cost) INTO LowestCost FROM Orders;
CREATE PROCEDURE: This statement is used to define a new stored procedure in MySQL.
GetLowestCost: The name of the stored procedure being created.
(OUT LowestCost DECIMAL(6,2)):
This specifies that LowestCost is an OUT parameter.
The OUT keyword means that this parameter is used to return a value from the procedure.
DECIMAL(6,2) defines the data type of LowestCost as a decimal number with up to 6 digits, 2 of which are after the decimal point.
SELECT MIN(Cost): This part of the procedure selects the minimum value of the Cost column from the Orders table. The MIN() function returns the smallest value in a column.
INTO LowestCost: This stores the result of MIN(Cost) into the LowestCost variable, which is the OUT parameter.
FROM Orders: This indicates that the data is being selected from the Orders table.
What This Stored Procedure Does:
When you call this stored procedure, it will find the lowest cost from the Orders table and return that value in the LowestCost variable.
Example Usage:
To call the stored procedure and capture the output:

CALL GetLowestCost(@lowest);
SELECT @lowest;  -- This will display the lowest cost from the Orders table.
In this example, @lowest will hold the value returned by the LowestCost OUT parameter.


Stored Procedure vs. Function:
Stored Procedures:
Can perform actions like INSERT, UPDATE, DELETE, and SELECT.
Can return multiple result sets.
Can have IN, OUT, and INOUT parameters.
Cannot be used directly in SQL statements like a SELECT.
Functions:
Primarily used to compute and return a value.
Can only return a single value.
Can be used in SQL statements (like SELECT, WHERE, etc.).
Cannot perform actions like INSERT, UPDATE, or DELETE (only SELECT).
1. Using Stored Procedures
a) Creating a Stored Procedure
Here’s an example of a stored procedure that takes two numbers as input, adds them, and returns the result.
sql
DELIMITER //

CREATE PROCEDURE add_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END //

DELIMITER ;
IN: Input parameter (you pass values to it).
OUT: Output parameter (it returns a value to you).
b) Calling the Stored Procedure
Once the stored procedure is created, you can call it using the CALL statement.
sql
CALL add_numbers(5, 10, @output);
SELECT @output;  -- This will return 15
Here, @output captures the result of the stored procedure.
2. Using Functions
a) Creating a Function
A function takes input, performs a calculation, and returns a value. Let’s create a function to calculate the square of a number.
sql
DELIMITER //

CREATE FUNCTION square_number(num INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN num * num;
END //

DELIMITER ;
DETERMINISTIC means the function will always return the same result for the same input.
The function returns an integer value.
b) Using the Function
You can use functions directly in SQL queries, unlike stored procedures.
sql
SELECT square_number(4);  -- This will return 16
You can also use the function in other SQL statements, like WHERE or GROUP BY clauses:
sql
SELECT * FROM my_table WHERE square_number(my_column) > 100;
Differences in Calling Stored Procedures and Functions
Stored Procedure:
Called using the CALL statement.
It can accept multiple input (IN) and output (OUT) parameters.
It cannot be used directly in a SELECT statement.
Example:
sql
CALL add_numbers(10, 20, @output);
Function:
Called like a built-in MySQL function (e.g., SUM, AVG).
Can return only one value.
Can be used in SELECT, WHERE, and ORDER BY statements.
Example:
sql
SELECT square_number(5);
Real-Life Example
a) Stored Procedure Example:
Let’s say you want to create a stored procedure that fetches the details of a customer by their ID.



DELIMITER //

CREATE PROCEDURE get_customer_details(IN customer_id INT)
BEGIN
    SELECT * FROM customers WHERE id = customer_id;
END //

DELIMITER ;
You can call this procedure like this:



CALL get_customer_details(123);
This would return all the details of the customer whose ID is 123.
b) Function Example:
Let’s create a function that returns the total amount a customer has spent based on their orders.

DELIMITER //

CREATE FUNCTION total_spent(customer_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(order_total) INTO total FROM orders WHERE customer_id = customer_id;
    RETURN total;
END //

DELIMITER ;
Now you can use this function in a query:
SELECT total_spent(123) AS total FROM dual;
This will return the total amount spent by the customer with ID 123.

---------------

Creating a stored procedure in a database that uses only input variables is quite straightforward. Below, I'll guide you through the process using MySQL as an example. The steps are similar in other relational databases, but the syntax might differ slightly.
Example: MySQL Stored Procedure
1. Create a Table
First, let's create a simple table to work with:
sql