Store Procedure and Function in Database with simple example

SQL stored procedures and functions are powerful SQL features that enable users to create reusable and efficient code for complex database operations. Encapsulating a sequence of SQL statements into a single procedure or function allows users to simplify and modularize their code, improving both maintainability and scalability.

In a programming language, function is said to be a set of instructions that take some input and execute some tasks. A function can either be predefined or user-defined. In the C program, a function can be called multiple times to provide reusability and modularity. It may or may not return a value

Create Table

CREATE TABLE Persons (    PersonID int,    LastName varchar(255),    FirstName varchar(255),    Address varchar(255),    City varchar(255),    image BLOB);

How to create Store Procedure :

DELIMITER //CREATE  PROCEDURE insertPerson_new(IN id INT,IN lname Varchar(255), IN fname Varchar(255), IN address Varchar(255), IN city Varchar(255))BEGIN    insert into persons(PersonID,LastName,FirstName,Address,City) values(id,lname,fname,address,city);       END// 

How to call Store procedure?

call insertPerson_new(1213, 'testl', 'testfname', 'addresstest', 'citytere');

select * from Persons;

How to create functions?

DELIMITER //CREATE FUNCTION CustomerLevel(    credit DECIMAL(10,2))RETURNS VARCHAR(20)DETERMINISTICBEGIN    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 ;

How to call function?

select CustomerLevel(100);