Oracle SELECT FOR Statement: Usage Guide

1 year ago
Emily Johnson
2 minutes

In Oracle, the SELECT FOR statement is used to select elements from a collection variable in PL/SQL. This statement allows for individual extraction of elements from a collection and manipulation within the PL/SQL code.

Here is the basic syntax for the SELECT statement:

SELECT column_name
INTO variable_name
FROM collection_name;

In this syntax, column_name is the name of the column in the collection, variable_name is the variable used to store each element, and collection_name is the name of the collection. By executing a SELECT FOR statement, you can extract each element from the collection one by one and store it in a variable for processing.

For example, suppose there is a collection variable called salary_list that stores employee salaries. You can use the SELECT FOR statement to retrieve salaries one by one and perform operations.

DECLARE
   TYPE SalaryListType IS TABLE OF NUMBER;
   salary_list SalaryListType := SalaryListType(1000, 2000, 3000);
   v_salary NUMBER;
BEGIN
   FOR i IN 1..salary_list.COUNT LOOP
      SELECT column_value
      INTO v_salary
      FROM TABLE(salary_list)
      WHERE INDEX = i;

      DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
   END LOOP;
END;

In the above example, salaries from the salary_list collection can be retrieved one by one using the SELECT FOR statement and printed to the console. This makes it easier to operate on elements within a collection in PL/SQL.

bannerAds