Oracle PL SQL Tutorial Day 1
Oracle PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension to SQL. It enables developers to write complex database interactions using control structures, loops, exception handling, and modular programming. With PL/SQL, you can perform powerful operations such as writing stored procedures, triggers, and packages that provide enhanced performance and maintainability.
In this article, we will explore the key features and functions of Oracle PL/SQL with examples that illustrate how each is used in real-world applications.
1. Block Structure
PL/SQL code is organized into blocks. Each block consists of three main parts: DECLARE, BEGIN, and EXCEPTION (optional).
Example:
DECLARE
v_employee_name VARCHAR2(50);
BEGIN
SELECT first_name INTO v_employee_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
2. Variables and Data Types
PL/SQL supports scalar types (NUMBER, VARCHAR2, DATE) and composite types (RECORD, TABLE).
Example:
DECLARE
v_salary NUMBER := 50000;
v_bonus NUMBER;
BEGIN
v_bonus := v_salary * 0.10;
DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
3. Control Structures
PL/SQL includes IF, CASE, LOOP, FOR, and WHILE constructs.
IF-ELSE Example:
DECLARE
v_grade CHAR := 'B';
BEGIN
IF v_grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF v_grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSE
DBMS_OUTPUT.PUT_LINE('Needs Improvement');
END IF;
END;
FOR Loop Example:
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Value: ' || i);
END LOOP;
END;
4. Cursors
Cursors allow row-by-row processing.
Implicit Cursor Example:
BEGIN
UPDATE employees SET salary = salary + 1000 WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
END;
Explicit Cursor Example:
DECLARE
CURSOR emp_cursor IS SELECT first_name FROM employees WHERE department_id = 10;
v_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END LOOP;
CLOSE emp_cursor;
END;
5. Exception Handling
PL/SQL handles runtime errors via EXCEPTION blocks.
Example:
BEGIN
UPDATE employees SET salary = -1000 WHERE employee_id = 101;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
6. Stored Procedures and Functions
Stored Procedure Example:
CREATE OR REPLACE PROCEDURE give_bonus(p_id NUMBER, p_bonus NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_bonus WHERE employee_id = p_id;
END;
Function Example:
CREATE OR REPLACE FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_id;
RETURN v_salary;
END;
7. Triggers
Triggers execute automatically in response to events.
Example:
CREATE OR REPLACE TRIGGER trg_audit_emp
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (log_text) VALUES ('Inserted employee: ' || :NEW.employee_id);
END;
8. Packages
Packages group related procedures and functions.
Package Specification:
CREATE OR REPLACE PACKAGE emp_pkg AS
PROCEDURE raise_salary(p_id NUMBER, p_amount NUMBER);
FUNCTION get_bonus(p_id NUMBER) RETURN NUMBER;
END;
Package Body:
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE raise_salary(p_id NUMBER, p_amount NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_id;
END;
FUNCTION get_bonus(p_id NUMBER) RETURN NUMBER IS
v_bonus NUMBER := 1000;
BEGIN
RETURN v_bonus;
END;
END;
9. Collections and Records
PL/SQL supports collections like associative arrays and records.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_numbers NumList;
BEGIN
FOR i IN 1..5 LOOP
v_numbers(i) := i * 10;
DBMS_OUTPUT.PUT_LINE('Value: ' || v_numbers(i));
END LOOP;
END;
10. Bulk Collect and FORALL
Used for bulk processing of data.
Example:
DECLARE
TYPE EmpTab IS TABLE OF employees%ROWTYPE;
v_emps EmpTab;
BEGIN
SELECT * BULK COLLECT INTO v_emps FROM employees WHERE department_id = 10;
FOR i IN v_emps.FIRST .. v_emps.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emps(i).first_name);
END LOOP;
END;
11. Autonomous Transactions
Execute independent commits inside procedures.
Example:
CREATE OR REPLACE PROCEDURE log_action(p_message VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_log (log_text) VALUES (p_message);
COMMIT;
END;
12. Advanced Features
Dynamic SQL Example:
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE temp_table(id NUMBER)';
END;
Hopefully the overview it will help to understand Oracle PL SQL. No need to worry about the codes, we will explore each topic in detail one by one with examples and practice.
At the end of this series you will be able to write complex business logic using oracle pl sql.
Conclusion
It just an overview of ORACLE PL SQL. We will explore each topic one by one in detail with practical examples.
Oracle PL/SQL provides robust procedural capabilities within the database. Features like block structure, exception handling, stored programs, packages, and performance-enhancing techniques like BULK COLLECT make it an ideal choice for enterprise-level applications. With a strong foundation in PL/SQL, developers can create scalable, modular, and high-performing database applications.
0 Comments