CODEWITHSUNDEEP
plsqlcursor
maurux
maurux

Reputation: 33

PL/SQL: Cursor ::Retrieve a list of employees for each department

How to retrieve list of employees for each department from table EMP into a comma-delimited new table something like:

[column x:ie deptno]  [column y:ie ename]
--------------------------
    7           Jesus, María, José
    5           Staz, Przemek, Tomek 
    6           John, Jane, Bob

below table is where I want to put my result from Function concatenate_list compilation

CREATE TABLE Z
(
   x NUMBER(2)     NOT NULL,
   y VARCHAR2 (4000)     NOT NULL
);




SET SERVEROUTPUT ON

CREATE OR REPLACE FUNCTION concatenate_list (xy_cursor IN SYS_REFCURSOR)
  RETURN VARCHAR2
IS
  lret  VARCHAR2(30000);
  ltemp VARCHAR2(30000);
BEGIN
  LOOP
    FETCH xy_cursor
    INTO ltemp;
    EXIT WHEN xy_cursor%notfound;
    lret  := lret  || ',' || ltemp;
    END LOOP;
    RETURN LTRIM(lret, ',');
 END;
/
SHOW ERRORS

how to insert the results from "Function concatenate_lit compile" and get a result as mentioned above. Maybe using something like this:

INSERT INTO Z( x, y) SELECT e1.x,
   concatenate_list(CURSOR(SELECT e2.y FROM EMP e2 WHERE e2.x= e1.x));

but how to set it up form inside the PL/SQL block

Upvotes: 1

Views: 1329

Answers (1)

Ravi
Ravi

Reputation: 31437

This may help you.

declare
type cur_name is ref cursor;
emp_name cur_name;
v_ename emp.ename%type;
v_all_ename varchar2(1000);
v_deptno emp.deptno%type; 
cursor c is select deptno,cursor(select ename from emp e where e.deptno=f.deptno) from emp f group by deptno;
begin
open c;
loop
fetch c into v_deptno,emp_name;
exit when c%notfound;
loop
fetch emp_name into v_ename;
exit when emp_name%notfound;
v_all_ename:=v_all_ename||v_ename;
v_all_ename:=v_all_ename||',';
end loop;
dbms_output.put_line(v_deptno||' '||v_all_ename); 
v_all_ename:='';
end loop;
close c;
end;

Upvotes: 1

Related Questions