PL/SQL Course: From Beginner to Advanced

This course is designed to guide you through PL/SQL, from the basics to advanced topics. Each
module contains explanations and example queries to reinforce learning.

Module 1: Introduction to PL/SQL

1.1 What is PL/SQL?

 Explanation: PL/SQL (Procedural Language/Structured Query Language) is Oracle's

procedural extension to SQL. It allows you to write code in a procedural format, making
SQL more powerful with features like loops, conditions, and exception handling.

1.2 Structure of a PL/SQL Block

 Explanation: A PL/SQL program is made up of blocks. Each block can contain multiple
statements and has three sections:
1. Declaration Section (optional): Declare variables, constants, etc.
2. Execution Section (mandatory): The main logic of the block is placed here.
3. Exception Section (optional): Handle exceptions or errors that occur during
execution.
 Query:

DECLARE
v_message VARCHAR2(50);
BEGIN
v_message := 'Hello, PL/SQL!';
DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

Module 2: Variables and Data Types

2.1 Declaring Variables

 Explanation: Variables are used to store data. PL/SQL supports various data types like
NUMBER, VARCHAR2, DATE, etc.
 Query:

DECLARE
v_name VARCHAR2(30);
v_age NUMBER(3);
BEGIN
v_name := 'John Doe';
 v_age := 28;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
DBMS_OUTPUT.PUT_LINE('Age: ' || v_age);
END;
/

2.2 Constants

 Explanation: Constants are similar to variables but their values cannot be changed once
set.
 Query:

DECLARE
c_tax_rate CONSTANT NUMBER := 0.05;
v_income NUMBER := 10000;
v_tax NUMBER;
BEGIN
v_tax := v_income * c_tax_rate;
DBMS_OUTPUT.PUT_LINE('Tax: ' || v_tax);
END;
/

Module 3: Control Structures

3.1 Conditional Statements (IF-THEN-ELSE)

 Explanation: Control the flow of execution using conditions.

 Query:

DECLARE
v_score NUMBER := 85;
BEGIN
IF v_score >= 90 THEN
DBMS_OUTPUT.PUT_LINE('Grade: A');
ELSIF v_score >= 80 THEN
DBMS_OUTPUT.PUT_LINE('Grade: B');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade: C');
END IF;
END;
/

3.2 Loops

 Explanation: Loops allow you to execute a block of code multiple times.

 Query (Simple Loop):

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

 Query (For Loop):

BEGIN
FOR v_counter IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
END LOOP;
END;
/

Module 4: Exception Handling

4.1 Types of Exceptions

 Explanation: PL/SQL has predefined exceptions like NO_DATA_FOUND,

TOO_MANY_ROWS, etc. You can also create custom exceptions.
 Query (Handling Predefined Exception):

DECLARE
v_name VARCHAR2(20);
BEGIN
SELECT name INTO v_name FROM employees WHERE employee_id = 999;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
/

 Query (Custom Exception):

DECLARE
e_invalid_salary EXCEPTION;
v_salary NUMBER := -5000;
BEGIN
IF v_salary < 0 THEN
RAISE e_invalid_salary;
END IF;
EXCEPTION
WHEN e_invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Salary cannot be negative.');
END;
/
Module 5: Cursors
5.1 Implicit Cursors

 Explanation: Automatically created by Oracle when an SQL statement is executed.

 Query:

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

5.2 Explicit Cursors

 Explanation: Explicit cursors are defined by the programmer for queries that return more
than one row.
 Query:

DECLARE
CURSOR emp_cursor IS SELECT employee_id, name FROM employees WHERE
department_id = 10;
v_emp_id employees.employee_id%TYPE;
v_name employees.name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_id, v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_name);
END LOOP;
CLOSE emp_cursor;
END;
/

Module 6: Procedures and Functions

6.1 Creating Procedures

 Explanation: Procedures are subprograms that perform a specific action.

 Query:

CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2) IS

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
/
BEGIN
greet_user('Alice');
END;
/

6.2 Creating Functions

 Explanation: Functions are similar to procedures but they return a value.

 Query:

CREATE OR REPLACE FUNCTION calculate_bonus(p_salary IN NUMBER) RETURN NUMBER

IS
BEGIN
RETURN p_salary * 0.10;
END;
/

DECLARE
v_salary NUMBER := 50000;
v_bonus NUMBER;
BEGIN
v_bonus := calculate_bonus(v_salary);
DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/

Module 7: Packages
7.1 Creating Packages

 Explanation: A package is a collection of related procedures, functions, variables, and

other package constructs grouped together.
 Query:

CREATE OR REPLACE PACKAGE emp_pkg IS

PROCEDURE raise_salary(p_emp_id IN NUMBER, p_increase IN NUMBER);
FUNCTION get_salary(p_emp_id IN NUMBER) RETURN NUMBER;
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

PROCEDURE raise_salary(p_emp_id IN NUMBER, p_increase IN NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_increase WHERE employee_id =
p_emp_id;
END raise_salary;

FUNCTION get_salary(p_emp_id IN NUMBER) RETURN NUMBER IS

v_salary NUMBER;
BEGIN
 SELECT salary INTO v_salary FROM employees WHERE employee_id =
p_emp_id;
RETURN v_salary;
END get_salary;
END emp_pkg;
/

BEGIN
emp_pkg.raise_salary(101, 500);
DBMS_OUTPUT.PUT_LINE('Updated Salary: ' || emp_pkg.get_salary(101));
END;
/

Module 8: Triggers
8.1 Creating Triggers

 Explanation: Triggers are PL/SQL blocks that automatically execute in response to a

specific event on a table or view.
 Query:

CREATE OR REPLACE TRIGGER trg_before_insert

BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.employee_id := employees_seq.NEXTVAL;
:NEW.hire_date := SYSDATE;
END;
/

Module 9: Advanced Topics

9.1 Dynamic SQL

 Explanation: Dynamic SQL allows you to build and execute SQL statements
dynamically at runtime.
 Query:

DECLARE
v_table_name VARCHAR2(30) := 'employees';
v_sql VARCHAR2(1000);
BEGIN
v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
EXECUTE IMMEDIATE v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Number of records: ' || v_count);
END;
/
9.2 Bulk Collect and FORALL

 Explanation: Bulk Collect allows you to fetch multiple rows into collections, and
FORALL is used to perform DML operations on those collections.
 Query:

DECLARE
TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
v_emp_tab t_emp_tab;
BEGIN
SELECT * BULK COLLECT INTO v_emp_tab FROM employees WHERE department_id =
10;

FORALL i IN v_emp_tab.FIRST..v_emp_tab.LAST
UPDATE employees SET salary = salary * 1.10 WHERE employee_id =
v_emp_tab(i).employee_id;
END;
/

This course should provide a comprehensive foundation in PL/SQL. Each module builds on the
previous one, ensuring that by the end, you have a strong understanding of both basic and
advanced PL/SQL concepts.

Would you like to delve deeper into any specific topic