PL/SQL = Procedural Language + SQL
PL/SQL stands for Procedural Language for SQL, and it is Oracle Corporation’s
procedural extension for SQL and the Oracle relational database.
🔹 What Is PL/SQL?
------------------------------
PL/SQL is a programming language used to write programs that interact with Oracle
databases. It combines the power of SQL (Structured Query Language) with procedural
programming features like variables, loops, and conditions.
🔧 Key Features of PL/SQL
===================================================================================
==
Feature Description
Tightly integrated with SQL You can run SQL statements directly inside PL/SQL
code.
Procedural language Supports variables, loops, conditionals (IF, CASE),
etc.
Blocks of code PL/SQL code is organized into blocks (anonymous
or named).
Error handling Built-in exception handling using EXCEPTION
blocks.
Stored procedures You can write functions, procedures, and triggers stored
in the database.
It's Oracle's extension of SQL that lets you write blocks of code with:
Variables
Loops
Conditions (IF, CASE)
Error handling (EXCEPTION)
🧱 Basic PL/SQL Structure
=============================================================
BEGIN
-- SQL and PL/SQL code goes here
DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
📘 What You Can Do With PL/SQL
==================================================================
Create stored procedures and functions
Write triggers that respond to database events (e.g., inserts, updates)
Build packages that group procedures/functions logically
Handle complex business logic inside the database
Improve performance by reducing round trips between the app and the database.
-----------------------------------------------------------------------------
Example: Stored Procedure
CREATE OR REPLACE PROCEDURE add_employee (
p_emp_id IN NUMBER,
p_emp_name IN VARCHAR2
) AS
BEGIN
INSERT INTO employees(employee_id, first_name) VALUES (p_emp_id, p_emp_name);
COMMIT;
END;
/
You call this procedure to add a new employee.
---------------------------------------------------------------------------------
Example: Function
CREATE OR REPLACE FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2 AS
v_name VARCHAR2(100);
BEGIN
SELECT first_name INTO v_name FROM employees WHERE employee_id = p_emp_id;
RETURN v_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Employee Not Found';
END;
/
You call this function to get the name of an employee by their ID.
---------------------------------------------------------------------------------
A trigger is a special kind of stored procedure that automatically runs (or
"fires") in response to certain events on a table like INSERT, UPDATE, or DELETE.
Example: Trigger on INSERT
CREATE OR REPLACE TRIGGER trg_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
-- Automatically set the hire date to current date if not provided
IF :NEW.hire_date IS NULL THEN
:NEW.hire_date := SYSDATE;
END IF;
END;
/
This trigger fires before a new employee is inserted, and if no hire date is given,
it sets it to the current date.
-----------------------------------------------------------------------------------
----------------------------------
✅ Real-World Example
==========================================================================
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT first_name INTO v_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
An Introduction to Stored Procedures and Packages
=========================================================================
Oracle allows you to access and manipulate database information using procedural
schema objects called PL/SQL program units. Procedures, functions, and packages are
all examples of PL/SQL program units.
Stored Procedures and Functions
-------------------------------------
A procedure or function is a schema object that logically groups a set of SQL and
other PL/SQL programming language statements together to perform a specific task.
Procedures and functions are created in a user's schema and stored in a database
for continued use.
You can execute a procedure or function interactively using an Oracle tool, such as
SQL*Plus, or call it explicitly in the code of a database application, such as an
Oracle Forms or Precompiler application, or in the code of another procedure or
trigger.
Figure 14 - 1 illustrates a simple procedure stored in the database, being called
by several different database applications.
https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch14.htm#:~:text=A
%20procedure%20or%20function%20is,a%20database%20for%20continued%20use.
oracle userid USER589681
