Oracle 9i: SQL e PL/SQL

1
Oracle 9iSQL e PL/SQL

• Bruno Celso Cunha de Freitas
• Marcos José de Menezes Cardoso Júnior
• bccf,mjmcj_at_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
Supermercado
Pessoa
Funcionario
Cod_Pessoa (PK) Nome Endereco Fone Tipo
Cod_Pessoa (PK e FK) Cod_Cargo (FK) Salario
VendaxProd
Cod_Venda (PK e FK) Cod_Prod (FK) Qtd Sub_Total
4
DDL (Data Definition Language)

• Comando Create
• Create Table ltnomegt (
• ltcampogt lttipogt(lttamanhogt) NOT NULL,
• ...
• CONSTRAINT "PK_lttableNamegt primary key
  (ltcampo1gt,ltcampo2gt,...,ltcampongt),
• CONSTRAINT FK_lttableNamegtforeign key
  (ltcampogt) REFERENCES lttablegt(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 ltnomegt MODIFY (
• ltcampogt lttipogt(lttamanhogt) NOT NULL
• )
• Ex
• Alter Table pessoa MODIFY (
• nome varchar2(200) NOT NULL
• )

8
DDL

• Comando Alter
• Adicionando uma coluna
• Alter table ltnomegt add (
• ltcampogt lttipogt(lttamanhogt) NOT NULL
• )
• Ex
• Alter table funcionario add (
• data date NOT NULL
• )

9
DDL

• Comando Alter
• Eliminando uma coluna
• Alter Table ltnomegt DROP (
• ltcampogt
• )
• Ex
• Alter Table funcionario DROP (
• data
• )

10
DDL

• Comando Drop
• Excluindo uma tabela
• DROP TABLE ltesquemagt.lttabelagt CASCADE
  CONSTRAINTS
• Ex
• DROP TABLE pessoa CASCADE CONSTRAINTS

11
DDL

• Visões
• Tabelas virtuais que não ocupam espaço físico
• create view ltnomegt ltatributosgt as select
  ltconsultasgt
• /Criar uma visão dos funcionários que ganham
  mais de 1000 reais/
• Create view func_1000 as select from
  funcionario where salario gt 1000

12
DML (Data Manipulation Language)

• Comando Insert
• Insert into ltesquema.gtlttabelagt
  (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,sala
  rio) values (1,1,250.33)

13
DML

• Comando Update
• update ltesquema.gtlttabelagt 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 ltesquema.gtlttabelagt WHERE condição
• Ex.
• Delete from funcionario where cod_pessoa 1
• Delete from pessoa where cod_pessoa 1

15
DML

• Comando Select
• Select ltcampo1,...,campongt from lttabelagt
  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_cargoc
  argo.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() gt 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 gt 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 lttabelagt
• 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 de um funcionário for menor
  do que R 500, ele deverá ter um aumento de 10
  /
• DECLARE
• sal funcionario.salariotype
• BEGIN
• select salario into sal from funcionario where
  
• cod_pessoa 1
• FOR UPDATE OF salario
• IF sal lt 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.salariotype
• i number(5)
• BEGIN
• select count(cod_pessoa) into i from
  funcionario
• if i gt 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

28
PL/SQL

• É possível fazer o mesmo com
• FOR...LOOP
• Ex. FOR j IN 1..10 LOOP
• ltcomandosgt
• END LOOP
• WHILE
• Ex. i 1
• WHILE i lt 10 LOOP
• ltcomandosgt
• 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 gt 8000 then
• raise_application_error(-20000,'VALOR
  INCORRETO')
• end if
• end
• /
• Obs RAISE_APPLICATION_ERROR (número do erro,
  mensagem do erro)
• -gt número do erro compreendido
  entre -20000 e 20999

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.salariotype
• begin
• if (new.cod_pessoa gt 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 ltnome-do-triggergt
• 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(percen
  tual number) is
• begin
• update funcionario set salario salario (1
  percentual/100)
• end

36
Stored Procedures

• Executando uma Stored Procedure
• EXEC ltnome-da-proceduregt
• Ex.
• EXEC aumenta_salario(5)
• Excluindo uma Stored Procedure
• DROP PROCEDURE ltnome-da-proceduregt
• 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 in
  cargo.cod_cargotype) 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 ltnome-da-funcaogt from dual
• Ex.
• select contafunc(1) from dual
• Excluindo uma função
• drop function ltnome-da-funçãogt
• 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_cargotype) 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_cargotype) 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 ltnome-do-pacotegt
• Ex.
• drop package body pacote_teste
• / Excluindo o pacote inteiro /
• drop package ltnome-do-pacotegt
• 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
  Beginners Guide, Califórnia, Berkeley, 1997.

45
Oracle 9iSQL e PL/SQL

• Bruno Celso Cunha de Freitas
• Marcos José de Menezes Cardoso Júnior
• bccf,mjmcj_at_cin.ufpe.br
• Gerenciamento de Dados e Informação
• Centro de Informática - UFPE