Administracao de Banco de Dados

Administrao de Banco de DadosLinguagem PL/SQL

Prof. Dr. Wagner J. [email protected]

Lins-SP, Agosto de 2012

Apresentao da Disciplina

Ementa: PL/SQL: blocos annimos, cursores, stored procedures,

functions, triggers.

Critrios de Aproveitamento: Provas: peso 8.0 (oito) Trabalhos: peso 2.0 (dois)

2

BibliografiaLivro Texto:1. Introduo a Sistemas de Banco de Dados; Date, C. J.; Ed. Campus; 8 edio; 2004;

15 exemplares.2. Sistemas de Banco de Dados; Korth, Henry F.; Ed. Campus; 5 edio; 2006; 15

exemplares.3. Oracle Database 11g PL/SQL: Programao; McLaughlin, Michel; Ed. Novatec; 1

edio; 2009; 10 exemplares.

Complementar:4. Projeto e Modelagem de Banco de Dados; Lightstone, Teorey; Ed. Campus; 1 edio;

2006; 10 exemplares.5. Oracle 10g PL/SQL; Oliveira, Celso H. Poderoso de; Ed. Novatec; 1 edio; 2005; 10

exemplares.6. Oracle 10g Database: Guia do DBA; Serson, Roberto Rubinstein; Ed. Novatec; 1 edio;

2004; 10 exemplares.7. Oracle 10g: Ideal Para Quem Deseja Iniciar o Aprendizado do Oracle; Ramalho, Jos

A.; Ed. Thomson; 1 edio; 2005; 10 exemplares.8. Gerenciando Banco de Dados: A Abordagem Entidade-Relacionamento para Projeto

Lgico; Chen, Peter; Makron Books; 1 edio; 1990; 7 exemplares.

3

Linguagem PL/SQL

Definio Blocos annimos Cursores Stored procedures Functions Triggers

4

Oracle 11g

Instalao e Servios iSQLPlus Criao de um usurio DBA

5

Instalao e Servios

No Windows, possvel realizar a instalao default, sem alterar as configuraes padres. Mas fique atento para anotar a senha inicial que solicitada ao usurio SYSTEM (Administrador).

Se voc possui um computador com 512Mb de memria (o ideal 1Gb) e processador acima de 2GHertz, pode instalar a verso completa do Oracle 11g. O disco de instalao tem cerca de 600Mb.

Para mquinas mais antigas, recomenda-se o uso do Oracle 11g Express Edition. A configurao mnima 256Mb e 1GHertz. O instalador tem cerca de 250Mb.

Obs.: Somente a verso 11g Express Edition gratuita para uso comercial (limitado a 1 base de dados de 4Gb).

6

Instalao e Servios (cont.)

Afim de carregar o Oracle somente quando realmente desejar utiliz-lo, coloque os 3 servios listados a seguir para serem iniciados manualmente. ORACLESERVICE - o principal servio. Efetivamente, esse o

SGBD e consome bastante recursos do sistema.

TNSLISTERNER responsvel por fazer a comunicao entre as chamadas (consultas) ao B.D. e o servio do SGBD.

ISQLPLUS o servio que disponibiliza o programa iSQLPlus para realizar a conexo ao Oracle atravs de browser (11g).

Para localizar os servios, que na instalao so colocados para iniciar automaticamente, procure em: INICIAR / CONFIGURAES / PAINEL DE CONTROLE /

FERRAMENTAS ADMINISTRATIVAS / SERVIOS

7

http://:/isqlplus

http://ANDROMEDA:5560/isqlplusiSQLPlus

8

Criao de um usurio DBA

Para utilizar o Oracle, necessrio possuir um usurio (ou uma conta) atravs da qual possvel se conectar ao banco de dados.

Sintaxe:CREATE USER IDENTIFIED BY ;

Para se poder criar um usurio, preciso estar conectado com privilgio de administrador (SYSTEM).

CONN SYSTEM / ORACLE @ ORCL;

usurio senha banco

9

Criao de um usurio DBA (cont.)

Criando um usurio chamado wagner cuja senha unilins:CREATE USER wagner IDENTIFIED BY unilins;

Concedendo privilgio de Administrador (DBA) ao usurio:GRANT DBA TO wagner;

Conectando-se com o novo usurio:CONN WAGNER/UNILINS@ORCL;

Para verificar o usurio atual:SHOW USER;

Obs.: Por questo de segurana, no recomendado atribuir o privilgio de DBA para usurios comuns do sistemas.

10

Linguagem Procedural PL/SQL

A linguagem PL/SQL uma extenso do SQL Combina o poder de manipulao de dados do SQL com o

poder de processamento das linguagens de programao Estruturada em blocos Permite a criao de variveis Permite controle do fluxo de execuo (IF, FOR) Permite integrao entre diferentes ferramentas Oracle No permite comandos DDL No tem nenhuma funcionalidade de entrada ou de sada

construda diretamente na linguagem O PL/SQL no padronizado nos diversos SGBDs.

11

Vantagens do uso de PL/SQL

Possui maior poder de processamento (consultas e controle de fluxo)

Pode melhorar o desempenho de uma aplicao (reaproveita o mesmo plano de execuo)

Diminui o trafego de dados pela rede (vrias consultas pode ser encapsuladas numa nica requisio)

Controle centralizado no servidor (processamento centralizado)

Pode ser utilizado por diversas ferramentas (forms/reports, java, php, ...)

12

Bloco Annimo

So blocos PL/SQL que podem ser gerados para executar uma sequencia lgica de instrues SQL e procedurais.

Prottipo bsico:[DECLARE]

seo declarativa variveis, tipos, cursores e subprogramasBEGIN

seo executvel instrues SQL e procedurais[EXCEPTION]

seo de tratamento de exceesEND;

possvel, por exemplo, criar um bloco annimo para realizar a manuteno em algumas tabelas. Se esse bloco for salvo em arquivo SCRIPT, possvel carrega-lo e executa-lo todas as vezes que for necessrio.

13

Exemplo 01:Criando uma varivel e apresentado seu valor.

SET SERVEROUTPUT on;DECLARE N number := 5;BEGIN

dbms_output.put_line('Valor de N ' || N);END;

14

SERVEROUTPUT serve para habilitar a sada e fazer testes.

|| serve para concatenao

Exemplo 02:Criando uma varivel de substituio.

SET SERVEROUTPUT on;DECLARE S varchar2(20);BEGIN

S := '&input';dbms_output.put_line('Valor de S ' || S);

END;

15

&input uma varivel de substituio, usada para permitir que o usurio informe valores.

Exemplo 03:Tratando excees

SET SERVEROUTPUT on;DECLARE

S varchar2(10);BEGIN

S := '&input';dbms_output.put_line('Valor de S ' || S);

EXCEPTIONWHEN others THEN

dbms_output.put_line(SQLERRM);END;

16

Um possvel erro a digitao de uma string maior que o tamanho definido da varivel S.

Comentrios

H 2 tipos de comentrios em PL/SQL:

-- comentrio de uma nica linha.

/* comentrio de mltiplas linhas. em geral, usado para textos extensos. */

17

Estrutura de Controle Condicional

Estruturas de controle fazem a verificao de uma condio lgica e a ramificao da execuo de programas de acordo com as expresses booleanas.

Em PL/SQL, possvel criar desvios condicionais atravs de IF e CASE.

Todos statements IF so blocos e terminam com END IF. H 2 subtipos: if-then-else e if-then-elsif-then-else.

Os statements CASE tambm so blocos e terminam com END CASE.

A seguir, veremos exemplos do uso de IF e CASE.

18

IF, IF ... ELSE

BEGINIF 1=1 THEN

dbms_output.put_line('Equal');END IF;

END;

19

BEGINIF 1=2 THEN

dbms_output.put_line('Equal');ELSE

dbms_output.put_line('Different');END IF;

END;

IF ... ELSIF ... ELSE

20

BEGINIF 1>2 THEN

dbms_output.put_line('Major');ELSIF 1

Operadores Lgicos

BEGINIF 1=1 AND 2=2 THEN

dbms_output.put_line('True');END IF;

END;

21

BEGINIF 1=1 OR 1=2 THEN


END;

Operadores Lgicos (cont.)

22

BEGINIF 1 BETWEEN 1 AND 3 THEN

dbms_output.put_line('In the range');END IF;

END;

BEGINIF 1 IN (1,2,3) THEN

dbms_output.put_line('In the set');END IF;

END;


23

BEGINIF NOT FALSE THEN


END;

BEGINIF 'Str%' LIKE 'String' THEN

dbms_output.put_line('Match');END IF;

END;


24

DECLARE var BOOLEAN;BEGIN

IF var IS NULL THENdbms_output.put_line('It is null');

END IF;

END;

Exemplo 04:Calcular a mdia e verificar se foi aprovado.

DECLARE nota1 number := 8;nota2 number := 7;media number;

BEGINmedia:= (nota1+nota2) / 2;IF media >= 6.0 THEN

dbms_output.put_line('Voc foi aprovado!');ELSE

dbms_output.put_line('Voc foi reprovado');END IF;

END;

25

Exemplo 05:Comparar valores.

BEGINCASE true

WHEN (1>3) THENdbms_output.put_line('Um maior que trs');

WHEN (3

Estruturas Iterativas

O PL/SQL permite loops FOR, SIMPLE e WHILE. Loops FOR podem ser numricos e cursor.

O loop numrico repete atravs de um valor definido, enquanto loop cursor repete atravs de registros retornados por um cursor statement SELECT (veremos posteriormente).

Loops simples so estruturas explcitas. Eles exigem que voc gerencie ambos os ndice loop e o critrio exit.

O loop WHILE difere do loop simples, pois controla a entrada para o loop, no a sada.

Vermos a seguir os exemplos de estruturas iterativas.

27

Exemplo 06:Apresentar a tabuada do nmero 3 (FOR).

DECLARE N number := 3;R number;

BEGINFOR I IN 1..10 LOOP

R := N * I;

dbms_output.put_line(N || 'x' || I || ' = ' || R);END LOOP;

END;

28

Exemplo 07:Tabuada (LOOP simples).

DECLARE N number := 3;I number := 1;R number;

BEGINLOOP

EXIT WHEN I > 10;R := N * I;

dbms_output.put_line(N || 'x' || I || ' = ' || R);I := I + 1;

END LOOP;END;

29

Exemplo 08:Tabuada (WHILE).

DECLARE N number := 3;I number := 1;R number;

BEGINWHILE I

Uso de SQL em blocos annimos

Agora que j conhecemos as principais estruturas de controle da linguagem PL/SQL, iremos ver como usar em conjunto dos recursos SQL.

Assim, alm dos comando DML (SELECT, INSERT, UPDATE, DELETE), poderemos declarar variveis e usar instrues de controle (IF, CASE, FOR, LOOP, WHILE).

31

Exemplo 09:Obter a quantidade de registros de uma tabela.

CREATE TABLE tb_teste (codigo integer not null primary key,descricao varchar(40)

);

CREATE SEQUENCE seq_teste START WITH 1;

INSERT INTO tb_teste (codigo, descricao) VALUES (seq_teste.NEXTVAL, 'Teste 1');INSERT INTO tb_teste (codigo, descricao) VALUES (seq_teste.NEXTVAL, 'Teste 2');INSERT INTO tb_teste (codigo, descricao) VALUES (seq_teste.NEXTVAL, 'Teste 3');COMMIT;

32

DECLARE qtde number;BEGIN

SELECT count(*) INTO qtde FROM tb_teste;dbms_output.put_line(' Existem ' || qtde || ' registros.');

END;

Exemplo 10:Apresentar os registros de uma tabela.

BEGINFOR REG IN (SELECT descricao FROM tb_teste) LOOP

dbms_output.put_line(REG.descricao);END LOOP;

END;

33

A varivel REG instanciada automaticamente e o lao FOR executado de acordo com a quantidade de registros retornados pela consulta.

Exemplo 11:Inserir novos registros via PL/SQL.

BEGINFOR I IN 4..10 LOOP

INSERT INTO tb_teste (codigo, descricao) VALUES (seq_teste.NEXTVAL, 'Teste ' || I);

END LOOP;END;

34

ps. Executar o exemplo anterior para ver se os registros foram gravados.

Exemplo 12:Controle de transao.

BEGINSAVEPOINT init_transaction;FOR I IN 11..20 LOOP

INSERT INTO tb_teste (codigo, descricao) VALUES (seq_teste.NEXTVAL, 'Teste ' || I);

END LOOP;COMMIT;

EXCEPTIONWHEN others THEN

ROLLBACK TO init_transaction;dbms_output.put_line(SQLERRM);

END;

35

ps. Agora temos a garantia de que toda a transao seja realizada ou, caso ocorra algum erro durante a transao, toda a operao ser desfeita.

Exemplo 13:Excluir os registros com cdigos pares.

BEGINFOR REG IN (SELECT codigo FROM tb_teste) LOOP

IF MOD(REG.codigo, 2) = 0 thenDELETE FROM tb_teste WHERE REG.codigo = codigo;

END IF;

END LOOP;END;

36

ps. Utilize ROLLBACK para desfazer a excluso.lembre-se, o ROLLBACK s ir funcionar at o ltimo COMMIT.

Exemplo 14:Apresentar cdigo e descrio de todos os registros cadastrados. Ao final, apresentar a quantidade de registros listados. Caso a tabela esteja vazia,emitir uma mensagem de aviso.

BEGINSELECT count(*) INTO qtde FROM tb_teste;IF qtde > 0 then

FOR REG IN (select * from tb_teste) LOOPdbms_output.put_line(REG.codigo||' - '|| REG.descricao);

end LOOP;dbms_output.put_line('Listados: ' || qtde);

elsedbms_output.put_line('No h registros');

END IF;

END;

37

Exemplo 15:Apresentar em maisculo a descrio dos registros com cdigo at 5 e em minsculo a descrio dos registros com cdigo acima de 5.

38

BEGINFOR REG IN (select * from tb_teste) LOOP

IF REG.codigo

Exemplo 16:Apresentar todas as descries em minsculo, exceto a do registro com o maior cdigo de dever aparecer em maisculo.

39

DECLARE maior Number;

BEGINSELECT MAX(codigo) INTO maior FROM tb_teste;FOR REG IN (select * from tb_teste) LOOP

IF REG.codigo maior THENREG.descricao := lower(REG.descricao);

ELSEREG.descricao := upper(REG.descricao);

END IF;

dbms_output.put_line(REG.codigo||' - '|| REG.descricao);end LOOP;

END;

Exerccios1. Criar um bloco annimo que compare 2 nmeros e apresente o maior valor.2. Altere o exerccio anterior para que seja prevista a possibilidade dos valores

serem iguais.3. Altere o exerccio anterior para que seja prevista a possibilidade de existir valor

nulo.4. Criar um bloco annimo que dado o nmero de minutos retorne o nmero de

horas correspondente.5. Crie uma funo que dado um salrio retorne o nmero de salrios mnimos

que a pessoa ganha.6. Fazer um bloco annimo para verificar se o ano atual par.7. Fazer um bloco annimo para verificar se o ano atual bissexto.8. Criar um bloco annimo para apresentar os anos bissextos entre 2000 e 2100. 9. Escreva um bloco annimo que crie uma varivel do tipo date, atribua a data

corrente e mostre na tela: A data atual e o ltimo dia deste ms.10. Criar um bloco PL/SQL para imprimir os 15 primeiros termos da sequncia de

Fibonacci: 1 1 2 3 5 8 13 21 34 55 ...

40

1. Criar um bloco annimo que compare 2 nmeros e apresente o maior valor.

set serveroutput on;declare

n1 number := 3;n2 number := 5;

beginif (n1>n2) then

dbms_output.put_line(n1 || ' maior');else

dbms_output.put_line(n2 || ' maior');end if;

end;

41

2. Altere o exerccio anterior para que seja prevista a possibilidade dos valores serem iguais.



beginif (n1>n2) then

dbms_output.put_line(n1 || ' maior');elsif (n1

3. Altere o exerccio anterior para que seja prevista a possibilidade de existir valor nulo.



beginif (n1 is null) or (n2 is null) then

dbms_output.put_line('Valores invlidos!');elsif (n1>n2) then

dbms_output.put_line(n1 || ' maior');elsif (n1

4. Criar um bloco annimo que dado o nmero de minutos retorne o nmero de horas correspondente.


M number := 120;H number;

beginH := M / 60;dbms_output.put_line(H);

end;

44

5. Crie uma funo que dado um salrio retorne o nmero de salrios mnimos que a pessoa ganha.


minimo number := 622.00;salario number;qtde: number;

beginsalario := &salario;qtde := salario / minimo;dbms_output.put_line(qtde ' salrios mnimos');

end;

45

6. Fazer um bloco annimo para verificar se o ano atual par.


ano number;begin

select extract(year from sysdate) into anofrom dual;if ano mod 2 = 0 then

dbms_output.put_line(ano || ' par');else

dbms_output.put_line(ano || ' mpar');end if;

end;

46

7. Fazer um bloco annimo para verificar se o ano atual bissexto.


ano number;begin

select extract(year from sysdate) into anofrom dual;if ano mod 4 = 0 then

dbms_output.put_line(ano || ' bissexto');else

dbms_output.put_line(ano || ' no bissexto');end if;

end;

47

8. Criar um bloco annimo para apresentar os anos bissextos entre 2000 e 2100.


ano number;begin

dbms_output.put_line('Anos bissextos entre 2000 e 2100');for ano in 2000..2100 loop

if ano mod 4 = 0 thendbms_output.put_line(ano);

end if;end loop;

end;

48

9. Escreva um bloco annimo que crie uma varivel do tipo date, atribua a data corrente e mostre na tela: A data atual e o ltimo dia deste ms.


hoje date;ultimo date;

beginselect sysdate, last_day(sysdate) into hoje, ultimofrom dual;dbms_output.put_line(hoje || ' | ' || ultimo);

end;

49

10. Criar um bloco PL/SQL para imprimir os 15 primeiros termos da sequncia de Fibonacci: 1 1 2 3 5 8 13 21 34 55 ...


ult number := 1;ant number := 0;aux number;

beginfor i in 1..15 loop

dbms_output.put_line(ult);aux := ult;ult := ult + ant;ant := aux;

end loop;end;

50

Cursores

Um cursor um tipo abstrato de dados da linguagem PL/SQL que permite armazenar o resultado de uma instruo SELECT. Ou seja, um cursor uma matriz gerada dinamicamente a partir de uma consulta.

Existem 2 tipos de cursores: explcitos e implcitos. Cursor explcito criado no bloco de declaraes. Cursor implcito criado dentro de qualquer bloco de

execuo ou de exceo.

Por questo de desempenho, o uso de cursores fortemente recomendado.

Sintaxe: declare cursor is ;

51

Criar as tabelas produto e categoria

create table tb_categoria (cod_cat integer not null primary key,descricao varchar(60)

);

create table tb_produto (cod_prod integer not null primary key,descricao varchar(60),preco decimal(8,2),cod_cat integer,foreign key(cod_cat) references tb_categoria(cod_cat)

);

52

Cadastrar alguns registroscreate sequence sq_produto start with 1 increment by 1;

insert into tb_categoria (cod_cat, descricao) values (1,'Informtica');

insert into tbl_categoria (cod_cat, descricao) values (2,'Papelaria');

insert into tb_produto (cod_prod,decricao,preco,cod_cat) values (sq_produto.nextval, 'Impressora', 250.00, 1);

insert into tb_produto (cod_prod, decricao,preco,cod_cat) values (sq_produto.nextval, 'mouse', 49.90, 1);

insert into tb_produto (cod_prod, decricao,preco,cod_cat) values (sq_produto.nextval, 'papel', 15.00, 2);

insert into tb_produto (cod_prod, decricao,preco,cod_cat) values (sq_produto.nextval, 'grampeador', 7.50, 2);

commit;

53

Cursor na forma expandida

Declare cursor C is select descricao, preco from tb_produto; R C%rowType;

BeginOpen C;Loop

Fetch C into R;Exit when C%NotFound;dbms_output.put_line(R.descricao || ' ' || R.preco);

End loop;Close C;

End;

54

No exemplo, o cursor foi declarado de forma explcita.

Cursor na forma simplificada

Declare cursor C is select descricao, preco from tb_produto;Begin

For R in C loopdbms_output.put_line(R.descricao || ' ' || R.preco);

End loop;End;

55

O cursor foi criado de forma implcita.No exemplo, o cursor aberto e fechado automaticamente.O lao de repetio tambm incrementado de forma automtica.

Apresentar a descrio do produto, preo do produto e a descrio da categoria.

Declare cursor C is select p.descricao, p.preco, c.descricao categoriafrom tb_produto p inner join tb_categoria c

on p.cod_cat = c.cod_cat;Begin

For R in C loopdbms_output.put_line(R.descricao || ' ' ||

R.preco || ' ' ||R.categoria);

End loop;End;

56

ps. Note que o cursor pode obter dados de uma ou mais tabelas.

Cursor recebendo parmetro

Apresentar uma listagem de cada categoria com seus respectivos produtos utilizando quebra de sesso por categoria. CATEGORIA HIGIENE:

- Sabonete R$ 0,80 - Shampoo R$ 4,20

CATEGORIA BEBIDA:

Coca-Cola R$ 4,50 Suco de uva R$ 7,80

57

Declare cursor CC is select cod_cat, descricaofrom tb_categoria;

cursor CP(p_cod_cat number) is select descricao, precofrom tb_produtowhere cod_cat = p_cod_cat;

BeginFor RC in CC loop

dbms_output.put_line('Categoria: ' || RC.descricao);For RP in CP(RC.cod_cat) loop

dbms_output.put_line('- ' || RP.descricao ||'R$ ' || RP.preco);

End loop;End loop;

End;

58

Cursor recebendo parmetro (cont.)

Stored Procedure

Um Stored Procedure um tipo de programa PL/SQL que, aps ser compilado, permanece armazenado no servidor do banco de dados, podendo ser executado a qualquer momento diretamente no banco ou atravs de uma linguagem de programao.

possvel criar Stored Procedures com ou sem parmetros formais. A execuo de uma stored procedure mais rpida do que a execuo

de instrues SQL simples. Sintaxe:

CREATE [OR REPLACE] PROCEDURE AS

BEGIN

END;

59

Exemplo 1:Stored Procedure para cadastrar novos produtos.

CREATE OR REPLACE procedure prc_inserir_produto(p_desc varchar, p_preco number) AS

BEGININSERT INTO tb_produto(cod_prod, descricao, preco)

VALUES(sq_produto.nextval, p_desc, p_preco);COMMIT;

END;

60

Para executar, use: EXEC prc_inserir_produto('iPad',1500.00);Em caso de erros: SHOW ERRORS;

Exemplo 2:Modificando o exemplo anterior para gravar a descrio em maisculo e sem espaos.


BEGININSERT INTO tb_produto(cod_prod, descricao, preco)

VALUES(sq_produto.nextval, upper(trim(p_desc)),p_preco);

COMMIT;END;

61

Exemplo 3:Modificando o exemplo anterior para cadastrar somente valor positivo para o preo.


v_preco number;BEGIN

if p_preco < 0 thenv_preco := p_preco * (-1);

elsev_preco := p_preco;

end if;INSERT INTO tb_produto(cod_prod, descricao, preco)

VALUES(sq_produto.nextval, p_desc, v_preco);COMMIT;

END;62

Passagem de Parmetro IN OUT

Existem 3 formas de passagem de parmetro em PL/SQL: IN, OUT e IN OUT. Com IN (forma default), o parmetro de entrada. Com OUT, a passagem por referncia.

create or replace procedure prc_par_impar(p_t IN number, p_ret OUT varchar2) is

beginif(MOD(p_t,2)=0) then

p_ret := ' par';else

p_ret := ' mpar';end if;

end;63

Exerccios (1/3)1. Criar um stored procedure que receba como parmetro o

cdigo de uma categoria e apresente todos os produtos desta categoria.

2. Modificar o programa anterior de forma que seja recebida a descrio da categoria ao invs do cdigo.

3. Elaborar um stored procedure que receba como parmetro a descrio de uma categoria e atualize os preos dos produtos. Os produtos com preo at 100 reais sero reajustados em 3%, os demais tero aumento de 5%.

4. Criar uma stored procedure para apresentar a descrio de cada categoria com a respectiva quantidade de produtos cadastrados.

5. Criar uma stored procedure para apresentar a descrio, o preo e a categoria do produto mais caro.

64

Exerccios (2/3)

6. Criar um stored procedure para receber a descrio de uma categoria e o percentual de aumento que dever ser aplicado a todos os produtos.

7. Criar um stored procedure para receber 2 cdigos de produtos. Deve-se aumentar em 8% o produto mais barato e baixar 6% o produto mais caro.

8. Criar um stored procedure para listar todos os produtos com quebra de sesso por categoria.

9. Criar um stored procedure para apagar as categorias que no possuem nenhum produto.

10. Criar um stored procedure para listar os produtos cadastrados em duplicidade (pela descrio).

65

Exerccios (3/3)11. Criar um stored procedure para listar os produtos at um

determinado preo. O valor do limite dever ser passado como parmetro.

12. Criar um stored procedure para listar os produtos cujo preo esteja dentro de um intervalo. Os limites do intervalo devero ser passados como parmetros.

13. Criar um stored procedure para listar as categorias com mais de 5 produtos.

14. Criar um stored procedure para listar todos os produtos com quebra de sesso por categoria, somente das categorias com mais de 5 produtos.

15. Criar um stored procedure que receba o cdigo de uma categoria e faa a excluso de seu registro, mas somente se a categoria no tiver produtos cadastrados.

66

1. Criar um stored procedure que receba como parmetro o cdigo de uma categoria e apresente todos os produtos desta categoria.

create or replace procedureprc_mostrar_produtos1(x number) AS

cursor p(y number) is select * from tb_produto where cod_cat = y;

beginfor r in p(x) loop

dbms_output.put_line('Descricao: '||r.descricao);dbms_output.put_line('Preco: ' || r.preco);

end loop;end;

exec prc_mostrar_produtos1(1);67

2. Modificar o programa anterior de forma que seja recebida a descrio da categoria ao invs do cdigo.

create or replace procedureprc_mostrar_produtos2(x varchar) AS

cursor p(y varchar) is select p.descricao, p.precofrom tb_produto p inner join tb_categoria c

on p.cod_cat = c.cod_catwhere c.descricao = y;

beginfor r in p(x) loop

dbms_output.put_line('Descricao: '||r.descricao);dbms_output.put_line('Preco: ' || r.preco);

end loop;end;

exec prc_mostrar_produtos2('Informtica');68

3. Elaborar um stored procedure que receba como parmetro a descrio de uma categoria e atualize os preos dos produtos. Os produtos com preo at 100 reais sero reajustados em 3%, os demais tero aumento de 5%.

create or replace procedureprc_atualizar_produtos(x varchar) as

v_cod_cat number;begin

select cod_cat into v_cod_catfrom tb_categoriawhere descricao = x;

update tb_produtoset preco = preco * 1.03where cod_cat = v_cod_cat and preco 100;

end;

exec prc_atualizar_produtos('Informtica');69

4. Criar uma stored procedure para apresentar a descrio de cada categoria com a respectiva quantidade de produtos cadastrados.

create or replace procedure prc_listar_categorias ascursor c is

select c.descricao, count(*) qtdefrom tb_categoria c inner join tb_produto p

on c.cod_cat = p.cod_catgroup by c.descricao;

beginfor r in c loop

dbms_output.put_line(r.descricao || ' - ' || r.qtde);end loop;

end;

70

5. Criar uma stored procedure para apresentar a descrio, o preo e a categoria do produto mais caro.

create or replace procedure prc_mais_caro asv_max number;cursor b(p_preco) is

select p.descricao, p.preco, c.descricao categoriafrom tb_categoria c left join tb_produto p

on c.cod_cat = p.cod_catwhere p.preco = p_preco;

beginselect MAX(preco) into v_max from tb_produto;for r in b loop

dbms_output.put_line(r.descricao || ' - ' ||r.preco || ' - ' ||r.categoria);

end loop;end;

exec prc_mais_caro;

71

6. Criar um stored procedure para receber a descrio de uma categoria e o percentual de aumento que dever ser aplicado a todos os produtos.

create or replace procedureprc_categoria_aumento(p_cat varchar, p_aumento number) as

beginupdate tb_produtoset preco = preco + (preco * p_aumento / 100)where cod_cat = (select cod_cat

from tb_categoriawhere descricao = p_cat);

end;

exec prc_categoria_aumento('Informtica', 3);

72

7. Criar um stored procedure para receber 2 cdigos de produtos. Deve-se aumentar em 8% o produto mais barato e baixar 6% o produto mais caro.

create or replace procedureprc_produto_aumento(p_cod1 number, p_cod2 number) as

preco1 number;preco2 number;

beginselect preco into preco1 from tb_produto where cod_prod = p_cod1;select preco into preco2 from tb_produto where cod_prod = p_cod2;if preco1

8. Criar um stored procedure para listar todos os produtos com quebra de sesso por categoria.

create or replace procedure prc_produtos_categoria ascursor a is

select cod_cat, descricao from tb_categoria;cursor b(p_cod_cat) is

select descricao, precofrom tb_produtowhere cod_cat = p_cod_cat;

beginfor c in a loop

dbms_output.put_line(c.descricao);for d in b(c.cod_cat) loop

dbms_output.put_line(d.descricao || ' ' || d.preco);end loop;

end loop;end;

exec prc_produtos_categoria;

74

9. Criar um stored procedure para apagar as categorias que no possuem nenhum produto.

create or replace procedure prc_categoria_vazia asbegin

delete from tb_categoriawhere cod_cat in

(select c.cod_catfrom tb_categoria c where not exists

(select 1 from tb_produto p where p.cod_cat=c.cod_cat));

end;

75

10. Criar um stored procedure para listar os produtos cadastrados em duplicidade (pela descrio).

create or replace procedure prc_produtos_duplicados ascursor a is

select descricao, count(*) qtdefrom tb_produtogroup by descricaohaving count(*) > 1;

begindbms_output.put_line('Produtos com descricao duplicada: '); for r in a loop


end;

exec prc_produtos_duplicados;

76

11. Criar um stored procedure para listar os produtos at um determinado preo. O valor do limite dever ser passado como parmetro.

create or replace procedure prc_produto_preco(p_max number)ascursor a(p_maximo number) is

select descricao, precofrom tb_produtowhere preco

12. Criar um stored procedure para listar os produtos cujo preo esteja dentro de um intervalo. Os limites do intervalo devero ser passados como parmetros.

create or replace procedureprc_produto_faixa_preco(p_min number, p_max number)as

cursor a(p_minimo number, p_maximo number) is select descricao, precofrom tb_produtowhere preco between p_minimo and p_maximo;

beginfor r in a(p_min, p_max) loop

dbms_output.put_line(r.descricao || ' - ' || r.preco);end loop;

end;

exec prc_produto_faixa_preco(80,120);

78

13. Criar um stored procedure para listar as categorias com mais de 5 produtos.

create or replace procedure prc_listar_categorias ascursor c is

select c.descricao, count(*) qtdefrom tb_categoria c inner join tb_produto p

on c.cod_cat = p.cod_catgroup by c.descricaohaving count(*) > 5;

beginfor r in c loop


end;

79

14. Criar um stored procedure para listar todos os produtos com quebra de sesso por categoria, somente das categorias com mais de 5 produtos.

create or replace procedure prc_produtos_categoria_5 ascursor a is

select c.cod_cat, c.descricao, count(*) qtdefrom tb_categoria c inner join tb_produto p

on c.cod_cat = p.cod_catgroup by c.cod_cat, c.descricaohaving count(*) > 5;

cursor b(p_cod_cat number) isselect descricao, precofrom tb_produtowhere cod_cat = p_cod_cat;

beginfor c in a loop

dbms_output.put_line(c.descricao);for d in b(c.cod_cat) loop

dbms_output.put_line(d.descricao || ' ' d.preco);end loop;

end loop;end;

80

15. Criar um stored procedure que receba o cdigo de uma categoria e faa a excluso de seu registro, mas somente se a categoria no tiver produtos cadastrados.

create or replace procedureprc_excluir_categoria(p_cod_cat number) as

v_qtde number;cursor a is

select COUNT(*) into v_qtdefrom tb_produtowhere cod_cat = p_cod_cat;

beginif v_qtde = 0 then

delete from tb_categoria where cod_cat = p_cod_cat;dbms_output.put_line('Excluso realizada');

elsedbms_output.put_line('Excluso no realizada');

end if;end;

exec prc_excluir_categoria(1);

81

Tipos de Exceo

Os erros de programas PL/SQL ocorrem quando voc comete um erro digitando o programa ou definindo o programa. Erros de compilao Erros de execuo (run-time)

82

Erros de compilao

So geralmente erros de digitao: Esquecer um ponto e vrgula No fechar uma string literal Escrever de maneira errada um identificador

(palavra reservada) Comentar um valor lxico requerido pelas regras de

anlise

comum que um erro de compilao seja acusado na linha seguinte em que tenha ocorrido.

83

Erros de execuo (run-time)

Erros de run-time podem ocorrer na declarao, execuo e blocos de exceo PL/SQL.

Erros levantados no bloco de execuo so lanados no bloco de exceo local.

84

Funo Erros pr-definidos pelo Oracle

Erros definidos pelo usurio

SQLCODE Retorna um nmero negativo que mapeia as excees pr-definidas do Oracle.

Retorna um 1 positivo se no existeEXCEPTION_INIT PRAGMA definida. Se estiver definida, retorna um nmero negativo entre -20001 e -20999.

SQLERRM Retorna o cdigo de erro e mensagem para uma exceo.

Retorna um 1 e uma mensagem definida pelo usurio se disparada pelo comando RAISE. Caso contrrio, retorna um cdigo de erro entre -20001 e -20999 e uma mensagem de texto ajustada por RAISE_APPLICATION_INFO.

Funes Pr-definidas de Excees (1/2)

85

EXCEO ERRO QUANDO OCORREACCESS_INTO_NULL ORA-06530 Ocorre ao tentar acessar um objeto no inicializado.CASE_NOT_FOUND ORA-06592 Ocorre quando um statement CASE criado sem uma

clusula ELSE e nenhuma condio executada.CURSOR_ALREADY_OPEN ORA-06531 Ocorre ao tentar abrir um cursor que j est aberto.DUP_VAL_ON_INDEX ORA-00001 Ocorre ao tentar inserir um valor duplicado de ndice.

INVALID_CURSOR ORA-01001 Ocorre ao tentar uma operao no permitida sobre um cursor, como fechar um cursor fechado.

INVALID_NUMBER ORA-01722 Ocorre ao tentar atribuir algo no numrico para um campo numrico.

LOGIN_DENIED ORA-01017 Ocorre ao tentar entrar em um programa com um nome de usurio ou senha invlida.

NO_DATA_FOUND ORA-01403 Ocorre ao tentar usar a estrutura SELECT-INTO e o statement retornar um valor nulo.

NOT_LOGGED_ON ORA-01012 Ocorre quando um programa lanar uma chamada de banco de dados e no estiver conectado, por exemplo aps ter desconectado sua sesso.

Funes Pr-definidas de Excees (2/2)

86

EXCEO ERRO QUANDO OCORREPROGRAM_ERROR ORA-06501 Ocorre quando o Oracle no tenha pego formalmente o

erro ocorrido.ROWTYPE_MISMATCH ORA-06504 Ocorre quando sua estrutura de cursor falhar ao

concordar com sua varivel cursor ou parmetro formalSTORAGE_ERROR ORA-06500 Esse erro ocorre quando o SGA ficar sem memria ou

estiver corrompido.SYS_INVALID_ROWID ORA-01410 Ocorre ao tentar converter uma string em um valor

ROWID invlido.TIMEOUT_ON_RESOURCE ORA-00051 Ocorre quando o BD no for capaz de assegurar um

bloqueio a um recurso.TOO_MANY_ROWS ORA-01422 Ocorre ao usar o SELECT-INTO ou sub-query e a

consulta retornar mais de uma linha. VALUE_ERROR ORA-0650 Isso ocorre ao tentar atribuir uma varivel dentro de

outra que muito pequena para sustent-la.ZERO_DIVIDE ORA-01476 Isso ocorre ao tentar dividir um nmero por zero.

Diretrizes para a Captura de Excees

A palavra-chave EXCEPTION inicia a seo de tratamento de excees.

WHEN OTHERS a ltima clusula.

So permitidos vrios handlers de exceo.

Somente um handler processado antes de se sair do bloco.

permitido o uso de operadores lgicos (AND, OR, NOT)

87

Exemplo 1Consultar preo de um produto.

DECLAREv_descricao tb_produto.descricao%type := '&p_descricao';v_preco tb_produto.preco%type;

BEGINSELECT preco INTO v_precoFROM tb_produtoWHERE descricao = v_descricao;dbms_output.put_line('Preo: ' || v_preco);

EXCEPTIONWHEN NO_DATA_FOUND THEN

dbms_output.put_line('NENHUM REGISTRO ENCONTRADO.');WHEN TOO_MANY_ROWS THEN

dbms_output.put_line('PRODUTOS COM MESMA DESCRIO.');WHEN OTHERS THEN

dbms_output.put_line('ERRO DESCONHECIDO.' );END;

88

Usando Stored Procedure com parmetro OUT

CREATE OR REPLACE PROCEDURE prc_buscaProduto(p_codigo IN number, p_descricao OUT varchar2, p_preco OUT number) is

BEGINSELECT descricao, precoINTO p_escricao, p_precoFROM tb_produtoWHERE cod_prod = p_codigo;

EXCEPTIONWHEN NO_DATA_FOUND THEN

p_descricao := 'Produto no localizado';p_preco := 0;

END;89

DECLAREv_codigo tb_produto.cod_prod%TYPE; v_descricao tb_produto.descricao%TYPE;v_preco tb_produto.preco%TYPE;

BEGINv_codigo := 12;prc_buscaProduto(v_codigo, v_descricao, v_preco);dbms_output.put_line('Cdigo: ' || v_codigo);dbms_output.put_line('Descrio: ' || v_descricao);dbms_output.put_line('Preo: ' || v_preco);

END;

90

Usando Stored Procedure com parmetro OUT (cont.)

Exemplo 2Chave primria duplicada.

DECLAREv_descricao tb_produto.descricao%TYPE;v_preco tb_produto.preco%TYPE;v_cod_cat tb_produto.cod_cat%TYPE;

BEGINv_descricao := 'Pen drive'; v_preco := 44.99; v_cod_cat := 1;INSERT INTO tb_produtoVALUES(1, v_descricao, v_preco, v_cod_cat);dbms_output.put_line ('Insero executada com sucesso.');

EXCEPTIONWHEN DUP_VAL_ON_INDEX THEN

dbms_output.put_line ('Cdigo j cadastrado.' );WHEN Others THEN

dbms_output.put_line ('Erro no cadastramento.' );END;

91

Exemplo 3Valor muito grande para o campo.

DECLAREv_descricao VARCHAR(10);v_preco number(3,2);

BEGINv_descricao := 'Pen drive de 128GB e USB3'; v_preco := 144.99; dbms_output.put_line ('Descrio: ' || v_descricao);dbms_output.put_line ('Preo: ' || v_preco);

EXCEPTIONWHEN VALUE_ERROR THEN

dbms_output.put_line (SQLERRM);WHEN Others THEN

dbms_output.put_line (SQLERRM);END;

92

Exemplo 4Valor numrico invlido.

DECLAREv_descricao VARCHAR(10);v_preco number(3,2);

BEGINv_descricao := 'Pen drive'; v_preco := 'A49.99'; dbms_output.put_line ('Descrio: ' || v_descricao);dbms_output.put_line ('Preo: ' || v_preco);

EXCEPTIONWHEN INVALID_NUMBER THEN

dbms_output.put_line ('Valor numrico invlido');WHEN Others THEN


93

Exemplo 5Diviso por ZERO.

DECLAREvalor number(8,2);

BEGINvalor := 100 / 0;dbms_output.put_line ('Resultado: ' || valor);

EXCEPTIONWHEN ZERO_DIVIDE THEN

dbms_output.put_line (SQLERRM);WHEN Others THEN


94

Possvel gravar os erros ocorridosDECLARE v_code number;

v_erro varchar(100);BEGIN...EXCEPTION...

WHEN OTHERS THENROLLBACK;v_code := SQLCODE;v_erro := SQLERRM;INSERT INTO tb_errosVALUES(v_code, v_erro);

END;

Obs. preciso criar a tabela de erros.create table tb_erros (

codigo_erro number, mensagem_erro varchar2(100)

);95

Declarando excees do usurio

Uma exceo declarada como qualquer outra varivel PL/SQL.

DECLARE e EXCEPTION;

BEGINRAISE e;dbms_output.put_line('Essa linha no ser executada');

EXCEPTIONWHEN OTHERS THENIF SQLCODE = 1 THEN

dbms_output.put_line('SQLERRM');END IF;

END;

Resposta: [User-Defined Exception]

96

Exemplo 1Alterar um produto

DECLAREe_invalid_product EXCEPTION;

BEGINUPDATE tb_produtoSET descricao = '&product_description' WHERE cod_prod = &product_number;

IF SQL%NOTFOUND THENRAISE e_invalid_product;

END IF;

COMMIT;EXCEPTION

WHEN e_invalid_product THEN DBMS_OUTPUT.PUT_LINE('Cdigo de produto invlido.');

END;

97

Exemplo 2Cadastrar Categoria

DECLAREv_codigo tb_categoria.cod_cat%Type;v_descricao tb_categoria%Type;campos_obrigatorios EXCEPTION;

BEGIN-- v_codigo := 1;

v_descricao := 'Material Esportivo';IF (codigo IS NULL) OR (descricao IS NULL) THEN

RAISE campos_obrigatorios;ELSE

INSERT INTO tb_categoria(cod_cat, descricao) VALUES (v_codigo, v_descricao);END IF;

EXCEPTIONWHEN campos_obrigatorios THEN

dbms_output.put_line ('Preencher todos os campos.');WHEN Dup_Val_On_Index THEN

dbms_output.put_line ('Cdigo j cadastrado.' );WHEN Others THEN

dbms_output.put_line ('Erro no cadastramento.' );END;

98

PRAGMA EXECEPTION_INIT

O PRAGMA EXCEPTION_INIT associa um nome de exceo a um nmero de erro do Oracle. Capturar erro -2292, violao de restrio de integridade.

DECLARE e_categoria EXCEPTION;PRAGMA EXCEPTION_INIT (e_categoria, -2292);v_cod_cat tb_categoria.cod_cat%TYPE := &p_cod_cat;

BEGINDELETE FROM tb_categoria WHERE cod_cat = v_cod_cat;COMMIT;

EXCEPTION WHEN e_categoria THEN

DBMS_OUTPUT.PUT_LINE('Categoria no pode ser removida. Existem produtos relacionados.');

END;

99

RAISE_APPLICATION_ERROR

O RAISE_APPLICATION_ERROR aborta a execuo.

DECLAREsem_comissao EXCEPTION; -declara uma exception

BEGINIF comissao IS NULL THEN

RAISE sem_comissao; -chama a exceptionEND IF;

bonus := (salario * 0.10) + (comissao * 0.15);EXCEPTION

WHEN sem_comissao THENRAISE_APPLICATION_ERROR(-21000,No h comisso!);

END;

100

Exerccios

1. Criar um bloco annimo que receba como parmetro o cdigo de um produto e o percentual de aumento. Deve-se atualizar o preo do produto. Caso o cdigo informado no exista ou o preo atual seja nulo, deve-se gerar uma exceo.

2. Alterar o exerccio anterior trabalhando com stored procedure.3. Criar um stored procedure que receba o cdigo de um produto a ser

excludo. Caso o produto no exista, gerar uma exceo.4. Criar um stored procedure que receba a descrio, preo e categoria

de um produto. Verificar:a) Se todos os campos foram preenchidosb) Se o tamanho do campo descrio compatvel.c) Se o tipo do campo preo vlido.d) Se a categoria informada existe.e) Se outro erro ocorrer, apresentar o cdigo e a descrio do erro.

101

102

Trabalho (individual ou em dupla)Sistema para cotao de preos (1/3)

O diagrama de classes acima representa um sistema para cotao de preos, no qual: cada cotao possui um produto; cada cotao recebe propostas de diversos fornecedores; cada fornecedor poder enviar proposta para uma ou mais cotaes.

COTACAOcod_cotqtdedata_iniciodata_terminoPROPOSTA

valorprazo_entrega

0..N 0..NFORNECEDORcod_fornrazao_socialnome_fantasiacnpjieenderecocidadetelefone

PRODUTOcod_proddescricaoprecoestoque

0..N1..1

Trabalho:Sistema para cotao de preos (2/3)

1. Criar um bloco annimo para listas os fornecedores cadastrados.

2. Criar um bloco annimo para listas todas as cotaes cadastradas (com descrio do produto, qtde e perodo)

3. Criar um bloco annimo para listas as cotaes em aberto (com descrio do produto, qtde cotada e perodo).

4. Criar um bloco annimo que apresente a relao de produtos com as respectivas cotaes j realizadas.

5. Criar um stored procedure para cadastrar novos fornecedores. O cdigo do fornecedor dever ser gerado automaticamente e deve-se verificar se o cnpj j est cadastrado.

103

Trabalho:Sistema para cotao de preos (3/3)

6. Criar um stored procedure que receba o cdigo de uma cotao e apresente as propostas recebidas (valor, prazo, razo social e cnpj do fornecedor)

7. Criar uma stored procedure que receba o cdigo de um produto e apresente as cotaes j realizadas.

8. Criar um stored procedure que receba o cnpj de um fornecedor e apresente as proposta feitas (valor, prazo, produto e qtde)

9. Criar um stored procedure que receba como parmetro uma data e apresente todas as cotaes ativas nesta data.

10. Criar um stored procedure que apresente as cotaes encerradas, abertas ou ainda aguardando incio. Deve-se passar como parmetro qual filtro dever ser aplicado.

104

Function (funo) Uma funo um programa PL/SQL que sempre

retorna um valor aps sua execuo. Assim como em Stored Procedure, podemos passar parmetros para uma funo. A diferena que as funes so chamadas como parte de uma expresso.

Sintaxe: CREATE [OR REPLACE] FUNCTION

[ ( ) ] RETURN IS

: ;BEGIN

;RETURN ;

END;

105

Como executar uma funo:

Com chamada: CALL function_name(parameter)

INTO target_variable_name;

Dentro de blocos PL/SQL: target_variable_name := function_name(parameter);

Com SQL: SELECT function_name(parameter)

FROM table_name;

Obs.: caso a funo no tenha parmetro, basta omitir.

106

Exemplo 1: Funo que recebe um valor e retorna o seu quadrado.

CREATE FUNCTION fn_quadrado(n IN number) RETURN number IS

q number;BEGIN

q := n * n;RETURN q;

END;

Para executar: SELECT fn_quadrado(5) FROM dual;

107

Exemplo 2: Funo que retorne o dia de amanh.

CREATE OR REPLACE FUNCTION fn_amanhaRETURN date ISd date;BEGIN

SELECT SYSDATE+1 INTO d FROM DUAL;RETURN d;

END;

Para executar: SELECT fn_amanha FROM dual;

108

Exemplo 3: Retornar a mdia de preos dos produtos.

CREATE OR REPLACE FUNCTION fn_media_precosRETURN number ISmedia number;BEGIN

SELECT AVG(preco) INTO media FROM tb_produto;

RETURN media;END;

Para executar: SELECT fn_media_precos FROM dual;

109

Exemplo 4: Recebe preo do produto e percentual de desconto, retorna o valor da promoo.

CREATE OR REPLACE FUNCTION fn_promocao(preco number, percentual number)

RETURN number ISBEGIN

RETURN preco - (preco * percentual / 100);END;

Teste 1: SELECT fn_promocao(100, 5) FROM dual;

Teste 2: SELECT descricao, preco, fn_promocao(preco, 5) promocaoFROM tb_produto;

obs.: a funo executada para cada registro encontrado na tabela tb_produto.

110

Exemplo 5: Calcular desconto de 3% se o preo custar at 100 reais, de 5% para at 1000 reais e de 8% se for acima de 1000 reais.

CREATE OR REPLACE FUNCTION fn_promocao(preco number) RETURN number ISpromocao number;BEGIN

if preco

Exerccios1. Crie uma funo que receba um nmero e retorne seu valor fatorial. 3! = 1x2x32. Criar uma funo que receba um nome completo e retorne apenas o primeiro

nome.

3. Crie uma funo que receba o nmero do ms e retorne o nome deste.4. Crie uma funo que receba como parmetro uma data (dia, ms e ano) e

retorne a data por extenso. Exemplo: 12/03/2008 12 de maro de 20085. Criar uma funo que receba uma data e retorne a idade em anos.6. Criar uma funo para recuperar o nmero de horas e minutos entre duas

datas.7. Criar uma funo que receba um CPF e retorne se vlido. 8. Criar uma funo que receba como parmetro o valor inicial e o valor final e

retorne a quantidade de produtos com preo dentro do intervalo.9. Criar uma funo que receba a descrio de um produto e retorne se o produto

est cadastrado ou no.10. Criar uma funo receber o preo de um produto e o classificar como baixo,

mdio ou alto, respectivamente para valores at 100, acima de 100 at 1000 e acima de 1000.

112

1. Criar uma funo que receba um nmero e retorne seu fatorial.

create or replace FUNCTION fatorial(n IN number) RETURN number IS

f number := 1;

BEGINFOR i IN 1..n LOOP

f := f * i;END LOOP;

RETURN f;END;

SELECT fatorial(5) FROM dual;

113

2. Criar uma funo que receba um nome completo e retorne apenas o primeiro nome.

CREATE OR REPLACE FUNCTION primeiro_nome(p_nome varchar)RETURN varchar ISv_nome varchar(80);v_posicao number;BEGIN

v_posicao := instr(ltrim(p_nome),' ');if v_posicao = 0 then

v_nome := p_nome;else

v_nome := substr(ltrim(p_nome), 1, v_posicao-1);end if;return v_nome;

END;

SELECT nome, primeiro_nome(nome) FROM tb_funcionario;114

3. Crie uma funo que receba o nmero do ms e retorne o nome deste.

CREATE OR REPLACE FUNCTION fn_mes_extenso(p_mes number) RETURN varchar ISv_mes varchar(30);

BEGINCASE

WHEN p_mes= 1 THEN v_mes := 'Janeiro';WHEN p_mes= 2 THEN v_mes := 'Fevereiro';WHEN p_mes= 3 THEN v_mes := 'Maro';WHEN p_mes= 4 THEN v_mes := 'Abril';WHEN p_mes= 5 THEN v_mes := 'Maio';WHEN p_mes= 6 THEN v_mes := 'Junho';WHEN p_mes= 7 THEN v_mes := 'Julho';WHEN p_mes= 8 THEN v_mes := 'Agosto';WHEN p_mes= 9 THEN v_mes := 'Setembro';WHEN p_mes=10 THEN v_mes := 'Outubro';WHEN p_mes=11 THEN v_mes := 'Novembro';WHEN p_mes=12 THEN v_mes := 'Dezembro';ELSE v_mes := 'Invlido!';

END CASE;RETURN v_mes;

END;

SELECT fn_mes_extenso(8) FROM dual; --Agosto115

4. Crie uma funo que receba como parmetro uma data (dia, ms e ano) e retorne a data por extenso. Exemplo: 12/03/2008 12 de maro de 2008.

CREATE OR REPLACE FUNCTION fn_data_extenso(p_data date)RETURN varchar ISBEGIN

RETURN to_char(p_data, 'DD de MONTH de YYYY','nls_date_language=portuguese');

END;

SELECT fn_data_extenso(SYSDATE) FROM dual;

116

-- maneira de separar a data-- v_dia := EXTRACT(DAY FROM p_data);-- v_mes := EXTRACT(MONTH FROM p_data);-- v_ano := EXTRACT(YEAR FROM p_data);

5. Criar uma funo que receba uma data e retorne a idade em anos.

CREATE OR REPLACE FUNCTION fn_idade(p_data date)RETURN number ISv_idade number;BEGIN

SELECT floor(months_between(SYSDATE, p_data) / 12) INTO v_idadeFROM dual;

RETURN v_idade;END;

SELECT fn_idade('01/05/2010') FROM dual;

117

6. Criar uma funo para recuperar o nmero de horas e minutos entre duas datas. (1/2)

CREATE OR REPLACE FUNCTION fn_diferenca_datas(p_inicial date, p_final date) IS

RETURN varchar;

diferenca number;horas number;minutos number;

BEGIN

SELECT p_final-p_inicial INTO diferenca FROM dual;SELECT trunc(diferenca)*24 + trunc((diferenca - trunc(diferenca))*24),

trunc((((diferenca - trunc(diferenca))*24) trunc((diferenca - trunc(diferenca))*24))*60)

INTO horas, minutosFROM dual;RETURN horas || 'h' || minutos || 'm';

END;

118

6. Criar uma funo para recuperar o nmero de horas e minutos entre duas datas. (2/2)

Exemplo de uso: DECLARE

data_inicial date;data_final date;resposta varchar(20);

BEGINdata_inicial := to_date('21/11/2010 18:57:00','dd/mm/yyyy hh24:mi:ss');data_final := to_date('22/11/2010 21:40:25','dd/mm/yyyy hh24:mi:ss');resposta := fn_diferenca_datas(data_inicial, data_final);dbms_output.put_line(resposta);

END;

119

7. Criar uma funo que receba um CPF e retorne se vlido. (1/2)

CREATE OR REPLACE FUNCTION fn_valida_cpf (p_cpf IN CHAR) RETURN BOOLEAN IS

m_total NUMBER := 0;m_digito NUMBER := 0;

BEGINFOR i IN 1 .. 9 LOOP

m_total := m_total + SUBSTR (p_cpf, i, 1) * (11 - i);END LOOP;

m_digito := 11 - MOD (m_total, 11);

IF m_digito > 9 THENm_digito := 0;

END IF;

IF m_digito != SUBSTR (p_cpf, 10, 1) THENRETURN FALSE;

END IF;

120

7. Criar uma funo que receba um CPF e retorne se vlido. (2/2)

m_digito := 0;m_total := 0;

FOR i IN 1 .. 10 LOOPm_total := m_total + SUBSTR (p_cpf, i, 1) * (12 - i);

END LOOP;

m_digito := 11 - MOD (m_total, 11);

IF m_digito > 9 THENm_digito := 0;

END IF;

IF m_digito != SUBSTR (p_cpf, 11, 1) THENRETURN FALSE;

END IF;

RETURN TRUE;END;

121

8. Criar uma funo que receba como parmetro o valor inicial e o valor final e retorne a quantidade de produtos com preo dentro do intervalo.

CREATE OR REPLACE FUNCTION fn_qtde_produtos(p_inicial NUMBER, p_final NUMBER)

RETURN number ISv_qtde number;

BEGINSELECT COUNT(*) INTO v_qtdeFROM tb_produtoWHERE preco BETWEEN p_inicial AND p_final;

RETURN v_qtde;END;

Exemplo de uso:DECLARE

v_qtde number(10);BEGIN

v_qtde := fn_qtde_produtos(50,80);dbms_output.put_line(v_qtde || ' produtos encontrados!');

END;

122

9. Criar uma funo que receba a descrio de um produto e retorne se o produto est cadastrado ou no.

CREATE OR REPLACE FUNCTION fn_procurar(p_descricao IN varchar) RETURN boolean IS

v_qtde number;BEGIN

SELECT COUNT(*) INTO v_qtdeFROM tb_produtoWHERE descricao = p_descricao;

RETURN (v_qtde0);END;

Exemplo de uso:DECLARE

v_descricao varchar(100) := 'Mouse';BEGIN

IF(fn_procurar(v_descricao)) THENdbms_output.put_line('Produto no encontrado!');

ELSEdbms_output.put_line('Produto encontrado!');

END IF;END;

123

10. Receber o preo de um produto e o classificar como baixo, mdio ou alto, respectivamente para valores at 100, acima de 100 at 1000 e acima de 1000.

CREATE OR REPLACE FUNCTION fn_classificar(p_preco IN number) RETURN varchar ISBEGIN

IF p_preco

Package (Pacote)

125

Package so reas de armazenamentos de subprogramas (stored procedure e function), constantes, variveis e cursores em PL/SQL que podero ser compartilhados com outros aplicativos.

Em outras palavras, os pacotes funcionam como bibliotecas do PL/SQL.

Existem packages nativos do Oracle e possvel criar novos pacotes.

Um PACKAGE constitudo de duas partes: PACKAGE SPECIFICATION PACKAGE BODY

Partes do Package

PACKAGE SPECIFICATION Tem como funo de criar a interface (prottipo) de suas

aplicaes e definir os tipos de variveis, cursores, excees, nomear rotinas, e funes, tudo que se define na parte de SPECIFICATION do seu PACKAGE poder ser compartilhado com outros scripts ou programasem SQL e PL/SQL.

PACKAGE BODY Tem como funo executar por completo todas as suas

rotinas, cursores e etc., que voc definiu na SPECIFICATION. Alm disso no BODY voc poder criar detalhes e declaraes que ficaro invisveis para outras aplicaes.

126

Partes do Package: visibilidade

127

Especificao do Pacote

CREATE OR REPLACE PACKAGE pkg_comissao IScomissao_padrao NUMBER := 0.10;PROCEDURE mudar_comissao(nova_comissao NUMBER);

END pkg_commissao;

128

comissao_padrao uma varivel global inicializada com 0.10 (10%).

mudar_comissao um procedimento pblico usado para redefinir a comisso fixa com base em algumas regras de negcio. Ele implementado no corpo do pacote.

Corpo do Pacote

CREATE OR REPLACE PACKAGE BODY pkg_comissao ISfunction fn_validar(comissao number) RETURN boolean ISmax_comissao tb_vendedor.comissao%TYPE;begin

select max(comissao) into max_comissao from tb_vendedor;RETURN (comissao BETWEEN 0.0 AND max_comissao);

end fn_validar;procedure prc_mudar_comissao(nova_comissao number) ISbegin

if fn_validar(nova_comissao) thencomissao_padrao := nova_comissao;

elseraise_application_error(-20210, 'Comisso Invlida!');

end if;end prc_mudar_comissao;

END pkg_comissao;

129

Utilizando o pacote: Chamando um procedimento a partir do SQL*Plus:

EXEC pkg_comissao.mudar_comissao(0.15);

Chamando uma procedimento em um esquema diferente: EXEC scott.pkg_comissao.mudar_comissao(0.15);

Para remover a especificao do pacote e do corpo: DROP PACKAGE pkg_comissao;

Para remover somente o corpo do pacote: DROP PACKAGE BODY pkg_comissao;

Para ver a especificao do pacote no dicionrio de dados: SELECT text FROM user_source

WHERE name = 'PKG_COMISSAO' AND type = 'PACKAGE';

Para ver o corpo do pacote: SELECT text FROM user_source

WHERE name = 'PKG_COMISSAO' AND type = 'PACKAGE BODY';

130

Orientaes para Escrita de Pacotes

Construa pacotes para uso geral.

Defina a especificao do pacote antes do corpo. A especificao do pacote deve conter apenas as

construes que voc quer que seja pblico. Mudanas nas especificaes do pacote requer

recompilao de cada subprograma referncia. A especificao do pacote deve conter o mnimo de

construes possvel.

131

Vantagens no Uso de Pacotes Facilita a manuteno:

Mantm a funcionalidade e a lgica juntas

Design fcil: Codificao da especificao e do corpo separadamente

Esconde informaes: Somente as declaraes na especificao do pacote so visveis e

acessveis para as aplicaes.

Construes no corpo do pacote esto privadas e inacessveis.

Funcionalidades adicionais: cursores

Melhor desempenho: O pacote inteiro carregado na memria naprimeira vez que for

referenciado.

Existe apenas uma cpia na memria para todos os usurios.

Sobrecarga: mltiplos subprogramas com o mesmo nome

132

Exerccios

1. Criar uma package chamado pkg_math com a seguinte estrutura:a) Uma varivel global PI com o valor 3.14159265b) Uma funo que receba como parmetro o raio, calcule e

retorne a rea de um crculo (pi * r2)c) Uma funo que receba como parmetro o raio e a altura

de um cilindro, calcule e retorne seu volume (pi * r2 * h)d) Um stored procedure para calcular o volume de uma esfera

(4/3 * pi * r3). Deve-se passar 2 parmetros: o primeiro parmetro (tipo IN) com o valor do raio da esfera e o segundo parmetro (tipo OUT) receber o volume calculado.

133

Exerccio 1 - Soluo create or replace package pkg_math is

PI number := 3.14159265;function fn_area_circulo(raio number) return number;function fn_volume_cilindro(raio number, altura number) return number;procedure prc_volume_esfera(raio IN number, volume OUT number);

end;

create or replace package body pkg_math isfunction fn_area_circulo(raio number) return number isbegin

return PI * (raio*raio);end;function fn_volume_cilindro(raio number, altura number) return number isbegin

return PI * (raio*raio) * altura;end;procedure prc_volume_esfera(raio IN number, volume OUT number) isbegin

volume := 4/3 * PI * (raio*raio*raio);end;

end;

134

Exerccio 1 Soluo (cont.)

Declarer number := 10;a number := 20;x number;

Beginx := pkg_math.fn_area_circulo(r);dbms_output.put_line('Um circulo de raio ' || r || ' tem rea ' || x);

x := pkg_math.fn_volume_cilindro(r, a);dbms_output.put_line('Um cilindro de raio ' || r || ' e altura ' || a || ' tem volume ' || x);

pkg_math.prc_volume_esfera(r, x);dbms_output.put_line('Uma esfera de raio ' || r || ' tem volume ' || x);

End;

135

Exerccios

2. Criar uma package chamado pkg_date com a seguinte estrutura:a) Um campo privado chamado idioma com o valor inicial

'portuguese'b) Uma funo que receba como parmetro o novo idioma,

que pode ser portuguese, english ou spanish, e altere a varivel global idioma.

c) Uma funo que retorne o valor do idioma atual.d) Um stored procedure para obter a data por extenso. Deve-

se passar 2 parmetros: o primeiro parmetro (tipo IN) com uma data no formato dd/mm/yyyy e o segundo parmetro (tipo OUT) receber a data por extenso no idioma atual.

136

Exerccio 2 - Soluo create or replace package pkg_date is

function fn_set_idioma(i varchar) return boolean;function fn_get_idioma return varchar;procedure prc_get_date(d IN date, extenso OUT varchar);

end;

create or replace package body pkg_date isidioma varchar(30) := 'portuguese';function fn_set_idioma(p_idioma varchar) return boolean isbegin

if(p_idioma in('portuguese','spanish','english')) thenidioma := p_idioma;return true;

elsereturn false;

end if;end;function fn_get_idioma return varchar isbegin

return idioma;end;procedure prc_get_date(d IN date, extenso OUT varchar) isbegin

extenso := to_char(d,'DD/MONTH/YYYY', 'nls_date_language='||idioma);end;

end;

137

Exerccio 2 Soluo (cont.)

set serveroutput on;declare extenso varchar(80);begin

if(pkg_date.fn_set_idioma('english')) thenpkg_date.prc_get_date(sysdate,extenso);dbms_output.put_line(extenso || ' (' || pkg_date.fn_get_idioma || ')');

elsedbms_output.put_line('Erro ao alterar idioma');

end if;end;

138

Trigger

Trigger (gatilho) um programa PL/SQL que disparado automaticamente a partir da execuo de seu evento associado.

Os triggers so usados para automatizar tarefas.

Quando existe uma sequencia de comandos a ser executado, o primeiro evento pode disparar um trigger que realiza as demais tarefas.

139

Trigger (cont.)

Como principais motivos para o uso de database trigger, podemos citar: gerao automtica de valores derivados

de colunas preveno de transaes invlidas reforar regras de negcio complexas prover auditoria gerar estatsticas sobre acesso s tabelas prover log de transaes

140

Trigger (cont.)

Sintaxe: CREATE [OR REPLACE] TRIGGER BEFORE | AFTER ON [FOR EACH ROW]BEGIN

;END;

BEFORE | AFTER - indicao o momento em que o trigger ser disparado. BEFORE indica que o corpo do trigger ser executado antes

do comando que a disparou. AFTER indica que o corpo do trigger ser executado aps o

comando que a disparou.

141

Um trigger composto por 4 partes:1. MOMENTO

BEFORE (tabela) AFTER (tabela) INSTEAD OF (view)

2. EVENTO INSERT UPDATE DELETE

3. NVEL Instruo (STATEMENT) Linha (ROW)

4. CORPO Bloco de instrues PL/SQL

142

Nvel de Trigger

Triggers podem ser disparadas: No nvel de linha (Row level trigger) ou No nvel de instruo (Statement level trigger).

O cdigo de uma trigger em nvel de linha (FOR EACH ROW) executado uma vez para cada linha da tabela em que est sendo realizada uma instruo SQL. Se voc executar uma instruo UPDATE que altera 10

linhas, o cdigo do trigger ser executado 10 vezes.

J na trigger em nvel de instruo, o seu cdigo executado apenas uma vez para qualquer instruo SQL Este tipo de trigger ser executado apenas 1 vez, mesmo

para uma instruo UPDATE que altera as 10 linhas.

143

Registros Especiais :NEW e :OLD

Os registros especiais :NEW e :OLD armazenam temporariamente os valores do ltimo registro manipulado pelo SGBD.

Ao inserir, criado o registro :NEW Ao apagar, criado o registro :OLD Ao atualizar, so criados os 2 registros :NEW e :OLD

144

DML :OLD :NEWINSERT NULO Valores novosDELETE Valores antigos NULOUPDATE Valores antigos Valores Novos

Exemplo 1: Trigger para garantir que a descrio de produto seja gravada em letras maisculas.

CREATE OR REPLACE TRIGGER trg_produto_descricaoBEFORE INSERT ON tb_produtoFOR EACH ROWBEGIN

:NEW.descricao := UPPER(:NEW.descricao);END;

145

Para testar, preciso inserir um novo registro:INSERT INTO tb_produto(cod_prod, descricao, preco)

VALUES(100, 'caneta', 2.50);COMMIT;

Para exibir os erros de compilao: SHOW ERRORS;

Exemplo 2: Trigger para gerao automtica do cdigo.

CREATE SEQUENCE seq_produto START WITH 101;CREATE OR REPLACE TRIGGER trg_produto_codigoBEFORE INSERT ON tb_produtoFOR EACH ROWBEGIN

SELECT seq_produto.NEXTVAL INTO :NEW.cod_prodFROM DUAL;

END;

146

Para testar, vamos inserir um novo registro sem cdigo:INSERT INTO tb_produto(descricao, preco)

VALUES('lpis', 1.25);COMMIT;

Exemplo 3: Trigger para, caso o preo do produto seja negativo, converter para positivo.

CREATE OR REPLACE TRIGGER trg_produto_precoBEFORE INSERT ON tb_produtoFOR EACH ROWBEGIN

IF :NEW.preco < 0 THEN:NEW.preco := :NEW.preco * (-1);

END IF;

END;

147


VALUES('borracha', -2.99);COMMIT;

Exemplo 4: Caso o campo estoque esteja vazio, gravar o valor ZERO utilizando uma trigger.

CREATE OR REPLACE TRIGGER trg_produto_estoqueBEFORE INSERT ON tb_produtoFOR EACH ROWBEGIN

IF :NEW.estoque IS NULL THEN:NEW.estoque := 0;

END IF;

END;

148


VALUES('mouse', 45.00);COMMIT;

ALTER TABLE tb_produto ADD estoque integer;

Exemplo 5: Trigger para remover espaos em branco na descrio ao inserir ou atualizar.

CREATE OR REPLACE TRIGGER trg_produto_descricaoBEFORE INSERT OR UPDATE OF descricao ON tb_produto

FOR EACH ROWBEGIN

:NEW.descricao := TRIM(:NEW.descricao);END;

149

UPDATE tb_produtoSET descricao = ' MOUSE ', preco = 44.99WHERE descricao = 'MOUSE';COMMIT;

Ao indicar UPDATE OF descricao, o gatilho s ser disparado se a descrio for atualizada. Desta forma, se apenas o preo for modificado, essa trigger no ser executada.

Exemplo 6: Controle de histrico de preos. Ao inserir um novo produto ou atualizar seu preo, deve-se lanar em histrico.

Antes, vamos criar a tabela de histrico:

CREATE TABLE tb_produto_historico (cod_prod_hist integer not null primary key,cod_prod integer not null,preco decimal(8,2),data date,FOREIGN KEY(cod_prod) REFERENCES tb_produto(cod_prod)

);CREATE SEQUENCE seq_produto_historico START WITH 1;

150

Exemplo 6: Controle de histrico de preos. Ao inserir um novo produto ou atualizar seu preo, deve-se lanar em histrico.

CREATE OR REPLACE TRIGGER trg_produto_historicoAFTER INSERT OR UPDATE OF preco ON tb_produtoFOR EACH ROWBEGIN

INSERT INTO tb_produto_historico(cod_prod_hist,cod_prod, preco,data)

VALUES(seq_produto_historico.NEXTVAL,:NEW.cod_prod, :NEW.preco, SYSDATE);

END;

151

Ateno: A execuo de triggers no so retroativas. Ou seja, produtos inseridos anteriormente no sero lanados no histrico.

Exerccio 6 Para testar, vamos inserir e atualizar alguns produtos. Depois basta verificar se a tabela de histrico foi populada.

INSERT INTO tb_produto(descricao,preco,estoque)VALUES('Teclado', 50.00, 25);

UPDATE tb_produtoSET preco = 55.00WHERE descricao = 'TECLADO';

UPDATE tb_produtoSET preco = 60.00WHERE descricao = 'TECLADO';

UPDATE tb_produtoSET preco = 49.99WHERE descricao = 'MOUSE';

COMMIT;

152

Exemplo 7: Iremos registrar, tambm, o usurio responsvel pela alterao de preo.

ALTER TABLE tb_produto_historicoADD usuario varchar(100);

CREATE OR REPLACE TRIGGER trg_produto_historicoAFTER INSERT OR UPDATE OF preco ON tb_produtoFOR EACH ROWBEGIN

INSERT INTO tb_produto_historico(cod_prod_hist,cod_prod,preco,data,usuario)

VALUES(seq_produto_historico.NEXTVAL,:NEW.cod_prod, :NEW.preco, SYSDATE, USER);

END;

153

Exemplo 8: Impedir que a tabela de produtos seja manipulada (INSERT, UPDATE e DELETE) fora do horrio comercial.

CREATE OR REPLACE TRIGGER trg_horario_comercialBEFORE INSERT OR UPDATE OR DELETE ON tb_produtoFOR EACH ROWBEGIN

IF(TO_CHAR(SYSDATE,'HH24:MM') < '08:00'OR TO_CHAR(SYSDATE,'HH24:MM') > '18:00'OR TO_CHAR(SYSDATE,'DY') IN ('SAB', 'DOM')) THENRAISE_APPLICATION_ERROR(-20500,

'Operao no permitida nesse horrio');END IF;

END;

154

Habilitando e Desabilitando um trigger

Algumas vezes, quando se precisa dar manuteno na base de dados, precisando desligar temporariamente alguns triggers, para isso, usamos: ALTER TRIGGER DISABLE;

ou ALTER TABLE DISABLE ALL TRIGGERS;

Para novamente habilitar o trigger, usamos: ALTER TRIGGER ENABLE;

Caso se queira excluir definitivamente um trigger: DROP TRIGGER ;

155

Exerccios1. Elaborar um trigger para gerar automaticamente o cdigo do

funcionrio.2. Elaborar um trigger para formatar nome e cargo do funcionrio em

maisculas.3. Elaborar um trigger para no aceitar que o salrio seja reduzido.4. Elaborar um trigger para no permitir que o cdigo seja alterado.5. Elaborar um trigger para no permitir que um funcionrio seja excludo.6. Criar um trigger para registrar todo o histrico de cargos dos

funcionrios, registrando o cargo e a data da mudana.7. Alterar a tabela e a trigger que mantm o histrico para que seja

registrado o usurio que realizou a alterao de cargo.8. Criar uma tabela e um trigger para controlar o histrico de salrios dos

funcionrios.9. Criar um trigger para no permitir que o cargo de um funcionrio seja

alterado 2 vezes no mesmo dia.10. Criar um trigger para no permitir que o salrio de um funcionrio seja

atualizado em menos de 3 meses.156

Tabelas de Funcionrios e HistricosCREATE TABLE tb_funcionario(

cod_func integer not null primary key,nome varchar(80),cargo varchar(40),salario decimal(8,2),situacao char(1) default 'A', check(situacao IN('A','I')) -- Ativo ou Inativo

);CREATE TABLE tb_historico_cargo(

cod_func integer not null,cargo varchar(40),data date

);CREATE TABLE tb_historico_salario(

cod_func integer not null,salario decimal(8,2),data date

);157

Para simplificar:- cargo foi criado como um atributo.- omitiu-se as PKs e FKs no histrico.

Exerccio 1 Elaborar um trigger para gerar automaticamente o cdigo do funcionrio.

CREATE SEQUENCE sq_funcionarioSTART WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER tg_funcionario_codigoBEFORE INSERT ON tb_funcionarioFOR EACH ROWBEGIN

SELECT sq_funcionario.NEXTVALINTO :NEW.cod_funcFROM dual;

END;

158

Exerccio 2 Elaborar um trigger para formatar nome e cargo do funcionrio em maisculas.

CREATE OR REPLACE TRIGGER tg_funcionario_maiusculasBEFORE INSERT OR UPDATE ON tb_funcionarioFOR EACH ROWBEGIN

:NEW.nome := UPPER(:NEW.nome);:NEW.cargo := UPPER(:NEW.cargo);

END;

159

Exerccio 3 Elaborar um trigger para no aceitar que o salrio seja reduzido.

CREATE OR REPLACE TRIGGER tg_funcionario_salarioBEFORE UPDATE OF salario ON tb_funcionarioFOR EACH ROWBEGIN

IF :NEW.salario < :OLD.salario THENRAISE_APPLICATION_ERROR(-20500,

'No permitido reduzir o salrio');END IF;

END;

160

Exerccio 4 Elaborar um trigger para no permitir que o cdigo seja alterado.

CREATE OR REPLACE TRIGGER tg_funcionario_alterar_codigoBEFORE UPDATE OF cod_func ON tb_funcionarioFOR EACH ROWBEGIN

RAISE_APPLICATION_ERROR(-20501,'No permitido alterar o cdigo');

END;

161

Exerccio 5 Elaborar um trigger para no permitir que um funcionrio seja excludo.

CREATE OR REPLACE TRIGGER tg_funcionario_alterar_codigoBEFORE DELETE ON tb_funcionarioFOR EACH ROWBEGIN

RAISE_APPLICATION_ERROR(-20501,'No permitido excluir fisicamente um registro');

END;

162

Exerccio 6 Criar um trigger para registrar todo o histrico de cargos dos funcionrios, registrando o cargo e a data da mudana.

CREATE OR REPLACE TRIGGER tg_historico_cargoAFTER INSERT OR UPDATE OF cargo ON tb_funcionarioFOR EACH ROWBEGIN

INSERT INTO tb_historico_cargo(cod_func, cargo, data)VALUES(:NEW.cod_func, :NEW.cargo, SYSDATE);

END;

163

Exerccio 7 Alterar a tabela e a trigger que mantm o histrico para que seja registrado o usurio que realizou a alterao de cargo.

ALTER TABLE tb_historico_cargo ADD usuario varchar(80);

CREATE OR REPLACE TRIGGER tg_historico_cargoAFTER INSERT OR UPDATE OF cargo ON tb_funcionarioFOR EACH ROWBEGIN

INSERT INTO tb_historico_cargo(cod_func, cargo, data, usuario)

VALUES(:NEW.cod_func, :NEW.cargo, SYSDATE, USER);END;

164

Exerccio 8 Criar uma tabela e um triggerpara controlar o histrico de salrios dos funcionrios.

CREATE OR REPLACE TRIGGER tg_historico_salarioAFTER INSERT OR UPDATE OF salario ON tb_funcionarioFOR EACH ROWBEGIN

INSERT INTO tb_historico_salario(cod_func, salario, data)VALUES(:NEW.cod_func, :NEW.salario, SYSDATE);

END;

165

Exerccio 9 Criar um trigger para no permitir que o cargo de um funcionrio seja alterado 2 vezes no mesmo dia.

create or replace trigger tg_validar_cargobefore update of cargo on tb_funcionariofor each rowdeclare

v_qtde number;begin

select count(*) into v_qtdefrom tb_historico_cargowhere cod_func = :new.cod_func

and trunc(data) = trunc(sysdate);if v_qtde 0 then

raise_application_error(-20503,'No permitido trocar de cargo no mesmo dia');

end if;end;

166

Exerccio 10 Criar um trigger para no permitir que o salrio de um funcionrio seja atualizado em menos de 3 meses.

create or replace trigger tg_validar_cargobefore update of salario on tb_funcionariofor each rowdeclare

v_qtde number;begin

select count(*) into v_qtdefrom tb_historico_cargowhere cod_func = :new.cod_func

and trunc(data) >= trunc(add_months(sysdate,-3));if v_qtde 0 then

raise_application_error(-20503,'Salrio j alterado a menos de 3 meses');

end if;end;

167

Exerccios

1. Criar uma tabela chamada tb_produto_estatistica com os campos: qtde_produtos e preco_medio. Cada vez que a tabela de produtos for manipulada (insert, update ou delete), deve-se disparar um trigger para atualizar os dados da tabela de estatsticas.

2. Criar uma tabela chamada tb_produto_auditoria com os campos: usuario, data, evento (Insert, Update, Delete), codigo_antigo, codigo_novo, descricao_antiga, descricao_nova, preco_antigo e preco_novo. Cada vez que a tabela de produtos for manipulada, deve-se disparar um trigger para criar a trilha para auditoria.

168

Controle de Estoque por Trigger1. Criar um trigger para dar baixa no estoque cada vez que

um novo item for adicionado no pedido.2. Criar um trigger para restaurar o estoque caso um item

seja removido do pedido.3. Criar um trigger para ajustar o estoque caso a

quantidade seja alterada.4. Remover os 3 triggers criados para controlar o estoque

de produtos.5. Criar um nico trigger que ir fazer todo o controle do

estoque (insert, update e delete).6. Criar um nico trigger que ir fazer a atualizao do

campo valor na tabela de pedido, sempre que a tabela de itens for manipulada.

169

Tabelas de PedidosCREATE TABLE tb_pedido(

cod_ped integer not null primary key,data date,valor decimal(8,2) default 0

);

CREATE TABLE tb_itens(cod_ped integer not null,cod_prod integer not null,qtde integer,PRIMARY KEY(cod_ped, cod_prod),FOREIGN KEY(cod_ped) REFERENCES tb_pedido(cod_ped),FOREIGN KEY(cod_prod) REFERENCES tb_pedido(cod_prod)

);

UPDATE tb_produto SET estoque = 100;

170

Exerccio 1 Criar um trigger para dar baixa no estoque cada vez que um novo item for adicionado no pedido.

CREATE OR REPLACE tg_estoque_baixarAFTER INSERT ON tb_itensFOR EACH ROWBEGIN

UPDATE tb_produtoSET estoque = estoque - :NEW.qtdeWHERE cod_prod = :NEW.cod_prod;

END;

171

Exerccio 2 Criar um trigger para restaurar o estoque caso um item seja removido do pedido.

CREATE OR REPLACE tg_estoque_restaurarAFTER DELETE ON tb_itensFOR EACH ROWBEGIN

UPDATE tb_produtoSET estoque = estoque + :OLD.qtdeWHERE cod_prod = :OLD.cod_prod;

END;

172

Exerccio 3 Criar um trigger para ajustar o estoque caso a quantidade seja alterada.

CREATE OR REPLACE tg_estoque_ajustarAFTER UPDATE ON tb_itensFOR EACH ROWBEGIN

UPDATE tb_produtoSET estoque = estoque + :OLD.qtde - :NEW.qtdeWHERE cod_prod = :NEW.cod_prod;

END;

173

Exerccio 4 Remover os 3 triggers criados para controlar o estoque de produtos.

DROP TRIGGER tg_estoque_baixar;DROP TRIGGER tg_estoque_restaurar;DROP TRIGGER tg_estoque_ajustar;

174

Exerccio 5 Criar uma nica trigger que ir fazer todo o controle do estoque.

CREATE OR REPLACE TRIGGER tg_estoqueAFTER INSERT OR UPDATE OR DELETE ON tb_itensFOR EACH ROWBEGIN

IF INSERTING THENUPDATE tb_produtoSET estoque = estoque - :NEW.qtdeWHERE cod_prod = :NEW.cod_prod;

ELSIF DELETING THENUPDATE tb_produtoSET estoque = estoque + :OLD.qtdeWHERE cod_prod = :OLD.cod_prod;

ELSIF UPDATING THENUPDATE tb_produtoSET estoque = estoque + :OLD.qtde - :NEW.qtdeWHERE cod_prod = :NEW.cod_prod;

END IF;END;

175

predicados condicionais

Trigger para Eventos do Sistema

Num trigger, podemos fazer referencia a um evento do sistema ao invs de referenciarmos uma tabela.

Os eventos de sistema so: AFTER SERVERERROR AFTER LOGON BEFORE LOGOFF AFTER STARTUP BEFORE SHUTDOWN

Voc pode criar triggers usando os eventos acima para DATABASE e SCHEMA. As duas excees so SHUTDOWN e STARTUP que s se

aplicam a DATABASE

176

Log para Controle de Acessos

Vamos criar a tabela de log de acesso e seu sinnimo:

CREATE TABLE tb_log_acesso (usuario varchar(80),horario char(20),evento char(6)

);

CREATE PUBLIC SYNONYM log_acesso_IDFOR SYSTEM.tb_log_acesso;

177

Trigger para registrar o logon

CREATE OR REPLACE TRIGGER tg_registrar_logonAFTER LOGON ON DATABASEBEGIN

INSERT INTO log_acesso_ID VALUES(USER, TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'),'LOGON');

COMMIT; END;

178

Trigger para registrar o logoff

CREATE OR REPLACE TRIGGER tg_registrar_logoffBEFORE LOGOFF ON DATABASEBEGIN

INSERT INTO log_acesso_ID VALUES(USER, TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'),'LOGOFF');

COMMIT; END;

179

Trigger com Tabela Mutante

Uma tabela mutante (MUTATING TABLE) uma tabela que est sendo modificada atualmente por uma instruo UPDATE, DELETE ou INSERT.

Quando criamos um trigger para determinada tabela e, dentro dela, tentamos manipular os dados dessa mesma tabela, enfrentamos o problema de trigger mutante.

O cdigo de erro Oracle ORA-04091 Isso ocorre porque o trigger executado no momento de

manipulao da tabela e o objeto (tabela) est em modo bloqueado at que se encerre.

Uma tabela s considerada mutante para gatilhos de linha (FOR EACH ROW).

180

Exemplo de Trigger Mutante

create or replace trigger tg_verificar_reserva_quartobefore insert or update on tb_reservasfor each rowdeclare cursor cur_reservas is

select * from tb_reservaswhere num_quarto = :new.num_quarto and(dt_entrada between :new.dt_entrada and :new.dt_saidaor dt_saida between :new.dt_entrada and :new.dt_saida);

beginfor reg_reservas in cur_reservas loop

raise_application_error(-20001,'Quarto j possui reserva para este perodo');

end loop;end;

181

Possveis Solues:

No use trigger Uma maneira de evitar o erro de tabela mutante no usar

trigger.

Use after or "instead of Se voc usar um gatilho AFTER para um trigger que ir atualizar

uma tabela, a atualizao j estar concluda quando o trigger for executado e a tabela no ser mutante.

Reescreva a sintaxe da trigger Existem algumas maneiras de evitar o erro de tabela mutante

com uma combinao de gatilhos de nvel de linha e de instruo.

Use autonomous transactions Voc pode evitar o erro de tabela mutante, marcando seu gatilho

como uma transao autnoma, tornando-se independente da tabela que chama o procedimento.

182

Resumo

PL/SQL Linguagem que rene recursos da linguagem SQL com instrues de controle tpicas de linguagens de programao procedurais.

BLOCOS ANONIMOS instrues PL/SQL que permitem a realizao de rotinas junto ao banco de dados e que no ficam registrados no servidor.

STORED PROCEDURES programas PL/SQL que permitem uso de passagem de parmetros e que, aps compilados, ficam gravados no servidor e podem ser executados atravs de linguagens de programao.

FUNCTION programas PL/SQL que podem receber parmetros e que sempre retornam um resultado.

TRIGGER programas PL/SQL que so disparados automaticamente pelo BD quando um evento associados executado.

183

Elaborar um trigger para gerar automaticamente as parcelas das contas a receber a venda. A venda deve possuir: num_venda, data_venda, valor_venda, qtde_parcelas. Em contas a receber deve-se armazenar: num_venda, num_parcela, data_vecto, valor e situao ((P) pendente ou (Q) quitado). A partir do numero de parcelas da venda, deve-se criar as parcelas em contas a receber. A data da primeira parcela deve ser igual a data de venda. As demais parcelas devem ter o mesmo dia nos meses seguintes. A soma de todas as parcelas deve ser exatamente o valor total da venda. A situao inicial de todas as parcelas dever ser pendente.

184

Trabalho(individual ou em dupla)

BibliografiaLivro Texto:1. Introduo a Sistemas de Banco de Dados; Date, C. J.; Ed. Campus; 8 edio; 2004;

15 exemplares.2. Sistemas de Banco de Dados; Korth, Henry F.; Ed. Campus; 5 edio; 2006; 15

exemplares.3. Oracle Database 11g PL/SQL: Programao; McLaughlin, Michel; Ed. Novatec; 1

edio; 2009; 10 exemplares.

Complementar:4. Projeto e Modelagem de Banco de Dados; Lightstone, Teorey; Ed. Campus; 1 edio;

2006; 10 exemplares.5. Oracle 10g PL/SQL; Oliveira, Celso H. Poderoso de; Ed. Novatec; 1 edio; 2005; 10

exemplares.6. Oracle 10g Database: Guia do DBA; Serson, Roberto Rubinstein; Ed. Novatec; 1 edio;

2004; 10 exemplares.7. Oracle 10g: Ideal Para Quem Deseja Iniciar o Aprendizado do Oracle; Ramalho, Jos

A.; Ed. Thomson; 1 edio; 2005; 10 exemplares.8. Gerenciando Banco de Dados: A Abordagem Entidade-Relacionamento para Projeto

Lgico; Chen, Peter; Makron Books; 1 edio; 1990; 7 exemplares.

185

Documents

Administracao de Banco de Dados