[go: up one dir, main page]
Scribd
Upload
22 views17 pages

Lecture 1 - Stored Procedure

The document provides an overview of functions and stored procedures in MySQL, highlighting their definitions, importance, and benefits such as code reusability and enhanced performance. It includes examples of creating a function to calculate discounts and a stored procedure with IN/OUT parameters. The content emphasizes the advantages of using stored procedures for efficient database operations.

Uploaded by

maryamanuur91
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
22 views17 pages

Lecture 1 - Stored Procedure

The document provides an overview of functions and stored procedures in MySQL, highlighting their definitions, importance, and benefits such as code reusability and enhanced performance. It includes examples of creating a function to calculate discounts and a stored procedure with IN/OUT parameters. The content emphasizes the advantages of using stored procedures for efficient database operations.

Uploaded by

maryamanuur91
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 17

CS220: DATABASE SYSTEMS

BESE-14
OBJECTIVES

Introduction
▸Functions
▸ Why Stored Procedure
▸Syntax
▸Examples
▸SP with one or more parameters
▸Types
FUNCTIONS

3
FUNCTIONS AND STORED PROCEDURES
 Function: A stored program in MySQL that returns a
single value.
 Stored Procedure: A stored program that performs one
or more actions but doesn’t necessarily return a value.
 Importance:
 Code reuse
 Enhanced performance
 Better maintainability

4
FUNCTION
DELIMITER //
CREATE FUNCTION CalculateDiscount(price DECIMAL(10, 2), discount_rate DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)

BEGIN
RETURN price - (price * discount_rate / 100);
END;//
DELIMITER ;

Usage:
SELECT CalculateDiscount(1000, 10);

5
STORED PROCEDURES

6
STORED PROCEDURE
 Prepared SQL code that you can save, so the
code can be reused over and over again.
 So if you have an SQL query that you write
over and over again, save it as a stored
procedure, and then just call it to execute it.
 You can also pass parameters to a stored
procedure, so that the stored procedure can
act based on the parameter value(s) that is
passed.
BENEFITS OF SP
 Less Network traffic
 Code Reusability

 Secure

 Easy to Maintain

8
FUNCTIONS VS STORED PROCEDURES

9
STORED PROCEDURE

10
EXAMPLE

11
CREATE PROCEDURE USING MYSQL WORKBENCH
WIZARD

12
EXAMPLE

13
14
IN AND OUT PARAMETER

15
IN/OUT PARAMETER
DELIMITER //

CREATE PROCEDURE GetDiscountedPrice(INOUT product_id INT, IN discount_percentage DECIMAL(5, 2))


BEGIN
DECLARE original_price DECIMAL(10, 2);

-- Retrieve the original price of the product


SELECT price INTO original_price FROM Products WHERE id = product_id;

-- Apply the discount and return the new price


SET product_id = original_price - (original_price * discount_percentage / 100);
END;
//

DELIMITER ;

16
THANK YOU

17

You might also like