PL/SQL Training and Placements Institute In RT Nagar, Bangalore |

PL/SQL (Procedural Language/Structured Query Language)

What is PL/SQL?

PL/SQL (Procedural Language/SQL) is an extension of SQL developed by Oracle Corporation for writing procedural scripts within an Oracle database. It combines SQL (Structured Query Language) with procedural programming features like variables, loops, and exception handling, allowing developers to create powerful and efficient database applications.

✅ Key Features of PL/SQL:
✔ Procedural Language – Supports variables, loops, conditions.
✔ Block Structure – Code is written in blocks (BEGIN… END).
✔ Exception Handling – Catches and handles errors efficiently.
✔ Performance – Reduces network traffic with stored procedures.
✔ Security – Uses privilege-based access control.

PL/SQL Architecture

PL/SQL follows a block-based architecture that consists of three major components:

1️⃣ PL/SQL Engine – Processes and executes PL/SQL statements.
2️⃣ SQL Engine – Executes SQL commands inside PL/SQL blocks.
3️⃣ Database Server – Stores and manages data using Oracle Database.

PL/SQL Block Structure

PL/SQL code is written in blocks, which consist of:

Basic PL/SQL Block Example

DECLARE
   v_name VARCHAR2(50);  -- Variable Declaration
BEGIN
   SELECT first_name INTO v_name FROM employees WHERE employee_id = 100;  
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);  -- Display Output
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
      DBMS_OUTPUT.PUT_LINE('No record found');
   WHEN OTHERS THEN  
      DBMS_OUTPUT.PUT_LINE('Some error occurred');
END;

PL/SQL Data Types

PL/SQL supports various data types categorized into:

🔹 Example: Variable Declaration

DECLARE
   v_emp_name VARCHAR2(100);  -- String Variable
   v_salary NUMBER(10,2);  -- Numeric Variable
   v_hire_date DATE;  -- Date Variable
   v_is_manager BOOLEAN := TRUE;  -- Boolean Variable
BEGIN
   -- Assign values
   v_emp_name := 'John Doe';
   v_salary := 50000.75;
   v_hire_date := SYSDATE;
   
   -- Print values
   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
   DBMS_OUTPUT.PUT_LINE('Hire Date: ' || v_hire_date);
END;

PL/SQL Control Structures

PL/SQL allows control flow using conditional statements, loops, and exception handling.

1️⃣ Conditional Statements (IF-ELSE)

DECLARE
   v_salary NUMBER := 50000;
BEGIN
   IF v_salary > 60000 THEN
      DBMS_OUTPUT.PUT_LINE('High Salary');
   ELSIF v_salary BETWEEN 40000 AND 60000 THEN
      DBMS_OUTPUT.PUT_LINE('Average Salary');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Low Salary');
   END IF;
END;

2️⃣ Loops (FOR, WHILE, LOOP)

a) FOR Loop

BEGIN
   FOR i IN 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
   END LOOP;
END;

b) WHILE Loop

DECLARE
   v_counter NUMBER := 1;
BEGIN
   WHILE v_counter <= 5 LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
      v_counter := v_counter + 1;
   END LOOP;
END;

c) Simple LOOP

DECLARE
   v_counter NUMBER := 1;
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > 5;
   END LOOP;
END;

PL/SQL Cursors (Fetching Data)

A cursor is used to fetch multiple rows from a database query.

🔹 Implicit Cursor (Automatically managed by Oracle)

BEGIN
   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
   IF SQL%ROWCOUNT > 0 THEN
      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
   END IF;
END;

🔹 Explicit Cursor (Manually Controlled)

DECLARE
   CURSOR emp_cursor IS SELECT first_name, salary FROM employees WHERE salary > 50000;
   v_name employees.first_name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_name, v_salary;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary);
   END LOOP;
   CLOSE emp_cursor;
END;

PL/SQL Procedures & Functions

PL/SQL supports stored procedures and functions for reusable code.

1️⃣ Stored Procedure

CREATE OR REPLACE PROCEDURE update_salary(p_emp_id NUMBER, p_percent NUMBER) AS
BEGIN
   UPDATE employees
   SET salary = salary + (salary * p_percent / 100)
   WHERE employee_id = p_emp_id;
   COMMIT;
END;

2️⃣ Function (Returns a Value)

CREATE OR REPLACE FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER AS
   v_salary NUMBER;
BEGIN
   SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
   RETURN v_salary;
END;

🔹 Calling the Function:

DECLARE
   v_sal NUMBER;
BEGIN
   v_sal := get_employee_salary(100);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_sal);
END;

PL/SQL Exception Handling

PL/SQL allows handling errors using the EXCEPTION block.

🔹 Example: Handling NO_DATA_FOUND Exception

DECLARE
   v_salary NUMBER;
BEGIN
   SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee not found');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred');
END;

Advantages of PL/SQL

✔ Efficient & Fast – Reduces network traffic by executing logic within the database.
✔ Highly Secure – Access control via stored procedures.
✔ Modular Code – Supports functions, procedures, packages.
✔ Exception Handling – Handles runtime errors effectively.

PL/SQL vs SQL

PL/SQL Career Opportunities

💼 PL/SQL Developer – Writes stored procedures and triggers.
💼 Database Administrator (DBA) – Manages Oracle databases.
💼 Backend Developer – Works on database-driven applications.
💼 Data Analyst – Analyzes data using SQL queries.

Final Thoughts

🔹 PL/SQL is essential for Oracle Database Development.
🔹 Mastering Cursors, Procedures, and Triggers can boost your database skills.
🔹 PL/SQL is widely used in finance, banking, healthcare, and enterprise applications.