Fundamentals of PL/SQL Programming Language

=====================================================

Introduction to PL/SQL

---

PL/SQL (Procedural Language/Structured Query Language) is a powerful programming language used for developing database applications. It is an extension of SQL, the standard language for accessing and managing data in relational databases.

History of PL/SQL

Oracle Corporation developed PL/SQL as a means to create complex business logic within the Oracle Relational Database Management System (RDBMS). The first version of PL/SQL was released in 1991. Since then, it has undergone several revisions and enhancements, becoming one of the most widely used programming languages for database applications.

Basic Syntax and Structure

---

Variables

PL/SQL uses variables to store data temporarily during execution. There are two types of variables:

Variable Type	Description
DECLARE variable	Declares a variable with an initial value.
:= assignment operator	Assigns a new value to the declared variable.

Example:

DECLARE 
salary NUMBER := 5000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Your salary is: ' || salary);
END;

Data Types

PL/SQL supports various data types, including:

• CHAR: Fixed-length character string.
• VARCHAR2: Variable-length character string.
• NUMBER: Numeric value with precision and scale.
• DATE: Date and time value.

Example:

DECLARE 
name CHAR(20) := 'John Doe';
salary NUMBER := 5000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Name: ' || name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || salary);
END;

SQL Statements

PL/SQL allows you to execute SQL statements within your program. You can use the EXECUTE IMMEDIATE statement or embed SQL code directly in the PL/SQL block.

Example:

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE employees (id NUMBER, name VARCHAR2(20))';
END;

Control Structures

---

Conditional Statements

PL/SQL provides several conditional statements for making decisions:

• IF-THEN-ELSE: Executes a statement if the condition is true.
• CASE: Evaluates multiple conditions and executes a corresponding action.

Example:

DECLARE 
salary NUMBER := 5000;
BEGIN
IF salary > 10000 THEN
DBMS_OUTPUT.PUT_LINE('You are eligible for bonus.');
ELSE
DBMS_OUTPUT.PUT_LINE('You are not eligible for bonus.');
END IF;
END;

Loops

PL/SQL supports two types of loops:

• FOR: Iterates over a cursor or an array.
• WHILE: Executes a statement while the condition is true.

Example:

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

Exception Handling

---

Raising Exceptions

You can raise exceptions using the RAISE statement:

DECLARE 
salary NUMBER := NULL;
BEGIN
IF salary IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be null');
END IF;
END;

Handling Exceptions

PL/SQL provides a built-in mechanism for handling exceptions using the EXCEPTION block:

DECLARE 
salary NUMBER := NULL;
BEGIN
IF salary IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be null');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

Conclusion

---

PL/SQL is a powerful programming language for developing database applications. Understanding its fundamentals, including syntax, data types, control structures, and exception handling, is essential for building robust and efficient database systems.

In the next article, we will explore advanced topics in PL/SQL, such as functions, procedures, packages, and cursors.