Há muitas maneiras de tratar dados do tipo collection e também há muitas maneiras dinâmicas de tratar dados convertendo eles para collection.

Abaixo será demonstrado como tratar estes dados em SELECTs e como altera-los:

Trabalhando com dados de collection / conjunto em SELECT

Sub-Query

É utilizado para listar todos os dados em uma unica linha.
Trabalha apenas com o tipo MULTISET. Por padrão sempre irá retornar um tipo MULTISET com um tipo ROW dentro : MULTISET( ROW() ).
Para que retorne apenas um MULTISET() é necessário especificar o parametro ITEM na sub-query.
Como dito antes , a vantagem deste recurso é permitir que o resultado de uma sub-query seja exibida em uma unica linha:

# echo "                                                                   
SELECT FIRST 1 tabname , MULTISET ( SELECT colname FROM syscolumns c     
where c.tabid = t.tabid) FROM systables t;                               
SELECT FIRST 1 tabname , MULTISET ( SELECT ITEM colname FROM syscolumns c
where c.tabid = t.tabid) FROM systables t;
" | dbaccess sysmaster

Database selected.
tabname       systables
(expression)  MULTISET{ROW('tabname'),ROW('owner'),ROW('partnum'),ROW('tabid'),
              ROW('rowsize'),ROW('ncols'),ROW('nindexes'),ROW('nrows'),ROW('cre
              ated'),ROW('version'),ROW('tabtype'),ROW('locklevel'),ROW('npused
              '),ROW('fextsize'),ROW('nextsize'),ROW('flags'),ROW('site'),ROW('
              dbname'),ROW('type_xid'),ROW('am_id'),ROW('pagesize'),ROW('ustlow
              ts'),ROW('secpolicyid'),ROW('protgranularity')}

1 row(s) retrieved.

tabname       systables
(expression)  MULTISET{'tabname','owner','partnum','tabid','rowsize','ncols','n
              indexes','nrows','created','version','tabtype','locklevel','npuse
              d','fextsize','nextsize','flags','site','dbname','type_xid','am_i
              d','pagesize','ustlowts','secpolicyid','protgranularity'}

1 row(s) retrieved.
Database closed.

Para melhor exibiçao dos dados ao usuário pode-se criar uma funçao que receba como parametro um campo MULTISET , leia os itens e retorne em uma unica string CHAR ou VARCHAR.
Segue exemplo de código:

# echo  '                                                                             
CREATE procedure show_dados( pSet multiset(varchar(254) not null ))                 
 RETURNING char(800) ;                                                              
  DEFINE vNome char(20)  ;                                                          
  DEFINE vRes  varchar(200) ;                                                       
  let vRes="" ;                                                                     
  let vNome="" ;                                                                    
  FOREACH cursor_a FOR                                                              
    SELECT tSet.nome INTO vNome                                                     
    FROM table(pSet) as tSet(nome)                                                  
    order by 1                                                                      
    let vRes=trim(vRes)||" "||trim(vNome);                                          
  END FOREACH ;                                                                     

  RETURN trim(vRes) ;
end procedure ;      
SELECT FIRST 1 tabname , show_dados(MULTISET ( SELECT ITEM colname FROM syscolumns c
where c.tabid = t.tabid)) FROM systables t;
drop procedure show_dados;
' | dbaccess sysmaster

Database selected.
Routine created.

tabname       systables
(expression)  am_id created dbname fextsize flags locklevel ncols nextsize nind
              exes npused nrows owner pagesize partnum protgranularity rowsize
              secpolicyid site tabid tabname tabtype type_xid ustlowts version

1 row(s) retrieved.
Routine dropped.
Database closed.

Collection-Derived

É utilizado para criar uma tabela virtual apartir do resultado de uma sub-query/campo-conjunto/UDR.
Para utilizar este recurso quando a sub-query retorna um tipo conjunto é obrigatório que o filtro da sub-query retorne apenas uma 1 linha .
O acesso sempre é utilizado a palavra reservada TABLE, com a seguinte sintaxes:
TABLE( MULTISET( expressao ) ) as [alias] ( nome_colunas,... )
TABLE(( [sub-query com campo collect] )) as [alias] ( nome_colunas,... )
Obs.: Atenção especial para a sintaxe acima, é utilizado 2 parenteses "((..))"

Baseado na tabela temporaria abaixo, segue exemplos .

create temp table tab_formatacao ( codigo integer ,  opcoes set(varchar(20) not null) );
insert into tab_formatacao values (1, set{"negrito","sublinhado","caixa-alta"} );       
insert into tab_formatacao values (2, set{"sobrescrito","italico"} );                   

Database selected.
Temporary table created.
1 row(s) inserted.
1 row(s) inserted.

Selecionar um campo do tipo conjunto

select * from table((select opcoes from tab_formatacao where codigo = 2 ));

unnamed_col_1        

sobrescrito
italico

2 row(s) retrieved.

select * from table((select opcoes from tab_formatacao where codigo = 1 ));

unnamed_col_1

negrito
sublinhado
caixa-alta

3 row(s) retrieved.


select * from table((select opcoes from tab_formatacao where codigo = 1 )) as formato(fmts) 
where fmts != "negrito";

fmts

sublinhado
caixa-alta

2 row(s) retrieved.

Juntar várias querys em uma unica linha

select * from table( multiset(select count(*) from tab_formatacao where "negrito" in opcoes) ) as t1(qtd_negrito) , 
table( multiset (select count(*) from tab_formatacao where "sublinhado" in opcoes) ) as t2(qtd_sublinhados);

     qtd_negrito  qtd_sublinhados

               1                1

1 row(s) retrieved.

-- PADRAO ANSI/ISO
select * from (select count(*) from tab_formatacao where "negrito" in opcoes) as t1(qtd_negrito) , 
(select count(*) from tab_formatacao where "sublinhado" in opcoes) as t2(qtd_sublinhados);

     qtd_negrito  qtd_sublinhados

               1                1

1 row(s) retrieved.

SPL - Stored Procedure Language

Exibir os resultado de uma stored procedure:
ver post Utilizar uma stored procedure em um select no lugar de uma tabela

Alterado dados de collection / conjunto

Existem 3 modos de trabalhar:

• SPL - Stored Procedure Language.
  É um dos meios mais flexiveis de trabalhar com campos collection. Com ele é possível manipular o dado e cada item do conjunto. Porém é necessário escrever algumas linhas de código.
  
• ESQL/C
  Assim como SPL também é bem flexivel o tratamendo ao dado
  
• SQL
  Inserts/Updates em campos collection devem ser feitos com o campo completo, ou seja, não é possível adicionar ou remover um item do conjunto de um campo.
  

O acesso sempre é utilizado a palavra reservada TABLE, com a seguinte sintaxe:
TABLE( [variavel_collection] ) as alias ( nome_coluna )

SPL - Stored Procedure Language

Para selecionar um campo:

-- Definir uma variável do mesmo collection e outra do mesmo tipo utilizado.
DEFINE v_conjunto set(integer not null);
DEFINE v_item integer;

-- Utilizar o parâmetro INTO do SELECT.
-- Observaçao: É necessário que o select retorne apenas uma linha ou deve-se utilizar um FOREACH.
SELECT conj INTO v_conjunto FROM tabela_xyz WHERE campo_chave = 1;

-- Declarar um cursor/foreach utilizando o recurso de "Collection Derived Table"
FOREACH cursor1 FOR SELECT * into v_item FROM TABLE(v_conjunto)
...
END FOREACH

Para alterar/deletar um campo:

{
Utilizando a estrutura para selecionar o dado (exemplo anterior) dentro do FOREACH, 
executar o UPDATE/DELETE utilizando o recurso "Collection Derived Table" com parametro 
WHERE CURRENT OF
Observaçao: Nao é possível atualizar ou apagar utilizando uma clausula WHERE. 
Caso seja feito, irá retornar erro -9612
}
FOREACH cursor1 FOR SELECT * into v_item FROM TABLE(v_conjunto)
...
-- Atualiza item atual do foreach
UPDATE TABLE(v_conjunto)(x) set x=13432 WHERE CURRENT OF cursor1;

-- Apaga item atual do foreach
DELETE TABLE(v_conjunto) WHERE CURRENT OF cursor1;
...
END FOREACH

Para atualizar a tabela com a variável do conjunto.

UPDATE tabela_xyz SET conjunto = v_conjunto WHERE campo_chave = 1;

Para alterar/deletar *todos* os itens de um campo:

-- Nao é necessário um FOREACH, basta ler o campo para uma váriavel igual seleçao.
UPDATE TABLE(v_conjunt)(x) set x=2;
-- Atualizar a tabela com o variável do conjunto.
UPDATE tabela_xyz SET conjunto = v_conjunto WHERE campo_chave = 1;

-- Executar UPDATE/DELETE utilizando o collection derived table.
DELETE FROM TABLE(v_conjunto)(x) ;

Para incluir dados em um campo:

-- Nao é necessário um FOREACH, basta ler o campo para uma váriavel igual seleçao. 
-- Executar INSERT utilizando o collection derived table.
INSERT INTO TABLE(v_conjunto) VALUES (5);

-- Caso seja utilizado o tipo LIST é necessário especificar a posiçao com o parametro AT.
INSERT AT 3 INTO TABLE(v_conjunto) VALUES (5);

-- Para saber identificar quantos itens existe um campo collection, utilizar a funçao cardinality().

ESQL/C

Pode-se utilizar os mesmos recursos do SPL mais os comandos:

ALLOCATE COLLECTION
ALLOCATE ROW
DEALLOCATE ROW
DEALLOCATE COLLECTION

SQL

Para alterar/incluir :

{
Deve-se sempre especificar todos os valores do campo, nao é possível tratar cada 
item de um campo igual SPL.
}
INSERT INTO tabela_xyz (conjunto) VALUES (set{4,32,42,2,45});
UPDATE tabela_xyz set conjunto = set{4,32,42,2,45};

Para deletar:
Nao é possível deletar um item em um campo de conjunto.
Para remover um item com SQL é necessário executar um UPDATE na linha, atualizando o campo (com todos os itens, removendo apenas item desejado)

Para selecionar os items de um campo:
Ler primeira parte deste post, lá explica como utilizar com SELECTs.

Média:

0

Ainda não votado

Sua avaliação: Selecione a avaliaçãoCancelar a avaliaçãoPoorOkayBomGreatAwesome

Sua avaliação: Nenhum