Oracle 9i: SQL e PL/SQL

1. Oracle 9i:SQL e PL/SQL Bruno Celso Cunha de Freitas Marcos José de Menezes Cardoso Júnior {bccf,mjmcj}@cin.ufpe.br Gerenciamento de Dados e Informação Centro de Informática - UFPE

2. Roteiro • Estudo de Caso: Supermercado Simples • SQL Básico: DDL e DML • Comandos Especiais • PL/SQL • Triggers • Stored Procedures • Functions • Packages

3. Cargo Cod_Cargo (PK) Descricao Produto Cod_Produto (PK) Descricao Preco Venda Cod_Venda (PK) Cod_Pessoa (FK) Data Total Cliente Cod_Pessoa (PK eFK) Pontos Supermercado Pessoa Funcionario Cod_Pessoa (PK) Nome Endereco Fone Tipo Cod_Pessoa (PK e FK) Cod_Cargo (FK) Salario VendaxProd Cod_Venda (PK eFK) Cod_Prod (FK) Qtd Sub_Total

4. DDL (Data Definition Language) • Comando Create Create Table <nome> ( <campo> <tipo>(<tamanho>) [NOT NULL], ... [CONSTRAINT "PK_<tableName>“] primary key (<campo1>,[<campo2>,...,<campon>]), [CONSTRAINT “FK_<tableName>“][foreign key] (<campo>) REFERENCES <table>(campo) );

5. DDL • Exemplo: Create table pessoa ( cod_pessoa number(5) NOT NULL, nome varchar2(150) NOT NULL, endereco varchar2(150) , fone varchar2(11) , tipo char(1) NOT NULL, CONSTRAINT “PK_PESSOA” primary key (cod_pessoa) );

6. DDL • Exemplo: Create table funcionario ( cod_pessoa number(5) NOT NULL, cod_cargo number(5) NOT NULL, salario number(5,2) , CONSTRAINT “PK_FUNC” primary key (cod_pessoa), CONSTRAINT “FK_PESSOA” foreign key (cod_pessoa) references pessoa(cod_pessoa), CONSTRAINT “FK_CARGO” foreign key (cod_cargo) references cargo(cod_cargo) );

7. DDL • Comando Alter • Modificando uma coluna Alter Table <nome> MODIFY ( <campo> <tipo>(<tamanho>) [NOT NULL] ); • Ex: Alter Table pessoa MODIFY ( nome varchar2(200) NOT NULL );

8. DDL • Comando Alter • Adicionando uma coluna Alter table <nome> add ( <campo> <tipo>(<tamanho>) [NOT NULL] ); • Ex: Alter table funcionario add ( data date NOT NULL );

9. DDL • Comando Alter • Eliminando uma coluna Alter Table <nome> DROP ( <campo> ); • Ex: Alter Table funcionario DROP ( data );

10. DDL • Comando Drop • Excluindo uma tabela DROP TABLE [<esquema>.]<tabela> [CASCADE CONSTRAINTS]; • Ex: DROP TABLE pessoa [CASCADE CONSTRAINTS];

11. DDL • Visões • Tabelas virtuais que não ocupam espaço físico • create view <nome> as select <consultas> /*Criar uma visão dos funcionários que ganham mais de 1000 reais*/ Create view func_1000 as select * from funcionario where salario > 1000

12. DML (Data Manipulation Language) • Comando Insert Insert into [<esquema.>]<tabela> [(campo1,...,campon)] values (valor1,...,valorn); Ex.: Insert into cargo (cod_cargo,descricao) values (1,’Caixa’); Insert into pessoa (cod_pessoa,nome,endereco,fone,tipo) values (1,’Bruno’,’meu_endereco’,’99999999’,’F’); Insert into funcionario (cod_pessoa,cod_cargo,salario) values (1,1,’250.33’);

13. DML • Comando Update update [<esquema.>]<tabela> set campo1 = valor1,...,campon = valorn WHERE condição Ex.: Update pessoa set endereco = ‘novo_endereco’, fone = null where cod_pessoa = 1; Update funcionario set salario = ‘650.60’ where cod_pessoa = 1

14. DML • Comando Delete Delete from [<esquema.>]<tabela> WHERE condição Ex.: Delete from funcionario where cod_pessoa = 1; Delete from pessoa where cod_pessoa = 1;

15. DML • Comando Select Select {* | <campo1,...,campon>} from <tabela> [where condição] Ex.: /* Listando todos os atributos de todas as pessoas */ Select * from pessoa; /* Listando nome e endereco de todas as pessoas */ Select nome,endereco from pessoa; /* Listando nome e descrição do cargo de todos os funcionários */ Select pessoa.nome, cargo.descricao from pessoa, cargo, funcionario where pessoa.cod_pessoa = funcionario.cod_pessoa and funcionario.cod_cargo=cargo.cod_cargo;

16. DML • Comando Select • Consultas encadeadas /* Listar o cliente que possui mais pontos*/ Select pessoa.nome from pessoa, cliente where cliente.pontos = (select MAX(cliente.pontos) from cliente) and pessoa.cod_pessoa = cliente.cod_pessoa;

17. DML • Comando Select • Cláusula Distinct /* Listando todos os cargos que possuem ao menos um funcionário*/ Select distinct cargo.descricao from funcionario, cargo where funcionario.cod_cargo = cargo.cod_cargo;

18. DML • Comando Select • Cláusula Group By /* Listando os cargos e a quantidade de funcionários em cada cargo agrupados por cargo */ select cod_cargo, count(cod_cargo) as soma from funcionario group by funcionario.cod_cargo

19. DML • Comando Select • Cláusula Having /* Listando os cargos e a quantidade de funcionários em cada cargo agrupados por cargo, porém só para aqueles cargos que possuem mais de dois funcionários atrelados a ele. */ select cod_cargo, count(cod_cargo) as soma from funcionario group by funcionario.cod_cargo having count(*) >= 2;

20. DML • Comando Select • Cláusula order by /* Listando o nome dos clientes em ordem alfabética */ Select pessoa.nome from pessoa where pessoa.tipo = ‘C’ order by pessoa.nome; /* Listando os salários em ordem decrescente */ Select funcionario.salario from funcionario order by salario desc;

21. DML • Comando Select • Cláusulas in e or /* Listando o nome dos funcionários cujo cargo seja caixa ou embalador */ Select pessoa.nome from pessoa,funcionario,cargo where pessoa.cod_pessoa = funcionario.cod_pessoa and funcionario.cod_cargo = cargo.cod_cargo and cargo.descricao in (‘caixa’,’embalador’); Select pessoa.nome from pessoa,funcionario,cargo where pessoa.cod_pessoa = funcionario.cod_pessoa and funcionario.cod_cargo = cargo.cod_cargo and cargo.descricao = ‘caixa’ or cargo.descricao = ’embalador’;

22. DML • Comando Select • Funções (MAX, MIN, SUM, AVG, COUNT) /* Mostrar o valor do maior salário dos funcionários */ Select MAX (salario) from Funcionario /* Mostrar qual o a média de pontos dos clientes */ Select AVG (pontos) from Cliente /* Mostrar quantos clientes possuem mais de 1000 pontos */ Select COUNT (*) from Cliente where pontos > 1000 /* Mostrar as despesas com pagamento de salário dos funcionários que o supermercado possui */ Select SUM (salario) from Funcionario

23. Comandos Especiais • DESCRIBE: Exibe a estrutura de uma tabela. Ex.: DESC <tabela>; • COMMIT: Grava uma transação no banco de dados. Ex.: COMMIT; • ROLLBACK: Recupera o banco de dados para a última posição que estava após o último comando commit ser executado. Ex.: ROLLBACK;

24. Exercícios Propostos • Terminar a criação do restante das tabelas • Selecionar o cliente que mais gastou ontem • Selecionar o cargo e o salário do funcionário que recebe menos • Selecionar quantos clientes com mais de 200 pontos gastaram no supermercado, hoje, mais de 500 reais

25. PL/SQL DECLARE -- declarações BEGIN -- instruções END;

26. PL/SQL • Ex.: /* Se o salário do funcionário com cod_pessoa = 1 for menor do que R$ 500, ele deverá ter um aumento de 10% */ DECLARE sal funcionario.salario%type; BEGIN select salario into sal from funcionario where cod_pessoa = 1 FOR UPDATE OF salario; IF sal < 500 THEN sal := sal * 1.1; update funcionario set salario = sal where cod_pessoa = 1; END IF; COMMIT; END;

27. PL/SQL • /* Dar um aumento de 10% para todos os funcionários */ DECLARE sal funcionario.salario%type; i number(5); BEGIN select count(cod_pessoa) into i from funcionario; if i > 0 then loop select salario into sal from funcionario where cod_pessoa = i FOR UPDATE OF salario; sal := sal * 1.1; update funcionario set salario = sal where cod_pessoa = i; COMMIT; i := i - 1; if i = 0 then exit; end if; end loop; end if; END; /

28. PL/SQL • É possível fazer o mesmo com: • FOR...LOOP Ex.: FOR j IN 1..10 LOOP <comandos> END LOOP; • WHILE Ex.: i := 1; WHILE i <= 10 LOOP <comandos> i := i + 1; END LOOP;

29. PL/SQL • Comando Case Ex.: /* Pegando nome e departamento dos funcionários e ordenando os departamentos por ordem alfabética */ Select pessoa.nome, ( case funcionario.cod_cargo when 1 then 'Gerência' when 2 then 'Atendimento' else 'diversos' end) as departamento from pessoa, funcionario where pessoa.cod_pessoa = funcionario.cod_pessoa order by departamento;

30. Triggers • Criando um trigger Ex.: /* Validando o domínio de um salário */ create or replace trigger testa_salario before insert or update of salario on funcionario for each row begin if :new.salario > 8000 then raise_application_error(-20000,'VALOR INCORRETO'); end if; end; /

31. Triggers /* Removendo os dados específicos da pessoa ao tentar excluí-la da tabela pessoa. */ create trigger remove_pessoa before delete on pessoa for each row begin if(:old.tipo = 'C') then delete from cliente where cod_pessoa = :old.cod_pessoa; else delete from funcionario where cod_pessoa = :old.cod_pessoa; end if; end; /

32. Triggers Set serveroutput on; // Necessário para visualizar a saída /* Imprimindo o valor antigo e o novo do salário */ create or replace trigger saldif before delete or insert or update on funcionario for each row declare sal_diff funcionario.salario%type; begin if (:new.cod_pessoa > 0) then sal_diff := :new.salario-:old.salario; dbms_output.put(' antigo: '||:old.salario); dbms_output.put(' novo: '||:new.salario); dbms_output.put_line(' Diferença:'||sal_diff); end if; end; /

33. Triggers • Alterando um trigger • Não pode ser alterado diretamente. Deve ser recriado com o comando create. • Excluindo um trigger drop trigger <nome-do-trigger>; Ex.: drop trigger testa_salario;

34. Triggers • Visualizando seus triggers /* Visualizando apenas o nome dos meus triggers */ Select trigger_name from user_triggers; /* Visualizando o corpo dos meus triggers */ Select trigger_body from user_triggers where trigger_name = ‘REMOVE_PESSOA’;

35. Stored Procedures • Criando uma Stored Procedure /* Criando procedimento para aumentar o salário dos funcionários */ create or replace procedure aumenta_salario(percentual number) is begin update funcionario set salario = salario * (1 + percentual/100); end;

36. Stored Procedures • Executando uma Stored Procedure EXEC <nome-da-procedure>; Ex.: EXEC aumenta_salario(5); • Excluindo uma Stored Procedure DROP PROCEDURE <nome-da-procedure>; Ex.: DROP PROCEDURE aumenta_salario;

37. Functions • Criando uma função /* Esta função conta a quantidade de funcionários em um determinado cargo. */ create or replace function contafunc(codCargo cargo.cod_cargo%type) return number is qtdFunc number; begin select count(*) into qtdFunc from funcionario where cod_cargo = codCargo; return qtdFunc; end; /

38. Functions • Executando uma função select <nome-da-funcao> from dual; Ex.: select contafunc(1) from dual; Obs:DUAL - tabela do usuário SYS e que todos os usuários têm acesso • Excluindo uma função drop function <nome-da-função>; Ex.: drop function contafunc;

39. Packages • Criando a chamada de um package create package pacote_teste is procedure aumenta_salario(percentual number); function contafunc(codCargo in cargo.cod_cargo%type) return number; end;

40. Packages • Criando o corpo de um package create package body pacote_teste is procedure aumenta_salario(percentual number) is begin update funcionario set salario = salario * (1 + percentual/100); end; function contafunc(codCargo in cargo.cod_cargo%type) return number is qtdFunc number; begin select count(*) into qtdFunc from funcionario where cod_cargo = codCargo; return qtdFunc; end; end;

41. Packages • Referenciando um subprograma do package Ex.: exec pacote_teste.aumenta_salario(10); select pacote_teste.contafunc(1) from dual;

42. Packages • Recompilando um package /* Compila apenas o corpo do pacote */ alter package pacote_teste compile body /* Compila apenas a chamada e o corpo do pacote */ alter package pacote_teste compile;

43. Packages • Excluindo um package /* Excluindo apenas o corpo do pacote */ drop package body <nome-do-pacote>; Ex.: drop package body pacote_teste; /* Excluindo o pacote inteiro */ drop package <nome-do-pacote>; Ex.: drop package pacote_teste;

44. Referências • Ramalho, José Antônio. Oracle 9i, São Paulo, Berkeley Brasil, 2002. • Corey, Michael J. & Abbey, Michael. Oracle 8i – A Beginner`s Guide, Califórnia, Berkeley, 1997.

45. Oracle 9i:SQL e PL/SQL Bruno Celso Cunha de Freitas Marcos José de Menezes Cardoso Júnior {bccf,mjmcj}@cin.ufpe.br Gerenciamento de Dados e Informação Centro de Informática - UFPE