Aula_pl-sql

UNIVEM PL/SQL

Dino Spoto 1

Banco de Dados IIEngenharia da Computao

PL/SQLOracle

Introduo bsica

Prof. Edmundo Spoto

Tpicos_Especiais1 - PL-SQL PL/SQL - 2

DINO

Aplicaes interrogar a BD tomar decises dialogar com o utilizador etc.

Linguagens hospedeiras

linguagem de programao convencionalC, Delphi, Ada, Java

linguagem de manipulao de dados (DML)otimizada para acesso rpido a grandes BD

O SGBDSGBD fornece um conjunto de procedimentos que implementam as principais funes da DML


DINO

Acesso ao Banco de Dados

chamadasDML

BDdados locais

aplicao

ligao direta (API)ODBCJDBC


DINO

Comunicao

os programas de aplicao no escrevem diretamente no BD

usam operaes do nvel conceitual - a bem da independncia dos dados

se uma chamada da DML produz uma relao esta pode ser maior que a rea de dados local as linguagens convencionais so orientadas a registros

Assim transferida para a rea local uma tupla de cada vez o seguinte obtido com uma instruo de fetch os dados na rea local so variveis normais do programa


DINO

PL/SQL - Ambiente

PL/SQLBlock

PL/SQLBlock

Execuo da expressoprocedimental

Execuo da expresso SQL

Servidor Oracle

PL/SQL

SQL

Engine PL/SQL


DINO

PL/SQL

Desenvolvimento modular de programas.

Permite executar instrues condicionais.

Permite executar instrues num ciclo. Permite processar vrias linhas

devolvidas por uma pergunta atravs de cursores.

Os erros podem ser processados com rotinas de tratamento de excees.

Sintaxe:

Declare

Begin

Exception

End;

o o o

o o o

o o o

Declare

Begin

Exception

End;

UNIVEM PL/SQL

Dino Spoto 2


DINO

Linguagem PL/SQL

A linguagem PL/SQL permite a declarao de constantes, variveis, subprogramas (procedures e funes), que favorecem a estruturao de cdigo;

Possui mecanismos para controle de erros de execuo;

Incorpora os novos conceitos de objeto, encapsulamento e, ainda, permite a interface com rotinas escritas em outras linguagens .


DINO

Modularidade

A modularidade um conceito que determina a diviso do programa em mdulos com aes bem definidas, que visam a facilitar o entendimento e a manuteno. Um problema complexo poderia ser subdividido em problemas

menos complexos que, por sua vez, poderiam ser novamente subdivididos at que obtivssemos problemas simples com solues de fcil implementao.

A PL/SQL, por possuir uma estrutura de blocos, favorece a modularidade. Alm de blocos annimos, temos a possibilidade de criar

procedimentos e funes armazenadas na base de dados e compartilhadas por outras aplicaes, ou pacotes (packages) que permitem que agrupemos os procedimentos, funes e variveis relacionadas.


DINO

Arquitetura

A PL/SQL no um produto independente; podemos consider-la um mdulo executor de blocos e subprogramas; Esse mdulo pode ser instalado no Oracle Server e nas ferramentas de

desenvolvimento da Oracle (Forms Builder, Report Builder, etc.). Estes dois ambientes so independentes e podem conter at mesmo verses diferentes

da PL/SQL. Esse mdulo, no entanto, trabalha da mesma forma: ele capaz de tratar os

comandos de PL/SQL, mas no os comandos de SQL, que devem ser resolvidos por mecanismos internos do Oracle Server.

Em ferramentas que possuem esse mdulo embutido, a ferramenta passa para seu mdulo executor local o bloco de PL/SQL que pode ser processado noprprio ambiente, com a exceo dos comandos de SQL encontrados.

Grande parte do trabalho realizada localmente, sem necessidade de envio de informaes para o ambiente servidor.

Em ferramentas que no tm esse mdulo embutido, tais como SQL*Plus e Enterprise Manager, torna-se necessrio o envio de todo o bloco para o servidor, para que este acione o seu mdulo executor local e processe o bloco de PL/SQL.


DINO

SUPORTE PARA SQL

A PL/SQL permite a utilizao, integrada no cdigo, dos comandos da DML, das funes de SQL, de comandos de controle de cursor e dos comandos para controle da transao (Commit, Rollback, etc.).

Benefcios da PL/SQL


DINO

SUPORTE PARA PROGRAMAO ORIENTADA A OBJETO

Quando criamos um tipo objeto, definimos suas caractersticas atravs de seus atributos e mtodos.

Os mtodos so escritos em PL/SQL. Na criao de um bloco de PL/SQL, podemos

declarar variveis com qualquer dos tipos predefinidos existentes no banco de dados ou com os tipos criados pelo usurio, inclusive tipos objeto.

Benefcios da PL/SQL


DINO

PERFORMANCE

A PL/SQL pode: Reduzir o trfego na rede pelo envio de um bloco contendo diversos

comandos de SQL agrupados em blocos para o Oracle. Adicionar performance s ferramentas que possuem um mdulo executor

local,. Reduzir no s o trfego na rede como tambm a programao quando

estabelecemos aes que podem ser compartilhadas em diversas aplicaes (Uso de Stored Procedure).

Benefcios da PL/SQL

UNIVEM PL/SQL

Dino Spoto 3


DINO

PORTABILIDADE

Aplicaes escritas em PL/SQL so portveis para qualquer sistema operacional e plataforma nos quais o Oracle execute. No h necessidade de customizao.

Isso significa que podemos escrever programas ou bibliotecas de programas que podem ser utilizados em diferentes ambientes.

Benefcios da PL/SQL


DINO

PRODUTIVIDADE

O aprendizado da PL/SQL pode ser aproveitado no desenvolvimento de aplicaes batch, online, relatrios, etc.

No desenvolvimento de um programa utilizando uma Ferramenta tal como FormsBuilder ou Report Builder. Conhecer a Linguagem ajudar muito no desenvolvimento de sistemas.

Benefcios da PL/SQL


DINO

Identificadores da Linguagem

Um identificador consiste em uma letra seguida de outras letras, nmeros, $ (dlar), _ (sublinhado) e # (smbolo numrico). Possui um limite mximo de 30 caracteres.

As letras podem ser maisculas ou minsculas indiscriminadamente, pois a linguagem no sensvel forma.

Opcionalmente, os identificadores podem ser declarados (e usados) entre aspas. Com essa sintaxe, podemos declarar variveis com outros caracteres alm daqueles estabelecidos no incio do texto (exceto aspas). O tamanho continua limitado a 30 caracteres (excluindo-se as aspas).


DINO

PALAVRAS RESERVADAS

Alguns identificadores possuem um significado especial em PL/SQL e no devem ser utilizados na declarao de variveis.- No utilizem palavras reservadas na declarao de variveis:

if, end, do, while, else, etc..

- Evite tambem de usar os mesmos nomes das colunas das tabelas. Dica use um v_nome para distinguir o nome da coluna na tabela.


DINO

Comentrios

Um comentrio em PL/SQL pode ser informado de duas maneiras:

- Dois hfens em qualquer ponto da linha torna o restante da linha como comentrio (--).

- /* (incio) e */ (fim) marcam uma regio que ser ignorada pelo compilador.

Restries: no podemos embutir um comentrio em outro e,no podemos utilizar comentrios com dois hfens em

blocos de PL/SQL que venham a ser processados dinamicamente por um Oracle Precompiler, (isso ocorre porque os caracteres de fim de linha so ignorados (no so considerados)) e, desta forma, o fim do comentrio no percebido at o fim do bloco. Neste caso, devemos usar /* e */.


DINO

Comentrios - Exemplo

Listagem L03_04 - Comentrios SQL> DECLARE

2 VALOR VARCHAR2(10);3 END_CASA VARCHAR2(20);4 BEGIN5 VALOR := -- A atribuio ser feita na outra linha6 'RUA A';7 END_CASA := /* a atribuio vir a seguir */ 'RUA B S/N';8 END;9 /

Procedimento PL/SQL concludo com sucesso

UNIVEM PL/SQL

Dino Spoto 4


DINO

FINAL DE LINHA

A indicao de fim de linha de comando em PL/SQL feita com um ponto-e-vrgula (;);

Observe que o comportamento do SQL*Plusmudar quando encontrar o primeiro comando de PL/SQL, pois no aguardar mais o ponto-e-vrgula (;) para indicao de fim de comando.

Para concluirmos a digitao de um programa, deveremos utilizar a barra (/) para encerrar sem executar.


DINO

Bloco Annimo

DECLARE ... definio dos objetos PL/SQL que sero

utilizados neste blocoBEGIN .... aes executveis EXCEPTION .... o que fazer se um ao executada causar

um erroEND;


DINO

Declarao e atribuio

Sintaxe:identifier [CONSTANT] datatype [NOT NULL]

[:= | DEFAULT expr];Exemplos:

Declarev_hiredate DATE;v_deptno NUMBER(2) NOT NULL :=10;c_comm CONSTANT NUMBER :=1400;v_ename emp.ename%TYPE;

Atribuio:v_hiredate := 31-DEC-98;

Variveis %TYPE herdam o tipo da coluna base e suportam mudanas nessa definio

TIPOS DE DADOS - Escalares

1. ESCALARES Tipo de dado numrico para armazenamento de inteiros vlidos no seguinte

intervalo de valores: de 231 + 1 (-2147483647) e 231 1 (2147483647). Binary_Integer possui um conjunto de subtipos (derivado do tipo bsico

Binary_Integer):1. - Natural Com intervalo de valores vlidos entre 0 a 231 1.2. - NaturalN Com intervalo de valores vlidos entre 0 a 231 1 e sem a

possibilidade de associao de valores NULLS.3. - Positive Com intervalo de valores vlidos entre 1 a 231 1.4. - PositiveN Com intervalo de valores vlidos entre 1 a 231 1 e sem a

possibilidade de associao de valores NULLS.5. - SignType Restrito seguinte lista de valores: -1, 0 e 1


DINO

TIPOS DE DADOS - Number

2. Number Tipo de dado numrico para armazenamento de

valores fixos ou em ponto flutuante com preciso de at 38 dgitos e magnitude de 1.0E-130 a 9.99E125.

Pode-se especificar preciso e escala. Se no especificarmos preciso, o default 38 (ou o maior tamanho vlido para o sistema operacional, o que for menor).

Escala pode variar de 84 a 127. Escalas negativas causam o arredondamento da parte inteira.


DINO

TIPOS DE DADOS

Listagem L03_06 Tipo de dado - NumberSQL> VARIABLE P1 NUMBERSQL> VARIABLE P2 NUMBERSQL> VARIABLE P3 NUMBERSQL> DECLARE2 WNUM1 NUMBER;3 WNUM2 NUMBER(38, -4);4 WNUM3 NUMBER(38, 127);5 BEGIN6 WNUM1 := 9.99E125;7 WNUM2 := 38925;8 WNUM3 := 123456789987654321.0E-130;9 :P1 := WNUM1;10 :P2 := WNUM2;11 :P3 := WNUM3;12 END;13 /

Procedimento PL/SQL concludo com sucesso.

SQL> PRINT

P1----------

9,990E+125

P2----------

40000

P3----------

1,235E-113

UNIVEM PL/SQL

Dino Spoto 5


DINO

Comentrios - Number

Na listagem L03_06, declaramos trs variveis no SQL*Plus para recebermos o resultado das atribuies feitas no programa.

O comando Print (de SQL*Plus) nos mostra o resultado: A varivel wnum1 no teve seu valor alterado, pois a

declaramos sem qualquer restrio quanto preciso ou escala. A varivel wnum2, por possuir uma escala negativa, causou o

arredondamento da parte inteira do nmero que passou de 38.925 para 40.000.

A varivel wnum3 perde os trs ltimos nmeros informados, pois a escala 127 e o valor multiplicado por 10-130.


DINO

Outro Exemplo

Observe o resultado do exemplo da Listagem L03_07:Listagem 3.07 Tipo de dadoSQL> DECLARE2 WNUM1 NUMBER := 1.23456789987654E-113;3 WNUM3 NUMBER(38,127);4 BEGIN5 WNUM3 := 123456789987654321.0E-130;6 IF WNUM3 > WNUM1 THEN7 :P1 := 3;8 ELSIF WNUM3 = WNUM1 THEN9 :P1 := 0;10 ELSE11 :P1 := 1;12 END IF;13 END;14 /


SQL> PRINT P1

P1----------

0


DINO

Comentrios - Number

O tipo Number tambm possui um conjunto de subtipos (derivados do tipo bsico Number):

- Decimal, Dec e Numeric armazenamento em ponto fixo com uma preciso mxima de 38 dgitos decimais.

- Doubl Precision, Float armazenamento em ponto flutuante com uma preciso mxima de 126 dgitos binrios, o que equivale a 38 dgitos decimais. Para efetuarmos a converso de preciso binria para preciso decimal, devemos multiplicar a preciso binria por 0.30103 e para realizar a operao inversa devemos multiplicar a preciso decimal por 3.32193.

- Real armazenamento em ponto flutuante com uma preciso mxima de 63 dgitos binrios, o que equivale a 38 dgitos decimais.- Integer, Int e Smallint armazenamento de inteiros com uma

preciso mxima de 38 dgitos.


DINO

Tipos de Dados Pls_Integer

3. Pls_IntegerTipo de dado numrico para armazenamento de inteiros vlidos no seguinte intervalo de valores: de 231 + 1 (-2.147.483.647) e 231 1(2.147.483.647). similar ao tipo Binary_Integer, porm mais rpido para efetuar clculos que um Binary_Integer ou um Number, pois utiliza machine arithmetic, enquanto os demais usam library arithmetic.Possui uma outra diferena em relao ao Binary_Integer no que se refere deteco de Overflow. Quando efetuamos um clculo usando variveis Pls_Integer e o valor ultrapassa a capacidade mxima da varivel, ocorre um erro de Overflow mesmo que a rea receptora tenha capacidade de armazenamento (seja um Number, por exemplo).J com Binary_Integer no ocorrer qualquer erro se atribuirmos o resultado a um Number.


DINO

Exemplo - Pls_Integer

Listagem L03_08 Tipo de dado Pls_IntegerSQL> DECLARE2 WNUM NUMBER;3 WPLS PLS_INTEGER := 2147483647;4 WBIN BINARY_INTEGER := 2147483647;5 BEGIN6 WNUM := WBIN + 1;7 WNUM := WPLS + 1;8 END;9 /

DECLARE*

ERRO na linha 1:ORA-01426: overflow numricoORA-06512: em line 7

A recomendao da Oracle que passemos a utilizar variveis do tipo Pls_Integer nas novas aplicaes para que possamos obter ganhos de performance


DINO

Tipos de Dados - Char

4. CharTipo de dado alfanumrico de tamanho fixo com comprimento de at32.767 bytes. Pode-se especificar o tamanho mximo na declarao de uma varivel com esse tipo. Caso isso no seja especificado, o comprimento default de 1 byte.O comprimento especificado em bytes e no em caracteres. Isso importante quando armazenamos valores multibytes.O conjunto de valores vlidos para armazenamento depende do charsetdo banco de dados.A definio de uma coluna do tipo Char no banco de dados estlimitada a 2.000 bytes.O tipo Char tem um nico subtipo Character que possui as mesmas caractersticas de seu tipo bsico.

UNIVEM PL/SQL

Dino Spoto 6


DINO

Exemplo - Char

Listagem L03_09 Tipo de dado - CHARSQL> VARIABLE P1 VARCHAR2(100)SQL> DECLARE

2 WCHAR CHAR(5);3 BEGIN4 WCHAR := 'ABC';5 :P1 := '*'||WCHAR||'*';6 END;7 /


SQL> PRINT P1

P1------------------------------

*ABC *

Na listagem L03_09, possvel observar que a varivel foi completada com brancos direita at atingir o comprimento especificado pelo tipo.


DINO

Tipos de Dados Varchar2

5. Varchar2Tipo de dado alfanumrico de tamanho varivel com comprimento de at 32.767bytes. A especificao de tamanho obrigatria para uma varivel declarada com esse tipo.O comprimento especificado em bytes e no em caracteres. Isso importante quando armazenamos valores multibyte.O conjunto de valores vlidos para armazenamento depende do charset do banco de dados.A definio de uma coluna do tipo Varchar2 no banco de dados est limitada a 4.000 bytes.O tipo Varchar2 possui os seguintes subtipos:

- String Possui as mesmas caractersticas do seu tipo bsico. Considerado um sinnimo.

Varchar Possui as mesmas caractersticas do seu tipo bsico atualmente. Em verses futuras, se tornar um tipo separado com caractersticas de comparao diferentes do Varchar2.


DINO

Exemplo Varchar2

Listagem L03_10 Tipo de dado Varchar2SQL> DECLARE

2 WVARCHAR2 VARCHAR2(32767);3 WVARCHAR VARCHAR(100);4 BEGIN5 WVARCHAR2 := 'ABC';6 WVARCHAR := WVARCHAR2;7 :P1 := '*'||WVARCHAR||'*';8 END;9 /


SQL> PRINT P1

P1----------------------------

*ABC*

Observe que, neste caso, somente armazenado para a varivel o que realmente foi atribudo.


DINO

Tipos de Dados - Long

6. LongTipo de dado alfanumrico de tamanho varivel com comprimento de at 32.767 bytes. Podemos especificar o tamanho mximo na declarao de uma varivel com esse tipo.Uma coluna Long no banco de dados armazena at2 GB (2.147.483.647 bytes).Podemos fazer referncia a colunas Long em comandos de DML (Insert, Update e muitos comandos Select), mas no em expresses, chamadas de funes SQL, ou em certas clusulas, tais como Where, Group By e Connect By.


DINO

Exemplo - Long

Listagem L03_11 Tipo de dado - LongSQL> DECLARE

2 WLONG LONG(32767);3 BEGIN4 WLONG := 'ABC';5 :P1 := '*'||WLONG||'*';6 END;7 /


SQL> PRINT P1P1

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

*ABC*


DINO

Tipos de Dados

7. RawTipo de dado binrio de tamanho varivel com comprimento de at 32.767. A especificao de tamanho na declarao de uma varivel com esse tipo obrigatria. So semelhantes a colunas Varchar2, porm a PL/SQL no interpreta seu contedo. Uma coluna Raw no banco de dados armazena at2.000 bytes.

8. Long RawTipo de dado binrio de tamanho varivel com comprimento de at 32.767.

A especificao de tamanho na declarao de uma varivel com esse tipo obrigatria. So semelhantes a colunas Long, porm a PL/SQL no interpreta seu contedo.Uma coluna Long Raw no banco de dados armazena at 2 GB (2.147.483.647).

9. RowidPara armazenamento de valores Rowid do banco de dados. Cada tabela criada no banco de dados possui uma pseudocoluna Rowid que armazena valores hexadecimais que correspondem ao endereo de cada linha (row).

UNIVEM PL/SQL

Dino Spoto 7


DINO

Exemplo - Rowid

Listagem L03_12 Tipo de dado - RowidSQL> SELECT CD_MAT, ROWID FROM FUNC

2 WHERE ROWNUM < 10;

CD_MAT ROWID---------- ------------------

10 AAAC0zAABAAAE5yAAA30 AAAC0zAABAAAE5yAAB50 AAAC0zAABAAAE5yAAC60 AAAC0zAABAAAE5yAAD70 AAAC0zAABAAAE5yAAE90 AAAC0zAABAAAE5yAAF

100 AAAC0zAABAAAE5yAAG110 AAAC0zAABAAAE5yAAH120 AAAC0zAABAAAE5yAAI

9 linhas selecionadas.

Um Rowid contm o seguinte formato:

- Data object number- Data file (onde o primeiro arquivo recebe o nmero 1)- Data block in the data file

- Row in the data block(onde a primeira ainda recebe o nmero 0)


DINO

Tipos de Dados

10. UrowidArmazena um Rowid lgico. Usado para captura do Rowid (lgico) de tabelas Index Organized.

11. NcharTipo de dado alfanumrico de tamanho fixo com comprimento de at 32.767 bytes. Podemos especificar o tamanho mximo na declarao de uma varivel com este tipo. Caso isso no seja especificado, o comprimento default de 1 byte.

12. Nvarchar2Tipo de dado alfanumrico de tamanho varivel com comprimento de at32.767 bytes. A especificao de tamanho obrigatria para uma varivel declarada com este tipo

13. Boolean um tipo de dados para variveis (no podem ser definidas como colunas em uma base de dados). Armazena valores lgicos True e False e a ausncia de valor Null. No possui tamanho e nem qualquer tipo de parmetro.


DINO

Tipo de Dado - Date

14. Date Tipo de dado para armazenamento de valores de data e hora. Os valores variam de 01 de janeiro de 4712 A.C. at 31 de dezembro de 9999 D.C.Na definio de uma varivel de tipo data, no especificamos comprimento.Podemos operar com variveis tipo data (da mesma forma que com colunas Date) adicionando ou subtraindo valores inteiros, usando as funes de SQL para data e subtraindo duas datas para obter o intervalo de dias.Quando associamos uma data a uma varivel alfanumrica, ocorre a converso automtica de tipo, da mesma forma que ocorre quando associamos uma varivel alfanumrica a uma varivel de data. O layout default para a converso depende do valor de Nls_Date_Format em vigor para a sesso.


DINO

Exemplo - Date

Listagem L03_13 Tipo de dado - DateSQL> DECLARE2 WVARCHAR VARCHAR2(20);3 WVARCHAR2 VARCHAR2(20) := '05/02/87';4 WDATE DATE;5 WDATE2 DATE;6 BEGIN7 WDATE := TO_DATE('01011987', 'DDMMYYYY');8 WVARCHAR := WDATE;9 WDATE2 := WVARCHAR2;10 :P1 := WVARCHAR||' - '||TO_CHAR(WDATE2, 'DDMMYYYY');11 END;12 /


SQL> PRINT P1

P1

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

01/01/87 - 05021987

O formato default de data para a sesso era dd/mm/rr, uma vez que a atribuio de uma varivel Varchar2 a uma varivel Date foi interpretada corretamente.


DINO

Tipos de Dados - Data

15. TimestampExtenso do tipo Date. Ano, ms e dia assim com hora, minuto, segundo e frao de segundo. Podemos indicar o nmero de dgitos da parte fracionria do segundo.

16. Timestamp With Time ZoneExtenso ao tipo Timestamp. Inclui a apresentao da zona de tempo. Onde a zona de tempo corresponde diferena (entre horas e minutos) entre a hora local e UTC (hora de Greenwich).

17. Timestamp With Local Time ZoneExtenso ao tipo Timestamp. A diferena entre esta opo e a anterior que a zona de tempo no armazenada no banco de dados. O dado, quando armazenado, normalizado para Dbtimezone e, quando recuperado, visualizado pelo usurio com a Time Zone da sesso (ocorre uma segunda converso).

18. Interval Day to SecondArmazena um perodo de tempo em dias, horas, minutos e segundos.

19. Interval Year to MonthArmazena um perodo de tempo em anos e meses.


DINO

Exerccio - Datas

Listagem L03_14 Tipo de dado - DatasSQL> VARIABLE P1 VARCHAR2(1000)SQL> VARIABLE P2 VARCHAR2(1000)SQL> DECLARE2 WTM TIMESTAMP := CURRENT_TIMESTAMP;3 WTM_TZ TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;4 WTM_LTZ TIMESTAMP WITH LOCAL TIME ZONE :=

LOCALTIMESTAMP;5 IDS INTERVAL DAY TO SECOND := NUMTODSINTERVAL

(1125.3, 'MINUTE');6 IYM INTERVAL YEAR TO MONTH := NUMTOYMINTERVAL

(25.3, 'MONTH');7 BEGIN8 :P1 := WTM||' | '||WTM_TZ||' | '||WTM_LTZ;9 :P2 := IDS||' | '||IYM;10 END;11 /


UNIVEM PL/SQL

Dino Spoto 8


DINO

Exemplo

SQL> PRINT P1

P1 (WTM ) (WTM_TZ ) (WTM_LTZ)--------------------------------------------------------------------------------

22/08/03 20:00:59,328000 | 22/08/03 20:00:59,390000 -03:00 | 22/08/03 20:00:59,406000

SQL> PRINT P2

P2 (IDS) (IYM )--------------------------------------------------------------------------------

+00 18:45:18.000000 | +02-01

Na listagem L03_14 podemos observar que o padro de apresentao para valores de timestampj compreende data, hora, minuto, segundo e, para alguns, a zona de tempo. A varivel Day to Second armazena um valor que pode conter dia(no data), hora, minuto, sgundo e frao.J a varivel do tipo Year To Month armazena anos e meses.


DINO

Tipo de Dados - Lob

20. LobsSo conjuntos de tipos de dados predefinidos e que armazenam valores chamados locators, os quais especificam a localizao dos lobs (largeobjects) armazenados na linha ou fora dela.Armazenam valores com comprimento de at 4 GB. Permitem o acesso randmico a trechos do dado.So diferentes dos tipos Long e Long Raw nos seguintes aspectos:

- Podem ser atributos (exceto Nclob) de tipos objeto, Longs no podem.- O comprimento mximo de 4 GB e o dos Longs 2 GB.- Lobs suportam acesso randmico, enquanto Longs suportam somente

acesso seqencial.- Pode-se declarar diversas colunas Lob em uma mesma tabela; Longs

no so permitidos (somente 1).


DINO

Tipos de Dados

21. BlobO tipo Blob tem a capacidade de armazenar grandes valores binrios. O tamanho mximo no pode exceder 4 GB. O armazenamento da informao pode ser feito na prpria linha ou em outro espao especfico.

22. ClobO tipo Clob tem a capacidade de armazenar grandes volumes de dados alfanumricos single-

byte. O tamanho mximo no pode exceder 4 GB. O armazenamento da informao pode ser feito na prpria linha ou em outro espao especfico.

23. NclobO tipo Nclob tem a capacidade de armazenar grandes volumes de dados alfanumricos single-byte ou multibyte do tipo Nchar. O tamanho mximo no pode exceder 4 GB. O armazenamento da informao pode ser feito na prpria linha ou em outro espao especfico.

24. BfileO tipo Bfile tem a capacidade de armazenar grandes volumes de dados binrios fora do banco de dados. O locator de um Bfile inclui um diretrio que especifica o caminho completo do arquivo no servidor. O arquivo endereado por um Bfile fica fora do banco de dados. No banco de dados, armazenamos apenas um locator (endereo) para o arquivo. O tamanho do arquivo no pode exceder 4 GB


DINO

Tipos de Dados - COMPOSTOS

So aqueles tipos que possuem componentesinternos que podem ser manuseados individualmente.

Nesse grupo, se encontram os tipos definidos pelo usurio: Table, Record e Varray. Sero vistos em tpicos especficos de Banco de Dados Objeto Relacional.


DINO

Tipos de Dados - REFERENCE

So aqueles tipos que armazenam valores chamados ponteiros, que apontam para outros itens do programa ou do banco de dados.

Fazem parte deste grupo os tipos Ref Cursor e Ref , que tambm sero vistos em tpicos especficos.


DINO

SUBTIPOS DEFINIDOS PELO USURIO

Cada tipo de PL/SQL define um conjunto de valores vlidos e um conjunto de operaes aplicveis s variveis declaradas com aquele tipo. Subtipos declaram o mesmo conjunto de operaes, mas apenas um subconjunto de seus valores.

Listagem L03_15 SubtipoSQL> DECLARE2 SUBTYPE DATA IS DATE NOT NULL;3 SUBTYPE TEXTO IS VARCHAR2;4 SUBTYPE CODIGO IS NUMBER;5 DT_HOJE DATA := SYSDATE;6 DESCRICAO TEXTO(100);7 CD_MAT CODIGO(5);8 BEGIN9 DT_HOJE := SYSDATE;10 DESCRICAO := 'TESTE DE SUBTIPO';11 CD_MAT := 12345;12 :P1 := DESCRICAO||'-'||CD_MAT||'-'||DT_HOJE;13 END;14 /Procedimento PL/SQL concludo com sucesso.

SQL> PRINT P1

P1

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

TESTE DE SUBTIPO-12345-04/04/04

UNIVEM PL/SQL

Dino Spoto 9


DINO

Subtipos - Exemplos

Podemos definir subtipos de um tipo bsico e, posteriormente, uma varivel com aquele tipo. As restries estabelecidas para o subtipo devem ser respeitadas pelo item (veja Listagem L03_16).

Listagem 3.16 SubtipoSQL> DECLARE2 SUBTYPE DATA IS DATE NOT NULL;3 SUBTYPE TEXTO IS VARCHAR2(100);4 SUBTYPE CODIGO IS NUMBER(5);5 DT_HOJE DATA := SYSDATE;6 DESCRICAO TEXTO(100);7 CD_MAT CODIGO(5);8 BEGIN9 DT_HOJE := SYSDATE;10 DESCRICAO := 'TESTE DE SUBTIPO';11 CD_MAT := 123456;12 :P1 := DESCRICAO||'-'||CD_MAT||'-'||DT_HOJE;13 END;14 /

DECLARE*

ERRO na linha 1:ORA-06502: PL/SQL: erro: preciso de nmero grande demais numrico ou de valorORA-06512: em line 11


DINO

CONVERSO IMPLCITA Quando atribumos uma varivel de um determinado tipo a

uma outra varivel de outro tipo, ocorre uma converso implcita de tipo de dado.

A converso implcita possvel em PL/SQL, mas no recomendada, porque pode dificultar a performance e, ainda, sofrer modificaes de uma verso para outra do software.

A tabela a seguir indica o que possvel converter:


DINO

Tabela 3.01 Converso Implcita de Tipo de Dado

Tipo B_Integer Blob Char CLOB Date Long Number P_Integer Raw Urowid Varchar2

BInteger S S S S S

Blob S

Char S S S S S S S S S

CLOB S S

Date S S S

Long S S S

Number S S S S S

P_Integer S S S S S

Raw S S S S

Urowid S S

Varchar2 S S S S S S S S S


DINO

Exemplos Converso ImplcitaListagem L03_17 Converso implcitaSQL> DECLARE

2 DT_HOJE DATE := SYSDATE;3 DESCRICAO VARCHAR2(100) := '123';4 CD_MAT NUMBER(5) := 0;5 BEGIN6 CD_MAT := DESCRICAO;7 DESCRICAO := DT_HOJE;8 :P1 := DESCRICAO||'-'||CD_MAT||'-'||DT_HOJE;9 END;

10 /


SQL> PRINT P1

P1

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

04/04/04-123-04/09/04

Na Listagem L03_17, as atribuies no resultaram em erros, pois os valores eram compatveis. Qual o resultado obtido se a varivel Descrio fosse inicializada com o valor abc em vez de 123?


DINO

DECLARAES

A parte declarativa de um bloco de PL/SQL precede a parte executiva e deve conter todas as variveis necessrias execuo do bloco. A PL/SQL no declara, implicitamente, variveis como ocorre em outras linguagens.

Variveis e Constantes Podemos declarar variveis e constantes na parte declarativa

de qualquer bloco, subprograma ou pacote.

[Not Null] [:=

UNIVEM PL/SQL

Dino Spoto 10


DINO

INTEGRAO COM O ORACLE As variveis usadas pela PL/SQL podem ter os mesmos

tipos existentes no banco de dados, tanto os tipos predefinidos quanto aqueles definidos pelos usurios.

Os atributos %TYPE e %ROWTYPE permitem a integrao com o dicionrio de dados Oracle, pois podemos declarar uma varivel com o mesmo tipo de uma coluna definida em uma tabela do banco de dados.

Essa facilidade produz independncia do dado, reduz os custos de manuteno e permite que os programas se adaptem s mudanas ocorridas no banco de dados.

Campo da Tabela Tupla da Tabela


DINO

%TYPE

O atributo %Type copia o tipo de dado de uma varivel ou coluna do banco de dados.

Podemos, desta forma, declarar outra varivel baseada na definio de uma coluna do banco de dados ou baseada na definio de outra varivel.

Isso particularmente usado quando declaramos variveis que venham a receber informaes de colunas do banco de dados.

A utilizao de %Type favorece a independncia de dados, uma vez que no precisamos saber exatamente o tipo de dado da varivel a ser declarada e, caso haja modificaes no objeto original, a modificao da varivel Cpia feita automaticamente.


DINO

ExemploListagem 3.19 Declarao de varivelSQL> DECLARE

2 V_COD NUMBER(4) := 0;3 V_DATA DATE NOT NULL := SYSDATE;4 --5 WCOD V_COD%TYPE NOT NULL := 1234;6 WDATA V_DATA%TYPE := SYSDATE;7 --8 WDEP DEPTO.CD_DEPTO%TYPE;9 BEGIN

10 WDEP := NULL;11 V_COD := 1;12 WDATA := NULL;13 END;14 /

WDATA := NULL;*

ERRO na linha 12:ORA-06550: linha 12, coluna 14:PLS-00382: a expresso do tipo incorretoORA-06550: linha 12, coluna 3:PL/SQL: Statement ignored


DINO

ESCOPO DE VISIBILIDADE

O escopo de uma varivel a regio (bloco, subprograma ou pacote) onde a referncia a ela vlida. Dentro de um mesmo escopo, todas as variveis devem ter nomes nicos.

Uma varivel declarada em um bloco visvel nesse e em todos os blocos subordinados a ele. Se uma varivel redefinida em um sub-bloco, ambas so acessveis. No sub-bloco, porm, qualquer referncia no qualificada faracesso varivel de nvel mais interno.

Uma varivel declarada em um bloco deixa de existirquando o bloco termina.


DINO

Exemplo - Visibilidade

Listagem L03_20 Declarao de varivelSQL> VARIABLE MSG VARCHAR2(200) - - Varivel bindSQL> DECLARE2 WNUM NUMBER := 12;3 WCHAR VARCHAR2(20) := 'WCHAR EXTERNA';4 WUNICO NUMBER := 5;5 BEGIN6 DECLARE7 WNUM NUMBER := 10;8 WCHAR VARCHAR2(20) := 'WCHAR INTERNA';9 WTOTAL NUMBER;10 BEGIN11 WTOTAL := WNUM + WUNICO;12 :MSG := WCHAR||'-'||WTOTAL;13 END;14 END;15 /


SQL> PRINT MSG

MSG------------------------------

WCHAR INTERNA-15


DINO

Exemplos - VisibilidadeNa listagem L03_21 a seguir, fizemos referncia varivel wtotal no bloco principal e

recebemos um erro indicando que neste bloco no havia sido declarada nenhuma varivel wtotal. Esta varivel deixou de existir quando passamos pelo End ou sub-bloco.

Listagem L03_21 Declarao de varivelSQL> DECLARE2 WNUM NUMBER := 12;3 WCHAR VARCHAR2(20) := 'WCHAR EXTERNA';4 WUNICO NUMBER := 5;5 BEGIN6 DECLARE7 WNUM NUMBER := 10;8 WCHAR VARCHAR2(20) := 'WCHAR INTERNA';9 WTOTAL NUMBER;10 BEGIN11 WTOTAL := WNUM + WUNICO;12 :MSG := WCHAR||'-'||WTOTAL;13 END; - - Acaba o escopo da varivel WTOTAL14 WTOTAL := WTOTAL + 1;15 END;16 /

WTOTAL := WTOTAL + 1;*

ERRO na linha 14:

ORA-06550: linha 14, coluna 3:

PLS-00201: o identificador 'WTOTAL' deve ser declarado

ORA-06550: linha 14, coluna 3:PL/SQL: Statement ignored

UNIVEM PL/SQL

Dino Spoto 11


DINO

QUALIFICAO Para controlarmos a visibilidade e termos acesso s variveis

dos blocos de nvel superior, devemos qualificar qualquer referncia s variveis externas nos blocos internos.

Listagem L03_22 QualificaoSQL> BEGIN

2 3 DECLARE4 WCOD NUMBER := 1;5 BEGIN6 DECLARE7 WCOD NUMBER:= 5;8 BEGIN9 IF WCOD EXT.WCOD THEN

10 :MSG := 'DIFERENTE';11 ELSE12 :MSG := 'IGUAL';13 END IF;14 END;15 END;16 END;17 /


SQL> PRINT MSG

MSG

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

DIFERENTE


DINO

RESTRIES

PL/SQL no admite referncias a variveis no declaradas, mesmo que elas venham a ser declaradas posteriormente. Para que utilizemos uma varivel (mesmo sendo na declarao de outra), devemos ter efetuado sua declarao primeiro.

Listagem L03_23 Referncia a varivelSQL> DECLARE

2 WCOD NUMBER := WVAL + 1;3 WVAL NUMBER := 0;4 BEGIN5 WCOD := 1;6 END;7 /

WCOD NUMBER := WVAL + 1;

*

ERRO na linha 2:ORA-06550: linha 2, coluna 30:

PLS-00320: a declarao do tipo desta ORA-06550: linha 2, coluna 20:

PL/SQL: Item ignoredORA-06550: linha 5, coluna 3:PLS-00320: a declarao do tipo desta ORA-06550: linha 5, coluna 3:

PL/SQL: Statement ignored


DINO

Exemplo Declarao de Variveis

Listagem L03_24 Declarao de varivelSQL> DECLARE

2 A, B, C NUMBER;3 BEGIN4 A:= 0;5 END;6 /A, B, C NUMBER;*

ERRO na linha 2:ORA-06550: linha 2, coluna 4:PLS-00103: Encontrado o smbolo "," quando um dos seguintes smbolos eraesperado:constant exception table LONG_ double ref

No podemos declarar, de uma vez, diversas variveis referentes a um mesmo tipo.


DINO

Exemplo Variveis booleanasListagem L03_25 Varivel booleanaSQL> DECLARE2 WB1 BOOLEAN;3 WB2 BOOLEAN;4 WB3 BOOLEAN;5 BEGIN6 WB1 := 1;7 WB2 := 'TRUE';8 WB3 := (5 > 3);9 END;10 /

WB1 := 1;

*

ERRO na linha 6:


PLS-00382: a expresso do tipo incorretoORA-06550: linha 6, coluna 3:

PL/SQL: Statement ignoredORA-06550: linha 7, coluna 10:

PLS-00382: a expresso do tipo incorreto


PL/SQL: Statement ignored


DINO

COMANDOS BSICOS

Atribuio A atribuio de valor a uma varivel feita com a notao :=. As variveis e constantes so criadas e recebem seu valor

inicial cada vez que for iniciado o bloco no qual esto declaradas.

Na ausncia de atribuio de valor inicial, a varivel considerada sem valor ou Null.

muito importante, portanto, que as variveis sejam inicializadas antes do uso.


DINO

Exemplo de Atribuio

Listagem L03_26 AtribuioSQL> VARIABLE RETORNO NUMBERSQL> DECLARE2 V1 NUMBER := 1;3 V2 NUMBER;4 BEGIN5 V1 := V1 + V2;6 :RETORNO := V1 * 2;7 END;8 /


SQL> PRINT RETORNO

RETORNO----------

O resultado do programa apresentado pela Listagem L03_26 foi Null,

UNIVEM PL/SQL

Dino Spoto 12


DINO

Comandos

- Comandos de Desvios de Controle (deciso)- Comandos SQL- Comandos de Laos


DINO

IF

O comando IF verifica uma condio, e dependendo do resultado realiza uma ou outra ao. Permite a execuo condicional de uma determinada ao.

Existem trs sintaxes vlidas de IF em PL/SQL.Sintaxe 3.03 IF

IF (CONDIO) THEN [CORPO DE COMANDOS] END IF;

IF (CONDIO) THEN [CORPO DE COMANDOS] ELSE/[ELSEIF] [CORPO DE COMANDOS DO ELSE]

END IF;


DINO

Exemplos

If Var1 > 10 thenVar2 = Var1 + 20 ;

End If ;

If Not ( Var1 < = 10 ) then

Var2 = Var1 + 20 ; End If ;

If Var1 > 10 thenIf Var2 < Var1

thenVar2 =

Var1 + 20 ;End If ;

End If ;


ElseVar2 = Var1 *

Var1 ;End If ;


ElseIf Var1 between 7 and 8 then

Var2 = 2 * Var1;Else

Var2 = Var1 * Var1 ;End If ;

End If ;

If Var1 > 10 then

Var2 = Var1 + 20 ;

ElseIf Var1 between 7 and 8 then

Var2 = 2 * Var1 ;

Else

Var2 = Var1 * Var1;

End If;Tpicos_Especiais1 - PL-SQL PL/SQL - 70

DINO

Exerccio - IF

Listagem L03_27 IFSQL> VARIABLE MSG VARCHAR2(100)SQL> DECLARE2 VALOR NUMBER := &VAL;3 BEGIN4 IF VALOR > 0 THEN5 :MSG := 'Valor maior que zero';6 ELSIF VALOR = 0 THEN7 :MSG := 'Valor igual a zero';8 ELSE9 :MSG := 'Valor menor que zero';10 END IF;11 END;12 /

Entre o valor para val: -5antigo 2: VALOR NUMBER := &VAL;novo 2: VALOR NUMBER := -5;


SQL> PRINT MSG

MSG------------------------------------------------

Valor menor que zero


DINO

IF - %FOUND

beginupdate FUNC set

VL_SAL=240where VL_SAL

UNIVEM PL/SQL

Dino Spoto 13


DINO

Exemplo Select Into

Listagem L03_28 Select IntoSQL> DECLARE

2 SALARIO NUMBER;3 ENDROW ROWID;4 V_COD FUNC.CD_MAT%TYPE := 150;5 BEGIN6 SELECT VL_SAL, ROWID INTO SALARIO, ENDROW7 FROM FUNC8 WHERE CD_MAT =V_COD;9 :MSG := Salrio Ant = ||SALARIO|| - ;

10 IF SALARIO < 2000 THEN11 SALARIO := SALARIO * 1.3;12 ELSIF SALARIO IS NULL THEN13 SALARIO := 1500;14 ELSE15 SALARIO := SALARIO * 1.15;16 END IF;17 UPDATE FUNC SET VL_SAL = SALARIO18 WHERE ROWID = ENDROW;19 :MSG := :MSG|| Salrio Atual = '||SALARIO;20 COMMIT;21 END;22 /


SQL> PRINT MSG

MSG

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

Salrio Ant = 2700,0 Salrio Atual = 2907,2


DINO

GOTO

O comando Goto efetua um desvio incondicional para um Label.

O Label deve ser nico dentro do escopo e deve preceder um comando ou um bloco PL/SQL. Alm de ser usado para desvios, um Label tambm serve como qualificador de variveis, como foi mostrado no item Qualificao do tpico Declaraes.

Veremos no prximo tpico que a PL/SQL possui diversas estruturas de iterao e que raramente teremos necessidade de utilizar um comando Goto.


DINO

Exemplo - Goto

Listagem L03_29 GotoSQL> DECLARE2 CONTA NUMBER := 0;3 BEGIN4 5 CONTA := CONTA + 1;6 IF CONTA < 5 THEN7 GOTO INICIO;8 END IF;9 END;10 /


Na Listagem L03_29, aparece um Label(incio) que deve ser codificado entre . No comando Goto, os smbolos () no devem ser usados.


DINO

Restries - Goto

Existem algumas situaes em que o comando Goto no pode ser usado:

- - Desvio para dentro de um IF ou Loop (GoTo Prximo_IF).- - Desvio para dentro de um sub-bloco (GoTo subbloco).- - Desvio para fora de um subprograma (GoTo Para_Fora).

- Desvio para dentro de um bloco a partir da rea de exceo (GoTo Incio).


DINO

Exerccio restries goto

Listagem L03_30 GoToSQL> DECLARE

2 PROCEDURE DESVIO IS3 BEGIN4 GOTO PARA_FORA;5 END;6 BEGIN7 8 IF TRUE THEN9 GOTO PROXIMO_IF;

10 END IF;11 IF FALSE THEN12 13 GOTO SUBBLOCO;14 END IF;15 16 BEGIN17 18 NULL;19 END;20 EXCEPTION21 WHEN OTHERS THEN22 GOTO INICIO;23 END;24 /

GOTO PROXIMO_IF;*

ERRO na linha 9:ORA-06550: linha 9, coluna 7:PLS-00375: instruo GOTO invlida;

este GOTO no pode ser ramificado para o

label 'PROXIMO_IF'ORA-06550: linha 11, coluna 13:PL/SQL: Statement ignoredORA-06550: linha 4, coluna 5:PLS-00375: instruo GOTO invlida;


label 'PARA_FORA'ORA-06550: linha 6, coluna 1:PL/SQL: Statement ignoredORA-06550: linha 22, coluna 5:PLS-00375: instruo GOTO invlida;


label 'INICIO'ORA-06550: linha 22, coluna 5:PL/SQL: Statement ignored


DINO

NULL

Este comando, explicitamente, indica que no h ao a ser feita. Serve para compor certas situaes em que um comando exigido, mas nenhuma ao , realmente, necessria.

Listagem L03_31 NullSQL> DECLARE

2 VEZ NUMBER := 1;3 BEGIN4 5 VEZ := VEZ + 1;6 IF VEZ > 10 THEN7 GOTO FIM;8 ELSE9 VEZ := VEZ**2 - 1;

10 END IF;11 GOTO INICIO;12 13 NULL;14 END;15 /


J vimos anteriormente que todo Label deve preceder um comando ou um bloco de PL/SQL. No exemplo da listagem L03_31, a presena do comando Null teve a finalidade de validar o posiocionamento do Label Fim. Caso o comando no fosse acionado, no poderamos programar o Fim antes de End(no um comando), que , na verdade, fim de bloco.

UNIVEM PL/SQL

Dino Spoto 14


DINO

Estruturas de controle

Ciclos LOOP FOR WHILE


DINO

CURSOR TYPE (L03_82)

SET LINESIZE 70DECLARETYPE TREG IS RECORD (NOME VARCHAR2(30),

MAT NUMBER NOT NULL := 0,DEP DEPTO.CD_DEPTO%TYPE);

VREG TREG;CURSOR C1 IS SELECT NM_FUNC||' '||NM_SOBRENOME, CD_MAT,

CD_DEPTO FROM FUNC;BEGINOPEN C1;:MSG := '';LOOPFETCH C1 INTO VREG;EXIT WHEN C1%NOTFOUND;IF VREG.MAT > 200 AND VREG.DEP IN ('D11', 'E11') THEN

:MSG := :MSG || VREG.NOME ||'; ';END IF;

END LOOP;END;/


DINO

Uso de Procedure L03_101-107

-- Criar uma procedure INSS que calcula o desconto do salrio A entrada de parmetros Sal (IN) e saida Valor (IN OUT)

DECLAREVINSS NUMBER;SALARIO NUMBER := 3000;PROCEDURE INSS(SAL IN NUMBER, VALOR IN OUT NUMBER) ISBEGINVALOR := SAL * 0.08;IF VALOR >= 1000 THEN

VALOR := 1000;END IF;

END INSS;BEGININSS(3000, VINSS);INSS(SALARIO, VINSS);

END;/Procedimento PL/SQL concludo com sucesso.


DINO

Estruturas de controle

LOOPstatement1;

EXIT [WHEN condition];END LOOP;

condition - uma varivel booleana ou uma expresso que evolui para (TRUE, FALSE ou NULL)

Exerccio: bloco que insira 10 linhas numa tabela de linhas de encomenda, para a encomenda 0017


DINO

Exemplo de ciclo

DECLAREv_num_enc itens.no_enc%TYPE := 134;Contador number(2) := 1;BEGIN

LOOPINSERT INTO itens( no_enc, linha )

values(v_num_enc, contador );Contador:= contador+1;Exit when contador >10;End loop;

END;


DINO

Estruturas de controlo

FOR counter IN [REVERSE]lower_bound . . uper_bound LOOP

statement1;statement2;. . .

END LOOP;

WHILE condition LOOPstatement1;statement2;. . .

END LOOP;

UNIVEM PL/SQL

Dino Spoto 15


DINO

FOR - IN REVERSE

O comando FOR permite que controle o nmero de vezes que as aoes foram repetidas e define quando termina essa repetio.

FOR variavel_de_controle IN REVERSE ( menor_valor .. maior_valor );

declarex number;

beginFor x in 1..10

loopdbms_output.put_line(x);

end loop;end;


DINO

While - condio

O comando WHILE permite que se verifica uma condio a cada incio de execuo de um grupo de aes.

WHILE condicaodeclare

x number := 0;begin

while x < 10loopdbms_output.put_line(x);x := x + 1;

end loop;end;


DINO

FOR - TYPE

Listagem: L03_73 a 79DECLARE

TYPE TTABLE IS TABLE OF VARCHAR2(20)INDEX BY BINARY_INTEGER;

TTEXTO TTABLE;I NUMBER := 0;CURSOR C1 IS SELECT NM_FUNC FROM FUNC;

BEGINFOR RC1 IN C1 LOOP

I := I + 1;TTEXTO(I) := RC1.NM_FUNC;

END LOOP;:MSG := 'Quantidade de elementos lidos : '||TTEXTO.COUNT;

END;/


DINO

Exemplo comando de saida

declarev_sal number(10,2);v_ename varchar2(20);

beginselect ename,sal into v_ename,v_sal from emp

where empno = 7369;if v_sal < 5000 then

dbms_output.put_line('O '||v_ename||' recebe um salrio razovel !!!');elsif v_sal = 5000 then

dbms_output.put_line('O '||v_ename||' recebe um bom salrio!!!');else

dbms_output.put_line('O '||v_ename||' recebe um salrio timo !!!');end if;end;/


DINO

Parte2

STORED PROCEDURE PROCEDURE FUNCTION TRIGGERS


DINO

Tipos de Blocos

Annimo Procedimento Funo

[DECLARE]

BEGIN--statements

[EXCEPTION]

END;

PROCEDURE nameIS

BEGIN--statements

[EXCEPTION]

END;

FUNCTION nameRETURN datatypeISBEGIN-- statementsRETURN value;

[EXCEPTION]

END;

Um bloco dentro de outro funciona como uma instruo (composta)Nvel de encaixe arbitrrio

Funes SQL disponveis em PL/SQL excepto as de agregao

UNIVEM PL/SQL

Dino Spoto 16


DINO

Criando Procedures

CREATE OR REPLACE PROCEDURE nome_da_procedure( argumento mode

tipo_do_argumento) IS/ASbloco_PL/SQL;

Obs.: A clusula REPLACE utilizada quando a procedure j existe. Nunca utilize a clusula DECLARE no incio do bloco PL/SQL.


DINO

Criando Procedures

Argumento o nome de uma varivel PL/SQL passada para a procedure

Mode identifica o tipo de argumento ( IN/OUT/IN OUT)

Tipo do argumento tipo do dado ( datatype)

Bloco PL/SQL o corpo da procedure que define as aes que sero executadas quando a procedure for executada

IS ou AS essas clusulas so equivalentes, pode-se utilizar tanto uma quanto outra


DINO

ARGUMENTOS

Somente um dos trs modos pode ser escolhido para cada argumento (IN,OUT,IN OUT).

IN argumento Passa o valor do ambiente chamador para a procedure(default).

OUT argumento Retorna um valor da procedure para o ambiente chamador.

IN OUT argumento Passa um valor do ambiente chamador para a procedure, e a procedure retorna um valor para o ambiente chamador.


DINO

Exemplo : Criando uma procedure com o argumento IN.

Armazene todas as informaes de um novo empregado:CREATE OR REPLACE PROCEDURE novos_empregados

( v_emp_no IN emp.empno%TYPE,v_emp_name IN emp.ename%TYPE,v_emp_job IN emp.job%TYPE,v_mgr_no IN emp.mgr%TYPE,v_emp_hiredate IN emp.hiredate%TYPE,v_emp_sal IN emp.sal%TYPE,v_emp_comm IN emp.comm%TYPE,v_dept_no IN emp.deptno%TYPE)

ISBEGIN

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm,deptno )

VALUES ( v_emp_no, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_dept_no);

COMMIT WORK;END novos_empregados;


DINO

Out

Retornando um valor da procedure para o ambiente chamador utilizando o argumento OUT

Devolver informaes sobre um funcionrio:CREATE OR REPLACE PROCEDURE pesquisa_empregado

( v_emp_no IN emp.empno%TYPE,v_emp_name OUT emp.ename%TYPE,v_emp_sal OUT emp.sal%TYPE,v_emp_comm OUT emp.comm%TYPE)

ISBEGIN

SELECT ename, sal, commINTO v_emp_name, v_emp_sal, v_emp_commFROM empWHERE empno = v_emp_no;

END pesquisa_empregado;


DINO

IN OUT

Exemplo: Criando uma procedure com um argumento IN/OUT.

Transforme a seqncia de sete dgitos de um nmero para o formato de nmero de telefone.

CREATE OR REPLACE PROCEDURE formata_telefone(v_phone_no IN OUT varchar2)

ISBEGIN

v_phone_no := SUBSTR (v_phone_no,1,3) || - ||SUBSTR (v_phone_no,4,4);

END formata_telefone;

UNIVEM PL/SQL

Dino Spoto 17


DINO

Criando Funes

Sintaxe:

CREATE OR REPLACE FUNCTIONnome_da_funcao

( argumento mode tipo_do_argumento)RETURN tipo_do_dado

IS/ASbloco_pl/sql

RETURN Identifica o tipo do dado que a funo irretornar.


DINO

Criando uma funo

Retornar um valor de uma funo para o ambiente chamador utilizando a clusula RETURN

Exemplo : Criando uma funo.

CREATE OR REPLACE FUNCTION pesquisa_salario( v_emp_no IN s_emp.empno%TYPE)RETURN NUMBER

ISv_emp_sal emp.sal%TYPE:= 0;

BEGINSELECT sal INTO v_emp_salFROM empWHERE empno = v_emp_no;RETURN (v_emp_sal);

END pesquisa_salario;


DINO

Exibindo resultados -SERVEROUTPUT

1. Habilite a opo SERVEROUTPUTSET serveroutput ON

2. Prepare o texto da mensagem de sada com a procedure PUT

DBMS_OUTPUT.PUT(texto);3. Exiba as mensagem de sada na tela com a

procedure NEW_LINEDBMS_OUTPUT.NEW_LINE;


DINO

Exibindo uma FunoCREATE OR REPLACE FUNCTION average_sal

(v_n IN NUMBER)RETURN NUMBER

ISCURSOR emp_cursor IS

SELECT empno, salFROM empORDER BY sal DESC;

v_total_sal emp.sal%type := 0;v_counter NUMBER;

BEGINFOR r_emp IN emp_cursor LOOP

EXIT WHEN emp_cursor%ROWCOUNT > v_n;v_total_sal := v_total_sal + r_emp.sal;v_counter := emp_cursor%ROWCOUNT;DBMS_OUTPUT.PUT (Loop = );DBMS_OUTPUT.PUT (v_counter);DBMS_OUTPUT.PUT (; Empno = );DBMS_OUTPUT.PUT (r_emp.empno);DBMS_OUTPUT.NEW_LINE;

END LOOP;RETURN (v_total_sal/v_counter);

END average_sal;

Exibindo a Funo:

SQL> SET verify OFFSQL> SET serveroutput ONSQL> DEFINE p_n = 3SQL> VARIABLE g_average NUMBER

SQL> EXECUTE :g_average := average_sal (&p_n);


DINO

Exception

declarev_nome varchar2(30);v_sal number(8,2);

beginselect NM_FUNC, VL_SAL

INTO v_nome, v_salFrom func

where CD_MAT=122;dbms_output.put_line(v_nome||' '||v_sal);exceptionwhen no_data_found thendbms_output.put_line('Funcionario nao Cadastrado');

end;/


DINO

Exception - %TYPE

declarev_nome FUNC.NM_FUNC%TYPE;v_sal FUNC.VL_SAL%TYPE;

beginselect NM_FUNC, VL_SALINTO v_nome, v_salFrom FUNC;

dbms_output.put_line(v_nome||' '||v_sal);exceptionwhen no_data_found thendbms_output.put_line('Funcionario nao Cadastrado');

when too_many_rows thendbms_output.put_line('Retornou muitos registros');

end;/

UNIVEM PL/SQL

Dino Spoto 18


DINO

Procedimentos

Sintaxe:CREATE [OR REPLACE] PROCEDURE procedure_name

(parameter1 [mode1] datatype1,parameter2 [mode2] datatype2, ...)

IS | AS PL/SQL Block

Exemplo: SQL> CREATE OR REPLACE PROCEDURE raise_salary2 (v_id in emp.empno%TYPE)3 IS4 BEGIN5 UPDATE emp6 SET sal = sal * 1.17 WHERE empno = v_id;8 END raise_salary;9 /

Executar: SQL> EXECUTE raise_salary (7369) PL/SQL> raise_salary(7369)


DINO

Funes

Criando Funes Para criar uma funo ou procedure depender

de que forma ser chamada e de que forma espera-se os valores.

Criar uma nova funo com o comando CREATE FUNCTION, a qual declara uma lista de argumentos, declara o argumento que ir retornar e define as aes que sero realizadas utilizando blocos PL/SQL.


DINO

Funes

Sintaxe: CREATE [OR REPLACE] FUNCTION function_name(parameter1 [mode1] datatype1,parameter2 [mode2] datatype2,

)RETURN tipo do dadoIS | ASPL/SQL BlockRETURN identifica o tipo do dado que a funo ira retornar

Exemplo: SQL> CREATE OR REPLACE FUNCTION get_sal2 (v_id IN emp.empno%TYPE)3 RETURN NUMBER4 IS v_salary emp.sal%TYPE :=0;5 BEGIN6 SELECT sal INTO v_salary7 FROM emp WHERE empno = v_id;8 RETURN (v_salary);9 END get_sal;10 /


DINO

Exemplo Function (L03_108)

CREATE OR REPLACE FUNCTION NOME(PDEPTO IN VARCHAR2) RETURN VARCHAR2 IS

NOME VARCHAR2(100);BEGIN

SELECT NM_DEPTO INTO NOMEFROM DEPTO

WHERE CD_DEPTO = PDEPTO;RETURN NOME;

EXCEPTIONWHEN NO_DATA_FOUND THEN

NOME := 'DEPARTAMENTO INEXISTENTE';RETURN NOME;

END;/


DINO

Exemplo Procedure (L03_110)

CREATE OR REPLACE PROCEDURE TESTE (MATRICULA IN NUMBER, PERCENTUAL IN NUMBER := 0.1) IS

VPERC NUMBER;BEGIN

IF PERCENTUAL > 1 THENVPERC := PERCENTUAL / 100;

ELSIF PERCENTUAL > 0 THENVPERC := PERCENTUAL;

END IF;IF VPERC IS NOT NULL THEN

UPDATE FUNCSET VL_SAL = VL_SAL + VL_SAL * VPERC

WHERE CD_MAT = MATRICULA;COMMIT;

END IF;END;/


DINO

Fazer as listagens

L03_115 PROCEDURE VERIFICA L03_141 SET SEVEROUT ON L03_142 146 USO DE DBMS_OUTPUT.PUT_LINE L03_163 MALA DIRETA

UNIVEM PL/SQL

Dino Spoto 19


DINO

Passando valores do ambiente chamador para uma procedure utilizando o argumento IN

CREATE OR REPLACE PROCEDURE novos_empregados ( v_emp_no IN emp.empno%TYPE, v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_hiredate IN emp.hiredate%TYPE, v_emp_sal IN emp.sal%TYPE, v_emp_comm IN emp.comm%TYPE, v_dept_no IN emp.deptno%TYPE)

IS BEGIN

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno )

VALUES ( v_emp_no, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm,

v_dept_no); COMMIT WORK;

END novos_empregados; Tpicos_Especiais1 - PL-SQL PL/SQL - 110

DINO

Retornando um valor da procedure para o ambiente chamador utilizando o argumento OUT

CREATE OR REPLACE PROCEDURE pesquisa_empregado ( v_emp_no IN emp.empno%TYPE,

v_emp_name OUT emp.ename%TYPE, v_emp_sal OUT emp.sal%TYPE, v_emp_comm OUT emp.comm%TYPE)

IS BEGIN

SELECT ename, sal, commINTO v_emp_name, v_emp_sal, v_emp_commFROM empWHERE empno = v_emp_no;

END pesquisa_empregado;

Devolver informaes sobre um funcionrio.


DINO

Passando um valor do ambiente chamador para a procedure, e a procedure retorna um valor para o

ambiente chamador

Transforma a seqncia de sete dgitos de um nmero para o formato de nmero de telefone.

CREATE OR REPLACE PROCEDURE formata_telefone (v_phone_no IN OUT varchar2)

IS BEGIN

v_phone_no := SUBSTR (v_phone_no,1,3) || - || SUBSTR (v_phone_no,4,4);

END formata_telefone;


DINO

Gerenciando excees em tempo de execuo

Pode-se gerenciar qualquer tipo de exceo em tempo de execuo permitindo propagar para o ambiente chamador ou tomar aes quando essas acontecerem.

RAISE_APPLICATION_ERROR(numero_erro, texto_erro) Numero_erro o nmero do erro definido pelo

usurio. Deve estar entre -20000 e -20999. Texto_erro a mensagem definida pelo usurio.


DINO

Exemplo

CREATE OR REPLACE PROCEDURE exclui_funcionario(v_emp_no IN emp.empno%TYPE) IS

BEGIN DELETE FROM EMP WHERE empno = v_emp_no;

IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20200, Funcionario nao

existe); END IF; COMMIT WORK;

END exclui_funcionario;


DINO

Exemplo (exception e rollback)CREATE OR REPLACE PROCEDURE inclui_funcionario

( v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_sal IN emp.sal%TYPE)

IS v_emp_hiredate emp.hiredate%TYPE:=sysdate; v_emp_comm emp.comm%TYPE:=0.0; v_dept_no emp.deptno%TYPE;

BEGIN SELECT deptno

INTO v_dept_noFROM empWHERE empno = v_mgr_no;

INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno ) VALUES (Next_emp, v_emp_name, v_emp_job,

v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_dept_no);

COMMIT WORK; EXCEPTION

WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, Gerente nao e um empregado

valido.); END inclui_funcionario;

UNIVEM PL/SQL

Dino Spoto 20


DINO

Exception_Init

Pode-se tambm definir excees que no so contempladas pelo ORACLE.

. declare uma exceo

. associe essa exceo com um nmero de erro utilizando com

. comando PRAGMA EXCEPTION_INIT.


DINO

Exemplo

CREATE OR REPLACE PROCEDURE exclui_funcionario(v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_sal IN emp.sal%TYPE, v_emp_hiredate emp.hiredate%TYPE, v_emp_comm emp.comm%TYPE, v_dept_no emp.deptno%TYPE)

IS e_invalid_manager EXCEPTION; PRAGMA EXCEPTION_INIT (e_invalid_manager, -2291);

BEGIN INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (next_emp, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate,v_emp_sal,

v_emp_comm, v_dept_no); COMMIT WORK; EXCEPTION

WHEN e_invalid_manager THEN RAISE_APPLICATION_ERROR (-20201, Gerente nao e um empregado

valido.); END exclui_funcionario;


DINO

Execuo - Procedure

DECLARE v_empno NUMBER := 7654;

BEGIN ...

exclui_funcionario (v_empno); ...

END; Executando de outra procedure.

CREATE PROCEDURE processa_funcionario(v_emp_no IN emp.empno%TYPE)

IS BEGIN

...

exclui_funcionario (v_empno); ...

END; Tpicos_Especiais1 - PL-SQL PL/SQL - 118

DINO

Execuo - Procedure

Executando do SQL*Plus ou do SQL*DBA. Para isso, necessrio utilizar o comando EXECUTE.

Para entrar com valores via SQL*Plus deve-se utilizar o comando ACCEPT e substituir o parmetro de entrada da procedure pela varivel do ACCEPT iniciado pela string &.

ACCEPT p_empno PROMPT Entre com o numero do funcionario :

EXECUTE exclui_funcionario (&p_empno);


DINO

ProC ou OCI

Executando de um Pr-Compilador ou aplicao OCI. void exec_exclui_func () {

EXEC SQL BEGIN DECLARE SECTION; int empno;

EXEC SQL END DECLARE SECTION; printf(\nEntre com o numero do funcionario: ); scanf(%d,&empno); EXEC SQL EXECUTE;

beginexclui_funcionario(:empno);

end; EXEC SQL END-EXEC;

return; }


DINO

Java - JDBC

1. Criar a stored procedure

2. Criar um programa em java (jdbc) que execute a procedure criada no Oracle

UNIVEM PL/SQL

Dino Spoto 21


DINO

Executando - Funo

Executando uma funo de um bloco PL/SQL DECLARE

v_empno NUMBER := 7654; v_sal NUMBER;

BEGIN ...

v_sal := pesquisa_salario(v_emp_no); ...

END; Executando uma funo de uma procedureCREATE PROCEDURE processa_emp (v_emp_no IN

emp.empno%TYPE) IS v_sal NUMBER; BEGIN

...

v_sal := pesquisa_salario(v_emp_no); ...

END; No SQL*Plus, o valor retornados de uma funo armazenado em

varivel global. Tpicos_Especiais1 - PL-SQL PL/SQL - 122

DINO

Executando uma funo do SQL*Plus

ACCEPT p_empno PROMPT Entre com o numero do funcionario :

VARIABLE g_sal NUMBER

EXECUTE :g_sal := pesquisa_salario(&p_empno);

PRINT g_sal


DINO

Tratando as Funes e Procedures

Exibir todas as procedure e funes.

SELECT object_name, object_typeFROM user_objectsWHERE object_type IN (PROCEDURE, FUNCTION) ORDER BY object_name;

Documentando uma procedure armazenada. SELECT text

FROM user_sourceWHERE type = PROCEDUREAND name = EXCLUI_FUNCIONARIOORDER BY line;


DINO

Criando PACKAGES

Declarando construes pblicas dentro da especificao do package.

Sintaxe: CREATE OR REPLACE PACKAGE

nome_do_package IS/AS -- declaracao_de_variaveis ---- declaracao_de_cursores ---- declaracao_de_excessoes ---- declaracao_de_procedures ---- declaracao_de_funcoes --END nome_do_package


DINO

Exemplo

Criando a especificao do Pacote

CREATE OR REPLACE PACKAGE comm_package IS g_comm_rate NUMBER := 0.1;

PROCEDURE reset_comm_rate(v_comm_rate IN NUMBER);

END comm_package;


DINO

Controle - Package

Definir todas construes, pblicas e privadas, dentro do corpo do package.

Sintaxe : CREATE OR REPLACE PACKAGE BODY

nome_do_package IS/AS -- declaracao_de_variaveis ---- declaracao_de_cursores -- declaracao_de_excessoes ---- declaracao_de_procedures ---- declaracao_de_funcoes --END nome_do_package

UNIVEM PL/SQL

Dino Spoto 22


DINO

Exemplo Armazenando Package

CREATE OR REPLACE PACKAGE BODY comm_package IS FUNCTION validade_comm_rate (v_comm_rate IN NUMBER)

RETURN BOOLEAN IS

v_max_comm_rate NUMBER; BEGIN

SELECT MAX (comm/sal) INTO v_max_comm_rateFROM emp; IF v_comm_rate > v_max_comm_rate THEN

RETURN (FALSE); ELSE

RETURN (TRUE); END IF;

END validade_comm_rate; PROCEDURE reset_comm_rate (v_comm_rate IN NUMBER)

IS v_valid BOOLEAN;

BEGIN v_valid := validade_comm_rate (v_comm_rate); IF v_valid = TRUE THEN

g_comm_rate := v_comm_rate; ELSE

RAISE_APPLICATION_ERROR (-20210, Taxa de comissao invalida); END IF;

END reset_comm_rate; END comm_package; Tpicos_Especiais1 - PL-SQL PL/SQL - 128

DINO

Executando Package

Depois de armazenar o package no banco de dados, chame a construo do package de dentro de um package ou de fora do package, dependendo se a construo for privada ou pblica.

Quando executar uma procedure, funo ou package dentro de um package no necessrio colocar o nome do qualificador.

Exemplo : Chamar uma funo de uma procedure dentro do package.


DINO

Exemplo

CREATE OR REPLACE PACKAGE BODY comm_packageIS ...

PROCEDURE reset_comm_rate (v_comm_rate IN NUMBER) IS

v_valid BOOLEAN; BEGIN

v_valid := validade_comm_rate (v_comm_rate); IF v_valid = TRUE THEN

g_comm_rate := v_comm_rate; ELSE

RAISE_APPLICATION_ERROR (-20210, Taxa de comissao invalida);

END IF; END reset_comm_rate;

END comm_package;Tpicos_Especiais1 - PL-SQL PL/SQL - 130

DINO

Executando Package

Quando chamar uma procedure ou funo de fora do package, o qualificador deve ser utilizado.

Exemplo : Chamando uma procedure de um package do SQL*PlusSQL> EXECUTE comm_package.reset_comm_rate (.15);

Exemplo: Chamando uma procedure de um package de um diferente usurio.

SQL> EXECUTE paulo.comm_package.reset_comm_rate(.15);

Exemplo: Chamando uma procedure de um package de um banco de dados remoto.

SQL> EXECUTE paulo.comm_package.reset_comm_rate (.15)@pr;


DINO

Gatilhos

Um gatilho um bloco PL/SQL que executa implicitamente sempre que um evento particular acontece.

Um gatilho pode ser do BD ou da aplicao. Quando executa?

BEFORE - Antes de executar uma frase DML sobre uma tabela.

AFTER - Depois da frase DML INSTEAD OF - Executa o corpo do gatilho em vez da frase que

ativou o gatilho.


DINO

Gatilhos

Que frase DML ativa gatilhos? INSERT; UPDATE; DELETE.

Tipo de gatilhos: (quantas vezes deve o gatilho executar ao acontecer o evento?) Statement - executa uma vez; Row - executa uma vez para cada linha afetada pelo gatilho.

Corpo do gatilho Bloco PL/SQL ou chamada a procedimento.

UNIVEM PL/SQL

Dino Spoto 23


DINO

Gatilhos

SintaxeCREATE [OR REPLACE] TRIGGER trigger_name

timingevent1 [OR event2 OR event3]

ON table_nametrigger_body

Ou (gatilho de linha)CREATE [OR REPLACE] TRIGGER trigger_name

timingevent1 [OR event2 OR event3]

ON table_name[REFERENCING OLD AS old / NEW AS new]

FOR EACH ROW[WHEN condition]

trigger_body


DINO

Gatilhos

ExemploSQL> CREATE OR REPLACE TRIGGER derive_commission_pct

2 BEFORE INSERT OR UPDATE OF sal ON emp3 FOR EACH ROW4 BEGIN5 IF NOT (:NEW.JOB IN (MANAGER,PRESIDENT))6 and :NEW.SAL > 50007 THEN8 RAISE_APPLICATION_ERROR9 (-20202,Emplyee cannot earn this amount);

10 END IF;11 END;

UPDATE EMP SET SAL=6500 WHERE ENAME=MILLER;


DINO

Exemplo - Before

CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT ON empBEGIN

IF (TO_CHAR (sysdate,`DY`) IN (`SAT`,`SUN`)) OR (TO_NUMBER (TO_CHAR(sysdate,`HH24`)) NOT BETWEEN 8 AND 18) THEN

RAISE_APPLICATION_ERROR (-20500, `No possvel inluir funcionrio fora do horrio normal de trabalho);

END IF; END;


DINO

Combinando - DML

CREATE OR REPLACE TRIGGER secure_empBEFORE DELETE OR INSERT OR UPDATE ON emp

BEGIN IF (TO_CHAR (sysdate,`DY`) IN (`SAT`,`SUN`))

OR (TO_NUMBER (TO_CHAR(sysdate,`HH24`)) NOT BETWEEN 8 AND 18) THEN IF DELETING THEN

RAISE_APPLICATION_ERROR (-20502, `Nao e possivel remover funcionario fora do horario normal de

trabalho); ELSIF INSERTING THEN

RAISE_APPLICATION_ERROR (-20503, `Nao e possivel inserir funcionario fora do horario normal de

trabalho); ELSIF UPDATING (SAL) THEN

RAISE_APPLICATION_ERROR (-20504, `Nao e possivel alterar a coluna salario do funcionario fora do

horario normal de trabalho); END IF;

END IF; END;


DINO

Criando TRIGGERS de linha

Este gatilho dispara quando um evento de manipulao de dados afetando linhas em uma tabela. Para se criar esse trigger, basta apenas acrescentar FOR EACH ROW no comando CREATE TRIGGER.

Sintaxe :

CREATE O REPLACE TRIGGER nome_do_triggerBEFORE/AFTER evento ON nome_da_tabela FOR EACH ROW WHEN clausula_de_restricao

bloco PL/SQL


DINO

Exemplo

CREATE OR REPLACE TRIGGER audit_empBEFORE DELETE OR INSERT OR UPDATE ON empFOR EACH ROW

BEGIN IF DELETING THEN UPDATE audit_tableSET del = del + 1 WHERE user_name = user AND table_name = ÈMP`

AND column_name IS NULL; ELSIF INSERTING THEN

UPDATE audit_tableSET ins = ins + 1 WHERE user_name = user AND table_name = ÈMP`

AND column_name IS NULL; ELSIF UPDATING THEN

UPDATE audit_tableSET upd = upd + 1 WHERE user_name = user AND table_name = ÈMP`

AND column_name IS NULL; END IF;

END;

UNIVEM PL/SQL

Dino Spoto 24


DINO

Exemplo 2

CREATE OR REPLACE TRIGGER audit_emp_valuesAFTER DELETE OR INSERT OR UPDATE ON empFOR EACH ROW BEGIN INSERT INTO audit_emp_values ( user_name,

timestamp, empno, old_name, new_name, old_job, new_job, old_mgr, new_mgr, old_sal, new_sal)

VALUES ( user, sysdate, :old.empno, :old.ename, :new.ename, :old.job, :new.job, :old.mgr, :new.mgr, :old.sal, :new.sal);

END;


DINO

Trigger Sal_maior_gerente

create or replace trigger sal_maior_gerenteafter update or insert of sal on empfor each rowdeclarev_sal emp.sal%TYPE;

beginSELECT sal INTO v_sal from emp where empno = :new.mgr;IF (:new.sal > v_sal) THENIF UPDATING('SAL') THENRAISE_APPLICATION_ERROR (-20701, 'salario Atualizado nao

pode ser maior que o salario do gerente');ELSIF INSERTING THEN

RAISE_APPLICATION_ERROR (-20702, 'salario Inserido no pode ser superior ao salrio do gerente');

end if;end if;

end;/


DINO

Auditoria

Criar uma tabela de auditoria:CREATE TABLE AUDIT_EMP_VALUES (USER_NAME VARCHAR2(10),

TIMESTAMP DATE,EMPNO NUMBER(5),OLD_NAME VARCHAR2(20),NEW_NAME VARCHAR2(20),OLD_JOB VARCHAR2(10),NEW_JOB VARCHAR2(10),OLD_MGR NUMBER(5),NEW_MGR NUMBER(5),OLD_SAL NUMBER(12,2),NEW_SAL NUMBER(12,2));

Criar a Trigger:CREATE OR REPLACE TRIGGER audit_emp_values

AFTER DELETE OR INSERT OR UPDATE ON empFOR EACH ROW BEGIN INSERT INTO audit_emp_values ( user_name, timestamp, empno, old_name, new_name, old_job,

new_job, old_mgr, new_mgr, old_sal, new_sal) VALUES ( user, sysdate, :old.empno, :old.ename, :new.ename, :old.job, :new.job, :old.mgr, :new.mgr,

:old.sal, :new.sal); END; /


DINO

TRIGGERS ARMAZENADAS

Gerenciando triggers armazenadas dentro do banco de dados com comandos similares aos comandos para procedures.

Tarefa Comando Criar um novo trigger CREATE TRIGGER Modificar um trigger CREATE OR REPLACE

TRIGGER existente Remover um trigger DROP TRIGGER


DINO

Desabilitando Gatilhos

Diferente das procedures, pode-se desabilitar um trigger quando for conveniente.

Sintaxe: ALTER TRIGGER nome_do_trigger

DISABLE/ENABLE ou ALTER TABLE nome_da_tabela

DISABLE/ENABLE ALL TRIGGERS Quando um trigger criado, ele fica disponvel

automaticamente.


DINO

Exemplo Replicar tabs com gatilhos

CREATE OR REPLACE TRIGGER emp_replicaBEFORE INSERT OR UPDATE ON emp FOR EACH ROW BEGIN

IF INSERTING THEN IF :new.flag IS NULL THEN INSERT INTO emp@prVALUES (:new.empno, :new.ename, ..., B);

:new.flag = A; END IF;

ELSE IF :new.flag = :old.flag THEN

UPDATE emp@prSET ename = :new.ename, ... flag = :new.flag

WHERE empno = :new.empno; END IF;

IF :old.flag = A THEN :new.flag = B;

ELSE :new.flag = A;

END IF; END IF;

END;

UNIVEM PL/SQL

Dino Spoto 25


DINO

Variveis de Ligao

Variveis definidas no PL/SQL no so visveis pelo SQL*PlusBind variables -> Variveis ligadas so variveis que so criadas no SQL*Plus e podem ser referidas no PL/SQL.

Como criar Ex: VARIABLE ret_val NUMBER;

Para referir vars ligadas no PL/SQL usa-se (:) imediatamente antes da varivel

Ex: :ret_val :=1;Para mudar o valor desta varivel no SQL*Plus necessrio escrever um bloco PL/SQL.

Ex: SQL> begin2 :ret_val:=4;3 end;4 /


DINO

Exemplo em SQL/Plus

SQL> VARIABLE g_sal_mensal NUMBERSQL> ACCEPT p_sal_anual PROMPT Salrio anual: SQL> DECLARESQL> v_sal NUMBER(9,2) := &p_sal_anual;SQL> BEGINSQL> :g_sal_mensal := v_sal / 12;SQL> END;SQL> /SQL> PRINT g_sal_mensalSQL> /* ou: SET SERVEROUTPUT ONSQL> DBMS_OUTPUT.PUT_LINE(Salrio mensal de ||

TO_CHAR(v_sal));SQL> */


DINO

PL/SQL - SELECT em PL/SQL

SINTAXE: SELECT select_listINTO {variable_name[, variable_name]

| record_name}FROM tableWHERE condition;

Exemplo: DECLAREv_sum_sal emp.sal%TYPE;v_deptno NUMBER NOT NULL :=10;

BEGINSELECT SUM(sal) --group functionINTO v_num_salFROM empWHERE dptno=v_deptno;

END;

erro se pergunta devolver 0 (NO_DATA_FOUND) ou mais do que 1 linha (TOO_MANY_ROWS) clusula INTO crucial


DINO

PL/SQL - INSERT/UPDATE em PL/SQL

BEGININSERT INTO emp(empno, ename, job, deptno)VALUES empno_sequence.nextval,Harding,Clerk, 10);

END;

DECLAREv_sal_increase emp.sal%TYPE := 2000;

BEGINUPDATE empSET sal:= sal + v_sal_increaseWHERE job = ANALYST;

END;


DINO

Registros

Definir um tipo RECORD para agrupar dados heterogneos TYPE nome_tipo IS RECORD (campo[, campo]...); nome_campo tipo [[NOT NULL] {:= | DEFAULT} expresso]

Pode-se herdar a definio de um registroDECLARE

registro_emp emp%ROWTYPEBEGIN

SELECT * INTO registro_emp FROM emp WHERE... Tornando desnecessrio conhecer em detalhe a tabela base


DINO

Registros encaixados

DECLARETYPE TimeRec IS RECORD (mins SMALLINT, hrs SMALLINT);TYPE MeetingRec IS RECORD (

day DATE,time_of TimeRec, -- nested recordroom_no INTEGER(4));

TYPE PartyRec IS RECORD (day DATE,time_of TimeRec, -- nested recordplace VARCHAR2(25));

seminar MeetingRec;party PartyRec;

BEGIN ...party.time_of := seminar.time_of;

END;

UNIVEM PL/SQL

Dino Spoto 26


DINO

Tabelas

Coluna de Binary integer funciona como ndice; segunda coluna (de contedo) pode ser simples ou composta

DECLARETYPE CourseList IS TABLE OF VARCHAR2(10);courses CourseList;

BEGINcourses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');courses.DELETE(3); -- delete element 3/* PL/SQL keeps a placeholder for element 3. So, the

next statement appends element 4, not element 3. */courses.EXTEND; -- append one null element/* Now element 4 exists, so the next statement does

not raise SUBSCRIPT_BEYOND_COUNT. */courses(4) := 'Engl 2005';


DINO

Tabelas

DECLARETYPE NumList IS TABLE OF NUMBER;depts NumList := NumList(10, 20, 30, 40);

BEGINdepts.DELETE(3); -- apaga terceiro elementoFORALL i IN depts.FIRST..depts.LAST ligao em massa

DELETE FROM emp WHERE deptno = depts(i); -- causa erroEND;

Mtodos para tabelas Exists First Prior Extend Delete Count Last Next Trim


DINO

PL/SQL - Cursores

Um cursor uma rea privada do SQL. H dois tipos de cursores:

Implcitos (chamados SQL) Explcitos

O servidor Oracle usa cursores implcitos para analisar e executar cdigo SQL.

Os cursores explcitos so explicitamente declarados pelo programador.


DINO

Atributos do cursor SQL

Atravs dos atributos do cursor implcito (chamado SQL) podemos testar a sada produzida por uma pergunta SQL: SQL%ROWCOUNT - n de linhas afetadas pela mais recente

frase SQL; SQL%FOUND - TRUE se a mais recente frase SQL afeta uma

ou mais linhas; SQL%NOTFOUND - TRUE se a mais recente frase SQL no

afeta nenhuma linha; SQL%ISOPEN - Sempre FALSE porque o PL/SQL fecha

sempre os cursores implcitos depois de os executar;


DINO

Controle de cursores explcitos

cria uma rea no SQL

Declare Open Fetch CloseEmpty?

yes

no

identifica conjunto activo

testa fim de dados

carrega linha corrente liberta conjunto

ativo


DINO

Cursores - declarao

CURSOR cursor_name [(parameter[, parameter]...)][RETURN return_type] IS select_statement;

cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]

DECLARECURSOR c1 IS SELECT empno, ename, job, sal FROM emp

WHERE sal > 2000; CURSOR c2 RETURN dept%ROWTYPE IS

SELECT * FROM dept WHERE deptno = 10;

sem clusula INTO

UNIVEM PL/SQL

Dino Spoto 27


DINO

Cursor num Package

CREATE PACKAGE BODY emp_stuff ASCURSOR c1 RETURN emp%ROWTYPE IS

SELECT * FROM emp WHERE deptno = 20; --new WHERE clause

...

END emp_stuff;


DINO

Processamento das linhas

DECLAREemp_rec emp%ROWTYPE;

-- ou emp_rec emp_stuff.c1%ROWTYPE...

BEGIN...

OPEN emp_stuff.c1;LOOP

FETCH emp_stuff.c1 INTO emp_rec;EXIT WHEN emp_suff.c1%NOTFOUND;...

END LOOP;CLOSE emp_stuff.c1;

END;


DINO

Atributos dos cursores explcitos

Atravs dos atributos dos cursores explcitos podemos controlar o processamento do resultado: SQL%ROWCOUNT - n de linhas devolvidas at ao momento; SQL%FOUND - TRUE se o fetch mais recente devolveu linha; SQL%NOTFOUND - TRUE se o fetch mais recente no

devolveu linha; SQL%ISOPEN TRUE se o cursor estiver aberto

Convm fechar o cursor; mximo de cursores abertos (50) no parmetro da BD OPEN_CURSORS


DINO

Cursor de ciclo FOR

DECLAREresult temp.col1%TYPE;CURSOR c1 IS

SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;BEGIN

FOR c1_rec IN c1 LOOP/* calcula e armazena os resultados */result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);INSERT INTO temp VALUES (result, NULL, NULL);

END LOOP;COMMIT;

END;


DINO

Cursor de sub-pergunta

DECLAREbonus REAL;

BEGINFOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP

bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);INSERT INTO bonuses VALUES (emp_rec.empno, bonus);

END LOOP;COMMIT;

END;

Neste caso no se pode usar os atributos de cursor porque o cursor no tem nome


DINO

F I M

Prof. Edmundo Spoto UNIVASF - [email protected]

Documents

Aula_pl-sql