Conceitos básicos de SQL e ORACLE SQL* PLUS

O Comando SELECT

SELECT [DISTINCT] {*, column [alias],...}FROM 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

SQL> SELECT DISTINCT depto_num 2 FROM emp;

DEPTO_NUM--------- 10 20 30

– DISTINCT

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? Type----------------- -------- ------------DEPTO_NUM NOT NULL NUMBER(2)DEPTO_NOM VARCHAR2(14)DEPTO_LOC 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 CARGO DEPTO_NUM---------- --------- ---------RONALDO CAIXA 30MANUEL CAIXA 20PAULO CAIXA 20LUCIANO 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

BETWEEN

...AND...

IN(lista)

LIKE

IS NULL

Significado

Entre dois valores (inclusive)

Satisfaz uma lista de valores

Satisfaz um padrão de caracteres

É um valor nulo (null)

Operador BETWEEN

ENOME SAL---------- ---------MARIA 1250SERGIO 1500MATHEUS 1250PAULO 1100LUCIANO 1300

SQL> SELECT enome, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500;

limite inferior

limite superior

Operador IN

SQL> SELECT enum, enome, sal, ger 2 FROM emp 3 WHERE ger IN (7902, 7566, 7788);

ENUM ENOME SAL GER--------- ---------- --------- --------- 7902 JOSE 3000 7566 7369 MANUEL 800 7902 7788 FABIO 3000 7566 7876 PAULO 1100 7788

SQL> SELECT enome 2 FROM emp 3 WHERE enome LIKE ‘M%';

Operador LIKE

– Utilize o operador LIKE para realizar pesquisas por padrões (wildcards).

• % substitui zero ou mais caracteres• _ substitui um único caracter

SQL> SELECT enome, ger 2 FROM emp 3 WHERE ger IS NULL;

ENOME GER---------- ---------CARLOS

Operador IS NULL

– Testando valores nulos (null)

Operadores Lógicos

Operador

AND

OR

NOT

Significado

Retorna TRUE se a condição de

ambos os componentes for TRUE

Retorna TRUE se a condição de um

dos componentes for TRUE

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 CARGO---------- ---------CARLOS PRESIDENTEMARIA VENDEDORCELSO VENDEDORSERGIO VENDEDORMATHEUS VENDEDOR

Cláusula ORDER BY

SQL> SELECT enome, cargo, depto_num, dtinicio 2 FROM emp 3 ORDER BY dtinicio DESC;

ENOME CARGO DEPTO_NUM DTINICIO---------- --------- --------- ---------PAULO CAIXA 20 12-JAN-83FABIO ANALISTA 20 09-DEC-82LUCIANO CAIXA 10 23-JAN-82RONALDO CAIXA 30 03-DEC-81JOSE ANALISTA 20 03-DEC-81CARLOS PRESIDENTE 10 17-NOV-81MARIA VENDEDOR 30 28-SEP-81...14 rows selected.

Manipulando dados

O Comando INSERT

– Adicione linhas a uma tabela utilizando o comando INSERT.

– O comando INSERT insere apenas uma linha por vez.

Não esqueça o COMMIT

INSERT INTO table [(column [, column...])]VALUES (value [, value...]);

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

–

– Strings e datas devem ser informando entre aspas simples.

Inserindo Novas Linhas

SQL> INSERT INTO depto (depto_num, depto_nome, depto_loc) 2 VALUES (50, 'DESENVOLVIMENO', ‘RECIFE');1 row created.1 row created.

SQL> INSERT INTO depto (depto_num, depto_nome ) 2 VALUES (60, ‘REC HUMANOS');1 row created.1 row created.

SQL> INSERT INTO depto 2 VALUES (70, ‘JURIDICO', NULL);1 row created.1 row created.

Inserindo Linhas com Nulls

– Implicitamente: omita o nome da coluna da lista de colunas.

– Explicitamente: especifique o valor NULL.

Inserindo Data e Hora do Sistema

– A função SYSDATE informa a data e a hora corrente.

SQL> INSERT INTO emp (enum, enome, cargo, 2 ger, dtinicio, sal, comis, 3 depto_num) 4 VALUES (7196, ‘ANTONIO', ‘VENDEDOR', 5 7782, SYSDATE, 2000, NULL, 6 10);1 row created.1 row created.

Inserindo Datas

– Adicionando um novo empregado.

SQL> INSERT INTO emp 2 VALUES (2296,'ROMANO',‘VENDEDOR',7782, 3 TO_DATE(‘03-02-1997','DD-MM-YYYY'), 4 1300, NULL, 10);1 row created.1 row created.

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

– Modifique mais de uma linha por vez especificando uma condição na cláusula WHERE.

Não esqueça o COMMIT

UPDATE tableSET column = value [, column = value][WHERE condition];

Atualizando linhas em uma tabela– Linhas específicas podem ser modificadas

utilizando a cláusula WHERE.

– Todas as linhas da tabela são modificadas se a cláusula WHERE for omitida.

SQL> UPDATE emp 2 SET depto_num = 20 3 WHERE enum = 7782;1 row updated.1 row updated.

SQL> UPDATE emp 2 SET depto_num = 20;14 rows updated.14 rows updated.

– Linhas específicas podem ser eliminadas utilizando a cláusula WHERE.

– Todas as linhas da tabela são eliminadas se a cláusula WHERE for omitida.

Eliminando Linhas de uma Tabela

SQL> DELETE FROM depto 2 WHERE depto_nome = 'DESENVOLVIMENTO'; 1 row deleted.1 row deleted.

SQL> DELETE FROM depto;4 rows deleted.4 rows deleted.

Exibindo dados de múltiplas tabelas

Joins– Utilize uma junção para consultar dados de

mais de uma tabela.

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

SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1 = table2.column2;

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.

EMPEMP

““o maior salário o maior salário da tabela EMP”da tabela 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

MAX(SAL)

---------

5000

AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)-------- --------- --------- --------- 1400 1600 1250 5600

SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE cargo LIKE ‘VEND%';

Funções AVG e SUM

– Utilize o AVG e SUM apenas para dados numéricos

SQL> SELECT MIN(dtinicio), MAX(dtinicio) 2 FROM emp;

MIN(DTINI MAX(DTINI--------- ---------17-DEZ-80 12-JAN-83

Funções MIN e MAX

– Utilize MIN e MAX para qualquer tipo de dado

COUNT(*)--------- 6

SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE depto_num = 30;

Função COUNT

– COUNT(*) retorna o número de linhas na tabela

COUNT(COMIS)------------ 4

SQL> SELECT COUNT(comis) 2 FROM emp 3 WHERE depto_num = 30;

Função COUNT

– COUNT(coluna) retorna o número de linhas não nulas da tabela

Criando Grupos de Dados EMPEMP

““média salarial média salarial por por

departamento”departamento”

2916.66672916.6667

21752175

1566.66671566.6667

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

DEPTO_NUM

AVG(SAL)

---------

---------

10

2916.6667

20

2175

30

1566.6667

Criando Grupos de Dados: A Cláusula GROUP BYSELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];

–Divida as linhas de uma tabela em pequenos grupos usando a cláusula GROUP BY.

SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY depto_num;

–Colunas utilizadas em funções de grupo não precisam estar listadas no GROUP BY.

AVG(SAL)--------- 2916.6667 21751566.6667

A Cláusula GROUP BY

SQL> SELECT depto_num, cargo, sum(sal) 2 FROM emp 3 GROUP BY depto_num, cargo;

Utilizando GROUP BY em Múltiplas Colunas

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

SQL> CREATE TABLE depto 2 (depto_num NUMBER(2), 3 depto_nom VARCHAR2(14), 4 depto_loc VARCHAR2(13));Table created.Table created.

– Criando a tabela depto.

– Listando a estrutura da tabela criada.

SQL> DESCRIBE depto

Name Null? Type --------------------------- -------- --------- DEPTO_NUM NOT NULL NUMBER(2) DEPTO_NOME VARCHAR2(14) DEPTO_LOC VARCHAR2(13)

Consultando o Dicionário de Dados– Tabelas do usuário

– Objetos

– Tabelas, visões, sinônimos e seqüências

SQL> SELECT * 2 FROM user_tables;

SQL> SELECT DISTINCT object_type 2 FROM user_objects;

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.

Essa operação não pode ser desfeita.

SQL> DROP TABLE depto30;Table dropped.Table dropped.

Renomeando Objetos da Base

SQL> RENAME depto TO departamento;Table renamed.Table renamed.

– Tabelas, visões, sinônimos e seqüências

Truncando uma Tabela

– Remove todas as linhas da tabela liberando o espaço ocupado

Essa operação não pode ser desfeita

SQL> TRUNCATE TABLE departamento;Table truncated.Table truncated.

Subqueries

Subqueries

–A subquery (inner query) geralmente é executada antes da consulta principal.

–O resultado da subquery é, então, avaliado pelo da query principal (outer query).

SELECT select_listFROM tableWHERE expr operator

(SELECT select_list FROM table);

2975

SQL> SELECT enome 2 FROM emp 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE enum=7566);

Utilizando uma Subquery

ENOME----------CARLOSJOSEFABIO

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 enum, enome, sal, depto_num 2 FROM emp 3 WHERE enum = &num_empregado;

Enter value for num_empregado: 73697369

ENUM ENOME SAL DEPTO_NUM--------- ---------- --------- --------- 7369 MANUEL 800 20

–Utilize aspas simples para datas e strings.

Enter value for cargo: ANALISTAANALISTA

ENOME DEPTO_NUM SAL*12---------- --------- ---------FABIO 20 36000JOSE 20 36000

Substituindo Variáveis com o &

SQL> SELECT enome, depto_num, sal*12 2 FROM emp 3 WHERE cargo = '&cargo';

SQL> SELECT enum, enome, cargo, &nome_coluna 2 FROM emp 3 WHERE &condicao 4 ORDER BY &ordenar_por;

Especificando Nomes de Colunas, Expressões e Textos em Tempo de Execução

Enter value for nome_coluna: salsalEnter value for condicao: sal>=3000sal>=3000Enter value for ordenar_por: enomeenome

ENUM ENOME CARGO SAL--------- ---------- --------- --------- 7902 JOSE ANALISTA 3000 7839 CARLOS PRESIDENTE 5000 7788 FABIO ANALISTA 3000

Utilizando o Comando TTITLE e o BTITLE

–Exibe cabeçalhos e rodapés

–Define o cabeçalho

–Define o rodapé

TTI[TLE] [texto|OFF|ON]

SQL> TTITLE ‘Relatório de|Empregados'

SQL> BTITLE 'Confidencial'

Exemplo de RelatórioSex Out 24 pág. 1 Relatório de

Empregados

Cargo Empregado Salario----------------------- ----------------------- -----------------CAIXA PAULO R$1,100.00CAIXA RONALDO R$950.00CAIXA LUCIANO R$1,300.00CAIXA MANUEL R$800.00GERENTE MARCOS R$2,850.00GERENTE ANA R$2,450.00GERENTE JOAO R$2,975.00VENDEDOR CELSO R$1,600.00VENDEDOR MARIA R$1,250.00VENDEDOR SERGIO R$1,500.00VENDEDOR MATHEUS R$1,250.00

Confidencial

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 procedureBEGIN 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;--excecoesexception 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 NOMECONDICAO DE ATIVACAOBEGIN <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_FUNCIONARIOBEFORE DELETE OR INSERT OR UPDATE ON acf_EMPREGADOBEGIN INSERT INTO acf_LOG_funcionario VALUES(SYSDATE,'Tabela modificada');END;/

Sintaxe (exemplo)CREATE OR REPLACE TRIGGER t_verifica_data_contratosBEFORE insert on llma_diario for each row DECLARE datainicial llma_contratos.dataini%TYPE; BEGIN SELECT to_char(dataini,'dd/mm/yyyy') into datainicial FROM llma_contratos WHERE cod_con = :new.cod_con;  --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_DataColetaInvalidaBEFORE 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_AlteraItemColetaAFTER insert or update or delete on xitemcoleta FOR EACH ROWBEGIN 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