Conceitos básicos de SQL e
ORACLE SQL* PLUS
O Comando SELECT
SELECT
FROM
[DISTINCT] {*, column [alias],...}
table;
– SELECT identifica as colunas
– FROM identifica as tabelas
Selecionando linhas
SQL> SELECT *
2 FROM
depto;
SQL> SELECT depto_num, depto_loc
2 FROM
depto;
SQL> SELECT enome, sal, sal+300
2 FROM
emp;
SQL> SELECT enome, sal, 12*sal+100
2 FROM
emp;
Definindo Alias para Colunas
SQL> SELECT enome AS nome, sal AS salario
2 FROM
emp;
NOME
SALARIO
------------- --------...
SQL> SELECT enome "Nome",
2
sal*12 “Salario Anual"
3 FROM
emp;
Nome
Salario Anual
------------- ------------...
Eliminando Linhas Duplicadas
– DISTINCT
SQL> SELECT DISTINCT depto_num
2 FROM
emp;
DEPTO_NUM
--------10
20
30
SQL*Plus
SQL Plus


Aplicação cliente para conexão com Oracle.
Localização:
– Start  Programs  Banco de dados  Oracle OraHome81
 Application Development  SQL Plus
– Usuário e senha são idênticos
Interface gráfica do SQL Plus
Configurações

Opcoes ambiente
– Linesize – modifica a quantidade de caracteres por linha.
Escolher personalizado. Escolhar ativar. Especificar 1000
caracteres. Aumentar largura do buffer para 1000.
Listando a Estrutura de Tabelas
SQL> DESCRIBE depto
Name
Null?
----------------- -------DEPTO_NUM
NOT NULL
DEPTO_NOM
DEPTO_LOC
Type
-----------NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
Executando um script no SQL
Plus
@caminho_completo
Restringindo consultas e
ordenando resultados
Utilizando a Cláusula WHERE
SQL> SELECT enome, cargo, depto_num
2 FROM
emp
3 WHERE cargo='CAIXA';
ENOME
---------RONALDO
MANUEL
PAULO
LUCIANO
CARGO
DEPTO_NUM
--------- --------CAIXA
30
CAIXA
20
CAIXA
20
CAIXA
10
Operadores de Comparação
Operador
Significado
=
Igual a
>
Maior que
>=
Maior ou igual a
<
Menor que
<=
Menor ou igual a
<>
Diferente de
Outros Operadores
Operador
Significado
BETWEEN
...AND...
Entre dois valores (inclusive)
IN(lista)
Satisfaz uma lista de valores
LIKE
Satisfaz um padrão de caracteres
IS NULL
É um valor nulo (null)
Operador BETWEEN
SQL> SELECT
2 FROM
3 WHERE
enome, sal
emp
sal BETWEEN 1000 AND 1500;
ENOME
SAL
---------- --------MARIA
1250
SERGIO
1500
MATHEUS
1250
PAULO
1100
LUCIANO
1300
limite
inferior
limite
superior
Operador IN
SQL> SELECT
2 FROM
3 WHERE
ENUM
--------7902
7369
7788
7876
enum, enome, sal, ger
emp
ger IN (7902, 7566, 7788);
ENOME
SAL
GER
---------- --------- --------JOSE
3000
7566
MANUEL
800
7902
FABIO
3000
7566
PAULO
1100
7788
Operador LIKE
– Utilize o operador LIKE para realizar
pesquisas por padrões (wildcards).
• % substitui zero ou mais caracteres
• _ substitui um único caracter
SQL> SELECT
2 FROM
3 WHERE
enome
emp
enome LIKE ‘M%';
Operador IS NULL
– Testando valores nulos (null)
SQL> SELECT
2 FROM
3 WHERE
enome, ger
emp
ger IS NULL;
ENOME
GER
---------- --------CARLOS
Operadores Lógicos
Operador
Significado
AND
Retorna TRUE se a condição de
ambos os componentes for TRUE
OR
Retorna TRUE se a condição de um
dos componentes for TRUE
NOT
Retorna TRUE se a condição for
FALSE (vise-versa)
Operador NOT
SQL> SELECT enome, cargo
2 FROM
emp
3 WHERE cargo NOT IN('CAIXA','GERENTE','ANALISTA');
ENOME
---------CARLOS
MARIA
CELSO
SERGIO
MATHEUS
CARGO
--------PRESIDENTE
VENDEDOR
VENDEDOR
VENDEDOR
VENDEDOR
Cláusula ORDER BY
SQL> SELECT
enome, cargo, depto_num, dtinicio
2 FROM
emp
3 ORDER BY dtinicio DESC;
ENOME
CARGO
DEPTO_NUM
---------- --------- --------PAULO
CAIXA
20
FABIO
ANALISTA
20
LUCIANO
CAIXA
10
RONALDO
CAIXA
30
JOSE
ANALISTA
20
CARLOS
PRESIDENTE
10
MARIA
VENDEDOR
30
...
14 rows selected.
DTINICIO
--------12-JAN-83
09-DEC-82
23-JAN-82
03-DEC-81
03-DEC-81
17-NOV-81
28-SEP-81
Manipulando dados
O Comando INSERT
– Adicione linhas a uma tabela utilizando o
comando INSERT.
INSERT INTO
VALUES
table [(column [, column...])]
(value [, value...]);
– O comando INSERT insere apenas uma
linha por vez.
Não esqueça o COMMIT
Inserindo Novas Linhas
– Insira uma nova linha informando os
valores para cada coluna da tabela.
– Liste os valores na mesma ordem das
colunas na tabela.
– Opcionalmente, liste as colunas na
cláusula do INSERT.
SQL> INSERT INTO depto (depto_num, depto_nome, depto_loc)
2 VALUES
(50, 'DESENVOLVIMENO', ‘RECIFE');
1 –row created.
– Strings e datas devem ser informando
entre aspas simples.
Inserindo Linhas com Nulls
– Implicitamente: omita o nome da coluna
da lista de colunas.
SQL> INSERT INTO
2 VALUES
1 row created.
depto (depto_num, depto_nome )
(60, ‘REC HUMANOS');
– Explicitamente: especifique o valor
NULL.
SQL> INSERT INTO
2 VALUES
1 row created.
depto
(70, ‘JURIDICO', NULL);
Inserindo Data e Hora do Sistema
– A função SYSDATE informa a data e a
hora corrente.
SQL> INSERT INTO
2
3
4 VALUES
5
6
1 row created.
emp (enum, enome, cargo,
ger, dtinicio, sal, comis,
depto_num)
(7196, ‘ANTONIO', ‘VENDEDOR',
7782, SYSDATE, 2000, NULL,
10);
Inserindo Datas
– Adicionando um novo empregado.
SQL> INSERT INTO
2 VALUES
3
4
1 row created.
emp
(2296,'ROMANO',‘VENDEDOR',7782,
TO_DATE(‘03-02-1997','DD-MM-YYYY'),
1300, NULL, 10);
– Verificando a data de admissão.
ENUM ENOME
CARGO
GER DTINICIO SAL COMIS DEPTO_NUM
---- ------- -------- ---- --------- ---- ----- --------2296 ROMANO VENDEDOR 7782 03-FEB-97 1300
10
O Comando UPDATE
– Modificando linhas existentes com o
comando UPDATE.
UPDATE
SET
[WHERE
table
column = value [, column = value]
condition];
– Modifique mais de uma linha por vez
especificando uma condição na cláusula
WHERE.
Não esqueça o COMMIT
Atualizando linhas em uma tabela
– Linhas específicas podem ser modificadas
utilizando a cláusula WHERE.
SQL> UPDATE emp
2 SET
depto_num = 20
3 WHERE
enum = 7782;
1 row updated.
– Todas as linhas da tabela são modificadas
se a cláusula WHERE for omitida.
SQL> UPDATE emp
2 SET
depto_num = 20;
14 rows updated.
Eliminando Linhas de uma Tabela
– Linhas específicas podem ser eliminadas
utilizando a cláusula WHERE.
SQL> DELETE FROM
2 WHERE
1 row deleted.
depto
depto_nome = 'DESENVOLVIMENTO';
– Todas as linhas da tabela são eliminadas
se a cláusula WHERE for omitida.
SQL> DELETE FROM
4 rows deleted.
depto;
Exibindo dados de múltiplas
tabelas
Joins
– Utilize uma junção para consultar dados de
mais de uma tabela.
SELECT
FROM
WHERE
table1.column, table2.column
table1, table2
table1.column1 = table2.column2;
– Especifique a condição de junção na cláusula
WHERE.
– Informe o nome da tabela junto com o nome
da coluna, se tabelas diferentes possuírem
colunas com os mesmos nomes.
Criando Alias para Tabelas
SQL> SELECT emp.enum, emp.enome, emp.depto_num,
2
depto.depto_num, depto.depto_loc
3 FROM
emp, depto
4 WHERE emp.depto_num = depto.depto_num;
SQL> SELECT e.enum, e.enome, e.depto_num,
2
d.depto_num, d.depto_loc
3 FROM
emp e, depto d
4 WHERE e.depto_num = d.depto_num;
Funções de Grupo
(Agregação)
O que são Funções de Grupo?
– Funções de grupo operam em conjuntos de
linhas, produzindo um resultado por grupo.
EMP
DEPTO_NUM
SAL
--------- --------10
2450
10
5000
10
1300
20
800
20
1100
20
3000
20
3000
20
2975
30
1600
30
2850
30
1250
30
950
30
1500
30
1250
“o maior salário
da tabela EMP”
MAX(SAL)
--------5000
Funções AVG e SUM
– Utilize o AVG e SUM apenas para dados
numéricos
SQL> SELECT
2
3 FROM
4 WHERE
AVG(sal), MAX(sal),
MIN(sal), SUM(sal)
emp
cargo LIKE ‘VEND%';
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
-------- --------- --------- --------1400
1600
1250
5600
Funções MIN e MAX
– Utilize MIN e MAX para qualquer tipo de
dado
SQL> SELECT
2 FROM
MIN(dtinicio), MAX(dtinicio)
emp;
MIN(DTINI MAX(DTINI
--------- --------17-DEZ-80 12-JAN-83
Função COUNT
– COUNT(*) retorna o número de linhas na
tabela
SQL> SELECT
2 FROM
3 WHERE
COUNT(*)
--------6
COUNT(*)
emp
depto_num = 30;
Função COUNT
– COUNT(coluna) retorna o número de
linhas não nulas da tabela
SQL> SELECT
2 FROM
3 WHERE
COUNT(COMIS)
-----------4
COUNT(comis)
emp
depto_num = 30;
Criando Grupos de Dados
EMP
DEPTO_NUM
SAL
--------- --------10
2450
10
5000 2916.6667
10
1300
DEPTO_NUM AVG(SAL)
20
800
20
1100 “média salarial --------- --------20
3000 2175 por
10 2916.6667
20
3000 departamento”
20
2175
20
2975
30
1600
30 1566.6667
30
2850
30
1250 1566.6667
30
950
30
1500
30
1250
Criando Grupos de Dados:
A Cláusula GROUP BY
SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
column, group_function(column)
table
condition]
group_by_expression]
column];
–Divida as linhas de uma tabela em
pequenos grupos usando a cláusula
GROUP BY.
A Cláusula GROUP BY
–Colunas utilizadas em funções de grupo não
precisam estar listadas no GROUP BY.
SQL> SELECT
AVG(sal)
2 FROM
emp
3 GROUP BY depto_num;
AVG(SAL)
--------2916.6667
2175
1566.6667
Utilizando GROUP BY em
Múltiplas Colunas
SQL> SELECT
depto_num, cargo, sum(sal)
2 FROM
emp
3 GROUP BY depto_num, cargo;
DEPTO_NUM CARGO
SUM(SAL)
--------- --------- --------10 CAIXA
1300
10 GERENTE
2450
10 PRESIDENTE
5000
20 ANALISTA
6000
20 CAIXA
1900
...
9 rows selected.
Criando e Manipulando
Tabelas
Tabela
– Unidade básica de armazenamento da
base de dados, formada por colunas e
linhas (tuplas)
Criando Tabelas
– Criando a tabela depto.
SQL> CREATE TABLE depto
2
(depto_num NUMBER(2),
3
depto_nom VARCHAR2(14),
4
depto_loc VARCHAR2(13));
Table created.
– Listando a estrutura da tabela criada.
SQL> DESCRIBE depto
Name
Null?
--------------------------- -------DEPTO_NUM
NOT NULL
DEPTO_NOME
DEPTO_LOC
Type
--------NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
Consultando o Dicionário de Dados
– Tabelas do usuário
SQL> SELECT
2 FROM
*
user_tables;
– Objetos
SQL> SELECT
2 FROM
DISTINCT object_type
user_objects;
– Tabelas, visões, sinônimos e seqüências
SQL> SELECT
2 FROM
*
user_catalog;
Descartando uma Tabela
– Todos os dados e a estrutura da tabela
são destruídos.
– Qualquer transação pendente é
encerrada.
– Todos os índices são descartados.
SQL> DROP TABLE depto30;
Table dropped.
Essa operação não pode ser desfeita.
Renomeando Objetos da Base
– Tabelas, visões, sinônimos e seqüências
SQL> RENAME depto TO departamento;
Table renamed.
Truncando uma Tabela
– Remove todas as linhas da tabela
liberando o espaço ocupado
SQL> TRUNCATE TABLE departamento;
Table truncated.
Essa operação não pode ser desfeita
Subqueries
Subqueries
SELECT
FROM
WHERE
select_list
table
expr operator
(SELECT
FROM
select_list
table);
–A subquery (inner query) geralmente é
executada antes da consulta principal.
–O resultado da subquery é, então, avaliado
pelo da query principal (outer query).
Utilizando uma Subquery
SQL> SELECT enome
2 FROM
emp
2975
3 WHERE sal >
4
(SELECT sal
5
FROM
emp
6
WHERE enum=7566);
ENOME
---------CARLOS
JOSE
FABIO
Regras para Subqueries
–Utilize subqueries entre parênteses.
–As subqueries vêem sempre à direita do
operador de comparação.
–Não utiliza a cláusula ORDER BY em
subqueries.
–Uma subquery retorna uam tabela sobre a
qual pode-se realizar qualquer uma das
operações vista anteriormente.
Melhorando a apresentação
dos resultados no SQL*PLUS
Substituindo Variáveis com o &
–Utilize uma variável prefixada com um &
para ler um valor de entrada.
SQL> SELECT
2 FROM
3 WHERE
enum, enome, sal, depto_num
emp
enum = &num_empregado;
Enter value for num_empregado: 7369
ENUM ENOME
SAL DEPTO_NUM
--------- ---------- --------- --------7369 MANUEL
800
20
Substituindo Variáveis com o &
–Utilize aspas simples para datas e strings.
SQL> SELECT enome, depto_num, sal*12
2 FROM
emp
3 WHERE cargo = '&cargo';
Enter value for cargo: ANALISTA
ENOME
DEPTO_NUM
SAL*12
---------- --------- --------FABIO
20
36000
JOSE
20
36000
Especificando Nomes de Colunas,
Expressões e Textos em Tempo de
Execução
SQL>
2
3
4
SELECT
FROM
WHERE
ORDER BY
enum, enome, cargo, &nome_coluna
emp
&condicao
&ordenar_por;
Enter value for nome_coluna: sal
Enter value for condicao: sal>=3000
Enter value for ordenar_por: enome
ENUM
--------7902
7839
7788
ENOME
---------JOSE
CARLOS
FABIO
CARGO
SAL
--------- --------ANALISTA
3000
PRESIDENTE
5000
ANALISTA
3000
Utilizando o Comando
TTITLE e o BTITLE
–Exibe cabeçalhos e rodapés
TTI[TLE] [texto|OFF|ON]
–Define o cabeçalho
SQL> TTITLE ‘Relatório de|Empregados'
–Define o rodapé
SQL> BTITLE 'Confidencial'
Exemplo de Relatório
Sex Out 24
pág.
Relatório de
Empregados
Cargo
Empregado
----------------------- ----------------------CAIXA
PAULO
CAIXA
RONALDO
CAIXA
LUCIANO
CAIXA
MANUEL
GERENTE
MARCOS
GERENTE
ANA
GERENTE
JOAO
VENDEDOR
CELSO
VENDEDOR
MARIA
VENDEDOR
SERGIO
VENDEDOR
MATHEUS
Confidencial
Salario
----------------R$1,100.00
R$950.00
R$1,300.00
R$800.00
R$2,850.00
R$2,450.00
R$2,975.00
R$1,600.00
R$1,250.00
R$1,500.00
R$1,250.00
1
Stored Procedures
Stored Procedures


Procedimantos que permanecem
armazenados no banco, de forma
compilada.
Servem para executar alguma
computação quando invocados
Sintaxe
CREATE OR REPLACE PROCEDURE NOME
( NOME TIPO[,NOME TIPO] )
IS BEGIN
[DECLARE]
<CORPO>
COMMIT;
EXCEPTION
WHEN OTHERS THEN
<CORPO>
END NOME;
/
Sintaxe (exemplo)
CREATE OR REPLACE PROCEDURE AJUSTE
(VALOR REAL, CAD INTEGER)
IS BEGIN
UPDATE acf_EMPREGADO SET salario=salario +
salario*VALOR WHERE cadastro=CAD;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO acf_ERROS values(SYSDATE,'Erro na
execucao de ajuste');
END AJUSTE;
/
Execução
EXECUTE AJUSTE(0.1, 21);
O procedimento é executado. Caso algum erro ocorra,
então a tabela de erros será atualizada.
Exemplo
CREATE OR REPLACE PROCEDURE Lista_Func
(codDepto llma_funcionarios.depto_func%TYPE)
IS BEGIN
DECLARE
--cursor para guardar os funcionarios
CURSOR c_diario_func is
SELECT llma_funcionarios.cod_func,
llma_funcionarios.nome_func,
FROM
llma_funcionarios
WHERE depto_func = codDepto;
--declaracao das variaveis do cursor
v_cod_func
llma_funcionarios.cod_func%TYPE;
v_nome_func
llma_funcionarios.nome_func%TYPE;
Exemplo (cont)
--inicio da procedure
BEGIN
open c_diario_func;
dbms_output.put_line('** Lista do Diario dos Funcionarios **');
loop fetch c_diario_func into v_cod_func, v_nome_func;
dbms_output.put_line('Codigo do Funcionario : ' || v_cod_func);
dbms_output.put_line('Nome Funcionario : ' || v_nome_func);
dbms_output.put_line('');
dbms_output.put_line('---------------------------------');
dbms_output.put_line('');
exit when c_diario_func%NOTFOUND;
end loop;
close c_diario_func;
END;
--excecoes
exception
when no_data_found then
dbms_output.put_line('Nenhuma registro foi encontrado');
when others then
dbms_output.put_line('Erro desconhecido: ' || to_char(sqlcode));
END Lista_Func;
/
Procedures





Prodecures não retornam valores
A partir de uma procedure pode-se chamar
outras procedures
Procedures são salvas em um arquivo .sql e
compiladas no Oracle com o comando
@caminho_completo do SQL Plus
Caso ocorra algum erro de compilação a
procedure não funciona corretamente
Erros de compilação podem ser vistos com o
comando show_errors do SQL Plus.
Triggers
Triggers


Procedimantos especiais guardados no
banco de forma compilada
Acionados automaticamente pelo banco
quando sua condição de ativação for
veradeira
Sintaxe
CREATE OR REPLACE TRIGGER NOME
CONDICAO DE ATIVACAO
BEGIN
<CORPO>
END;
/
A CONDICAO DE ATIVACAO pode ser montada a partir de
expressões lógicas:
BEFORE DELETE ON NOME_TABELA
ou
AFTER DELETE OR INSERT OR UPDATE ON NOME_TABELA
Sintaxe (exemplo)
CREATE OR REPLACE TRIGGER LOG_FUNCIONARIO
BEFORE DELETE OR INSERT OR UPDATE ON acf_EMPREGADO
BEGIN
INSERT INTO acf_LOG_funcionario
VALUES(SYSDATE,'Tabela modificada');
END;
/
Sintaxe (exemplo)
CREATE OR REPLACE TRIGGER t_verifica_data_contratos
BEFORE insert on llma_diario for each row
DECLARE
datainicial llma_contratos.dataini%TYPE;
BEGIN
SELECT to_char(dataini,'dd/mm/yyyy')
FROM
llma_contratos
WHERE cod_con = :new.cod_con;
into
datainicial
--faz a condicao se a data ta no periodo
IF ((:new.datahoraini < datainicial)
or (:new.datahoraini > datafinal)) then
raise_application_error(-20500, 'Data Inicio tem que esta no
periodo de: '|| to_char(datainicial,'dd/mm/yyyy') || ' a ' ||
to_char(datafinal,'dd/mm/yyyy'));
END IF;
END;
/
Sintaxe (exemplo)
CREATE or REPLACE TRIGGER TG_DataColetaInvalida
BEFORE INSERT ON xcoleta FOR EACH ROW
BEGIN
IF :NEW.data < sysdate THEN
RAISE_APPLICATION_ERROR ('-20000', 'Data já passou');
END IF;
END;
/
Sintaxe
(exemplo)
CREATE or REPLACE TRIGGER TG_AlteraItemColeta
AFTER insert or update or delete on xitemcoleta FOR EACH ROW
BEGIN
IF DELETING THEN
UPDATE XCOLETA SET PESO = (PESO - :OLD.PESO),
QUANTIDADE = (QUANTIDADE - :OLD.QUANTIDADE),
VOLUME = (VOLUME - :OLD.VOLUME)
WHERE COLETA = :OLD.COLETA;
ELSIF INSERTING THEN
UPDATE XCOLETA
SET PESO = (PESO + :NEW.PESO),
QUANTIDADE = (QUANTIDADE + :NEW.QUANTIDADE),
VOLUME = (VOLUME + :NEW.VOLUME)
WHERE COLETA = :NEW.COLETA;
ELSIF UPDATING THEN
UPDATE XCOLETA
SET PESO = (PESO + :NEW.PESO - :OLD.PESO),
QUANTIDADE=(QUANTIDADE+ :NEW.QUANTIDADE- :OLD.QUANTIDADE),
VOLUME = (VOLUME + :NEW.VOLUME - :OLD.VOLUME)
WHERE COLETA = :OLD.COLETA;
END IF;
END;
/
Triggers




Triggers são salvos em um arquivo .sql e
compiladas no Oracle com o comando
@caminho_completo do SQL Plus
Caso ocorra algum erro de compilação o
trigger não funciona corretamente
Erros de compilação podem ser vistos com o
comando show_errors do SQL Plus.
Gatilhos podem ser utilizados para
implementar regras de negócio