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
Roteiro








Estudo de Caso: Supermercado Simples
SQL Básico: DDL e DML
Comandos Especiais
PL/SQL
Triggers
Stored Procedures
Functions
Packages
Supermercado
Pessoa
Funcionario
Cargo
Cod_Pessoa (PK)
Cod_Pessoa (PK e FK)
Cod_Cargo (PK)
Nome
Cod_Cargo (FK)
Descricao
Endereco
Salario
Fone
Tipo
VendaxProd
Cod_Venda (PK e FK)
Cliente
Cod_Pessoa (PK e FK)
Pontos
Produto
Venda
Cod_Prod (FK)
Cod_Produto (PK)
Cod_Venda (PK)
Qtd
Descricao
Cod_Pessoa (FK)
Sub_Total
Preco
Data
Total
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
cod_pessoa
cod_cargo
salario
CONSTRAINT
CONSTRAINT
references
CONSTRAINT
references
);
funcionario (
number(5)
NOT NULL,
number(5)
NOT NULL,
number(5,2)
,
“PK_FUNC” primary key (cod_pessoa),
“FK_PESSOA” foreign key (cod_pessoa)
pessoa(cod_pessoa),
“FK_CARGO” foreign key (cod_cargo)
cargo(cod_cargo)
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
);
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 coluna
Alter 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 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
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/SQL
DECLARE
-- declarações
BEGIN
-- instruções
END;
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...LOOP
Ex.: FOR j IN 1..10 LOOP
<comandos>
END LOOP;

WHILE
Ex.: i := 1;
WHILE i <= 10 LOOP
<comandos>
END LOOP;
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;
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;
/
Obs: RAISE_APPLICATION_ERROR (número do erro, mensagem do erro);
-> número do erro compreendido entre -20000 e –20999
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;
/
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;
/
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 Freitas
Marcos José de Menezes Cardoso Júnior
{bccf,mjmcj}@cin.ufpe.br
Gerenciamento de Dados e Informação
Centro de Informática - UFPE