Os exemplos utilizados ao longo do texto, salvo menção em contrário, foram adaptados de GROFF, J. R., WEINBERG, P. N. e OPPEL, A. J. SQL: The Complete Reference, relacionado nas referências bibliográficas. ****Os scripts para criação e população das tabelas encontram-se no material de apoio.
De forma bastante simplista e resumida, cursores (cursors) são estruturas onde são armazenados os resultados e informações relativos à execução de comandos DML. Há dois tipos de cursores: implícitos e explícitos. Cursores implícitos são automaticamente criados e gerenciados pelo SGBDR quando são executados os comandos INSERT, UPDATE, DELETE, SELECT INTO ou SELECT BULK COLLECT INTO. Embora o SGDBR Oracle crie cursores implícitos para todos comandos DML, sua utilização mais comum é a partir das duas últimas formas do comando SELECT. A seguir, alguns exemplos de cursores implícitos.
Após a execução, o resultado exibido é:
No exemplo anterior, foram apresentadas duas formas distintas, porém equivalentes, de utilização dos resultados de uma consulta em um programa PL/SQL. Em ambas, o resultado consistia em uma única linha. Um erro ocorreria se a consulta retornasse mais linhas.
Na primeira forma, os valores de quatro colunas foram atribuídos a quatro variáveis. Observe que tanto o número quanto o tipo das variáveis devem ser iguais (ou equivalentes, no caso do tipo) aos das colunas selecionadas. Na segunda, uma linha inteira foi atribuída uma variável tipo RECORD, com estrutura idêntica à da tabela.
Os dois erros mais comuns que podem ocorrer com cursores implícitos são ORA-01403 data not found (a consulta não retornou qualquer linha) e ORA-01422 exact fetch returns more than requested number of rows (a consulta retornou mais de uma linha). É aconselhável que ambos os casos sejam tratados dentro do próprio procedimento. No exemplo a seguir, a procedure retorna o nome da filial cujo rep_id do gerente é passado como parâmetro.
Após a execução, o resultado exibido é:
PL/SQL permite tratar resultados de consultas com múltiplas linhas. As cláusulas BULK COLLECT INTO (SELECT) e FORALL (INSERT, UPDATE e DELETE) permitem tratar estes casos. Uma versão da procedure DADOS_FILIAL, alterada para tratar os casos em que um representante gerencia mais de uma filial, é mostrada a seguir.
Das linhas 1 a 4 é criado o tipo tipo_dadosfiliais, uma tabela aninhada de registros, que receberá o resultado da consulta. Este tipo é global ao esquema. O parâmetro de saída dados_filiais (linha 8), agora pode receber 0 ou mais linhas de resultado. Observe que o tipo é qualificado. O comando SELECT BULK COLLECT INTO, nas linhas 14 e 15, atribui o resultado da consulta na tabela aninhada dados_filiais. Por se tratar de um cursor implícito, não são necessárias a inicialização ou inserção de elementos em dados_filiais. Isto é feito automaticamente após a execução da consulta. Observe também que não é mais necessário incluir tratamento para os erros ORA-01403 e ORA-01422. Para saber a quantidade de linhas retornadas pela consulta, utiliza-se o atributo COUNT. **Após a execução, o resultado exibido é:
É possível limitar o número de linhas atribuídas pelo comando SELECT BULK COLLECT INTO colocando-se a cláusula LIMIT n ao final do comando, onde n é o número máximo desejado.
O comando FORALL permite que múltiplas linhas sejam inseridas, excluídas ou alteradas de uma só vez. Sua forma geral é:
