I want to use the select operator to select a column with dates (years), and then put it in an array variable, so that I can iterate over its values in a loop.
Here is the full code:
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
otdel VARCHAR2(20),
date_empl DATE,
salary NUMBER(10, 2),
bonus NUMBER(10, 2),
nalog NUMBER(10, 2)
);
INSERT INTO employees (employee_id, otdel, date_empl, first_name, last_name, salary)
VALUES (101, 'IT', to_date('2015-01-14', 'yyyy/mm/dd'), 'John', 'Doe', 5000),
(102, 'TOP Management', to_date('2007-06-30', 'yyyy/mm/dd'), 'Jane', 'Smith', 6000),
(103, 'IT', to_date('2010-02-23', 'yyyy/mm/dd'), 'Alice', 'Johnson', 5500),
(104, 'Sales', to_date('2017-08-24', 'yyyy/mm/dd'), 'Sean', 'Connery', 3500),
(105, 'Sales', to_date('2019-03-30', 'yyyy/mm/dd'), 'Marv', 'Smith', 3000);
COMMIT;
DECLARE
TYPE SelRes IS VARRAY(10) OF NUMBER(4,0);
years SelRes;
xxx NUMBER(4,0);
BEGIN
SELECT EXTRACT (YEAR FROM empl.date_empl) INTO years FROM employees empl;
FOR y in years
LOOP
SELECT COUNT(*) INTO xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = y;
IF xxx>0 THEN
DBMS_OUTPUT.PUT_LINE(y || ': ' || xxx || ' empl(-s) hired');
END IF;
END LOOP;
END;
In this case, the error occurs at the first select:
Output:
SELECT EXTRACT (YEAR FROM empl.date_empl) INTO years FROM employees empl;
*
ERROR at line 7:
ORA-06550: line 7, column 50:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 9, column 12:
PLS-00862: The type of the iterand is not compatible with an iteration control.
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
The idea is as follows: to calculate by year the number of employees who were hired this year. At the same time, I don't want to take all the years from MIN_YEAR to MAX_YEAR, but only the years from the array.
Is it possible to implement this in PL/SQL somehow?
There are 3 issues with that pl/sql block:
SELECT INTO is only applicable if the query result returns a single row. Use BULK COLLECT INTO for multiple values
To iterate through the elements of an array, use FOR i IN 1 .. l_array.COUNT
Syntax to reference an element of an array. y is the iterator, that will have the position value of the element (1,2,...). The value at position y is referenced with syntax l_array(y)
DECLARE
TYPE SelRes IS VARRAY(10) OF NUMBER(4,0);
years SelRes;
xxx NUMBER(4,0);
BEGIN
-- BULK COLLECT INTO for multiple values.
SELECT EXTRACT (YEAR FROM empl.date_empl) BULK COLLECT INTO years FROM employees empl;
-- Syntax for iterating through elements of a array:
FOR y in 1 .. years.COUNT
LOOP
SELECT COUNT(*) INTO xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = years(y);
IF xxx>0 THEN
DBMS_OUTPUT.PUT_LINE(years(y) || ': ' || xxx || ' empl(-s) hired');
END IF;
END LOOP;
END;
However, I would't use VARRAY datatype for this since that has a fixed number of elements. Use a nested table instead. Also prefix local variables to avoid name conflicts - you could get unexpected results if variables have the same name as object names.
DECLARE
TYPE selrestyp IS TABLE OF NUMBER;
l_years selrestyp;
l_xxx NUMBER(4,0);
BEGIN
SELECT EXTRACT (YEAR FROM empl.date_empl) BULK COLLECT INTO l_years FROM employees empl;
FOR y in 1 .. l_years.COUNT
LOOP
SELECT COUNT(*) INTO l_xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = l_years(y);
IF l_xxx>0 THEN
DBMS_OUTPUT.PUT_LINE(l_years(y) || ': ' || l_xxx || ' empl(-s) hired');
END IF;
END LOOP;
END;
Note that use of arrays is not needed and you can just use "cursor FOR LOOP". I added a "DISTINCT" clause to avoid duplicate years:
DECLARE
l_xxx NUMBER(4,0);
BEGIN
FOR y in (SELECT DISTINCT EXTRACT (YEAR FROM empl.date_empl) as year FROM employees empl)
LOOP
SELECT COUNT(*) INTO l_xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = y.year;
IF l_xxx>0 THEN
DBMS_OUTPUT.PUT_LINE(y.year || ': ' || l_xxx || ' empl(-s) hired');
END IF;
END LOOP;
END;