Play Store Application link – SQL in 18 steps – App on Google Play
Stored procedures and functions help automate and reuse SQL queries. Think of them as reusable scripts that can execute database operations efficiently. Instead of writing the same query multiple times, you can call a stored procedure or function when needed.
1. What Are Stored Procedures?
A stored procedure is a set of SQL statements that are saved in the database and can be executed when needed.
Real-world example: Imagine a banking application where you frequently need to transfer money between accounts. Instead of writing complex SQL queries repeatedly, you can create a stored procedure that takes from_account, to_account, and amount as input and executes the transaction.
2. Creating and Using Stored Procedures
MySQL Example:
DELIMITER $$
CREATE PROCEDURE TransferMoney(
IN from_account INT,
IN to_account INT,
IN amount DECIMAL(10,2)
)
BEGIN
START TRANSACTION;
UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
COMMIT;
END $$
DELIMITER ;
Executing the Stored Procedure:
CALL TransferMoney(101, 102, 500.00);
3. SQL Functions
Functions return a value and can be used inside queries. They can be Scalar (returning a single value), Aggregate (working on multiple rows), or Table-Valued (returning a table).
Scalar Function (Returns a Single Value)
CREATE FUNCTION GetAccountBalance(acc_id INT) RETURNS DECIMAL(10,2)
BEGIN
DECLARE balance DECIMAL(10,2);
SELECT balance INTO balance FROM accounts WHERE account_id = acc_id;
RETURN balance;
END;
Usage:
SELECT GetAccountBalance(101) AS balance;
Aggregate Functions (Operate on Multiple Rows)
SELECT COUNT(*) FROM customers; -- Counts total customers
SELECT AVG(salary) FROM employees; -- Finds average salary
Table-Valued Function (Returns a Table – SQL Server)
CREATE FUNCTION GetHighSalaryEmployees()
RETURNS TABLE
AS
RETURN (
SELECT * FROM employees WHERE salary > 50000
);
Usage:
SELECT * FROM GetHighSalaryEmployees();
4. Database-Specific Variations
PL/SQL in Oracle:
CREATE OR REPLACE PROCEDURE UpdateSalary(emp_id INT, new_salary DECIMAL)
AS
BEGIN
UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
COMMIT;
END;
Execution:
BEGIN
UpdateSalary(105, 70000);
END;
/
T-SQL in SQL Server:
CREATE PROCEDURE UpdateSalary @emp_id INT, @new_salary DECIMAL(10,2)
AS
BEGIN
UPDATE employees SET salary = @new_salary WHERE employee_id = @emp_id;
END;
Execution:
EXEC UpdateSalary 105, 70000;
Conclusion
Stored procedures and functions make SQL powerful by allowing reusable, efficient, and optimized database operations. While stored procedures help execute multiple statements, functions return specific values or tables. Understanding their usage in different databases like MySQL, Oracle (PL/SQL), and SQL Server (T-SQL) helps in writing better database-driven applications.
I will immediately clutch your rss feed as I can’t to find your e-mail subscription link or newsletter service. Do you’ve any? Please permit me know in order that I could subscribe. Thanks.
I’ll right away take hold of your rss as I can’t to find your e-mail subscription hyperlink or e-newsletter service. Do you have any? Please allow me realize in order that I may just subscribe. Thanks.
Greetings! Very helpful advice in this particular article! It is the little changes that make the largest changes. Thanks for sharing!
I am sure this piece of writing has touched all the internet users, its really really fastidious paragraph on building up new web site.
I’ll immediately seize your rss as I can’t to find your e-mail subscription link or e-newsletter service. Do you have any? Kindly permit me understand so that I may subscribe. Thanks.
I really like what you guys tend to be up too. This kind of clever work and coverage! Keep up the excellent works guys I’ve incorporated you guys to our blogroll.
I am sure this paragraph has touched all the internet people, its really really nice paragraph on building up new weblog.
Magical Leprechaun Pudding is a yummy snack for St. Patrick’s Day and can also be utilized as a great small science experiment for the children too!
These are really impressive ideas in on the topic of blogging. You have touched some fastidious factors here. Any way keep up wrinting.
You need to be a part of a contest for one of the finest sites on the net. I will highly recommend this web site!
Hello! I’ve been reading your website for a while now and finally got the bravery to go ahead and give you a shout out from Huffman Tx! Just wanted to tell you keep up the good job!
I am sure this paragraph has touched all the internet viewers, its really really pleasant piece of writing on building up new website.
I am sure this paragraph has touched all the internet visitors, its really really good article on building up new web site.
You have made some decent points there. I checked on the net for more information about the issue and found most people will go along with your views on this web site.
I am sure this post has touched all the internet users, its really really pleasant post on building up new blog.
My brother recommended I may like this web site. He was totally right. This put up actually made my day. You cann’t believe simply how so much time I had spent for this information! Thank you!
There’s definately a lot to know about this topic. I love all of the points you’ve made.
**men balance**
MEN Balance Pro is a high-quality dietary supplement developed with research-informed support to help men maintain healthy prostate function.
**aquasculpt**
aquasculpt is a premium metabolism-support supplement thoughtfully developed to help promote efficient fat utilization and steadier daily energy.
**prodentim reviews**
ProDentim is a distinctive oral-care formula that pairs targeted probiotics with plant-based ingredients to encourage strong teeth, comfortable gums, and reliably fresh breath
I like it when folks get together and share thoughts. Great website, stick with it!
Ahaa, its good dialogue concerning this piece of writing here at this weblog, I have read all that, so now me also commenting at this place.
Wow! This blog looks exactly like my old one! It’s on a entirely different subject but it has pretty much the same page layout and design. Great choice of colors!
Ahaa, its fastidious discussion on the topic of this article here at this blog, I have read all that, so at this time me also commenting at this place.
I am sure this paragraph has touched all the internet visitors, its really really nice post on building up new website.
There is definately a great deal to learn about this topic. I love all of the points you have made.
Ahaa, its fastidious discussion on the topic of this paragraph here at this web site, I have read all that, so at this time me also commenting here.
You made some good points there. I looked on the net for more information about the issue and found most people will go along with your views on this website.
What’s up, I log on to your blog regularly. Your story-telling style is witty, keep up the good work!
I’ll immediately seize your rss as I can’t in finding your e-mail subscription hyperlink or e-newsletter service. Do you’ve any? Please allow me understand so that I may just subscribe. Thanks.