Sistema de Banco de Dados, 5/E
Capítulo 4: SQL avançada
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Capítulo 4: SQL avançada
 Tipos de dados e esquemas da SQL
 Restrições de integridade
 Autorização
 SQL embutida
 SQL dinâmica
 Funções e construções procedurais **
 Consultas recursivas **
 Recursos SQL avançados **
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Tipos de dados internos na SQL
 date: Uma data de calendário contendo um ano (de quatro dígitos),
mês e dia do mês.
 Exemplo: date ‘2005-7-27’
 time: A hora do dia, em horas, minutos e segundos.
 Exemplo: time ‘09:00:30’
time ‘09:00:30.75’
 timestamp: Data e hora
 Exemplo: timestamp ‘2005-7-27 09:00:30.75’
 interval: Período de tempo
 Exemplo: interval ‘1’ dia
 Subtrair um valor de date/time/timestamp de outro gera um
valor de interval
 Valores de interval podem ser somados aos valores de
date/time/timestamp values
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Tipos de dados internos na SQL (cont.)
 Podem extrair valores de campos individuais de
date/time/timestamp
 Exemplo: extract (year from r.horainício)
 Podem converter tipos de string com date/time/timestamp
 Exemplo: cast <string-valued-expression> as date
 Exemplo: cast <string-valued-expression> as time
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Tipos definidos pelo usuário
 A construção create type na SQL cria um tipo definido pelo usuário
create type Dollars as numeric (12,2) final
 A construção create domain na SQL-92 cria tipos de domínio
definidos pelo usuário
create domain nome_pessoa char(20) not null
 Tipos e domínios são semelhantes. Domínios podem ter
restrições, como not null, especificadas neles.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Restrições de domínio
 As restrições de domínio são a forma mais elementar de
restrição de integridade. Elas testam valores inseridos no
banco de dados e testam consultas para garantir que as
comparações façam sentido.
 Novos domínios podem ser criados de tipos de dados
existentes.
 Exemplo:
create domain Dólares as numeric(12,2)
create domain Libras as numeric(12,2)
 Não podemos atribuir ou comparar um valor do tipo Dólares
com um valor do tipo Libras.
 Entretanto, podemos converter o tipo como abaixo
(cast r.A as Libras)
(Também devemos multiplicar pela taxa de
conversão dólar para libra)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Tipos de objeto grande
 Objetos grandes (fotografias, vídeos, arquivos CAD etc.) são
armazenados como um objeto grande:
 blob: binary large object – o o b é uma grande conjunto de
dados binários não interpretados (cuja interpretação fica a
cargo de uma aplicação externa ao sistema de banco de
dados).
 clob: character large object – o objeto é um grande conjunto
de dados de caractere.
 Quando uma consulta retorna um objeto grande, um ponteiro
é retornado em vez do próprio objeto grande.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Restrições de integridade
 As restrições de integridade protegem contra danos acidentais no
banco de dados, garantindo que as mudanças feitas no banco de
dados por usuários autorizados não resultem em uma perda da
consistência dos dados.
 Uma conta-corrente precisa ter um saldo maior que US$10.000
 Um salário de um funcionário de banco precisa ser pelo menos
de US$4 por hora
 Um cliente precisa ter um número de telefone (não nulo)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Restrições em uma única relação
 not null
 primary key
 unique
 check (P ), onde P é um predicado
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Restrição not null
 Declare que nome_agência para agência é not null
nome_agência char(15) not null
 Declare que o domínio Dólares é not null
create domain Dólares numeric(12,2) not null
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A restrição unique
unique ( A1, A2, …, Am)
A especificação unique diz que os atributos
A1, A2,..., Am
formam uma chave candidata.
Ao contrário das chaves primárias, as chaves candidatas podem ser não
nulas.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula check
check (P ), onde P é um predicado
Exemplo: Declare nome_agência como a chave primária para agência e
assegure-se de que os valores de ativo sejam não negativos.
create table agência
(nome_agência
char(15)
cidade_agência
char(30),
ativo integer,
primary key (nome_agência),
check (ativo > = 0))
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula check (cont.)

A cláusula check na SQL-92 permite que os domínios sejam restritos:
 Use a cláusula check para garantir que um domínio de salário horário
permita apenas valores maiores que um valor especificado.
create domain sal_hora numeric(5,2)
constraint teste_valor check(valor > = 4.00)
 O domínio tem uma restrição que garante que o salário por hora seja
maior que US$ 4.
 A cláusula constraint teste_valor é opcional; útil para indicar que
restrição uma atualização violou.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Integridade referencial
 Garante que um valor que aparece em uma relação para um
determinado conjunto de atributos também apareça para um certo
conjunto de atributos em outra relação.
 Exemplo: Se “Perryridge” é um nome de agência aparecendo em uma
das tuplas na relação conta, então, existe uma tupla na relação
agência para a agência “Perryridge”.
 As chaves primária, candidata e estrangeira podem ser especificadas
com parte da instrução SQL create table:
 A cláusula primary key lista os atributos que compõem a chave
primária.
 A cláusula unique key lista os atributos que compõem a chave
candidata.
 A cláusula foreign key lista os atributos que compõem a chave
estrangeira e o nome da relação referenciada pela chave estrangeira.
Como padrão, uma chave estrangeira referencia os atributos de chave
primária da tabela referenciada.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Integridade referencial na SQL – Exemplo
•
create table cliente
(nome_cliente char(20),
rua_cliente char(30),
cidade_cliente char(30),
primary key (nome_cliente))
create table agência
(nome_agência char(15),
cidade_agência char(30),
ativo
numeric(16,2),
primary key (nome_agência),
check (ativo >= 0))
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Integridade referencial na SQL (cont.)
•
create table conta
(número_conta char(10),
nome_agência char(15),
saldo
numeric(12,2),
primary key (número_conta),
foreign key (nome_agência) references agência,
check (saldo >= 0))
create table depositante
(nome_cliente char(20),
número_conta char(10),
primary key (nome_cliente, número_conta),
foreign key (nome_cliente) references cliente,
foreign key (número_conta) references conta)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Afirmações
 Uma afirmação é um predicado expressando uma condição que
desejamos que o banco de dados sempre satisfaça.
 Uma afirmação na SQL tem a forma
create assertion <nome-afirmação> check <predicado>
 Quando uma afirmação é criada, o sistema testa sua validade, e a
testa novamente em cada atualização que pode violar a
afirmação.
 Esse teste pode introduzir uma quantidade significativa de
overhead; portanto, as afirmações devem ser usadas com
muito critério.
 A afirmação de
para todo X, P(X)
é obtida de uma maneira aproximada usando
não existe X tal que not P(X)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Exemplo de afirmação

Cada empréstimo tem pelo menos um cliente que mantém uma conta com
um saldo mínimo de US$ 1000.
create assertion restrição_saldo check
(not exists (
select *
from empréstimo
where not exists (
select *
from tomador, depositante, conta
where empréstimo.número_empréstimo =
tomador.número_empréstimo)
and tomador.nome_cliente = depositante.nome_cliente
and depositante.número_conta = conta.número_conta
and conta.saldo >= 1000)))
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Exemplo de afirmação

A soma de todas as quantias de empréstimo para cada agência
precisa ser menor que a soma de todos os saldos de conta na
agência.
•
create assertion restrição_soma check
(not exists (select *
from agência
where (select sum(quantia)
from empréstimo
where empréstimo.nome_agência =
agência.nome_agência)
>= (select sum(saldo)
from conta
where conta.nome_agência =
agência.nome_agência)))
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Autorização
Formas de autorizações sobre partes do banco de dados:

Read – permite leitura, mas não modificação dos dados.

Insert – permite inserção de novos dados, mas não modificação dos
dados existentes.

Update – permite modificação, mas não exclusão dos dados.

Delete – permite exclusão dos dados.
Formas de autorização para modificar o esquema de banco de dados
(discutidas no Capítulo 8):

Index – permite a criação e exclusão de índices.

Resources – permite a criação de novas relações.

Alteration – permite a inclusão ou exclusão de atributos em uma
relação.

Drop – permite a exclusão de relações.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Especificação de autorização na SQL
 A instrução grant é usada para conferir autorização
grant <lista-privilégios>
on <nome-relação ou nome-view> to <lista_usuários/papéis>
 <lista_usuários> é:
 Um ID de usuário
 public, que permite a todos os usuários válidos o privilégio concedido
 Uma função (mais sobre isso no Capítulo 8)
 Conceder um privilégio sobre uma view não implica conceder quaisquer
privilégios sobre as relações subjacentes.
 O concessor do privilégio precisa já possuir o privilégio sobre o item
especificado (ou ser o administrador do banco de dados).
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Privileges in SQL
 select: permite acesso de leitura à relação, ou a capacidade
de consultar usando a view
 Exemplo: Conceda aos usuários U1, U2, e U3
autorização select sobre a relação agência:
grant select on agência to U1, U2, U3
 insert: a capacidade de inserir tuplas
 update: a capacidade de atualizar a instrução SQL update
 delete: a capacidade de excluir tuplas
 all privileges: usado com uma forma reduzida para todos os
privilégios permitidos
 mais no Capítulo 8
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Revoking Authorization in SQL
 A instrução revoke é usada para revogar autorização.
• revoke <lista-privilégios> on <nome-relação ou nome-view> from <lista-
usuários>
 Exemplo:
• revoke select on agência from U1, U2, U3
 <lista-privilégios> pode ser all para revogar todos os privilégios que o
revogado possa deter.
 Se <lista-privilégios> incluir public, todos os usuários perdem o
privilégio exceto aqueles que o concederam explicitamente.
 Se o mesmo privilégio foi concedido duas vezes para o mesmo usuário
por diferentes concessores, o usuário pode conservar o privilégio
após a revogação.
 Todos os privilégios que dependem do privilégio sendo revogado
também são revogados.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
SQL embutida
 O padrão SQL define incorporações da SQL em diversas
linguagens de programação, como C, Java e Cobol.
 Uma linguagem em que consultas SQL são embutidas é chamada
de linguagem nativa, e as estruturas SQL permitidas na linguagem
nativa constituem a SQL embutida.
 A forma básica dessas linguagens segue a da incorporação
System-R da SQL na PL/I.
 A instrução EXEC SQL é usada para identificar SQL embutida para
o processador.
EXEC SQL <instrução SQL embutida> END-EXEC
Nota: Isso varia conforme a linguagem (por exemplo, o embutimento
Java usa # SQL { …. }; )
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta de exemplo
 De dentro de uma linguagem nativa, encontre os nomes e cidades
dos clientes que possuem mais do que o valor de quantia em
qualquer conta.
 Especifique a consulta em SQL e declare um cursor para ela
EXEC SQL
declare c cursor for
select nome_cliente, cidade_cliente
from depositante, cliente, conta
where depositante.nome_cliente = cliente.nome_cliente
conta.número_conta = depositante.número_conta
and conta.saldo > :quantia
END-EXEC
Korth • Silberschatz • Sundarshan
and
Sistema de Banco de Dados, 5/E
SQL embutida (cont.)

A instrução open faz com que a consulta seja avaliada
EXEC SQL open c END_EXEC

A instrução fetch faz com que os valores de uma tupla no resultado da consulta
seja colocado nas variáveis de linguagem nativa.
EXEC SQL fetch c into :cn, :cc END_EXEC
Chamadas repetidas para fetch obtêm sucessivas tuplas no resulado da
consulta

Uma variável chamada SQLSTATE na área de comunicação SQL (SQLCA) é
definida para ‘02000’ para indicar que não há mais dados disponíveis

A instrução close faz com que o sistema de banco de dados exclua a relação
temporária que armazena o resultado da consulta.
EXEC SQL close c END_EXEC
Nota: Os detalhes acima variam com a linguagem. Por exemplo, a incorporação
Java define repetidores Java para percorrer as tuplas resultado.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Atualizações através de cursores
 Pode atualizar tuplas buscadas pelo cursor declarando que o cursor
é para atualização
•
declare c cursor for
select *
from conta
where nome_agência = ‘Perryridge’
for update
 Para atualizar a tupla no local atual do cursor c:
•
update conta
set saldo = saldo + 100
where current of c
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
SQL dinâmica
 permite que programas construam e submetam consultas SQL
em tempo de execução.
 Exemplo do uso da SQL dinâmica de dentro de um programa C.
char * prog-sql = “update conta
set saldo = saldo * 1,05
where número_conta = ?”;
EXEC SQL prepare prog-din from :prog-sql;
char conta[10] = “A-101”;
EXEC SQL execute prog-din using :conta;
 O programa SQL dinâmico contém um ?, que é um marcador de
lugar para um valor que é fornecido quando o programa SQL é
executado.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
ODBC e JDBC
 API (interface de programa de aplicação) para um programa
interagir com um servidor de banco de dados
 A aplicação faz chamadas para
 Conectar-se com o servidor de banco de dados
 Enviar comandos SQL ao servidor de banco de
dados
 Transferir tuplas do resultado, uma por uma, para
as variáveis de programa
 ODBC (Open Database Connectivity) funciona com C, C++,
C# e Visual Basic
 JBDC (Java Database Connectivity) funciona com Java
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
ODBC

Padrão Open DataBase Connectivity(ODBC)
 Padrão para um programa de aplicação se comunicar com um
servidor de banco de dados.
 Interface de programa de aplicação (API) para
 Abrir uma conexão com um banco de dados,
 Enviar consultas e atualizações,
 Recuperar os resultados

Aplicações como GUI, planilhas etc. podem usar ODBC
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
ODBC (cont.)

Cada sistema de banco de dados com suporte a ODBC fornece uma biblioteca
que precisa ser vinculada com ao programa cliente.

Quando o programa cliente faz uma chamada API ODBC, o código na biblioteca
se comunica com o servidor para realizar a ação requisitada e buscar
resultados.

O programa primeiramente aloca um ambiente SQL e, depois, um descritor de
conexão de banco de dados.

O programa abre a conexão de banco de dados usando SQLConnect().
Parâmetros para SQLConnect:
 descritor de conexão,
 o servidor ao qual se conectar,
 o identificador de usuário,
 senha

Também precisa especificar tipos de argumentos:
 SQL_NTS indica que o argumento anterior é uma string terminada em nulo.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Código ODBC
 int ODBCexample()
{
•
RETCODE error;
•
HENV
env;
•
HDBC
conn; /* conexão a banco de dados */
•
SQLAllocEnv(&env);
•
SQLAllocConnect(env, &conn);
•
/* ambiente */
SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi",
SQL_NTS, "avipasswd", SQL_NTS);
•
{ …. Faça trabalho real … }
•
SQLDisconnect(conn);
•
SQLFreeConnect(conn);
•
SQLFreeEnv(env);
}
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Código ODBC (cont.)




O programa envia comandos para o banco de dados usando SQLExecDirect
As tuplas resultado são buscadas usando SQLFetch()
SQLBindCol() vincula variáveis de linguagem C a atributos do resultado da
consulta
 Quando uma tupla é buscada, seus valores de atributo são automaticamente
armazenados em variáveis C correspondentes.
 Argumentos para SQLBindCol()
 Variável stmt ODBC, posição de atributo no resultado da consulta
 Conversão de tipo de SQL para C
 O endereço da variável
 Para tipos de tamanho varredura, como arrays de caractere,
– O tamanho máximo da variável
– Local para armazenar o tamanho real quando uma tupla é buscada
– Nota: Um valor negativo retornado para o campo length indica um
valor nulo
O bom estilo de programação exige que o resultado de toda chamada de função
seja verificado para garantir que não haja qualquer erro; omitimos a maioria
dessas verificações por brevidade.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Código ODBC (cont.)
 Corpo principal do programa
•
char branchname[80];
float balance;
int lenOut1, lenOut2;
HSTMT stmt;
SQLAllocStmt(conn, &stmt);
char * sqlquery = “select branch_name, sum (balance)
from account
group by branch_name”;
error = SQLExecDirect(stmt, sqlquery, SQL_NTS);
if (error == SQL_SUCCESS) {
SQLBindCol(stmt, 1, SQL_C_CHAR, branchname 80, &lenOut1);
SQLBindCol(stmt, 2, SQL_C_FLOAT, &balance, 0 , &lenOut2);
while (SQLFetch(stmt) == SQL_SUCCESS) {
printf (“%s%g\n”, branchname, balance);
}
}
SQLFreeStmt(stmt, SQL_DROP);
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Mais recursos ODBC

Instrução preparada
 Instrução SQL preparada: compilada no banco de dados
 Pode ter marcadores de lugar: Por exemplo, inserir em valores de conta (?,?,?)
 Executa repetidamente com valores reais para os marcadores

Recursos de metadados
 Encontrar todas as relações no banco de dados e
 Encontrar os nomes e tipos de colunas de um resultado de consulta ou uma
relação no banco de dados.

Como padrão, cada instrução SQL é tratada como uma transação separada que é
confirmada automaticamente.
 Pode desativar a confirmação automática em uma conexão
 SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0)
 As transações precisam ser confirmadas por ou revertidas por
 SQLTransact(conn, SQL_COMMIT) or
 SQLTransact(conn, SQL_ROLLBACK)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Níveis de conformidade da ODBC
 Os níveis de conformidade especificam subconjuntos da funcionalidade
definida pelo padrão.
 Básico
 Nível 1 requer suporte para consulta a metadados
 Nível 2 requer a capacidade de enviar e recuperar arrays de valores
de parâmetro e recuperar informações de catálogo mais detalhadas.
 O padrão SQL define uma interface em nível de chamada (CLI), que é
semelhante à interface ODBC, mas com algumas diferenças sutis.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
JDBC
 JDBC é uma API Java para comunicação com sistemas de
banco de dados que aceitam SQL
 A JDBC aceita vários recursos para consulta e atualização
de dados, e para recuperar resultados de consulta.
 A JDBC também aceita recuperação de metadados, como
consulta sobre relações presentes no banco de dados e os
nomes e tipos dos atributos de relação.
 Modelo para comunicação com o banco de dados:
 Abra uma conexão
 Crie um objeto “Instrução”
 Execute consultas usando o objeto Instrução para enviar
consultas e buscar resultados
 Mecanismo de exceção para tratamento de erros
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Código JDBC
• public static void JDBCexample(String dbid, String userid, String
–
–
passwd)
{
•
try {
– Class.forName ("oracle.jdbc.driver.OracleDriver");
– Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@aura.bell-labs.com:2000:bankdb", userid,
passwd);
•
Statement stmt = conn.createStatement();
•
… Do Actual Work ….
•
stmt.close();
•
conn.close();
•
}
•
catch (SQLException sqle) {
•
System.out.println("SQLException : " + sqle);
•
}
}
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Código JDBC (cont.)
 Atualize para o banco de dados
try {
stmt.executeUpdate( "insert into account values
('A-9732', 'Perryridge', 1200)");
} catch (SQLException sqle) {
System.out.println("Could not insert tuple. " + sqle);
}
 Execute consulta e busque e imprima os resultados
ResultSet rset = stmt.executeQuery( "select nome_agência,
avg(saldo)
from conta
group by nome_agência");
while (rset.next()) {
System.out.println(
rset.getString("nome_agência") + " " +
rset.getFloat(2));
}
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Detalhes do código JDBC
 Obtendo campos de resultado:
 rs.getString(“nomeagencia”) e rs.getString(1) equivalente se
nomeagencia for o primeiro argumento do resultado
selecionado.
 Lidando com valores nulos
• int a = rs.getInt(“a”);
• if (rs.wasNull()) Systems.out.println(“Got null value”);
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Extensões procedurais e procedimentos
armazenados

A SQL fornece uma linguagem de módulo
 Permite a definição de procedimentos em SQL, com instruções
if-then-else, loops for e while etc.
 Mais no Capítulo 9

Procedimentos armazenados
 Podem armazenar procedimentos no banco de dados
 Depois executá-los usando a instrução call
 Permitem que aplicações externas operem no banco de dados
sem conhecer os detalhes internos

Esses recursos são abordados no Capítulo 9 (Bancos de dados
baseados em objeto)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções e procedimentos

A SQL:1999 aceita funções e procedimentos
 Funções/procedimentos podem ser escritos na própria SQL, ou
em uma linguagem de programação externa
 As funções são especialmente úteis com tipos de dados
especializados, como imagens e objetos geométricos
 Exemplo: funções para verificar se polígonos se sobrepõem ou
para comparar a semelhança de imagens
 Alguns sistemas de banco de dados aceitam funções com valor de
tabela, que podem retornar uma relação como resultado

A SQL:1999 também aceita um rico conjunto de construções
imperativas, incluindo
 Loops, if-then-else, atribuição

Muitos bancos de dados possuem extensões procedurais
proprietárias para a SQL que diferem da SQL:1999
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções SQL
 Defina uma função que, dado o nome de um cliente, retorna a
contagem do número de contas pertencentes a esse cliente.
–
create function contagem_conta (nome_cliente varchar(20))
returns integer
begin
declare contagem_c integer;
select count(*) into contagem_c
from depositante
where depositante.nome_cliente = nome_cliente
return contagem_c;
end
 Encontre os nomes e endereços de todos os clientes com mais de
uma conta.
–
select nome_cliente, rua_cliente, cidade_cliente
from cliente
where contagem_conta (nome_cliente ) > 1
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções de tabela

A SQL:2003 acrescentou funções que retornam uma relação como
resultado

Exemplo: Encontre todas as contas que um determinado cliente possui
create function contas_de (nome_cliente char(20))
returns table (
número_conta char(10),
nome_agência char(15),
saldo numeric(12,2))
return table
(select número_conta, nome_agência, saldo
from conta
where exists (
select *
from depositante
where depositante.nome_cliente = contas_de.nome_cliente
and depositante.número_conta = conta.número_conta))
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções de tabela (cont.)
 Uso
select *
from table (contas_de (‘Smith’))
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Procedimentos SQL

A função contagem_autor poderia, alternativamente, ser escrita como
procedimento:
create procedure proc_contagem_conta (in título varchar(20), out contagem_a
integer)
begin
select count(autor) into contagem_a
from depositante
where depositante.nome_cliente = proc_contagem_conta.nome_cliente
end

Procedimentos podem ser chamados de um procedimento SQL ou de SQL
embuitido, usando a instrução call.
declare contagem_a integer;
call proc_contagem_conta(‘Smith’, contagem_a);

Os procedimentos e funções também podem ser chamados a partir de SQL
dinâmica

A SQL:1999 permite mais de um procedimento/função do mesmo nome (chamado
de overloading), desde que o número de argumentos seja diferente ou, pelo menos,
os tipos dos argumentos sejam diferentes
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Construções procedurais
 Instrução composta: begin ... end
 Pode conter várias instruções SQL entre o begin e o end
 Variáveis locais podem ser declaradas dentro de uma
instrução composta
 Instruções while e repeat:
– declare n integer default 0;
while n < 10 do
set n = n + 1;
end while
repeat
set n = n 1;
until n = 0
end repeat
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Construções procedurais (cont.)
 Loop For
 Permite interação sobre todos os resultados de uma
consulta
 Exemplo: Encontre o total de todos os saldos na agência
Perryridge
declare n integer default 0;
for r as
select saldo from conta
where nome_agência = ‘Perryridge’
do
set n = n + r.saldo
end for
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Construções procedurais (cont.)



Instruções condicionais (if-then-else)
Por exemplo, para encontrar a soma dos saldos para cada uma de três categorias de
conta (com saldo < 1000, com saldo >= 1000 e < 5000, e com saldo >= 5000)
if r.saldo < 1000
then set l = l + r.saldo
elseif r.saldo < 5000
then set m = m + r.saldo
else set h = h + r.saldo
end if
A SQL:1999 também aceita uma instrução case semelhante à instrução case da linguagem
C
Sinalização de condições de exceção e declaração de handlers que possam manipular
exceções
declare sem_estoque condition
declare exit handler for sem_estoque
begin
…
.. signal sem_estoque
end
 O handler aqui é exit – faz com que o fechamento begin..end seja encerrado
 Outras ações possíveis na exceção
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
External Language Functions/Procedures

A SQL:1999 permite o uso de funções e procedimentos escritos
em outras linguagens, como C ou C++.

Declaração de procedimentos e funções de linguagem externos
create procedure proc_contagem_conta (in nome_cliente
varchar(20), out contagem integer)
language C
external name ‘/usr/avi/bin/proc_contagem_conta’
create function contagem_conta (nome_cliente varchar(20))
returns inteiro
language C
external name ‘/usr/avi/bin/contagem_conta’
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Rotinas de linguagem externa

Vantagens dos procedimentos/funções de linguagem externa:
 Mais eficiente para muitas operações; e mais poder expressivo

Desvantagens:
 Código para implementar função pode precisar ser carregado no
sistema de banco de dados e executado no espaço de endereço do
sistema de banco de dados
 Risco de dano acidental das estruturas de banco de dados
 Risco de segurança, permitindo que usuários acessem dados não
autorizados
 Existem alternativas, que oferecem boa segurança ao custo de um
desempenho potencialmente menor
 Execução direta no espaço do sistema de banco de dados é usada
quando a eficiência é mais importante do que a segurança
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E

Segurança com rotinas de linguagem
externa
Para lidar com problemas de segurança
 Use técnicas de sandbox
 É o uso de uma linguagem segura, como Java, que não pode ser
usada para acessar/danificar outras partes do código de banco de
dados
 Ou execute funções/procedimentos de linguagem externa em um
processo separado, sem acesso à memória do processo de banco de
dados
 Parâmetros e resultados comunicados através de comunicação
interprocessos

Ambos apresentam overhead de desempenho

Muitos sistemas de banco de dados aceitam os dois métodos acima como
execução direta no espaço de endereço do sistema de banco de dados
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Recursão na SQL

A SQL:1999 permite definição de view recursiva

Exemplo: Encontre todos os pares funcionário-gerente, onde o funcionário
está sugeito ao gerente direta ou indiretamente (ou seja, o gerente do
gerente, o gerente do gerente do gerente etc.)
with recursive func (nome_funcionário, nome_gerente) as (
select nome_funcionário, nome_gerente
from gerente
union
select gerente.nome_funcionário, func.nome_gerente
from gerente, func
where gerente.nome_gerente = empl.nome_funcionário
)
select *
from func
Essa view de exemplo, func, é chamada o fechamento transitivo da relação
gerente
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
O poder da recursão

As views recursivas possibilitam escrever consultas, como consultas de
fechamento transitivo, que não podem ser escritas sem recursão ou
repetição.
 Intuição: Sem recursão, um programa não recursivo e não repetitivo
pode realizar apenas um número fixo de junções de gerente consigo
mesmo
 Isso pode fornecer apenas um número fixo de níveis de gerentes
 Dado um programa, podemos construir um banco de dados com um
número maior de níveis de gerentes em que o programa não
funcionará
 O próximo slide mostra uma relação gerente e cada etapa do processo
repetitivo que constrói func de sua definição recursiva. O resultado final é
chamado o ponto fixo da definição de view recursiva.

As views recursivas precisam ser monotônicas. Ou seja, se acrescentarmos
tuplas a gerente, a view conterá todas as tuplas que continha antes, e talvez
mais
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Exemplo de cálculo de ponto fixo
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Recursos SQL avançados **

Criação de tabelas que possuem o mesmo esquema de uma tabela
existente:
create table temp_conta like conta

A SQL:2003 permite que subconsultas ocorram em qualquer lugar
que um valor seja necessário, desde que a subconsulta retorne
apenas um valor. Isso também se aplica a atualizações
A SQL:2003 permite que subconsultas na cláusula from acessem
atributos de outras relações na cláusula
select nome_cliente, num_contas
from cliente, lateral(
select count(*)
from conta
where conta.nome_cliente = cliente.nome_cliente)
as este_cliente (num-contas)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Recursos SQL avançados (cont.)

A construção merge permite processamento em lote de atualizações.

Exemplo: A relação fundos_recebidos (número_conta, quantia ) tem
um lote de depósitos a ser acrescentado à conta apropriada na
relação conta
– merge into conta as A
using
(select *
from fundos_recebidos) as F
on (A.número_conta = F.número_conta)
when matched then
update set saldo = saldo+F.quantia
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Fim do capítulo
Korth • Silberschatz • Sundarshan