PL/SQL Tutorial: Anonymous Blocks, Procedures, Functions, and Packages

📝 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;

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! 🚀

Know Oracle Fusion Apps

Saturday, July 23, 2022