Acetatos

Capítulo 4: SQL

Estrutura básica

Operações com conjuntos
 Funções de agregação

Valores nulos

Subconsultas embebidas

Relações derivadas
 Vistas

Modificação da Base de Dados
 Embedded SQL
 Linguagem de Definição de Dados
Database System Concepts
4.1.1
©Silberschatz, Korth and Sudarshan (modificado)
Embedded SQL
 SQL fornece uma linguagem declarativa para manipulação de bases de
dados. Facilita a manipulação e permite optimizações muito difíceis se
fossem programadas em linguagens imperativas.
 Mas há razões para usar SQL juntamente com linguagens de
programação gerais (imperativas):
 o SQL não tem a expressividade de uma máquina de Turing (há perguntas
impossíveis de codificar em SQL – e.g. fechos transitivos)
 usando SQL juntamente com linguagens gerais é possível suprir esta
deficiência
 nem tudo nas aplicações de bases de dados é declarativo (e.g. acções de
afixar resultados, interfaces, etc)
 Essa parte pode ser programado em linguagens gerais
 O standard SQL define uma série de embeddings, para várias linguagens
de programação (e.g. Pascal, PL/I, C, C++, Cobol, etc).
 À linguagem na qual se incluem comandos SQL chama-se linguagem
host. Às estruturas SQL permitidas na linguagem host chama-se SQL
embutido (ou embedded SQL)
Database System Concepts
4.1.2
©Silberschatz, Korth and Sudarshan (modificado)
Embedded SQL
 Permite acesso a bases e dados SQL, via outra linguagens de
programação.
 Toda a parte de acesso e manipulação da base de dados é feito
através de código embutido. Todo o processamento associado é
feito pelo sistema de bases de dados. A linguagem host recebe os
resultados e manipula-os.
 O código tem que ser pré-processado. A parte SQL é
transformada em código da linguagem host, mais chamadas a
run-time do servidor.
 A expressão EXEC SQL é usado para identificar código SQL
embutido
EXEC SQL <embedded SQL statement > END-EXEC
Nota: Este formato varia de linguagem para linguagem. E.g. em C
usa-se ‘;’ em vez do END-EXEC. Em Java usa-se # SQL { …. } ;
Database System Concepts
4.1.3
©Silberschatz, Korth and Sudarshan (modificado)
Cursores
 Para executar um comando SQL numa linguagem host é
necessário começar por declarar um cursor para esse comando.
 O comando pode conter variáveis da linguagem host, precedidas
de :
 E.g. Encontrar os nome e cidades de clientes cujo saldo seja
superior a amount
EXEC SQL
declare c cursor for
select customer-name, customer-city
from account natural inner join depositor
natural inner join customer
where account.balance > :amount
END-EXEC
Database System Concepts
4.1.4
©Silberschatz, Korth and Sudarshan (modificado)
Embedded SQL (Cont.)
 O comando open inicia a avaliação da consulta no cursor
EXEC SQL open c END-EXEC
 O comando fetch coloca o valor de um tuplo em variáveis da
linguagem host.
EXEC SQL fetch c into :cn, :cc END-EXEC
 Chamadas sucessivas a fetch obtêm tuplos sucessivos
 Uma variável chamada SQLSTATE na SQL communication area
(SQLCA) toma o valor ‘02000’ quando não há mais dados.
 O comando close apaga a relação temporária, criada pelo
open, que contem os resultados da avaliação do SQL.
EXEC SQL close c END-EXEC
Database System Concepts
4.1.5
©Silberschatz, Korth and Sudarshan (modificado)
Modificações com Cursores
 Como não devolvem resultado, o tratamento de modificações
dentro doutras linguagens é mais fácil.
 Basta chamar qualquer comando válido SQL de insert, delete,
ou update entre EXEC SQL e END SQL
 Em geral, as váriaveis da linguagem host só podem ser usadas
em locais onde se poderiam colocar variáveis SQL.
 Não é possível construir comandos (ou parte deles)
manipulando strings da linguagem host
Database System Concepts
4.1.6
©Silberschatz, Korth and Sudarshan (modificado)
Dynamic SQL
 Permite construir e (mandar) executar comandos SQL, em run-
time.
 E.g. (chamando dynamic SQL, dentro de um programa em C)
char * sqlprog = “update account
set balance = balance * 1.05
where account-number = ?”
EXEC SQL prepare dynprog from :sqlprog;
char account [10] = “A-101”;
EXEC SQL execute dynprog using :account;
 A string contém um ?, que indica o local onde colocar o valor a
ser passado no momento da chamada para execução.
Database System Concepts
4.1.7
©Silberschatz, Korth and Sudarshan (modificado)
Linguagens proprietárias
 O embedded SQL permite a uma linguagem host aceder e





manipular uma base de dados. Mas não ajuda muito na
apresentação dos resultados das consultas
A maior parte dos sistemas comerciais incluem linguagens
proprietárias que, para além do embedded SQL, têm primitivas
próprias para (entre outras) criar interfaces no ecrã (forms) e
para formatar dados para apresentação de relatórios (reports).
Algumas destas linguagens têm ainda construtores de mais alto
nível, para trabalhar sobre cursores.
Tipicamente os programas nestas linguagens, compilam para
outras linguagens (eg C) embedded SQL.
Os sistemas comerciais costumam ainda ter aplicações de
geração fácil de programas na linguagem proprietária
No Oracle a linguagem proprietária é o PLSQL. O Forms e o
Reports são aplicações que geram PLSQL.
Database System Concepts
4.1.8
©Silberschatz, Korth and Sudarshan (modificado)
ODBC
 Standard Open DataBase Connectivity(ODBC)
 Standard para comunicação entre programas e servidores de bases
de dados
 application program interface (API) para
 Abrir uma ligação a uma base de dados
 Enviar consultas e pedidos de modificações
 Obter os resultados
 Aplicações diversas (eg GUI, spreadsheets, etc) podem usar
ODBC
Database System Concepts
4.1.9
©Silberschatz, Korth and Sudarshan (modificado)
ODBC (Cont.)
 Um sistema de bases de dados que suporte ODBC tem uma
“driver library” que tem que ser ligada com o programa cliente.
 Quando o cliente faz uma chamada à API ODBC, o código da
library comunica com o servidor, que por sua vez executa a
chamada e devolve os resultados.
 Um programa ODBC começa por alocar um ambiente SQL, e
um connection handle.
 Para abrir uma ligação a uma BD, usa-se SQLConnect(). Os
parâmetros são:
 connection handle,
 servidor onde ligar
 username,
 password
Database System Concepts
4.1.10
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de código ODBC
 int ODBCexample()
{
RETCODE error;
HENV env; /* environment */
HDBC conn; /* database connection */
SQLAllocEnv(&env);
SQLAllocConnect(env, &conn);
SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS,
"avipasswd", SQL_NTS);
{ …. Manipulação propriamente dita … }
SQLDisconnect(conn);
SQLFreeConnect(conn);
SQLFreeEnv(env);
}
Database System Concepts
4.1.11
©Silberschatz, Korth and Sudarshan (modificado)
ODBC (Cont.)
 Os programas enviam comandos SQL à base de dados usando
SQLExecDirect
 Os tuplos resultado são obtidos via SQLFetch()
 SQLBindCol() liga variáveis da linguagem a atributos do resultado
do SQL
 Quando um tuplo é obtido com um fetch, os valores dos seus
atributos são automaticamente guardados nas ditas variáveis.
Database System Concepts
4.1.12
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de código ODBC
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);
Database System Concepts
4.1.13
©Silberschatz, Korth and Sudarshan (modificado)
JDBC
 JDBC é uma API Java para comunicar com sistemas de bases
de dados que suportam o SQL.
 JDBC suporta várias formas de consulta e modificação de bases
de dados
 O modelo de comunicação com a base de dados:
 Abre uma ligação
 Cria um objecto “statement”
 Executa comandos usando esse objecto para enviar os comandos e
obter os resultados
 Usa mecanismos de excepção para lidar com os erros
Database System Concepts
4.1.14
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de 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);
}
}
Database System Concepts
4.1.15
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo de código JDBC (Cont.)
 Actualização
try {
stmt.executeUpdate( "insert into account values
('A-9732', 'Perryridge', 1200)");
} catch (SQLException sqle) {
System.out.println("Could not insert tuple. " + sqle);
}
 Execução de perguntas
ResultSet rset = stmt.executeQuery( "select branch_name, avg(balance)
from account
group by branch_name");
while (rset.next()) {
System.out.println(
rset.getString("branch_name") + " " + rset.getFloat(2));
}
Database System Concepts
4.1.16
©Silberschatz, Korth and Sudarshan (modificado)
Linguagem de Definição de Dados (DDL)
Linguagem para especificar a informação acerca de cada
relação, incluindo:
 O esquema de cada relação.
 O domínio de valores associados com cada atributo.
 Restrições de integridade
 O conjunto de índices a manter para cada relação.
 Informação de segurança e autorização para cada
relação.
 As estruturas de armazenamento físico em disco de
cada relação.
Database System Concepts
4.1.17
©Silberschatz, Korth and Sudarshan (modificado)
Tipos em SQL
 char(n). Cadeia de caracteres de comprimento fixo n.
 varchar(n). Cadeia de caracteres de comprimento variável, com o máximo
n caracteres, especificado pelo utilizador.
 int. inteiro (um subconjunto finito dos inteiros, dependente da máquina).
 smallint. Inteiro pequeno (um subconjunto do tipo int).
 numeric(p,d). Número de vírgula fixa, com precisão de p dígitos e com n
casas decimais.
 real, double precision. Números de vírgula flutuante, com precisão
dependente da máquina.
 float(n). Número de vírgula flutuante, com um mínimo de precisão de n
dígitos.
 Os valores nulos são permitidos em todos os tipos de dados. A declaração
de um atributo como not null proíbe os valores nulos para esse atributo.
 create domain construção em SQL-92 que cria um tipo de dados definido
pelo utilizador (não se encontra implementado em Oracle)
create domain person-name char(20) not null
Database System Concepts
4.1.18
©Silberschatz, Korth and Sudarshan (modificado)
Tipos em Oracle
 Os tipos suportados pelo SGBD Oracle são:
 CHAR(size), VARCHAR2(size), NCHAR(size), NVARCHAR2(size)
 NUMBER(p,s)
 DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE e
TIMESTAMP WITH LOCAL TIME ZONE
 INTERVAL YEAR TO MONTH e INTERVAL DAY TO SECOND.
 RAW(size), LONG RAW, LONG
 CLOB, NCLOB, BLOB, BFILE
 ROWID, UROWID
Database System Concepts
4.1.19
©Silberschatz, Korth and Sudarshan (modificado)
Conversão (implícita) para tipos SQL
CHARACTER(n)
CHAR(n)
CHAR(n)
CHARACTER VARYING(n)
VARCHAR2(n)
CHAR VARYING(n)
NATIONAL CHARACTER(n)
NCHAR(n)
NATIONAL CHAR(n)
NCHAR(n)
NATIONAL CHARACTER VARYING(n)
NVARCHAR2(n)
NATIONAL CHAR VARYING(n)
NCHAR VARYING(n)
INTEGER
NUMBER(38)
INT
SMALLINT
FLOAT(b)
NUMBER
DOUBLE PRECISION
REAL
Database System Concepts
4.1.20
©Silberschatz, Korth and Sudarshan (modificado)
Tipos Data/Tempo em SQL (cont.)
 date. datas, contendo um ano com (4 dígitos), mês e dia
 E.g. date ‘2001-7-27’
 time. Tempo (diário), em horas, minutos e segundos.
 E.g. time ’09:00:30’
time ’09:00:30.75’
 timestamp: data mais hora
 E.g. timestamp ‘2001-7-27 09:00:30.75’
 Interval: período de tempo
 E.g. Interval ‘1’ day
 A subtracção de dois valores de date/time/timestamp devolve um intervalo
 Os valores de intervalos podem ser adicionados a valores de
date/time/timestamp
 Pode-se extrair campos do valor date/time/timestamp
 E.g. extract (year from SYSDATE)
 Pode-se converter cadeias de caracteres para date/time/timestamp
 E.g. cast <string-valued-expression> as date
Database System Concepts
4.1.21
©Silberschatz, Korth and Sudarshan (modificado)
Datas/Tempo em Oracle
 O suporte de datas em SGBD Oracle difere ligeiramente do que foi
descrito anteriormente.
 Os tipos base para representar datas/tempos são:
 DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE e TIMESTAMP WITH
LOCAL TIME ZONE.
 Para representar intervalos temos:
 INTERVAL YEAR TO MONTH e INTERVAL DAY TO SECOND.
 Existem funções especiais para converter de e para datas/intervalos:
 TO_CHAR(date), TO_NCHAR(date).
 TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL,
TO_DSINTERVAL, NUMTOYMINTERVAL, NUMTODSINTERVAL
 A pseudocoluna SYSDATE e função CURRENT_DATE retornam a data
actual.
 Para mais detalhes consultar o manual.
Database System Concepts
4.1.22
©Silberschatz, Korth and Sudarshan (modificado)
Instrução Create Table
 Uma tabela SQL é definida recorrendo ao comando
create table :
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
 r é o nome da relação
 Ai é o nome de um atributo no esquema de relação r
 Di é o tipo de dados dos valores no domínio do atributo Ai
 Exemplo:
create table branch
(branch_name char(15) not null,
branch_city
char(30),
assets
integer)
Database System Concepts
4.1.23
©Silberschatz, Korth and Sudarshan (modificado)
Restrições de integridade em Create Table
 not null
 primary key (A1, ..., An)
 unique (A1, ..., An)
 check (P), em que P é um predicado
Exemplo:
create table branch
(branch-namechar(15),
branch-city char(30)
assets
integer,
primary key (branch-name),
check (assets >= 0))
A declaração primary key num atributo garante
automaticamente not null em versões posteriores ao
SQL-92. Deve ser explicitado em SQL-89
Database System Concepts
4.1.24
©Silberschatz, Korth and Sudarshan (modificado)
Instrução Drop Table
 O comando drop table remove da base de dados toda a
informação sobre a relação (e não apenas os dados).
drop table loan
 Em Oracle, caso hajam chaves externas (veremos daqui a
uns dias o que isso é), deve-se utilizar a declaração
cascade constraints.
drop table loan cascade constraints
 Para eliminar vistas deve-se utilizar drop view
Database System Concepts
4.1.25
©Silberschatz, Korth and Sudarshan (modificado)
Instrução Alter Table
 O comando alter table é utilizado para modificar o esquema, ou
as restrições sobre relações já existente.
 Para adicionar novos atributos:
alter table r add A D
em que A é o nome do atributo a adicionar à relação r e D o
domínio de A. Todos os tuplos existentes ficam com null no novo
atributo.
 O comando alter table também pode ser utilizado para eliminar
atributos de uma relação
alter table r drop A
em que A é o nome de um atributo na relação r (em Oracle
deve-se escrever alter table r drop column A)
Database System Concepts
4.1.26
©Silberschatz, Korth and Sudarshan (modificado)
Instrução Alter Table
 Para adicionar novas restrições:
alter table r add constraint N R
em que N é um nome dado à nova restrição e R define a
restrição. Por exemplo:
alter table account add constraint saldo_pos
check (balance > 0)
 Para remover restrições (definidas previamente com um nome):
alter table r drop constraint N
por exemplo:
alter table account drop constraint saldo_pos
Database System Concepts
4.1.27
©Silberschatz, Korth and Sudarshan (modificado)
Exemplo retirado das aulas práticas
create table alunos(
num_aluno number(6) not null,
nome varchar2(30) not null,
local varchar2(25),
data_nsc date not null,
sexo char(1) not null check ( sexo in ( 'F' , 'M' ) ),
cod_curso number(3) not null);
…
alter table alunos add constraint pk_aluno
primary key (num_aluno);
alter table alunos add constraint un_aluno
unique (num_aluno, cod_curso);
…
Database System Concepts
4.1.28
©Silberschatz, Korth and Sudarshan (modificado)