CODEWITHSUNDEEP
oracle-databaseplsqlcursor
devBem
devBem

Reputation: 840

Cursor merging values from multiple rows. PL/SQL

I've got table EMP which contains employees name and department he works in. Something like say (obviously it's way bigger than my example):

dept name
10   Clark
10   King
20   Jacob
30   Michael
10   Miller 
30   John
40   Leonardo

Then there's second table:

Emps_in_Depts (Deptno NUMBER(2), Employees VARCHAR2(4000))

And my task is to write PL/SQL code which would insert data into that second table in such fashion:

(10,CLARK$KING$MILLER$)
(30,Michael$John)

and so on for every dept there is.

I have basic idea of using cursor inside cursor but I have no idea how to merge values from multiple rows into a single one.

  DECLARE
     CURSOR kursor
     IS
       SELECT DISTINCT deptno
       FROM emp;
     
       departament  
            emp.deptno%TYPE;
  BEGIN
     OPEN kursor;

    LOOP
       FETCH kursor INTO departament;
       EXIT WHEN kursor%NOTFOUND;
       
       --HERE SHOULD BE SECOND CURSOR 
       --which I'm not sure how to code so that it fetches from  
       --SELECT ename FROM emp WHERE deptno = departament
       --
       --and it should insert values into Emps_in_depts here I guess.
   
     END LOOP;

     CLOSE kursor;
  END;

Upvotes: 0

Views: 4082

Answers (2)

Guntram Blohm
Guntram Blohm

Reputation: 9819

What you can do is basically:

DECLARE
    TYPE EmpCurTyp IS REF CURSOR; 
    empname   EmpCurTyp;
    nameList varchar2(4000);
    name varchar2(200);
BEGIN
    OPEN empname FOR  -- open cursor variable
        'SELECT name FROM emp WHERE dept = :s' USING departament;
    LOOP
        FETCH empname INTO name
        EXIT WHEN empname%NOTFOUND
        nameList:=nameList||'$'||name
    END LOOP;
    CLOSE empname;
    INSERT INTO emps_in_deps VALUES (departament, nameList)
END

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Check function LISTAGG (available since Oracle 11.2), you can alomost copy it from documentation.

Upvotes: 2

Related Questions