PLSQL Avancado

(1)

PL/SQL Avançado

(2)

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions!

Start Free Trial

Cancel Anytime.

(3)

Cancel Anytime.

2 2

1

1 MUTATIMUTATING NG TABLETABLESS ... 44 1.1 O

1.1 OQUE ÉQUE ÉMUTATINGMUTATING TABLETABLE ?...?... 44 1.2 S

1.2 SIMULANDO UMIMULANDO UMMUTATINGMUTATING TABLETABLE... 44 1.3 Q

1.3 QUAIS EVENTOS CAUSAMUAIS EVENTOS CAUSAMMUTATINGMUTATING TABLE...TABLE... 55 2

2 CURSOR CURSOR VARIAVARIABLESBLES... 77 2.1 C

2.1 CARACTERÍSTICAS DASARACTERÍSTICAS DASVVARIÁVEISARIÁVEISCCURSORURSOR... 77 2.2 D

2.2 DECLARANDO OECLARANDO OTTIPOIPOREFREF CURSORCURSORE AE AVVARIÁVEL DOARIÁVEL DOTTIPOIPOCCURSORURSOR... 88 2.3 A

2.3 ABRINDO UMABRINDO UMAVVARIÁVEL DOARIÁVEL DOTTIPOIPOCCURSORURSOR(OPEN(OPEN -- FOR)FOR) ... 99 2.4 E

2.4 EXTRAINDOXTRAINDODDADOS DAADOS DAVVARIÁVEL DOARIÁVEL DOTTIPOIPOCCURSORURSOR(FETCH(FETCH)) ... 99 2.5 F

2.5 FECHANDO UMAECHANDO UMAVVARIÁVEL DOARIÁVEL DOTTIPOIPOCCURSORURSOR(CLOS(CLOSE)E)... 99 2.6 E

2.6 EXEMPLO DEXEMPLO DEVARIABLEVARIABLE CURSOCURSORR ... 1010 3

3 LOCAL LOCAL MODULEMODULES...S... 1111 3.1 E

3.1 EXEMPLO DE UMXEMPLO DE UMLOCALLOCAL MODULMODULEE... 1111 4

4 OVERLOVERLOADINOADING...G... 1212 5

5 FUNCTIFUNCTIONS ONS EM EM UMA UMA INSTRINSTRUÇÃO UÇÃO SQL...SQL... 1414 5.1 PRAGMA

5.1 PRAGMA RESTRICRESTRICT_REFERT_REFERENCES...ENCES... 1414 6

6 PRIMARY PRIMARY KEY KEY X X WHERE WHERE CURRENT CURRENT OF OF X X ROWID PARA ROWID PARA UPDATE...UPDATE... 1717 6.1 A

6.1 ALTERANDO PELALTERANDO PELAPRIMARYPRIMARY KEY...KEY... 1717 6.2 A

6.2 ALTERANDO PELALTERANDO PELAWHEREWHERE CURRENTCURRENT OFOF ... 1818 6.2.1

6.2.1 Efeitos Efeitos causados causados pelo pelo FOR FOR UPDATE UPDATE ... ... 1818 6.3 A

6.3 ALTERANDO PELOLTERANDO PELOROWIDROWID ... 1818

6.4

6.4 CCOMPARAÇÃOOMPARAÇÃOPRIMARYPRIMARY KEYKEY XX WHEREWHERE CURRENTCURRENT OFOFXXROWIDROWID PARAPARAUPDAUPDATE...TE... 1919 7

7 EXPLIEXPLICIT CIT CURSORCURSORS S X X IMPLIIMPLICIT CIT CURSOCURSORSRS ... 2121 7.1 C

7.1 COMPARANDOOMPARANDOIMPLICITIMPLICIT CURSORCURSOR XX EXPLICITEXPLICIT CURSORCURSOR... 2222 8

8 ORACLE ORACLE AND AND PL/SQPL/SQL L FEATUFEATURES RES BY BY VERSIVERSION ON NUMBERNUMBER... 2323 9

9 DEPENDDEPENDÊNCIAÊNCIAS S E E RECOMPIRECOMPILAÇÃO LAÇÃO DE DE OBJETOBJETOS...OS... 2424 9.1 D

9.1 DEPENDÊNCIAEPENDÊNCIADDIRETA EIRETA EIINDIRETANDIRETA... 2424 9.2 D

9.2 DEPENDÊNCIAEPENDÊNCIALLOCAL EOCAL ERREMOTAEMOTA... 2525 9.3 C

9.3 CONSIDERAÇÕES SOBRE A RECOMPILAÇÃO AUTOMÁTICA DE OBJETOSONSIDERAÇÕES SOBRE A RECOMPILAÇÃO AUTOMÁTICA DE OBJETOS... 2626 9.4 V

9.4 VISUALIZANDO DEPENDÊNCIAS DIRETASISUALIZANDO DEPENDÊNCIAS DIRETAS(USER(USER_DEPENDE_DEPENDENCIES)NCIES)... 2727 9.5 V

9.5 VISUALIZANDO DEPENDÊNCIAS DIRETAS E INDIRETASISUALIZANDO DEPENDÊNCIAS DIRETAS E INDIRETAS... 2727 10

10 ORACLE ORACLE PACKAGPACKAGESES... 2929 10.1 10.1 DBMS_JDBMS_JOB...OB... 2929 10.2 10.2 DBMS_DDBMS_DDL...DL... 2929 10.3 10.3 DBMS_JDBMS_JOB...OB... 2929 10.4

10.4 DBMS_ODBMS_OUTPUT...UTPUT... 2929 10.5

10.5 DBMS_SDBMS_SQLQL ... 2929 10.6

10.6 UTL_FIUTL_FILE...LE... 2929 10.7

10.7 DBMS_SDBMS_SHAREDHARED_POOL..._POOL... 2929 11

(4)

Cancel Anytime.

(5)

Cancel Anytime.

3 3

13

13 DIVERSDIVERSOS OS (DICA(DICAS)...S)... 2929 13.1 H

13.1 HINTS DE PAGINAÇÃOINTS DE PAGINAÇÃO... 2929 13.2

13.2 PLS_INPLS_INTEGERTEGER... 2929 13.3 VARRAY

13.3 VARRAY ((ORACLEORACLE8)8) ... 2929 13.4 R

13.4 RECURSIVIDADEECURSIVIDADE... 2929 13.5 D

(6)

Cancel Anytime.

(7)

Cancel Anytime.

4 4

1 1 MU

MUT

TA

ATIN

TING

G TA

TABLE

BLES

S

1.1

1.1 O O que que é é MUTATING MUTATING TABLE TABLE ? ?

Uma MUTATING table ou tabela mutante é uma tabela que está sendo Uma MUTATING table ou tabela mutante é uma tabela que está sendo modificad

modificada a atualmente patualmente por uma instor uma instrução UPDATE, DErução UPDATE, DELETE ou INSERT. ULETE ou INSERT. Umama tabela não é considerada mutante para gatilhos STATEMENT, somente para tabela não é considerada mutante para gatilhos STATEMENT, somente para triggers de ROW (FOR EACH ROW).

triggers de ROW (FOR EACH ROW).

1.2

1.2 Simulando um MUTATING TABLE Simulando um MUTATING TABLE

Considere a trigger abaixo. Esta é disparada antes do UPDATE (BEFORE Considere a trigger abaixo. Esta é disparada antes do UPDATE (BEFORE UPDATE) para cada row (FOR EACH ROW). A trigger tem a finalidade de 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 verificar se na tabela QUOTAS_AREAS já não existe outro registro com datas intercaladas. A tabela QUOTAS_AREAS é uma tabela MUTATING. Sendo assim, intercaladas. A tabela QUOTAS_AREAS é uma tabela MUTATING. Sendo assim, a trigger não executará retornando o seguinte erro :

a trigger não executará retornando o seguinte erro :

ORA-04091: table USUARIO2.QUOTA

ORA-04091: table USUARIO2.QUOTAS_AREAS is MUTATING, trigger/function may not S_AREAS is MUTATING, trigger/function may not see itsee it ORA-06512: at

ORA-06512: at "USUARIO2"USUARIO2.TRG_BUPD_Q.TRG_BUPD_QUOTAS_AREAUOTAS_AREAS", liS", line 2ne 2 ORA-04088: error during

ORA-04088: error during execution of execution of trigger 'USUARIO2.TRG_BUtrigger 'USUARIO2.TRG_BUPD_QUOTAS_APD_QUOTAS_AREAS'REAS'

CREATE OR REPLACE TRIGGER

CREATE OR REPLACE TRIGGER TRG_BUPD_QUOTAS_ARETRG_BUPD_QUOTAS_AREASAS BEFORE UPDATE

BEFORE UPDATE ON QUOTAS_AREAS ON QUOTAS_AREAS FOR EACH ROW FOR EACH ROW BEGIN

BEGIN

FOR REG_AREAS_QUOTAS IN (SELECT * FOR REG_AREAS_QUOTAS IN (SELECT * FROM QUOTAS_AREAS FROM QUOTAS_AREAS WHERE

WHERE COD_PRODUTO COD_PRODUTO = = :NEW.COD_PRODUTO:NEW.COD_PRODUTO AND

AND COD_AREA COD_AREA = = :NEW.COD_ARE:NEW.COD_AREAA AND

AND (DATA_INICIAL (DATA_INICIAL BETWEEN BETWEEN :NEW.DATA_INICIAL :NEW.DATA_INICIAL ANDAND :NEW.DATA_FINAL

:NEW.DATA_FINAL OR

OR DATA_FINAL DATA_FINAL BETWEEN BETWEEN :NEW.DATA_INICIAL :NEW.DATA_INICIAL ANDAND :NEW.DATA_FINAL)) LOOP :NEW.DATA_FINAL)) LOOP RAISE_APPLICATION_ERROR (-20001,'Pro

RAISE_APPLICATION_ERROR (-20001,'Produto já duto já possui quotas cadastradapossui quotas cadastrada para este periodo');

para este periodo'); END LOOP;

END LOOP;

END TRG_BINS_QUOTAS_AREAS; END TRG_BINS_QUOTAS_AREAS;

(8)

Cancel Anytime.

(9)

Cancel Anytime.

5 5

1.3

1.3 Quais Quais eventos eventos causam causam MUTATING MUTATING TABLE TABLE

O erro causado por uma tabela MUTATING depende da versão do 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. ORACLE. A cada versão alguns eventos não causam mais MUTATING TABLE. Segue abaixo alguns eventos que causam MUTATING. Este foram testados na Segue abaixo alguns eventos que causam MUTATING. Este foram testados na ORACLE 8.1.6.

ORACLE 8.1.6.

TRIGGERS CRIADAS NA TABELA ITENS PEDIDOS COM OPERAÇÕES EM TRIGGERS CRIADAS NA TABELA ITENS PEDIDOS COM OPERAÇÕES EM PEDIDOS

PEDIDOS Evento

Evento Ocorrência Ocorrência MUTATINGMUTATING SELECT

SELECT na na tabela tabela PEDIDOS PEDIDOS NÃONÃO UPDATE

UPDATE na na tabela tabela PEDIDOS PEDIDOS NÃONÃO INSERT

INSERT na na tabela tabela PEDIDOS PEDIDOS NÃONÃO BEFORE INSERT

BEFORE INSERT

DELETE

DELETE na na tabela tabela PEDIDOS PEDIDOS NÃONÃO SELECT

INSERT na na tabela tabela PEDIDOS PEDIDOS NÃONÃO AFTER INSERT

AFTER INSERT

DELETE

INSERT na na tabela tabela PEDIDOS PEDIDOS NÃONÃO BEFORE UPDATE

BEFORE UPDATE

DELETE

INSERT na na tabela tabela PEDIDOS PEDIDOS NÃONÃO AFTER UPDATE

AFTER UPDATE

DELETE

INSERT na na tabela tabela PEDIDOS PEDIDOS NÃONÃO BEFORE DELETE

BEFORE DELETE

DELETE

INSERT na na tabela tabela PEDIDOS PEDIDOS NÃONÃO AFTER DELETE

AFTER DELETE

DELETE

(10)

Cancel Anytime.

(11)

Cancel Anytime.

6 6

TRIGGERS CRIADAS NA TABELA ITENS_PEDIDOS COM OPERAÇÕES EM TRIGGERS CRIADAS NA TABELA ITENS_PEDIDOS COM OPERAÇÕES EM ITENS_PEDIDOS

ITENS_PEDIDOS Evento

Evento Ocorrência Ocorrência MUTATINGMUTATING BEFORE

BEFORE INSERT INSERT SELECT SELECT na na tabela tabela ITENS_PEDIDOITENS_PEDIDOS S NÃONÃO AFTER INSERT

AFTER INSERT SELECT SELECT na na tabela tabela ITENS_PEDIDOITENS_PEDIDOS S SIMSIM BEFORE

BEFORE UPDATE UPDATE SELECT SELECT na na tabela tabela ITENS_PEDIDOITENS_PEDIDOS S SIMSIM AFTER UPDATE

AFTER UPDATE SELECT SELECT na na tabela tabela ITENS_PEDIDOITENS_PEDIDOS S SIMSIM BEFORE

BEFORE DELETE DELETE SELECT SELECT na na tabela tabela ITENS_PEDIDOITENS_PEDIDOS S SIMSIM AFTER DELETE

(12)

Cancel Anytime.

(13)

Cancel Anytime.

7 7

2 2 CURSOR

CURSOR V

VARIABLES

ARIABLES

A versão 2.3 do PL/SQL, disponível com o ORACLE SERVER Versão 7.3, 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 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 cursor convencional, a variável do tipo cursor aponta para a linha corrente no conjunto de resultados de

conjunto de resultados de uma consulta uma consulta que retorne que retorne múltiplas linhas, mas múltiplas linhas, mas aoao contrário do

contrário do cursor estático, cursor estático, que que está vinculado está vinculado à à uma uma única consulta, única consulta, asas variáveis cursor podem

variáveis cursor podem ser associadas a ser associadas a várias consultas, até várias consultas, até mesmo dentromesmo dentro de um mesmo programa. Variáveis do tipo cursor são verdadeiras variáveis 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 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 subprogramas. Uma variável do tipo cursor é como um ponteiro nas linguagens C e

C e Pascal, ela guarda a posição na memória (endereço) de um objeto ao invésPascal, 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 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 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 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 passar resultados de consultas entre diferentes programas PL/SQL, ou ainda entre programa PL/SQL cliente e programa servidor. Em um ambiente entre programa PL/SQL cliente e programa servidor. Em um ambiente cliente/servidor, por exemplo, um programa do lado cliente poderia abrir a 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 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 variável como argumento a uma PROCEDURE armazenada no servidor. Este programa poderia então continua

programa poderia então continuar a extrair seus dados e r a extrair seus dados e passar a variável depassar a variável de volta ao cliente para que este feche o

volta ao cliente para que este feche o cursor.cursor.

2.1

2.1 Características Características das das Variáveis Variáveis Cursor Cursor

As variáveis do tipo

(14)

Cancel Anytime.

(15)

Cancel Anytime.

8 8

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

cursor.

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

conjunto de resultados) a outra variável do tipo cursor.variável do tipo cursor.

2.2

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

A criação de variáveis do tipo cursor é feita em duas etapas: primeiro 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 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 daquele tipo. A sintaxe para se criar um tipo de referência a cursor é a seguinte:

seguinte:

TYPE nome_tipo_cursor IS REF CURSOR [RETURN

TYPE nome_tipo_cursor IS REF CURSOR [RETURN tipo_retorntipo_retornado];ado];

Onde nome_tipo_cursor é o nome do tipo e tipo_retornado é a 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 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, qualquer estrutura válida para uma clausula RETURN de um cursor normal, definida usando o atributo %ROWTYPE ou referenciando um registro (record) definida usando o atributo %ROWTYPE ou referenciando um registro (record) previamente definido.

previamente definido.

A clausula RETURN é opcional, e quando usada, o tipo é dito "forte" pois 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 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 declarada de um tipo "forte" pode apenas ser utilizada com declarações SQL que retornem dados do mesmo tipo

que retornem dados do mesmo tipo da declaração usada na clausula RETURN.da declaração usada na clausula RETURN. Por outro lado, o

Por outro lado, o tipo cursor que não possui a clausula RETURN é dito "fraco" etipo 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 pode ser utilizado de formas muito mais flexíveis, isto é, pode ser utilizado com consultas que retornem qualquer estrutura de dados.

consultas que retornem qualquer estrutura de dados.

Uma vez declarado o tipo REF CURSOR você pode declarar a variável daquele Uma vez declarado o tipo REF CURSOR você pode declarar a variável daquele tipo, como mostrado no seguinte exemplo:

(16)

Cancel Anytime.

(17)

Cancel Anytime.

9 9

É importante lembrar que a declaração de uma variável do tipo cursor É 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 não cria um objeto cursor. Para que isto ocorra é necessário usar a sintaxe OPEN FOR, que cria um novo objeto

OPEN FOR, que cria um novo objeto cursor e o associa à variável.cursor e o associa à variável.

Outra observação importante é que variáveis cursor não possuem persistência Outra observação importante é que variáveis cursor não possuem persistência de estado por serem apenas ponteiros, não podendo portanto serem de estado por serem apenas ponteiros, não podendo portanto serem referenciadas após o fim da e

referenciadas após o fim da execução do procedimento que abriu o cursor.xecução do procedimento que abriu o cursor. Topo

Topo

2.3

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

A declaração OPEN - FOR associa o cursor com uma consulta que retorne 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 múltiplas linhas, executa a consulta e identifica o conjunto de respostas. A sintaxe para a declaração é:

sintaxe para a declaração é:

OPEN {nome_variável_cursor } FOR

OPEN {nome_variável_cursor } FOR declaração_sqdeclaração_sql;l;

Onde variável_cursor_de_ambiente é uma variável declarada em um 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 é 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 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 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.

diferentes consultas, não sendo necessário para isto fecha-lo antes.

2.4

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

Assim como com cursores estáticos, para se obter o resultado da consulta 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 é utilizada a declaração FETCH que extrai as linhas uma a uma da variável do tipo cursor, e possui a

tipo cursor, e possui a seguinte sintaxe:seguinte sintaxe: FETCH {nome_variável_cu

FETCH {nome_variável_cursor } rsor } INTO INTO registro;registro; FETCH {nome_variável_cu

FETCH {nome_variável_cursor } rsor } INTO INTO variável1 [,variável2 ...];variável1 [,variável2 ...];

Quando a variável do tipo cursor foi declarada como sendo de um tipo 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 "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 INTO são compatíveis com a estrutura da consulta associada à variável do tipo

(18)

Cancel Anytime.

(19)

Cancel Anytime.

Cancel Anytime. 10 10 CLOSE {nome_variável_curso CLOSE {nome_variável_cursor r };}; 2.6

2.6 Exemplo Exemplo de de VARIABLE VARIABLE CURSOR CURSOR

Este exemplo tem o objetivo

Este exemplo tem o objetivo de mostrar os dados dos de mostrar os dados dos clientes. Através daclientes. Através da execução da PROCEDURE prc_exibe_clientes podemos mostra dados dos execução da PROCEDURE prc_exibe_clientes podemos mostra dados dos cliente. Sendo que, esta PROCEDURE possui um parâmetro que determina a cliente. Sendo que, esta PROCEDURE possui um parâmetro que determina a quantidade de clientes que devem ser mostrados. Esta PROCEDURE abre quantidade de clientes que devem ser mostrados. Esta PROCEDURE abre cursor (VARIABLE CURSOR) e

cursor (VARIABLE CURSOR) e chama a PROCEDURE PRC_EXIBE_DADOS. Esta,chama a PROCEDURE PRC_EXIBE_DADOS. Esta, executa FETCHS no cursor.

executa FETCHS no cursor.

CREATE OR REPLACE PACKAGE PCK_CLIENTES CREATE OR REPLACE PACKAGE PCK_CLIENTES IS

IS

--TYPE PC_CLIENTE IS REF CURSOR RETURN CLIENTES%ROW--TYPE; TYPE PC_CLIENTE IS REF CURSOR RETURN CLIENTES%ROWTYPE;

--PROCEDURE PRC_EXIBE_CLIENTES (PI_QUANTIDADE NUMBER ); PROCEDURE PRC_EXIBE_CLIENTES (PI_QUANTIDADE NUMBER );

--END PCK_CLIENTES; END PCK_CLIENTES;

CREATE OR REPLACE PACKAGE BODY

CREATE OR REPLACE PACKAGE BODY PCK_CLIENTESPCK_CLIENTES IS

IS

PROCEDURE PRC_BUSCA_DADOS (PI_CURSOR IN PCK_CLIENTES.PC_CLIENTE PROCEDURE PRC_BUSCA_DADOS (PI_CURSOR IN PCK_CLIENTES.PC_CLIENTE

,PI_QUANTIDADE NUMBER) ,PI_QUANTIDADE NUMBER) IS IS --LR_CLIENTE CLIENTES%ROWTYPE; LR_CLIENTE CLIENTES%ROWTYPE; --BEGIN BEGIN

--FOR indice IN 1..pi_quantidade LOOP FOR indice IN 1..pi_quantidade LOOP

--FETCH pi_cursor INTO lr_cliente; FETCH pi_cursor INTO lr_cliente; EXIT WHEN pi_cursor%NOTFOUND; EXIT WHEN pi_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Nome

(20)

Cancel Anytime.

(21)

Cancel Anytime.

Cancel Anytime. 11 11 CLOSE lr_cliente; CLOSE lr_cliente; --END PRC_EXIBE_CLIENTES; END PRC_EXIBE_CLIENTES; END PCK_CLIENTES; END PCK_CLIENTES;

3 3 LOCAL

LOCAL MODULES

MODULES

Um local module é uma PROCEDURE ou FUNCTION que é definida dentro 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 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. é chamado local porque é unicamente definido dentro de um bloco PL/SQL pai. Este não pode ser chamado por outro bloco PL/SQL externo.

Este não pode ser chamado por outro bloco PL/SQL externo.

3.1

(22)

Cancel Anytime.

(23)

Cancel Anytime.

Cancel Anytime. 12 12 END LOOP; END LOOP; END; END;

4 OVERLOADING

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

de parâmetros.

Como exemplo, usaremos uma PACKAGE do ORACLE, a UTL_FILE. Como exemplo, usaremos uma PACKAGE do ORACLE, a UTL_FILE.

FUNCTION FOPEN RETURNS RECORD FUNCTION FOPEN RETURNS RECORD

Argument

Argument Name Name Type Type In/Out In/Out Default?Default? ---

--- --- --- --- --- --- ---ID

(24)

Cancel Anytime.

(25)

Cancel Anytime.

13 13

em código de programas já construídos, criou-se uma nova com o parâmetro em código de programas já construídos, criou-se uma nova com o parâmetro MAX_LINESIZE.

MAX_LINESIZE.

Obs. Para identificar qual subprograma será executado utiliza-se Obs. Para identificar qual subprograma será executado utiliza-se inicialmente o número de parâmetros, caso este seja idêntico nos inicialmente o número de parâmetros, caso este seja idêntico nos procedimento

procedimento, utiliza-se o tipo , utiliza-se o tipo de dados dos de dados dos parâmetros.parâmetros.

Ex. Observe abaixo os procedimentos PUT_LINE definidos na PACKAGE Ex. Observe abaixo os procedimentos PUT_LINE definidos na PACKAGE SPECIFICATION da DBMS_OUTPUT. Ambas

SPECIFICATION da DBMS_OUTPUT. Ambas possuem o mesmo nome e númeropossuem o mesmo nome e número de parâmetros. No entanto, o tipo de dado do parâmetro é diferente nos três de parâmetros. No entanto, o tipo de dado do parâmetro é diferente nos três procedimentos.

procedimentos.

PROCEDURE PUT_LINE PROCEDURE PUT_LINE Argument

Argument Name Name Type Type In/Out In/Out Default?Default? --- --- --- --- --- --- --- ---A A VARCHAR2 VARCHAR2 ININ PROCEDURE PUT_LINE PROCEDURE PUT_LINE Argument

Argument Name Name Type Type In/Out In/Out Default?Default? --- --- --- --- --- --- --- ---A A NUMBER NUMBER ININ PROCEDURE PUT_LINE PROCEDURE PUT_LINE Argument

Argument Name Name Type Type In/Out In/Out Default?Default? ---

--- --- --- --- --- --- ---A

(26)

Cancel Anytime.

(27)

Cancel Anytime.

14 14

5 5 FUNCTIONS

FUNCTIONS EM

EM UMA

UMA INSTRUÇÃO

INSTRUÇÃO SQL

SQL

O ORACLE permite o uso de funções armazenados no banco de dados 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 em uma instrução SQL. Tal recurso é utilizado geralmente para facilitar o entendimento de consultas complexas.

entendimento de consultas complexas.

Ex. Considere que o cálculo de aposentadoria de um empregado é o Ex. Considere que o cálculo de aposentadoria de um empregado é o resultado

resultado várias consulvárias consultas em várias tabetas em várias tabelas. Sendo alas. Sendo assim. poderíssim. poderíamos criaramos criar uma função (stored procedure) que retorna o valor de beneficio com base em uma função (stored procedure) que retorna o valor de beneficio com base em algumas informações da tabela empregado.

algumas informações da tabela empregado. SELECT nome, FNC_VLR_BENEFICIO

SELECT nome, FNC_VLR_BENEFICIO (cod_emp, salario, data_admissão)(cod_emp, salario, data_admissão) FROM empregados

FROM empregados

No entanto algumas restrições deve ser

No entanto algumas restrições deve ser respeitadas :respeitadas :

-- A função não pode modificar tabelas de banco de dados; portanto,A função não pode modificar tabelas de banco de dados; portanto, ela não pode executar INSERT, UPDATE e

(28)

Cancel Anytime.

(29)

Cancel Anytime.

Cancel Anytime. 15 15 Sintaxe Sintaxe PRAGMA RESTRICT_REFERENCE

PRAGMA RESTRICT_REFERENCES (function_naS (function_name, me, WNDSWNDS [,WNPS] [,WNPS] [,RNDS] [,RNDS] [,RNPS] ); [,RNPS] ); WNDS

WNDS Writes Writes No No Database Database State. State. A A função função não não pode pode modificamodificar r asas tabelas do banco de dados

tabelas do banco de dados WNPS

WNPS Writes Writes No No Package Package State. State. A A função função não não pode pode modificamodificar r valoresvalores de variáveis de PACKAGE

de variáveis de PACKAGE RNDS

RNDS Read Read No No Database Database State. State. A A função função não não pode pode consultar consultar asas tabelas do banco de

tabelas do banco de RNPS

RNPS Read Read No No Package Package State. State. A A função função não não pode pode ler ler a a variáveis variáveis dede package

package

Exemplo Exemplo

CREATE OR REPLACE PACKAGE

CREATE OR REPLACE PACKAGE PCK_FOLHAPCK_FOLHA IS

(30)

Cancel Anytime.

(31)

Cancel Anytime.

16 16

(32)

Cancel Anytime.

(33)

Cancel Anytime.

17 17

6 6 PRIMARY

PRIMARY KEY

KEY X

X WHERE

WHERE CURRENT

CURRENT OF

OF x

x ROWID

ROWID para

para

UPDATE

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

dados obtidos e alterar a tabela em questão.

6.1

(34)

Cancel Anytime.

(35)

Cancel Anytime.

18 18

6.2

6.2 Alterando Alterando pela pela WHERE WHERE CURRENT CURRENT OF OF

DECLARE DECLARE CURSOR c_emp IS CURSOR c_emp IS SELECT * SELECT * FROM EMPREGADOS FROM EMPREGADOS WHERE SALARIO > 50000 WHERE SALARIO > 50000 FOR UPDATE FOR UPDATE novo_salari

novo_salario o number;number; BEGIN

BEGIN

FOR emp_row in c_emp LOOP FOR emp_row in c_emp LOOP

(36)

Cancel Anytime.

(37)

Cancel Anytime.

Cancel Anytime. 19 19 DECLARE DECLARE CURSOR c_emp IS CURSOR c_emp IS

SELECT e.rowid, e.* SELECT e.rowid, e.*

FROM EMPREGADOS e FROM EMPREGADOS e WHERE SALARIO > 50000; WHERE SALARIO > 50000; novo_salari

novo_salario o number;number; BEGIN

BEGIN

FOR emp_row in c_emp LOOP FOR emp_row in c_emp LOOP

novo_salario := calcula_salario (emp_row.salario) ; novo_salario := calcula_salario (emp_row.salario) ;

update empregados update empregados

set salario =

set salario = novo_salarionovo_salario where rowid = emp_row.rowid; where rowid = emp_row.rowid;

(38)

Cancel Anytime.

(39)

Cancel Anytime.

20 20

(40)

Cancel Anytime.

(41)

Cancel Anytime.

21 21

7 7 EXPLICIT

EXPLICIT CURSORS

CURSORS X

X IMPLICIT

IMPLICIT CURSORS

CURSORS

O PL/SQL permite que um comando SQL seja incluído em um bloco sem O PL/SQL permite que um comando SQL seja incluído em um bloco sem estar explicitamente associado a um cursor. Sege exemplo abaixo :

estar explicitamente associado a um cursor. Sege exemplo abaixo : BEGIN

BEGIN

SELECTt nome SELECTt nome

INTO

INTO :nome_clien:nome_clientete FROM clientes

FROM clientes

WHERE contato = 'JOAO'; WHERE contato = 'JOAO';

AND data_aniversario =

(42)

Cancel Anytime.

(43)

Cancel Anytime.

22 22

7.1

7.1 Comparando Comparando IMPLICIT IMPLICIT CURSOR CURSOR X X EXPLICIT EXPLICIT CURSOR CURSOR

TABLE

TABLE SCAN SCAN INDEX INDEX LOOKUPLOOKUP IMPLICIT CURSOR

IMPLICIT CURSOR 322 322 44

EXPLICIT CURSOR

(44)

(45)

Cancel Anytime.

23 23

8

(46)

(47)

Cancel Anytime.

24 24

8.1

8.1 8.1 8.1 Native Native dynamic dynamic SQLSQL Java external routimes Java external routimes Invoker´s rights

Invoker´s rights NCOPY parameters NCOPY parameters Autonomou

Autonomous s transactionstransactions Bulk operations

(48)

(49)

Cancel Anytime.

25 25

(50)

(51)

Cancel Anytime.

26 26

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

(52)

(53)

Cancel Anytime.

27 27

(54)

Cancel Anytime.

28 28

(55)

Cancel Anytime.

29 29

(56)

Cancel Anytime.

30 30