Oracle PL/SQL supports a rich set of data types, defining the kind of values a variable or constant can hold and the operations that can be performed on them. Choosing the correct data type is essential for efficient memory usage, data integrity, and avoiding runtime errors in your PL/SQL programs.
Scalar Data Types (NUMBER, VARCHAR2, DATE, BOOLEAN, etc.)
Scalar data types hold a single, indivisible value. They are the most commonly used data types in Oracle PL/SQL.
NUMBER
The NUMBER data type is used to store numeric values. It can store integers, fixed-point numbers, and floating-point numbers. It's highly versatile and widely used in Oracle databases.
Example 1: Declaring a Simple Integer
DECLARE
-- Declaring an integer variable
-- NUMBER without precision/scale implies maximum precision.
order_quantity NUMBER := 150;
BEGIN
DBMS_OUTPUT.PUT_LINE('Order Quantity: ' || order_quantity);
END;
/
Explanation This example demonstrates a basic NUMBER declaration for an integer. When no precision or scale is specified, NUMBER can store numbers with maximum precision, making it flexible for various numeric values.
Example 2: Declaring a Fixed-Point Number
DECLARE
-- Declaring a fixed-point number with precision and scale
-- total 5 digits, 2 digits after the decimal point
product_price NUMBER(5, 2) := 99.99;
BEGIN
DBMS_OUTPUT.PUT_LINE('Product Price: ' || product_price);
-- What happens if we assign a value with more decimal places?
-- product_price := 123.456; -- Oracle will round this to 123.46
-- DBMS_OUTPUT.PUT_LINE('Rounded Product Price: ' || product_price);
END;
/
Explanation This example shows NUMBER with specified precision and scale (NUMBER(5, 2)). This is ideal for financial or measurement data where a fixed number of decimal places is required. Oracle will round values if they exceed the specified scale.
Example 3: Declaring a Floating-Point Number (BINARY_FLOAT, BINARY_DOUBLE)
DECLARE
-- Declaring a binary float for single-precision floating-point numbers
temperature BINARY_FLOAT := 25.75;
-- Declaring a binary double for double-precision floating-point numbers
pi_val BINARY_DOUBLE := 3.141592653589793;
BEGIN
DBMS_OUTPUT.PUT_LINE('Temperature: ' || temperature);
DBMS_OUTPUT.PUT_LINE('PI Value: ' || pi_val);
END;
/
Explanation This example introduces BINARY_FLOAT and BINARY_DOUBLE for efficient storage and computation of floating-point numbers, particularly useful for scientific and engineering applications where precision is critical.
Example 4: Using NUMBER in Calculations and Rounding (Advanced)
DECLARE
-- Declaring variables for calculations
net_amount NUMBER(10, 2) := 1250.75;
tax_rate NUMBER(3, 2) := 0.08; -- 8%
gross_amount NUMBER(10, 2);
rounded_amount NUMBER(10, 2);
BEGIN
-- Calculating gross amount
gross_amount := net_amount * (1 + tax_rate);
DBMS_OUTPUT.PUT_LINE('Net Amount: ' || net_amount);
DBMS_OUTPUT.PUT_LINE('Gross Amount (unrounded): ' || gross_amount);
-- Rounding the gross amount to two decimal places
rounded_amount := ROUND(gross_amount, 2);
DBMS_OUTPUT.PUT_LINE('Gross Amount (rounded): ' || rounded_amount);
-- More advanced number functions
DBMS_OUTPUT.PUT_LINE('CEIL(gross_amount): ' || CEIL(gross_amount)); -- Ceiling
DBMS_OUTPUT.PUT_LINE('FLOOR(gross_amount): ' || FLOOR(gross_amount)); -- Floor
END;
/
Explanation This example demonstrates NUMBER in practical calculations and introduces built-in SQL functions like ROUND, CEIL, and FLOOR for numeric manipulation, essential for accurate financial and statistical reporting in PL/SQL.
Example 5: NUMBER with Scale for Currency Conversion (Advanced)
DECLARE
-- Exchange rate for USD to EUR, allowing for high precision
usd_to_eur_rate NUMBER(10, 8) := 0.93256789;
-- Amount in USD
amount_usd NUMBER(12, 2) := 1000.00;
-- Converted amount in EUR
amount_eur NUMBER(12, 2);
BEGIN
-- Performing currency conversion
amount_eur := amount_usd * usd_to_eur_rate;
DBMS_OUTPUT.PUT_LINE('Amount in USD: ' || amount_usd);
DBMS_OUTPUT.PUT_LINE('USD to EUR Rate: ' || usd_to_eur_rate);
DBMS_OUTPUT.PUT_LINE('Amount in EUR: ' || ROUND(amount_eur, 2)); -- Rounding to typical currency precision
-- Demonstrate storing large numbers
DECLARE
large_population NUMBER(15) := 8100000000;
BEGIN
DBMS_OUTPUT.PUT_LINE('World Population (approx): ' || large_population);
END;
END;
/
Explanation This advanced example highlights the flexibility of the NUMBER data type for handling currency exchange rates with high precision and for storing very large integer values. It reinforces the importance of choosing appropriate precision and scale for specific data requirements in Oracle PL/SQL.
VARCHAR2
The VARCHAR2 data type is used to store variable-length character strings. It's the most common data type for textual information in Oracle PL/SQL.
Example 1: Declaring a Simple VARCHAR2
DECLARE
-- Declaring a VARCHAR2 variable for a city name
city_name VARCHAR2(100) := 'Seattle';
BEGIN
DBMS_OUTPUT.PUT_LINE('City: ' || city_name);
END;
/
Explanation This basic example demonstrates the declaration of a VARCHAR2 variable with a specified maximum length of 100 characters, ideal for storing variable-length textual data like names or addresses.
Example 2: VARCHAR2 with Character Functions
DECLARE
-- Declaring a VARCHAR2 variable for a customer name
customer_name VARCHAR2(50) := 'Alice Wonderland';
-- Variable to store the uppercase version of the name
upper_name VARCHAR2(50);
-- Variable to store the substring
first_word VARCHAR2(20);
BEGIN
-- Converting to uppercase
upper_name := UPPER(customer_name);
DBMS_OUTPUT.PUT_LINE('Uppercase Name: ' || upper_name);
-- Extracting a substring
first_word := SUBSTR(customer_name, 1, INSTR(customer_name, ' ') - 1);
DBMS_OUTPUT.PUT_LINE('First Word: ' || first_word);
END;
/
Explanation This example showcases common string manipulation functions like UPPER and SUBSTR with VARCHAR2, which are frequently used for data cleaning, formatting, and extraction in PL/SQL.
Example 3: Handling NULL and Empty Strings in VARCHAR2
DECLARE
-- Declaring a VARCHAR2 variable with a NULL value
middle_initial VARCHAR2(1);
-- Declaring a VARCHAR2 variable as an empty string (treated as NULL in Oracle)
empty_string VARCHAR2(10) := '';
BEGIN
-- NULL check
IF middle_initial IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Middle Initial is NULL.');
END IF;
-- Empty string check (Oracle treats empty strings as NULL for VARCHAR2)
IF empty_string IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Empty string is treated as NULL in Oracle VARCHAR2.');
END IF;
-- Assigning a value
middle_initial := 'J';
IF middle_initial IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Middle Initial is now: ' || middle_initial);
END IF;
END;
/
Explanation This example demonstrates Oracle's handling of NULL and empty strings for VARCHAR2. In Oracle PL/SQL, an empty string ('') is treated as NULL for VARCHAR2 and CHAR data types, a crucial detail for data validation and conditional logic.
Example 4: VARCHAR2 for Dynamic SQL (Advanced)
DECLARE
-- Variable to store a table name
table_name VARCHAR2(30) := 'EMPLOYEES';
-- Variable to store a column name
column_to_check VARCHAR2(30) := 'JOB_ID';
-- Variable to store the dynamic query string
dynamic_query VARCHAR2(200);
-- Variable to store the result of the query
distinct_job_id VARCHAR2(10);
BEGIN
-- Constructing a dynamic SQL query
dynamic_query := 'SELECT ' || column_to_check || ' FROM ' || table_name || ' WHERE ROWNUM = 1';
-- Executing the dynamic query
EXECUTE IMMEDIATE dynamic_query INTO distinct_job_id;
DBMS_OUTPUT.PUT_LINE('First Job ID from ' || table_name || ': ' || distinct_job_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error executing dynamic query: ' || SQLERRM);
END;
/
Explanation This advanced example shows VARCHAR2 being used to construct dynamic SQL statements. This is a powerful technique for building flexible and adaptable PL/SQL procedures, allowing you to modify SQL commands at runtime.
Example 5: VARCHAR2 for Regular Expressions (Advanced)
DECLARE
-- String containing a sample email address
email_address VARCHAR2(100) := 'user.name@example.com';
-- Regular expression pattern to validate an email format
email_pattern CONSTANT VARCHAR2(100) := '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$';
-- Variable to hold the extracted domain
domain_name VARCHAR2(50);
BEGIN
-- Using REGEXP_LIKE to validate email format
IF REGEXP_LIKE(email_address, email_pattern) THEN
DBMS_OUTPUT.PUT_LINE('Email address is valid: ' || email_address);
ELSE
DBMS_OUTPUT.PUT_LINE('Email address is invalid: ' || email_address);
END IF;
-- Using REGEXP_SUBSTR to extract the domain name
domain_name := REGEXP_SUBSTR(email_address, '@([a-zA-Z0-9.-]+)\.', 1, 1, NULL, 1);
DBMS_OUTPUT.PUT_LINE('Domain Name: ' || domain_name);
END;
/
Explanation This advanced example demonstrates the use of VARCHAR2 in conjunction with Oracle's powerful regular expression functions (REGEXP_LIKE, REGEXP_SUBSTR). This is essential for advanced string parsing, validation, and data extraction from complex textual data.
DATE
The DATE data type stores date and time information, including year, month, day, hour, minute, and second. It's fundamental for handling temporal data in Oracle PL/SQL.
Example 1: Declaring a Simple DATE Variable and SYSDATE
DECLARE
-- Declaring a date variable
current_date DATE;
BEGIN
-- Assigning the current system date and time
current_date := SYSDATE;
DBMS_OUTPUT.PUT_LINE('Current Date and Time: ' || TO_CHAR(current_date, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
Explanation This basic example shows how to declare a DATE variable and assign the current system date and time using SYSDATE. It also uses TO_CHAR for formatted date output, which is crucial for displaying dates in a human-readable format.
Example 2: DATE Arithmetic (Adding/Subtracting Days)
DECLARE
-- Declaring a start date
start_date DATE := TO_DATE('2025-01-01', 'YYYY-MM-DD');
-- Variable to store a date after adding days
future_date DATE;
-- Variable to store a date after subtracting days
past_date DATE;
BEGIN
-- Adding 10 days to the start date
future_date := start_date + 10;
DBMS_OUTPUT.PUT_LINE('Start Date: ' || TO_CHAR(start_date, 'YYYY-MM-DD'));
DBMS_OUTPUT.PUT_LINE('Date 10 days later: ' || TO_CHAR(future_date, 'YYYY-MM-DD'));
-- Subtracting 5 days from the start date
past_date := start_date - 5;
DBMS_OUTPUT.PUT_LINE('Date 5 days earlier: ' || TO_CHAR(past_date, 'YYYY-MM-DD'));
END;
/
Explanation This example demonstrates basic date arithmetic with the DATE data type, showing how to add and subtract days to manipulate dates, a common requirement in scheduling and reporting applications.
Example 3: DATE Functions (MONTHS_BETWEEN, ADD_MONTHS)
DECLARE
-- Declaring two date variables
hire_date DATE := TO_DATE('2020-03-15', 'YYYY-MM-DD');
current_time DATE := SYSDATE;
-- Variable to store the number of months between dates
months_diff NUMBER;
-- Variable to store a date after adding months
future_month_date DATE;
BEGIN
-- Calculating the number of months between two dates
months_diff := MONTHS_BETWEEN(current_time, hire_date);
DBMS_OUTPUT.PUT_LINE('Months between hire date and now: ' || ROUND(months_diff, 2));
-- Adding 6 months to a date
future_month_date := ADD_MONTHS(hire_date, 6);
DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(hire_date, 'YYYY-MM-DD'));
DBMS_OUTPUT.PUT_LINE('Date after 6 months: ' || TO_CHAR(future_month_date, 'YYYY-MM-DD'));
END;
/
Explanation This example introduces MONTHS_BETWEEN and ADD_MONTHS, powerful functions for performing more complex date manipulations, such as calculating age in months or determining future dates.
Example 4: Handling Time Zones with DATE (Advanced)
DECLARE
-- Declaring a DATE variable
event_time DATE := TO_DATE('2025-06-14 10:00:00', 'YYYY-MM-DD HH24:MI:SS');
BEGIN
-- DATE data type does not store time zone information directly.
-- For time zone handling, consider TIMESTAMP WITH TIME ZONE.
DBMS_OUTPUT.PUT_LINE('Event Time (Local): ' || TO_CHAR(event_time, 'YYYY-MM-DD HH24:MI:SS'));
-- To simulate time zone awareness with DATE, you'd convert to UTC or a specific time zone
-- and back, typically involving database session settings or explicit conversions.
-- Example: Converting a local time to a UTC time (simplistic, assumes session timezone)
DBMS_OUTPUT.PUT_LINE('Event Time in UTC (estimated): ' || TO_CHAR(NEW_TIME(event_time, 'PDT', 'GMT'), 'YYYY-MM-DD HH24:MI:SS'));
END;
/
Explanation This example touches upon the limitations of DATE concerning time zones and suggests using TIMESTAMP WITH TIME ZONE for robust time zone handling, an important consideration for global applications. It also briefly shows NEW_TIME for basic time zone conversions.
Example 5: Working with Date Literals and Comparisons (Advanced)
DECLARE
-- A date literal for a project deadline
project_deadline DATE := '2025-12-31';
-- Current date for comparison
today_date DATE := TRUNC(SYSDATE); -- TRUNC removes the time component
BEGIN
DBMS_OUTPUT.PUT_LINE('Project Deadline: ' || TO_CHAR(project_deadline, 'YYYY-MM-DD'));
DBMS_OUTPUT.PUT_LINE('Today''s Date: ' || TO_CHAR(today_date, 'YYYY-MM-DD'));
IF today_date > project_deadline THEN
DBMS_OUTPUT.PUT_LINE('Project is overdue!');
ELSIF today_date = project_deadline THEN
DBMS_OUTPUT.PUT_LINE('Project deadline is today!');
ELSE
DBMS_OUTPUT.PUT_LINE('Project is still on track.');
END IF;
-- Calculate days remaining
DBMS_OUTPUT.PUT_LINE('Days remaining to deadline: ' || (project_deadline - today_date));
END;
/
Explanation This advanced example demonstrates using date literals, comparing DATE values, and calculating the difference between two dates (which results in the number of days). This is fundamental for project management, scheduling, and general temporal comparisons in PL/SQL.
BOOLEAN
The BOOLEAN data type stores logical values: TRUE, FALSE, or NULL. It's primarily used for conditional logic and flag variables in PL/SQL.
Example 1: Declaring a Simple BOOLEAN
DECLARE
-- Declaring a boolean variable for a flag
is_processed BOOLEAN := FALSE;
BEGIN
-- Conditional check
IF NOT is_processed THEN
DBMS_OUTPUT.PUT_LINE('Data not yet processed.');
is_processed := TRUE; -- Change the value
END IF;
IF is_processed THEN
DBMS_OUTPUT.PUT_LINE('Data has been processed.');
END IF;
END;
/
Explanation This basic example illustrates the declaration and simple usage of a BOOLEAN variable, demonstrating its role in controlling program flow through IF statements. This is fundamental for logical operations in PL/SQL.
Example 2: BOOLEAN in Logical Operations (AND, OR, NOT)
DECLARE
-- Boolean flags for user permissions
can_read BOOLEAN := TRUE;
can_write BOOLEAN := FALSE;
can_delete BOOLEAN := TRUE;
BEGIN
-- Using AND operator
IF can_read AND can_write THEN
DBMS_OUTPUT.PUT_LINE('User has read and write access.');
ELSE
DBMS_OUTPUT.PUT_LINE('User does not have both read and write access.');
END IF;
-- Using OR operator
IF can_read OR can_delete THEN
DBMS_OUTPUT.PUT_LINE('User has either read or delete access (or both).');
END IF;
-- Using NOT operator
IF NOT can_write THEN
DBMS_OUTPUT.PUT_LINE('User cannot write.');
END IF;
END;
/
Explanation This example demonstrates BOOLEAN variables in conjunction with logical operators (AND, OR, NOT), which are essential for building complex conditional expressions and controlling program flow in PL/SQL.
Example 3: Returning BOOLEAN from a Function (Advanced)
CREATE OR REPLACE FUNCTION is_valid_email (p_email_address IN VARCHAR2)
RETURN BOOLEAN
IS
-- Simple pattern for demonstration
v_pattern CONSTANT VARCHAR2(100) := '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$';
BEGIN
-- Return TRUE if the email matches the pattern, otherwise FALSE
RETURN REGEXP_LIKE(p_email_address, v_pattern);
EXCEPTION
WHEN OTHERS THEN
-- Handle potential errors, though REGEXP_LIKE usually safe
RETURN FALSE;
END;
/
DECLARE
email1 VARCHAR2(100) := 'test@example.com';
email2 VARCHAR2(100) := 'invalid-email';
BEGIN
IF is_valid_email(email1) THEN
DBMS_OUTPUT.PUT_LINE('"' || email1 || '" is a valid email address.');
ELSE
DBMS_OUTPUT.PUT_LINE('"' || email1 || '" is NOT a valid email address.');
END IF;
IF is_valid_email(email2) THEN
DBMS_OUTPUT.PUT_LINE('"' || email2 || '" is a valid email address.');
ELSE
DBMS_OUTPUT.PUT_LINE('"' || email2 || '" is NOT a valid email address.');
END IF;
END;
/
Explanation This advanced example demonstrates returning a BOOLEAN value from a PL/SQL function. This is a common pattern for creating reusable validation logic or flag-checking functions, improving modularity in your Oracle PL/SQL applications.
Example 4: BOOLEAN for Loop Control (Advanced)
DECLARE
-- Boolean flag to control loop execution
stop_loop BOOLEAN := FALSE;
counter NUMBER := 0;
BEGIN
-- Loop until stop_loop becomes TRUE
WHILE NOT stop_loop LOOP
counter := counter + 1;
DBMS_OUTPUT.PUT_LINE('Loop iteration: ' || counter);
IF counter >= 5 THEN
stop_loop := TRUE; -- Set flag to exit loop
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Loop finished.');
END;
/
Explanation This example illustrates using a BOOLEAN variable to control the execution of a WHILE loop. This pattern is often used when the exit condition of a loop depends on a complex logical state.
Example 5: BOOLEAN for Feature Toggling (Advanced)
DECLARE
-- A boolean constant to control a feature
FEATURE_A_ENABLED CONSTANT BOOLEAN := TRUE;
-- Another feature toggle
FEATURE_B_ENABLED CONSTANT BOOLEAN := FALSE;
BEGIN
IF FEATURE_A_ENABLED THEN
DBMS_OUTPUT.PUT_LINE('Feature A is enabled. Executing Feature A logic...');
-- Call a procedure for Feature A
-- process_feature_a();
ELSE
DBMS_OUTPUT.PUT_LINE('Feature A is disabled.');
END IF;
IF FEATURE_B_ENABLED THEN
DBMS_OUTPUT.PUT_LINE('Feature B is enabled. Executing Feature B logic...');
-- process_feature_b();
ELSE
DBMS_OUTPUT.PUT_LINE('Feature B is disabled.');
END IF;
END;
/
Explanation This advanced example demonstrates using BOOLEAN constants for "feature toggling," a powerful technique in software development to enable or disable functionalities without code redeployment, providing flexibility for A/B testing or staged rollouts.
Declaring Variables and Constants
Declaring variables and constants in Oracle PL/SQL involves specifying their name, data type, and optionally an initial value. Proper declaration ensures that your program allocates the necessary memory and correctly interprets the values stored.
Example 1: Basic Variable Declaration
DECLARE
-- Declare a simple integer variable
v_count NUMBER;
-- Declare a string variable
v_message VARCHAR2(255);
BEGIN
-- Assign values to the declared variables
v_count := 10;
v_message := 'Hello, PL/SQL World!';
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
DBMS_OUTPUT.PUT_LINE('Message: ' || v_message);
END;
/
Explanation This example demonstrates the most straightforward way to declare variables in the DECLARE section of a PL/SQL block. It covers basic NUMBER and VARCHAR2 declarations, which are fundamental for storing data.
Example 2: Declaring Variables with Initial Values
DECLARE
-- Declare and initialize a numeric variable
v_total_sum NUMBER := 0;
-- Declare and initialize a boolean flag
v_is_valid BOOLEAN := FALSE;
-- Declare and initialize a date variable to current date
v_start_date DATE := SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Initial Sum: ' || v_total_sum);
DBMS_OUTPUT.PUT_LINE('Is Valid: ' || CASE WHEN v_is_valid THEN 'TRUE' ELSE 'FALSE' END);
DBMS_OUTPUT.PUT_LINE('Start Date: ' || TO_CHAR(v_start_date, 'YYYY-MM-DD'));
END;
/
Explanation This example shows how to declare variables and assign them an initial value directly at the time of declaration. This is a good practice for ensuring variables have a known state from the start, improving code reliability.
Example 3: Declaring Constants
DECLARE
-- Declare a constant for the maximum length of a name
MAX_NAME_LENGTH CONSTANT NUMBER := 50;
-- Declare a constant for a fixed discount rate
DISCOUNT_RATE CONSTANT NUMBER(3, 2) := 0.15; -- 15%
-- Declare a constant for the company name
COMPANY_NAME CONSTANT VARCHAR2(100) := 'Acme Corporation';
BEGIN
DBMS_OUTPUT.PUT_LINE('Max Name Length: ' || MAX_NAME_LENGTH);
DBMS_OUTPUT.PUT_LINE('Discount Rate: ' || DISCOUNT_RATE * 100 || '%');
DBMS_OUTPUT.PUT_LINE('Company: ' || COMPANY_NAME);
-- Attempting to modify a constant will raise a PLS-00363 error.
-- MAX_NAME_LENGTH := 60;
END;
/
Explanation This example focuses on declaring constants using the CONSTANT keyword. It reinforces that constant values are immutable after declaration, making them ideal for fixed application parameters.
Example 4: Declaring Variables in Subprograms (Advanced)
DECLARE
-- Global variable for the block
global_counter NUMBER := 0;
-- Procedure to increment a counter
PROCEDURE increment_counter (p_step IN NUMBER) IS
-- Local variable to the procedure
procedure_local_var NUMBER := 100;
BEGIN
global_counter := global_counter + p_step;
DBMS_OUTPUT.PUT_LINE('Inside procedure - Global Counter: ' || global_counter);
DBMS_OUTPUT.PUT_LINE('Inside procedure - Local Var: ' || procedure_local_var);
END increment_counter;
-- Function to double a number
FUNCTION double_number (p_input IN NUMBER) RETURN NUMBER IS
-- Local constant to the function
MULTIPLIER CONSTANT NUMBER := 2;
BEGIN
RETURN p_input * MULTIPLIER;
END double_number;
BEGIN
DBMS_OUTPUT.PUT_LINE('Initial Global Counter: ' || global_counter);
increment_counter(5);
increment_counter(3);
DBMS_OUTPUT.PUT_LINE('Final Global Counter: ' || global_counter);
DBMS_OUTPUT.PUT_LINE('Doubled 7: ' || double_number(7));
END;
/
Explanation This advanced example demonstrates declaring variables and constants within PL/SQL subprograms (procedures and functions). It highlights the concept of variable scope, where variables declared inside a subprogram are local to that subprogram.
Example 5: Declaring Collection Variables (Advanced)
DECLARE
-- Declaring a PL/SQL table (associative array) of numbers
TYPE number_list_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
my_numbers number_list_type;
-- Declaring a nested table of VARCHAR2
TYPE string_array_type IS TABLE OF VARCHAR2(50);
my_strings string_array_type := string_array_type(); -- Initialize for nested table
-- Declaring a record type
TYPE employee_rec_type IS RECORD (
employee_id NUMBER,
first_name VARCHAR2(50),
salary NUMBER(8,2)
);
my_employee employee_rec_type;
BEGIN
-- Populate the PL/SQL table
my_numbers(1) := 10;
my_numbers(2) := 20;
my_numbers(10) := 100; -- Sparse array
DBMS_OUTPUT.PUT_LINE('First number: ' || my_numbers(1));
DBMS_OUTPUT.PUT_LINE('Tenth number: ' || my_numbers(10));
-- Populate the nested table
my_strings.EXTEND;
my_strings(my_strings.LAST) := 'Apple';
my_strings.EXTEND;
my_strings(my_strings.LAST) := 'Banana';
DBMS_OUTPUT.PUT_LINE('First string: ' || my_strings(1));
-- Populate the record
my_employee.employee_id := 101;
my_employee.first_name := 'Jane';
my_employee.salary := 75000.50;
DBMS_OUTPUT.PUT_LINE('Employee: ' || my_employee.first_name || ' (ID: ' || my_employee.employee_id || ') - Salary: ' || my_employee.salary);
END;
/
Explanation This advanced example delves into declaring more complex data structures: PL/SQL tables (associative arrays), nested tables, and record types. These are essential for handling collections of data and structured information within your PL/SQL programs.
The %TYPE Attribute (Anchoring to table columns)
The %TYPE attribute in Oracle PL/SQL is a powerful tool for declaring variables that inherit the data type and size (and sometimes constraints) of a specific database column or another already declared variable. This "anchoring" makes your code more robust and adaptable to schema changes. When the underlying column's data type or size changes, your PL/SQL code automatically adjusts without needing modification, reducing maintenance efforts.
Example 1: Basic %TYPE Declaration
DECLARE
-- Declaring a variable v_employee_name with the same data type and size
-- as the first_name column in the employees table.
-- This is ideal for ensuring data type consistency with your database schema.
v_employee_name employees.first_name%TYPE;
v_employee_id employees.employee_id%TYPE;
BEGIN
-- Assigning a value to the anchored variable
v_employee_name := 'Stephen';
v_employee_id := 100; -- Assuming employee_id 100 exists
DBMS_OUTPUT.PUT_LINE('Employee Name (anchored): ' || v_employee_name);
DBMS_OUTPUT.PUT_LINE('Employee ID (anchored): ' || v_employee_id);
END;
/
Explanation This beginner example demonstrates the fundamental use of %TYPE to declare variables that derive their data type and length directly from existing table columns. This automatically adapts your variable declarations to any changes in the underlying table structure.
Example 2: %TYPE with SELECT INTO
DECLARE
-- Anchoring a variable to the employees.salary column
emp_salary employees.salary%TYPE;
-- Anchoring a variable to the departments.department_name column
dept_name departments.department_name%TYPE;
BEGIN
-- Retrieving salary for a specific employee into the anchored variable
SELECT salary
INTO emp_salary
FROM employees
WHERE employee_id = 101; -- Assuming employee_id 101 exists
DBMS_OUTPUT.PUT_LINE('Employee 101 Salary: ' || emp_salary);
-- Retrieving department name for a specific department
SELECT department_name
INTO dept_name
FROM departments
WHERE department_id = 10; -- Assuming department_id 10 exists
DBMS_OUTPUT.PUT_LINE('Department 10 Name: ' || dept_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found for the specified ID.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Explanation This example combines %TYPE with SELECT INTO statements, a very common and efficient way to fetch data from database tables directly into PL/SQL variables while maintaining data type consistency. This reduces potential data truncation issues.
Example 3: %TYPE for Function Parameters and Return Types (Advanced)
-- Function to get an employee's email, anchored to the email column type
CREATE OR REPLACE FUNCTION get_employee_email (
p_employee_id IN employees.employee_id%TYPE
) RETURN employees.email%TYPE
IS
v_email employees.email%TYPE;
BEGIN
SELECT email
INTO v_email
FROM employees
WHERE employee_id = p_employee_id;
RETURN v_email;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/
DECLARE
-- Declaring a variable to hold the retrieved email, also anchored
email_address employees.email%TYPE;
emp_id employees.employee_id%TYPE := 103; -- Assuming employee_id 103 exists
BEGIN
email_address := get_employee_email(emp_id);
IF email_address IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || ' Email: ' || email_address);
ELSE
DBMS_OUTPUT.PUT_LINE('Email not found for employee ' || emp_id);
END IF;
END;
/
Explanation This advanced example demonstrates using %TYPE for both function parameters and return types. This is a best practice for building robust and reusable PL/SQL functions and procedures, as it ensures that the function's interface remains consistent with the underlying database schema.
Example 4: %TYPE with a Previously Declared Variable (Advanced)
DECLARE
-- Declare a base variable
v_product_code VARCHAR2(20);
-- Declare another variable inheriting the data type and size from v_product_code
v_new_product_code v_product_code%TYPE;
-- Declare a third variable inheriting from the base variable
v_old_product_code v_product_code%TYPE;
BEGIN
v_product_code := 'ABC-12345';
v_new_product_code := 'XYZ-98765';
v_old_product_code := v_product_code;
DBMS_OUTPUT.PUT_LINE('Original Product Code: ' || v_product_code);
DBMS_OUTPUT.PUT_LINE('New Product Code (anchored to original var): ' || v_new_product_code);
DBMS_OUTPUT.PUT_LINE('Old Product Code (anchored to original var): ' || v_old_product_code);
-- Demonstrate that if v_product_code's length were to change (e.g., in a package),
-- v_new_product_code would automatically adapt.
END;
/
Explanation This example illustrates a less common but equally valid use of %TYPE: deriving the data type from another already declared PL/SQL variable. This can be useful for maintaining consistency within a PL/SQL block or package when variables are logically related.
Example 5: %TYPE for Collection Elements (Advanced)
DECLARE
-- Define a PL/SQL table (associative array) type where each element
-- is anchored to the employees.last_name column type.
TYPE employee_names_tab_type IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;
-- Declare a variable of the defined collection type
employee_last_names employee_names_tab_type;
-- Cursor to fetch employee last names
CURSOR c_employees IS
SELECT last_name FROM employees WHERE ROWNUM <= 5 ORDER BY last_name;
i PLS_INTEGER := 1;
BEGIN
-- Populate the collection using a cursor loop
FOR emp_rec IN c_employees LOOP
employee_last_names(i) := emp_rec.last_name;
i := i + 1;
END LOOP;
-- Display the contents of the collection
DBMS_OUTPUT.PUT_LINE('First 5 Employee Last Names:');
FOR j IN 1..employee_last_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('- ' || employee_last_names(j));
END LOOP;
END;
/
Explanation This advanced example demonstrates using %TYPE to define the data type of elements within a PL/SQL collection (specifically an associative array). This ensures that each element in the collection has the same data type as the specified database column, maintaining consistency when handling sets of related data.
The %ROWTYPE Attribute (Anchoring to table rows)
The %ROWTYPE attribute is an extremely powerful feature in Oracle PL/SQL that allows you to declare a record variable that can hold an entire row of data from a specified database table or view, or even from a cursor. This attribute automatically defines a record with fields corresponding to each column in the row, inheriting their respective data types and sizes. This eliminates the need to declare individual variables for each column, making your code significantly more concise, readable, and robust against schema changes.
Example 1: Basic %ROWTYPE Declaration from a Table
DECLARE
-- Declaring a record variable that can hold an entire row from the employees table.
-- This is highly recommended for fetching full rows of data.
employee_record employees%ROWTYPE;
BEGIN
-- Fetching a full row for employee_id 100 into the record variable
SELECT *
INTO employee_record
FROM employees
WHERE employee_id = 100; -- Assuming employee_id 100 exists in your HR schema
-- Accessing individual fields of the record using dot notation
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_record.employee_id);
DBMS_OUTPUT.PUT_LINE('First Name: ' || employee_record.first_name);
DBMS_OUTPUT.PUT_LINE('Last Name: ' || employee_record.last_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || employee_record.salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee 100 not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Explanation This beginner-friendly example demonstrates the core functionality of %ROWTYPE. It shows how to declare a record variable that automatically mirrors the structure of the employees table, allowing you to fetch an entire row and access individual columns using dot notation (record_name.column_name).
Example 2: %ROWTYPE with Cursors for Iterating Through Rows
DECLARE
-- Declaring a cursor to fetch employees earning more than 10000
CURSOR c_high_earners IS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000
ORDER BY salary DESC;
-- Declaring a record variable based on the cursor's structure.
-- This ensures the record matches the columns selected by the cursor.
high_earner_rec c_high_earners%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('High Earners (Salary > 10000):');
DBMS_OUTPUT.PUT_LINE('-----------------------------');
-- Opening the cursor
OPEN c_high_earners;
LOOP
-- Fetching a row into the %ROWTYPE variable
FETCH c_high_earners INTO high_earner_rec;
-- Exiting the loop when no more rows are found
EXIT WHEN c_high_earners%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || high_earner_rec.employee_id ||
', Name: ' || high_earner_rec.first_name || ' ' || high_earner_rec.last_name ||
', Salary: ' || high_earner_rec.salary);
END LOOP;
-- Closing the cursor
CLOSE c_high_earners;
END;
/
Explanation This example demonstrates a common and powerful pattern: using %ROWTYPE in conjunction with explicit cursors. The record variable high_earner_rec automatically adopts the structure of the SELECT statement in c_high_earners, simplifying iteration through result sets.
Example 3: %ROWTYPE in Cursor FOR Loops (Advanced)
DECLARE
-- No explicit record declaration needed for the FOR loop.
BEGIN
DBMS_OUTPUT.PUT_LINE('Employees in Department 50:');
DBMS_OUTPUT.PUT_LINE('-------------------------');
-- The record variable emp_rec is implicitly declared as employees%ROWTYPE
-- by the Cursor FOR loop, for each row returned by the query.
FOR emp_rec IN (SELECT * FROM employees WHERE department_id = 50 ORDER BY last_name) LOOP
DBMS_OUTPUT.PUT_LINE(' ID: ' || emp_rec.employee_id ||
', Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
', Job: ' || emp_rec.job_id);
END LOOP;
DBMS_OUTPUT.PUT_LINE('-------------------------');
DBMS_OUTPUT.PUT_LINE('Departments and Managers:');
FOR dept_mgr_rec IN (
SELECT d.department_name, e.first_name || ' ' || e.last_name AS manager_name
FROM departments d
LEFT JOIN employees e ON d.manager_id = e.employee_id
ORDER BY d.department_name
) LOOP
DBMS_OUTPUT.PUT_LINE(' Department: ' || dept_mgr_rec.department_name ||
', Manager: ' || NVL(dept_mgr_rec.manager_name, 'N/A'));
END LOOP;
END;
/
Explanation This advanced example highlights the elegance of the Cursor FOR loop with %ROWTYPE. The loop automatically declares a record variable (e.g., emp_rec) that matches the structure of the SELECT statement, simplifying row processing and eliminating explicit cursor management. This is often the preferred method for iterating through query results.
Example 4: %ROWTYPE for Inserting/Updating Rows (Advanced)
DECLARE
-- Declaring a record variable anchored to the employees table structure
new_employee_rec employees%ROWTYPE;
BEGIN
-- Populate the record with values for a new employee
new_employee_rec.employee_id := 999;
new_employee_rec.first_name := 'Test';
new_employee_rec.last_name := 'User';
new_employee_rec.email := 'TESTUSER'; -- Must be unique
new_employee_rec.phone_number := '515.123.4567';
new_employee_rec.hire_date := SYSDATE;
new_employee_rec.job_id := 'IT_PROG';
new_employee_rec.salary := 6000;
new_employee_rec.commission_pct := NULL;
new_employee_rec.manager_id := 103; -- Assuming 103 is a valid manager
new_employee_rec.department_id := 60; -- Assuming 60 is a valid department
-- Inserting the entire record into the employees table
-- This syntax simplifies INSERT statements.
INSERT INTO employees VALUES new_employee_rec;
DBMS_OUTPUT.PUT_LINE('New employee inserted: ' || new_employee_rec.first_name || ' ' || new_employee_rec.last_name);
-- Updating a specific column of the record and then updating the table
new_employee_rec.salary := 6500; -- Change salary in the record
UPDATE employees
SET ROW = new_employee_rec -- Update the entire row with the record's current values
WHERE employee_id = new_employee_rec.employee_id;
DBMS_OUTPUT.PUT_LINE('Updated salary for employee ' || new_employee_rec.employee_id);
-- Rollback the transaction to keep the table clean for repeated execution
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Error: Employee ID or Email already exists. Rolling back.');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred during DML: ' || SQLERRM);
ROLLBACK;
END;
/
Explanation This advanced example showcases using %ROWTYPE not just for fetching, but also for INSERTING and UPDATING entire rows. By manipulating a %ROWTYPE variable and then performing DML operations using VALUES record_name or SET ROW = record_name, you simplify your SQL statements and maintain consistency with the table structure.
Example 5: %ROWTYPE with Record Collections (Advanced)
DECLARE
-- Define a PL/SQL table (nested table) of employee records
TYPE employee_tab_type IS TABLE OF employees%ROWTYPE;
-- Declare a variable of the defined collection type
hr_employees employee_tab_type := employee_tab_type();
-- Cursor to fetch a subset of employees
CURSOR c_some_employees IS
SELECT *
FROM employees
WHERE ROWNUM <= 3
ORDER BY employee_id;
BEGIN
-- Populate the collection by fetching rows from the cursor
FOR emp_row IN c_some_employees LOOP
hr_employees.EXTEND; -- Add a new element to the collection
hr_employees(hr_employees.LAST) := emp_row; -- Assign the fetched row to the new element
END LOOP;
DBMS_OUTPUT.PUT_LINE('Employees in Collection:');
DBMS_OUTPUT.PUT_LINE('-----------------------');
-- Iterate through the collection and display employee details
FOR i IN 1..hr_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(' ID: ' || hr_employees(i).employee_id ||
', Name: ' || hr_employees(i).first_name || ' ' || hr_employees(i).last_name ||
', Salary: ' || hr_employees(i).salary);
END LOOP;
-- Accessing a specific element in the collection
IF hr_employees.COUNT > 1 THEN
DBMS_OUTPUT.PUT_LINE('Second Employee Last Name: ' || hr_employees(2).last_name);
END IF;
END;
/
Explanation This advanced example demonstrates combining %ROWTYPE with PL/SQL collections (specifically a nested table of records). This allows you to store and manipulate entire sets of database rows in memory as a single logical unit, which is highly useful for batch processing, temporary storage, or passing multiple rows between PL/SQL subprograms.
Bind Variables
Bind variables are placeholders in SQL statements or PL/SQL blocks that allow you to substitute values at runtime. Instead of embedding literal values directly into your SQL statements, you use bind variables, which are then passed to the SQL engine. This offers significant advantages: enhanced performance through statement caching (the SQL engine can reuse the execution plan for repeated statements), improved security by preventing SQL injection, and better resource utilization.
Example 1: Basic Bind Variable in an Anonymous Block
DECLARE
-- Declare a PL/SQL variable
p_employee_id NUMBER := 101;
v_employee_name employees.first_name%TYPE;
BEGIN
-- SQL statement using a bind variable (:p_employee_id)
-- This allows the SQL engine to parse and optimize the query once.
SELECT first_name
INTO v_employee_name
FROM employees
WHERE employee_id = :p_employee_id; -- :p_employee_id is the bind variable
DBMS_OUTPUT.PUT_LINE('Employee ID ' || p_employee_id || ' First Name: ' || v_employee_name);
-- Change the value of the PL/SQL variable and re-execute
p_employee_id := 102;
SELECT first_name
INTO v_employee_name
FROM employees
WHERE employee_id = :p_employee_id;
DBMS_OUTPUT.PUT_LINE('Employee ID ' || p_employee_id || ' First Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found for the given ID.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Explanation This fundamental example shows how to use a bind variable (:p_employee_id) within a SELECT statement in an anonymous PL/SQL block. The value from the PL/SQL variable p_employee_id is "bound" to the placeholder, allowing Oracle to reuse the parsed SQL statement for different employee IDs.
Example 2: Bind Variables in Dynamic SQL (EXECUTE IMMEDIATE)
DECLARE
-- PL/SQL variables
table_name_var VARCHAR2(30) := 'EMPLOYEES';
column_name_var VARCHAR2(30) := 'SALARY';
min_salary_param NUMBER := 7000;
max_salary_param NUMBER := 12000;
employee_count NUMBER;
dynamic_sql_stmt VARCHAR2(500);
BEGIN
-- Constructing a dynamic SQL statement with bind variable placeholders
dynamic_sql_stmt := 'SELECT COUNT(*) FROM ' || table_name_var ||
' WHERE ' || column_name_var || ' BETWEEN :min_sal AND :max_sal';
-- Executing the dynamic SQL using EXECUTE IMMEDIATE and
-- passing the bind variables using the USING clause.
EXECUTE IMMEDIATE dynamic_sql_stmt
INTO employee_count
USING min_salary_param, max_salary_param;
DBMS_OUTPUT.PUT_LINE('Number of employees with salary between ' || min_salary_param || ' and ' || max_salary_param || ': ' || employee_count);
-- Change the bind variable values and re-execute
min_salary_param := 15000;
max_salary_param := 20000;
EXECUTE IMMEDIATE dynamic_sql_stmt
INTO employee_count
USING min_salary_param, max_salary_param;
DBMS_OUTPUT.PUT_LINE('Number of employees with salary between ' || min_salary_param || ' and ' || max_salary_param || ': ' || employee_count);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in dynamic SQL with bind variables: ' || SQLERRM);
END;
/
Explanation This example demonstrates the critical use of bind variables with EXECUTE IMMEDIATE for dynamic SQL. The USING clause is employed to pass values to the named bind variables (:min_sal, :max_sal), ensuring statement reuse and protecting against SQL injection, a core aspect of secure PL/SQL programming.
Example 3: Bind Variables in Procedures/Functions (IN parameters)
CREATE OR REPLACE PROCEDURE get_employee_details (
p_emp_id IN employees.employee_id%TYPE,
p_first_name OUT employees.first_name%TYPE,
p_last_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE
)
IS
BEGIN
-- The IN parameter p_emp_id acts as a bind variable in the SQL query
SELECT first_name, last_name, salary
INTO p_first_name, p_last_name, p_salary
FROM employees
WHERE employee_id = p_emp_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_first_name := NULL;
p_last_name := NULL;
p_salary := NULL;
END;
/
DECLARE
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
-- Call the procedure, passing a value to the IN parameter (which becomes a bind variable)
get_employee_details(100, v_first_name, v_last_name, v_salary);
DBMS_OUTPUT.PUT_LINE('Employee 100: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
get_employee_details(102, v_first_name, v_last_name, v_salary);
DBMS_OUTPUT.PUT_LINE('Employee 102: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
get_employee_details(999, v_first_name, v_last_name, v_salary); -- Non-existent employee
IF v_first_name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Employee 999 not found.');
END IF;
END;
/
Explanation This example demonstrates how IN parameters in PL/SQL procedures and functions naturally act as bind variables when used in the SQL statements within those subprograms. This is the most common way to leverage bind variables in modular PL/SQL development.
Example 4: Using FORALL with Bind Variables for Bulk DML (Advanced)
DECLARE
-- Define a nested table type for employee IDs and new salaries
TYPE employee_id_list IS TABLE OF employees.employee_id%TYPE;
TYPE new_salary_list IS TABLE OF employees.salary%TYPE;
-- Declare variables of the defined types
l_employee_ids employee_id_list := employee_id_list(100, 101, 102); -- Existing employee IDs
l_new_salaries new_salary_list := new_salary_list(7000, 8500, 4800); -- New salaries
BEGIN
-- Bulk update using FORALL and bind variables (collection elements are bound)
-- This significantly improves performance for DML operations on collections.
FORALL i IN 1 .. l_employee_ids.COUNT
UPDATE employees
SET salary = l_new_salaries(i) -- Bind variable from collection
WHERE employee_id = l_employee_ids(i); -- Bind variable from collection
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated in bulk.');
-- Rollback the transaction to keep the table clean
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error during bulk update: ' || SQLERRM);
ROLLBACK;
END;
/
Explanation This advanced example showcases FORALL, a powerful construct for bulk DML operations. When FORALL is used, each element of the PL/SQL collection (e.g., l_new_salaries(i)) acts as a bind variable to the SQL statement, leading to massive performance gains by reducing context switching between the PL/SQL and SQL engines.
Example 5: Retrieving Multiple Rows with BULK COLLECT and Bind Variables (Advanced)
DECLARE
-- Define record type for employee details
TYPE emp_details_rec IS RECORD (
employee_id employees.employee_id%TYPE,
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
salary employees.salary%TYPE
);
-- Define a nested table type of the record
TYPE emp_details_tab IS TABLE OF emp_details_rec;
-- Declare a variable of the nested table type to hold fetched rows
l_emp_data emp_details_tab;
-- Bind variable for the salary threshold
p_salary_threshold NUMBER := 6000;
BEGIN
-- Fetch multiple rows into the collection using BULK COLLECT
-- The :p_salary_threshold is a bind variable.
SELECT employee_id, first_name, last_name, salary
BULK COLLECT INTO l_emp_data
FROM employees
WHERE salary > :p_salary_threshold
ORDER BY salary DESC;
DBMS_OUTPUT.PUT_LINE('Employees with salary > ' || p_salary_threshold || ':');
DBMS_OUTPUT.PUT_LINE('------------------------------------');
IF l_emp_data.COUNT > 0 THEN
FOR i IN 1 .. l_emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(' ID: ' || l_emp_data(i).employee_id ||
', Name: ' || l_emp_data(i).first_name || ' ' || l_emp_data(i).last_name ||
', Salary: ' || l_emp_data(i).salary);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('No employees found above the threshold.');
END IF;
-- Change the threshold and re-execute
p_salary_threshold := 10000;
SELECT employee_id, first_name, last_name, salary
BULK COLLECT INTO l_emp_data
FROM employees
WHERE salary > :p_salary_threshold
ORDER BY salary DESC;
DBMS_OUTPUT.PUT_LINE('Employees with salary > ' || p_salary_threshold || ' (re-run):');
DBMS_OUTPUT.PUT_LINE('------------------------------------');
IF l_emp_data.COUNT > 0 THEN
FOR i IN 1 .. l_emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(' ID: ' || l_emp_data(i).employee_id ||
', Name: ' || l_emp_data(i).first_name || ' ' || l_emp_data(i).last_name ||
', Salary: ' || l_emp_data(i).salary);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('No employees found above the new threshold.');
END IF;
END;
/
Explanation This advanced example demonstrates BULK COLLECT for efficiently retrieving multiple rows from the database into a PL/SQL collection. The WHERE clause utilizes a bind variable (:p_salary_threshold), allowing the SQL statement to be reused for different salary criteria while minimizing context switching and enhancing performance. This is a crucial technique for high-performance data retrieval in Oracle PL/SQL.