TREINAMENTO PLSQL

__________________________________________________________________________________________

ÍNDICE

ASSUNTO: PAG

1. CONCEITO.........................................................................................................................81

2. ESTRUTURA......................................................................................................................81

3. ARQUITETURA..................................................................................................................82

4. COMPONENTES DA LINGUAGEM...................................................................................82IDENTIFICADORES................................................................................................................82PALAVRAS RESERVADAS...................................................................................................82LITERAIS................................................................................................................................ 82COMENTÁRIOS:..................................................................................................................... 83

5. DECLARAÇÕES................................................................................................................83TIPOS DE VARIAVEIS............................................................................................................83

VARIÁVEIS SIMPLES................................................................................................83ATRIBUTOS ESPECIAIS...........................................................................................84PROBLEMA PROPOSTO...........................................................................................85

RESOLUÇÃO 1:...........................................................................................................85RESOLUÇÃO 2............................................................................................................86

VARIÁVEIS COMPOSTAS.........................................................................................86PROBLEMA PROPOSTO...........................................................................................88

RESOLUÇÃO:..............................................................................................................88CONSTANTES...........................................................................................................88

CONVERSÃO DE TIPO DE VARIÁVEL.................................................................................89EXEMPLOS DE DECLARAÇÕES..........................................................................................89

VARIÁVEIS ESPECIAIS.............................................................................................89ATRIBUTOS PARA CURSOR....................................................................................90PROBLEMA PROPOSTO...........................................................................................91

RESOLUÇÃO:..............................................................................................................91

6. ATRIBUIÇÕES...................................................................................................................92

7. FUNÇÕES PRÉ-DEFINIDAS.............................................................................................92CONTROLE DE ERROS.........................................................................................................92

SQLCODE..................................................................................................................92SQLERRM.................................................................................................................. 92

NUMÉRICAS........................................................................................................................... 93ABS............................................................................................................................ 93CEIL............................................................................................................................ 93COS............................................................................................................................ 93COSH......................................................................................................................... 93EXP............................................................................................................................ 93FLOOR....................................................................................................................... 93LN............................................................................................................................... 93LOG............................................................................................................................ 94MOD........................................................................................................................... 94POWER...................................................................................................................... 94ROUND....................................................................................................................... 94SIGN........................................................................................................................... 94SIN.............................................................................................................................. 94SINH........................................................................................................................... 94SQRT.......................................................................................................................... 94TAN............................................................................................................................ 95

__________________________________________________________________________________________

__________________________________________________________________________________________

TANH.......................................................................................................................... 95TRUNC....................................................................................................................... 95

CARACTERES........................................................................................................................ 95ASCII.......................................................................................................................... 95CHR............................................................................................................................ 95CONCAT..................................................................................................................... 95INITCAP...................................................................................................................... 95INSTR......................................................................................................................... 95LENGTH..................................................................................................................... 96LOWER....................................................................................................................... 96LPAD.......................................................................................................................... 96LTRIM......................................................................................................................... 96REPLACE...................................................................................................................96RPAD.......................................................................................................................... 96RTRIM........................................................................................................................ 96SOUNDEX.................................................................................................................. 97SUBSTR..................................................................................................................... 97TRANSLATE...............................................................................................................97UPPER....................................................................................................................... 97

DATAS.................................................................................................................................... 97ADD_MONTHS...........................................................................................................97LAST_DAY.................................................................................................................97MONTHS_BETWEEN.................................................................................................97NEW_TIME.................................................................................................................98NEXT_DAY.................................................................................................................98SYSDATE................................................................................................................... 98ROUND....................................................................................................................... 98TRUNC....................................................................................................................... 98

CONVERSÃO......................................................................................................................... 99CHARTOROWID........................................................................................................99HEXTORAW...............................................................................................................99RAWTOHEX...............................................................................................................99ROWIDTOCHAR........................................................................................................99TO_CHAR...................................................................................................................99

MISCELÂNEA....................................................................................................................... 100DECLARE................................................................................................................. 100GREATEST..............................................................................................................101LEAST...................................................................................................................... 101NVL........................................................................................................................... 101UID........................................................................................................................... 101USER........................................................................................................................ 101USERENV................................................................................................................101VSIZE....................................................................................................................... 101PROBLEMA PROPOSTO:........................................................................................102

RESOLUÇÃO.............................................................................................................102

8. COMANDOS.....................................................................................................................103LABELS................................................................................................................................ 103EXIT....................................................................................................................................... 103IF THEN ELSE...................................................................................................................... 104LOOP.................................................................................................................................... 105

PROBLEMA PROPOSTO:........................................................................................106RESOLUÇÃO.............................................................................................................106

WHILE LOOP........................................................................................................................ 107PROBLEMA PROPOSTO:........................................................................................107

RESOLUÇÃO:............................................................................................................107FOR LOOP............................................................................................................................ 108GOTO.................................................................................................................................... 109NULL..................................................................................................................................... 109

__________________________________________________________________________________________

__________________________________________________________________________________________

PROBLEMA PROPOSTO:........................................................................................109RESOLUÇÃO:............................................................................................................110

9. COMUNICAÇÃO COM O SQL.........................................................................................110OPEN CURSOR.................................................................................................................... 110CURSOR LOOP.................................................................................................................... 111


10. TRATAMENTO DE ERROS...........................................................................................112EXCEÇÕES PRÉ-DEFINIDAS..............................................................................................113


EXCEÇÕES DEFINIDAS PELOS USUÁRIOS.....................................................................115RAISE....................................................................................................................... 115PRAGMA EXCEPTION_INIT....................................................................................115RAISE_APPLICATION_ERROR..............................................................................116PROPAGAÇÃO DA EXCEÇÃO................................................................................116SQLERRM................................................................................................................117

11. SUBPROGRAMAS.........................................................................................................117PROCEDURES..................................................................................................................... 118DECLARAÇÕES FORWARD...............................................................................................119STORED SUBPROGRAMS..................................................................................................120

CHAMADAS DE UM STORED PROGRAM..............................................................120CRIANDO UM STORED SUBPROGRAM................................................................120

12. DATABASE TRIGGERS................................................................................................121

13. PACKAGES....................................................................................................................123CRIANDO UM PACKAGE....................................................................................................123VANTAGENS DO USO DE PACKAGES..............................................................................124REFERÊNCIAS A PACKAGES............................................................................................125

14. MENSAGENS EM STORED PROCEDURES E TRIGGERS.........................................125EXEMPLO............................................................................................................................. 125PROCEDURES E FUNÇÕES...............................................................................................125

DISABLE PROCEDURE...........................................................................................125ENABLE PROCEDURE............................................................................................126GET_LINE PROCEDURE.........................................................................................126GET_LINES PROCEDURE......................................................................................126NEW_LINE PROCEDURE........................................................................................126PUT PROCEDURE...................................................................................................127PUT_LINE.................................................................................................................127EXEMPLO................................................................................................................127

__________________________________________________________________________________________

__________________________________________________________________________________________

GUIA DE REFERENCIA DE PL/SQL COM BASE ORACLE

1. CONCEITO

A PL/SQL é uma linguagem procedural do ORACLE, extensão ao SQL. Este capítulo da apostila tem o objetivo de dar uma visão geral da linguagem e suas potencialidades. Através de exemplos simples veremos a aplicabilidade da linguagem. No fim do capítulo estão transcritos os principais comandos e sua sintaxe. Com a PL/SQL pode-se usar comandos SQL para manipular os dados da base ORACLE e fluxos de controle para processar os dados. Pode-se declarar constantes e variáveis, definir subprogramas (procedures ou funções) e controlar erros de execução.

2. ESTRUTURA

A PL/SQL é uma estrutura em blocos, ou seja, as unidades básicas (procedures, funções, etc.) podem conter qualquer número de sub-blocos. Um bloco permite que se façam declarações associadas ao bloco e que deixam de existir quando o bloco termina.

Cada bloco é composto basicamente de 3 partes:

DECLARE<declarações> ______________ opcional

BEGIN<lógica> ___________________ obrigatóriaEXCEPTION

<erros> _____________ opcional

END;

A ordem das partes é lógica, ou seja, primeiro deve-se efetuar as declarações, para depois utilizar (na lógica) as variáveis criadas. As exceções ocorridas durante a execução podem ser resolvidas na parte referente a erros.

Exemplo:

DECLAREw_salario NUMBER(5);

BEGINSELECT vl_sal INTO w_salario FROM funcWHERE cd_mat = 150FOR UPDATE OF;

IF w_salario < 800THEN

UPDATE funcSET salario = w_salario * 1.3

WHERE cd_mat = 150;ELSE

UPDATE funcSET vl_sal = w_salario * 1.15

WHERE cd_mat = 150;END IF;

COMMIT;END;

__________________________________________________________________________________________

81

__________________________________________________________________________________________

3. ARQUITETURA

O módulo executável da PL/SQL pode ser instalado no RDBMS ou em softwares aplicativos tais como: SQL*REPORT, SQL*FORMS, SQL*MENU. Estes dois ambientes (RDBMS e aplicativos) são independentes. No ambiente instalado, a PL/SQL aceita como entrada qualquer bloco ou subprograma escrito na linguagem. Quando a instalação é feita no RDBMS, é possível a inclusão de rotinas em PL/SQL nos aplicativos das host languages. É possível ainda, a compilação separada de subprogramas e armazenamento na base de dados ORACLE, que serão lidos a tempo de execução. Um Subprograma explicitamente criado usando uma ferramenta do ORACLE é chamado de “stored subprogram”, que uma vez compilado e armazenado no dicionário de dados, poderá ser chamado por qualquer número de aplicações conectadas à base de dados.

Ex.: no SQL *PLUS

SQL> execute teste (‘DILSON’)

4. COMPONENTES DA LINGUAGEM

IDENTIFICADORES

Consiste de uma letra opcionalmente seguida de números, “$”, ”_” ou “#”. As letras podem ser minúsculas ou maiúsculas.

Ex.: Legais Ilegaismoney$$$tree teste&testeab### cd_deptonovo_teste_ nmfunc

PALAVRAS RESERVADAS

Palavras que possuam um significado especial para a SQL.

Ex.: BEGINEND

LITERAIS

É uma representação explícita de um número, caracter, string ou boleano, não representado por um identificador.Ex.:

Numéricos Caracteres Strings Boleanos030 ‘Z’ ‘10-NOV-91’ TRUE6 ‘%’ ‘hello,world’ FALSE-14 ‘7’+32767 ‘.‘12.0 ‘.‘.5 ‘z’‘2E5 ‘(‘-9.5E-3

__________________________________________________________________________________________

82

__________________________________________________________________________________________

COMENTÁRIOS:O início do comentário é marcado por dois hífens em qualquer ponto da linha. O restante da linha é considerado comentário. Para comentários que ultrapassem uma linha, pode-se usar a notação /* (inicio) e */(fim).

Ex.:SELECT vl_sal INTO w_salario – obtêm o salário atual

/* calculo da bonificação */IF w_salario > 50000

5. DECLARAÇÕES

TIPOS DE VARIAVEIS

Cada constante ou variável possui um tipo que especifica o formato de armazenamento, restrições e intervalo de valores. O dado pode ser simples ou composto.

SUBTIPOS – São associados aos tipos com uma restrição, que indiquem um subconjunto de valores.

VARIÁVEIS SIMPLES

BINARY INTEGER numérico, para armazenamento de valores inteiros de -2**31 a (2**31) - 1

sintaxe: binary_integer

subtipos: natural -de 0 a (2**31) -1positive -de 1 a (2**31) -1

NUMBER numérico, para armazenamento de valores em ponto flutuante com precisão de até 38 dígitos.

sintaxe: number [ (<precisão>,<escala>) ]

subtipos: dec idem a numberdecimal idem a numberdouble precision idem a numberfloat idem a numberinteger idem a numberint idem a numbernumeric idem a numberreal idem a numbersmallint idem a number

CHAR alfanumérico de tamanho fixo com até 32767 caracteres.

sintaxe: char [ (<comprimento>) ]

subtipo: string idem a char

LONG alfanumérico de tamanho variável com comprimento de até 32760.

sintaxe: long

__________________________________________________________________________________________

83

__________________________________________________________________________________________

VARCHAR2 alfanumérico de tamanho variável com comprimento de até 32767.

sintaxe: varchar2 [(<comprimento>) ]

subtipo: varchar idem a varchar2

RAW para armazenamento de dados binários (tam.máx. até 32767).

sintaxe: raw (<comprimento>)

LONG RAW para armazenamento de dados binários até o tamanho de 32760.

sintaxe: long raw

BOOLEAN valores boleanos (true, false, ou null).

DATE armazenamento de datas.

sintaxe: date

ROWID valores de “rowid”do Oracle (em hexadecimal).

sintaxe: rowid

formato: BBBBBBBBB.RRRR.FFFFonde:BBBBBBBBB - bloco dentro do arquivo (database file)RRRR - row dentro do bloco ( primeira row é 0)FFFF - número do arquivo (database file)

ATRIBUTOS ESPECIAIS

As variáveis PL/SQL e constantes possuem atributos, que são propriedades que permitem a referência ao tipo e estrutura do objeto sem necessidade de repetição de sua definição. As tabelas e colunas do database possuem atributos similares, que podemos usar para facilitar a manutenção.

%TYPE este atributo copia os atributos de uma variável, constante ou coluna do database. É particularmente usado quando declaramos variáveis que pertençam as colunas do database.

sintaxe: <variável>/<constante>/<coluna>%TYPE

Ex.:

DECLAREW_CODIGO NUMBER(3)W_CODIGO2 W_CODIGO%TYPE; - variável de tipo idêntico a códigoW_COD_DEP DEPTO.CD_DEPTO%TYPE

/* variável de tipo idêntico à variável da base de dados cd_depto */

__________________________________________________________________________________________

84

__________________________________________________________________________________________

%ROWTYPE este atributo gera um tipo de registro que representa uma linha da tabela. O registro pode armazenar uma linha de dados selecionados da tabela ou recebidos de um cursor (fetched).

sintaxe: <tabela>/<cursor>%ROWTYPE

Ex.:

DECLAREW_DEP_ROW DEPTO%ROWTYPE; - variável do tipo rowW_MAT NUMBER(3);CURSOR W_C1 Ls - cursor com apenas 2 colunasSELECT CD_DEPTO,NM_DEPTOFROM DEPTO; - colunas da tabelaW_C1_ROW WC1%ROWTYPE; - possui as mesmas colunas de

W_C1

BEGIN

SELECT * INTO W_DEP_ROW - contém todos os dados lidos FROM FROM depto - da linha da tabelaWHERE cd_mat = W_MAT;IF W_DEP_ROW.CD_DEPTO = ‘Á00’ - a referência a cada campo é THEN

- feita com o uso da variável de tipo ROWTYPE

PROBLEMA PROPOSTOSuponhamos a existência de uma tabela (RESULTADO) com o layout (VALOR N (30), ORDEM N(5)). Deseja-se preenchê-la a partir da tabela funcionário, com a matrícula do funcionário (ordem) e a expressão trunc(nr_git*vl_sal/nr_cargo) na coluna valor.

RESOLUÇÃO 1:

DECLAREw_reg_func FUNC%ROWTYPE;CURSOR w_c1 lS

SELECT * FROM func ORDER BY cd_mat;BEGIN

OPEN w_c1;LOOP

FETCH w_c1 INTO w_reg_func;EXIT WHEN w_c1%NOTFOUND;INSERT INTO resultado (ordem,valor)VALUES (w_reg_func.cd_mat, trunc(w_reg_func.nr_git * w_reg_func.vl_sal / w_reg_func.nr_cargo));

ENDLOOP;CLOSE w_c1;

END;

__________________________________________________________________________________________

85

__________________________________________________________________________________________

RESOLUÇÃO 2

DECLARECURSOR w_c1 lS

SELECT cd_mat, nr_git, vl_sal, nr_cargo FROM func;w_reg_func w_c1%ROWTYPE;

BEGINOPEN w_c1;LOOP

FETCH w_c1 INTO w_reg_func;EXIT WHEN w_c1%NOTFOUND;INSERT INTO resultado (ordem,valor)VALUES (w_reg_func.cd_mat, trunc(w_reg_func.nr_git * w_reg_func.vl_sal / w_reg_func.nr_cargo));

END LOOP;CLOSE w_c1;

END

VARIÁVEIS COMPOSTAS

TABLE - tabelas na PL/SQL podem ter somente uma coluna e uma primary key, nenhuma das quais nomeadas. A declaração cria um tipo de variável. Deve ser feita uma declaração de variável com o tipo criado posteriormente para uso.

sintaxe: TYPE <nome> IS TABLE OF{<tipo coluna> / <tabela>.<coluna>%TYPE}[NOT NULLl] INDEX BY BINARY_INETGER

Ex.:

DECLARETYPE tab IS TABLE OF char(10)

INDEX BY BINARY_INTEGER;teste TAB;

teste (1) := ’1234567890’;teste (-1) := ‘-1234567890’;

RECORD - Permite a criação de um item estruturado.

sintaxe: TYPE <nome> IS RECORD(<campo1>{<tipo campo>/<tabela>.<coluna>%TYPE}[NOT NULL], <campo2>{<tipo campo>/<tabela>.<coluna>%TYPE}[NOT NULL] ...)

__________________________________________________________________________________________

86

__________________________________________________________________________________________

Ex.:

DECLARETYPE registro IS RECORD(depto CHAR(03) NOT NULL,nome DEPTO. NM_DEPTO%TYPE);

outro_reg REGISTRO;reg_teste REGISTRO;

...........

REG_TESTE.DEPTO := ‘A00’;SELECT cd_depto, nm_depto INTO outro_reg FROM DEPTOWHERE cd_depto = ‘D11’;

Ex.:

DECLARETYPE tab is table of CHAR(2) INDEX BY BINARY_INTEGER;TYPE registro IS RECORD (a1 TAB, a2 CHAR(1));

teste REGISTRO;

BEGINteste.a1 (1) := ‘aa’;teste.a1 (2) := ‘bb’;teste.a1 (3) := ‘cc’;

__________________________________________________________________________________________

87

__________________________________________________________________________________________

PROBLEMA PROPOSTODeseja-se saber, para cada cargo, a quantidade de funcionários por departamento. O nome do cargo deve ser apresentado no resultado.

RESOLUÇÃO:

DECLARETYPE t_cargo IS TABLE OF char(25)

INDEX BY BINARY_INTEGER;TYPE t_codigo IS TABLE OF number(3)

INDEX BY BINARY_INTEGER;cargo T_CARGO;cod_cargo T_CODIGO;CURSOR w_c1 IS

SELECT func.cd_depto, nr_cargo, nm_depto, count(*) contaFROM func, deptoWHERE func.cd_depto = depto.cd_deptoGROUP BY func.cd_depto, nm_depto, nr_cargo;

w_c1_row w_c1%ROWTYPE;BEGIN

cod_cargo(1) := 42;cargo(1) := ‘OPERADOR’cod_cargo(2) := 43;cargo(2) := ‘DIGITADOR’...OPEN w_c1;LOOP

FETCH w_c1 INTO w_c1_row;EXIT WHEN w_c1%NOTFOUND;FOR i IN 1..12 LOOP

IF cod_cargo(i) =w_c1_row.nr_cargoTHEN

INSERT INTO saida(valor, texto, qtd)VALUES(w_c1_row.cd_depto, to_char(w_c1_row.nr_cargo)ll’ ‘llcargo(i),w_c1_row.conta);EXIT;

END IF;END LOOP;

END LOOP;END;

CONSTANTES

A declaração de uma constante é semelhante à declaração de uma variável, exceto que devemos adicionar a palavra chave CONSTANT e, imediatamente, associar um valor inicial. Seu valor não poderá ser alterado durante o programa.

sintaxe: <nome da constante> CONSTANTE<tipo> :=/DEFAULT <valor inicial>;

Ex.:

DECLARE W_TESTE CONSTANT REAL := 3.14159;

W_TESTE1 CONSTANT REAL DEFAULT := 3.14159;

__________________________________________________________________________________________

88

__________________________________________________________________________________________

CONVERSÃO DE TIPO DE VARIÁVEL

Pode-se converter de um tipo de variável para outro explicitamente, porém, em muitas situações a PL/SQL pode converter o tipo de caracter em outro, implicitamente. Isto ocorre quando usamos variável de um tipo onde era esperado outro. A PL/SQL utiliza uma das seguintes funções para executar esta conversão:

TO_CHAR TO_DATETO_VARCHAR2 TO_NUMBERCHARTOROWID ROWIDTOCHARHEXTORAW RAWTOHEXTO_BINARY_INTEGER

EXEMPLOS DE DECLARAÇÕES

DECLARE W_DATA DATE; - variável de tipo data W_CONTADOR SMALLINT := 0; - variável iniciada com zero W_CODIGO CHAR(O3)NOT NULL := ‘A00’ - variável c/ restrição W_TESTE CONSTANT REAL := 3.14159; - /* constante de tipo real - o valor

inicial é obrigatório */ W_CODIGO2 W_CODIGO%TYPE := ‘B01’ - variável de tipo idêntico a código W_COD_DEP DEPTO.CD_DEPTO%TYPE - variável de tipo idêntico a variável da

da base de dados cd_depto W_DEP_ROW DEPTO%ROWTYPE - variável tipo row

CURSOR W_C1 IS - cursor c/ 2 colunas da tabela SELECT cd_depto, nm_depto FROM DEPTO; W_C1_ROW W_C1%ROWTYPE - possui as mesmas colunas de w_c1

BEGIN SELECT * INTO W_DEP_ROW FROM DEPTO WHERE cd_depto = ‘A00’; IF W_DEP_ROW.cd_DEPTO = ‘A00’ THEN W_CONTADOR := WHERE_CONTADOR + 1; END IF;END

VARIÁVEIS ESPECIAIS

EXCEPTION - nomeia uma exceção definida pelo usuário.

sintaxe: <nome da exceção> EXCEPTION

Ex.:

DECLARE erro_matrícula EXCEPTION;

CURSOR - declara um cursor.

__________________________________________________________________________________________

89

__________________________________________________________________________________________

sintaxe:CURSOR <nome cursor>[(<parâmetro> <tipo>[,<parâmetro <tipo>...])]RETURN<tipo>/<variável%TYPE/<table.column>%TYPE/<table>%rowtype

IS <comando SELECT >;

onde:1. A cláusula RETURN define o tipo de dado do resultado de um cursor.2. Pode-se usar o atributo %ROWTYPE para representar uma linha em uma tabela da base.3. Pode-se usar %TYPE para representar o tipo de uma variável, constante ou coluna da

base.4. Um cursor deve estar associado a um comando SELECT com mesmo número, tipo e

ordem de elementos selecionados que os da cláusula RETURN.Ex.:

DECLARE CURSOR w_c1 IS SELECT cd_mat, vl_sal FROM func;CURSOR w_c2 (dat_ini DATE) IS

SELECT cd_mat, nm_func FROM funcWHERE de_nasc > dat_ini;

ATRIBUTOS PARA CURSOR

Existem 2 tipos de cursores em PL/SQL: implícito e explícito. A PL/SQL implicitamente declara um cursor para cada comando SQL que manipule dados, inclusive queries que retornem uma única row.

%FOUND - indica se o último FETCH retornou uma linha ou não, para cursores explícitos. E se alguma row foi afetada pelo último comando INSERT, UPDATE ou DELETE para cursores implícitos.

sintaxe: <cursor> %FOUNDSQL%FOUND

Ex.:

LOOP FETCH w_c1 INTO w_c1_row;IF w_c1%FOUNDTHEN

%NOTFOUND - indica se o último FETCH retornou uma row ou não, para cursores explícitos. E se alguma row foi afetada pelo último comando INSERT, UPDATE ou DELETE para cursores implícitos.

sintaxe: <cursor>%NOTFOUNDSQL%NOTFOUND

Ex.:

LOOP FETCH w_c1 INTO w_c1_row;IF w_c1%NOTFOUNDTHEN

EXIT;...

END LOOP;

__________________________________________________________________________________________

90

__________________________________________________________________________________________

%ISOPEN - permite que se verifique se um cursor está aberto ou não. No caso de cursores implícitos o resultado será sempre FALSE, uma vez que o Oracle fecha o cursor após uma operação.

sintaxe: <cursor>%ISOPENSQL%ISOPEN

Ex.:

IF NOT (w_c1%ISOPEN)THEN...

%ROWCOUNT - indica o número de rows lidas para o cursor associado (para cursores explícitos) ou o número de rows afetadas no último comando INSERT, UPDATE, DELETE ou SELECT (para cursores implícitos). Após a abertura do cursor, o valor de ROWCOUNT é zero. O número só será incrementado SE O ÚLTIMO FETCH retornou uma row.

sintaxe: <cursor>%ROWCOUNTSQL %ROWCOUNT

Ex.:

LOOP FETCH w_c1 INTO w_c1_row;IF w_c1% ROWCOUNTTHEN

...

PROBLEMA PROPOSTO Deseja-se calcular e emitir a folha de pagamento de uma empresa. Em função da necessidade de se emitir vários relatórios sobre o cálculo efetuado, será gerada uma tabela intermediária com os resultados para posterior impressão.

RESOLUÇÃO:

DECLARE CURSOR w_c1 IS SELECT cd_mat, vl_sal, vl_sal * .10 fgts FROM func;w_c1_row w_c1 %ROWTYPE;w_vl_lim NUMBER(15,2) :=&1;w_vl_ir NUMBER(15,2) := 0;w_vl_inss NUMBER(15,2) := 0tabela inválida EXCEPTION;PROCEDURE ir (val_sal IN NUMBER, val_ir OUT NUMBER) IS BEGIN

IF val_sal < 300.00 THENval_ir := 0;ELSIF val_sal BETWEEN 300.01 AND 1000.00 THEN

val_ir := val_sal *10;ELSIF val_sal BETWEEN 1000.01 AND 3000.00 THEN

val_ir := val_sal *20;ELSE

val_ir := val_sal *30;END IF;

END;FUNCTION inss (val_sal IN NUMBER, val_lim IN NUMBER) RETURN NUMBERISval_inss NUMNER(15,2);

__________________________________________________________________________________________

91

__________________________________________________________________________________________

BEGIN val_inss := val_sal *.08;IF val_inss := > val_lim THEN

val_inss := val_lim;END IF;RETURN val_inss;

END inss;

BEGIN DELETE FROM folha;COMMIT;OPEN w_c1;LOOP

FETCH w_c1 INTO w_c1_row;EXIT WHEN w_c1%NOTFOUND;ir(w_c1_row.vl_sal, w_vl_ir);w_vl_inss := inss(w_c1_row.vl_sal,w_vl_lim);INSERT INTO folha (cd_mat, vl_sal, vl_ir, vl_inss, vl_fgts)VALUES (w_c1_row.cd_mat, w_c1_row.vl_sal, w_vl_ir, w_vl_inss,w_c1_row.fgts);

END LOOP;CLOSE w_c1;COMMIT;END;

6. ATRIBUIÇÕES

As variáveis e constantes são inicializadas cada vez que é iniciado o bloco em que elas estão declaradas. Por default, as variáveis são inicializadas com “NULL”. É importante, portanto que as variáveis antes de serem usadas sejam inicializadas.

Ex.:sim_não boolean;...sim_não := (contador > 500);

IF sim_não IS NULL - verifica se uma variável tem o valor = NULLTHEN

7. FUNÇÕES PRÉ-DEFINIDAS

A PL/SQL permite a utilização de diversas funções pré-definidas para manipulação dos dados. Pode-se usá-las onde expressões do mesmo tipo são permitidas.

CONTROLE DE ERROS

SQLCODEFunção numérica que retorna o código do erro associado à última exceção.

Definição: FUNCTION SQLCODE RETURN NUMBEREx.: <variável> := SQLCODE;

SQLERRMFunção string que retorna a mensagem de erro associado ao último SQLCODE.

__________________________________________________________________________________________

92

__________________________________________________________________________________________

Definição: FUNCTION SQLERRM [(error_number NUMBER)] RETURN CHAREx.: <variável> := SQLERRM(-1023);

NUMÉRICAS

ABSRetorna o valor absoluto do argumento.

Definição: FUNCTION ABS (<n> NUMBER) RETURN NUMBEREx.: <variável> := ABS (<variável2>);

CEILRetorna o menor inteiro maior ou igual ao argumento.

Definição: FUNCTION CEIL(<n> NUMBER) RETURN NUMBEREx.: <variável> := CEIL (<variável2>);

COSRetorna o coseno do argumento, que deve ser expresso em radianos.

Definição: FUNCTION COS (<n> NUMBER) RETURN NUMBEREx.: <variável> := COS (<variável2>);

Obs.: se <n> estiver em graus, basta que seja dividido por 57.29578 para ser convertido para radianos.

COSHRetorna o coseno hiperbólico do argumento.

Definição: FUNCTION COSH (<n> NUMBER) RETURN NUMBEREx.: <variável> := ABS (<variável2>);

EXPRetorna e elevado à n-esima potência, onde e (~2.71828) é a base do logaritmo neperiano.

Definição: FUNCTION EXP (<n> NUMBER) RETURN NUMBEREx.: <variável> := EXP (<variável2>);

FLOORRetorna o maior inteiro menor ou igual ao argumento.

Definição: FUNCTION FLOOR(<n> NUMBER) RETURN NUMBEREx.: <variável> := FLOOR (<variável2>);

LNRetorna o logaritmo natural do argumento, que deve ser maior que zero. Definição: FUNCTION LN (<n> NUMBER) RETURN NUMBEREx.: <variável> := LN (<variável2>);

__________________________________________________________________________________________

93

__________________________________________________________________________________________

LOGRetorna o logaritmo de <n> na base <m>, sendo que <m> deve ser maior que 1 e <n> deve ser maior que 1 e <n> deve ser maior que zero.

Definição: FUNCTION LOG (<n> NUMBER) RETURN NUMBEREx.: <variável> := LOG (<variável2>);

MODRetorna o resto da divisão de <m> por <n>. Se <n> for zero, <m> é retornado.

Definição: FUNCTION MOD (<n> NUMBER) RETURN NUMBEREx.: <variável> := MOD (<variável2>), (<variável3>);

POWER

Retorna o número <m> elevado à <n>-ésima potência. Se <m> for negativo, <n> deve ser inteiro.

Definição: FUNCTION POWER (<n> NUMBER) RETURN NUMBEREx.: <variável> := POWER (<variável2>);

ROUNDRetorna <m> arredondado para <n> casas decimais. Se <n> for omitido, zero será assumido.

Definição: FUNCTION ROUND (<n> NUMBER) RETURN NUMBEREx.: <variável> := ROUND (<variável2>), (<variável3>);

SIGNRetorna –1 se o argumento for negativo, 0 se igual a zero ou 1 se o argumento for maior que zero.

Definição: FUNCTION SIGN (<n> NUMBER) RETURN NUMBEREx.: <variável> := SIGN (<variável2>);

SINRetorna o seno de <n>, que deve ser expresso em radianos.

Definição: FUNCTION EXP (<n> NUMBER) RETURN NUMBEREx.: <variável> := SIN (<variável2>);

Obs.: Se <n> estiver em graus, basta que seja dividido por 57.29578 para ser convertido para radianos.

SINHRetorna o seno hiperbólico do argumento.

Definição: FUNCTION SINH (<n> NUMBER) RETURN NUMBEREx.: <variável> := SINH (<variável2>);

SQRTRetorna a raiz quadrada do argumento, que não pode ser negativo.

Definição: FUNCTION SQRT (<n> NUMBER) RETURN NUMBEREx.: <variável> := SQRT (<variável2>);

__________________________________________________________________________________________

94

__________________________________________________________________________________________

TANRetorna a tangente de <n>, que deve ser expresso em radianos.

Definição: FUNCTION TAN (<n> NUMBER) RETURN NUMBEREx.: <variável> := TAN (<variável2>);

TANHRetorna a tangente hiperbólica do argumento.

Definição: FUNCTION TANH (<n> NUMBER) RETURN NUMBEREx.: <variável> := TANH (<variável2>);

TRUNCRetorna o número <m> truncado para <n> casas decimais. Se o <n> for omitido, zero será assumido.

Definição: FUNCTION TRUNC (<n> NUMBER) RETURN NUMBEREx.: <variável> := TRUNC (<variável2>);

CARACTERES

ASCIIRetorna o código ASCII correspondente à string informada no argumento.

Definição: FUNCTION ASCII (<str> VARCHAR2) RETURN NUMBEREx.: <variável> := ASCII (<variável2>);

CHRRetorna a string correspondente à representação numérica informada como argumento. É o contrario da função ASCII.

Definição: FUNCTION CHR (<n> NUMBER) RETURN CHREx.: <variável> := CHR (<variável2>);

CONCATRetorna uma string que é o resultado da concatenação de <str1> (na frente) com <str2>.

Definição: FUNCTION CONCAT (<str1> VARCHAR2,<str2> VARCHAR2) RETURN VARCHAR2Ex.: <variável> := CONCAT (<variável2>,<variável3>);

INITCAPRetorna a primeira letra de cada palavra do argumento em letra maiúscula e as demais em minúsculas.

Definição: FUNCTION INITCAP (<str> VARCHAR2) RETURN VARCHAR2Ex.: <variável> := INITCAP (<variável2>);

INSTRRetorna a posição da <n>-ésima ocorrência de <str2> dentro de <str1>, começando na posição <pos>.

__________________________________________________________________________________________

95

__________________________________________________________________________________________

Definição: FUNCTION INSTR (<str1> VARCHAR2,<str2> VARCHAR2) [<pos> NUMBER [,<n> NUMBER ]]) RETURN NUMBER

Ex.: <variável> := INSTR (<variável2>, <variavel3>,<pos>,<n>);

LENGTHRetorna o número de caracteres da string <str>. Se o argumento é um item definido como CHAR, o comprimento incluirá os brancos. Se <str> for null, o resultado da função será NULL.

Definição: FUNCTION LENGTH (<str> VARCHAR2) RETURN NUMBEREx.: <variável> := LENGTH (<variável2>);

LOWERRetorna o argumento com todas as letras minúsculas.

Definição: FUNCTION LOWER (<str> VARCHAR2) RETURN VARCHAR2Ex.: <variável> := LOWER (<variável2>);

LPADCompleta à esquerda, com os caracteres <pad> para que o tamanho da string resultado seja <len>.

Definição: FUNCTION LPAD (<str> VARCHAR2,<len> NUMBER [,<pad> VARCHAR2]) RETURN

VARCHAR2Ex.: <variável> := LPAD (<variável2>, <comprimento>,’*’);

LTRIMRetira , da esquerda para direita, os caracteres <set> até que seja encontrado um caracter diferente de <set>.

Definição: FUNCTION LTRIM (<str> VARCHAR2 [, <set> VARCHAR2]) RETURN VARCHAR2Ex.: <variável> := LTRIM (<variável2>, <variável3>);

REPLACERetorna <str1> com cada ocorrência de <str2> substituída por <str3>. Se <str3> não for informado, todas as ocorrências se <str2> serão removidas. Se nem <str2> nem <str3> forem informadas a função retornará NULL.

Definição: FUNCTION REPLACE (<str1> VARCHAR2,<str2> VARCHAR2[,<str3> VARCHAR2]) RETURN VARCHAR2

Ex.: <variável> := REPLACE (<variável1>, <variável2>,<variável3);

RPADCompleta, à direita, com os caracteres <pad> para que o tamanho da string resultado seja <len>.

Definição: FUNCTION RPAD (<str> VARCHAR2,<len> NUMBER [,<pad> VARCHAR2])RETURN VARCHAR2

(<n> NUMBER) RETURN NUMBEREx.: <variável> := RPAD (<variável2>, < tamanho>,’*’);

RTRIMRetira , da direita para esquerda, os caracteres <set> até que seja encontrado um caracter diferente de <set>.

__________________________________________________________________________________________

96

__________________________________________________________________________________________

Definição: FUNCTION RTRIM (<str> VARCHAR2 [, <set> VARCHAR2]) RETURN VARCHAR2Ex.: <variável> := RTRIM (<variável2>, <variável3>);

SOUNDEXRetorna um string que represente o som de <str>.

Definição: FUNCTION SOUNDEX (<str> VARCHAR2) RETURN VARCHAR2Ex.: <variável> := SOUNDEX (<variável2>);

SUBSTRRetorna uma parte da string <str>, a partir da posição <pos> por <len> caracteres. Se l<len> for omitido, retorna o restante da string.

Definição: FUNCTION SUBSTR (<str> VARCHAR2, <pos> NUMBER [,<len> NUMBER]) RETURN VARCHAR2

Ex.: <variável> := SUBSTR (<variável2>, <posição inicial>, <tamanho>);

TRANSLATERetorna <str>, substituindo cada um dos caracteres presentes em <set1> pelo caracter correspondente em <set2>. Se <set1> tiver mais caracteres que <set2>, e esses caracteres estiverem presentes em <str>, serão removidos do resultado.

Definição: FUNCTION TRANSLATE (<str> VARCHAR2, <set1> VARCHAR2,<set3> CHAR) RETURN VARCHAR2

Ex.: <variável> := TRANSLATE (<variável2>, ‘ABCDEF’, GHIJKL’);

UPPERRetorna o argumento com todas as letras maiúsculas.

Definição: FUNCTION UPPER (<str> VARCHAR2) RETURN VARCHAR2Ex.: <variável> := UPPER (‘texto’);

DATAS

ADD_MONTHSRetorna a data <dte> adicionada de <n> meses. <n. deve ser um inteiro e pode ser negativo.

Definição: FUNCTION ADD_MONTHS (<dte> DATE, <n>NUMBER) RETURN DATEEx.: <variável> := ADD_MONTHS (v_dt_nasc,4);

LAST_DAYRetorna a data do último dia do mês de <dte>.

Definição: FUNCTION LAST_DAY (<dte> DATE) RETURN DATEEx.: <variável> := LAST_DAY (<v_dt_adm>);

MONTHS_BETWEENRetorna o número de meses entre <dte1> e <dte2>.Definição: FUNCTION MONTHS_BETWEEN (<dte1> DATE,<dte2> DATE) RETURN DATEEx.: <variável> := MONTHS_BETWEEN (sysdate, v_dt_nasc) /12;

__________________________________________________________________________________________

97

__________________________________________________________________________________________

NEW_TIMEConverte a data e hora que está no meridiano <zon1>, para a data e hora no meridiano <zon2>.

Definição: FUNCTION NEW_TIME (<dte> DATE,<zon1> VARCHAR2, <zon2> VARCHAR2) RETURN DATE

Ex.: <variável> := NEW_TIME (v_dt_nasc, ‘AST’,’GMT’);

ABREVIATURAS PARA MERIDIANOS

AST,ADT Atlantic Standard ou Daylight TimeGMT GreenwitchPST, PDT Pacific Standard ou Daylight Time

NEXT_DAYRetorna a data do primeiro dia da semana nomeado por <day> que seja posterior a <dte>.

Definição: FUNCTION NEXT_DAY (<dte> DATE,<day> VARCHAR2) RETURN DATEEx.: <variável> := NEXT_DAY (sysdate, ‘moday’);

SYSDATERetorna a data e hora correntes.

Definição: FUNCTION SYSDATE RETURN DATEEx.: <variável> := SYSDATE ;

ROUNDRetorna <dte> arredondado para o formato especificado.

Definição: FUNCTION ROUND (<dte> [,<fmt>]) RETURN DATEEx.: <variável> := ROUND (sysdate,’ww’);

TRUNCRetorna uma data no formato especificado por <fmt>, representado <dte> truncada na unidade correspondente.

Definição: FUNCTION TRUNC (<dte> DATE [, <fmt> VARCHAR2 ) RETURN DATEEx.: <variável> := TRUNC (sysdate,’ww’);

FORMATOS PARA ROUND E TRUNC

CC SéculoSYYY, YYYY, YEAR, SYEAR, YYY, YY, Y AnoQ Quarto de anoMONTH, MON, MM MêsWW Início da semana do anoWHERE Início da semana do mêsDDD, DD, J DiaDAY, DY, D Último SábadoHH, HH12, HH24 HoraMI Minuto

__________________________________________________________________________________________

98

__________________________________________________________________________________________

CONVERSÃO

CHARTOROWIDConverte a string <str> do tipo CHAR ou VARCHAR2 para ROWID.

Definição: FUNCTION CHARTOROWID (<str> CHAR) RETURN ROWIDEx.: <variável> := CHARTOROWID (‘00000000E.000ª0007’);

HEXTORAWConverte uma string hexadecimal do tipo CHAR ou VARCHAR2 para RAW.

Definição: FUNCTION HEXTORAW (<str> CHAR) RETURN RAWEx.: FUNCTION HEXTORAW (<str> VARCHAR2) RETURN RAW

<variável> := HEXTORAW (‘F6’);

RAWTOHEXConverte um valor binário em uma string hexadecimal do tipo VARCHAR2.

Definição: FUNCTION RAWTOHEX (<bin> RAW) RETURN VARCHAR2Ex.: <variável> := RAWTOHEX (<variável do tipo raw);

ROWIDTOCHARConverte o valor binário de <bin> para uma string hexadecimal de 18 bytes.

Definição: FUNCTION ROWIDTOCHAR (<bin> ROWID) RETURN VARCHAR2Ex.: <variável> := ROWIDTOCHAR (<variável do tipo rowid);

TO_CHARConverte um valor numérico ou data para o formato especificado.

Definição: FUNCTION TO_CHAR (<dte> DATE [,<fmt> VARCHAR2 [,<nls1>]])RETURN VARCHAR2FUNCTION TO_CHAR (<n> NUMBER) [,<fmt> VARCHAR2[,<NLS2>]]) RETURN VARCHAR2

Ex.: <variável> := TO_CHAR(5678.32, ‘9.999.99’, ‘nls_numeric_characters =’, ‘.’);

FORMATOS NUMÉRICOS PARA TO CHAR E TO NUMBER

9 9999 A quantidade de 9’s determina o comprimento0 0999 Completa com zeros à esquerda em vez de brancos$ $999 Prefixa o valor com o símbolo $B B999 Substitui o valor 0 por brancoMI 999MI Mostra “-“ após um valor negativoS S999 Coloca um “-“ ou um “+” antes do número.PR 999PR Mostra um valor negativo entre <>D 99D99 Inclui o caracter decimalG 99G99 Inclui o caracter separador de milhar, 99,99 Mostra uma “,” na posição correspondente.. 99.99 Mostra um “.” decimal na posição correspondente.V 999V99 Multiplica o valor por 10<n>, onde <n> corresponde ao número de

9’s após VE 9.99EEEE Notação científicaRN,rn RN Maiúscula ou minúscula para numerais romanos

__________________________________________________________________________________________

99

__________________________________________________________________________________________

FORMATOS DE NLS

‘NLS_DATE_LANGUAGE = <language>’ ________________para <nls1>‘NLS_NUMERIC_CHARACTERS = ‘ ‘<d> <g>’,NLS_CURRENCY = “<text>”NLS_ISSO_CURRENCY = “<text>” ____________ para <nls2>

Onde:

<d> caracter decimal<g> separador de milhar<text> símbolo monetário

TO_DATEConverte uma string ou um número para o formato data.

Definição: FUNCTION TO_DATE (<str> VARCHAR2[,<fmt> VARCHAR2 [,<nls1>]])RETURN DATE

Ex.: <variável> := TO_DATE (‘12/01/84’, ‘dd/mm/yy’)

FORMATOS DE DATA PARA TO_CHAR E TO_DATE

CC, SCC SéculoSYYY, YYYY, YEAR, SYEAR, YYY, YY, Y AnoQ Quarto de anoMONTH Mês por extensoMON Mês abreviado para três letrasMM Mês (numérico)WW Semana do anoWHERE Semana do mêsWHERE Início da semana do mêsDDD Dia do anoDD Dia do mêsD Dia da semanaDAY Nome do diaDY Dia abreviado p/ 3 letrasJ Dia em data JulianaHH, HH12, HH24 HoraMI MinutoSS Segundo

TO_NUMBERConverte <str> para o valor numérico correspondente.

Definição: FUNCTION TO_NUMBER (<str> VARCHAR2 [, <nls2> ]])RETURN NUMBER

Ex.: <variável> := TO_NUMBER (‘5.678,32’, ‘9.999,99’, ‘nls_numeric_characters = ‘ , ‘.’);

MISCELÂNEA

DECLARESó é permitido em comandos SQL.

__________________________________________________________________________________________

100

__________________________________________________________________________________________

GREATESTRetorna a maior <expr> da lista de valores. Todas as expressões após a primeira são convertidas para o tipo de dado da primeira antes da comparação ser feita.

Definição: FUNCTION GREATEST (<expr1>, <expr2>,...)Ex.: <variável> := GREATEST (‘aaaa’, ‘bbb’, ‘cccc’);

LEASTRetorna a menor <expr> da lista de valores. Todas as expressões após a primeira são convertidas para o tipo de dado da primeira antes da comparação ser feita.

Definição: FUNCTION LEAST (<expr1>, <expr2>,...)Ex.: <variável> := LEAST(‘aaaa’, ‘bbb’, ‘cccc’);

NVLSe <expr1> for null, retorna <expr2>. Se <expr1> não for null, retorna <expr1>.

Definição: FUNCTION NVL ((<expr1>, <expr2>,) RETURN <ret>Onde: <ret> será de tipo igual a <expr1> e <expr2>

Ex.: <variável> := NVL (v_nr_gir,0);

UIDRetorna o valor do inteiro associado a cada username pelo Oracle.

Definição: FUNCTION UID RETURN NUMBEREx.: <variável> := UID;

USERRetorna o username corrente.

Definição: FUNCTION USER RETURN VARCHAR2Ex.: <variável> := USER;

USERENVRetorna informações sobre o user e a sessão.

Definição: FUNCTION USERENV (<str> VARCHAR2) RETURN VARCHAR2Ex.: <variável> := USERENV (‘terminal’);

Valores de <str>

entryid identificador da entradasessionid Identificador da sessãoterminal identificador do terminallanguage identificador da linguagem em uso

VSIZERetorna o número de bytes usado para armazenar a representação interna de <expr>.

Definição: FUNCTION VSIZE (<exp> DATE l NUMBER l VARCHAR2) RETURN NUMBEREx.: <variável> := VSIZE (sysdate);

__________________________________________________________________________________________

101

__________________________________________________________________________________________

PROBLEMA PROPOSTO: Gravar na tabela RESULTADO a quantidade de funcionários por departamento, convertendo o código do departamento da seguinte forma:

departamentos AXX somar 100 ao número do departamento departamentos BXX somar 200 ao número do departamento e assim por diante

RESOLUÇÃO

DECLARE CURSOR w_c1 IS SELECT cd_depto,count(*) total FROM func GROUP BY cd_depto;W_c1_row w_c1%ROWTYPEw_cd_depto number(3);w_str_depto varchar(1);BEGIN

OPEN w_c1;LOOP FETCH w_c1 INTO w_c1_row; IF w_c1%NOTFOUND THEN

CLOSE w_c1;EXIT;

ENDIF; W_str_depto := SUBSTR (w_c1_row.cd_depto, 1, 1); IF w_str_depto = ‘A’THEN w_cd_depto := 100;

ELSIF w_str_depto = ‘B’ THEN w_cd_depto :=200;ELSIF w_str_depto = ‘C’ THEN w_cd_depto :=300;ELSIF w_str_depto = ‘D’ THEN w_cd_depto :=400;ELSIF w_str_depto = ‘E’ THEN w_cd_depto :=500;

END IF; w_cd_depto := w_cd_depto+ TO_NUMBER(SUBSTR(w_c1_row.cd_depto,2,2)); INSERT INTO RESULTADO (ordem, campo) VALUES

(w_cd_depto,w_c1_row.total);END LOOP;

END;

__________________________________________________________________________________________

102

__________________________________________________________________________________________

8. COMANDOS

LABELSIdentifica um comando ou conjunto de comandos. Utilizado para desvios e para qualificação.Deve existir um comando após o LABEL.

sintaxe: <<label>>

Ex.:<<externo>>FOR i IN 1..10 LOOP

IF externo.iTHEN

...

ENDIF;<<Interno>>FOR i IN 1..10 LOOP

IF inetrno iTHEN

...ENDIFEXIT externo WHEN... – encerra ambos os loops

END LOOP;END LOOP externo;<<fim>> -- este comando é ilegal porque END não éEnd; -- um comando executável

EXITEncerra um loop.

sintaxe: EXIT [ <label>] [WHEN <condição>]

Ex.:LOOP

FETCH w_c1 INTO w_c1_row;IF w_c1%FOUNDTHEN

calc_ir........INSERT

ELSEEXIT;

ENDIFEND LOOP;

__________________________________________________________________________________________

103

__________________________________________________________________________________________

IF THEN ELSEA seqüência de comandos só será executada se a condição for verdadeira.

sintaxe:IF <condição>THEN

<seqüência de comandos>END IF;

ouIF <condição>THEN

<seqüência de comandos>ELSE

<seqüência de comandos>END IF;

ouIF <condição>THEN

<seqüência de comandos>ELSIF <condição>THEN

<seqüência de comandos>ELSE

<seqüência de comandosEND IF;

<condição>[NOT] <expressão boleana> [[AND I OR ] <expressão boleana>]

<expressão boleana>]<literal boleano> I<variável boleana> I<chamada de função boleana> I(<expressão boleana>) I<expressão PLSQL> <operador relacional> <expressão PLSQL> I<expressão PLSQL> IS [NOT] NULL I<expressão PLSQL> [NOT] LIKE <pattern> I<expressão PLSQL> [NOT] BETWEEN

<expressão PLSQL> AND <expressão PLSQL> I<expressão PLSQL> [NOT] IN (<expressão PLSQL>[,

<expressão PLSQL>]) I<expressão PLSQL> { <nome cursor> I SQL}

{%NOTFOUND I %FOUND I%ISOPEN}

__________________________________________________________________________________________

104

__________________________________________________________________________________________

Ex.:IF val_sal < 300.00THEN

Val_ir := 0;ELSIF val_sal BETWEEN 300.01 AND 1000.00+THEN

val_ir := val_sal * .10;ELSEIF val_sal BETWEEN 1000.01 and 3000.00THEN

val_ir := val_sal * .20;ELSE

val_ir := val_sal * .30;END IF;

LOOPA seqüência de comandos é executada num número infinito de vezes ou até que seja encontrado um comando “EXIT” ou a condição de “WHEN” seja satisfeita.

sintaxe:

[<< <label> >>]LOOP

<sequencia de comandos>END LOOP

ou LOOP

<sequencia de comandos>IF ....THEN

EXIT; -- encerra o loopEND IF;

END LOOP;

ouLOOP

<sequencia de comandos>EXIT WHEN -- encerra o loop

END LOOP;Ex.:

OPEN w_c1;LOOP

FETCH WHERE_C1 INTO w_c1_row;EXIT WHEN w_c1%NOTFOUND;calc_ir(w_c1_row.vl_sal,w_vl_lim);

...END LOOP;CLOSE w_c1;

__________________________________________________________________________________________

105

__________________________________________________________________________________________

PROBLEMA PROPOSTO: Preencher a tabela TABDEP (nome, código, salário, média, totsal, totméd) com os valores abaixo obtidos na tabela de departamento:

nome nome do departamento código código do departamento salário somatório dos salários do departamento correspondente média média salarial do departamento correspondente totsal total de salários da tabela de funcionários totméd média da tabela de funcionários total

RESOLUÇÃO

DECLARE w_tot_sal number(10,2);w_tot_med number(10,2);CURSOR w_ c1 ISSELECT nm_depto, depto.cd_depto, SUM(vl_sal)soma, AVG(vl_sal) mediaFROM func, deptoWHERE func.cd_depto = depto.cd_deptoGROUP BY nm_depto, depto.cd_depto;w_c1_row w_c1%ROWTYPE;

BEGIN SELECT SUM(vl_sal) INTO w_tot_sal FROM func;SELECT AVG(vl_sal) INTO w_tot_med FROM func;OPEN w_c1;LOOP

FETCH w_c1 INTO w_c1_row;IF w_c1%NOTFOUNDTHEN

CLOSE w_c1EXIT;

END IF;INSERT INTO tabdep (nome, código, salário, média, totsal, totmed)VALUES (w_c1_row.nm_depto, w_c1_row.cd_depto, w_c1_row.soma,w_c1_row.media, w_tot_sal, w_tot_med);

END LOOP;END;

__________________________________________________________________________________________

106

__________________________________________________________________________________________

WHILE LOOPA seqüência de comandos é executada enquanto a condição for verdadeira. Antes de cada iteração do loop, a condição é avaliada. Se for verdadeira, a seqüência de comandos é executada.

sintaxe:

[<< <label> >>]WHILE <condição> LOOP

<seqüência de comandos>END LOOP;

Ex.:OPEN w_c1;FETCH w_c1 INTO w_c1_row;WHILE w_c1%FOUND LOOP

...INSERT INTO folha

END LOOP;CLOSE w_c1;

PROBLEMA PROPOSTO: Preencher a tabela TABDEP (nome, código, salário, média, totsal, totméd) com os valores abaixo obtidos na tabela de departamento


Obs.:Este problema é o mesmo anterior.

RESOLUÇÃO:

DECLAREw_tot_sal number(10,2);w_tot_med number(10,2);CURSOR w_c1 ISSELECT nm_depto, depto.cd_depto, SUM,(vl_sal)soma,AVG(vl_sal)mediaFROM func, deptoWHERE func.cd_depto = depto.cd_deptoGROUP BY nm_depto, depto.cd_depto;W_c1_row w_c1%ROWTYPE;

BEGINSELECT SUM(vl_sal) INTO w_tot_sal FROM func;SELECT AVG(vl_sal) INTO w_tot_med FROM func;OPEN w_c1;FETCH w_c1 INTO w_c1_row;WHILE w_c1%FOUND LOOP

INSERT INTO tabdep (nome, código, salário, média, totsal, totmed)VALUES(w_c1_row.nm_depto, w_c1_row.cd_depto, w_c1_row.soma, w_c1_row.media, w_tot_sal, w_tot_med);FETCH w_c1 INTO w_c1_row;

END LOOP;END;

__________________________________________________________________________________________

107

__________________________________________________________________________________________

FOR LOOP

A seqüência de comandos é executada um número fixo de vezes estabelecido no comando. No início do comando a quantidade de vezes que o mesmo será executado já é conhecida, uma vez que não se pode alterar o valor de <contador> durante a iteração.

sintaxe:[<< <label> >>]FOR <contador> IN [REVERSE] <inferior>..<superior> LOOP

<seqüência da comandos>END LOOP;

Ex.:

FOR i IN 1..3 LOOP — <seqüência de comandos><seqüências de comandos> — executa 3 vezes

END LOOP; — o comando vezes é inválido, pois i só Vezes := i +1 — existe no escopo do comando FOR

FOR i IN início..fim LOOP — <seqüência de comandos><seqüência de comandos> — <executada n vezes dependendo

END LOOP; — do valor de início e fim

Fim := 1;FOR i IN 3..fim LOOP — <seqüência de comados>

<seqüência de comandos> — não será executadaEND LOOP;

FOR IN REVERSE 1..3 LOOP — <seqüência de comandos><seqüência de comandos> — será executado 3 vezes

END LOOP; — i terá o valor inicial de 3

<<início>> — a variável usada em um loopFOR i IN 1..25 LOOP — é automaticamente declarada pela PL/SQL

FOR i IN 1..10 LOOP — no exemplo foram criadasIF início.i > 15 — duas variáveis iTHEN... — a referência ao nível

END LOOP; — externo é feita atravésEND LOOP INÍCIO; — do label

<<início>> — esta forma de interrupçãoFOR i IN 1..25 LOOP — do loop (exit <label> when..)

FOR i IN 1..10 LOOP — encerra os dois níveis ….. — de loopsEXIT início WHEN…

END LOOP;END LOOP início;

__________________________________________________________________________________________

108

__________________________________________________________________________________________

GOTO

Desvia incondicionalmente para um “label”, o qual deve ser dentro do scopo e deve preceder um comando ou um bloco da PL/SQL.

sintaxe: GOTO << <label> >>

Ex.:

BEGIN…

GOTO inclui; — desvio para o comando INSERT…

<<inclui>>INSERT INTO func…

END;

BEGIN…<<altera>>BEGIN

UPDATE func — o desvio pode ser para traz…

END;GOTO altera;…<<fim>> — este label é porque END

END; — não é um comando executavél

NULL

Este comando explicitamente indica que não há ação a ser feita. Serve para compor certas situações em que um comando é exigido, mas nenhuma ação é realmente necessária.

sintaxe: NULL;

Ex.:

BEGIN …<<fim>> — o comando “null” resolveNULL; — o problema anterior

END;

PROBLEMA PROPOSTO: Preencher a tabela TABDEP (nome, código, salário, média, totsal, totméd) com os valores abaixo obtidos na tabela de departamento:


Obs: Este problema é o mesmo anterior.

__________________________________________________________________________________________

109

__________________________________________________________________________________________

RESOLUÇÃO:

DECLAREw_tot_sal number(10,2)w_tot_med number(10,2)CURSOR w_c1 ISSELECT nm_depto, depto.cd_depto, SUM(vl_sal) soma, AVG(vl_sal)mediaFROM func, deptoWHERE func.cd_depto = depto.cd_depto;GROUP BY nm_depto, depto.cd_depto;w_c1_row w_c1% ROWTYPE;

BEGIN SELECT SUM(vl_sal) INTO w_tot_sal FROM func:SELECT AVG(vl_sal) INTO w_tot_med FROM func;OPEN w_c1;<<ler>>>FETCH w_c1 INTO w_c1_row;IF w_c1%FOUNDTHEN

INSERT INTO tabdep (nome, código, salário, média, totsal, totméd)VALUES (w_c1row.nm_depto, w_c1_row.cd_depto,w_c1_row.soma),w_c1_row.media, w_tot_sal, w_tot_med);GOTO ler;

END IF;END;

9. COMUNICAÇÃO COM O SQL

Por ter uma extensão do SQL, a PL*SQL permite a utilização de quase todos os comandos SQL. Foram excluídos desta lista dos comandos de “DDL” (ALTER, CREATE, RENAME,...) e os de “DDL” (GRANT, REVOKE,...).

Consideramos que a sintaxe dos comandos SQL já é de conhecimento dos leitores e não serão repetidos nesta apostila.

OPEN CURSOR

Executa o query associado com uma declaração explícito de cursor.

sintaxe: OPEN <cursor> [(<parâmetro> [, <parâmetro> ...])]

Ex.:

DECLARE CURSOR w_c2 (matrícula NUMBER) IS

SELECT nm_func, cd_matFROM func

WHERE cd_mat <= matrícula;BEGIN

OPEN w_c2 (350);....

END;

__________________________________________________________________________________________

110

TRIDATA, 03/01/-1,

No título o “Goto” esta junto, e aqui está separado!

__________________________________________________________________________________________

CURSOR LOOP

Implicitamente declara uma área para receber a row, abre um cursor, lê cada row e fecha o cursor quando todas as rows tiverem sido processadas.

sintaxe: [<< <label> >>]FOR <RECORD> IN <cursor> [(<parâmetro> [,<parâmetro> ...])] LOOP

<seqüência de comandos>END LOOP;

Ex.:DECLARE

CURSOR w_c2 IS SELECT cd_mat, vl_sal, vl_sal * .10 fgtsFROM func;

...BEGIN

DELETE FROM folha;COMMIT;FOR w_c2_row IN w_c2 LOOP

calc_ir(w_c2_row.vl_sal, w_vl_ir);w_vl_inss := calc_inss(w_c2_row.vl_sal, w_vl_lim);INSERT INTO folha

(cd_mat, vl_sal, vl_ir, vl_inss, vl_fgts)VALUES

(w_c2_row.cd_mat, w_c2_row.vl_sal,w_vl_ir, w_vl_inss, w_c2_row.fgts);

END LOOP;COMMIT;

END;

PROBLEMA PROPOSTO:Preencher a tabela TABDEP (nome, código, salário, média, totsal, totméd) com os valores abaixo obtidos na tabela de departamento:


Obs: Este problema é o mesmo anterior.

__________________________________________________________________________________________

111

__________________________________________________________________________________________

RESOLUÇÃO:

DECLAREw_tot_sal number(10,2)w_tot_med number(10,2)CURSOR w_c1 ISSELECT nm_depto, depto.cd_depto, SUM(vl_sal) soma, AVG(vl_sal)mediaFROM func, deptoWHERE func.cd_depto = depto.cd_depto;GROUP BY nm_depto, depto.cd_depto;

BEGIN SELECT SUM(vl_sal) INTO w_tot_sal_ FROM func;SELECT AVG(vl_sal) INTO w_tot_med_ FROM func;FOR w_c1row IN w_c1 LOOP

INSERT INTO tabdep (nome, código, salário, média, totsal, totméd)VALUES (w_c1row.nm_depto, w_c1_row.cd_depto,w_c1_row.soma,w_c1_row.media, w_tot_sal, w_tot_med);

END LOOP;END;

10. TRATAMENTO DE ERROS

Em PL/SQL uma warning ou error condition é chamada uma exception. Existem algumas exceções já definidas pelo Oracle com minemônicos para referência. Para as demais, podem ser dados nome pelo usuário, como veremos neste capítulo.

Quando um erro ocorre, uma exception é setada, isto é, a seqüência de execução do programa é interrompida e o controle é transferido para a área de tratamento de execução do programa.

As exceções pré-definidas pelo Oracle são setadas quando a condição de erro ocorre. As exceções criadas pelo programa deverão ser setadas explicitamente pelo verbo RAISE.

__________________________________________________________________________________________

112

TRIDATA, 03/01/-1,

Scc (Sistema de controle de cartão) isto é um minemônicos.

__________________________________________________________________________________________

EXCEÇÕES PRÉ-DEFINIDAS

Nome da Exceção Oracle Error SQLCODE Condição de ErroCURSOR_ALREADY_OPEN ORA-06511 -6511 É setada se for executado um

OPEN para um cursor já aberto.DUP_VAL_ON_INDEX ORA-00001 -1 É setada se for tentada uma

inclusão de uma coluna com valor duplicado em uma tabela que possui um índice unique nesta coluna.

INVALID_CURSOR ORA-01001 -1001 É setada se for feita uma operação ilegal com um cursor. Por exemplo: CLOSE em um cursor não aberto.

INVALID_NUMBER ORA-01722 -1722 É setada se algum comando SQL tentou uma conversão de uma string para número e esta conversão falha porque a string não representa um número.

LOGIN_DENIED ORA-01017 -1017 É setada se for feita uma tentativa de logon com um username/password inválido.

NO_DATA_FOUND ORA-01403 +100 É setada se num SELECT INTO nenhuma row foi retornada ou se foi feita uma referência a uma row não inicializada em uma tabela PL/SQL.

NOT_LOGGED_ON ORA-01012 -1012 É setada se uma programa PL/SQL tenata fazer acesso ao database sem efetuar um logon.

PROGRAM_ERROR ORA-06501 -6501 É setada se ocorrer um problema interno.

STORANGE_ERROR ORA-06500 -6500 É setada se PL/SQL sai da memória ou se a memória estiver corrompida.

TIME_ON_RESOURSE ORA-00051 -51 É setada se ocorrer timeout enquanto o ORACLE estiver aguardando por um recurso.

TOO_MARY_ROWS ORA-014222 -1422 É setada se um comando SELECT INTO retormar mais que uma row.

TRANSACTION_BACKED_OUT

ORA-00061 -61 É setada quando a parte remota de uma transação é desmanchada. A transação local deve ser desmanchada também.

VALUE_ERROR ORA-06502 -6502 É setada se uma operação aritmética, conversão, constraint error, truncation ocorrer.

ZERO_DIVIDE ORA-01476 -1476 É setada se houver ocorrido uma divisão por zero.

__________________________________________________________________________________________

113

__________________________________________________________________________________________

Ex.:DECLARE

…BEGIN

SELECT …SELECT …SELECT …

EXCEPTIONWHEN NO_DATA_FOUND THEN

…END;

PROBLEMA PROPOSTO: Fazer um programa para crítica dos dados informados através de parâmetros:

salário deve estar entre R$ 1500 e R$ 2000 mensais. Na base é gravado o salário atual. departamento deve existir na tabela de departamentos. grau de instrução deve estar entre 15 e 20. ramal deve iniciar com 2. sexo deve ser F ou M.

RESOLUÇÃO:

DECLAREw_salario number(7,2) := &1;w-depto char(3) := &2;w_git number(2) := &3;w_ramal number(4) := &4;w_nome char(8) := &5;w_sexo char(1) := &6;e_salario exception;e_salario exception;e_depto exception;e_git exception;e_ramal exception;e_sexo exception;w_aux number(3) := );

BEGINIF w_salario < 1500 OR w_salario > 2000 THEN

RAISE e_salario;END IF;select count(*) into w_auxi FROM depto WHERE cd_depto = w_depto;IF w_aux = o THEM

RAISE e_depto;END IF;IF w_git < 15 OR w_git > 20 THEM

RAISE e_git;END IF;IF SUBSTR(TO_CHAR (nr_ramal),1,1) <> ‘2’ THEM

RAISE e_ramal;END IF;IF w_sexo <> ‘F' AND w_sexo <> ‘M’ THEM

RAISE e_sexo;END IF;SELECT MAX(cd_mat) INTO w_aux FROM func;INSERT INTO func (cd_mat, vl_sal, cd_depto, nr _ramal, in_sexo, nm _func, nr _git)VALUES (w_aux + 1, w_sal * 12, w_depto, w_ramal, w_sexo. w_func, w_git);EXCEPTION

WHEN e_sexo THEM

__________________________________________________________________________________________

114

__________________________________________________________________________________________

RAISE_APPLICATION_ERROR (-20001, ‘SEXO INVALIDO’);WHEN e_ramal THEM

RAISE_APPLICATION_ERROR (-20003, ‘GRAU DE INSTRUCAOINVALIDO’);

WHEN e_depto THEMRAISE_APPLICATION_ERROR (-20004, ‘DEPARTAMENTO INVALIDO’);

WHEN e_salario THENRAISE_APPLICATION_ERROR (-20005, ‘SALARIO INVALIDO’);

WHEN OTHERS THENRAISE_APPLICATION_ERROR (-20999, SQLERRM(SQLCODE));

END;

EXCEÇÕES DEFINIDAS PELOS USUÁRIOS

A PL/SQL permite que sejam definidas exceções de um programa. Este tipo de exceção deve ser setada explicitamente pelo verbo RAISE.

RAISE

Seta uma exceção.

sintaxe: RAISE <exceção>

Ex.:DECLARE

erro_soma EXCEPTION;…

BEGINIF …THEN

RAISE erro_soma;END IF;

EXCEPTIONWHEN erro_soma THEN

…WHEN OTHERS THEN

…END;

Obs.: O desvio de execução do programa é transferido para a exceção OTHERS quando o erro ocorrido não foi tratado em outras exceções mais específicas. É uma opção para diminuição da lista de exceptions.

PRAGMA EXCEPTION_INIT

Associa um nome de exceção com um número de SQLCODE. Isto permite que se façam testes se erro mais específicos em vez de usar OTHERS.

sintaxe: PRAGMA EXCEPTION_INIT (<nome da exceção>,<número>)

__________________________________________________________________________________________

115

__________________________________________________________________________________________

Ex.:

DECLAREsem_privilegio EXCEPTION;PRAGMA EXCEPTION_INIT (sem_privilegio, -1031);— O ORACLE retorna o erro -1031 se, por exemplo, for feita uma tentativa de alterar uma tabela em que o usuário só tem autorização de SELECT.

BEGIN…

EXCEPTIONWHEN sem_privilegio THEN

…END;

RAISE_APPLICATION_ERROR

É uma procedure que permite ao usuário enviar mensagens de um subprograma ou database trigger.

sintaxe: RAISE_APPLICATION_ERROR (<número>,<mensagem>);

Ex.:

CREATE TRIGGER checa_salario…DECLARE…BEGIN

…IF (:new.vl_sal <salario_minimo OR :new.vl_sal > salário_maximo)THEN

RAISE_APPLICATION_ERROR (-20225, ‘Salário fora de faixa’);…

END IF;END;

Obs.: <número> deve variar de -20000 a -20999 e a <mensagem> deve possuir até 512 bytes de comprimento.

PROPAGAÇÃO DA EXCEÇÃO

Quando uma exceção é setada, se PL/SQL não encontrar um tratamento para ela no bloco correto ou subprograma, a exceção se propaga. Isto é, a exceção se reproduz no bloco externo e assim por diante até que a PL/SQL retorne um erro para o ambiente (abortando o programa).

Ex.:BEGIN

…BEGIN

IF x = 1 THEN RAISE A;ELSIF x = 2 THEN RAISE B;ELSE RAISE C;END IF;…

EXCEPTIONWHEN A THEN…

END;

__________________________________________________________________________________________

116

__________________________________________________________________________________________

…A’EXCEPTION

WHEN B THEN…

END;

A exceção A é tratada no bloco mais interno. Após seu tratamento o programa continua no comando A.

A exceção B se propaga para o bloco mais externo, é tratada e o programa termina normalmente.

A exceção COMANDO SQL: se propaga para o bloco mais externo, não e tratada e o erro passa para o ambiente, isto é, o programa é abortado.

SQLERRM

String procedure para traduzir um SQLCODE.

sintaxe: SQLERRM (<sqlcode>)

Ex.:

DECLAREmsg CHAR(100)

BEGINFOR num IN 1..9999 LOOP

msg := SQLERRM (num * -1);INSERT INTO tab_erro VALUES (msg);

END LOOP;END;

11. SUBPROGRAMAS

Subprogramas são blocos PL/SQL com nome, que podem receber parâmetros e ser invocados. A PL/SQL possui dois tipos de subprogramas chamados procedures e funções. Geralmente usa-se uma procedure para executar uma ação e uma função para calcular um valor.

Da mesma forma que qualquer outro bloco (anônimo) PL/SQL, os subprogramas possuem uma parte declarativa, uma parte executável e uma parte opcional para tratamento de exceção.

Os subprogramas podem ser definidos em qualquer ferramenta ORACLE que suporte PL/SQL. Podem ser declaradas dentro de blocos, procedures, funções e packages. Os subprogramas devem ser declarados no fim da parte declarativa, após todos os outros objetos do programa.

Ex.:DECLARE

w_vl_lim NUMBER(15,2) := &1;…PROCEDURE calc_ir (val_sal IN NUMBER, val_ir OUT NUMBER) IS BEGIN

…END calc_ir;FUNCTION calc_inss (val_sal IN NUMBER, val_lim IN NUMBER)

RETURN NUMBER ISval_inss NUMBER(15,2);BEGIN

…

__________________________________________________________________________________________

117

__________________________________________________________________________________________

BEGIN…

END;PROCEDURES

As procedures são subprogramas que executam uma ação específica.

sintaxe: PROCEDURE <nome da procedure> [(<parâmetro>[,<parâmetro>,…])] ISBEGIN

<comandos>[EXCEPTION

<tratamento das exceções>]END <nome da procedure>];

onde: parâmetro possui a seguinte sintaxe:

<nome da variável> [IN | OUT |IN OUT] <tipo> [{:= | DEFAULT} <valor>]

Quando [IN | OUT …] não for especificado, será assumido IN.

Ex:

PROCEDURE calc_ir (val_sal IN NUMBER, val_ir OUT NUMBER) ISBEGIN

IF val_sal < 300.00THEN

val_ir := 0;ELSIF val_sal BETWENN 300.01 AND 1000.00THEN

val_ir := val_sal * .10;ELSIF val_sal BETWEEN 1000.01 AND 3000.00THEN

val_ir := val_sal *.20;ELSE

val_ir := val_sal *.30;END calc_ir;

FUNCTIONS

Uma função é um subprograma que calcula um valor. Funções e procedures são estruturalmente iguais, exceto que as funções possuem uma cláusula RETURN que especifica o tipo de retorno da função.

sintaxe: FUNCTION <nome da função> [(<parâmetro>[,<parâmetro>,…])]RETURN <tipo da função> IS[<variáveis locais>]

BEGIN<comando>

[EXCEPTION<tratamento das exceções>]

END <nome da função>];

onde: parâmetros possui a seguinte sintaxe:

<nome da variável> [IN | OUT | IN OUT] <tipo> [{:=| DEFAULT} <valor>]

Quando [IN | OUT …] não for especificado, será assumido IN.

__________________________________________________________________________________________

118

__________________________________________________________________________________________

Ex.:

FUNCTION calc_inss (val_sal IN NUMBER, val_lim IN NUMBER)RETURN NUMBER IS

val_inss NUMBER(15,2);BEGIN

val_inss := val_sal * .08;IF val_inss > val_limTHEN

val_inss := val_lim;END IF;RETURN val_inss;

END calc_inss;

DECLARAÇÕES FORWARD

A PL/SQL exige que se declare um subprograma antes de chama-lo. No caso de recursividade, porém isto não é possível. A PL/SQL resolve este problema com uma declaração forward. Esta declaração pode ser usada para:

definir subprogramas em ordem alfabética

definir subprogramas recursivos

grupar subprogramas dentro de packages

Uma declaração forward consiste de uma declaração de subprograma terminada por um ;.

Ex:

DECLAREw_vl_lim NUMBER(15,2) := &1;…PROCEDURE calc_ir(val_sal IN NUMBER, val_ir OUT NUMBER); — declaração

— forwardFUNCTION calc_inss (val_sal IN NUMBER, val_lim IN NUMBER)

RETURN NUMBER ISval_inss NUMBER(15,2);BEGIN

…RETURN val_inss;

END calc_inss;PROCEDURE calc_ir (val_sal IN NUMBER, val_ir OUT NUMBER) IS BEGIN

…END calc_ir;

BEGIN…

END;

__________________________________________________________________________________________

119

__________________________________________________________________________________________

STORED SUBPROGRAMS

No ORACLE é possível armazenar um subprograma PL/SQL na base da dados após a compilação. Várias vantagens são adquiridas com este procedimento, tais como:

Produtividade Vários programas podem fazer rotinas previamente gravadas na base de dados, diminuindo a redundância de codificação.

Performance Subprogramas podem reduzir a necessidade de calls nas aplicações. Por exemplo, para executar 10 comandos SQL individuais, 10 calls são necessárias, porém para executar um subprograma contendo dez comandos SQL, apenas um call é necessário.

Memória Subprogramas rem a vantagem de compartilhar memória. Ou seja, somente uma cópia de um subprograma necessita ser carregado na memória para execução por múltiplos usuários.

Integridade A utilização de librarys diminui a possibilidade de erros na codificação de rotinas de consistência, uma vez que só há a necessidade de se efetuar a validação da rotina uma vez e ela poderá ser usada por qualquer número de aplicações.

Segurança Pode haver um aumento na segurança, uma vez que o DBA pode restringir o acesso a determinadas operações, fornecendo autorização somente através de subprogramas.

CHAMADAS DE UM STORED PROGRAM

Local Sintaxe:De outro subprograma

<nome da procedure> (<parâmetro>…);

De um programa de aplicação (HOST)

EXEC SQL EXECUTEBEGIN <nome da procedure> (<parâmetro)…); END;

END-EXEC;De uma ferramenta ORACLE

EXECUTE <nome da procedure> (<parâmetro>…); (SQL*PLUS)ouBEGIN <nome da procedure> (<parâmetro>…); END;

CRIANDO UM STORED SUBPROGRAM

sintaxe: CREATE [OR REPLACE]PROCEDURE <nome da procedure>[(<parâmetro>,…])]AS/IS [<variáveis locais>]BEGIN

<comandos>[EXCEPTION

<tratamento das exceções>]END <nome da procedure>];

CREATE [OR REPLACE]FUNCTION <nome da função> [(<parâmetro>[,<parâmetro>…])]

RETURN <tipo da função>AS/IS [<variáveis locais>]

BEGIN<comandos>

[EXCEPTION<tratamento das exceções>]

END <nome da função>];

A sintaxe é semelhante à de um subprograma definido dentro de um programa PL/SQL.

__________________________________________________________________________________________

120

__________________________________________________________________________________________

12. DATABASE TRIGGERS

Um DATABASE TRIGGER é um programa PL/SQL armazenado em um banco ORACLE, associado com uma tabela específica. O ORACLE irá disparar a execução do DATABASE TRIGGER automaticamente quando uma determinada operação SQL afeta a tabela.

Deste forma, pode-se usar um DATABASE TRIGGER para:

Logar modificações

garantir críticas complexas

Gerar o valor de colunas

Implementar níveis de segurança mais complexos

Manter tabelas duplicadas

Pode-se associar até 12 DATABASE TRIGGERS a cada tabela, um de cada tipo (BEFORE UPDATE <row>, BEFORE DELETE <row>, BEFORE INSERT <row>, BEFORE INSERT <comando>, BEFORE UPDATE <comando>, BEFORE DELETE <comando> e as mesmas sintaxes para AFTER). Um DATABASE TRIGGER é composto de 3 partes:

evento

constraint (opcional)

ação

Quando o evento ocorre, o trigger é disparado e um bloco PL/SQL “anônimo” executa a ação.

Deve-se observar que os DATABASE TRIGGERS executam com os privilégios do OWNER e não do usuário corrente.

sintaxe: CREATE [OR REPLACE] TRIGGER <nome trigger>{BEFORE | AFTER}{DELETE | INSERT | UPDATE [OF <coluna>[,<coluna>]

[OR DELETE | INSERT | UPDATE [OF <coluna>[,<coluna>]…}ON <tabela>[REFERENCING {OLD [AS] <nome> | NEW [AS] <nome>

[OLD [AS] <nome> | NEW [AS] <nome>]}FOR EACH ROW [WHEN (<condição>)]] <bloco PL/SQL/>

__________________________________________________________________________________________

121

__________________________________________________________________________________________

Ex.:

CREATE TRIGGER checa_salarioBEFORE UPDATE OF vl_sal, nr_git ON FUNCFOR EACH ROW WHEN (NEW.nr_git < 56)DECLARE

salario_minimo NUMBER(5) := 0;salario_maximo NUMBER(5) := 0;faixa EXCEPTION;negativo EXCEPTION;excede EXCEPTION;

BEGINSELECT MIN(vl_sal), MAX(vl_sal)

INTO salario-minimo, salario_maximoFROM folha

WHERE nr_git = :new.nr_git;IF (:NEW.vl_sal < salario_minimo OR

:NEW.vl_sal > salario_maximo)THEN

RAISE faixaELSIF (:NEW.vl_sal < OLD.vl_sal)THEN

RAISE negativo;ELSIF (:New.vl_sal > 1.1 * :OLD.vl_sal)THEN

RAISE excede;END IF;

EXCEPTIONWHEN faixa THEN

RAISE_APPLICATION_ERROR(-20225, ‘Salário fora da faixa’);WHEN negativo THEN

RAISE_APPLICATION_ERROR(-20230, ‘Incremento negativo’);WHEN excede THEN

RAISE_APPLICATION_ERROR(-20235, ‘Incremento excede 10%’);WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20999, SQLERRM(SQLCODE));END;

__________________________________________________________________________________________

122

__________________________________________________________________________________________

13. PACKAGES

Um package é um objeto que grupa logicamente subprogramas, objetos e tipos PL/SQL. Packages são compostos de duas partes: A especificação e o corpo do pacote. A especificação é a interface com as aplicações. Nela são declarados os tipos, variáveis, constantes, exceções, cursores e subprogramas. No corpo do pacote é concluída a definição dos cursores e subprogramas e feita a implementação da especificação.

Um package não pode ser chamado, receber parâmetro nem ser aninhado (declarado dentro de outro package).

sintaxe: PACKAGE <nome do pacote> IS — parte da especificação<declarações>

END [<nome do pacote>];

PACKAGE BODY <nome do pacote> IS — corpo do pacote<corpo dos subprogramas>

[BEGIN<comandos de inicialização>]

END [<nome do pacote>];

A parte de especificação são declarações públicas, visíveis pelas aplicações. O corpo implementa detalhes e declarações privadas que são invisíveis pelas aplicações. O corpo seria uma caixa preta.

CRIANDO UM PACKAGE

Um pacote pode ser criado internamente no SQL*PLUS ou no SQL*DBA usando-se os comandos CREATE PACKAGE e CREATE PACKAGE BODY.

sintaxe: CREATE [OR REPLACE]PACKAGE <nome do pacote> AS/IS

<declarações>END <nome do pacote>

CREATE [OR REPLACE]PACKAGE BODY <nome do pacote> AS/IS

<corpo dos subprogramas<[BEGIN

<inicializações>]END <nome do pacote>

__________________________________________________________________________________________

123

__________________________________________________________________________________________

Ex:CREATE PACKAGE funcionario AS

TYPE func_reg_type IS RECORD )cd_depto_mat NUMBER, vl_sal NUMBER);CURSOR salario (mat NUMBER) RETURN func_reg_type;

PROCEDURE admissão(nome CHAR, grau NUMBER, sal NUMBER, depto CHAR);

PROCEDURE demissão (mat NUMBER);END funcionario;

CREATE PACKAGE BODY funcionario AsCURSOR salario (mat NUMBER) RETURN func_reg_type IS

SELECT cd_mat, vl_sal FROM funcWHERE cd_mat = matORDER BY vl_sal DESC;

PROCEDURE admissão(nome CHAR, grau NUMBER, sal NUMBER, depto CHAR) IS

BEGININSERT INTO func

(nm_func, nr_git, vl_sal, cd_mat, cd_depto)VALUES(nome, grau, sal, cd_mat_seq.NEXTVAL, depto);

END admissão;

PROCEDURE demissão (mat NUMBER) ISBEGIN

DELETE FROM func WHERE cd_mat = mat;END demissão;

END funcinario;

Deve-se observar que somente a especificação do pacote é visível e acessível pela aplicação. Detalhes da implementação que se localizam do body são invisíveis e inacessíveis. Desta forma pode-se alterar o body sem haver necessidade de se recompilar os programas que usarem os packages

VANTAGENS DO USO DE PACKAGES

Packages oferecem várias vantagens:

Modularidade Packages permitem que se encapsule logicamente tipos, objetos e subprogramas relacionados.

Desenho da aplicação quando uma aplicação é desenhada, tudo que á necessário inicialmente é a informação da especificação do package. Pode se codificar e compilar a especificação sem o corpo do package. Uma vez que armazenado a referência ao pacote pode ser compilada também.

Segurança Com pacotes pode-se especificar que tipos, objetos e subprogramas são públicos ou privados. Os públicos são especificados na parte de especificação dos packages. Já os privados são totalmente especificados do body do package. Eles são usados dentro do próprio pacote e não precisam ficar visíveis a quem usar os pacotes.

Funcionalidade Variáveis públicas e cursores empacotados podem ser compartilhados por todas as procedures que executarem no ambiente.

Performance Quando é feita a primeira chamada de um package subprogram pela primeira vez, todo o pacote é carregado na memória. Assim, subsequentes chamadas a outros subprogramas dentro do pacote não requerem I/O de disco

__________________________________________________________________________________________

124

__________________________________________________________________________________________

REFERÊNCIAS A PACKAGES

Para fazer uma referência aos tipos, objetos e subprogramas declaradas dentro da especificação de packages deve-se usar a seguinte notação:

<nome de package>. <tipo><nome de package>. <objeto><nome de package>. <subprograma>

Pode-se fazer referência a pacotes de dentro de database triggers, stored subprograms e blocos PL/SQL dentro de programas e blocos PL/SQL anônimos.

A inicialização da parte executável do package é feita uma única vez, na primeira vez que for feita referência ao package (por sessão).

14. MENSAGENS EM STORED PROCEDURES E TRIGGERS

Podemos enviar mensagens a partir de procedures ou packag’s usando package DBMS_OUTPUT. Este package possui duas procedures PUT e PUT_LINE, que farão com que a mensagem seja armazenada em um buffer que pode ser mostrado quando o trigger, procedure ou package for executado.

Estas mensagens poderão ser mostradas com o uso da procedure GET_LINE ou com o uso do comando SET SERVER OUTPUT ON no SQL*PLUS ou SQL*DBA, caso contrário estas mensagens serão ignoradas.

Este package pode ser especialmente útil para depuração.

EXEMPLOSuponha que desejássemos depurar um trigger. Neste caso devemos incluir uma ou mais linhas de mensagens, da seguinte forma:

DBMS_OUTPUT.PUT_LINE (‘O novo valor é’||:new.col);

Devemos usar o SQL*PLUS ou SQL*DBA com a opção SERVER UOTPUT setada para ON, para executar o trigger e depurar as mensagens incluídas.

PROCEDURES E FUNÇÕES

Veremos, agora cada uma das procedures e funções contidas no package DBMS_OUTPUT:

DISABLE PROCEDURE

Esta procedure desabilita as chamadas para PUT, PUT_LINE, NEW_LINE, GET_LINE e GET_LINES e limpa o buffer.

Esta rotina não é necessária se estivermos usando a opção SERVER UOTPUT no SQL*PLUS ou SQL*DBA.

sintaxe: DISABLE;

__________________________________________________________________________________________

125

__________________________________________________________________________________________

ENABLE PROCEDURE

Esta procedure habilita chamadas para PUT, PUT_LINE, NEW_LINE, GET_LINE e GET_LINES. As chamadas a estas procedures são ignoradas se o package DBMS_OUTPUT não estiver habilitado.

Deve-se especificar a quantidade de informação (em bytes) as ser armazenada no buffer. Se o buffer for excedido, receberemos a seguinte mensagem de erro:

-20000, ORU-10027: buffer overflow, limit of buffer_limit bytes.

São permitidas múltiplas chamadas à ENABLE. Não é necessária a chamda a esta procedure quando utilizamos a opção SQL*DBA ou SQL*PLUS. Se existirem múltiplas chamadas a esta procedure, é o maior valor dentre os especificados. O tamanho máximo é 1.000.000 e o mínimo é 2.000 bytes.

sintaxe: ENABLE (buffer_size in integer);

GET_LINE PROCEDURE

Esta procedure recupera uma linha da informação do buffer, excluindo o caracter newline final. O tamanho máximo da linha é 255 bytes.

Todas as linhas não recuperadas serão descartadas quando usadas as procedures PUT, PUT_LINE ou NEW_LINE.

Se a procedure concluir com sucesso, o status retornado é zero, caso contrário é 1 (não existem linhas no buffer).

sintaxe: GET_LINE(line out varchar2, status out integer);

GET_LINES PROCEDURE

Esta procedure recupera um array de linhas e pode ser usada no lugar de GET_LINE para reduzir o número de chamadas ao servidor. Devemos especificar o número de linhas que desejamos recuperar do buffer. Após recuperar o número de linhas especificado, a procedure retorna o número de linhas realmente recuperado. Se este número for menor que o número de linhas requisitado, significa que não existem mais linhas no buffer.Todas as linhas não recuperadas serão descartadas quando usadas as procedures PUT, PU_LINR ou NEW_LINE.Cada linha no array pode ter até 255 bytes de comprimento.

Sntaxe: GET_LINES (lines out chararr, numlines in out integer);

NEW_LINE PROCEDURE

Esta procedure coloca uma marca de fim da linha no buffer. Normalmente usamos esta procedure após uma ou mais chamadas à procedure PUT, para indicar fim de mensagem. Cada chamada a NEW_LINE gerará uma linha a ser retornada pelo GET_LINE.

sintaxe: NEW_LINE;

__________________________________________________________________________________________

126

__________________________________________________________________________________________

PUT PROCEDURE

Esta procedure especifica a informação que desejamos armazenar no buffer. Devemos usar esta procedure para adicionar pedaços de informação ao buffer. As procedures GET_LINE e GET_LINES não retornam uma linha que não tenham sido terminadas com o caracter newline. Se o tamanho especificado pelo buffer for excedido, será recebido uma mensagem de erro.

Sintaxe: PUT (item varchar2 | number | date);

Obs.: Se passarmos como parâmetro um number ou date, quando o item for recuperado será convertido (com TO_CHAR) para string no formato default. Se desejarmos um formato particular, devemos passar o parâmetro já convertido (VARCHAR2).

PUT_LINE

Esta procedure especifica a informação que desejamos armazenar no buffer. Esta procedure armazena, automaticamente, um caracter de fim de linha a cada texto enviado. Cada chamada a PUT_LINE gera uma linha a ser recuperada pela procedure GET_LINE.

EXEMPLO

O package DBMS_OUTPUT é, normalmente, usado para depuração de stored procedures e tringgers, como veremos no exemplo abaixo:

CREATE FUNCTION depto_sal (p_cd_depto IN CHAR) RETURN NUMBER IS CURSOR func_cursor IS

SELECT vl_sal FROM func WHERE cd_depto = p_cd_depto;v_total_sal NUMBER(11,2) := 0;v_conta NUMBER(10) := 1;func_rec func_cursor%ROWTYPE;BEGIN

FOR func_rec IN func_cursor LOOPv_total_sal := v_total_sal + func_rec.vl_sal;PUT_LINE (‘loop NO. = ‘ || TO_CHARY(v_conta) || ‘; salário = ‘|| TO_CHAR(v_conta_sal));v_conta := v_conta + 1;

END LOOP;PUT_LINE (‘Total de salários = ‘|| TO_CHAR(v_total_sal));RETURN v_total_sal;

END depto_sal;

__________________________________________________________________________________________

127

Documents

TREINAMENTO PLSQL