Marcelo Henrique dos Santos

(1)

Marcelo Henrique dos Santos

Mestrado em Educação (em andamento) MBA em Negócios em Mídias Digitais

MBA em Marketing e Vendas Especialista em games

Bacharel em Sistema de Informação Email: marcelosantos@outlook.com

JAVA ENTERPRISE EDITION - DESENVOLVENDO APLICAÇÕES CORPORATIVAS Marcelo Henrique dos Santos

(2)

JAVA ENTERPRISE EDITION - DESENVOLVENDO APLICAÇÕES CORPORATIVAS

Marcelo Henrique dos Santos

SQL (Structure Query

Language)

(3)

SQL

• Linguagem para:

– Definição de dados: criação das estruturas

• Data Definition Language (DDL)

– Manipulação de dados: atualização e consultas

• Data Manipulation Language (DML)

(4)

Histórico

• Linguagem SQUEL desenvolvida pela IBM para um banco de dados experimental R

• Baseada no padrão ANSI e ISO: – SQL-86

– SQL-89 – SQL-92 – SQL:1999 – SQL:2003

• A maioria dos SGBD comerciais suportam o SQL-92, e algumas das caracteristicas das

(5)

Mais SQL

• SQL é considerada a razão principal para o sucesso dos bancos de dados relacionais comerciais

– Tornou-se a linguagem padrão para bases relacionais

– Funciona entre diferentes produtos – Embedded SQL: Java, C/C++, Cobol… – Fácil uso para o usuário

(6)

SQL COMO LINGUAGEM DE DEFINIÇÃO DE DADOS

• O esquema de cada relação

• O domínio dos valores associados a cada atributo

• Restrições de integridade • O conjunto de índices

• Visões

• Permissão de acesso às relações

Permite especificar:

(7)

DDL (Data Definition

Language)

(8)

Criando uma base de

dados

• Criação de um BD

– SQL padrão não oferece tal comando

• BDs são criados via ferramentas do SGBD

– alguns SGBDs (SQL Server, DB2, MySQL) oferecem este comando

• create database nome_BD • drop database nome_BD

(9)

Criando Esquemas em

SQL

• Comandos para definição de esquemas

– create table

• define a estrutura da tabela, suas restrições de integridade e cria uma tabela vazia

– alter table

• modifica a definição de uma tabela:

– atributos chave não podem ser removidos de uma tabela – atributos NOT NULL não podem ser inseridos em uma tabela

– drop table

• remove uma tabela com todas as suas tuplas

(10)

Criação de Tabelas

CREATE TABLE

• Colunas são especificadas primeiro, sob a forma:

• Depois Chaves, integridade referencial e restrições de integridade

CREATE TABLE <nome_da_tabela> (C₁ D₁, C₂ D₂, ..., C_n D_n,

...

PRIMARY KEY <lista_de_Colunas>, FOREIGN KEY <nome_da_coluna> REFERENCES

<nome_tab_ref>(<nome_da_coluna_ref>));

– cada C_i é uma coluna no esquema da tabela – D_i é o tipo de dado no domínio da coluna C10 _i

(11)

Criando Esquemas em

SQL

CREATE TABLE Ambulatorios (

nroa int,

andar numeric(3) NOT NULL, PRIMARY KEY(nroa)

)

CREATE TABLE Medicos (

codm int,

nome varchar(40) NOT NULL, especialidade char(20),

CPF numeric(11) UNIQUE, cidade varchar(30),

nroa int,

PRIMARY KEY(codm),

FOREIGN KEY(nroa) REFERENCES Ambulatorios

(12)

12

https://msdn.microsoft.com/pt-br/library/ms187752(v=sql.120).aspx

(13)

Create Table

• Exemplo:

create table produto

(codigo integer not null, descricao varchar(30), tipo varchar (20)

PRIMARY KEY codigo)

Codigo Descricao tipo

(14)

Alterando Tabelas

ALTER TABLE nome_tabela

ADD [COLUMN] nome_atributo_1 tipo_1 [{RIs}]

[{, nome_atributo_n tipo_n [{RIs}]}] |

MODIFY [COLUMN] nome_atributo_1 tipo_1 [{RIs}]

[{, nome_atributo_n tipo_n [{RIs}]}] |

DROP COLUMN nome_atributo_1 [{, nome_atributo_n }]

|

ADD CONSTRAINT nome_RI_1 def_RI_1 [{, nome_RI_n def_RI_n}]

|

DROP CONSTRAINT nome_RI_1 [{, nome_RI_n}]

|

[ADD|DROP] [PRIMARY KEY ...|FOREIGN KEY ...]

(15)

(16)

Restrições

• NOT NULL

– Restrição aplicada em colunas cujos valores não podem ser nulos

• Valor Default

– Usado para inicializar o valor de uma coluna – DEFAULT <valor> logo após a restrição:

create table produto

(codigo integer not null, descricao varchar(30),

tipo varchar (20)

(17)

Tipos de Domínios

em SQL

• char (n). Character de tamanho n definido pelo usuário

• varchar (n). • int

• Smallint

• numeric (p,d)

• real, double precision • float(n)

(18)

SQL (Structure Query

Language)

(19)

SQL

• Linguagem para:

– Definição de dados: criação das estruturas

• Data Definition Language (DDL)

– Manipulação de dados: atualização e consultas • Data Manipulation Language (DML)

(20)

Manipulação de Dados

• Define operações de manipulação de dados

– I (INSERT) – A (UPDATE) – E (DELETE) – C (SELECT) • Instruções declarativas – manipulação de conjuntos

– especifica-se o que fazer e não como fazer

(21)

Inserções, Alterações e

Exclusões

(22)

SQL – Insert

• Inserção de dados

INSERT INTO nome_tabela [(lista_atributos)] VALUES (lista_valores_atributos)

[, (lista_valores_atributos)]

• Exemplos

INSERT INTO Ambulatorios VALUES (1, 1, 30) INSERT INTO Medicos

(codm, nome, idade, especialidade, CPF, cidade) VALUES (4, ‘Carlos’, 28,’ortopedia’,

11000110000, ‘Joinville’);

(23)

SQL – Inserção a partir

de outra tabela

• Inserção de dados

Permite inserir em uma tabela a partir de outra tabela

A nova tabela terá os mesmos atributos, com os mesmos domínios

• Exemplos

INSERT into cliente as SELECT * from funcionario

(24)

SQL – Update

• Alteração de dados

UPDATE nome_tabela

SET nome_atributo_1 = Valor

[{, nome_atributo_n = Valor}] [WHERE condição]

• Exemplos

UPDATE Medico

SET cidade = ‘Florianopolis’ UPDATE Ambulatorios

SET capacidade = capacidade + 5, andar = 3 WHERE nroa = 2

(25)

SQL – DML

• Exclusão de dados

DELETE FROM nome_tabela

[WHERE condição] • Exemplos

DELETE FROM Ambulatorios DELETE FROM Medicos

WHERE especialidade = ‘cardiologia’ or cidade < > ‘Florianopolis’

(26)

(27)

Estrutura Básica

• Uma consulta em SQL tem a seguinte forma: select A₁, A₂, ..., A_n

from r₁, r₂, ..., r_m where P

– A_irepresenta um atributo – R_irepresenta uma tabela – P é um predicado

• Esta consulta é equivalente a uma expressão da Algebra Relacional

• O resultado de uma consulta SQL é sempre uma tabela

)) ( ( ₁ ₂ , , , ₂ 1 A A P m A _n r  r   r  _  

(28)

Estrutura Básica:

resumindo….

SELECT lista de atributos desejados

FROM uma ou mais tabelas

WHERE com restrições sobre atributos

 Exemplo: encontre o nome e o salário dos funcionarios da relação

funcionário

SELECT nome, salario FROM funcionario

(29)

Distinct

• O SQL permite duplicatas em relações e resultados em consultas

• Para eliminar duplatas, usa-se a cláusula DISTINCT depois do SELECT

Exemplo: SELECT distinct nome FROM funcionario

(30)

A cláusula *

• O asterisco na cláusula SELECT denota TODOS OS ATRIBUTOS

SELECT *

FROM funcionario

• Expressões artitméticas podem ser usadas na cláusula SELECT +, –, , /

• Exemplo: SELECT nome, salario + 200 FROM funcionario

(31)

A cláusula FROM

• Equivale a operação de Produto Cartesiano da Álgebra

• Lista as relações envolvidas na consulta • Exemplo: SELECT 

FROM funcionario, departamento

(32)

A cláusula FROM

• Quando mais de uma tabela é utilizada é necessário dar um apelido para elas que deve ser utilizado para diferenciar atributos iguais

• Exemplo: SELECT f.

FROM funcionario f, departamento d

WHERE f.codDepto = d.codDepto

(33)

A cláusula WHERE

• A cláusula where especifica as condições que o resultado precisa satisfazer

• Corresponde ao predicado de seleção da álgebra • Exemplo: SELECT nome, salario

FROM funcionario

WHERE salario > 2000

• operadores AND, OR e NOT podem ser usados • Exemplo: SELECT nome, salario

(34)

Renomeando atributos

• Renomeação de atributos

old-name as new-name

• Exemplo: SELECT nome as nomeCliente, (salario+200) as

comissao

(35)

Operações com Strings

• O SQL permite comparar strings com o operador like

• Pode ser combinado com outros caracteres

– % compara substrings

• Exemplo I: encontre o nome dos funcionarios cujos nomes iniciam com “Pedro”

select nome

from funcionario

(36)

Operações com Strings

• Exemplo II: encontre o nome dos funcionarios cujos nomes contém “Pedro” no nome

select nome

where nome like ‘%Pedro%'

(37)

Operações de Conjunto

• Envolvem ao menos 2 tabelas

• Interseção e União: elimina automaticamente repetições

– Relações precisam ser compatíveis (mesmo número de atributos)

– Union ALL e intersects ALL preserva duplicatas

(38)

(select nome from conta)

intersect

(select nome from emprestimo)

n Encontre os clientes que tenham empréstimos e contas

(select nome from conta)

union

(select nome from emprestimo)

Operações de Conjunto

(39)

Ordenando tuplas com

Order By

• Exemplo: Liste em ordem alfabética os funcionarios que trabalham no departamento financeiro

select distinct funcionario.nome from funcionario, departamento

where funcionario.codDepto=departamento.codDepto AND

departamento.nome=‘financeiro’ order by funcionario.nome

Order by pode ser em ordem descendente

(40)

Funções de Agregação

• Operam sobre múltiplos valores de uma coluna da tabela e retornam um valor

avg: média

min: valor mínimo

max: valor máximo

sum: soma de valores

count: número de valores

(41)

• Exemplos:

n Encontre a soma dos salários dos funcionários

select count(*) FROM cliente

select SUM(salario) FROM funcionario

n Encontre o número de tuplas da relação CLIENTE

Funções de Agregação

(42)

Funções de Agregação e

Group By

Encontre o total de funcionários de cada departamento

Nota: Atributos na cláusula SELECT que estão FORA da

função de agregação precisam aparecer na lista de atributos do GROUP BY

select d.nome, count(f.*) as numeroFuncionarios

FROM funcionario f, departamento d WHERE f.codDepto=d.codDepto GROUP BY d.nome

(43)

Funções de Agregação e

Having

• A função HAVING é utilizada para aplicar condições sobre grupos e não sobre uma única tupla

• Exemplo: Quais são os departamentos onde a soma dos salários dos funcionários ultrapassa 50.000

Nota: predicados da cláusula having são aplicados depois que os grupos foram gerados, mas a condição do where é aplicada antes da formação dos

grupos

select d.nome, sum(f.salario)

from funcionario f, departamento d where f.codDepto=d.codDepto

group by d.nome

(44)

Consultas Aninhadas

• Uma subconsulta select-from-where está aninhada dentro de outra consulta

• Exemplo: Selecione os clientes que são funcionários

select nomeCliente

From cliente

Where nomeCliente in (select nomeFuncionario

from funcionario)

(45)

Valores nulos

• Consulta sobre valores inexistentes

• Exemplo: Encontre os funcionarios que não possuem carteira de habilitação

– select nome

where carteiraHabilitacao is null

OBS: cuidado que valores nulos em operações matemáticas podem dar problemas

(46)

Álgebra SQL

 _nome(

(Médicos  X

 = Médicos.codm = Consultas.codm

(_codm( _{data = ’06/11/13’} (Consultas))) ) )

Select nome From Médicos Where codm in (select codm from Consultas where data = ‘06/11/13’) (_CPF(Funcionários))  (_CPF(Pacientes)) Select CPF From Funcionários Where CPF not in (select CPF from Pacientes) (_CPF(Médicos))  (_CPF(Pacientes)) Select CPF From Médicos Where CPF in (select CPF from Pacientes)

SQL e Álgebra Relacional

46

(47)

Visões e Autorização de

Acesso

(48)

Visões

• Na aula anterior aprendemos consultas

SQL. Hoje nos vamos ver visões, que são

uma outra forma de fazer consultas em

SQL

• Dois objetivos principais das visões:

– Simplificar consultas

– Autorização de acesso (segurança)

(49)

Conceito

• Visão: é um meio de prover ao usuário um “modelo personalizado” do banco de dados. • É uma relação que não armazena dados,

composta dinamicamente por uma consulta

que é previamente analisada e otimizada.

(50)

Visão

• Um SGBD pode dar suporte a um grande número de visões sobre qualquer conjunto de relações

• O SGBD armazena a definição da visão, mas ela é instanciada quando uma consulta sobre ela for executada

– Toda visão pode ser consultada mas nem toda

(51)

Criação e consultas em

visões

(52)

• Em SQL uma visão é definida como;

Create view nomeDaVisao <expressão de consulta>,

Onde <expressao de consulta> é qualquer expressão de consulta válida em SQL

• Projeto (codProj, tipo, descricao)

• ProjetoEmpregado (codProj, codEmp, dataInicial, dataFinal)

• Empregado (codEmp, nome, categoria, salario)

Esta visão terá os atributos especificados na consulta Create view vAltoEscalao as

select codEmp, nome, salario from empregado

where salario>10000

(53)

Visão sobre uma

relação

• Outra forma

Create view vAltoEscalao (a, b, c) as select codEmp, nome, salario

from empregado

where salario>10000

Esta visão terá os atributos a, b, c, que serão instanciados com os respectivos valores recuperados pela consulta (codEmp, nome, salario)

(54)

Visão com várias

relações

• Projetos do alto escalão ( visão com várias tabelas)

Create view vProjetoAltoEscalao as

select e.codEmp, e.nome, e.salario, p.descricao from empregado e, projeto p, projetoEmpregado pe where e.salario>10000 AND e.codEmp=pe.codEmp AND pe.codProj=p.codProj

(55)

Visão: recursividade

(visão sobre visão)

• Projetos do alto escalãoo (visão sobre visão)

• vAltoEscalao (codEmp, nome, salario)

Create view vProjetosAltoEscalao as

select e.codEmp, a.nome, a.salario, p.descricao

from altoEscalao a, projeto p, projetoEmpregado pe where a.codEmp=pe.codEmp AND

pe.codProj=p.codProj

(56)

Consultas em Visões

• Consultas SQL podem ser especificadas

sobre a visão

select nome

from vProjetosAltoEscalao

Where descricao = “Projeto A”

• Uma visão está sempre atualizada: Ao modificar tuplas nas tabelas envolvidas na visão, a visão vai automaticamente refletir as alterações

• A visão não é realizada quando é criada mas quando executamos uma consulta sobre ela.

(57)

Consultas em Visões

• Quando uma visão não é mais

necessária

podemos

eliminá-la,

usando o comando drop view

DROP VIEW nomeDaVisao

(58)

(59)

Autorização de Acesso

Objetivo

– proteção contra acessos mal intencionados

– controlar quais dados um usuário/grupo de usuários pode ter acesso

– controlar quais operações um usuário/grupo de usuários pode realizar sobre estes dados

(60)

Autorização de Acesso:

Exemplos

• Exemplo 1: Em um sistema bancário, um funcionário precisa saber os dados dos clientes, mas apenas os que tem conta poupança na agência

1899-6

• Exemplo 2: um funcionário de uma empresa deve ter acesso aos nomes dos funcionários e aos seus projetos, mas não de ter acesso ao salário dos funcionários

Empregado (#codEmp, nome, categoria, salario)

Projeto (#codProj, tipo, descricao)

ProjetoEmpregado (# codProj, # codEmp, dataInicial, dataFinal)

Cliente (#codCli, nome, endereco, codCidade)

Agencia (#codAgencia, nome, descricao)

(61)

Autorização de Acesso

Um usuário do Banco de dados pode ter diversas formas de autorização a partes do BD:

• O DBA é o superusuário do BD, que pode tudo

– alguns privilégios são exclusivos dele, como a recuperação do BD, a configuração de parâmetros do SGBD, etc.

– concede/retira (revoga) privilégios de acesso aos outros usuários

(62)

Autorização de Acesso

Duas formas principais de acesso:

• Nível de conta (usuário): o administrador do BD pode dar permissões aos usuários para criar esquemas, modificar e criar tabelas e selecionar dados

– O DBA estabelece permissões da conta, independente das relações do BD

(63)

Autorização de Acesso

• Nível de relação/visão: o DBA pode controlar o privilégio de acesso de cada usuário a relações ou visões específicas do BD

– definidas para SQL

– Para cada usuário podem ser dadas permissões de leitura(seleção), modificação e referência

(64)

Autorização de Acesso

• Autorização de leitura: permite apenas recuperar dados de uma relação

– permissão select

• Autorização de modificação: privilégios para

insert, delete e update

– Para insert e update é possível restringir os

atributos

• Autorização de referência: uma conta (de

usuário) pode fazer referência a uma relação ao especificar restrições de integridade

(65)

Autorização de Acesso

em SQL

• Lista basica: alter, delete, index, insert, select e update

• Sintaxe:

grant <lista de privilégios> on <nome da relação ou visão> to <lista de usuários> • Exemplo:

grant select on cliente to U1, U2, U3

grant update (saldo) on deposito to U1, U2

(66)

Autorização de Acesso:

Exemplos

• Exemplo 1: Em um sistema bancário, um funcionário precisa saber os

dados dos clientes, mas apenas os que tem conta poupança na agência 1899-6

• Solução: usar views

Create view vClientePoupancaAgencia as select c.codCli, c.nome, p.numConta from cliente c, poupanca p

where c.codCli=p.cod_cli and p.codAgencia=“1899-6”

Cliente (codCli, nome, endereco, codCidade)

Agencia (codAgencia, nome, descricao)

Poupança ( numConta, # codAgencia, # codCli, saldo)

(67)

Autorização de Acesso:

Exemplos

• Exemplo 2: um funcionário de uma empresa deve ter acesso aos nomes

dos funcionários e aos seus projetos, mas não de ter acesso ao salário dos funcionários

Create view vEmpregadoProjeto as

select e.codEmp, e.nome, p.tipo, p.descricao, j.dataInicial, j.dataFinal

from empregado e, projeto p, projetoEmpregado j where e.codEmp=j.codEmp and j.codProj=p.codProj

• grant select on vEmpregadoProjeto to U50

Empregado (codEmp, nome, categoria, salario)

(68)

Autorização de Acesso

Roles (Papéis): são interessantes quando um grupo de usuários tem as mesmas

restrições de acesso:

ex: vários caixas de um banco (várias agências) tem permissão para creditar e debitar valores na conta dos clientes. Ao invés de dar permissão de inclusão, alteração e exclusão para cada um dos caixas que tem este direito, cria-se o papel movimentação

Create role movimentaçao

E dá-se a permissão ao papel

Grant insert, update, delete ON TABELA to movimentaçao

Vincula todos os usuarios ao papel

(69)

Exemplo Completo

• Suponha que o DBA crie 4 contas U1, U2, U3 e U4. • 1) Somente U1 deve criar relações no banco

(privilégio de conta)

– grant createTab to U1

• 2) com essa autorização o usuário U1 pode criar relações e terá TODOS os privilégios sobre elas

– Suponha que U1 criou as relações

(70)

Exemplo Completo

• 3) Suponha que U1 quer dar ao usuário U2 permissão para incluir e remover tuplas em

Empregado e Projeto

– grant INSERT, DELETE ON EMPREGADO,PROJETO TO U2

• 4) Suponha que U1 quer dar ao usuário U3

permissão para recuperar tuplas em Empregado e seja capaz de PROPAGAR este privilégio

– grant SELECT ON EMPREGADO TO U3 WITH GRANT OPTION

(71)

Exemplo Completo

• 4) Agora U3 pode conceder privilégio de seleção para U4 sobre a relação Empregado

– grant SELECT ON EMPREGADO TO U4

– Obs: U4 não pode propagar este privilégio

• 5) Suponha que U1 queira revogar a permissão de U3

– REVOKE SELECT ON EMPEGADO FROM U3

(72)

Exemplo Completo

• 6)Suponha que U1 queira dar ao usuário U3

permissão apenas para consultar empregados que trabalhem no projeto AATOM.

Create view vEmpregadoProjeto as

select e.codEmp, e.nome, e.categoria, e.salario from empregado e, projeto p, projetoEmpregado j where e.codEmp=j.codEmp and j.codProj=p.codProj and p.descricao=“AATOM”

– grant SELECT ON vEMRPEGADOProjeto TO U3

Projeto (codProj, tipo, descricao)

(73)

(74)

Lista de Permissões no

PostgreSQL

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }

[,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...]

TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }

ON SEQUENCE sequencename [, ...]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }

ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]

GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }

ON SCHEMA schemaname [, ...]

GRANT { CREATE | ALL [ PRIVILEGES ] }

ON TABLESPACE tablespacename [, ...]

GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]

(75)

(76)

Stored Procedures

• É um conjunto de comandos SQL definidos pelo usuário que ficam armazenados num BD como um procedimento/função, para eventuais processamentos.

• São processamentos de tarefas da aplicação que residem no SGBD ao invés de no código da aplicação (cliente).

(77)

Stored Procedures

• Vantagens:

• 1.Desempenho

– Ex.: Seja a consulta

SELECT codigop, nome, COUNT(*) FROM Projeto p, Alocacao a

WHERE p.codproj = a.codigop GROUP BY p.codproj, p.nome

(78)

Stored Procedures

• Se vários usuários realizarem esta consulta o tráfego de rede será alto.

• se criarmos uma stored procedure para executar esta consulta, os usuários necessitarão apenas de um comando para executar a consulta anterior: EXEC nomeProcedimento;

• Outro ponto é a compilação, a consulta anterior seria compilada a cada chamada, enquanto o procedimento contendo a consulta seria compilado uma única vez

(79)

Stored Procedures -

Vantagens

• 2. Facilita o gerenciamento do BD, pois a consulta é escrita em um único lugar, portanto a manutenção desta torna-se mais eficaz e segura.

(80)

Stored Procedures -

Vantagens

• 3. Segurança: podemos usar stored procedures para limitar o acesso de alguns usuários ao BD. Desta forma, a maneira em que o BD pode ser modificado é estritamente definida.

(81)

Stored Procedures:

SQL/PSM

• SQL/PSM - Persistent Stored Modules: parte do padrão SQL relativo às Stored Procedures • No momento cada SGBD oferece sua própria

linguagem (Oracle PL/SQL, Microsoft Transact/SQL, etc)

• Em PSM, definimos módulos que são coleções de definições de funções ou procedimentos, declarações de tabelas temporárias, dentre outros.

(82)

Stored Procedures

-SQL/PSM

• Criando Funções e Procedimentos

– CREATE PROCEDURE <NOME> (<parâmetros>) declarações locais

corpo do procedimento

– CREATE FUNCTION <NOME> RETURNS <tipo> declarações locais

corpo da função

• obs.: parâmetros são do tipo modo-nome-tipo (onde modo indica IN, OUT ou INOUT)

(83)

Stored Procedures

-SQL/PSM

Exemplo:

CREATE PROCEDURE MudaEndereco ( IN endAntigo VARCHAR(255),

IN endNovo VARCHAR(255) )

UPDATE Empregado SET endereco = endNovo

WHERE endereco = endAntigo;

(84)

Stored Procedures

-SQL/PSM

Alguns Comandos:

1) Chamada a um procedimento:

CALL <nome procedure> (<lista argumentos>);

Obs.: CALL é aplicado apenas a Procedures (não a Function) Esta chamada pode ser realizada de vários lugares:

- Programa com SQL embutido

EXEC SQL CALL calcula(:x, 3);

- Como comando em outro procedimento ou função PSM: CALL calcula (10);

(85)

Stored Procedures

-SQL/PSM

2) Comando de Retorno (usado apenas em funções)

RETURN <expressão>; (OBS este comando não encerra a função)

) Declaração de variáveis locais: DECLARE <nome> <tipo>;

(86)

Stored Procedures

-SQL/PSM

3) Comando de atribuição

SET <variável> = <expressão>;

4) Grupo de comandos:

delimitados por BEGIN e END

5) Labels: colocamos labels em comandos precedendo estes pelo nome do label e dois pontos.

(87)

Stored Procedures

-SQL/PSM

6) Comandos condicionais 7) Laços IF <condição> THEN LOOP

<comandos> <Comandos> ELSEIF <condição> THEN END LOOP;

<comandos> …

ELSE <comandos> END IF;

(88)

Stored Procedures

-SQL/PSM

Exemplo: Função sobre o esquema Filmes que recebe um ano e nome de estúdio e retorna TRUE se aquele estúdio produziu apenas um filme preto e

branco naquele ano ou nada produziu.

CREATE FUNCTION PretoeBranco( a int, studio char[15]) RETURNS BOOLEAN

IF not exists (

select * from Filme where ano = a and nomeStudio = studio)

THEN RETURN TRUE; -- não faz a função retornar agora ELSEIF 1 <=

(select count(*) from Filme where ano = a and nomeStudio = nome and NOT emcores) THEN RETURN TRUE;

ELSE RETURN FALSE; END IF;

(89)

Stored Procedures

-SQL/PSM

Exemplo: Procedimento que calcula a média e variância de um estúdio CREATE PROCEDURE MeanVar ( IN s char[15],

OUT mean REAL, OUT variance REAL) DECLARE NotFound FOR SQLSTATE ‘02000’; DECLARE filmeCursor CURSOR FOR

select duracao from Filme where nomeStudio = s; DECLARE novaDuracao INTEGER;

DECLARE contaFilmes INTEGER; BEGIN SET mean = 0.0; SET variance = 0.0; SET contaFilmes = 0; OPEN filmeCursor; filmeLOOP: LOOP

FETCH filmeCursor INTO novaDuracao;

IF NotFound THEN LEAVE filmeCurdor END IF; SET contaFilmes = contaFilmes + 1;

SET mean = mean + novaDuracao;

SET variance = variance + novaDuracao * novaDuracao; END LOOP;

SET mean = mean / contaFilmes;

SET variance = variance/contaFilmes - mean * mean; CLOSE filmeCursor;

(90)

Stored Procedures

-SQL/PSM

For-Loops

usado para fazer iterator num cursor

FOR <nome laço> AS <nome cursor> CURSOR FOR <query>

DO

<comandos> END FOR;

Veja exemplo no próximo slide! - WHILE <condição> DO <comandos> END WHILE; - REPEAT <comandos> UNTIL <condição> END REPEAT;

(91)

Stored Procedures

-SQL/PSM

Exemplo: Mesmo procedimento de média e variância de estúdios, usando FOR-Loops

CREATE PROCEDURE MeanVar ( IN s char[15], OUT mean REAL, OUT variance REAL) DECLARE contaFilmes INTEGER;

BEGIN

SET mean = 0.0; SET variance = 0.0; SET contaFilmes = 0;

FOR filmeLOOP AS filmeCursor CURSOR FOR select duracao from Filme where nomeStudio = s; DO

SET contaFilmes = contaFilmes + 1; SET mean = mean + novaDuracao;

SET variance = variance + novaDuracao * novaDuracao; END FOR;

SET mean = mean / contaFilmes;

SET variance = variance/contaFilmes - mean * mean; END;

(92)

Stored Procedures

-SQL/PSM

Exceções em PSM:

É possível testar o SQLSTATE para verificar a ocorrência de erros e tomar uma decisão, quando erros ocorram.

Isto é feito através do EXCEPTION HANDLER que é

associado a blocos BEGIN END (o handler aparece dentro do bloco) Os componentes do handler são:

1) Lista de exceções a serem tratadas

2) Código a ser executado quando exceção ocorrer

3) Indicação para onde ir depois que o handler concluir

SINTAXE: DECLARE <onde ir> HANDLER FOR <condições> <comando>

As escolhas de <onde ir> são: - CONTINUE

- EXIT (sai do bloco BEGIN .. END) - UNDO

(93)

Stored Procedures

-SQL/PSM

Exemplo de exceções em PSM:

CREATE FUNCTION getSalario (mat integer) RETURNS FLOAT DECLARE NotFound CONDITION FOR SQLSTATE ‘02000’; DECLARE TooMany CONDITION FOR SQLSTATE ‘21000’;

BEGIN

DECLARE EXIT HANDLER FOR NotFound, TooMany RETURN NULL;

RETURN ( select salario from Empregado where where matricula = mat); END;

(94)

BIBLIOGRAFIA

• Elmasri & Navathe – Fundamentos de Bancos de Dados

• Carlos Alberto Heuser – Projeto de Banco de Dados

• Korth e Silberchatz – Sistema de Bancos de Dados

PROGRAMAÇÃO ORIENTADA A EVENTOS

(95)

BIBLIOGRAFIA

• Baptista, Cláudio. Banco de Dados II / Capítulo 1: SQL-PSM. Bacharelado em Ciência da

Computação UFCG/DSC.

• HEUSER, C.A. Projeto de Banco de Dados. 6ª Edição. Porto Alegre. Capítulos 2 e 3

• Korth, H. F.; Sudarshan, S; Silberschatz, A. Sistema de Banco de Dados. 5a ed. Editora Campus, 2006. - Capítulo 6

(96)

BIBLIOGRAFIA

• HEUSER, C.A. Projeto de Banco de Dados. 6ª Edição. Porto Alegre. Capítulos 2 e 3

• Korth, H. F.; Sudarshan, S; Silberschatz, A. Sistema de Banco de Dados. 5a ed. Editora Campus, 2006. - Capítulo 6

• Elmasri, R.; Navathe S. B. Sistemas de Banco de Dados. 4 ed. Editora Addison-Wesley. 2005. - Capítulo 3

(97)

BIBLIOGRAFIA

• Elmasri, R.; Navathe S. B. Sistemas de Banco de Dados. 4 ed. Editora Addison-Wesley. 2005. - Capítulo 3