Cursor PL / SQL (Português)

Resumo: neste tutorial, você aprenderá sobre o cursor PL / SQL e seu uso.

Um cursor é um ponteiro que aponta para o resultado de uma consulta. PL / SQL tem dois tipos de cursores: cursores implícitos e cursores explícitos.

Cursores implícitos

Sempre que o Oracle executa uma instrução SQL, como SELECT INTO, INSERT, UPDATE e DELETE, ele cria automaticamente um cursor implícito.

O Oracle gerencia internamente todo o ciclo de execução de cursores implícitos e revela apenas as informações e status do cursor, como SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND e SQL%NOTFOUND.

O cursor implícito não é elegante quando a consulta retorna zero ou várias linhas que causa NO_DATA_FOUND ou TOO_MANY_ROWS exceção respectivamente.

Cursores explícitos

Um cursor explícito é uma instrução SELECT declarada explicitamente na seção de declaração do bloco atual ou uma especificação de pacote.

Para um cursor explícito, você tem cont role sobre seu ciclo de execução de OPEN, FETCH e CLOSE.

O Oracle define um ciclo de execução que executa uma instrução SQL e associa um cursor a ela.

A ilustração a seguir mostra o ciclo de execução de um cursor explícito:

Vamos examinar cada etapa em detalhes.

Declarar um cursor

Antes de usar um cursor explícito, você deve declará-lo na seção de declaração de um bloco ou pacote da seguinte maneira:

Nesta sintaxe :

• Primeiro, especifique o nome do cursor após a CURSOR palavra-chave.
• Segundo, defina uma consulta para buscar dados após a IS palavra-chave.

Abrir um cursor

Antes de começar a buscar linhas do cursor, você deve abri-lo. Para abrir um cursor, use a seguinte sintaxe:

Nesta sintaxe, o cursor_name é o nome do cursor declarado na seção de declaração.

Quando você abre um cursor, o Oracle analisa a consulta, vincula variáveis e executa a instrução SQL associada.

O Oracle também determina um plano de execução , associa variáveis de host e parâmetros de cursor aos marcadores de posição na instrução SQL, determina o conjunto de resultados e define o cursor para a primeira linha do conjunto de resultados.

Mais sobre o cursor parametrizado no próximo tutorial.

Buscar de um cursor

A instrução FETCH coloca o conteúdo da linha atual em variáveis. A sintaxe da instrução FETCH é a seguinte:

Para recuperar todas as linhas em um conjunto de resultados, você precisa para buscar cada linha até a última.

Fechando um cursor

Depois de buscar todas as linhas, você precisa fechar o cursor com o CLOSE instrução:

Fechar um cursor instrui o Oracle a liberar a memória alocada em um momento apropriado.

Se você declarar um cursor em um bloco anônimo, procedimento ou função, o cursor será fechado automaticamente quando a execução desses objetos terminar.

No entanto, você deve fechar explicitamente os cursores baseados em pacote. Observe que se você fechar um cursor que ainda não foi aberto, o Oracle levantará uma INVALID_CURSOR exceção.

Atributos explícitos do cursor

Um cursor tem quatro atributos aos quais você pode fazer referência no seguinte formato:

onde cursor_name é o nome do cursor explícito.

1)% ISOPEN

Este atributo é TRUE se o cursor estiver aberto ou FALSE se não for.

2)% FOUND

Este atributo tem quatro valores:

• NULL antes da primeira busca
• TRUE se um registro foi buscado com sucesso
• FALSE se nenhuma linha retornada
• INVALID_CURSOR se o cursor não estiver aberto

3)% NOTFOUND

Este atributo tem quatro valores:

• NULL antes da primeira busca
• FALSE se um registro foi obtido com sucesso essencialmente
• TRUE se nenhuma linha for retornada
• INVALID_CURSOR se o cursor não estiver aberto

3)% ROWCOUNT

O atributo %ROWCOUNT retorna o número de linhas obtidas do cursor. Se o cursor não estiver aberto, este atributo retorna INVALID_CURSOR.

Exemplo de cursor PL / SQL

Usaremos as tabelas orders e order_items de o banco de dados de amostra para a demonstração.

A declaração a seguir cria uma visão que retorna as receitas de vendas por clientes:

Os valores da coluna de crédito são 5% das receitas totais de vendas.

Suponha que você precise desenvolver um bloqueie que:

1. Redefina os limites de crédito de todos os clientes para zero.
2. Busque clientes classificados por vendas em ordem decrescente e dê a eles novos limites de crédito de um orçamento de 1 milhão.

O seguinte bloco anônimo ilustra a lógica:

Na seção de declaração, declaramos três variáveis.

A primeira é l_budget cujo valor inicial é 1,000,000.

A segunda variável é uma variável de cursor explícita chamada c_sales cujo a instrução recupera dados da sales view:

A terceira variável é um registro baseado em cursor denominado c_sales.

Na seção de execução, fazemos o seguinte:

• Primeiro, redefina os limites de crédito de todos os clientes a zero usando uma instrução UPDATE.
• Em segundo lugar, abra o c_sales cursor.
• Terceiro, busque cada linha do cursor. Em cada iteração do loop, atualizamos o limite de crédito e reduzimos o orçamento. O loop termina quando não há linha para buscar ou o orçamento se esgota.
• Por fim, feche o cursor.

A consulta a seguir recupera dados do customers tabela para verificar a atualização:

Resultado:

Como você pode ver claramente no resultado, apenas os primeiros clientes têm limites de crédito. Se você somar todos os limites de crédito, o total deve ser 1 milhão, conforme mostrado a seguir:

Agora , você deve entender os cursores PL / SQL, incluindo cursores implícitos e explícitos, e como usá-los efetivamente para processar dados, linha por linha, de uma tabela.