 PL/SQL (Procedural Language for SQL) is

Oracle Corporation's procedural extension for

SQL.
 PL/SQL includes procedural language
elements such as conditions and loops, and
can handle exceptions (run-time errors).
 The first public version of the PL/SQL
definition was in 1995.
 A stored procedure is a set of SQL
statements that can be stored in the database
and executed as a single unit.
 It allows you to encapsulate complex
operations, making it easier to manage and
reuse code.
 Stored procedures can accept parameters,
perform operations such as data
manipulation, and return results to the caller.
DELIMITER //
CREATE PROCEDURE procedure_name
([IN|OUT|INOUT] param_name datatype, ...)
BEGIN
-- Declarations (if any)
-- SQL statements (including control
structures)
-- Exception handling (optional)
END //
DELIMITER ;
 DELIMITER: By default, MySQL treats
semicolons (;) as the end of a statement. To
define a stored procedure, we change the
delimiter temporarily (e.g., to //) to let MySQL
know that the procedure’s body isn’t ending
with a semicolon.
 CREATE PROCEDURE: This is the statement to
create a new stored procedure. You must
define a unique procedure name
(procedure_name).
 Parameters:
◦ IN: Input parameter (default). The caller provides a
value.
◦ OUT: Output parameter. The procedure modifies
the value, and the caller can retrieve it.
◦ INOUT: Input/output parameter. The caller provides
an initial value, and the procedure can modify it.
 BEGIN ... END: This block contains the actual
logic of the procedure, including SQL queries
and control structures (like IF, LOOP, WHILE).
 SQL Statements: You can include multiple SQL
statements, such as SELECT, INSERT, UPDATE,
and control structures.
 Exception Handling: MySQL provides basic
error handling with DECLARE ... HANDLER.
Create Procedure:
CREATE PROCEDURE GetAllStudent()
BEGIN
SELECT * FROM STUDENTS;
END //
DELIMITER ;
Call Procedure:
CALL GetAllStudent;
DELIMITER //
CREATE PROCEDURE SetExample()
BEGIN
DECLARE v_discount DECIMAL(5,2);
-- Set a value using SET
SET v_discount = 10.00;
SELECT v_discount;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE PrintMultiple()
BEGIN
DECLARE s_name VARCHAR(100);
DECLARE s_id INT;
-- Set a value using SET
SET s_name = 'PRIYANKA';
SET s_id = '1'; SELECT CONCAT(s_name,':',s_id)
AS StudentInfo;
END
//DELIMITER ;
CALL PrintMultiple;
 Purpose: The IN parameter allows you to pass
values into the procedure.
 Characteristics:
◦ The value of an IN parameter is read-only inside the
procedure, meaning it cannot be modified.
◦ It is the most common type of parameter used to
send input data to the procedure.
DELIMITER //
CREATE PROCEDURE GetUserName(IN user_id
INT)
BEGIN
SELECT name
FROM users
WHERE id = user_id;
END //
DELIMITER ;
CALL GetUserName(1);
 Purpose: The OUT parameter allows you to
return values from the procedure.
 Characteristics:
◦ The value of an OUT parameter is written by the
procedure and returned to the caller.
◦ You can modify it within the procedure, but its
initial value is not accessible.
DELIMITER //
CREATE PROCEDURE GetUserEmail(IN user_id
INT, OUT email VARCHAR(255))
BEGIN
SELECT user_email INTO email
FROM users WHERE id = user_id;
END //
DELIMITER ;
SET @email =‘’;
CALL GetUserEmail(1, @email);
 Purpose:
◦ The INOUT parameter allows you to pass a value
into the procedure and then return a modified value
out of it.
 Characteristics:
◦ It behaves like both IN and OUT. You can pass a
value in and also modify and return it.
DELIMITER //
CREATE PROCEDURE UpdateBalance(INOUT
user_balance DECIMAL(10,2), IN
deposit_amount DECIMAL(10,2))
BEGIN
SET user_balance = user_balance +
deposit_amount;
END //
DELIMITER ;
IF condition THEN
-- statements to execute if condition is
true
ELSEIF condition THEN
-- statements to execute if this condition
is true
ELSE
-- statements to execute if none of the
conditions are true
END IF;
[loop_label:] LOOP
-- statements
IF condition THEN
LEAVE loop_label;
END IF;
END LOOP;
BEGIN
DECLARE counter INT DEFAULT 1;
loop_label: LOOP
-- Print the counter value
SELECT counter AS CounterValue;
-- Exit the loop when the counter reaches 5
IF counter >= 5 THEN
LEAVE loop_label;
END IF;
-- Increment the counter
SET counter = counter + 1;
END LOOP;
END //
WHILE condition DO
-- statements
END WHILE;
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= 5 DO
-- Print the counter value
SELECT counter AS CounterValue;
-- Increment the counter
SET counter = counter + 1;
END WHILE;
END //
REPEAT
-- statements
UNTIL condition
END REPEAT;
BEGIN
DECLARE counter INT DEFAULT 1;
REPEAT
-- Print the counter value
SELECT counter AS CounterValue;
-- Increment the counter
SET counter = counter + 1;
UNTIL counter > 5
END REPEAT;
END //
A cursor in SQL (and specifically in MySQL) is
a database object that allows you to retrieve
and manipulate rows returned by a query one
at a time. Cursors are especially useful when
you need to process each row individually
rather than working with the entire result set
at once.
 Implicit Cursors: Automatically created by
MySQL when a single-row SQL statement is
executed (e.g., SELECT INTO).
 Explicit Cursors: Defined by the user for
queries that return multiple rows. They
provide more control over fetching rows.
 Declaration: Define a cursor with a specific
SQL query.
 Opening: Execute the SQL query and establish
the cursor.
 Fetching: Retrieve rows from the cursor one
at a time.
 Closing: Release the cursor and free
resources.
 An implicit cursor is automatically created by
the database system when executing SQL
statements that return a single row or
multiple rows.
 Unlike explicit cursors, which need to be
explicitly declared, opened, fetched, and
closed, implicit cursors handle these
operations automatically.
 They are typically used in SELECT INTO,
INSERT, UPDATE, and DELETE statements.
DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)
BEGIN
DECLARE emp_name VARCHAR(100);
DECLARE emp_salary DECIMAL(10,2);
-- Implicit cursor used in
SELECT INTO
SELECT name, salary INTO emp_name, emp_salary
FROM employees
WHERE id = emp_id;
-- Output the employee's details
SELECT emp_name AS Name, emp_salary AS Salary;
END //
DELIMITER ;
 Declare the Cursor:
DECLARE cursor_name CURSOR FOR
SELECT_statement;
 Open the Cursor:
OPEN cursor_name;
 Fetch Rows:
FETCH cursor_name INTO variable1, variable2, ...;
 Close the Cursor:
CLOSE cursor_name;
DELIMITER //
CREATE PROCEDURE ProcessEmployees()
BEGIN
DECLARE v_emp_id INT;
DECLARE v_first_name VARCHAR(50);
DECLARE done INT DEFAULT FALSE;
-- Declare the cursor for fetching employee
details
DECLARE emp_cursor CURSOR FOR
SELECT employee_id, first_name FROM
employees;
-- Declare a handler for when there are no more rows
to fetch
DECLARE CONTINUE HANDLER FOR NOT FOUND SET
done = TRUE;
-- Open the cursor
OPEN emp_cursor;
-- Loop to fetch rows from the cursor read_loop:
LOOP
FETCH emp_cursor INTO v_emp_id, v_first_name;
IF done THEN
LEAVE read_loop;
-- Exit the loop if no more rows
END IF;
 -- Process each row (for demonstration,
just select)
SELECT v_emp_id AS EmployeeID,
v_first_name AS FirstName;
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END //
DELIMITER ;
DELIMITER //

CREATE PROCEDURE CheckEmployee(IN emp_id

INT)
BEGIN
DECLARE v_first_name VARCHAR(50);
DECLARE done INT DEFAULT FALSE;

-- Declare a handler for when no rows are found

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
-- Select the first name of the employee based
on employee ID
SELECT first_name INTO v_first_name
FROM employees
WHERE employee_id = emp_id;
-- Check if a row was found or not
IF done THEN
SELECT 'No employee found with that ID' AS
Message;
 ELSE
SELECT v_first_name AS FirstName;
END IF;
END //
DELIMITER ;
 The ROW_COUNT() function returns the
number of rows affected by the last executed
INSERT, UPDATE, or DELETE statement. It’s
useful for checking whether an operation was
successful.
DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary(IN
emp_id INT, IN new_salary DECIMAL(10,2))
BEGIN
-- Update the salary for the specified employee
UPDATE employees
SET salary = new_salary
WHERE employee_id = emp_id;
-- Check the number of affected rows
DECLARE affected_rows INT;
SET affected_rows = ROW_COUNT();
 -- Output the result
IF affected_rows > 0 THEN
SELECT 'Salary updated successfully.' AS
Message;
ELSE
SELECT 'No employee found with that ID.'
AS Message;
END IF;
END //
DELIMITER ;
Thank you