Как мога да групирам няколко реда в колекция в моя PL/SQL курсор?

Имам курсор, където трябва да получа цял куп информация за интерфейс, но една от таблиците, от които трябва да получа информация, има по един ред за всяка информация, която ми трябва в колони.

Разгледах PIVOT и в началото изглеждаше, че ще е бъркотия (особено като се има предвид, че нямам нужда от агрегат за това), но успях да го накарам да работи добре; но аз съм сериен обучаващ се, така че все пак искам да разбера дали е възможно по следния начин:

Мислех да направя това, като извлека таблица като колона: "тип таблица на (обект)", така че да имам масив от масив в моя PL/SQL код... и работи добре! Когато моята таблица със свойства имаше само един ред, но получих ORA-01427, когато имаше повече от един. Ето кратък примерен код (използвам само таблиците с идентификатори и адреси за простота, това е истински малък PL, който създадох само за да тествам тази функционалност):

CREATE OR REPLACE TYPE CAIB_FIELDS AS OBJECT (
ID_QUALIFIER  VARCHAR2(3),
ID_NUMBER     VARCHAR2(20)
)
/
CREATE TYPE CAIB_TBL AS TABLE OF CAIB_FIELDS
/

DECLARE
CURSOR MYCUR(CID IN VARCHAR2) IS
SELECT CUST_ID,CUST_ADDR,UPD_DATE
(SELECT CAIB_TBL(CAIB_FIELDS(ID_QUALIFIER,ID_NUMBER)) FROM CUSTOMER_IDS B
  WHERE B.CUST_ID = A.CUST_ID
  AND   B.CUST_ADDR = A.CUST_ADDR) CAIB
FROM CUSTOMER_ADDR A
WHERE A.CUST_ID = CID
;
   TYPE MYCUR_TYPE IS TABLE OF MYCUR%ROWTYPE;
    REC_MYCUR MYCUR_TYPE;
BEGIN
    OPEN MYCUR('918888'); --This customer has only one ID row -> OK! IT WORKS!
    --For customer ID '002632', he has several ID ROWS -> ERROR ORA-01427
    LOOP
        FETCH MYCUR BULK COLLECT INTO REC_MYCUR LIMIT 100;
        FOR I IN 1..REC_MYCUR.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).CAIB(1).ID_QUALIFIER);
            --DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).CAIB(2).ID_QUALIFIER); --this would be OK if Oracle would allow me to fetch multiple rows on my CAIB_TBL type, of course I'll just loop here if it works, but for the sake of this test, I just used fixed values..
        END LOOP;
        EXIT WHEN MYCUR%NOTFOUND;
    END LOOP;
END;

Благодаря предварително!!

--- РЕДАКТИРАНЕ, отговорът на tbone е точно това, което търсех, но не отразява точния сценарий, тъй като се занимава само с таблица с една колона; за няколко колони решението се променя леко, ето последният ми тест:

create table testA
(
col1 number,
col2 varchar2(50)
);

create table testB
(
col1 number,
col2 varchar2(50),
col3 varchar2(50)
);

insert into testA values (1,'A');
insert into testA values (2,'B');
insert into testA values (3,'C');

insert into testB values (1,'X','x');
insert into testB values (1,'Y','y');
insert into testB values (1,'Z','z');

insert into testB values (2,'BA','ba');
insert into testB values (2,'BB','bb');
commit;


CREATE OR REPLACE TYPE t_test_rec AS object
(col2 varchar2(50),
col3 varchar2(50)
)
/
create or replace type t_vchar_tab as table of t_test_rec;

DECLARE
CURSOR MYCUR IS
SELECT A.COL1, 
CAST(MULTISET(SELECT B.COL2,B.COL3 FROM TESTB B WHERE B.COL1 = A.COL1 ORDER BY B.COL2) AS T_VCHAR_TAB) AS TESTB_VALS
FROM TESTA A
;
   TYPE MYCUR_TYPE IS TABLE OF MYCUR%ROWTYPE;
    REC_MYCUR MYCUR_TYPE;
BEGIN
    OPEN MYCUR;
    LOOP
        FETCH MYCUR BULK COLLECT INTO REC_MYCUR LIMIT 100;
        FOR I IN 1..REC_MYCUR.COUNT
        LOOP
            IF REC_MYCUR(I).TESTB_VALS.COUNT = 0 THEN
                DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).COL1 || '->(NULL)');
            ELSE
                FOR J IN 1..REC_MYCUR(I).TESTB_VALS.COUNT
                LOOP
                   DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).COL1 || '->' || REC_MYCUR(I).TESTB_VALS(J).COL2 || ',' || REC_MYCUR(I).TESTB_VALS(J).COL3);
                   NULL;
                END LOOP;
            END IF;
        END LOOP;
        EXIT WHEN MYCUR%NOTFOUND;
    END LOOP;
END;
/

sql oracle plsql
person LFLFM    schedule 26.02.2014    source източник

Отговори (1)


arrow_upward
3
arrow_downward

Малко е неясно какво се опитвате да постигнете, но въз основа на заглавието (групиране на няколко реда в колекция от курсор), можете да направите нещо подобно:

set echo on;
set display on;
set linesize 200;

create table testA
(
col1 number,
col2 varchar2(50)
);

create table testB
(
col1 number,
col2 varchar2(50)
);

create or replace type t_vchar_tab as table of varchar2(50);

insert into testA values (1,'A');
insert into testA values (2,'B');

insert into testB values (1,'X');
insert into testB values (1,'Y');
insert into testB values (1,'Z');
commit;

-- select all related testB.col2 values in a nested table for each testA.col1 value
select a.col1, 
cast(multiset(select b.col2 from testB b where b.col1 = a.col1 order by b.col2) as t_vchar_tab) as testB_vals
from testA a;

Така че изходът ще бъде само 2-та реда от tableA, но има колона с вложена таблица, съдържаща всички съвпадащи редове от tableB

person tbone    schedule 26.02.2014
comment
О, разбира се! CAST(MULTISET())! Вашият пример не е ТОЧНО това, което търсех, но решението е. Ще редактирам въпроса си, за да добавя редактирана версия на вашия пример, отразяваща точните ми нужди. Благодаря много! - person LFLFM; 26.02.2014