sql plus dicas

5/17/2018 SQL Plus Dicas - slidepdf.com

http://slidepdf.com/reader/full/sql-plus-dicas 1/77

Conceitos básicos de SQL eORACLE 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_loc2 FROM depto;

SQL> SELECT enome, sal, sal+3002 FROM emp;

SQL> SELECT enome, sal, 12*sal+1002 FROM emp;



Definindo Alias para Colunas

SQL> SELECT enome AS nome, sal AS salario2 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 ---------

1020

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 1000caracteres. 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 emp3 WHERE cargo='CAIXA';

ENOME CARGO DEPTO_NUM ---------- --------- ---------RONALDO CAIXA 30

MANUEL CAIXA 20PAULO CAIXA 20

LUCIANO 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 1500

MATHEUS 1250PAULO 1100

LUCIANO 1300

SQL> SELECT enome, sal2 FROM emp3 WHERE sal BETWEEN 1000 AND 1500;

limiteinferior

limitesuperior



Operador IN

SQL> SELECT enum, enome, sal, ger2 FROM emp3 WHERE ger IN (7902, 7566, 7788);

ENUM ENOME SAL GER --------- ---------- --------- ---------

7902 JOSE 3000 75667369 MANUEL 800 79027788 FABIO 3000 7566

7876 PAULO 1100 7788



SQL> SELECT enome

2 FROM emp3 WHERE enome LIKE ‘M%';

Operador LIKE

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

• % substitui zero ou mais caracteres

• _ substitui um único caracter



SQL> SELECT enome, ger2 FROM emp3 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 deambos os componentes for TRUE

Retorna TRUE se a condição de umdos componentes for TRUE

Retorna TRUE se a condição forFALSE (vise-versa)



Operador NOT

SQL> SELECT enome, cargo2 FROM emp3 WHERE cargo NOT IN('CAIXA','GERENTE','ANALISTA');

ENOME CARGO---------- ---------CARLOS PRESIDENTE

MARIA VENDEDOR CELSO VENDEDOR

SERGIO VENDEDOR MATHEUS VENDEDOR



Cláusula ORDER BY

SQL> SELECT enome, cargo, depto_num, dtinicio2 FROM emp3 ORDER BY dtinicio DESC;

ENOME CARGO DEPTO_NUM DTINICIO---------- --------- --------- ---------PAULO CAIXA 20 12-JAN-83FABIO ANALISTA 20 09-DEC-82LUCIANO CAIXA 10 23-JAN-82

RONALDO CAIXA 30 03-DEC-81JOSE ANALISTA 20 03-DEC-81CARLOS PRESIDENTE 10 17-NOV-81

MARIA VENDEDOR 30 28-SEP-81...14 rows selected.



Manipulando dados



O Comando INSERT

– Adicione linhas a uma tabela utilizando ocomando 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 osvalores 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.



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

1 row created.

SQL> INSERT INTO depto2 VALUES (70, ‘JURIDICO', NULL);

1 row created.

Inserindo Linhas com Nulls

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

– Explicitamente: especifique o valorNULL.



Inserindo Data e Hora do Sistema

– A função SYSDATE informa a data e ahora 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.



Inserindo Datas

– Adicionando um novo empregado.

SQL> INSERT INTO emp2 VALUES (2296,'ROMANO',‘VENDEDOR',7782, 3 TO_DATE(‘03-02-1997','DD-MM-YYYY'),

4 1300, NULL, 10);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 ocomando UPDATE.

– Modifique mais de uma linha por vez

especificando uma condição na cláusulaWHERE.

Não esqueça o COMMIT

UPDATE table

SET column = value [, column = value][WHERE condition];



Atualizando linhas em uma tabela

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

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

SQL> UPDATE emp2 SET depto_num = 20

3 WHERE enum = 7782;1 row updated.

SQL> UPDATE emp2 SET depto_num = 20;

14 rows updated.



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

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

Eliminando Linhas de uma Tabela

SQL> DELETE FROM depto2 WHERE depto_nome = 'DESENVOLVIMENTO';

1 row deleted.

SQL> DELETE FROM depto;4 rows deleted.



Exibindo dados de múltiplastabelas



Joins

– Utilize uma junção para consultar dados demais 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íremcolunas com os mesmos nomes.

SELECT table1.column, table2.column FROM table1, table2

WHERE 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.EMP

“o maior salário

da tabela EMP”

DEPTO_NUM SAL--------- ---------

10 245010 500010 130020 80020 110020 300020 300020 2975

30 160030 285030 125030 95030 150030 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 emp4 WHERE cargo LIKE ‘VEND%';

Funções AVG e SUM

– Utilize o AVG e SUM apenas para dadosnumé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 dedado



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

6

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

Função COUNT

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



COUNT(COMIS)

------------4

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

Função COUNT

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



Criando Grupos de Dados

EMP

“média salarial

pordepartamento”

2916.6667

2175

1566.6667

DEPTO_NUM SAL--------- ---------

10 245010 500010 1300

20 80020 110020 300020 300020 297530 1600

30 285030 125030 95030 150030 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áusulaGROUP BY.



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

–Colunas utilizadas em funções de grupo nãoprecisam 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 emp3 GROUP BY depto_num, cargo;

Utilizando GROUP BY em

Múltiplas Colunas

DEPTO_NUM CARGO SUM(SAL)--------- --------- ---------

10 CAIXA 130010 GERENTE 2450

10 PRESIDENTE 500020 ANALISTA 600020 CAIXA 1900

...9 rows selected.



Criando e Manipulando

Tabelas



Tabela

– Unidade básica de armazenamento dabase de dados, formada por colunas elinhas (tuplas)



Criando Tabelas

SQL> CREATE TABLE depto2 (depto_num NUMBER(2),3 depto_nom VARCHAR2(14),4 depto_loc VARCHAR2(13));

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_type2 FROM user_objects;

SQL> SELECT *2 FROM user_catalog;



Descartando uma Tabela

– Todos os dados e a estrutura da tabelasã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.



Renomeando Objetos da Base

SQL> RENAME depto TO departamento;Table renamed.

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



Truncando uma Tabela

– Remove todas as linhas da tabelaliberando o espaço ocupado

Essa operação não pode ser desfeita

SQL> TRUNCATE TABLE departamento;Table truncated.



Subqueries



Subqueries

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

–

O resultado da subquery é, então, avaliadopelo da query principal (outer query ).

SELECT select_list FROM table

WHERE expr operator

(SELECT select_list

FROM table);



2975

SQL> SELECT enome2 FROM emp3 WHERE sal > 4 (SELECT sal5 FROM emp6 WHERE enum=7566);

Utilizando uma Subquery

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



Regras para Subqueries

–Utilize subqueries entre parênteses.

–As subqueries vêem sempre à direita dooperador de comparação.

–

Não utiliza a cláusula ORDER BY emsubqueries .

–Uma subquery retorna uam tabela sobre aqual pode-se realizar qualquer uma das

operações vista anteriormente.



Melhorando a apresentaçãodos 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 emp3 WHERE enum = &num_empregado;

Enter value for num_empregado: 7369

ENUM ENOME SAL DEPTO_NUM --------- ---------- --------- ---------

7369 MANUEL 800 20



–Utilize aspas simples para datas e strings .

Enter value for cargo: ANALISTA

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

Substituindo Variáveis com o &

SQL> SELECT enome, depto_num, sal*12

2 FROM emp3 WHERE cargo = '&cargo';

Especificando Nomes de Colunas



SQL> SELECT enum, enome, cargo, &nome_coluna2 FROM emp3 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: sal Enter value for condicao: sal>=3000 Enter value for ordenar_por: enome

ENUM ENOME CARGO SAL--------- ---------- --------- ---------

7902 JOSE ANALISTA 30007839 CARLOS PRESIDENTE 50007788 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 deEmpregados

Cargo Empregado Salario----------------------- ----------------------- -----------------CAIXA PAULO R$1,100.00

CAIXA 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 permanecemarmazenados 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 THENINSERT 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)



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;

--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 chamaroutras procedures

Procedures são salvas em um arquivo .sql ecompiladas 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 nobanco de forma compilada

Acionados automaticamente pelo bancoquando sua condição de ativação forveradeira



Sintaxe

CREATE OR REPLACE TRIGGER NOMECONDICAO DE ATIVACAOBEGIN <CORPO>

END;

/

A CONDICAO DE ATIVACAO pode ser montada a partir deexpressõ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_EMPREGADOBEGININSERT INTO acf_LOG_funcionario

VALUES(SYSDATE,'Tabela modificada');END;/

Sintaxe (exemplo)



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') into datainicial

FROM llma_contratosWHERE 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;

/

Si t ( l )



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



pCREATE 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 ecompiladas no Oracle com o comando@caminho_completo do SQL Plus

Caso ocorra algum erro de compilação otrigger 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

sql plus dicas

Documents