André L. S.

Se você é um DBA ou trabalha com programação e usa muito banco de dados SQL, este tópico não é você, a menos que não conheça as cláusulas INNER JOIN e OUTER JOIN, muito comuns em SGBDs SQL.

Não sabe o que é SGBD? Os Sistemas de Gerenciamento de Banco de Dados são programas que auxiliam na manipulação dos dados armazenados. O MySQL, PostgreSQL, MS SQL Server ou Oracle, que você tem em sua casa ou empresa, são SGBDs.

Para este tutorial você precisa ter, pelo menos, o conhecimento básico de SQL.

Chega de enrolação, vamos ao que interessa!

INNER JOIN

A cláusula INNER JOIN é usada quando se quer recuperar dados em mais de uma tabela através da igualdade de suas foreign keys.

Por exemplo, pense que você quer criar um programa que sirva como agenda telefônica. Você cadastra as pessoas e os contatos telefônicos, como uma pessoa poderá ter nenhum até N números de telefones, você separa em duas entidades como neste modelo:

Olhando rapidamente você percebe que a entidade TELEFONE tem o atributo NUM_PESSOA que é o número de identificação do registro na entidade PESSOA.

Vamos supor que as tabelas estejam preenchidas com os seguintes registros:

 +----------------------------+  +---------------------------+
 |           PESSOA           |  |         CONTATO           |
 +------------+---------------+  +------------+--------------+
 | NUM_PESSOA | NOME_PESSOA   |  | NUM_PESSOA | NUM_TELEFONE |
 | 1          | José          |  | 1          | 6589-3666    |
 | 2          | Arnaldo       |  | 3          | 9888-6699    |
 | 3          | Maria         |  | 3          | 8956-6666    |
 | 4          | Elverdelando  |  | 2          | 2888-9877    |
 +------------+---------------+  +------------+--------------+

Para recuperar os telefones de uma pessoa você deve usar INNER JOIN, implicitamente ou explicitamente. Vou explicar os dois casos.

O método explícito é usar a cláusula explicitamente (duh!), que é formada por INNER JOIN <tabela filha> ON <atributos de identificação>, no nosso exemplo:

SELECT nome_completo, num_telefone FROM pessoa INNER JOIN telefone ON pessoa.num_pessoa = telefone.num_pessoa

Estamos dizendo: “me traga o nome e os números dos telefones da pessoa onde o número da pessoa em TELEFONE seja igual ao número da pessoa em PESSOA”.

Na forma implícita você não usa a cláusula INNER JOIN, o tratamento de igualdade é feito na cláusula WHERE. Usando o mesmo exemplo:

SELECT nome_completo, num_telefone FROM pessoa, telefone WHERE pessoa.num_pessoa = telefone.num_pessoa

Em ambos os casos o resultado será:

 +---------------------------+
 |         CONTATO           |
 +------------+--------------+
 | NOM_PESSOA | NUM_TELEFONE |
 | José       | 6589-3666    |
 | Maria      | 9888-6699    |
 | Maria      | 8956-6666    |
 | Arnaldo    | 2888-9877    |
 +------------+--------------+

“Que simples! Hummmm espera um pouco… Onde está o Elverdelando (que nominho, hein?!).”

Lembra que dissemos ao banco “me traga o nome e os números dos telefones da pessoa onde o número da pessoa em TELEFONE seja igual ao número da pessoa em PESSOA”? Então! O Elverdelando não esta na tabela TELEFONE, por isso não foi retornado no resultado.

Quando você precisar retornar os dados, mesmo não estando na tabela filha, usamos o OUTER JOIN.

OUTER JOIN

A clásula OUTER JOIN tem duas variantes, LEFT OUTER JOIN e RIGHT OUTER JOIN, sendo que estas cláusula devem ser declaradas sempre de maneira explícita. Alguns SGBDs facilitam a utilização do OUTER JOIN te polpando de declarar toda a sintaxe, como é o caso do Oracle que utiliza o símbolo (+) na identificação fraca, mas NÃO É ANSI, ou seja, não funcionará em qualquer SGBD.

Para que apareçam todas as pessoas, inclusive as que não têm número de telefone cadastrado usamos LEFT OUTER JOIN ou RIGHT OUTER JOIN.

A sintaxe é parecida com a INNER JOIN, mudando apenas o nome da cláusula: LEFT[RIGHT] OUTER JOIN <tabela filha> ON <atributos de identificação>;

Assim, faremos…

SELECT nome_completo, num_telefone FROM pessoa LEFT OUTER JOIN telefone ON pessoa.num_pessoa = telefone.num_pessoa

ou então

SELECT nome_completo, num_telefone FROM telefone RIGHT OUTER JOIN pessoa ON pessoa.num_pessoa = telefone.num_pessoa

Em ambos os casos o resultado será:

 +-----------------------------+
 |         CONTATO             |
 +--------------+--------------+
 | NOM_PESSOA   | NUM_TELEFONE |
 | José         | 6589-3666    |
 | Maria        | 9888-6699    |
 | Maria        | 8956-6666    |
 | Arnaldo      | 2888-9877    |
 | Elverdelando |              |
 +--------------+--------------+

Como você pôde perceber, a única diferença entre o LEFT OUTER JOIN e o RIGHT OUTER JOIN é só a indicação de qual é o lado forte do relacionamento, ou seja, em qual lado da cláusula está a tabela que pode não ter valores na tabela filha. No nosso caso, ambos apontando para a tabela PESSOA.

INNER JOIN é muito usado para consulta em banco de dados, quase que totalmente em seu método implícito. Já as cláusulas OUTER JOIN também são usadas, mas não com tanta frequência, posso dizer que é raro encontra-las por aí.

Espero que este tutorial os ajude!

Como prometido há muito tempo atrás (e bota tempo nisso) no tópico “Passando Objetos Java para uma Procedure do Oracle“, mostrarei como recuperar objetos Java contendo uma coleção de outro objetos através de uma procedure do Oracle. Para quem não leu, é altamente recomendado ler o post anterior.

Para este tutorial, precisamos incluir a tabela TBL_CLASS e adicionar sua primary key como foreign key na TBL_USER.

--num class será a PK e o desc_class será a descrição
create table TBL_CLASS (num_class number, desc_class varchar(100));
alter table TBL_CLASS add primary key(num_class);

alter table TBL_USER add num_class number;
alter table TBL_USER add constraint FK_CLASS foreign key(num_class) references tbl_class(num_class);

Agora precisamos incluir os novos tipos:

create or replace type class_type as object (num_class number, desc_class varchar2(100), users arr_users);
/
create or replace type arr_class as table of class_type;
/

O tipo class_type será o objeto. Repare que na sua assinatura incluímos o tipo arr_users, que será nossa coleção de user_type (vide post anterior), o tipo arr_class será uma coleção de class_type.

Agora incluiremos a procedure responsável por retornar nossa coleção de class_type.

procedure pro_select_class(clas in class_type, class_return in out arr_class)is
  class_ref_cur ref_cur;
  --Coleção de classes
  classes arr_class := arr_class();

  begin
    open class_ref_cur for
      select cast(
                multiset(
                  select num_class,
                         desc_class,
                         (select cast(
                                  multiset(
                                    select user_name,
                                           height,
                                           b_date
                                    from tbl_user
                                    --Fazendo o JOIN com a TBL_USER
                                    where tbl_user.num_class = tbl_class.num_class
                                  ) as arr_users)
                          from dual) users
                  from tbl_class
                  --Usando o atributo num_class do parâmetro de entrada
                  where num_class = clas.num_class) as arr_class
      ) classes
    from dual;

    --incluindo ao retorno no array
    fetch class_ref_cur into classes;
    --passando o array para a variável out
    class_return := classes;
end pro_select_class;

Repare que a procedure recebe o tipo class_type como parâmetro in e retorna o tipo arr_class.

Separando trecho responsável por resgatar e montar nossos objetos teremos:

--Montará a coleção de retorno
select cast(
        multiset(

          --Retornará os objetos class_type e seus atributos
          select num_class,
                 desc_class,

                 --Populará a coleção com user_type
                 (select cast(
                          multiset(
                            select user_name,
                                   height,
                                   b_date
                            from tbl_user
                            where tbl_user.num_class = tbl_class.num_class
                          ) as arr_users)

                  from dual) users

          from tbl_class
          where num_class = clas.num_class) as arr_class
) classes
from dual;

Os objetos de banco estão prontos, agora para o Java!

Criaremos o objeto que será interpretado pelo Oracle. Vamos chamá-lo de TypeClass:

public class TypeClass implements SQLData{
	public static final String ORACLE_OBJECT_NAME = "CLASS_TYPE"; //Nome do tipo no Oracle
	public static final String ORACLE_CLASS_ARRAY_NAME = "ARR_CLASS"; //Nome do array no Oracle

        //campos criados na tabela TBL_CLASS
	private Long number;
	private String desc;
	private Array users; //Esta será a coleção de user_type (ou TypeUser no Java)

	public String getSQLTypeName() throws SQLException {
		return ORACLE_OBJECT_NAME;
	}

	public void readSQL(SQLInput stream, String typeName) throws SQLException {
		setNumber(stream.readLong());
		setDesc(stream.readString());
		setUsers(stream.readArray());//Usado pelo JDBC driver para ler a coleção
	}

	public void writeSQL(SQLOutput stream) throws SQLException {
		stream.writeLong(getNumber());
		stream.writeString(getDesc());
		stream.writeArray(getUsers());//Usado pelo JDBC driver para armazenar a coleção
	}
	//Getters e setters omitidos
}

Precisamos mapear os dois tipos já que ambos serão interpretados na requisição, desta forma:

Map> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);
typeMaps.put(TypeClass.ORACLE_OBJECT_NAME, TypeClass.class);

Também precisaremos mapear os dois arrays:

typeMaps.put(TypeClass.ORACLE_CLASS_ARRAY_NAME, TypeClass[].class);//retornado pela procedure
typeMaps.put(TypeUser.ORACLE_USER_ARRAY_NAME, TypeUser[].class);//retornado pela coleção de class_type

Para a requisição faremos:

cs = conn.prepareCall("{call PAC_BEAN.PRO_SELECT_CLASS(?,?)}");
//registrando o tipo de saída, que será um array de TypeClass
cs.registerOutParameter("class_return", OracleTypes.ARRAY, TypeClass.ORACLE_CLASS_ARRAY_NAME);

//passando o objeto de parâmetros da query
cs.setObject("clas", classQry);

cs.execute();
//recuperando e iterando o array de TypeClass
Object[] array = (Object[])cs.getArray("class_return").getArray();

for(Object obj : array){
	TypeClass objClass = ((TypeClass)obj);

	System.out.println("Description: "+objClass.getDesc());

        //Aqui pegamos a coleção (ou array) de user_type(TypeUser) retornada pela query.
	Object[] userArray = (Object[])objClass.getUsers().getArray();
	for(Object user : userArray){
		System.out.println("\tName: " + ((TypeUser)user).getName());
		System.out.println("\tHeight: " + ((TypeUser)user).getHeight());
		System.out.println("\tBirth: " + sdf.format(((TypeUser)user).getBirth())+ "\r\n");
	}
}

O final você terá um TypeClass que contém um java.sql.Array de TypeUser no atributo getUsers().

E aqui cumpro minha promessa. Baixe o código fonte com o exemplo contendo este e o post anterior aqui.

Até a próxima!