Getting Started with PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension for SQL and the Oracle relational database. It allows you to write procedural logic, such as loops, conditionals, and functions, directly into the SQL commands. This tutorial will guide you through the basics of PL/SQL with examples.

Table of Contents

1. Introduction to PL/SQL
2. PL/SQL Block Structure
3. Variables
4. Control Structures
   • IF-THEN-ELSE
   • CASE
   • LOOP
   • WHILE
5. Cursors
6. Exceptions
7. Procedures and Functions
8. Packages

1. Introduction to PL/SQL

PL/SQL is a block-structured language. A PL/SQL block consists of three sections:

• Declaration
• Execution
• Exception handling

Here is a simple PL/SQL block that displays “Hello, World!”:

DECLARE
   message VARCHAR2(100) := 'Hello, World!';
BEGIN
   DBMS_OUTPUT.PUT_LINE(message);
END;
/

2. PL/SQL Block Structure

• Declaration Section: This is where you declare variables, constants, and other program constructs.
• Execution Section: This is where the main logic of your program resides.
• Exception Handling Section: This section is used to handle errors that occur during execution.

3. Variables

Variables in PL/SQL are declared in the declaration section. They must have a data type. Here’s an example:

DECLARE
   age INTEGER := 30;
   name VARCHAR2(50) := 'John';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Name: ' || name || ', Age: ' || age);
END;
/

4. Control Structures

IF-THEN-ELSE

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

CASE

DECLARE
   day_number NUMBER := 4;
   day_name VARCHAR2(20);
BEGIN
   CASE day_number
      WHEN 1 THEN day_name := 'Sunday';
      WHEN 2 THEN day_name := 'Monday';
      WHEN 3 THEN day_name := 'Tuesday';
      WHEN 4 THEN day_name := 'Wednesday';
      WHEN 5 THEN day_name := 'Thursday';
      WHEN 6 THEN day_name := 'Friday';
      WHEN 7 THEN day_name := 'Saturday';
   END CASE;
   DBMS_OUTPUT.PUT_LINE('Day is: ' || day_name);
END;
/

LOOP

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

WHILE

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

5. Cursors

Cursors are used to retrieve data row by row from a result set. Here’s an example:

DECLARE
   cursor_name SYS_REFCURSOR;
   emp_record employees%ROWTYPE;
BEGIN
   OPEN cursor_name FOR
      SELECT * FROM employees;
   LOOP
      FETCH cursor_name INTO emp_record;
      EXIT WHEN cursor_name%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.first_name || ' ' || emp_record.last_name);
   END LOOP;
   CLOSE cursor_name;
END;
/

6. Exceptions

Exceptions are used to handle errors that occur during execution. Here’s an example:

DECLARE
   result NUMBER;
BEGIN
   result := 10 / 0; -- This will raise a division by zero error
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;
/

7. Procedures and Functions

Procedures and functions are reusable program units that can be called from other parts of your code.

Procedure Example

CREATE OR REPLACE PROCEDURE greet IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, World!');
END greet;
/

-- To execute the procedure
BEGIN
   greet;
END;
/

Function Example

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER) RETURN NUMBER IS
   result NUMBER;
BEGIN
   result := a + b;
   RETURN result;
END add_numbers;
/

-- To execute the function
DECLARE
   sum_result NUMBER;
BEGIN
   sum_result := add_numbers(10, 20);
   DBMS_OUTPUT.PUT_LINE('Sum: ' || sum_result);
END;
/

8. Packages

Packages are groups of related procedures, functions, variables, and cursors.

Package Example

CREATE OR REPLACE PACKAGE my_package IS
   PROCEDURE proc1;
   FUNCTION func1 RETURN NUMBER;
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package IS
   PROCEDURE proc1 IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Procedure 1');
   END proc1;

   FUNCTION func1 RETURN NUMBER IS
   BEGIN
      RETURN 100;
   END func1;
END my_package;
/

-- To use the package
BEGIN
   my_package.proc1;
   DBMS_OUTPUT.PUT_LINE('Function Result: ' || my_package.func1);
END;
/

This tutorial covers the basics of PL/SQL, including variables, control structures, cursors, exceptions, procedures, functions, and packages. PL/SQL is a powerful language that allows you to create robust and efficient database applications in Oracle.