SQL

PROCEDURES
Karthika Devi M S
Assistant Professor, DCSE
PROCEDURES IN PL/SQL

 PL/SQL procedures are reusable code blocks that perform specific actions

or logic within a database environment.

 They consist of two main components such as the procedure

header which defines the procedure name and optional parameters and
the procedure body which contains the executable statements
implementing the desired business logic.
PROCEDURES IN PL/SQL
 A PL/SQL procedure is a reusable block of code that contains a specific set of
actions or logic.
The procedure contains two parts:
1. Procedure Header
 The procedure header includes the procedure name and optional parameter list.
 It is the first part of the procedure and specifies the name and parameters

2. Procedure Body
 The procedure body contains the executable statements that implement the
specific business logic.
 It can include declarative statements, executable statements, and exception-
handling statements
 PARAMETERS IN
PROCEDURES
1. IN parameters
 Used to pass values into the procedure
 Read-only inside the procedure
 Can be a variable, literal value, or expression in the calling statement.

2. OUT parameters
 Used to return values from the procedure to the calling program
 Read-write inside the procedure
 Must be a variable in the calling statement to hold the returned value

3. IN OUT parameters
 Used for both passing values into and returning values from the procedure
 Read-write inside the procedure
 Must be a variable in the calling statement
 VARIABLE DECLARATION IN
PL/SQL
DECLARE
name VARCHAR2(20) := ‘Welcome';
BEGIN
DBMS_OUTPUT.PUT_LINE(name);
END;

Initializing during declaration

DECLARE
name VARCHAR2(20) := ‘Welcome';
BEGIN
DBMS_OUTPUT.PUT_LINE(name);
END;
 Initialization After Declaration
 CREATE PROCEDURE
CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [, parameter da
tatype]) ]
BEGIN
Declaration_section
Executable_section
END ; Parameter Name Descriptions
It represents the name of the
procedure_name
stored procedure.
It represents the number of
parameter parameters. It can be one or more
than one.
It represents the declarations of
Declaration_section
all variables.
It represents the code for the
Executable_section
function execution.
 HOW TO CALL A STORED PROCEDURE?
 We can use the CALL statement to call a stored procedure. This
statement returns the values to its caller through its parameters (IN, OUT,
or INOUT). The following syntax is used to call the stored procedure in
MySQL:
 CALL procedure name ( parameter(s))

CREATE PROCEDURE get_merit_student ()

BEGIN
SELECT * FROM student_info WHERE marks > 70;
SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END &&
DELIMITER ;

CALL get_merit_student();
 PROCEDURES WITH IN
PARAMETER
DELIMITER //
CREATE PROCEDURE GetCustomerSalary(IN CustomerID Int)
BEGIN
SELECT SALARY FROM CUSTOMERS WHERE ID = CustomerID;
END //
DELIMITER ;
 PROCEDURES WITH OUT
PARAMETER
DELIMITER //
CREATE PROCEDURE GetDetail(OUT total INT)
BEGIN
SELECT COUNT(AGE) INTO total FROM CUSTOMERS
WHERE AGE = 25;
END //
DELIMITER ;
 PROCEDURES WITH INOUT
PARAMETER
 The INOUT parameter is a combination of an IN parameter and an OUT parameter.
You can pass data into the stored procedure and receive data from the stored
procedure using the same parameter.
DELIMITER //
CREATE PROCEDURE increaseSalary(INOUT Cust_Id Int, INOUT curr_Salary
Int)
BEGIN
SELECT SALARY INTO curr_Salary From CUSTOMERS Where ID =
Cust_Id;
SET curr_Salary = curr_Salary * 1.1;
Update CUSTOMERS SET SALARY = curr_Salary Where ID = Cust_Id;
END //
DELIMITER ;
In the procedure: cust_id and curr_Salary. These two are used as both an input and
output parameters.
The stored procedure first retrieves the current salary of the customer from the database
using the cust_id parameter. It then increases the salary by 10% and updates the
customers salary in the database using the same parameter.
HOW TO DELETE/DROP STORED
PROCEDURES IN MYSQL?
 DROP PROCEDURE [ IF EXISTS ] procedure_name;
PL/SQL FOR LOOP
 PL/SQL stands for Procedural Language/ Structured Query Language.
It has block structure programming features.
 With PL/SQL, you can fetch data from the table, add data to the table,
make decisions, perform repetitive tasks, and handle errors.
 PL/SQL supports SQL queries. To fetch records, process data, or execute
complex calculations, the FOR loop helps to efficiently iterate over a range
of values or collections

DECLARE
--declare loop variable and provide its datatype
loop_varaible datatype;
BEGIN
--for loop with start and end value
FOR loop_variable IN start_value .. end_value LOOP
set of statements
END LOOP;
END;
PRINT NUMBER FROM 1 TO 5 USING FOR
LOOP IN PL/SQL
SET SERVEROUTPUT ON;
DECLARE
counter NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('PL/SQL FOR LOOP EXECUTION');
FOR counter IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('COUNTER VALUE: '|| counter);
END LOOP;
END;
PL/SQL NESTED FOR
LOOP
PL/SQL WHILE LOOP
 In PL/SQL, the WHILE loop statement is a control structure statement that repeatedly

executes a code block that is inside the 'While Loop' as long as the specific
condition set in the 'While Loop' is TRUE.

 The condition set in the 'WHILE' statement is a boolean expression that evaluates

to TRUE, FALSE, or NULL.

 To terminate the loop prematurely or based on some specific scenario then

the EXIT or EXIT WHEN statement is used.

 The WHILE LOOP is used when you are not sure about the number of times the code

block needs to execute.

 Only during the WHILE Loop execution, the specific condition set to end the execution is

made TRUE and the control moves out of the WHILE Loop statement.
PL/SQL WHILE LOOP
PURPOSE OF PL SQL
• The purpose of PL/SQL is to merge database commands with procedural
programming language.
• It offers more complete programming solutions for building critical
applications that operate on the Oracle database.
FEATURES OF PL/SQL
• PL/SQL is tightly integrated with SQL.
• It offers extensive extensive error checking mechanisms.
• It supports numerous data types for flexible data handling.
• Includes a variety of programming structures, such as loops and
conditionals. Includes a variety of programming structures, such as loops
and conditionals.
• It supports structured programming through functions and procedures.
• It supports object-oriented programming, enabling more complex data
handling and manipulation.
• It supports the web application development and server pages.
SQL VS PL SQL SQL PL SQL

SQL (Structured Query Language) is a PL/SQL (Procedural Language/SQL) is an

standard language used for creating, extension of SQL. And it adds
manipulating, and retrieving data procedural capabilities to SQL,
from relational databases. enabling the creation of more complex
and powerful database applications

SQL is mainly used to write queries, as PL/SQL supports variables, data types,
well as create and execute DDL (Data and control structures such as loops
Definition Language) and DML (Data and conditionals, which SQL does
Manipulation Language) statements. not. This makes PL/SQL more efficient for
writing program blocks, functions,
procedures, triggers, and packages.
ADVANTAGES OF
PROCEDURES
 They result in performance improvement of the application. If a procedure
is being called frequently in an application in a single connection, then the
compiled version of the procedure is delivered.
 They reduce the traffic between the database and the application since the
lengthy statements are already fed into the database and need not be sent
again and again via the application.
 They add to code reusability, similar to how functions and methods work in
other languages such as C/C++ and Java.
DISADVANTAGES OF
PROCEDURES
 Stored procedures can cause a lot of memory usage. The database
administrator should decide an upper bound as to how many stored
procedures are feasible for a particular application.
 MySQL does not provide the functionality of debugging the stored
procedures.
FUNCTION
A function is same as a procedure except that it returns a value.

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
 PL SQL FUNCTIONS PL SQL PROCEDURES
Functions always return a value The procedure can return a value
after the execution of queries. using “IN OUT” and “OUT”
arguments.

Each and every time functions are Procedures are compiled only once
compiled they provide output but they can be called many times
according to the given input. as needed without being compiled
each time.

A Function can not return multiple A procedure is able to return

result sets. multiple result sets.
The function can be called using procedures cannot be called from
Stored Procedure. function.
A function used only to read data. A procedure can be used to read
and modify data.
The return statement of a function While the return statement of the
returns the control and function’s procedure returns control to the
result value to the calling program. calling program, it can not return
the result value.
 PL SQL FUNCTIONS PL SQL PROCEDURES

The function does not support Procedure supports try-catch

try-catch blocks blocks for error handling.

A function can be operated in the While it can’t be operated in the

SELECT statement. SELECT statement.

Functions do not permit It allows transaction

transaction management. management.

In functions, we can use only a In procedures, we can use

table variable. Temporary tables temporary tables or table
can not be created in function. variables to store temporary
data.
PL SQL CURSORS
 Oracle creates a memory area, known as the context area, for

processing an SQL statement, which contains all the information needed

for processing the statement; for example, the number of rows
processed, etc.

 A cursor is a pointer to this context area. PL/SQL controls the

context area through a cursor. A cursor holds the rows (one or more)
returned by a SQL statement. The set of rows the cursor holds is referred to
as the active set.
PL SQL TRIGGERS
 Trigger is a statement that a system executes automatically when there is any modification to the

database.

 In a trigger, we first specify when the trigger is to be executed and then the action to be performed

when the trigger executes.

 Triggers are used to specify certain integrity constraints and referential constraints that cannot be

specified using the constraint mechanism of SQL.

 They are used to maintain the integrity of the data, enforce business rules, and automate tasks.
We can set triggers to fire before or after an INSERT, UPDATE, or DELETE operation.
TYPES OF TRIGGERS
1.AFTER INSERT activated after data is inserted into the table.

2.AFTER UPDATE: activated after data in the table is modified.

3.AFTER DELETE: activated after data is deleted/removed from the table.

4.BEFORE INSERT: activated before data is inserted into the table.

5.BEFORE UPDATE: activated before data in the table is modified.

6.BEFORE DELETE: activated before data is deleted/removed from the table.

WRITE A TRIGGER TO ENSURE THAT NO
EMPLOYEE OF AGE LESS THAN 25 CAN BE
INSERTED IN THE DATABASE.
delimiter $$
CREATE TRIGGER Check_age BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF NEW.age < 25 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR:
AGE MUST BE ATLEAST 25 YEARS!';
END IF;
END; $$
delimiter;
create table employee_backup (employee_no int, employee_name varchar(40), job varchar(40),
hiredate date, salary int, primary key(employee_no));

delimiter $$
CREATE TRIGGER Backup BEFORE DELETE ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee_backup
VALUES (OLD.employee_no, OLD.name,
OLD.job, OLD.hiredate, OLD.salary);
END; $$
delimiter;
Declare count int
Set count=0;
delimiter $$
CREATE TRIGGER Count_tupples
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
SET count = count + 1;
END; $$
delimiter;
• SQL Server triggers are special stored procedures that are executed automatically in
response to the database object, database, and server events. SQL Server provides three
type of triggers:

• Data manipulation language (DML) triggers which are invoked automatically in response to
INSERT, UPDATE, and DELETE events against tables.
• Data definition language (DDL) triggers which fire in response to CREATE, ALTER, and DROP
statements. DDL triggers also fire in response to some system stored procedures that
perform DDL-like operations.
• Logon triggers which fire in response to LOGON events
•Creating a trigger in SQL Server – show you how to create a trigger in response to insert and delete
events.
•Creating an INSTEAD OF trigger – learn about the INSTEAD OF trigger and its practical applications.
•Creating a DDL trigger – learn how to create a DDL trigger to monitor the changes made to the
structures of database objects such as tables, views, and indexes.
•Disabling triggers – learn how to disable a trigger of a table temporarily so that it does not fire when
associated events occur.
•Enabling triggers – show you how to enable a trigger.
•Viewing the definition of a trigger – provide you with various ways to view the definition of a trigger.
•Listing all triggers in SQL Server – show you how to list all triggers in a SQL Server by querying data
from the sys.triggers view.
•Removing triggers – guide you how to drop one or more existing trigger.