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);
Comments