📝 PL/SQL Tutorial: Anonymous Blocks, Procedures, Functions, and Packages with Simple Examples

PL/SQL is a powerful block-structured language used in Oracle databases for procedural programming. It allows developers to write modular, reusable code.

---

📚 1. PL/SQL Block Structure Overview

PL/SQL code can be categorized as:

• Anonymous Blocks: Unnamed, not stored in the database, used for quick scripts
• Named Blocks: Stored in the database, reusable
  • Functions
  • Procedures
  • Packages

Example to get current year:

SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;

Anonymous blocks are quick, one-time use code blocks. Named blocks like functions, procedures, and packages are stored in the database and can be reused multiple times, making your code cleaner and more maintainable.

---

🔹 2. Functions

What is a Function?

A function returns a value and can be used in SQL statements or PL/SQL code.

• Oracle Provided Functions: TO_CHAR(), TO_DATE(), SUM(), AVG(), etc.
• User Defined Functions: Custom functions for specific logic.

Functions take input parameters, perform calculations or operations, and return a single value. You can use built-in Oracle functions or create your own to encapsulate logic you want to reuse.

Syntax to Create a Function

CREATE [OR REPLACE] FUNCTION function_name [(parameters)] RETURN return_datatype IS | AS
  [declaration_section]
BEGIN
  executable_section
  [EXCEPTION exception_section]
END [function_name];

Example: Calculate Area of Circle

CREATE OR REPLACE FUNCTION AREA_CIRC_FUNC (V_RADIUS INTEGER) RETURN INTEGER IS
  V_AREA NUMBER;
BEGIN
  V_AREA := 3.14 * (V_RADIUS * V_RADIUS);
  RETURN V_AREA;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('SOME ERROR: ' || SQLERRM);
    RETURN NULL;
END;

How to Run the Function?

1. From PL/SQL Block:

DECLARE
  V_AREA NUMBER;
  V_RADIUS INTEGER := &a;
BEGIN
  V_AREA := AREA_CIRC_FUNC(V_RADIUS);
  DBMS_OUTPUT.PUT_LINE('AREA OF CIRCLE IS: ' || V_AREA);
END;

2. From SQL Query:

SELECT AREA_CIRC_FUNC(100) FROM DUAL;

You can call functions directly within PL/SQL blocks or SQL queries. Functions simplify code by encapsulating reusable logic like calculating the area of a circle.

---

🔹 3. Procedures

What is a Procedure?

A procedure is a named PL/SQL block that performs an action but does not return a value directly.

Procedures are used when you want to perform tasks such as inserting data, printing messages, or any operation that does not need to return a value.

Basic Procedure Example

CREATE OR REPLACE PROCEDURE PRINT_MSG_PRC IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END PRINT_MSG_PRC;

Execute Procedure

EXEC PRINT_MSG_PRC;

or

BEGIN
  PRINT_MSG_PRC;
END;

---

Procedure with Parameters

IN Parameter (default mode)

CREATE OR REPLACE PROCEDURE ADD_VAL_PRC(X IN NUMBER) IS
  V NUMBER(5);
BEGIN
  V := X + 5000;
  DBMS_OUTPUT.PUT_LINE(V);
END;

Run:

EXEC ADD_VAL_PRC(1000);

Procedure with Multiple IN Parameters

CREATE OR REPLACE PROCEDURE PRINT_EMP_PRC(
  v_empno NUMBER,
  v_sal   NUMBER
) IS
  vname VARCHAR2(10);
BEGIN
  SELECT ename INTO vname FROM empl WHERE empno = v_empno AND sal = v_sal;
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || vname);
END;

Run:

EXEC PRINT_EMP_PRC(7839, 5000);

Procedures can accept parameters to make them flexible. IN parameters allow passing values to the procedure. You can create procedures to perform various tasks using the input parameters.

---

OUT Parameter Example

CREATE OR REPLACE PROCEDURE PRINT_ENAME_PRC(
  vin_empno IN NUMBER,
  vout_ename OUT VARCHAR2
) IS
BEGIN
  SELECT ename INTO vout_ename FROM empl WHERE empno = vin_empno;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('SOME ERROR: ' || SQLERRM);
END;

Call with anonymous block:

DECLARE
  V_EMPNO NUMBER := &A;
  V_ENAME VARCHAR2(100);
BEGIN
  PRINT_ENAME_PRC(V_EMPNO, V_ENAME);
  DBMS_OUTPUT.PUT_LINE('Value from procedure: ' || V_ENAME);
END;

OUT parameters allow a procedure to send data back to the caller. This is useful when you want to retrieve values like employee names or calculations.

---

IN OUT Parameter Example

CREATE OR REPLACE PROCEDURE DER_SAL_PRC(x IN OUT NUMBER) IS
BEGIN
  SELECT sal INTO x FROM empl WHERE empno = x;
END DER_SAL_PRC;

Call:

DECLARE
  V_EMPNO NUMBER := &A;
BEGIN
  DER_SAL_PRC(V_EMPNO);
  DBMS_OUTPUT.PUT_LINE('Salary of employee is: ' || V_EMPNO);
END;

IN OUT parameters act as both input and output. You pass a value in, and the procedure modifies or returns a related value.

---

🔹 4. Packages

What is a Package?

A package groups related procedures, functions, variables, and other elements together.

Packages help organize your code logically and improve maintainability by grouping related subprograms together. They have two parts: specification (interface) and body (implementation).

Package Specification Syntax

CREATE [OR REPLACE] PACKAGE package_name IS
  -- Public declarations
  [PROCEDURE procedure_name (parameters);]
  [FUNCTION function_name (parameters) RETURN datatype;]
END package_name;
/

Package Body Syntax

CREATE [OR REPLACE] PACKAGE BODY package_name IS
  -- Implementation of procedures and functions
END package_name;
/

Example: Math Package

Package Spec

CREATE OR REPLACE PACKAGE MATH_PKG IS
  FUNCTION AREA_CIRC_FUN (VIN NUMBER) RETURN NUMBER;
  PROCEDURE AREA_SQR_PRC (VIN NUMBER);
END MATH_PKG;
/

Package Body

CREATE OR REPLACE PACKAGE BODY MATH_PKG IS

  FUNCTION AREA_CIRC_FUN (VIN NUMBER) RETURN NUMBER IS
    V_AREA NUMBER;
  BEGIN
    V_AREA := 3.14 * (VIN * VIN);
    RETURN V_AREA;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR in AREA_CIRC_FUN: ' || SQLERRM);
      RETURN 0;
  END;

  PROCEDURE AREA_SQR_PRC (VIN NUMBER) IS
    V_AREA NUMBER;
  BEGIN
    V_AREA := VIN * VIN;
    DBMS_OUTPUT.PUT_LINE('AREA OF SQUARE IS: ' || V_AREA);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR in AREA_SQR_PRC: ' || SQLERRM);
  END;

END MATH_PKG;
/

Using the Package

DECLARE
  V_IN_RAD NUMBER := &A;
  V_IN_SIDE NUMBER := &B;
  V_AREA_CIRCLE NUMBER;
BEGIN
  -- Call package function
  V_AREA_CIRCLE := MATH_PKG.AREA_CIRC_FUN(V_IN_RAD);
  DBMS_OUTPUT.PUT_LINE('AREA OF CIRCLE IS: ' || V_AREA_CIRCLE);

  -- Call package procedure
  MATH_PKG.AREA_SQR_PRC(V_IN_SIDE);
END;
/

---

Summary

Concept	Description	Example Use Case
Anonymous Block	Temporary unnamed PL/SQL block	Quick scripts or tests
Function	Named block returning a value	Calculating areas, business logic
Procedure	Named block performing actions, no return	Print messages, complex logic
Package	Group of related functions and procedures	Organizing code by functionality

Happy Coding! 🚀