PL/SQL___________________________________

2
 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.
What is PL/SQL?

3
 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.
Stored Procedure

4
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 ;
Stored Procedure (Contd…)

5
 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).

6
 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).

7
 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.

8
Create Procedure:
DELIMITER //
CREATE PROCEDURE GetAllStudent()
BEGIN
SELECT * FROM STUDENTS;
END //
DELIMITER ;
Call Procedure:
CALL GetAllStudent;

9
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 ;
SET and SELECT

10
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;
SELECT (Contd…)

11
 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.
IN Parameter

12
DELIMITER //
CREATE PROCEDURE GetUserName(IN user_id
INT)
BEGIN
SELECT name
FROM users
WHERE id = user_id;
END //
DELIMITER ;
CALL GetUserName(1);
IN Parameter (Contd…)

13
 Purpose: The OUT parameter allows you to
return values from the procedure.
◦ 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.
OUT Parameter

14
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);
OUT Parameter

15
 Purpose:
◦ The INOUT parameter allows you to pass a value into
the procedure and then return a modified value out
of it.
◦ It behaves like both IN and OUT. You can pass a value
in and also modify and return it.
INOUT Parameter

16
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 ;
INOUT Parameter

17
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;
IF…ELSE…

18
[loop_label:] LOOP
-- statements
IF condition THEN
LEAVE loop_label;
END IF;
END LOOP;
LOOP

19
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 //
LOOP (Contd…)

20
WHILE condition DO
-- statements
END WHILE;
WHILE

21
BEGIN
WHILE counter >= 5 DO
END WHILE;
END //
WHILE (Contd…)

22
REPEAT
-- statements
UNTIL condition
END REPEAT;
REPEAT…UNTIL

23
BEGIN
REPEAT
UNTIL counter > 5
END REPEAT;
END //
REPEAT…UNTIL (Contd…)

24
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.
CURSOR

25
 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.
Types of Cursors:

26
 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.
Cursor Lifecycle:

27
 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.
IMPLICIT CURSOR

28
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 ;
IMPLICIT CURSOR (Contd…)

29
 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;
Explicit Cursors

30
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;
Explicit Cursors (Contd…)

31
-- 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;

32
-- 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 ;

33
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;
NOT FOUND

34
-- 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;
NOT FOUND (Contd…)

35
ELSE
SELECT v_first_name AS FirstName;
END IF;
END //
DELIMITER ;
NOT FOUND (Contd…)

36
 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.
ROW COUNT()

37
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();
ROW COUNT() (Contd…)

38
-- 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 ;
ROW COUNT() (Contd…)

PL/SQL___________________________________

Recommended

More Related Content

Similar to PL/SQL___________________________________ (20)

Recently uploaded (20)

PL/SQL___________________________________