PL/SQL Tutorial – What is PL/SQL?
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s proprietary extension to SQL. This PL/SQL tutorial will help you understand how Oracle uses PL/SQL to combine the power of SQL with procedural logic to write complex database programs.
What is PL/SQL?
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s proprietary extension of SQL—designed specifically for Oracle databases. It blends the data manipulation power of SQL with the control structures of procedural programming languages like Ada and Pascal.
Unlike standard SQL, which is non-procedural and declarative, PL/SQL allows writing full programs using variables, control structures (like IF, FOR, WHILE), functions, procedures, and object-oriented constructs. This makes PL/SQL ideal for writing business logic inside the Oracle database.
Why "PL" in PL/SQL?
The “PL” stands for Procedural Language. PL/SQL was designed to support the creation of complex logic directly in the database layer—something SQL alone couldn’t do efficiently. Over time, Oracle enhanced PL/SQL to support object-oriented programming (OOP) features, making it a hybrid procedural + OOP language.
Key Features of PL/SQL
| Feature | Description |
|---|---|
| Procedural Logic | Supports variables, loops, conditional branching (IF, WHILE, FOR) |
| SQL Integration | Fully supports SQL statements for querying and data manipulation |
| Error Handling | Built-in EXCEPTION handling for better reliability |
| Modular Code | Allows creating procedures, functions, packages, and triggers |
| Object-Oriented | Supports object types, methods, and even limited inheritance (Oracle 8+) |
PL/SQL Examples
Basic Anonymous Block
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL!');
END;
Example Declaring Variables in PL/SQL:
DECLARE
name VARCHAR2(50) := 'amantpoint';
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || name);
END;
Control Structures (Loops & Conditions)
Example IF Statement:
DECLARE
score NUMBER := 85;
BEGIN
IF score >= 75 THEN
DBMS_OUTPUT.PUT_LINE('Passed');
ELSE
DBMS_OUTPUT.PUT_LINE('Try Again');
END IF;
END;
Example FOR Loop:
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Value: ' || i);
END LOOP;
END;
PL/SQL as Object-Oriented
Oracle now supports object types with methods:
Example:
CREATE TYPE student_t AS OBJECT (
name VARCHAR2(100),
grade NUMBER,
MEMBER PROCEDURE display_info
);
/
CREATE TYPE BODY student_t AS
MEMBER PROCEDURE display_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Name: ' || name || ', Grade: ' || grade);
END;
END;
/
DECLARE
s student_t := student_t('Ravi', 90);
BEGIN
s.display_info;
END;
/
Conclusion
PL/SQL is a powerful tool for Oracle developers to build scalable, secure, and performance-oriented applications. Whether you’re creating triggers, procedures, or OOP constructs, PL/SQL enables you to do more with Oracle databases.
