Oracle 9i:SQL e PL/SQL
Bruno Celso Cunha de FreitasMarcos José de Menezes Cardoso Júnior
{bccf,mjmcj}@cin.ufpe.br
Gerenciamento de Dados e InformaçãoCentro de Informática - UFPE
Roteiro Estudo de Caso: Supermercado Simples SQL Básico: DDL e DML Comandos Especiais PL/SQL Triggers Stored Procedures Functions Packages
SupermercadoCod_Pessoa (PK)NomeEnderecoFoneTipo
Cod_Pessoa (PK e FK)Cod_Cargo (FK)Salario
Cod_Venda (PK e FK)Cod_Prod (FK)QtdSub_Total
Pessoa Funcionario
Cod_Cargo (PK)Descricao
Cargo
Cod_Produto (PK)DescricaoPreco
ProdutoCod_Venda (PK)Cod_Cliente (FK)DataTotal
VendaVendaxProd
Cod_Pessoa (PK e FK)Pontos
Cliente
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));
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)
);
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)
);
DDL Comando Alter
Modificando uma colunaAlter Table <nome> MODIFY ( <campo> <tipo>(<tamanho>) [NOT NULL]);
• Ex:Alter Table pessoa MODIFY ( nome varchar2(200) NOT NULL);
DDL Comando Alter
Adicionando uma coluna
Alter table <nome> add ( <campo> <tipo>(<tamanho>) [NOT NULL]
);
• Ex:Alter table funcionario add ( data date NOT NULL);
DDL Comando Alter
Eliminando uma colunaAlter Table <nome> DROP ( <campo>);
• Ex:Alter Table funcionario DROP ( data
);
DDL Comando Drop
Excluindo uma tabela
DROP TABLE [<esquema>.]<tabela> [CASCADE CONSTRAINTS];
• Ex:DROP TABLE pessoa [CASCADE CONSTRAINTS];
DDL Visões
Tabelas virtuais que não ocupam espaço físico create view <nome> [<atributos>] 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
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’);
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
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;
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 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;
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;
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;
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;
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;
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’;
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
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;
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
PL/SQLDECLARE
-- declaraçõesBEGIN
-- instruçõesEND;
PL/SQL Ex.: /* Se o salário de um funcionário 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;
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;/
PL/SQL É possível fazer o mesmo com:
FOR...LOOPEx.: FOR j IN 1..10 LOOP <comandos> END LOOP;
WHILEEx.: i := 1; WHILE i <= 10 LOOP <comandos> END LOOP;
PL/SQL Comando CaseEx.: /* 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;
Triggers Criando um triggerEx.:/* 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;/
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;/
TriggersSet serveroutput on; // Necessário para visualizar a saída/* Imprimindo o valor antigo e o novo do salário */create or replace trigger saldifbefore delete or insert or update on funcionariofor 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;
/
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;
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’;
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;
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;
Functions Criando uma função/* Esta função conta a quantidade de funcionários em um
determinado cargo. */create or replace 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;/
Functions Executando uma função select <nome-da-funcao> from dual; Ex.: select contafunc(1) from dual;
Excluindo uma função drop function <nome-da-função>;
Ex.: drop function contafunc;
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;
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;
Packages Referenciando um subprograma do
package Ex.:
exec pacote_teste.aumenta_salario(10); select pacote_teste.contafunc(1) from dual;
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;
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;
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.
Oracle 9i:SQL e PL/SQL
Bruno Celso Cunha de FreitasMarcos José de Menezes Cardoso Júnior
{bccf,mjmcj}@cin.ufpe.br
Gerenciamento de Dados e InformaçãoCentro de Informática - UFPE