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