how could I use parameter for employees in each year in plsql-CodePudding

How I could add employees each year under 'Acquire since YYYY' in their year I find a way for a week and still don't know how it's work This is my statement

DECLARE
    CURSOR c_year IS SELECT EXTRACT (YEAR FROM hire_date) YEAR, COUNT(*) PERSON
                    FROM employees
                    GROUP BY EXTRACT (YEAR FROM hire_date)
                    ORDER BY 1 ASC;
    CURSOR c_emp (thisyear NUMBER) IS   SELECT employee_id ID, first_name, last_name , hire_date
                                        FROM employees
                                        WHERE EXTRACT(YEAR FROM hire_date) = thisyear
                                        ORDER BY 1 ASC;
BEGIN
    FOR e_year IN c_year LOOP
        DBMS_OUTPUT.PUT_LINE('Acquire since '|| e_year.YEAR || ' ('||e_year.PERSON || ' persons) ==========');
            FOR e_emp IN c_emp(8) LOOP
                DBMS_OUTPUT.PUT_LINE('ID: '||e_emp.ID||' '||e_emp.first_name||' '||e_emp.last_name);
            END LOOP;
    END LOOP;
END;

And This is result

Acquire since 2001 (1 persons) ==========
Acquire since 2002 (7 persons) ==========
Acquire since 2003 (6 persons) ==========
Acquire since 2004 (10 persons) ==========
Acquire since 2005 (29 persons) ==========
Acquire since 2006 (24 persons) ==========
Acquire since 2007 (19 persons) ==========
Acquire since 2008 (11 persons) ==========

But I want employee's detail under each line by their year what should I do

CodePudding user response：

Pass e_Year.YEAR into the parameterized cursor instead of the static value 8:

DECLARE
  CURSOR c_year IS
    SELECT EXTRACT (YEAR FROM hire_date) YEAR, COUNT(*) PERSON
    FROM employees
    GROUP BY EXTRACT (YEAR FROM hire_date)
    ORDER BY 1 ASC;

  CURSOR c_emp (thisyear NUMBER) IS
    SELECT employee_id ID, first_name, last_name , hire_date
    FROM employees
    WHERE EXTRACT(YEAR FROM hire_date) = thisyear
    ORDER BY 1 ASC;
BEGIN
  FOR e_year IN c_year LOOP
    DBMS_OUTPUT.PUT_LINE('Acquire since '|| e_year.YEAR || ' ('||e_year.PERSON || ' persons) ==========');
    FOR e_emp IN c_emp(e_year.year) LOOP
      DBMS_OUTPUT.PUT_LINE('ID: '||e_emp.ID||' '||e_emp.first_name||' '||e_emp.last_name);
    END LOOP;
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE employees ( employee_id, first_name, last_name, hire_date ) AS
SELECT 1, 'Alice', 'Abbot', DATE '2001-01-01' FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Baron', DATE '2001-02-01' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Count', DATE '2001-03-01' FROM DUAL UNION ALL
SELECT 4, 'Debra', 'Duke',  DATE '2002-01-01' FROM DUAL UNION ALL
SELECT 5, 'Emily', 'Earl',  DATE '2002-02-01' FROM DUAL UNION ALL
SELECT 6, 'Fiona', 'Friar', DATE '2003-01-01' FROM DUAL UNION ALL
SELECT 7, 'Gerri', 'Grace', DATE '2004-01-01' FROM DUAL;

Outputs:

Acquire since 2001 (3 persons) ==========
ID: 1 Alice Abbot
ID: 2 Betty Baron
ID: 3 Carol Count
Acquire since 2002 (2 persons) ==========
ID: 4 Debra Duke
ID: 5 Emily Earl
Acquire since 2003 (1 persons) ==========
ID: 6 Fiona Friar
Acquire since 2004 (1 persons) ==========
ID: 7 Gerri Grace

fiddle