Capítulo 4: SQL avançada

Korth • Silberschatz • Sundarshan

Sistema de Banco de Dados, 5/E

Capítulo 4: SQL avançada



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 **



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



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



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.



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)



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.



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)



Restrições em uma única relação

not null

primary key

unique

check (P ), onde P é um predicado



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



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.



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))



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.



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.



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))



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)



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)



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)))



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)))



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.



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).



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



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.



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 { …. }; )



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 SQLdeclare c cursor forselect nome_cliente, cidade_clientefrom depositante, cliente, contawhere depositante.nome_cliente = cliente.nome_cliente and conta.número_conta = depositante.número_contaand conta.saldo > :quantia

END-EXEC



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_EXECChamadas 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.



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



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.



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



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



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.



Código ODBC int ODBCexample()

{

• RETCODE error;

• HENV env; /* ambiente */

• HDBC conn; /* conexão a banco de dados */

• SQLAllocEnv(&env);

• SQLAllocConnect(env, &conn);

• SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS);

• { …. Faça trabalho real … }

• SQLDisconnect(conn);

• SQLFreeConnect(conn);

• SQLFreeEnv(env);

}



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.



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);



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)



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.



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



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);• }

– }



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));

}



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”);



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)



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



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_clientefrom clientewhere contagem_conta (nome_cliente ) > 1



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))



Funções de tabela (cont.)

Uso

select *from table (contas_de (‘Smith’))



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



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 whilerepeat set n = n 1;until n = 0end repeat



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



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.saldoelseif r.saldo < 5000 then set m = m + r.saldoelse set h = h + r.saldoend 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 conditiondeclare exit handler for sem_estoquebegin…

.. signal sem_estoqueend

O handler aqui é exit – faz com que o fechamento begin..end seja encerrado Outras ações possíveis na exceção



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 Cexternal name ‘/usr/avi/bin/proc_contagem_conta’

create function contagem_conta (nome_cliente varchar(20))returns inteirolanguage Cexternal name ‘/usr/avi/bin/contagem_conta’



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



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



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



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



Exemplo de cálculo de ponto fixo



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)



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



Fim do capítulo

Documents

Capítulo 4: SQL avançada