PLSQL ADVANCED 19C 23ai

PL/SQL significa Linguagem Procedural - Linguagem de Consulta Estruturada (PL/SQL).

Faz parte do produto Oracle Database, o que significa que não é

necessária instalação separada. É comumente usada para traduzir a lógica de negócios no banco de dados e expor

a camada de interface do programa para o aplicativo. Enquanto SQL é puramente uma linguagem de acesso

a dados que interage diretamente com o banco de dados, PL/SQL é uma linguagem de

programação na qual múltiplos SQLs e instruções procedurais podem ser agrupados em

uma unidade de programa. O código PL/SQL é portátil entre bancos de dados Oracle (sujeito a

limitações impostas pelas versões). O otimizador de banco de dados integrado refatora o código

para melhorar o desempenho da execução.

As vantagens da linguagem PL/SQL são as seguintes:

• PL/SQL suporta todos os tipos de instruções SQL, tipos de dados, SQL estático e SQL dinâmico

• O código PL/SQL é executado em todas as plataformas suportadas pelo Banco de Dados Oracle

• O desempenho do código PL/SQL pode ser aprimorado com o uso de variáveis ​​de ligação em consultas SQL diretas

• PL/SQL suporta o modelo orientado a objetos do Banco de Dados Oracle

• Os aplicativos PL/SQL aumentam a escalabilidade, permitindo que vários usuários invoquem a mesma unidade de programa

Escrever SQL em PL/SQL é uma das partes críticas da programação de banco de dados.

Todas as instruções SQL incorporadas em um bloco PL/SQL são executadas como um cursor. Um cursor é uma área de memória privada,

alocada temporariamente na Área Global do Usuário (UGA) da sessão, usada para processar instruções SQL.

A memória privada armazena o conjunto de resultados recuperado da execução SQL e dos atributos do cursor.

Os cursores podem ser classificados como cursores implícitos e explícitos. O Oracle cria um cursor implícito

para todas as instruções SQL incluídas na seção executável de um bloco PL/SQL. Nesse caso, o ciclo de vida do cursor é mantido pelo Banco de Dados Oracle. Para cursores explícitos, o ciclo de execução pode ser controlado pelo usuário.

Os desenvolvedores de banco de dados podem declarar explicitamente um cursor implícito na seção DECLARE,  juntamente com uma consulta SELECT.

Tratamento de exceções em PL/SQL

Se um programa apresentar um fluxo incomum e inesperado durante a execução, o que

pode resultar em um encerramento anormal do programa, a situação é considerada uma

exceção. Tais erros devem ser capturados e tratados na seção EXCEPTION do

bloco PL/SQL. Os manipuladores de exceções podem suprimir o encerramento anormal com

uma ação alternativa e segura.

O tratamento de exceções é uma das etapas importantes da programação de banco de dados.

Exceções não tratadas podem resultar em interrupções não planejadas de aplicativos, impactar a

continuidade dos negócios e frustrar os usuários finais.

Existem dois tipos de exceções: definidas pelo sistema e definidas pelo usuário. Enquanto o

Banco de Dados Oracle gera implicitamente uma exceção definida pelo sistema, uma exceção definida pelo usuário

é explicitamente declarada e gerada dentro da unidade do programa.

Além disso, a Oracle fornece duas funções utilitárias, SQLCODE e SQLERRM, para recuperar

o código de erro e a mensagem da exceção mais recente.

Exceções definidas pelo sistema

Como o nome indica, as exceções definidas pelo sistema são definidas e mantidas

implicitamente pelo Banco de Dados Oracle. Elas são definidas no pacote Oracle STANDARD.

Sempre que ocorre uma exceção dentro de um programa, o banco de dados seleciona

a exceção apropriada da lista disponível. Todas as exceções definidas pelo sistema são

associadas a um código de erro negativo (exceto de 1 a 100) e a um nome curto, que é

usado ao especificar os manipuladores de exceção.

Por exemplo, o programa PL/SQL a seguir inclui uma instrução SELECT para selecionar

os detalhes do funcionário 108. Ele gera a exceção NO_DATA_FOUND porque o ID do funcionário

108 não existe.

O RAISE_APPLICATION_ERROR é um procedimento fornecido pela Oracle que gera uma exceção definida pelo usuário

com uma mensagem de exceção personalizada. A exceção pode ser pré-definida opcionalmente na seção declarativa do PL/SQL.

A sintaxe do procedimento RAISE_APPLICATION_ERROR é a seguinte:

RAISE_APPLICATION_ERROR (número_do_erro, mensagem_do_erro[, {TRUE |

FALSE}])

Nesta sintaxe, o parâmetro número_do_erro é obrigatório, com o valor do erro variando entre 20000 e 20999.

mensagem_do_erro é a mensagem definida pelo usuário que aparece junto com a exceção.

O último parâmetro é um argumento opcional usado para adicionar o código de erro da exceção à pilha de erros atual.

O RAISE_APPLICATION_ERROR é um procedimento fornecido pela Oracle que gera uma exceção definida pelo usuário

com uma mensagem de exceção personalizada. A exceção pode ser pré-definida opcionalmente na seção declarativa do PL/SQL.

A sintaxe do procedimento RAISE_APPLICATION_ERROR é a seguinte:

RAISE_APPLICATION_ERROR (número_do_erro, mensagem_do_erro[, {TRUE |

FALSE}])

Nesta sintaxe, o parâmetro número_do_erro é obrigatório, com o valor do erro variando entre 20000 e 20999.

mensagem_do_erro é a mensagem definida pelo usuário que aparece junto com a exceção.

O último parâmetro é um argumento opcional usado para adicionar o código de erro da exceção à pilha de erros atual.

O programa PL/SQL a seguir lista os funcionários que ingressaram na organização após a data fornecida.

O programa deve gerar uma exceção se a data de admissão for anterior à data fornecida.

O bloco usa RAISE_APPLICATION_ERROR para gerar a exceção com o código de erro 20005,

e uma mensagem de erro correspondente aparece na tela:

No exemplo anterior, observe que o nome da exceção não é usado para criar o

manipulador de abordagens. Logo após a exceção ser gerado por meio de RAISE_APPLICATION_

ERRO, o programa está encerrado.

Se você desejar ter um manipulador de abordagens específico para as abordagens propostas por meio de

RAISE_APPLICATION_ERROR, você deve declarar a exceção na seção declarativa

e associe o número do erro usando PRAGMA EXCEPTION_INIT. Verifique o

seguinte programa PL/SQL:

O que é Propagação de Exceções em PL/SQL?

Propagação de exceções em PL/SQL refere-se ao processo em que uma exceção não tratada em um bloco PL/SQL é passada automaticamente para o bloco que a envolve ou para o ambiente de chamada . Suponha que uma exceção ocorra em um bloco, mas não haja um manipulador para essa exceção.

Nesse caso, ele se propaga para o próximo bloco externo e o processo se repete até que a exceção seja tratada por um manipulador apropriado ou chegue ao ambiente host . Se nenhum manipulador for encontrado , uma mensagem de erro é retornada ao usuário ou ao programa chamador .

Exemplo 1: Propagação de exceção com a exceção predefinida (ZERO_DIVIDE)

Exceções predefinidas em PL/SQL são exceções internas geradas pelo tempo de execução PL/SQL quando ocorrem condições de erro específicas do Oracle . Neste exemplo, ilustraremos como uma exceção predefinida, , é propagada por blocos aninhados.ZERO_DIVIDE

Explicação:

• No bloco interno , a divisão por zero gera a exceção ZERO_DIVIDE , que não é tratada no bloco interno.

• Ele se propaga para o bloco externo, onde o manipulador ZERO_DIVIDE o captura.

• A mensagem "Bloco externo: erro de divisão por zero detectado" é impressa.

Exemplo 2: Propagando uma exceção definida pelo usuário

A exceção definida pelo usuário no PL/SQL é a exceção personalizada que é explicitamente declarada e gerada pelo desenvolvedor para lidar com condições de erro específicas que não são cobertas pelas exceções predefinidas .

Exceções definidas pelo usuário permitem que os desenvolvedores definam e manipulem seus próprios cenários de erro com base na lógica de negócios ou nos requisitos personalizados .

Explicação:

• O bloco interno gera a exceção definida pelo usuário ( e_custom_exception ), mas não há nenhum manipulador para ela no bloco interno.

• A exceção é propagar o bloco externo, onde ele é capturado pelo manipulador e_custom_exception .

• A mensagem "Bloco externo: exceção personalizada capturada." é impressa.

Conclusão

Concluindo, a propagação de exceções em PL/SQL é um mecanismo poderoso que aumenta a robustez e a confiabilidade dos aplicativos de banco de dados . Ao permitir que exceções se propaguem por meio de blocos aninhados , os desenvolvedores podem criar estratégias flexíveis de tratamento de erros .

Isso evita que erros sejam ignorados, facilita a organização do código e ajuda a capturar condições inesperadas de tempo de execução. Entender a propagação de exceções é crucial para o desenvolvimento de programas PL/SQL resilientes que possam lidar com erros com elegância , resultando em aplicativos de banco de dados mais confiáveis ​​e fáceis de manter .

Em bancos de dados Oracle, a dependência direta e a dependência indireta entre objetos se referem à maneira como objetos (como views, procedures, functions, triggers, etc.) estão inter-relacionados.

? Definições rápidas:

Dependência direta: Quando um objeto usa diretamente outro.

Dependência indireta: Quando um objeto usa outro que, por sua vez, usa um terceiro.

Unidades de programa PL/SQL, bem como outros objetos de banco de dados, como visualizações, podem se referir a

outros objetos de banco de dados em sua seção procedural. Diz-se que a unidade de programa que faz a chamada

é dependente das unidades de programa chamadas (conhecidas como objetos referenciados). Se EMP

e DEPT são as tabelas base usadas na criação de uma visualização V_EMP_REP, então a visualização é

dependente de EMP e DEPT.

Uma sequência sempre pode ser um objeto referenciado. Um corpo de pacote

é sempre um objeto dependente.

A dependência de banco de dados pode ser classificada como direta ou indireta. Considere três

objetos — P, M e N. Se o objeto P referencia o objeto M e o objeto M referencia o objeto

N, então P é diretamente dependente de M e indiretamente dependente de N.

SQL é a linguagem de acesso a dados mais utilizada, enquanto PL/SQL é uma linguagem popular que pode se integrar perfeitamente com comandos SQL. O maior benefício de executar PL/ SQL é que o processamento do código ocorre nativamente no Oracle Database.

No passado, houve debates e discussões sobre programação do lado do servidor enquanto o cliente invocava as rotinas PL/SQL para executar uma tarefa. A abordagem de programação do lado do servidor tem muitos benefícios. Ela reduz as viagens de ida e volta da rede entre o cliente e o banco de dados. Ela reduz o tamanho do código e facilita a portabilidade do código, pois o PL/SQL pode ser executado em todas as plataformas, onde quer que o Oracle Database seja suportado.

O Oracle Database 12c apresenta muitos recursos e aprimoramentos de linguagem que se concentram na integração de SQL com PL/SQL, migração de código e conformidade com ANSI. Esta seção discute os novos recursos de SQL e PL/SQL no Oracle Database 12c.

Colunas IDENTITY

O Oracle Database 12c introduz colunas de identidade em SQL em conformidade com o padrão SQL do American National Standard Institute (ANSI). Uma coluna de tabela, marcada como IDENTITY, gera automaticamente um valor numérico incremental no momento da criação do registro. Antes do lançamento do Oracle 12c, os desenvolvedores precisavam criar uma sequência adicional no esquema e atribuir seu valor à coluna por meio de um gatilho ou em um bloco PL/SQL.

O novo recurso simplifica a escrita de código e beneficia a migração de um banco de dados não Oracle para Oracle.

Nota sobre IDENTITY: A cláusula GENERATED ALWAYS AS IDENTITY cria uma coluna com incremento automático (similar a AUTO_INCREMENT do MySQL ou SERIAL do PostgreSQL). Você também pode usar GENERATED BY DEFAULT.

A diferença entre GENERATED ALWAYS AS IDENTITY e GENERATED BY DEFAULT AS IDENTITY em colunas IDENTITY no Oracle é sutil, mas importante, especialmente em cenários onde você quer (ou não quer) controlar manualmente os valores da chave primária.

GENERATED ALWAYS AS IDENTITY. O gerador de sequências sempre fornece um valor de IDENTIDADE. Não é possível especificar um valor para a coluna.

GENERATED BY DEFAULT AS IDENTITY. O gerador de sequências fornece um valor de IDENTIDADE sempre que você não fornecer um valor de coluna.

GENERATED BY DEFAULT ON NULL AS IDENTITY. O gerador de sequências fornece o próximo valor de IDENTIDADE se você especificar um valor de coluna NULO.

VIEW user_tab_identity_cols

Column Datatype NULL Description

OWNER

VARCHAR2(128)

NOT NULL

Owner of the table

TABLE_NAME

VARCHAR2(128)

NOT NULL

Name of the table

COLUMN_NAME

VARCHAR2(128)

NOT NULL

Name of the identity column

GENERATION_TYPE

VARCHAR2(10)

Generation type of the identity column. Possible values are ALWAYS or BY DEFAULT.

SEQUENCE_NAME

VARCHAR2(128)

NOT NULL

Name of the sequence associated with the identity column

IDENTITY_OPTIONS

VARCHAR2(298)

Options for the identity column sequence generator

Quando usar cada um?

Caso de uso

Melhor opção

Você nunca quer que o usuário defina o ID manualmenteGENERATED ALWAYS

Você quer flexibilidade para às vezes definir o ID manualmente (ex: migração de dados)GENERATED BY DEFAULT

A cláusula DEFAULT ON NULL no Oracle PL/SQL é uma funcionalidade que define um valor padrão para uma coluna apenas quando o valor inserido for explicitamente NULL. Isso difere do comportamento padrão da cláusula DEFAULT, que não é aplicada se você passar um valor NULL explicitamente — somente se a coluna for omitida na inserção.

Comportamento:

· Se você omitir a coluna: o valor padrão é usado.

· Se você informar NULL explicitamente: o valor padrão também é usado, somente por causa do ON NULL.

Isso é útil para evitar valores nulos em colunas críticas, mesmo quando o NULL é passado diretamente

Vantagens de usar DEFAULT ON NULL

Vantagem

Descrição

✅ Previne valores NULL indesejados

Mesmo que alguém passe NULL, você pode forçar um valor padrão.

✅ Reduz a necessidade de triggers ou validações

Sem necessidade de lógica extra para tratar NULL.

✅ Mantém consistência de dados

Especialmente útil para colunas financeiras ou flags.

✅ Facilita integrações

Se o sistema externo envia NULL, o banco lida com isso automaticamente.

VARCHAR2 é um tipo de dado usado no Oracle para armazenar cadeias de texto de comprimento variável. Diferente de CHAR, ele não preenche com espaços em branco.

? 2. Limites do VARCHAR2

Em colunas de tabela 4000 bytes (ou caracteres)Até 32.767 bytes (com configuração especial)

Em PL/SQL (variáveis)32.767 caracteres— (limite padrão desde sempre)

Restrições :

Índices sobre colunas grandes Limitado a ~4000 bytes

DISTINCT, GROUP BY, ORDER BY Pode falhar com colunas muito longas

Performance Colunas grandes impactam I/O e memória

Ferramentas de terceiros Podem não suportar colunas > 4000 chars

O recurso pode ser controlado usando o parâmetro de inicialização MAX_STRING_SIZE. Ele

aceita dois valores:

• STANDARD (padrão) — O tamanho máximo anterior ao lançamento do Oracle

Database 12c será aplicado.

• EXTENDED — O novo limite de tamanho para tipos de dados de string será aplicado. Observe que, após o

parâmetro ser definido como EXTENDED, a configuração não poderá ser revertida.

As etapas para aumentar o tamanho máximo de string em um banco de dados são:

1. Reinicie o banco de dados no modo UPGRADE. No caso de um banco de dados plugável,

o PDB deve ser aberto no modo MIGRATE.

2. Use o comando ALTER SYSTEM para definir MAX_STRING_SIZE como EXTENDED.

3. Como SYSDBA, execute o script $ORACLE_HOME/rdbms/admin/utl32k.sql.

O script é usado para aumentar o limite máximo de tamanho de VARCHAR2,

NVARCHAR2 e RAW sempre que necessário.

4. Reinicie o banco de dados no modo NORMAL.

5. Como SYSDBA, execute utlrp.sql para recompilar os objetos de esquema com

status inválido.

Os pontos a serem considerados ao trabalhar com o suporte de 32k para tipos de string são:

• COMPATIBLE deve ser 12.0.0.0

• Após o parâmetro ser definido como EXTENDED, o parâmetro não pode ser revertido

para STANDARD

• Em ambientes RAC, todas as instâncias do banco de dados estão em conformidade com a

configuração de MAX_STRING_SIZE

Para consultas Top-N, o Oracle Database 12c introduz uma nova cláusula, FETCH FIRST, para

simplificar o código e cumprir as diretrizes do padrão ANSI SQL. A cláusula é

usada para limitar o número de linhas retornadas por uma consulta. A nova cláusula pode ser usada em

conjunto com ORDER BY para recuperar resultados Top-N.

A cláusula de limitação de linhas pode ser usada com a cláusula FOR UPDATE em uma consulta SQL. No

caso de uma visualização materializada, a consulta de definição não deve conter a cláusula FETCH.

Outra nova cláusula, OFFSET, pode ser usada para pular os registros do topo ou do meio,

antes de limitar o número de linhas. Para resultados consistentes, o valor de deslocamento deve ser

um número positivo, menor que o número total de linhas retornadas pela consulta. Para todos os

outros valores de deslocamento, o valor é contado como zero.

Palavras-chave com a cláusula FETCH FIRST são:

• FIRST | NEXT — Especifique FIRST para iniciar a limitação de linhas a partir do topo. Use NEXT

com OFFSET para pular determinadas linhas.

• ROWS | PERCENT — Especifique o tamanho do conjunto de resultados como um número fixo de linhas

ou porcentagem do número total de linhas retornadas pela consulta.

• ONLY | WITH TIES — Use ONLY para fixar o tamanho do conjunto de resultados, independentemente de

chaves de classificação duplicadas. Se desejar todos os registros com chaves de classificação correspondentes,

especifique WITH TIES

O Oracle Database suporta colunas invisíveis, o que implica que um usuário pode

controlar a visibilidade de uma coluna. Uma coluna marcada como invisível não aparece

nas seguintes operações:

• Consultas SELECT * FROM na tabela

• Comando SQL*Plus DESCRIBE

• Registros locais de %ROWTYPE

• Descrição da Oracle Call Interface (OCI)

Uma coluna pode ser tornada invisível especificando a cláusula INVISIBLE na

coluna. Colunas de todos os tipos (exceto tipos definidos pelo usuário), incluindo colunas virtuais,

podem ser marcadas como invisíveis, desde que as tabelas não sejam tabelas temporárias, externas

ou agrupadas. A instrução SELECT pode selecionar explicitamente uma coluna invisível.

Da mesma forma, a instrução INSERT não inserirá valores em uma coluna invisível, a menos que

explicitamente especificado.

Além disso, uma tabela pode ser particionada com base em uma coluna invisível. Uma coluna

mantém seu recurso de nulidade mesmo depois de se tornar invisível. Uma coluna invisível pode

se tornar visível, mas a ordem da coluna na tabela pode mudar

✅ Vantagens

• Evita quebra de aplicações legadas: você pode adicionar novas colunas sem impactar aplicações que usam SELECT *.

• Segurança / Auditoria: dados podem ser acessados apenas por usuários ou processos que saibam da existência da coluna.

• Transição de dados: facilita migrações ou testes de novas colunas sem expor para todo o sistema.

❌ Desvantagens

• Manutenibilidade: pode causar confusão se os desenvolvedores não souberem da existência dessas colunas.

• Ferramentas de BI ou ORM podem não detectar a coluna se ela for invisível.

• Complexidade desnecessária se não houver necessidade real de ocultação.

? Conclusão

As colunas invisíveis são úteis para:

• controle interno,

• auditoria,

• ou evolução de esquemas sem quebrar integrações.

Mas devem ser usadas com critério para evitar confusão e problemas de manutenção. Em sistemas com grande dependência de SELECT *, elas são uma ferramenta estratégica poderosa.

Bancos de dados temporais foram lançados como um novo recurso no ANSI SQL:2011. O termo

dados temporais pode ser entendido como uma informação que pode ser associada

a um período dentro do qual a informação é válida. Antes da inclusão do recurso

no Oracle Database , os dados cuja validade está vinculada a um período de tempo precisavam ser

manipulados pelo aplicativo ou usando vários predicados nas consultas. O Oracle

12c herda parcialmente o recurso do padrão ANSI SQL:2011 para oferecer suporte a

entidades cuja validade comercial pode ser delimitada por uma dimensão de tempo.

O recurso de banco de dados temporal no Oracle Database é diferente do recurso de

recall total no Oracle Database 11g. O recurso de recall total registra o tempo de transação

dos dados no banco de dados para garantir a validade da transação e não a

validade funcional. Por exemplo, um plano de investimento está ativo entre janeiro

e dezembro. A data registrada no banco de dados no momento do carregamento dos dados é o

carimbo de data/hora da transação.

Eenomeado como Flashback Data Archive e disponibilizado

para todas as versões do Oracle Database.

O recurso de tempo válido pode ser habilitado para uma tabela adicionando uma dimensão de tempo

usando a cláusula PERIOD FOR nas colunas de data ou carimbo de data/hora da

tabela. O script a seguir cria uma tabela t_tmp_db com tempo válido

Definir o período de tempo válido como ATUAL significa que todas as tabelas com um período temporal

válido listarão apenas as linhas válidas em relação à data de hoje. Você também pode definir o

período de tempo válido para uma data específica.

Arquivamento no Banco de Dados

O Oracle Database 12c introduz o Arquivamento no Banco de Dados para arquivar os dados de baixa prioridade

em uma tabela. Os dados inativos permanecem no banco de dados, mas não são visíveis para o aplicativo.

Você pode marcar dados antigos para arquivamento, o que não é ativamente necessário no aplicativo,

exceto para fins regulatórios. Embora os dados arquivados não sejam visíveis para o

aplicativo, eles estão disponíveis para consulta e manipulação. Além disso, os dados arquivados

podem ser compactados para melhorar o desempenho do backup.

Uma tabela pode ser habilitada especificando a cláusula ROW ARCHIVAL no nível da tabela,

que adiciona uma coluna oculta ORA_ARCHIVE_STATE à estrutura da tabela. O valor da coluna

deve ser atualizado para marcar uma linha para arquivamento. Por exemplo:

Definindo um subprograma PL/SQL na consulta SELECT e na UDF PRAGMA

O Oracle Database 12c inclui dois novos recursos para aprimorar o desempenho

de funções quando chamadas a partir de instruções SELECT.

Com o Oracle 12c, um subprograma PL/SQL pode ser criado em linha com a consulta SELECT na declaração da cláusula WITH.

A função criada na subconsulta da cláusula WITH não é armazenada no esquema do banco de dados e está disponível

para uso apenas na consulta atual.

Como um procedimento criado na cláusula WITH não pode ser chamado a partir da consulta SELECT,

ele pode ser chamado na função criada na seção de declaração.

O recurso pode ser muito útil em bancos de dados somente leitura,

onde os desenvolvedores não conseguiam criar wrappers PL/SQL.

O Oracle Database 12c adiciona a nova UDF PRAGMA para criar uma função autônoma com o mesmo objetivo.

Anteriormente, as consultas SELECT podiam invocar uma função PL/SQL, desde que a função não alterasse o estado de

pureza do banco de dados.

O desempenho da consulta seria prejudicado

devido à troca de contexto do SQL para o mecanismo PL/SQL (e vice-versa)

e às diferentes representações de memória do tipo de dados nos mecanismos de processamento.

No exemplo a seguir, a função fun_with_plsql calcula a remuneração anual

de um funcionário

ex2

Se a consulta que contém a declaração da cláusula WITH não for uma

instrução de nível superior, a instrução de nível superior deverá usar a dica

WITH_PLSQL. A dica será usada se as instruções INSERT, UPDATE ou DELETE

estiverem tentando usar um SELECT com uma definição de cláusula WITH.

A não inclusão da dica resulta em uma exceção ORA-32034:

uso não suportado da cláusula WITH.

Uma função pode ser criada com a UDF PRAGMA para informar ao compilador que a

função é sempre chamada em uma instrução SELECT. Observe que a função autônoma

criada no código a seguir tem o mesmo nome da do último exemplo.

A declaração da cláusula WITH local tem precedência sobre a função autônoma

no esquema.

objetivo

Como o objetivo do recurso é o desempenho, vamos prosseguir com um estudo de caso para

comparar o desempenho ao usar uma função autônoma, uma função PRAGMA UDF

e uma função declarada com a cláusula WITH.

No Oracle 23c (incluindo a variante Oracle 23ai), a cláusula ACCESSIBLE BY é um novo recurso de segurança PL/SQL que

permite restringir explicitamente quem pode invocar uma procedure, function, package ou trigger.

Essa funcionalidade é particularmente útil para encapsulamento forte e para evitar chamadas indevidas

ou acidentais por outros objetos no banco de dados.

Explicação:

A cláusula ACCESSIBLE BY define explicitamente quais objetos PL/SQL têm permissão de invocação.

Isso ajuda a proteger implementações internas contra chamadas externas indevidas, mesmo que o usuário tenha permissões no banco.

Pode ser usado em: FUNCTION, PROCEDURE, TRIGGER, TYPE, PACKAGE.

link para mais estudos

https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/ACCESSIBLE-BY-clause.html

Antes do Oracle Database 12c, uma unidade PL/SQL criada com os direitos do definidor (padrão

AUTHID) sempre era executada com os direitos do definidor, independentemente de o invocador ter ou não os privilégios

necessários. Isso pode levar a uma situação injusta, em que o usuário que invoca pode

executar operações indesejadas sem precisar do conjunto correto de privilégios. Da mesma forma,

para a unidade de direitos do invocador, se o usuário que invoca possuir um conjunto de privilégios superior

ao do definidor, ele pode acabar executando operações não autorizadas.

O Oracle Database 12c protege os direitos do definidor, permitindo que o usuário definidor conceda

funções complementares a subprogramas e pacotes PL/SQL individuais.

Do ponto de vista da segurança, a concessão de funções a subprogramas em nível de esquema

fornece controle granular, pois os privilégios do invocador são validados no

momento da execução.

No exemplo a seguir, criaremos dois usuários: U1 e U2. O usuário U1 cria um

procedimento PL/SQL P_INC_PRICE que adiciona uma sobretaxa ao preço de um produto em

um determinado valor. U1 concede o privilégio de execução ao usuário U2.

Em um cenário semelhante no passado, os administradores de banco de dados poderiam facilmente ter

concedido privilégios de seleção ou atualização ao U2, o que não é uma solução ideal do ponto de vista da segurança. O Oracle 12c permite que os usuários criem unidades de programa com direitos de invocador,

mas concedem as funções necessárias às unidades de programa e não aos usuários. Portanto, uma unidade com direitos de invocador é executada com os privilégios de invocador, além da função de programa PL/SQL.

Vamos verificar as etapas para criar uma função e atribuí-la ao procedimento. SYSDBA

cria a função e a atribui ao usuário U1. Usar a opção ADMIN ou DELEGATE

com a concessão permite que o usuário conceda a função a outras entidades.

Agora, o usuário U1 atribui o conjunto necessário de privilégios à função. A função é então

atribuída ao subprograma necessário. Observe que apenas funções, e não privilégios

individuais, podem ser atribuídos aos subprogramas em nível de esquema.

O usuário U2 tenta executar o procedimento novamente. O procedimento é executado com sucesso

o que significa que o valor de "Leite" foi aumentado em 5 unidades.

Uma view materializada (ou materialized view) no Oracle é um objeto de banco de dados que armazena fisicamente os

resultados de uma consulta. Diferente de uma view normal, que é apenas uma representação lógica da consulta e

é executada a cada vez que é chamada, a view materializada guarda os dados em disco, podendo ser atualizada

periodicamente ou manualmente.

✅ Vantagens da View Materializada

Melhora de desempenho:

Evita reprocessamento de consultas complexas (com joins, agregações, etc.).

Reduz tempo de resposta, pois os dados já estão armazenados.

Redução de carga no banco de dados:

Consultas pesadas não são recalculadas toda vez que são executadas.

Ideal para ambientes com relatórios e BI (Business Intelligence).

Suporte à replicação:

Pode ser usada para replicar dados entre bancos Oracle (ex: entre matriz e filiais).

Flexibilidade de atualização:

Pode ser atualizada automaticamente (FAST ou COMPLETE), sob demanda (ON DEMAND) ou em tempo real (ON COMMIT, se aplicável).

⚠️ Desvantagens da View Materializada

Dados desatualizados:

Como armazena dados físicos, eles podem ficar desatualizados entre as atualizações.

Pode não refletir imediatamente mudanças nas tabelas base.

Espaço em disco:

Ocupa espaço de armazenamento adicional (diferente de uma view comum).

Custo de manutenção:

Atualizações periódicas (REFRESH) consomem recursos.

Pode haver bloqueios ou atrasos durante o refresh, especialmente em grandes volumes de dados.

Complexidade adicional:

Requer estratégia de atualização adequada (FAST, COMPLETE, FORCE, etc.).

Algumas limitações de uso dependendo do tipo de consulta (por exemplo, funções não determinísticas,

subqueries correlacionadas, etc.).

⚙️ Exemplos de uso comum

Dashboards de BI (relatórios gerenciais).

Pré-cálculo de somatórios e métricas.

Sistemas distribuídos (replicação de dados).

Redução de tempo de resposta em análises complexas.

Claro! Vamos criar um exemplo prático usando Oracle Database 23c que demonstra como a view materializada melhora

a performance, mostrando também os planos de execução antes e depois, inclusive utilizando as

novas capacidades do 23c como o ENABLE CONCURRENT REFRESH e query rewrite com ANSI JOIN.

EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'order_summary_rtmv');

O parâmetro ENABLE CONCURRENT REFRESH permite que múltiplas sessões atualizem a MV em paralelo sem bloqueio

O ENABLE QUERY REWRITE faz com que consultas compatíveis sejam redirecionadas automaticamente para a MV,

sem necessidade de usar a MV diretamente

7. Benefícios esperados

Tempo de resposta dramaticamente reduzido

Carga muito menor sobre base tables

Atualização incremental usando MV Logs (fast refresh)

Múltiplas sessões podem fazer ON COMMIT simultaneamente graças ao ENABLE CONCURRENT REFRESH sem contention (enq: JI)

.

✅ Resumo

Etapa Sem MV Materializada Com MV Materializada (Oracle 23c)

Consulta Agregações e joins em tempo real Query rewrite via MV, sem agregação

Plano de Execução FULL TABLE SCAN + HASH JOIN + GROUP BY MAT_VIEW REWRITE ACCESS FULL sobre MV

Custo estimado (planner/CPU) Alto Muito reduzido

Atualização da MV Demorada (complete refresh) Fast refresh incremental via MV log

?‍? Aula: Cursores Implícitos em PL/SQL – Oracle Database

? Objetivo:

Apresentar o conceito de cursores implícitos em PL/SQL Oracle, demonstrar seu uso com comandos DML e SELECT INTO,

e explorar os atributos

importantes como %FOUND, %NOTFOUND, %ROWCOUNT, e %ISOPEN.

? 1. Introdução – O que são cursores?

Um cursor é uma área de memória que armazena o resultado de uma instrução SQL.

O Oracle automaticamente cria um cursor implícito sempre que um comando SQL (como INSERT, UPDATE, DELETE, SELECT INTO)

é executado dentro

de um bloco PL/SQL.

? 2. Por que usar cursores implícitos?

São fáceis de usar.

Úteis para verificar o resultado de uma instrução SQL (quantas linhas foram afetadas, por exemplo).

Menos código e mais simplicidade quando não se precisa iterar linha a linha.

⚙️ 3. Atributos dos Cursores Implícitos

Dentro de um bloco PL/SQL, podemos acessar os atributos do cursor implícito usando a palavra-chave SQL.

Atributo Descrição

SQL%FOUND Retorna TRUE se uma ou mais linhas foram afetadas.

SQL%NOTFOUND Retorna TRUE se nenhuma linha foi afetada.

SQL%ROWCOUNT Retorna o número de linhas afetadas.

SQL%ISOPEN Sempre retorna FALSE para cursores implícitos.

⚠️ Importante:

SELECT INTO só funciona se a consulta retornar apenas uma linha.

Se retornar nenhuma, gera NO_DATA_FOUND.

Se retornar mais de uma, gera TOO_MANY_ROWS.

Boas Práticas

Use cursores implícitos para operações simples com poucas linhas.

Use os atributos do cursor (SQL%...) para validar ações DML.

Evite cursores implícitos quando for necessário processar múltiplas linhas — prefira cursores explícitos ou técnicas

de bulk processing.

? Conceito

Em PL/SQL, um cursor é um ponteiro que permite percorrer linha por linha o resultado de uma consulta (SELECT).

Existem dois tipos:

Implícitos: utilizados automaticamente pelo Oracle quando uma instrução SQL é executada

(como SELECT INTO, INSERT, UPDATE, etc.).

Explícitos: declarados manualmente pelo programador para manipular mais de uma linha retornada por uma consulta.

? Vantagens dos Cursores Explícitos

Vantagem Explicação

Controle total Permite manipular linha por linha dos dados retornados.

Flexibilidade Ideal para situações onde é necessário processar registros individualmente.

Legibilidade Deixa claro que o código está iterando sobre uma consulta específica.

Acesso a atributos do cursor Como %ROWCOUNT, %FOUND, %NOTFOUND etc.

⚠️ Desvantagens

Desvantagem Explicação

Desempenho Processamento linha a linha (row-by-row) é mais lento que operações em lote (bulk).

Complexidade Exige mais código (declaração, abertura, fetch, fechamento).

--------------------------------------------------------------------------------------------------------------------

Um cursor explícito com parâmetros permite passar valores para o cursor no momento de sua abertura.

Isso possibilita que o mesmo cursor seja reutilizado com diferentes critérios de seleção, tornando o

código mais flexível e reutilizável

O Oracle suporta a parametrização de cursores explícitos. Se uma instrução SELECT precisar

ser executada com os mesmos predicados, mas com valores diferentes, é aconselhável usar

cursores parametrizados. A parametrização de um cursor é um recurso de programação poderoso,

pois pode aprimorar os padrões de codificação, reduzindo o número de construções de cursores explícitos

em um programa.

Estruturalmente, um cursor parametrizado é um cursor explícito com parâmetros.

Os parâmetros podem ou não ter valores padrão. O desenvolvedor fornece os

valores dos parâmetros no momento da abertura do cursor no corpo do programa. Opcionalmente,

você também pode prototipar fortemente um cursor parametrizado especificando a cláusula RETURN.

A seguinte definição de cursor usa o número do departamento como parâmetro:

? Vantagens dos Cursores Parametrizados

Vantagem Explicação

Reutilização de código O mesmo cursor pode ser usado com diferentes valores de parâmetros.

Flexibilidade Permite consultas dinâmicas com base na entrada do usuário ou de variáveis.

Manutenção mais fácil Evita duplicação de cursores para diferentes critérios.

Maior legibilidade O cursor deixa claro o que espera receber e processar.

⚠️ Desvantagens

Desvantagem Explicação

Complexidade extra Requer mais atenção no momento da abertura, pois exige passar parâmetros.

Não tão intuitivo para iniciantes Mais difícil de entender do que cursores sem parâmetros.

Ainda é row-by-row Como todo cursor explícito, tem menor desempenho em relação a operações em lote.

---------------------------------------------------------------------------------------------------------------------

Mais uma situação

? WHERE CURRENT OF

Permite referenciar a linha atual sendo processada por um cursor. Isso é útil para realizar UPDATE ou DELETE

diretamente na linha recuperada sem precisar reespecificar a condição da cláusula WHERE.

? Vantagens

Vantagem Explicação

Atualização direta WHERE CURRENT OF atualiza ou deleta exatamente a linha buscada.

Evita reescrever condições Elimina a necessidade de replicar a cláusula WHERE.

Código mais claro e seguro Evita erros de lógica ao referenciar a linha atual.

Reutilização de lógica Com cursores parametrizados, o mesmo cursor pode ser usado com diferentes critérios.

⚠️ Desvantagens

Desvantagem Explicação

Lock de linha FOR UPDATE bloqueia as linhas até o COMMIT, podendo impactar outros usuários.

Desempenho Uso de cursores linha a linha é menos eficiente que operações em lote.

Mais verboso Requer mais estrutura e cuidado (OPEN, FETCH, CLOSE, etc.).

-------------------------------------------------- PLSQL ADVANCED       ---------------------------------------------------------------

---------------------------------------------   Capítulo 24 - Strong Ref Cursor--------------------------------------------------------

--------------------------------------------- www.pedrofcarvalho.com.br ---------------------------------------------------------------

-------------------------------------------- contato@pedrofcarvalho.com.br ------------------------------------------------------------

-- ? O que é um Strong Ref Cursor?

-- Um Strong Ref Cursor (cursor referenciado fortemente) é um tipo de cursor com tipo de retorno explicitamente definido.

-- Isso permite que o compilador verifique o tipo de dados retornado, promovendo segurança e consistência no código.

/*

✅ Diferença entre Weak e Strong Ref Cursor

Característica             Strong Ref Cursor             Weak Ref Cursor

Tipagem                     Fortemente tipado (RETURN tipo) Fracamente tipado (sem RETURN)

Verificação                 Em tempo de compilação         Em tempo de execução

Segurança de tipo         Alta                         Baixa

Flexibilidade             Menor (mais rígido)             Maior (pode retornar qualquer estrutura) */

/*✅ Resumo: Quando usar cada um?

Use Cursor Explícito:

Se a lógica é simples e fixa.

Quando performance máxima for crítica (em loops intensos).

Use Strong Ref Cursor:

Quando precisa de consultas dinâmicas.

Para passar resultados entre funções/procedimentos.

Em sistemas complexos/modulares.

Ao expor dados para aplicações externas (via PL/SQL API).*/

CREATE TABLE cursos (

    id NUMBER PRIMARY KEY,

    nome VARCHAR2(100),

    area VARCHAR2(50)

);

INSERT INTO cursos VALUES (1, 'Engenharia de Software', 'Tecnologia');

INSERT INTO cursos VALUES (2, 'Administração', 'Negócios');

INSERT INTO cursos VALUES (3, 'Design Gráfico', 'Artes');

COMMIT;

--? 2. Criar um Tipo Fortemente Tipado (Record)

-- Tipo baseado na estrutura da tabela cursos

CREATE OR REPLACE PACKAGE tipos_curso_pkg IS

  TYPE t_curso_row IS RECORD (

    id    cursos.id%TYPE,

    nome  cursos.nome%TYPE,

    area  cursos.area%TYPE

  );

  TYPE t_curso_cursor IS REF CURSOR RETURN t_curso_row;

END tipos_curso_pkg;

-- ? 3. Criar o Bloco PL/SQL Usando o Strong Ref Cursor

SET SERVEROUTPUT ON

DECLARE

  c_cursor tipos_curso_pkg.t_curso_cursor;

  v_registro tipos_curso_pkg.t_curso_row;

BEGIN

  OPEN c_cursor FOR

    SELECT id, nome, area

    FROM cursos

    WHERE area = 'Tecnologia';

  LOOP

    FETCH c_cursor INTO v_registro;

    EXIT WHEN c_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Curso: ' || v_registro.nome || ' - Área: ' || v_registro.area);

  END LOOP;

  CLOSE c_cursor;

END;

/*? O Que Está Acontecendo?

Definimos um tipo de registro (record) com os campos da tabela cursos.

Criamos um ref cursor fortemente tipado que retorna esse tipo de registro.

Abrimos o cursor com um SELECT.

Iteramos sobre os dados com FETCH INTO e exibimos os resultados com DBMS_OUTPUT.*/

/*? Vantagens do Strong Ref Cursor

Validações em tempo de compilação, reduzindo erros de tempo de execução.

Código mais organizado e seguro.

Ideal para interfaces entre pacotes, procedures, e aplicações externas (como Java, .NET, etc.)

que precisam saber exatamente o que será retornado. */

-- Mais um exemplo

? 1. CRIAR UM PACOTE COMPLETO COM STRONG REF CURSOR

CREATE OR REPLACE PACKAGE curso_pkg IS

  -- Tipo de registro baseado na tabela CURSOS

  TYPE t_curso_row IS RECORD (

    id    cursos.id%TYPE,

    nome  cursos.nome%TYPE,

    area  cursos.area%TYPE

  );

  -- Cursor fortemente tipado (strong ref cursor)

  TYPE t_curso_cursor IS REF CURSOR RETURN t_curso_row;

  -- Procedure que retorna o cursor de cursos por área

  PROCEDURE obter_cursos_por_area(p_area IN VARCHAR2, p_cursor OUT t_curso_cursor);

END curso_pkg;

? Pacote de Corpo (Body)

CREATE OR REPLACE PACKAGE BODY curso_pkg IS

  PROCEDURE obter_cursos_por_area(p_area IN VARCHAR2, p_cursor OUT t_curso_cursor) IS

  BEGIN

    OPEN p_cursor FOR

      SELECT id, nome, area

      FROM cursos

      WHERE area = p_area;

  END obter_cursos_por_area;

END curso_pkg;

? 2. TESTANDO A PROCEDURE NO SQL*Plus / SQL Developer

SET SERVEROUTPUT ON

DECLARE

  v_cursor curso_pkg.t_curso_cursor;

  v_curso  curso_pkg.t_curso_row;

BEGIN

  -- Chamar a procedure

  curso_pkg.obter_cursos_por_area('Tecnologia', v_cursor);

  -- Ler os dados retornados pelo cursor

  LOOP

    FETCH v_cursor INTO v_curso;

    EXIT WHEN v_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Curso: ' || v_curso.nome || ' - Área: ' || v_curso.area);

  END LOOP;

  CLOSE v_cursor;

END;

? 3. COMO CONSUMIR ESSE CURSOR EM APLICAÇÕES EXTERNAS

? Oracle Forms ou Aplicações Java/.NET:

A procedure obter_cursos_por_area pode ser chamada diretamente por qualquer cliente que suporte chamadas PL/SQL e possa manipular um cursor.

Em Java, você usaria CallableStatement e o tipo OracleTypes.CURSOR para ler os dados.

? Exemplo simplificado em Java (Oracle JDBC):

CallableStatement cs = conn.prepareCall("{call curso_pkg.obter_cursos_por_area(?, ?)}");

cs.setString(1, "Tecnologia");

cs.registerOutParameter(2, OracleTypes.CURSOR);

cs.execute();

ResultSet rs = (ResultSet) cs.getObject(2);

while (rs.next()) {

    System.out.println("Curso: " + rs.getString("nome") + " - Área: " + rs.getString("area"));

}

✅ CONCLUSÃO

Um Strong Ref Cursor é ideal para retornar resultados com tipagem segura para aplicações.

A estrutura do pacote PL/SQL facilita a reutilização e a integração externa.

Ele pode ser usado diretamente com Oracle Forms, Java, .NET, Python (cx_Oracle), etc.

-------------------------------------------------- PLSQL ADVANCED       ---------------------------------------------------------------

--------------------------------------   Capítulo 26 - Sys_RefCursor -----------------------------------------------------

--------------------------------------------- www.pedrofcarvalho.com.br ---------------------------------------------------------------

-------------------------------------------- contato@pedrofcarvalho.com.br ------------------------------------------------------------

? 1. O Que É o SYS_REFCURSOR?

✅ Definição:

SYS_REFCURSOR é um tipo pré-definido em Oracle PL/SQL baseado em REF CURSOR, usado para armazenar e manipular

cursosres retornados dinamicamente por uma consulta SQL.

? É uma forma fraca (weakly-typed) de REF CURSOR.

? Por que usar SYS_REFCURSOR?

Evita declaração manual de tipos: Não precisa declarar TYPE my_cursor IS REF CURSOR.

Útil para retornar dados de forma genérica (ex: APIs PL/SQL, aplicações externas).

Facilita o desenvolvimento com queries que podem variar.

? 2. Diferença Entre REF CURSOR e SYS_REFCURSOR

Característica             REF CURSOR (tipo definido pelo usuário)         SYS_REFCURSOR

Precisa de declaração     Sim                                             Não

Forte ou fraco             Pode ser os dois                             Sempre fraco

Mais seguro com %ROWTYPE Sim                                             Não

Ideal para código genérico Não                                             Sim

? 3. Exemplo Prático com SYS_REFCURSOR

?️ Tabela base: emp

CREATE TABLE emp (

    empno NUMBER PRIMARY KEY,

    ename VARCHAR2(100),

    sal   NUMBER

);

INSERT INTO emp VALUES (1, 'Ana', 1000);

INSERT INTO emp VALUES (2, 'Bruno', 2000);

INSERT INTO emp VALUES (3, 'Carla', 3000);

COMMIT;

?‍? Exemplo: Função que retorna SYS_REFCURSOR

? Criar um pacote

-- Especificação do pacote

CREATE OR REPLACE PACKAGE emp_sys_pkg AS

    FUNCTION get_employees_by_salary(p_min_sal NUMBER) RETURN SYS_REFCURSOR;

END emp_sys_pkg;

/

-- Corpo do pacote

CREATE OR REPLACE PACKAGE BODY emp_sys_pkg AS

    FUNCTION get_employees_by_salary(p_min_sal NUMBER) RETURN SYS_REFCURSOR IS

        c SYS_REFCURSOR;

    BEGIN

        OPEN c FOR

            SELECT empno, ename, sal FROM emp WHERE sal >= p_min_sal;

        RETURN c;

    END;

END emp_sys_pkg;

/

▶️ Consumindo o SYS_REFCURSOR

DECLARE

    c SYS_REFCURSOR;

    v_empno emp.empno%TYPE;

    v_ename emp.ename%TYPE;

    v_sal   emp.sal%TYPE;

BEGIN

    c := emp_sys_pkg.get_employees_by_salary(2000);

    LOOP

        FETCH c INTO v_empno, v_ename, v_sal;

        EXIT WHEN c%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('Emp: ' || v_ename || ', Sal: ' || v_sal);

    END LOOP;

    CLOSE c;

END;

⚖️ 4. Vantagens e Desvantagens do SYS_REFCURSOR

✅ Vantagens

Vantagem                 Explicação

? Tipo já embutido         Não precisa declarar TYPE my_cursor IS REF CURSOR

? Flexível                 Aceita qualquer query sem restrição de tipo

? Integração             Ideal para chamadas entre PL/SQL e aplicações externas (Java, Python, .NET etc)

? Simples                 Facilita a prototipação de APIs

❌ Desvantagens

Desvantagem                 Explicação

❗ Menos seguro             Não verifica compatibilidade de tipos em tempo de compilação

? Mais propenso a erros Se a estrutura da query mudar, o consumidor pode falhar

⚠️ Sem suporte a %ROWTYPE Você deve declarar cada campo individualmente no FETCH

� 5. Quando Usar?

✅ Use SYS_REFCURSOR quando:

Precisa retornar dados dinamicamente de dentro de funções/procedimentos.

Deseja expor dados por meio de APIs PL/SQL.

Integra PL/SQL com aplicações externas que consomem cursores.

❌ Evite usar quando:

Você quer segurança de tipos forte (%ROWTYPE, validação em tempo de compilação).

Está trabalhando com estruturas fixas e bem definidas (melhor usar cursor forte).

✅ Conclusão

Use REF CURSOR com tipagem forte quando quiser segurança e verificação em tempo de compilação.

Use SYS_REFCURSOR quando quiser simplicidade e flexibilidade, principalmente para APIs.

Ambos são úteis e fazem parte de um código PL/SQL profissional — a escolha depende do contexto e objetivo.

-- Mais um exemplo

CREATE TABLE departamentos (

    deptno NUMBER PRIMARY KEY,

    dname  VARCHAR2(100)

);

CREATE TABLE empregados (

    empno   NUMBER PRIMARY KEY,

    ename   VARCHAR2(100),

    sal     NUMBER,

    deptno  NUMBER REFERENCES departamentos(deptno)

);

INSERT INTO departamentos VALUES (10, 'Vendas');

INSERT INTO departamentos VALUES (20, 'TI');

INSERT INTO empregados VALUES (1, 'Ana', 1500, 10);

INSERT INTO empregados VALUES (2, 'Bruno', 2500, 10);

INSERT INTO empregados VALUES (3, 'Carla', 3500, 20);

INSERT INTO empregados VALUES (4, 'Daniel', 1800, 20);

COMMIT;

? Parte 1 — REF CURSOR (Tipado)

Crie um pacote PL/SQL chamado emp_ref_pkg com:

Um TYPE chamado emp_cursor como REF CURSOR RETURN empregados%ROWTYPE

Uma função get_emp_by_dept(p_deptno IN NUMBER) que retorna esse cursor.

Em um bloco anônimo, consuma esse cursor para imprimir os funcionários de um departamento específico.

-- Especificação

CREATE OR REPLACE PACKAGE emp_ref_pkg AS

    TYPE emp_cursor IS REF CURSOR RETURN empregados%ROWTYPE;

    FUNCTION get_emp_by_dept(p_deptno IN NUMBER)

        RETURN emp_cursor;

END emp_ref_pkg;

/

-- Corpo

CREATE OR REPLACE PACKAGE BODY emp_ref_pkg AS

    FUNCTION get_emp_by_dept(p_deptno IN NUMBER)

        RETURN emp_cursor IS

        c emp_cursor;

    BEGIN

        OPEN c FOR

            SELECT * FROM empregados WHERE deptno = p_deptno;

        RETURN c;

    END;

END emp_ref_pkg;

DECLARE

    c emp_ref_pkg.emp_cursor;

    v_emp empregados%ROWTYPE;

BEGIN

    c := emp_ref_pkg.get_emp_by_dept(10); -- Buscar funcionários do departamento 10

    LOOP

        FETCH c INTO v_emp;

        EXIT WHEN c%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('Nome: ' || v_emp.ename || ', Salário: ' || v_emp.sal);

    END LOOP;

    CLOSE c;

END;

-- com SYS_RECURSOR

CREATE OR REPLACE PACKAGE emp_sys_pkg AS

    FUNCTION get_emp_by_sal(p_sal IN NUMBER)

        RETURN SYS_REFCURSOR;

END emp_sys_pkg;

CREATE OR REPLACE PACKAGE BODY emp_sys_pkg AS

    FUNCTION get_emp_by_sal(p_sal IN NUMBER)

        RETURN SYS_REFCURSOR IS

        c SYS_REFCURSOR;

    BEGIN

        OPEN c FOR

            SELECT empno, ename, sal FROM empregados WHERE sal >= p_sal;

        RETURN c;

    END;

END emp_sys_pkg;

DECLARE

    c SYS_REFCURSOR;

    v_empno empregados.empno%TYPE;

    v_ename empregados.ename%TYPE;

    v_sal   empregados.sal%TYPE;

BEGIN

    c := emp_sys_pkg.get_emp_by_sal(2500); -- Buscar funcionários com salário >= 2500

    LOOP

        FETCH c INTO v_empno, v_ename, v_sal;

        EXIT WHEN c%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('Nome: ' || v_ename || ', Salário: ' || v_sal);

    END LOOP;

    CLOSE c;

END;