Fundamentos PL/SQL

Parte
i
Fundamentos PL/SQL
Capítulo
1
Visão Geral do
Oracle Pl/SQL
4 Oracle Database 11g PL/SQL Programação
E
ste capítulo introduz o Procedure Language/Structured Query Language (PL/
SQL), explica a história, a arquitetura e a estrutura de bloco do PL/SQL, examina
e discute as novas características do Oracle 10g e do Oracle 11g. O capítulo é
dividido nas seguintes seções:
■■
História e seu background
■■
Arquitetura
■■
Estruturas de bloco básicas
■■
Oracle 10g: novas características
■■
Oracle 11g: novas características
História e Pano de Fundo
O PL/SQL foi desenvolvido pelo Oracle no final dos anos 80. Originalmente, PL/SQL tinha
capacidade limitada, mas isto mudou no começo dos anos 90. O PL/SQL oferece um banco de
dados Oracle interpretado pronto e um ambiente de programação de sistema de operação independente. SQL statements são originalmente integrados na linguagem PL/SQL, onde é possível
acionar diretamente PL/SQL através da linha de comando interface SQL*Plus. Ações diretas parecidas podem ser feitas em suas ligações de linguagem de programação externa, como ilustrado
nos Apêndices C e D.
O Oracle 8 Database introduziu tipos de objetos dentro do banco de dados. Ele transformou
o banco de dados Oracle de um modelo puramente relacional em um modelo objeto-relacional
(ou relacional estendido).
Estes tipos tinham valor limitado como coleções de variáveis escalares até se tornarem
instanciáveis no Oracle 9i, Release 2. A capacidade instanciável de tipos de objeto SQL tornou
os objetos internos do Oracle compatíveis com C++, Java, ou tipos objetos C#.Tipos de objetos
SQL são implementados em PL/SQL e descritos no Capítulo 15.
PL/SQL evoluiu com o advento das capacidades oferecidas de programação completa de
objeto-orientado pelo Oracle 9i, Release 2. PL/SQL não é mais uma linguagem puramente de
procedimento. É agora uma linguagem de programação de procedimento e de objeto-orientado.
O Oracle 11g Database também desenvolveu o PL/SQL de uma linguagem interpretada para
uma linguagem compilada nativamente. Você pode perguntar: “Isto não elimina o benefício de
uma linguagem de sistema de operação independente?” A resposta a esta questão é: de maneira
alguma. Agora é possível escrever PL/SQL em uma forma de sistema de operação independente.
Depois, pode desdobrá-lo e deixar o Oracle gerenciar sua compilação nativa. O Oracle g automatiza o processo para você em plataformas que dão suporte a ele.
Versões PL/SQL
Versões iniciais PL/SQL não eram seqüenciadas com a versão do banco de dados. Por
exemplo, o PL/SQL 1.0 enviado com o Oracle 6 Database. PL/SQL 2.x enviado com o
Oracle 7.x Databases. Começando com o Oracle 8, as versões PL/SQL correspondiam
com os números liberados pelo banco de dados, como PL/SQL 11.1 no Oracle 11g
Release 1 Database.
Como também tem sido exigido nos programas externos, PL/SQL é também o principal
portão de entrada para as bibliotecas externas. O rótulo bibliotecas externas é enganador, visto
que as bibliotecas Java podem ser armazenadas dentro do banco de dados. Oracle aciona os
procedimentos externos das bibliotecas externas através do PL/SQL independente de onde estão
Capítulo 1: Visão Geral do Oracle Pl/SQL 5
armazenadas. Os programas PL/SQL servem como wrappers para as bibliotecas externas. Os
Wrappers são interfaces que mascaram o tipo de conversão entre o banco de dados e os programas externos.
Você pode estender a funcionalidade do Oracle 11g Database ao construir funções armazenadas e procedimentos no PL/SQL, C, C++, ou Java. Programas Java podem ser diretamente
armazenados dentro do Oracle 11g Database em todas as suas versões, exceto na Edição Oracle
Express. O Capítulo 12 demonstra como construir e executar procedimentos externos. O capítulo 14 cobre como construir e alavancar bibliotecas Java dentro do banco de dados.
O PL/SQL continua a se desenvolver e tornou-se mais robusto. Isto é excelente para aqueles
treinados em PL/SQL, assim como a evolução entre as versões Java é excelente para os programadores treinados em Java. A programação PL/SQL apresenta desafios para os novatos desta
linguagem porque serve a muitos MASTERS no banco de dados Oracle. Na medida em que você
desenvolve suas habilidades nesta linguagem, aprenderá como usar PL/SQL para resolver até
mesmo os problemas mais complexos.
A Programação PL/SQL é Magia Negra?
No início, o PL/SQL 1.0 era pouco mais do que uma ferramenta de apresentação. Agora
a frase CASE em SQL oferece a maior parte daquela funcionalidade. No meio dos anos
90, os desenvolvedores descreviam a programação PL/SQL 2.x como Magia Negra. Na
época, este rótulo era apropriado, pois não havia muita literatura sobre a linguagem e
a disponibilidade de amostras de códigos na web era limitada porque a web ainda não
existia como hoje.
Hoje, ainda existem pessoas que vêem o PL/SQL como Magia Negra. Eles também
são apaixonados em escrever código neutro de banco de dados em Java ou outras linguagens. É politicamente correto falar isto para evitar soluções PL/SQL apesar de suas vantagens. Por que o Oracle PL/SQL é ainda uma Magia Negra para muitos, quando existem
hoje em dia tantos livros publicados sobre PL/SQL?
Poderia dizer que são seus cursores, mas eles existem em quaisquer programas conectados através da Oracle Call Interface (OCI) ou Java Database Connectivity (JDBC).
Se não são os cursores, você poderia dizer que é a sua sintaxe, tipos definidos de
usuário ou nuances de funções e procedimentos.
Será que estes são realmente tão diferentes assim das outras linguagens de programação?
Se a resposta a esta questão for não, acabou de ser iniciado no mundo do PL/SQL. Se
a resposta foi sim ou se pensa que existe alguma outra mágica na linguagem, ainda não
foi iniciado.
E como se tornar um iniciado? A resposta mais atrativa é ler este livro.
A verdadeira resposta é para desfazer o jargão do Oracle que assombra a linguagem
PL/SQL. Por exemplo, uma variável é sempre uma variável de algum tipo e uma função
ou procedimento é sempre uma sub-rotina que gerencia parâmetros formais pela referência ou valor que pode ou não retornar um resultado como um operand certo.
Estes tipos de regras simples alimentam a verdade para cada componente nesta
linguagem.
Arquitetura
A linguagem PL/SQL é uma ferramenta robusta com muitas opções. A PL/SQL permite escrever um código uma vez e alavancá-lo no banco de dados mais próximo aos dados. A PL/SQL
6 Oracle Database 11g PL/SQL Programação
pode simplificar o desenvolvimento de uma aplicação, otimizar execução e melhorar a utilização de recursos no banco de dados.
A linguagem é de programação case-insensitive, como SQL. Isto leva a numerosas direções
de melhores práticas de formatação. Em vez de repetir aqueles argumentos para um estilo ou
outro, recomenda que encontre um estilo consistente com seus padrões de organização e aplicálo consistentemente.
O código PL/SQL neste livro usa letras maiúsculas para palavras comando e minúsculas para
variáveis, nomes de colunas e chamadas de programas armazenados.
O PL/SQL foi desenvolvido pelos conceitos de modelagem de programação estruturada, digitação de dados estáticos, modularidade, gerenciamento de exceção e processamento (concorrente) paralelo encontrado na programação de linguagem Ada, desenvolvida pelo Departamento
de Defesa dos Estados Unidos, foi desenhada para dar suporte militar em tempo real e encaixada nos sistemas de segurança críticos, como aqueles em aeronaves e mísseis. A linguagem de
programação Ada pegou emprestada a sintaxe significante da linguagem e programação Pascal,
incluindo os operadores de tarefa e de comparação e os delimitadores de cotação-única.
Estas escolhas também ativaram a inclusão direta dos statements SQL em códigos de bloco
PL/SQL. Eles eram importantes porque SQl adotava os mesmos operadores Pascal, delimitadores
string e datatipos escalares declarativos que não mudam o tempo de execução e são conhecidos
como datatipos fortes, essenciais para a integração do Oracle e as linguagens PL/SQL.
A PL/SQL aceita tipos de dados dinâmicos, mapeando-os durante o tempo de execução
contra tipos definidos no catálogo do Oracle 11g Database. A compatibilidade de operadores
e delimitadores string [em série] significa análise de palavras simplificadas porque os statements
SQL são nativamente embebidos em unidade de programação PL/SQL.
NOTA
Os comandos básicos da linguagem de programação Java
descrevem variáveis escalares que guardam apenas uma coisa
de cada vez.
O tempo original de desenvolvimento do PL/SQL fez estas escolhas cuidadosamente. O
banco de dados Oracle foi recompensado ao longo dos anos por causa destas escolhas. Uma
escolha que se destaca como uma decisão maravilhosa é deixar você conectar variáveis PL/SQL
no catálogo do banco de dados. Esta é uma forma de herança de tipo run time. É possível usar
os pseudo-tipos %TYPE e %ROWTYPE para herdar as variáveis fortemente digitadas definidas no
catálogo do banco de dados (discutido nos Capítulos 3 e 9).
Ancorar as variáveis PL/SQL no catálogo de objetos do banco de dados é uma forma efetiva
de acoplamento estrutural. Elas podem minimizar o número de alterações que precisa fazer em
seus programas Pl/SQL. No mínimo, limitam a freqüência que você recodifica como resultado
de mudanças entre os tipos bases, como a mudança de uma VARCHAR2 para uma DATE. Elas
eliminam também a necessidade de redefinir os tamanhos das variáveis. Por exemplo, você não
precisa modificar seu código quando uma tabela muda o tamanho do comprimento de uma
variável de coluna string.
O Oracle também tomou outras decisões estratégicas quando limitou o número de tipos
bases SQL e começou um subtyping no catálogo do banco de dados. Ao subtyping, os tipos base
permitem ao Oracle desenvolver uma árvore de objetos de múltipla hierarquia que continua
a crescer e amadurecer. O enfoque do objeto orientado contribui e continua a contribuir para
que o Oracle desenvolvesse o modelo relacional para um modelo objeto relacional (também
conhecido como o modelo relacional estendido). O PL/SQL tira total vantagem ao subtyping os
tipos variáveis do SQL.
A máquina do tempo de execução do PL/SQL existe como um recurso dentro do ambiente
SQL*Plus. O ambiente SQL*Plus é tanto interativo quanto acessível. Cada vez que conecta o
Oracle 11g Database, o banco de dados cria uma nova sessão. Nesta sessão, é possível execu-
Capítulo 1: Visão Geral do Oracle Pl/SQL 7
tar statements SQL ou PL/SQL vindos do ambiente SQL*Plus. As unidades do programa PL/SQL
podem, então, executar os statements SQL ou procedimentos externos, como demonstrado na
Figura 1.1.
Os statements SQL podem também acionar as funções ou procedimentos PL/SQL, interagindo diretamente com os dados reais.
A chamada direta para PL/SQL pode ser feita através do Oracle Call Interface (OCI) ou Java
Database Connectivity (JDBC), que permite alavancar PL/SQL diretamente em suas aplicações
no banco de dados. Isto é importante porque permite gerenciar o escopo da transação em suas
unidades armazenadas de programa PL/SQL e simplifica tremendamente as diversas tarefas frequentemente colocadas na camada de abstração das aplicações.
O PL/SQL também dá suporte à construção de statements SQL no run time. Os statements
run time SQL são SQL dinâmicos podendo usar dois enfoques para o SQL dinâmico: um é o
Native Dynamic SQL (NDS) e o outro é o pacote DBMS_SQL. O Oracle 11g Database oferece
novas características NDS e melhora a velocidade de execução. Com esta nova versão, apenas
necessita usar o pacote DBMS_SQL quando não souber o número de colunas que sua chamada
(call) SQl dinâmica exige.
O Capítulo 11 demonstra SQl dinâmico e apresenta ambos os pacotes NDS e DBMS_SQL.
Agora tem uma visão de alto nível da linguagem PL/SQL. A próxima seção lhe dará uma
visão geral rápida das estruturas de bloco do PL/SQL.
Interactive
Interactive
Call
Response
Sessão Banco de Dados
Ambiente
SQL Plus*
SQL Statement
Engine
Dados
PL/SQL
Engine
Procedimentos
Externos
Buffer
Ler
Escrever
Input
Output
Externo Externo
FIGURA 1-1 Arquitetura de Processamento do Banco de Dados
Ler
Escrever
Input
Output
Externo Externo
8 Oracle Database 11g PL/SQL Programação
Estruturas Básicas de Bloco
PL/SQL é uma linguagem de programação em blocos. As unidades do programa podem ser
blocos nomeados ou não. Blocos não nomeados são conhecidos como blocos anônimos e são
rotulados durante o livro.
O estilo de código PL/SQL difere dos usados nas linguagens de programação C, C++ e Java.
Por exemplo, as chaves não delimitam os blocos na PL/SQL.
Programas de blocos anônimos são efetivos em algumas situações. Você geralmente usa blocos anônimos quando constrói script para gerar dados ou executar atividades de processamento
de one-time (uma vez). Eles também são efetivos quando quer alojar uma atividade em outra seção de execução de outro bloco PL/SQL. A estrutura básica de bloco anônimo deve conter uma
seção de execução. Também é possível colocar uma declaração opcional e seção de exceção
em blocos anônimos. A seguir demonstramos um protótipo de bloco anônimo:
[DECLARE]
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END;
/
O bloco de declaração permite definir datatipos, estruturas e variáveis. Definir uma variável
significa que pode dar um nome a ela e um datatipo. Você pode também declarar uma variável
dando a ela um nome, um datatipo e um valor, como também definir e projetar um valor ao
declarar uma variável.
Alguns tipos de objetos não podem ser definidos como variáveis escopo de modo limitado, mas devem ser definidos como tipos no catálogo de banco de dados, como discutido no
Capítulo 14.
As estruturas são variáveis compostas, como coleções, estruturas de gravação ou cursores
de sistema de referência. Também podem ser chamadas de funções, procedimentos ou cursores,
que agem como pequenas funções. Os cursores têm nomes, assinaturas e um tipo de retorno–as
colunas output de uma query ou statemente SELECT. A palavra reservada DECLARE começa o
bloco declaração e a palavra reservada BEGIN o encerra.
O bloco de execução permite processar dados. O bloco de execução pode conter variáveis,
desígnios, comparações, operações condicionais e iterações. Além disso, o bloco execução é
onde você acessa cursores e outras unidades nomeadas de programa. Funções, procedimentos e
alguns tipos de objetos são unidades nomeadas de programa. Também é possível alojar programas de blocos anônimos dentro do bloco de execução. A palavra reservada BEGIN inicia o
bloco de exceção e a opcional ou a palavra reservada exigida END o encerra. É preciso ter ao
menos um statement dentro de um bloco de execução. O statement mínimo de bloco anônimo a
seguir inclui um statement NULL:
BEGIN
NULL;
END;
/
Isto não faz nada, exceto permitir que a fase compilação complete sem um erro. A compilação em qualquer linguagem inclui uma frase sintática. A falta de um statement no bloco faz
surgir um erro de frase como demonstrado no Capítulo 5.
Capítulo 1: Visão Geral do Oracle Pl/SQL 9
O bloco de manejo de exceções permite gerenciar exceções. Você pode pegá-los e gerenciá-los aí.
O bloco de exceção permite um processamento alternativo; em muitos casos ele age como
combinação de um bloco catch (capturar) e finally (finalmente) na programação da linguagem
Java (ver Apêndice D para mais informações sobre Java). A palavra reservada EXCEPTION inicia
a seção e a END termina.
DICA
Você tem a mesma regra que exige o mínimo de um statement
para qualquer bloco em um bloco statement condicional
(como um statement IF) e loops.
Programas de bloco nomeados têm uma ligeira diferença na estrutura do bloco, pois eles são
armazenados no banco de dados. Também possuem uma seção declaração, a qual é conhecida
como um cabeçalho (header). O nome, a lista de parâmetros formais e qualquer tipo de retorno
de blocos nomeados PL/SQL são definidos pelo cabeçalho (header). O nome e a lista de parâmetros formais são conhecidos como a assinatura de uma sub-rotina. A área entre o cabeçalho
e os blocos de execução age como o bloco de declaração para o bloco nomeado. Esta mesma
regra é verdadeira para os corpos de tipo objeto discutidos no Capítulo 14. A seguir ilustramos
um protótipo de uma função de bloco nomeado:
FUNCTION function_name
[( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type
, parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type
, parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )]
RETURN [ sql_data_type | plsql_data_type ]
[ AUTHID {DEFINER | CURRENT_USER}]
[ DETERMINISTIC | PARALLEL_ENABLED ]
[ PIPELINED ]
[ RESULT_CACHE [RELIES ON table_name]] IS
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END;
/
O Capítulo 6 discute as regras que governam as funções, que podem se comportar como
sub-rotinas pass-by-value ou pass-by-reference. Sub-rotinas pass-by-value definem parâmetros
formais usando apenas um modo IN. Isto significa que a variável passada não pode mudar durante a execução da sub-rotina.
As sub-rotinas pass-by-reference definem parâmetros formais usando IN e OUT ou apenas
modos OUT.
O Oracle 11g continua passando cópias de variáveis ao invés de referências para variáveis,
a menos que designe uma dica (hint) NOCOPY.
Os implementos Oracle pass-by-reference comportam-se desta maneira para garantir a integridade das variáveis dos modos IN OUT. Este modelo garante variáveis sem alteração, a menos
que uma ação (call) subprograma se complete com sucesso. Este comportamento padrão pode
ser apagado usando um NOCOPY.
10 Oracle Database 11g PL/SQL Programação
O Oracle não recomenda o uso do NOCOPY porque, ao usá-lo, pode resultar em mudanças
parciais em seus valores de parâmetros reais. Finalmente, o banco de dados escolhe se age sobre
uma dica (hint) ou envia uma referência.
As funções podem solicitar dados usando statements SELECT, mas não podem executar statements DML, como INSERT, UPDATE, ou DELETE. Todas as outras regras se aplicam às funções
armazenadas, as mesmas que se aplicam aos blocos anônimos. As funções que definem parâmetros formais ou tipos de retorno que usam datatipos PL/SQL não podem ser acionadas através
de uma linha de comando SQL. Entretanto, é possível acionar funções que usam datatipos SQL
através da linha de comando SQL.
O valor padrão do AUTHID é DEFINER, conhecido como definidor de direitos. Isso significa
que qualquer pessoa com privilégios para executar programas armazenados o faz com os mesmos privilégios que o usuário da conta definiu.
O alternativo CURRENT_USER permite àqueles com privilégios de execução acionar o programa armazenado e executá-lo apenas contra seus dados do esquema/usuário. Isto é conhecido
como direitos invoker e descreve o processo de acionar uma fonte comum de programa em
frente a contas individuais e dados.
É recomendável evitar usar a cláusula DETERMINISTIC quando as funções dependerem das
variáveis dos estados de nível de sessão. As cláusulas DETERMINISTIC se adaptam melhor a
índices baseados em função e views materializados (materialized views).
A cláusula PARALLEL_ENABLE deve ser ativada para funções que planeja acionar a partir
das cláusulas SQL que poderiam ser usados em capacidade query paralelas. É bom olhar de
perto esta cláusula para usos em data warehouse.
A cláusula PIPELINED oferece uma melhora no desempenho quando funções retornam às
coleções, como tabelas alojadas ou VARRAYs. Melhoras no desempenho também serão notadas
quando retornar dos cursores do sistema de referência usando a cláusula PIPELINED.
A cláusula RESULT_CACHE indica uma função que é escondida apenas uma vez na SGA e
está disponível através das seções. Ela é nova no Oracle 11g Database. Funções através da seção
funcionam apenas com modo IN de parâmetros formais.
O Capítulo 6 contém os detalhes de implementação sobre estas cláusulas e oferece exemplos mostrando como usá-los.
A seguir ilustramos um protótipo de um procedimento de bloco nomeado:
PROCEDURE procedure_name
( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type,
parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type,
parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )
[ AUTHID {DEFINER | CURRENT_USER}]
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END;
/
O Capítulo discute as regras que governam os procedures agindo como funções em muitas
maneiras, mas não podem retornar um datatipo. Isto significa que não pode usá-los como
operandos direitos.
Diferente de funções, os procedures devem ser acionados pelos blocos PL/SQL. Os Procedures podem tanto solicitar os dados quanto manipulá-los. Procedures também são as sub-rotinas
de fundação para valores, passando de/para linguagens externas como, por exemplo, C, C++,
Java e PHP.
Esta seção apresenta e discute as estruturas básicas das unidades do programa PL/SQL.
Capítulo 1: Visão Geral do Oracle Pl/SQL 11
As seções seguintes revisarão as características mais recentes no Oracle 10g Database e as
novas características no Oracle 11g Database.
Novas Características do Oracle 10g
Diversas mudanças foram introduzidas no Oracle 10g Database. Nem todas estavam disponíveis
quando a edição anterior foi escrita porque não foram enviadas até o segundo lançamento do
banco de dados.
Novas características PL/SQL introduzidas no Oracle 11g incluem:
■■
Pacotes embutidos
■■
Avisos de tempo de compilação
■■
Comportamentos de número datatipo
■■
Um compilador PL/SQL otimizado
■■
Expressões regulares
■■
Alternativas de citação
■■
Coleção de operadores
■■
Busca de Erros Stack
■■
Encapsulamento de programas PL/SQL armazenados
As subseções cobrem características recentes introduzidas no Oracle 10 g. Elas também
cruzam referência Oracle 11g Database–características relacionadas que serão discutidas mais
tarde neste capítulo.
Pacotes Embutidos (Built-in)
Começando com o Oracle 10g Release 2, é possível ter acesso a diversos pacotes embutidos, novos ou melhorados. Três que merecem ser mencionados aqui são:
■■
DBMS_SCHEDULER Substitui o DBMS_JOB embutido e oferece nova funcionalidade para planejamento e execução de trabalhos em lote.
■■
DBMS_CRYPTO Agora inclui a habilidade para codificar e decodificar grandes
objetos e para dar suporte à globalização através de ajustes de múltiplos caracteres.
■■
DBMS_MONITOR Oferece uma busca de suporte API e acumulação estatística
feita por sessões.
Avisos de Compile-Time
Começando com o Oracle 10g Release 1, é possível ter uma idéia do desempenho de seus
programas PL/SQL através da ativação do parâmetro PLSQL_WARNINGS em suas instâncias de
desenvolvimento. Ajuste isto para uma sessão ou o banco de dados. O anterior é a prática recomendada pelo fato do overhead (elevado) imposto sobre o banco de dados.
Para ajustar este parâmetro use o seguinte comando:
ALTER SESSION SET plsql_warnings = ‘enable:all
12 Oracle Database 11g PL/SQL Programação
Compilação Condicional
Começando com Oracle 10g Release 2, é possível usar compilação condicional. Esta permite
incluir lógica de purificação ou lógica de propósitos especiais que funcionam apenas quando as
variáveis nível-sessão estão ajustadas.
O comando seguinte ajusta uma variável PL/SQL tempo de compilação DEBUG igual a 1:
ALTER SESSION SET PLSQL_CCFLAGS = ‘debug:1’;
Este comando ajusta uma variável de compilação de tempo PL/SQL DEBUG igual a 1.
Note que uma flag de tempo de compilação pode ser escrita em maiúsculo ou minúsculo,
como também ajustar as variáveis flag tempo-compilação para verdadeiro ou falso de forma que
elas ajam como variáveis Boolean. Quando quer ajustar mais de uma flag de compilação condicional, é necessário usar a seguinte sintaxe:
ALTER SESSION SET PLSQL_CCFLAGS = ‘name1:value1 [,
name(n+1):value(n+1) ]’;
Os parâmetros de compilação condicional são armazenados como nome e pares de valor
no parâmetro do banco de dados PLSQL_CCFLAG. O programa a seguir usa as diretrizes $IF,
$THEN, $ELSE, $ELSIF, $ERROR e $END que criam um bloco de código de compilação
condicional:
BEGIN
$IF $$DEBUG = 1 $THEN
dbms_output.put_line(‘Debug Level 1 Enabled.’);
$END
END;
/
Blocos de código condicional diferem dos códigos de blocos normais if-then-else (se-entãooutro). Mais notavelmente, a diretriz $END fecha o bloco ao invés de um END IF e ponto e
vírgula como discutido no Capítulo 4. Note também que o símbolo $$ denota uma variável de
compilação condicional PL/SQL.
As regras que governam compilação condicional são ajustadas pelo programa analisador
SQL. Não é possível usar compilação condicional em objetos tipos SQL. Esta limitação também
se aplica a tabelas alojadas e VARRAYs (tabelas escalares).
Compilação condicional difere em funções e procedimentos. O comportamento muda se a
função ou procedimento tiver uma lista de parâmetro formal. A compilação condicional pode
ser usada após a abertura de parênteses de uma lista parâmetro formal, como
CREATE OR REPLACE FUNCTION conditional_type
(magic_number $IF $$DEBUG = 1 $THEN SIMPLE_NUMBER $ELSE NUMBER $END)
RETURN NUMBER IS
BEGIN
RETURN magic_number;
END;
/
Como alternativa, pode usá-las após a palavra-chave AS ou IS nas funções parâmetros ou
procedimentos, que também podem ser usados dentro da lista de parâmetros formais e após o
AS ou IS nas funções parâmetro ou procedimentos.
Compilação condicional pode ocorrer apenas após a palavra-chave BEGIN nos triggers e nas
unidades de programa de bloco anônimas.
Capítulo 1: Visão Geral do Oracle Pl/SQL 13
Por favor note que não pode encapsular um placeholder ou uma variável bind dentro de um
bloco de compilação condicional.
O Capítulo 4 contém exemplos usando técnicas de compilação condicional.
Comportamento do Número Datatipo
Iniciando com Oracle 10g Release 1, o banco de dados agora usa máquina aritmética para BINARY_INTEGER, INTEGER, INT, NATURAL, NATURALN, PLS_INTEGER, POSTIVE, POSITIVEN
e SIGNTYPE. Isto significa que eles agora usam a mesma resolução que o datatipo BINARY_INTEGER.
Em versões anteriores do banco de dados, estes trabalhavam como o NUMBER datatipo e
usavam a mesma biblioteca matemática C.
As novas versões destes datatipos podem ser comparadas contra o infinito ou NaN (não um
número).
O lado ruim desta mudança é que agora eles usam precisão numérica e não decimal. As
aplicações do financial devem continuar a usar o datatipo NUMBER por esta razão.
Uma precisão simples BINARY_FLOAT e precisão dupla BINARY_DOUBLE são também fornecidas no Oracle 10g Database. Elas são ideais para computações matemáticas ou científicas.
Compilador PL/SQL Otimizado
Começando com Oracle 10g Release 1, o banco de dados agora otimiza sua compilação PL/
SQL. Isto é ajustado de forma padrão e se aplica tanto ao código-p interpretado quanto ao
código PL/SQL compilado nativamente. Você pode modificar isto ou cancelar a agressividade
do otimizador reajustando o parâmetro PLSQL_OPTIMIZE_LEVEL. A Tabela 1-1 qualifica os três
valores possíveis para o parâmetro. É possível desativar a sessão otimização usando:
ALTER SESSION SET plsql_optimize_level = 0;
Também pode ajustar o nível de otimização para um procedimento.
O protótipo é:
ALTER PROCEDURE some_procedure COMPILE plsql_optimize_level = 1;
Após ter ajustado o nível de otimização, poderá usar a cláusula REUSE SETTINGS para utilisar novamente o ajuste anterior, como o
ALTER PROCEDURE some_procedure COMPILE REUSE SETTINGS;
Embora seja informativo, deve geralmente deixá-lo no default (padrão).
O código otimizado sempre funciona mais rápido que um código não otimizado.
NOTA
O PLSQL_OPTIMIZE_LEVEL deve estar ajustado em 2 ou mais
alto para inling subprograma automático para ocorrer no Oracle 10g ou no 11g Database.
14 Oracle Database 11g PL/SQL Programação
Nível de Otimização
Significado da otimização
0
Sem otimização.
1
Otimização Moderada, pode eliminar códigos
sem uso ou exceções.
2 (default)
Otimização agressiva, pode rearranjar a fonte
do fluxo do código.
TABELA 1-1 Valores PLSQL_OPTIMIZE_LEVEL Disponíveis
Expressões Regulares
Iniciando com Oracle 10g Release 1, o banco de dados agora suporta um ajuste de funções de
expressões regulares. O acesso é feito do mesmo modo que nos statements SQL ou nas unidades
de programa PL/SQL. Elas são:
■■ REGEXP_LIKE Busca uma string para um padrão compatível de expressão
regular.
■■ REGEXP_INSTR Busca a posição inicial de um padrão compatível de uma
expressão regular.
■■ REGEXP_SUBSTR Busca uma substring usando um padrão compatível de
expressão regular.
■■ REGEXP_REPLACE Substitui uma substring usando um padrão compatível de
expressões regulares.
Estas são funções ponderosas. O Apêndice E discute, analisa e demonstra expressões regulares usando as funções de expressões regulares do Oracle 11g Database.
Citação Alternativa
Começando com o Oracle 10g Release 1, o banco de dados agora permite substituir a familiar
aspas por outro símbolo de citação. Isto é útil quando tiver uma quantidade de apóstrofes em
uma string que exige, individualmente, citação anterior com outra citação simples. O velho jeito
seria o seguinte:
SELECT ‘It’’s a bird, no plane, no it can’’t be ice cream!’ AS phrase
FROM dual;
A nova maneira é
SELECT q’(It’s a bird, no plane, no it can’t be ice cream!)’ AS phrase
FROM dual;
Ambos produzem o seguinte output:
PHRASE
-----------------------------------------------It’s a bird, no plane, no it can’t be ice cream!
[N.T.: Ele é um pássaro, não um avião, nem pode ser um sorvete!]
Capítulo 1: Visão Geral do Oracle Pl/SQL 15
Existem oportunidades para usar a sintaxe mais nova e economizar tempo, mas a maneira
antiga também continua funcionando, pois é mais facilmente compreendido e portátil.
Conjunto de Operadores
Começando com o Oracle 10g, Release 1, o banco de dados agora suporta conjunto de operadores para tabelas alojadas que incluem os operadores MULTISET EXCEPT, MULTISET INTERSECT, MULTISET UNION e MULTISET UNION DISTINCT.
MULTISET UNION comporta-se como o operador familiar UNION ALL. Ele retorna duas
cópias de tudo, na interseção entre dois sets e uma cópia dos complementos relativos.
O MULTISET UNION DISTINCT funciona como o operador UNION. Ele retorna uma cópia
de tudo, executando um tipo de operação incremental. O capítulo 7 discute estes operadores
como também as coleções.
Stack Tracing de Erros
Iniciando com o Oracle 10g Release 1, é possível finalmente formatar stack traces, que produzem uma lista de erros de chamada inicial para o local onde o erro foi lançado. A função usada
é: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE para produzir um stack trace. Também pode
acionar o FORMAT_CALL_STACK ou FORMAT_ERROR_STACK do mesmo pacote para trabalhar com as exceções lançadas. A seguir temos um exemplo simples:
DECLARE
local_exception EXCEPTION;
FUNCTION nested_local_function
RETURN BOOLEAN IS
retval BOOLEAN := FALSE;
BEGIN
RAISE local_exception;
RETURN retval;
END;
BEGIN
IF nested_local_function THEN
dbms_output.put_line(‘No raised exception’);
END IF;
EXCEPTION
WHEN others THEN
dbms_output.put_line(‘DBMS_UTILITY.FORMAT_CALL_STACK’);
dbms_output.put_line(‘------------------------------’);
dbms_output.put_line(dbms_utility.format_call_stack);
dbms_output.put_line(‘DBMS_UTILITY.FORMAT_ERROR_BACKTRACE’);
dbms_output.put_line(‘-----------------------------------’);
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(‘DBMS_UTILITY.FORMAT_ERROR_STACK’);
dbms_output.put_line(‘-------------------------------’);
dbms_output.put_line(dbms_utility.format_error_stack);
END;
/
16 Oracle Database 11g PL/SQL Programação
Este script produz o seguinte output:
DBMS_UTILITY.FORMAT_CALL_STACK
---------------------------------- PL/SQL Call Stack ----object line object
handle number
name
20909240 18 anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
----------------------------------ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
------------------------------ORA-06510: PL/SQL: unhandled user-defined exception
Provavelmente achará FORMAT_ERROR_BACKTRACE o mais útil. Ele captura a linha onde
o primeiro erro ocorreu no topo e, depois, move para trás através de ligações até chegar ligação
inicial. Números de linha e nomes de programas são exibidos juntos quando blocos nomeados
estão envolvidos no mesmo evento stack.
O Capítulo 5 contém mais sobre gerenciamento de erro.
Wrapping Programas PL/SQL Armazenados
Começando com o Oracle 10g Release 2, o banco de dados agora suporta a habilidade de
wrap ou ofuscar seus programas PL/SQL armazenados.
Isto é feito usando o pacote DBMS_DDL do procedimento CREATE_WRAPPED. Isto pé usado da seguinte forma:
BEGIN
dbms_ddl.create_wrapped(
‘CREATE OR REPLACE FUNCTION hello_world RETURN STRING AS ‘
||’BEGIN ‘
||’ RETURN ‘’Hello World!’’; ‘
||’END;’);
END;
/
Após criar a função, você pode solicitar isso usando a seguinte formatação de coluna
SQL*Plus e query:
SQL> COLUMN message FORMAT A20 HEADING “Message”
SQL> SELECT hello_world AS message FROM dual;
Message
-------------------Hello World!
É possível descrever a função para inspecionar sua assinatura e tipo retorno:
SQL> DESCRIBE hello_world
FUNCTION hello_world RETURNS VARCHAR2
Capítulo 1: Visão Geral do Oracle Pl/SQL 17
Qualquer tentativa de inspecionar suas operações detalhadas irá produzir um efeito ofuscado. Teste isto através de querying a função implementada, armazenada na coluna TEXT da
tabela USER_SOURCE, como a seguir:
SQL> COLUMN text FORMAT A80 HEADING “Source Text”
SQL> SET PAGESIZE 49999
SQL> SELECT text FROM user_source WHERE name = ‘HELLO_WORLD’;
O seguinte output é retornado:
FUNCTION hello_world wrapped
a000000
369
abcd
. . . et cetera . . .
Esta é uma ferramenta muito útil para esconder os detalhes da implementação de olhos
curiosos. Nós voltaremos a este tópico no Apêndice F.
Oracle 11g Novas Características
Novas características PL/SQL introduzidas no Oracle 11g incluem:
■■
Inlining subprograma automático
■■
Uma declaração continue
■■
Uma função cross-session PL/SQL result cache
■■
Melhoramentos no Dynamic SQL
■■
Mixado, nomeado e notação posicional SQL calls
■■
Um conjunto de conexões multiprocesso
■■
Um PL/SQL Hierarchical Profiler
■■
O PL/SQL Native Compiler agora gera código nativo
■■
PL/Scope
■■
Melhoramentos nas expressões regulares
■■
A SIMPLE_INTEGER datatipo
■■
Chamadas de seqüência direta nos statements SQL
Estes melhoramentos são brevemente discutidos nas seguintes subseções: o Capítulo 3 cobre
o datatipo SIMPLE_INTEGER.
O Capítulo 4 discute o statement contínuo.
O capítulo 6 demonstra a função cross-sessão PL/SQL result cache e as chamadas de notação posicional mixadas e nomeadas.
Inlining de subprograma automatic e o PL/SQL Native Compiler são vistos no Capítulo 9.
O Capítulo 16 discute o desenvolvimento de aplicações na web e o conjunto de conexão de
multiprocesso. Encontrará também mais informação sobre a Expressão Regular, PL/SQL Hierarchical Profiler e PL/Scope nos Apêndices E, G e H, respectivamente.
18 Oracle Database 11g PL/SQL Programação
Inlining Subprograma Automático
Inlining um subprograma substitui a chamada para subprogramas externos com uma cópia do
subprograma. Isto quase sempre melhora o desempenho do programa.
Você poderia instruir o compilador para inline subprogramas usando a diretriz compiladora PRAGMA INLINE no PL/SQL começando com o Oracle 11g Database. Você deve ajustar o
PRAGMA quando tiver o parâmetro PLSQL_OPTIMIZE_LEVEL ajustado para 2.
Vamos dizer que tenha uma função ADD_NUMBERS armazenada em um esquema;
então instrua uma unidade programa PL/SQL para inline uma chamada para a função ADD_
NUMBERS.
Isto seria muito útil quando chama a função ADD_NUMBERS em um loop, como neste
exemplo:
CREATE OR REPLACE PROCEDURE inline_demo
(a NUMBER
, b NUMBER ) IS
PRAGMA INLINE (add_numbers,’YES’);
BEGIN
FOR i IN 1..10000 LOOP
dbms_output.put_line(add_function(8,3));
END LOOP;
END;
/
O banco de dados automatiza escolhas inlining quando ajusta o parâmetro PLSQL_OPTIMIZE_LEVEL para 3. Isto geralmente o livra de identificar quando ele é apropriado para inline uma
função de chamadas.
Todavia, estas são apenas recomendações para o compilador. É recomendado que deixe o
motor otimizar seu código durante a compilação.
Declaração Continue
O declaração CONTINUE foi finalmente adicionado à linguagem PL/SQL. Alguns podem ter
emoções mistas. Existem opiniões de que a declaração contínuo leva a uma programação menos
que optimal, mas geralmente ele simplifica as estruturas loop.
A declaração CONTINUE sinaliza um final imediato para uma interação loop e retorna para
a primeira declaração no loop. O bloco anônimo a seguir ilustra isto usando um declaração
contínue quando o índice loop é um número par:
BEGIN
FOR i IN 1..5 LOOP
dbms_output.put_line(‘First statement, index is [‘||i||’].’);
IF MOD(i,2) = 0 THEN
CONTINUE;
END IF;
dbms_output.put_line(‘Second statement, index is [‘||i||’].’);
END LOOP;
END;
/
A função MOD retorna um zero ao dividir qualquer número par, então, a segunda linha
nunca é impressa porque a declaração CONTINUE aborta o resto do loop.
Capítulo 1: Visão Geral do Oracle Pl/SQL 19
Mais informação sobre o uso deste comando está no Capítulo 4.
O Apêndice J descreve a função MOD.
A Função Cross-Session PL/SQL Result Cache
A função cross-session PL/SQL result cache [resultado escondido] é um mecanismo para repartir
funções freqüentemente acessadas no SGA entre sessões.
Antes do Oracle 11g Database, cada chamada para uma função com os mesmos parâmetros
reais ou valores run time era cache uma vez por sessão. O único trabalho a mais para esta funcionalidade exigiria que codificasse os métodos de acesso. Você designa um dos seguintes result
cache [resultados escondidos]:
RESULT_CACHE clause
ou
RESULT_CACHE RELIES_ON(table_name)
A cláusula RELIES_ON coloca uma limitação no result cache. Qualquer mudança para a tabela
mencionada invalida a função, como também quaisquer funções, procedimentos ou vistas que
dependem da função.
O resultado quando chama a função pela primeira vez não é diferente do resultado noncache. Da mesma forma, o cache irá envelhecer fora do SGA quando não mais for chamado
ativamente pelas sessões.
Melhoramentos SQL Dinâmico
SQL dinâmico ainda tem duas variedades no Oracle 11g Database.
Também tem o Native Dynamic SQL, conhecido como NDS e o pacote DBMS_SQL embutido.
Ambos foram aperfeiçoados nesta nova versão.
SQL Dinâmico Nativo
No Oracle 11g, SQL dinâmico native agora suporta statements dinâmicos maiores que 32KB
pela aceitação de CLOB, que é acessado depois, em lugar de um statement SQL usando a
seguinte sintaxe:
OPEN cursor_name FOR dynamic_string;
A string dinâmica pode ser uma CHAR, VARCHAR2 ou CLOB. Ela não pode ser um Unicode
NCHAR ou NVARCHAR2. Isto remove a restrição anterior que limitava o tamanho das strings
construídas dinamicamente.
O Pacote DBMS_SQL Embutido (Built-in)
Diversas alterações têm melhorado a utilidade do pacote DBMS_SQL. Começando com o
Oracle 11g, agora é possível usar todos datatipos NDS-suportáveis, como também o procedure PARSE para trabalhar com statements maiores que 32KB. Isto é feito usando-se um datatipo
CLOB, que substituiu o trabalho anterior, onde era usado uma tabela de datatipos VARCHAR2
(tipicamente VARCHAR2A ou VARCHAR2S).
Felizmente o pacote DBMS_SQL continua a suportar o contorno, mas deve refletir sobre
mover adiante e encontrar uma solução melhor.
20 Oracle Database 11g PL/SQL Programação
DBMS_SQL adicionou duas novas funções: as funções TO_REFCURSOR e TO_CURSOR_
NUMBER, que permitem transferir cursor referência para cursores e vice versa.
Existem naturalmente palavras de sabedoria sobre o uso destes. Abra o cursor ou o sistema
de referência cursor antes de usá-los e, após executá-los, não poderá acessar as velhas estruturas.
Basicamente, o código reinstala a referência interativa a partir do cursor para o sistema de
referência cursor ou a partir dos sistemas de referência cursor para o cursor.
Por último, mas certamente não menos importante, é possível, agora, executar operações
biding bulk frente a tipos de coleção definidas pelo usuário.
Tipos de coleção podem ser tabelas escalares. Anteriormente estava restrito aos tipos definidos pelas especificações dos pacotes DBMS_SQL.
Nome Mixado e Notação de Posição de Chamadas
O Oracle 11g Database traz mudanças na maneira de nomear e no trabalho de notação posicional em ambos SQL e PL/SQL.
Na verdade, eles agora trabalham da mesma maneira tanto no SQL quanto no PL/SQL. Isto corrige este antigo costume no banco de dados no Oracle.
PL/SQL Chamadas
Antes você tinha duas escolhas: poderia listar todos os parâmetros em suas ordens posicionais
ou endereçar alguns para todos os parâmetros pela referência nomeada. Agora pode usar
referência posicional, a referência nomeada ou uma mistura de ambos.
A função a seguir permitirá experimentar os diferentes enfoques. A função aceita três parâmetros opcionais e retorna a soma dos três números.
CREATE OR REPLACE FUNCTION add_three_numbers
( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
BEGIN
RETURN a + b + c;
END;
/
As primeiras três subseções mostram como chamar usando notação posicional, nomeada e
mixada. Nelas você determina os parâmetros reais para cada um dos parâmetros formais definidos pela assinatura da função.
Também pode excluir um ou mais valores porque todos os parâmetros formais estão definidos como opcionais, o que significa que têm valores padrão. Isto é feito na subseção “Notação
Excludente.”
Notação Posicional
É possível chamar a função usando notação posicional através de
BEGIN
dbms_output.put_line(add_three_numbers(3,4,5));
END;
/
Notação Nomeada
Chame a função usando notação nomeada através de
Capítulo 1: Visão Geral do Oracle Pl/SQL 21
BEGIN
dbms_output.put_line(add_three_numbers(c => 4,b => 5,c => 3));
END;
/
Notação Mixada
Chame a função usando uma mistura de ambas as notações posicionada e nomeada através de
BEGIN
dbms_output.put_line(add_three_numbers(3,c => 4,b => 5));
END;
/
Existe uma restrição quanto à notação mixada. Todas as notações posicionais de parâmetros
reais devem ocorrer primeiro e na mesma ordem em que foram definidas pela função assinatura.
Não é possível determinar um valor posicional após um valor nomeado.
Notação Excludente
Como foi mencionado, também pode excluir um ou mais Parâmetros reais quando os parâmetros formais estão definidos como opcional. Todos os parâmetros na função ADD_THREE_NUMBERS são opcionais. O exemplo a seguir passa um valor para o primeiro parâmetro pela referência posicional e o terceiro parâmetro pela referência nomeada:
BEGIN
dbms_output.put_line(add_three_numbers(3,c => 4));
END;
/
Quando optar por não fornecer um parâmetro real, ela age como se estivesse passando um
valor nulo. Isto é conhecido como notação excludente. Esta tem sido a recomendação de anos
para listar as variáveis últimas na função e assinaturas do procedimento.
Agora, pode excluir um ou alguns, mas não todos os parâmetros opcionais. Este é um grande
avanço, mas tenha cuidado na forma como irá explorá-lo.
Notação Chamada SQL
Antes havia apenas uma escolha, onde era preciso listar todos os parâmetros em suas ordens
posicionais, pois não poderia usar referência nomeada no SQL. Isto foi corrigido no Oracle 11g;
agora é possível chamá-los do mesmo jeito que faz de um bloco PL/SQL.
O exemplo a seguir demonstra notação mixada em uma chamada SQL:
SELECT add_three_numbers(3,c => 4,b => 5) FROM dual;
Como nas versões anteriores, apenas podia chamar funções que tinham somente variáveis
no modo IN dos statements SQL. Não é possível chamar uma função do SQL quando qualquer
um de seus parâmetros formais tiver definido apenas como variáveis no modo IN OUT ou OUT.
Isto porque deve passar uma referência variável quando um parâmetro estiver em um modo
OUT. Funções retornam uma referência para variáveis do modo OUT passadas como
parâmetros reais.
22 Oracle Database 11g PL/SQL Programação
Conjunto de Conexão de Multiprocess
Enterprise JavaBeans (EJBs) ficou melhor com a liberação do conjunto de conexão de multiprocess no Oracle 11g Database. Ele é, oficialmente, Database Resident Connection Pooling
(DRCP). Esta característica permite gerenciar mais conjuntos de conexão capazes de serem
escalas pelo provedor. Antes disso precisava alavancar conjunto de conexão repartido ou Java
Servlet de múltiplos threads.
O conjunto de conexão multiprocess reduziu significantemente a memória de pegada na
fileira do banco de dados e ela melhora o aumento da capacidade de execução de um computador de ambas as fileiras do meio e do banco de dados. Uma conexão de banco de dados padrão
requer 4.4MB de memória real; 4MB é alocada para a conexão física e 400KB para a sessão
usuário.
Portanto, 500 conexões concorrentes dedicadas exigiriam aproximadamente 2.2GB de memória real. Um modelo provedor-dividido é mais escalável e exige apenas 600MB de memória
real para o mesmo número de usuários concorrentes. Oitenta por cento desta memória seria gerenciada no Shared Global Area (SGA) do Oracle. Database Resident Connection Pooling escala
melhor e exigiria apenas 400MB de memória real. Certamente, para aplicação na web, DRCP é
a solução preferida, especialmente quando usar conexões persistentes OCI8.
Os comportamentos destes modelos ditam suas respectivas escalabilidades. A Figura 1-2
demonstra graficamente o uso de memória para os três modelos de 500 a 2 mil usuários
concorrentes.
FIGURA 1-2 Escalabilidade de Conexão
A nova característica é entregue pelo novo pacote embutido DBMS_CONNECTION_POOL.
Este pacote permite iniciar, parar e configurar parâmetros pooling como, por exemplo, tamanho
e limite de tempo. Começe a conexão pool como o usuário SYS através do comando a seguir:
SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
Ative seus arquivos tnsnames.ora para suportar a conexão de um poll repartido. O seguinte
permite conectar um descriptor de conexão pool dividido, desde que substitua um hostname
correto, um domain, e um Oracle listener port number:
Capítulo 1: Visão Geral do Oracle Pl/SQL 23
ORCLCP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = hostname.domain)
(PORT = port_number)
)
(CONNECT_DATA = (SERVER = POOLED)
(SERVICE_NAME = orcl)
)
)
A chave SERVER no descriptor connect direciona as conexões para o service de conexão
pooling . É possível apenas usar o pool conexão quando tiver um Oracle 11g Database apoiado
ou Oracle 11g Client, embora esta característica possa ser backport subsequentemente. O erro a
seguir acontece quando se tenta conectar com uma versão mais velha ou biblioteca do provedor:
ERROR:
ORA-56606: DRCP: Client version does not support the feature.
A mensagem é sinalizada, vinda do provedor, quando ela falha em criar uma tomada apropriada; ela indica que está caindo o pool (conjunto) de requisição de conexão remota.
A Tabela 1-2 lista o dicionário da exibição de dados para conferência dos pools de conexão.
Você pode usá-los para monitorar conexões ou características de desempenho.
Para parar o pool de conexão como o usuário SYS use o seguinte comando:
SQL> EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();
Exibição
Descrição
DBA_CPOOL_INFO
O status, conexões máxima e mínima e o idle timeouts [tempo
desocupado] estão disponíveis nesta exibição para cada pool de
conexão.
V$CPOOL_STAT
O número de sessões solicitadas, o número de vezes em que uma
sessão corresponde a uma solicitação encontrada no pool e o tempo
total de espera por sessão de solicitação estão disponíveis nesta
exibição.
V$CPOOL_CC_STATS
As estatísticas de conexão de nível de classe para cada exemplo do
pool de conexão.
TABELA 1-2 Dicionário das Exibições dos Dados do Pool de Conexão
Parece que a liberação inicial irá suportar apenas um pool de conexão único. O nome do
pool de conexão é SYS_DEFAULT_CONNECTION_POOL. Também existem três outros procedimentos no pacote DBMS_CONNECTION_POOL para gerenciar o pool de conexão: os procedimentos ALTER_PARAM(), CONFIGURE_POOL() e RESTORE_DEFAULTS().
Para alterar um único parâmetro pool de conexão use o procedimento ALTER_PARAM().
Quando quiser alterar mais de um para todos parâmetros, use o procedimento CONFIGURE_
POOL(). O procedimento RESTORE_DEFAULTS() reajusta todos os parâmetros pool de conexões
para seus defaults (padrões).
Esta nova característica do Oracle 11g Database certamente melhora a escalabilidade [aumento da capacidade de execução de um computador] das aplicações da web. É uma caracterís-
24 Oracle Database 11g PL/SQL Programação
tica importante que autoriza a característica de conexão persistente introduzida nas bibliotecas
OC18 no banco de dados Oracle 10g Release 2.
PL/SQL Profiler Hierárquico
O profiler hierárquico entregue no Oracle 11g Database permite ver como todos componentes
em uma aplicação atuam. Isto difere de um profiler não hierárquico (flat) que simplesmente grava o tempo gasto em cada módulo. Os profilers hierárquicos seguem o ciclo de execução vindos
do programa contido até o mais baixo subprograma.
■■
O PL/SQL Hierarchical Profiler faz o seguinte:
■■
Ele informa o perfil de execução dinâmica de seu programa PL/SQL, que está
organizado por chamadas de subprogramas.
■■
Ele divide os tempos de execução SQLe PL/SQL e informa-os separadamente.
■■
Ele não exige nenhum tipo especial de fonte ou preparação de tempo de compilação, como o PRAGMA que é exigido para recomendação inlining .
■■ Ele armazena resultados em um conjunto de tabelas de bancos de dados, os
quais pode usar para desenvolver ferramentas de informação ou usar como
alternativa a ferramenta linha-comando plshprof para gerar apresentações
HTML simples.
O sumário do nível dos subprogramas inclui informação sobre o número de chamadas de
subprogramas, tempo gasto em subprogramas ou em suas sbtrees [sub árvores] e informações
detalhadas entre os módulos.
O Apêndice G discute como ler e usar o PL/SQL Hierarchical Profiler.
PL/SQL Compilador Nativo Gera Código Nativo
A compilação PL/SQL Native muda no Oracle 11g Database. É diferente das versões anteriores
onde o PL/SQL foi traduzido primeiro para código C e então compilado; agora é possível compilar diretamente.
O tempo de execução do código final, em alguns casos, é duas vezes mais rápido ou uma
ordem de magnitude maior.
O Oracle recomenda que execute todo PL/SQL em modo NATIVE ou INTERPRETED, que
é o padrão do banco de dados, e módulos PL/SQL são armazenados como texto claro ou texto
wrapped.
É possível ver programas armazenados através de querying todos ALL_SOURCE, DBA_
SOURCE ou tabelas de dicionários de dados USER_SOURCE .
O código modo NATIVE é compilado dentro de uma forma intermediária antes de ser
reduzido a um código específico da máquina. Uma cópia do código é também armazenada no
dicionário de dados, enquanto a biblioteca é posicionada em um diretório externo. Você mapeia
o diretório físico para o diretório virtual definido pelo parâmetro virtual PLSQL_NATIVE_LIBRARY_DIR.
O código nativamente compilado é vantajoso quando o código de tempo de execução [run
time] PL/SQL é lento. Isto pode acontecer com o código de intensivo-cumputar, mas geralmente,
os atrasos na execução são causados pelo processamento de statement SQl.
O novo PL/SQL Hierarchical Profiler deve ser usado para determinar se existe uma vantagem
significante que justifique seu esforço de conversão.