Plsql Avancado

PL/SQL Avançado

2

1

MUTATING TABLES ............................................................................................................................. 4 1.1 1.2 1.3

2

O QUE É MUTATING TABLE ?......................................................................................................... 4 SIMULANDO UM MUTATING TABLE ............................................................................................... 4 QUAIS EVENTOS CAUSAM MUTATING TABLE................................................................................. 5

CURSOR VARIABLES ........................................................................................................................... 7 2.1 2.2 2.3 2.4 2.5 2.6

3

CARACTERÍSTICAS DAS VARIÁVEIS CURSOR ....................................................................................... 7 DECLARANDO O TIPO REF CURSOR E A VARIÁVEL DO TIPO CURSOR ............................................... 8 ABRINDO UMA VARIÁVEL DO TIPO CURSOR (OPEN - FOR) ............................................................... 9 EXTRAINDO DADOS DA VARIÁVEL DO TIPO CURSOR (FETCH) .......................................................... 9 FECHANDO UMA VARIÁVEL DO TIPO CURSOR (CLOSE) ..................................................................... 9 EXEMPLO DE VARIABLE CURSOR ................................................................................................ 10

LOCAL MODULES............................................................................................................................... 11 3.1

EXEMPLO DE UM LOCAL MODULE ................................................................................................ 11

4

OVERLOADING.................................................................................................................................... 12

5

FUNCTIONS EM UMA INSTRUÇÃO SQL ....................................................................................... 14 5.1

6

PRAGMA RESTRICT_REFERENCES........................................................................................... 14

PRIMARY KEY X WHERE CURRENT OF X ROWID PARA UPDATE..................................... 17 6.1 ALTERANDO PELA PRIMARY KEY.................................................................................................. 17 6.2 ALTERANDO PELA WHERE CURRENT OF ..................................................................................... 18 6.2.1 Efeitos causados pelo FOR UPDATE ...................................................................................... 18 6.3 ALTERANDO PELO ROWID ............................................................................................................... 18 6.4 COMPARAÇÃO PRIMARY KEY X WHERE CURRENT OF X ROWID PARA UPDATE............... 19

7

EXPLICIT CURSORS X IMPLICIT CURSORS ............................................................................... 21 7.1

COMPARANDO IMPLICIT CURSOR X EXPLICIT CURSOR.......................................................... 22

8

ORACLE AND PL/SQL FEATURES BY VERSION NUMBER ...................................................... 23

9

DEPENDÊNCIAS E RECOMPILAÇÃO DE OBJETOS................................................................... 24 9.1 9.2 9.3 9.4 9.5

10 10.1 10.2 10.3 10.4 10.5 10.6 10.7

DEPENDÊNCIA DIRETA E INDIRETA .................................................................................................... 24 DEPENDÊNCIA LOCAL E REMOTA ...................................................................................................... 25 CONSIDERAÇÕES SOBRE A RECOMPILAÇÃO AUTOMÁTICA DE OBJETOS .............................................. 26 VISUALIZANDO DEPENDÊNCIAS DIRETAS (USER_DEPENDENCIES) .............................................. 27 VISUALIZANDO DEPENDÊNCIAS DIRETAS E INDIRETAS ....................................................................... 27 ORACLE PACKAGES ...................................................................................................................... 29 DBMS_JOB...................................................................................................................................... 29 DBMS_DDL..................................................................................................................................... 29 DBMS_JOB...................................................................................................................................... 29 DBMS_OUTPUT ............................................................................................................................. 29 DBMS_SQL ..................................................................................................................................... 29 UTL_FILE........................................................................................................................................ 29 DBMS_SHARED_POOL................................................................................................................. 29

11

COBOL E ORACLE .......................................................................................................................... 29

12

EXTERNAL FUNCTIONS................................................................................................................ 29

3

13 13.1 13.2 13.3 13.4 13.5

DIVERSOS (DICAS).......................................................................................................................... 29 HINTS DE PAGINAÇÃO ........................................................................................................................ 29 PLS_INTEGER ................................................................................................................................ 29 VARRAY (ORACLE8) ....................................................................................................................... 29 RECURSIVIDADE ................................................................................................................................ 29 DICIONÁRIO ORACLE ...................................................................................................................... 29

4

1 MUTATING TABLES

1.1

O que é MUTATING TABLE ?

Uma MUTATING table ou tabela mutante é uma tabela que está sendo modificada atualmente por uma instrução UPDATE, DELETE ou INSERT. Uma tabela não é considerada mutante para gatilhos STATEMENT, somente para triggers de ROW (FOR EACH ROW).

1.2 Simulando um MUTATING TABLE Considere a trigger abaixo. Esta é disparada antes do UPDATE (BEFORE UPDATE) para cada row (FOR EACH ROW). A trigger tem a finalidade de verificar se na tabela QUOTAS_AREAS já não existe outro registro com datas intercaladas. A tabela QUOTAS_AREAS é uma tabela MUTATING. Sendo assim, a trigger não executará retornando o seguinte erro : ORA-04091: table USUARIO2.QUOTAS_AREAS is MUTATING, trigger/function may not see it ORA-06512: at "USUARIO2.TRG_BUPD_QUOTAS_AREAS", line 2 ORA-04088: error during execution of trigger 'USUARIO2.TRG_BUPD_QUOTAS_AREAS'

CREATE OR REPLACE TRIGGER TRG_BUPD_QUOTAS_AREAS BEFORE UPDATE ON QUOTAS_AREAS FOR EACH ROW BEGIN FOR REG_AREAS_QUOTAS IN (SELECT * FROM QUOTAS_AREAS WHERE COD_PRODUTO = :NEW.COD_PRODUTO AND COD_AREA = :NEW.COD_AREA AND (DATA_INICIAL BETWEEN :NEW.DATA_INICIAL AND :NEW.DATA_FINAL OR DATA_FINAL BETWEEN :NEW.DATA_INICIAL AND :NEW.DATA_FINAL)) LOOP RAISE_APPLICATION_ERROR (-20001,'Produto já possui quotas cadastrada para este periodo'); END LOOP; END TRG_BINS_QUOTAS_AREAS;

5

1.3

Quais eventos causam MUTATING TABLE

O erro causado por uma tabela MUTATING depende da versão do ORACLE. A cada versão alguns eventos não causam mais MUTATING TABLE. Segue abaixo alguns eventos que causam MUTATING. Este foram testados na ORACLE 8.1.6. TRIGGERS CRIADAS NA TABELA ITENS PEDIDOS COM OPERAÇÕES EM PEDIDOS Evento

Ocorrência

MUTATING

BEFORE INSERT

SELECT na tabela PEDIDOS UPDATE na tabela PEDIDOS INSERT na tabela PEDIDOS DELETE na tabela PEDIDOS SELECT na tabela PEDIDOS UPDATE na tabela PEDIDOS INSERT na tabela PEDIDOS DELETE na tabela PEDIDOS SELECT na tabela PEDIDOS UPDATE na tabela PEDIDOS INSERT na tabela PEDIDOS DELETE na tabela PEDIDOS SELECT na tabela PEDIDOS UPDATE na tabela PEDIDOS INSERT na tabela PEDIDOS DELETE na tabela PEDIDOS SELECT na tabela PEDIDOS UPDATE na tabela PEDIDOS INSERT na tabela PEDIDOS DELETE na tabela PEDIDOS SELECT na tabela PEDIDOS UPDATE na tabela PEDIDOS INSERT na tabela PEDIDOS DELETE na tabela PEDIDOS

NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO NÃO

AFTER INSERT

BEFORE UPDATE

AFTER UPDATE

BEFORE DELETE

AFTER DELETE

6

TRIGGERS CRIADAS NA TABELA ITENS_PEDIDOS COM OPERAÇÕES EM ITENS_PEDIDOS Evento BEFORE INSERT AFTER INSERT BEFORE UPDATE AFTER UPDATE BEFORE DELETE AFTER DELETE

Ocorrência SELECT na SELECT na SELECT na SELECT na SELECT na SELECT na

tabela tabela tabela tabela tabela tabela

ITENS_PEDIDOS ITENS_PEDIDOS ITENS_PEDIDOS ITENS_PEDIDOS ITENS_PEDIDOS ITENS_PEDIDOS

MUTATING NÃO SIM SIM SIM SIM SIM

7

2 CURSOR VARIABLES

A versão 2.3 do PL/SQL, disponível com o ORACLE SERVER Versão 7.3, permite que se crie e utilize variáveis do tipo cursor. Da mesma forma que o cursor convencional, a variável do tipo cursor aponta para a linha corrente no conjunto de resultados de uma consulta que retorne múltiplas linhas, mas ao contrário do cursor estático, que está vinculado à uma única consulta, as variáveis cursor podem ser associadas a várias consultas, até mesmo dentro de um mesmo programa. Variáveis do tipo cursor são verdadeiras variáveis PL/SQL , você pode associar novos valores a ela e passa-la como argumento a subprogramas. Uma variável do tipo cursor é como um ponteiro nas linguagens C e Pascal, ela guarda a posição na memória (endereço) de um objeto ao invés de guardar o próprio objeto. Portanto quando você declara uma variável do tipo cursor você está criando um ponteiro e não um objeto. O principal benefício da variável do tipo cursor é que ela provê um mecanismo para se passar resultados de consultas entre diferentes programas PL/SQL, ou ainda entre programa PL/SQL cliente e programa servidor. Em um ambiente cliente/servidor, por exemplo, um programa do lado cliente poderia abrir a variável do tipo cursor e começar a extrair seus dados, e então passar essa variável como argumento a uma PROCEDURE armazenada no servidor. Este programa poderia então continuar a extrair seus dados e passar a variável de volta ao cliente para que este feche o cursor.

2.1

Características das Variáveis Cursor As variáveis do tipo cursor permitem que você: -

Associe uma variável do tipo cursor com diferentes consultas em tempos diferentes durante a execução de seu programa. Uma variável do tipo cursor pode ser usada para extrair dados de diferentes conjuntos de resultados. Passe uma variável do tipo cursor como argumento a um procedimento ou função.

-

Você pode compartilhar o resultado de uma consulta com outros procedimentos.

8

-

Empregue toda a funcionalidade de cursores estáticos. Você pode utilizar as declarações OPEN, FETCH e CLOSE e referenciar os atributos %ISOPEN,%FOUND, %NOTFOUND e %ROWCOUNT em variáveis do tipo cursor.

-

Associar o conteúdo de uma variável do tipo cursor (inclusive seu conjunto de resultados) a outra variável do tipo cursor.

2.2

Declarando o Tipo REF CURSOR e a Variável do Tipo Cursor

A criação de variáveis do tipo cursor é feita em duas etapas: primeiro você define um tipo REF CURSOR e então declara a variável como sendo daquele tipo. A sintaxe para se criar um tipo de referência a cursor é a seguinte:

TYPE nome_tipo_cursor IS REF CURSOR [RETURN tipo_retornado]; Onde nome_tipo_cursor é o nome do tipo e tipo_retornado é a especificação do dado retornado pelo tipo cursor. O tipo_retornado pode ser qualquer estrutura válida para uma clausula RETURN de um cursor normal, definida usando o atributo %ROWTYPE ou referenciando um registro (record) previamente definido. A clausula RETURN é opcional, e quando usada, o tipo é dito "forte" pois fica atachado a um tipo record, ou tipo row. Qualquer variável do tipo cursor declarada de um tipo "forte" pode apenas ser utilizada com declarações SQL que retornem dados do mesmo tipo da declaração usada na clausula RETURN. Por outro lado, o tipo cursor que não possui a clausula RETURN é dito "fraco" e pode ser utilizado de formas muito mais flexíveis, isto é, pode ser utilizado com consultas que retornem qualquer estrutura de dados. Uma vez declarado o tipo REF CURSOR você pode declarar a variável daquele tipo, como mostrado no seguinte exemplo: DECLARE -- Criando o tipo TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; -- Criando a variável dept_cv DeptCurTyp; BEGIN ... END;

9

É importante lembrar que a declaração de uma variável do tipo cursor não cria um objeto cursor. Para que isto ocorra é necessário usar a sintaxe OPEN FOR, que cria um novo objeto cursor e o associa à variável. Outra observação importante é que variáveis cursor não possuem persistência de estado por serem apenas ponteiros, não podendo portanto serem referenciadas após o fim da execução do procedimento que abriu o cursor. Topo

2.3

Abrindo uma Variável do Tipo Cursor (OPEN - FOR)

A declaração OPEN - FOR associa o cursor com uma consulta que retorne múltiplas linhas, executa a consulta e identifica o conjunto de respostas. A sintaxe para a declaração é: OPEN {nome_variável_cursor } FOR declaração_sql; Onde variável_cursor_de_ambiente é uma variável declarada em um ambiente PL/SQL como o SQL*Plus ou programa Pro*C, e declaração_sql é qualquer declaração SELECT que não possua a clausula FOR UPDATE. Outras declarações OPEN - FOR podem abrir a mesma variável do tipo cursor para diferentes consultas, não sendo necessário para isto fecha-lo antes.

2.4

Extraindo Dados da Variável do Tipo Cursor (FETCH)

Assim como com cursores estáticos, para se obter o resultado da consulta é utilizada a declaração FETCH que extrai as linhas uma a uma da variável do tipo cursor, e possui a seguinte sintaxe: FETCH {nome_variável_cursor } INTO registro; FETCH {nome_variável_cursor } INTO variável1 [,variável2 ...]; Quando a variável do tipo cursor foi declarada como sendo de um tipo "forte", o compilador PL/SQL verifica se a estrutura dos dados após a clausula INTO são compatíveis com a estrutura da consulta associada à variável do tipo cursor, verifica também se o número de variáveis é correspondente ao número de colunas retornadas pela consulta. Caso contrário será gerado um erro. O erro irá ocorrer em tempo de compilação se a variável for de um tipo "forte" e em tempo de execução se a variável for de um tipo "fraco". Em tempo de execução, a PL/SQL evoca a EXCEPTION ROWTYPE_MISMATCH.

2.5

Fechando uma Variável do Tipo Cursor (CLOSE)

A declaração CLOSE desabilita a variável do tipo cursor. Depois disto o conjunto de resultados associado é indefinido. A sintaxe é a seguinte:

10

CLOSE {nome_variável_cursor };

2.6

Exemplo de VARIABLE CURSOR

Este exemplo tem o objetivo de mostrar os dados dos clientes. Através da execução da PROCEDURE prc_exibe_clientes podemos mostra dados dos cliente. Sendo que, esta PROCEDURE possui um parâmetro que determina a quantidade de clientes que devem ser mostrados. Esta PROCEDURE abre cursor (VARIABLE CURSOR) e chama a PROCEDURE PRC_EXIBE_DADOS. Esta, executa FETCHS no cursor. CREATE OR REPLACE PACKAGE PCK_CLIENTES IS -TYPE PC_CLIENTE IS REF CURSOR RETURN CLIENTES%ROWTYPE; -PROCEDURE PRC_EXIBE_CLIENTES (PI_QUANTIDADE NUMBER ); -END PCK_CLIENTES;

CREATE OR REPLACE PACKAGE BODY PCK_CLIENTES IS PROCEDURE PRC_BUSCA_DADOS (PI_CURSOR IN PCK_CLIENTES.PC_CLIENTE ,PI_QUANTIDADE NUMBER) IS -LR_CLIENTE CLIENTES%ROWTYPE; -BEGIN -FOR indice IN 1..pi_quantidade LOOP -FETCH pi_cursor INTO lr_cliente; EXIT WHEN pi_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Nome : '||lr_cliente.nome_cliente); -END LOOP; -END PRC_BUSCA_DADOS; -PROCEDURE PRC_EXIBE_CLIENTES (PI_QUANTIDADE NUMBER ) IS -LR_CLIENTE PCK_CLIENTES.PC_CLIENTE; -BEGIN -OPEN lr_cliente FOR SELECT * FROM CLIENTES; PRC_BUSCA_DADOS (lr_cliente,PI_QUANTIDADE);

11

CLOSE lr_cliente; -END PRC_EXIBE_CLIENTES; END PCK_CLIENTES;

3 LOCAL MODULES

Um local module é uma PROCEDURE ou FUNCTION que é definida dentro na seção de declaração de bloco PL/SQL (Anônimo ou nomeado). Este módulo é chamado local porque é unicamente definido dentro de um bloco PL/SQL pai. Este não pode ser chamado por outro bloco PL/SQL externo.

3.1

Exemplo de um LOCAL MODULE

O exemplo abaixo tem a finalidade de mostra o nome dos clientes com base na tabela cliente. Este possui um LOCAL MODULE denominado prc_imprime. CREATE OR REPLACE PROCEDURE prc_mostra_clientes IS PROCEDURE prc_imprime (p_nome clientes.nome_cliente%TYPE) IS BEGIN DBMS_OUTPUT.PUT_LINE (p_nome); END; BEGIN FOR reg_emp in (select nome_cliente from clientes) loop prc_imprime(reg_emp.nome_cliente);

12

END LOOP; END;

4 OVERLOADING

Esse é um recurso que permite que você defina diferentes subprograma s (FUNCTIONS/P´ROCEDURES) com o mesmo nome dentro de uma pacote (PACKAGE). A maneira de destinguir os subprogramas é pelo nome e número de parâmetros. Como exemplo, usaremos uma PACKAGE do ORACLE, a UTL_FILE.

FUNCTION FOPEN RETURNS RECORD Argument Name Type In/Out Default? ---------------------------------------- ------ -------ID BINARY_INTEGER OUT LOCATION VARCHAR2 IN FILENAME VARCHAR2 IN OPEN_MODE VARCHAR2 IN FUNCTION FOPEN RETURNS RECORD Argument Name Type In/Out Default? ---------------------------------------- ------ -------ID BINARY_INTEGER OUT LOCATION VARCHAR2 IN FILENAME VARCHAR2 IN OPEN_MODE VARCHAR2 IN MAX_LINESIZE BINARY_INTEGER IN

Tais estão definidas na PACKAGE SPECIFICATION. Estas, possuem o mesmo nome, diferenciando-se somente no número de parâmetros. Quanto a PROCEDURE for executada com o parâmetro MAX_LINESIZE o ORACLE irá executar a PROCEDURE que possui este parâmetros. Podemos usar o OVERLOADING para manter duas versões de uma mesma rotina. No exemplo citado acima, a ORACLE inicialmente não possuía o parâmetro MAX_LINESIZE na função OPEN. Para manter a compatibilidade de

13

em código de programas já construídos, criou-se uma nova com o parâmetro MAX_LINESIZE. Obs. Para identificar qual subprograma será executado utiliza-se inicialmente o número de parâmetros, caso este seja idêntico nos procedimento, utiliza-se o tipo de dados dos parâmetros. Ex. Observe abaixo os procedimentos PUT_LINE definidos na PACKAGE SPECIFICATION da DBMS_OUTPUT. Ambas possuem o mesmo nome e número de parâmetros. No entanto, o tipo de dado do parâmetro é diferente nos três procedimentos.

PROCEDURE PUT_LINE Argument Name Type In/Out Default? ----------------------- -------------------- ------ -------A VARCHAR2 IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ----------------------- -------------------- ------ -------A NUMBER IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ----------------------- -------------------- ------ -------A DATE IN

14

5 FUNCTIONS EM UMA INSTRUÇÃO SQL

O ORACLE permite o uso de funções armazenados no banco de dados em uma instrução SQL. Tal recurso é utilizado geralmente para facilitar o entendimento de consultas complexas. Ex. Considere que o cálculo de aposentadoria de um empregado é o resultado várias consultas em várias tabelas. Sendo assim. poderíamos criar uma função (stored procedure) que retorna o valor de beneficio com base em algumas informações da tabela empregado. SELECT nome, FNC_VLR_BENEFICIO (cod_emp, salario, data_admissão) FROM empregados No entanto algumas restrições deve ser respeitadas : -

-

A função não pode modificar tabelas de banco de dados; portanto, ela não pode executar INSERT, UPDATE e DELETE As funções que lêem os valores de variáveis empacotadas não pode ser executadas remotamente ou em paralelo Somente funções chamadas em uma cláusula SELECT, VALUES ou SET podem gravar ou valores em variáveis empacotadas (variáveis definidas na PACKAGE SPECIFICATION) A função não pode chamar outro subprograma que quebre uma das regras acima.

Quando executarmos um comando SQL que possui chamada a uma função de banco e esta quebrar uma das restrições acima, um erro acontecerá.

5.1

PRAGMA RESTRICT_REFERENCES

Assegura que as restrições ao uso de funções em instruções SQL não sejam violadas. Não permite que a função seja criada se esta viola as restrições. É obrigatório no caso de chamar uma função dentro de um comando SQL.

15

Sintaxe PRAGMA RESTRICT_REFERENCES (function_name, WNDS [,WNPS] [,RNDS] [,RNPS] );

WNDS WNPS RNDS RNPS

Writes No Database State. A função não pode modificar as tabelas do banco de dados Writes No Package State. A função não pode modificar valores de variáveis de PACKAGE Read No Database State. A função não pode consultar as tabelas do banco de Read No Package State. A função não pode ler a variáveis de package

Exemplo

CREATE OR REPLACE PACKAGE PCK_FOLHA IS FUNCTION fnc_vlr_beneficio (p_cod_emp NUMBER, ,p_salario NUMBER ,p_admissão NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES (fnc_vlr_beneficio, WNDS); END; O comando PRAGMA definido abaixo da definição da função FNC_VLR_BENEFICIO assegura a que a função FNC_VLR_BENEFICIO não irá alterar tabelas do banco (WNDS). Na criação do corpo da PACKAGE, será valida esta restrição. Caso exista uma alteração em alguma tabela do banco, a PACKAGE será criada com erro.

16

17

6 PRIMARY KEY X WHERE CURRENT OF x ROWID para UPDATE

Freqüentemente, você pode deparar com a seguinte situação. Abrir um cursor de uma tabela, executar um complexo processamento com base nos dados obtidos e alterar a tabela em questão.

6.1

Alterando pela PRIMARY KEY DECLARE CURSOR c_emp IS SELECT * FROM EMPREGADOS WHERE SALARIO > 50000 novo_salario number; BEGIN FOR emp_row in c_emp LOOP novo_salario := calcula_salario (emp_row.salario) ; update empregados set salario = novo_salario where cd_empregado = emp_row.cd_empregado; END LOOP; END;

O update dentro do loop, usa o primary key da tabela, que utiliza o índice da primay key para efetuar busca. No entanto, surge uma questão : porque precisaríamos executar uma busca no índice da primary key se já temos conhecimento do registro corrente. De fato, o segundo index lookp é desnecessário. PL/SQL pode referenciar o registro corrente através do uso da clausula WHERE CURRENT OF cursor_name. Usando esta notação, PL/SQL pode usar um row address (ROWID) armazenado na estrutura do cursor para localizar o registro sem um index lookup.

18

6.2

Alterando pela WHERE CURRENT OF DECLARE CURSOR c_emp IS SELECT * FROM EMPREGADOS WHERE SALARIO > 50000 FOR UPDATE novo_salario number; BEGIN FOR emp_row in c_emp LOOP novo_salario := calcula_salario (emp_row.salario) ; update empregados set salario = novo_salario where current of c_emp; END LOOP; END;

O WHERE CURRENT OF clase elimina o I/O envolvido com m index lookup e aumenta a performace do UPDATE. No entanto, para usar o WHERE CURRENT OF, você precisa primeiro locar (LOCK) os registros envolvidos usando a clausula FOR UPDATE no select.

6.2.1 Efeitos causados pelo FOR UPDATE

Todos os registros selecionados pela QUERY precisam ser locados (LOCKED) antes que a primeiro registro possa ser retornado. Se você irá unicamente processar alguns registros, ou se, o tempo de resposta é fundamental, você provavelmente não irá querer usar a clįusula FOR UPDATE. Se você quer otimizar o UPDATE evitando desnecessários index read mas não quer assegurar o lock da cláusula FOR UPDATE, você pode pegar o ROWID and usar este para efetuar o UPDATE.

6.3

Alterando pelo ROWID

19

DECLARE CURSOR c_emp IS SELECT e.rowid, e.* FROM EMPREGADOS e WHERE SALARIO > 50000; novo_salario number; BEGIN FOR emp_row in c_emp LOOP novo_salario := calcula_salario (emp_row.salario) ; update empregados set salario = novo_salario where rowid = emp_row.rowid; END LOOP; END;

6.4 Comparação PRIMARY KEY X WHERE CURRENT OF x ROWID para

UPDATE

Acesso / Instrução PRIMARY KEY FOR UPDATE WHERECURRENT OF ROWID

SELECT 905 2557

UPDATE 2391 1648

906

1616

Obs. Os valores apresentados na tabela representam o número de blocos lido para cada forma de acesso (BLOCK READ).

20

21

7 EXPLICIT CURSORS X IMPLICIT CURSORS O PL/SQL permite que um comando SQL seja incluído em um bloco sem estar explicitamente associado a um cursor. Sege exemplo abaixo : BEGIN SELECTt nome INTO :nome_cliente FROM clientes WHERE contato = 'JOAO'; AND data_aniversario = '10-may-2000'; END; Um cursor é de fato associado com o código acima, mas é criado pela chamada do PL/SQL. Sendo um cursor referenciado, é denominado IMPLICIT CURSOR. Embora IMPLICIT CURSOR seja mais conveniente para a programação. Eles podem causar OVERHEAD. IMPLICID CURSORS podem unicamente retornar uma linha e o ORACLE precisa checar se uma única linha será retornada (TOO MANY ROWS). Se a busca é baseada em um índice, então o cheque pode ser feito em um único adicional I/O. Tipicamente, isto aumenta o numero de leituras exigidas de 3 para 4. Se os dados são baseados em um TABLE SCAN, então o ORACLE continua lendo até o fim da tabela. Em média, isto resulta em 2 acesso adicionais.

DECLARE CURSOR c_nome is SELECT nome INTO :nome_cliente FROM clientes WHERE contato = 'JOAO' AND data_aniversario = '10-may-2000'; BEGIN OPEN C_NOME; FETCH C_NOME; CLOSE C_NOME; END;

22

7.1

Comparando IMPLICIT CURSOR X EXPLICIT CURSOR

IMPLICIT CURSOR EXPLICIT CURSOR

TABLE SCAN 322 84

INDEX LOOKUP 4 3

Obs. Os valores apresentados na tabela representam o número acessos a tabela.

23

8 Oracle and PL/SQL Features by version Number

Oracle Version 6 7.0

PL/SQL Version 1.0 2.0

7.1

2.1

7.2

2.2

7.3

2.3

8.0

8.0

Features Added or Changed Initial Version CHAR datatype changed to fixed length Stored subprograms(procedures, functions, packages, And triggers User-defined composite types – tables e record Intersession communication with the DBMS_PIPE and DBMS_ALERT packages Output in SQL*Plus or Server Manager with the DBMS_OUTPUT package User-defined subtypes Ability to use user-defined functions in SQL statememts Dynamic PL/SQL with the DBMS_SQL package Cursor Variables Ability to use constrained subtypes Ability to schedule PL/SQL batch processing with the DBMS_JOB package Enhancements to cursor variables (allow fetch on server), And weakly typed) File I/O with the UTL_FILE package PL/SQL table attributes and tables of records Triggers stored in compiled form Objects Types Coolection types – nested tables and varrays Advanced Queuing option External Procedures LOB Enhancements

24

8.1

8.1

Native dynamic SQL Java external routimes Invoker´s rights NCOPY parameters Autonomous transactions Bulk operations

9 Dependências e recompilação de objetos

Alguns objetos fazem podem referencia a outros objetos com parte de sua definição. Por exemplo, um procedimento pode conter uma instrução SELECT que seleciona colunas de uma tabela. Por esse motivo, o procedimento é chamado de objeto dependente, enquanto a tabela é chamada de objeto referenciado. Se você alterar a definição de um objeto referenciado, os objetos dependentes podem ou não continuar funcionando adequadamente. O Oracle Server registra, automaticamente, as dependências entre objetos. Para gerenciar as dependências, todos os objetos do esquema possuem um status (válido ou inválido registrado no dicionário de dados na view USER_OBJECTS.

9.1

Dependência Direta e Indireta

Quando um objeto faz referência direta a um objeto denominamos de dependência direta. No entanto, um procedimento pode fazer referência indiretamente a outro objeto através de outro objeto intermediário. Esta referência é denominada dependência indireta. Observe o quadro abaixo :

25

A relação entre o PROCEDIMENTO e a VIEW é denominada dependência direta. Já a relação entre o procedimento e a TABELA é denominada dependência indireta.

9.2

Dependência Local e Remota

Dependências locais caracteriza-se quando os objetos situam-se no mesmo banco de dados. O ORACLE SERVER gerência, automaticamente todas as dependências locais usando uma tabela do dicionário de dados. Quando um referenciado é alterado, os objetos dependentes tornam-se inválidos. Na próxima vez que um objeto inválido for chamado, o ORACLE SERVER recompilará automaticamente.

No caso de dependência remotas, os objetos ficam em nós diferentes. O ORACLE SERVER não gerência dependências entre objetos de esquema remoto a não ser dependências de procedimentos local-para-procedimento-remoto. O

26

procedimento local e todos os seus objetos dependentes serão inválidos, mas não serão recompilados automaticamente quando chamados pela primeira vez.

9.3

Considerações sobre a recompilação automática de objetos -

Em algumas situações defrontamos com o erro ORA???? ao executar um procedimento de banco. Tal erro não acorre após a execução do procedimento novamente. Isto ocorre por que quanto executamos o procedimento na sessão (sql*plus / forms) a data da ultima alteração(timestamp) é armazenada em uma área no ORACLE(library cache). Quando executamos o procedimento novamente, o ORACLE verifica se a data de ultima alteração do procedimento na library cache é a mesma que data armazenada no dicionário ORACLE. Caso exista diferença, significa que a PROCEDURE foi alterada depois que PROCEDURE foi armazenada na library cache . Sendo assim, o ORACLE validar (parse) o procedimento para que não próxima vez o erro não ocorra.

-

Ao alterar objetos remotos, recomenda-se recompilar manualmente os objetos dependentes locais, não se baseando no mecanismo de dependência remota automático, a fim de evitar interrupção da produção

-

O mecanismo de dependência remota é diferente do mecanismo de dependência local. Na primeira vez que um subprograma remoto recompilado é chamado por um procedimento local, obtêm-se um erro de execução e o subprograma local é invalidado; na segunda vez que for chamado, ocorrerá uma recompilação implícita.

27

9.4

Visualizando dependências diretas (USER_DEPENDENCIES)

Podemos acessar a VIEW USER_DEPENDENCIES para identificar as dependências de um objeto. Segue abaixo a estrutura da tabela. Coluna NAME TYPE

REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME

9.5

Descrição Nome do Objeto dependente Tipo do Objeto dependente (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER ou VIEW) Esquema (OWNER) do objeto referenciado Nome do objeto referenciado Tipo do objeto referenciado Database link

Visualizando dependências diretas e indiretas

A ORACLE fornece VIEWS de usuários para exibir dependências diretas e indiretas em um formado hierárquico (TREE). Para isto é necessário as seguintes etapas : a) Certifique-se que o script ULTDTREE.SQL foi executado(com o DBA) b) Preencha a tabela DEPTREE_TEMPTAB com as informações para um determinado objetio referenciado, chamando o procedimento DEPTTREE_FILL. Segue abaixo os parâmetros do procedimento Tipo_objeto Proprieário(owner) Nome_objeto

é o tipo do objeto referenciado é o esquema do objeto referenciado é o nome do objeto

c) Consulte as views DEPTREE e IDDEPTREE SELECT nested_level, type, name FROM deptree ORDER BY seq#; NESTED_LEVEL TYPE NAME ---------------------------------------------------0 TABLE EMP 1 VIEW NEW_EMP 2 PROCEDURE ADD_EMP 1 PROCEDURE QUERY_EMP SELECT * FROM IDEPTREE;

28

DEPENDENCIES ----------------------------------------------------TABLE SCOTT.EMP VIEW SCOTT.NEW_EMP PROCEDURE SCOTT.ADD_EMP PROCEDURE SCOTT.QUERY_EMP

29

10 ORACLE PACKAGES 10.1 DBMS_JOB 10.2 DBMS_DDL 10.3 DBMS_JOB 10.4 DBMS_OUTPUT 10.5 DBMS_SQL 10.6 UTL_FILE 10.7 DBMS_SHARED_POOL

11 COBOL E ORACLE 12 EXTERNAL FUNCTIONS 13 DIVERSOS (DICAS) 13.1 Hints de paginação 13.2 PLS_INTEGER 13.3 VARRAY (oracle8) 13.4 Recursividade 13.5 Dicionário ORACLE

30