Manual SQL para usuários

MANUAL DO USUÁRIO
SQL
C O M A N D O S
B Á S I C O S
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
2 de 27
ÍNDICE
1.
2.
3.
4.
5.
6.
Introdução ....................................................................................................................................... 3
Seleção de Dados ........................................................................................................................... 4
Manipulando Dados ...................................................................................................................... 12
Tabelas e Estrutura de dados ....................................................................................................... 16
Executando Cálculos com Dados .................................................................................................. 17
Mostrando dados de múltiplas tabelas .......................................................................................... 26
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
3 de 27
1. Introdução
O presente trabalho visa orientar o consultor a utilizar os comandos básicos do SQL e SQL*PLUS
Este manual está definido em tópicos, conforme descrito abaixo:


Mostrar diferenças entre SQL e SQL*PLUS;
Comandos SQL;
Este capítulo mostrará como criar, acessar e manipular o Oracle utilizando o SQL (Structured Query Languague ) e
o SQL*Plus que é um software de propriedade da Oracle Corporation.
Escopo do SQL e SQL*PLUS
O SQL é uma linguagem para comunicação de qualquer ferramenta ou aplicação com o Oracle Server.
O SQL*PLUS é uma ferramenta Oracle que executa comandos SQL. O mesmo não é uma extensão do SQL.
SQL
O SQL trabalha com um buffer que guarda apenas um comando por vez. Estes comandos podem ser editados no
SQL*PLUS.
Comando SQL  Buffer SQL
Tarefas que podemos fazer com o SQL:



Selecionar dados de uma Base de Dados;
Adicionar, modificar e remover dados.
Criar, modificar, e remover Estruturas de dados (tabelas por exemplo).
Comandos mais utilizados.
Para seleção de dados:
SELECT
Para manipulação de dados:
INSERT
UPDATE
DELETE
COMMIT
ROLBACK
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
4 de 27
2. Seleção de Dados
O processo de seleção de dados (SELECT) aparece na fase de produção no ciclo de vida de desenvolvimento de
um sistema .
Entrando no SQL*PLUS
Para se “logar” no SQL*PLUS você deve ser um usuário de banco de Oracle, ou seja, possuir um nome DE
USUÁRIO E SENHA.
Mostrando a estrutura de uma tabela
Para mostrar a estrutura de uma tabela ,ou seja, suas colunas e características das mesmas, basta digitar o
comando:
SQL> DESC nome da tabela.
Exemplo:
SQL > DESC SAFX07
A coluna NULL mostra se deve ser preenchido algum valor nesta coluna, caso um registro seja inserido na tabela.
Mostrando dados com o comando SELECT
A função do comando SELECT é buscar e mostrar dados armazenados, em forma de tabelas, dentro do Banco de
Dados.
SINTAXE:
SELECT colunas
FROM nome da tabela
WHERE condições
ORDER BY expresssão
Onde:
SELECT - clausula que especifica quais colunas, expressões ou constantes serão lidas.
FROM – clausulas que especifica qual (is) tabela(s) possuem estas colunas.
WHERE – clausula que especifica critérios de seleção de linhas (opcional), se não colocada mostrará todos os
dados da tabela.
ORDER BY – clausula que dita a ordem que os dados devem ser mostrados. (opcional).
Mostrando todos os dados de uma tabela:
SQL> SELECT * FROM nome da tabela
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
5 de 27
Exemplo: SELECT * FROM SAFX07;
Para selecionar colunas em especifico:
SINTAXE:
SELECT nome da coluna1, nome da coluna 2, ...
FROM nome da tabela
Exemplo:
SQL> SELECT cod_empresa, cod_estab, movto_e_s FROM safx07;
Mostrando valores distintos:
Uma coluna pode possuir vários valores repetidos, se desejarmos ver estes valores sem repetição basta
adicionarmos antes do nome da coluna a clausula DISTINCT.
SINTAXE:
SELECT [DISTINCT] nome da coluna 1, nome da coluna 2
FROM nome da tabela
Exemplo:
SQL> SELECT DISTINCT
cod_empresa FROM safx07;
Utilizando Alias (apelidos) em colunas
Dentro do comando SELECT podemos substituir (só na saída do resultado) o cabeçalho da coluna. Para isto basta
inserir na frente do nome da coluna o alias (apelido) desejado.
Exemplo:
SQL>
SELECT DISTINCT cod_empresa código_empresa, cod_estab código_estabelecimento
FROM safx07;
CODIGO_EMPRESA
001
CODIGO_ESTABELECIMENTO
AFT
OSA
OBS: Quando você desejar que a escrita saia como você digitou, ou se existirem espaço em branco entre as
palavras, o alias deve ser colocado entre “aspas duplas”. Caso contrario o SQL*PLUS utiliza o default.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
6 de 27
Clausula WHERE
A clausula WHERE, como explicado anteriormente, serve para selecionarmos linhas em uma tabela de acordo com
a condição estabelecida.
Sintaxe:
SELECT {* [ nome da coluna] }
FROM nome da tabela
WHERE condição
Esta condição pode ser, por exemplo, cod_empresa = 001 ou cod_estab <> ‘AFT ““.
A tabela abaixo apresenta os operadores de comparação que podem ser utilizados na clausula WHERE.
=
<>
IGUAL A
DIFERENTE DE
>
<
<=
BETWEEN ... AND ...
NOT BETWEEN … AND
IN(lista)
NOT IN
LIKE
IS NULL
IS NOT NULL
MAIOR QUE
MENOR QUE
MENOR IGUAL
ENTRE DOIS VALORES
FOR A DA FAIXA ESTABELECIDA
Igual a um dos valores da lista
DIFERENTE DOS VALORES DA LISTA
SUPRESSÃO DE CARACTERES
VALORES NULOS
Valores diferentes de nulo
A seguir mostraremos cada condição:
Igual (=)
Retorna as linhas onde os valores de uma coluna são iguais aos estabelecidos.
Exemplo:
SQL> SELECT cod_empresa, cod_estab
WHERE cod_empresa = ‘001’;
from safx07
Para comparações com valores em caracteres devemos tomar o cuidado de descrever o valor tal como foi
armazenado, alem de colocar este valor entre ‘aspas simples’.
Quando comparamos colunas que armazenam datas devemos respeitar a máscara default do ORACLE que é DDMON-YYYY.
Exemplo com data:
SQL> SELECT data_saida_rec from x07_doctor_fiscal where data_saida_rec = ’01-jan-2000’ ;
Os valores da data também devem estar entre ‘aspas simples’.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Diferente de (<>):
Retorna as linhas com valores diferentes do valor estipulado na clausula WHERE.
SELECT cod_empresa, cod_estab
WHERE cod_empresa <> ‘001’;
from safx07
Maior que (>)
Retorna linhas que possuam valores maiores que o estipulado na coluna envolvida na clausula WHERE.
Exemplo:
SQL> SELECT cod_empresa, cod_estab, num_docfis
FROM safx07
WHERE vlr_contabil >
1500 ;
Podemos comparar data e caracteres também.
Maior ou igual a (> =)
Retorna linhas que possuam valores maior ou igual ao determinado.
SQL> SELECT cod_empresa, cod_estab from safx07
WHERE
vlr_contabil >= 10000;
Menor que (<):
Retorna linhas que possuam valores menores que o estabelecido.
Exemplo:
SELECT cod_empresa, cod_estab from x07_docto_fiscal
WHERE data_saida_rec < ’01-jan-2000’;
Menor igual a ( < = )
Retorna linhas que possuam valores, na coluna envolvidos, menores ou iguais ao valor de comparação.
SQL> SELECT cod_empresa, cod_estab, num_docfis from x07_docto_fiscal
WHERE
data_saída_rec <= ’01-jan-2000’;
Entre dois valores ( BETWEEN ... AND ... )
Todas as linhas que possuam valores dentro da faixa determinada serão listadas.
Exemplo :
SQL > SELECT
WHERE
cod_empresa, cod_estab from x07_docto_fiscal
data_saida_rec between ’01-jan-2000’ and ’31-jan-2000’ ;
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Página
7 de 27
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
8 de 27
Todas as NF’s emitidas entre as datas listadas na clausula WHERE serão listadas.
Fora da faixa estabelecida (NOT BETWEEN... AND...):
Retorna todas as linhas que se encontram fora da faixa discriminada na cláusula WHERE.
SQL> SELECT
WHERE
cod_empresa, cod_estab
num_docfis NOT BETWEEN
from safx07
‘101’ and ‘150’ ;
Igual a um dos valores da lista ( IN (lista)):
Usando o comparador IN na cláusula WHERE, podemos determinar valores de comparação.
SQL> SELECT
WHERE
AND
cod_empresa, cod_estab from safx07
cod_empresa = ‘001’
cod_estab in ( ‘AFT’ , ‘OSA’) ;
Todas as NF’s que pertençam à empresa 001 e todos os estabelecimentos com código igual a um dos valores da
lista serão mostrados.
Diferente dos valores da lista ( NOT IN ( lista) ) :
Retorna as linhas que possuem valores diferentes dos valores descritos na lista.
SQL> SELECT cod_empresa, cod_estab
WHERE
AND
from safx07
cod_empresa = ‘001’
cod_estab not in ( ‘AFT’, ‘OSA’);
Todas as NF’s que não pertençam aos estabelecimentos serão listados.
Supressão de caracteres (LIKE)
Para mostrar linhas que possuem parte dos valores comuns, devemos utilizar o operador LIKE. Este operador pode
ter um dos dois símbolos listados abaixo:
% Qualquer número de caracteres (análogo ao * do DOS)
_ Um único caráter (análogo ao ? do DOS)
SQL > SELECT
*
FROM safx01
WHERE cod_conta like '089.0001.1101%'
;
Podemos utilizar também em datas:
SQL >
SELECT * FROM x07_docto_fiscal
WHERE data_saida_rec like
'%-FEB-02'
;
Todas as NF’s com data de Fevereiro de 2002 serão listados.
O exemplo abaixo utiliza o outro símbolo ( _ ) .
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
9 de 27
Exemplo:
Listar os funcionários que o sobrenome começa com a letra ‘S’, e termine com a letra ‘h’ e possua 5 letras no
nome.
SQL> Select last_name
FROM s_emp
WHERE last_name LIKE
‘S_____h’;
Obs: Podemos comparar os dois símbolos em uma mesma comparação.
Valores nulos ( IS NOT NULL)
As colunas que se encontram vazias, para o banco de dados elas possuem valores chamadas NULL. Este valor
possui características específicas que geram a necessidade de comando particular para estes casos.
Se desejarmos visualizar as linhas onde uma determinada coluna está vazia devemos utilizar o IS NULL. Veja no
exemplo abaixo.
Exemplo:
SQL> SELECT *
FROM safx08 WHERE data_fiscal IS NULL;
O exemplo acima mostrará todas as NF’s que não possuem data fiscal relacionados.
Valores diferentes de NULL ( IS NOT NULL)
Da mesma forma poderíamos visualizar só as linhas que possuem dados ( quaisquer ) em uma coluna.
Exemplo:
SQL > SELECT *
FROM safx04
WHERE cod_fis_jur
IS NOT NULL;
Complexidade nas condições
A cláusula WHERE pode ter mais que uma condição no mesmo comando. Para isto incluiremos os operados AND
e OR onde:
AND mostra a linha se todas as condições subpostas no AND são verdadeiros.
OR mostra a linha se uma das condições da cláusula for verdadeiras.
Exemplo:
SQL > SELECT cod_emp, cod_estab
2
3
4
FROM
WHERE
AND
SAFX07
cod_empresa = '001'
cod_estab
= 'RJ0015' ;
Apenas as NF’s da empresa 001 e estabelecimento RJ0015 serão listados.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
10 de 27
Exemplo:
SQL> SELECT *
FROM safx07
AND cod_empresa = ‘001’
OR
cod_fis_jur = ‘1’ ;
Apenas NF’s que pertençam à empresa 001 ou código física jurídica = 1 (mesmo de outras empresas) serão
listadas.
A combinação de duas ou mais condições de busca obedecem a uma regra de procedentes. O resultado da
pesquisa depende desta ordem.
Ordem
Operador
Todos os operadores ( = , < , > , between, ...)
AND
OR
Para quebrar esta precedência, basta utilizar parênteses.
Exemplo:
SQL> SELECT nome, salario, depto
FROM
WHERE
emp
salário >= 1000 and ( depto =
44 or depto = 42) ;
Neste caso apenas funcionários do departamento 44 e 42 com salário maior ou igual a 1000 serão listados.
OBS: Teste o exemplo acima retirando os parênteses.
Ordenação
A ordenação de um resultado não influencia na ordem de gravação dos dados.
Podemos definir dois sentidos de ordenação ascendentes e descendentes.
A cláusula utilizada para ordenação é ORDER BY.
Sintaxe:
SELECT { * [ nome da coluna ] }
FROM nome da tabela
WHERE condição ( opcional)
ORDER BY nome da coluna ASC
Exemplo:
SQL> SELECT razao_social ,
FROM
WHERE
ORDER
data_x04
safx04
data_x04 = '20000101'
BY razao_social ;
Razao_social
data_atualizacao
ABCD Contabilidade
Beta Comunicações
Telesp Celular
Exemplo:
01-jan-2002
11-feb-2002
31-mar-2002
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
11 de 27
SQL> SELECT
razao_social , data_x04
FROM
safx04
WHERE
data_x04 = '20000101'
ORDER BY razao_social DESC;
Razao_social
data_atualizacao
Telesp Celular
Beta Comunicações
ABCD Contabilidade
31-mar-2002
11-feb-2002
01-jan-2002
OBS1: Note que apenas o operador ascendente deve ser explicitado.
OBS2: Não é necessária a coluna utilizada no ORDER BY estar no SELECT.
Quando utilizamos mais que uma coluna no ORDER BY o resultado é dado em função do subconjunto utilizado.
Exercícios:
Tabela: SAFX01
Selecionar conta, indicador (débito / crédito) e valor
1)
2)
3)
4)
Onde valor maior que 1000
Retornar somente os créditos
Data do lançamento entre 3 e 6 de fevereiro de 2004
Listar somente as contas 5.4.5.01.500 e 2.1.1.03.134
Faça-os separadamente e por último junte os quatro exercícios num só.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
3. Manipulando Dados
Objetivos:





Adicionar registros em uma tabela
Gerar números seqüenciais automaticamente
Modificar um registro existente
Remover um registro de uma tabela
Efetivar mudanças no banco de dados
Introdução
A manipulação de dados consiste de cinco comando básicos. São eles:
INSERT UPDATE
DELETE
COMMIT
ROLLBACK
Insere uma nova linha em uma tabela
Modifica linhas existentes.
Remove linhas de uma tabela
Confirma mudanças no Banco de dados
Descarta mudanças no banco de dados
Inserção de dados:
Para adicionar uma nova linha a uma tabela utilizamos o comando INSERT
Sintaxe:
INSERT INTO nome da tabela [ (nome da coluna [ , nome da coluna , ...]) ]
VALUES ( valor1, [, valor 2 ...])
Onde os valores são dados correspondentes às colunas da tabela em questão.
Exemplo:
SQL> INSERT INTO empresa (cod_empresa,razao_social, senha)
VALUES ( '099', 'MASTERSAF', 'MSAF');
Obs 1 : Quando as todas as colunas são preenchidas não é necessário listá-las
Obs 2 : Os valores que recebem caracteres ou datas devem estar entre aspas simples.
Quando nem todas as colunas possuem valores definidos devemos descrever os nomes das colunas.
Exemplo:
SQL> INSERT INTO func ( cod_func, nome_func, dt_admissao, cargo, salario)
VALUES ( ‘001’, ‘Adriana’, ’01-ago-1970’, SYSDATE, ‘consultor’, ‘3000’);
Note que a tabela func possui outras colunas não listadas que receberão valor NULL.
O SYSDATE é uma função que retorna a data corrente do sistema.
O valor NULL poder ser explicitado na listagem de valores, se desejado.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Página
12 de 27
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
13 de 27
Exemplo:
SQL> INSERT INTO func ( cod_func, nome_func, dt_admissao, cargo, salario)
VALUES (‘001’, ‘Adriana’, ’01-ago-1970’, SYSDATE, ‘consultor’, NULL);
Note que podemos tanto escrever NULL como utilizar ‘’ (aspas simples).
Porém devemos saber que as aspas simples só servem para colunas tipo caracter ou data.
Modificando dados
Para modificar dados existentes em uma tabela utilizamos o comando UPDATE.
Sintaxe;
UPDATE nome da tabela
SET nome da coluna = novo valor, [ nome da coluna = novo valor ]
WHERE condição
Exemplo:
SQL> UPDATE X07_docto_fiscal
SET
data_saida = ’01-jan-2000’ , serie_docfis = ‘1’
WHERE num_docfis = ‘123’ ;
Exemplo:
SQL> UPDATE X07_docto_fiscal
SET
movto_e_s = ’1’
WHERE data_saída = ’10-jan-2000’ ;
A cláusula WHERE é muito importante neste comando porque ela determina qual ou quais linhas serão
modificadas.
No comando acima todos as NF’s cuja data seja 10 de janeiro terão o valor da coluna movto_e_s modificada para
1.
Exemplo:
SQL> UPDATE SAFX07
SET
movto_e_s
= ’1’
Já neste exemplo todas as linhas da tabelas terão seus valores alterados.
Removendo dados:
Sintaxe:
DELETE FROM nome da tabela
WHERE condição
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Exemplo:
SQL> DELETE FROM
safx07
WHERE NUM_DOCFIS = ‘123’
O registro que possui a coluna NUM_DOCFIS igual a 123 será removido.
Nesse comando a cláusula WHERE é muito importante, pois podemos remover TODOS os dados caso não
colocarmos esta clausula.
Exemplo:
SQL> DELETE FROM safx07;
Todos os dados da tabela safx07 serão removidos.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Página
14 de 27
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
15 de 27
Controle de Transações:
A forma de controlar as transações dentro de um Banco de dados é utilizando os comandos COMMIT e
ROLLBACK. Uma transação é um conjunto de comandos ( insert, delete, updates).
A função do Commit é finalizar a transação corrente tornando permanente todas as modificações.
Já o Rollback apenas finaliza a transação corrente sem transferir para os arquivos de armazenamento as
modificações efetuadas.
COMMIT
Insert....
Insert ...
Delete..
ROLLBACK
Update…
Update
COMMIT
É importante destacar que todo dado modificado durante a transação só será efetivado quando aquela transação
for confirmada (COMMIT).
Estado do dado antes do COMMIT ou ROLLBACK
Os comandos de manipulação de dados inicialmente afetam o database buffer
O usuário corrente (que efetuou a mudança) pode visualizar suas modificações através do comando SELECT.
Outros usuários não enxergam estas modificações.
As linhas afetadas nos comandos”lockadas”. Nenhum outro usuário pode modificar estas linhas.
Estado do dado após um comando COMMIT
As mudanças são retiradas do database buffer e escritas em arquivos.
Todos os usuários podem visualizar as modificações
Os “locks” nas linhas afetadas são liberados.
Estado do dado após um comando ROLLBACK
As modificações são descartadas. Os arquivos continuam como estavam.
Os “locks’ na linha afetadas pelas modificações são liberadas.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
16 de 27
4. Tabelas e Estrutura de dados
Na fase do ciclo de vida de desenvolvimento de um sistema temos a necessidade de criar tabelas para
armazenamento de dados e estruturas auxiliares de acordo com o projeto em questão.
Tabela: Onde podemos depositar com segurança e eficiência, os dados da instituição e dele extrair informações
com a devida presteza e confiabilidade.
Tipos de dados no Oracle:
Ao determinar um tipo de dados, pode-se escolher entre três características básicas: Caracter, Numérico, ou Data.
Estas características são abaixo detalhadas:
CHARACTER
Os datatypes char e varchar2 armazena dados alfanuméricos. Estes datatypes podem também armazenar
caracteres especiais
CHAR – armazena string de caracteres de largura fixa, aceita uma largura de coluna entre 2 e 255 ( em bytes não
caracteres). Espaços em branco são utilizados para preencher o espaço restante, no caso do valor ter tamanho
inferior ao Maximo determinado para aquela coluna.
VARCHAR2, ao contrario, armazena somente os caracteres significativos (o que inclui brancos anteriores à
seqüência de caracteres e entre palavras) até 2048 bytes.
NUMBER
Este tipo de dados armazena números com casas decimais fixas ou flutuantes.
Oracle garante portabilidade de números com uma precisão igual ou inferior a 38 dígitos.
Pode-se determinar um numero total de digito e uma quantidade de decimais.
DATE
Valores em forma de datas. Faixa se 1/1/4712 AC até 31/12/4712 DC
LONG
É similar ao Varchar2, mas com tamanho máximo de 2 gigabytes.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
17 de 27
5. Executando Cálculos com Dados





Executar cálculos com números usando operadores aritméticos e funções
Executar cálculos com datas usando operadores aritméticos e funções
Formatação de números e datas
Concatenação e manipulação de caracteres string
Executar cálculos com função de agrupamento.
Executando cálculos aritméticos
Para modificar e apresentar dados numa variedade de caminhos podemos utilizar as funções aritméticas e
padronização do SQL.
Para executar cálculos com números podemos utilizar os operadores aritméticos. São eles
+ Adição
- Subtração
* Multiplicação
/ Divisão
Os operadores aritméticos podem ser utilizados em quaisquer cláusulas do comando SELECT exceto a cláusula
FROM.
Exemplo:
SQL> SELECT
nome, salario, perc_comissao, salario * perc_comissao / 100 COMISSAO
FROM
funcionario
WHERE
funcao = ‘Vendedor’
AND
salario * perc_comissao / 100 > 200
ORDER BY salario * perc_comissao / 100;
O resultado seria:
NOME
-----------------Nair
Silva
SALARIO
-------------1525
1459
PERC_COMISSAO
------------------------15
17.5
COMISSAO
----------------228.75
253.75
Para utilizar os operadores aritméticos devemos obedecer às regras de precedência.
Regra:
1 Multiplicação e divisão ( * , / )
2 Adição e subtração ( + , - )
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Página
Nome do Arquivo
18 de 27
Manual Usuário SQL
Exemplo:
SQL> SELECT nome , salario
(salario + 100 ) * 12
FROM funcionario
WHERE cod_depto = 41;
NOME
Simone
Andréa
Julio
Silvia
SALARIO
1450
1200
1400
940
“Bonus_anual”
bonus_anual
18600
15600
18000
12480
Funções numéricas
As funções numéricas são padrão SQL e só tem efeito sobre números ou colunas do tipo number.
Funções ROUND
Esta função arredonda valores aos mostrá-los. Note que os valores no Banco de dados continuam intactos.
Exemplo:
SQL> SELECT nome , salario , salario/22, ROUND(salario/22,0)
FROM
WHERE
funcionario
cod_depto =
NOME
50 ;
SALARIO
Simone
Andréa
2500
1550
SALARIO/22
113.636364
70.4545455
ROUND(SALARIO/22,0)
114
70
Função TRUNC:
Esta função mostra o numero truncado
Exemplo:
SQL> SELECT nome , salario , salario/22, TRUNC(salario/22,0)
FROM funcionario
WHERE cod_depto =
NOME
Simone
Andréa
50 ;
SALARIO
2500
1550
SALARIO/22
113.636364
70.4545455
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
TRUNC(SALARIO/22,0)
113
70
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Página
Nome do Arquivo
19 de 27
Manual Usuário SQL
Função MOD:
Esta função determina o valor referente ao resto da divisão de um numero.
Exemplo:
SQL> SELECT
FROM
WHERE
nome , data_final , SYSDATE , SYSDATE – data_final “total”
TRUNC( ( SYSDATE – data_final ) / 7 , 0 ) SEMANAS,
ROUND( MOD ( SYSDATE – data_final)_ , 7 ) , 0 ) DIAS
funcionário
cod_depto = 50 ;
O resultado seria:
NOME
DATA_FINAL
SYSDATE
Simone
Andréa
Junior
Mauro
09-FEB-1992
18-JAN-1992
22-JAN-1992
07-FEB-1992
15-DEC-1993
15-DEC-1993
15-DEC-1993
15-DEC-1993
total
SEMANAS
675.833993
697.833993
693.833993
677.833993
96
99
99
96
DIAS
4
5
1
6
Cálculos com valor NULL
Qualquer calculo efetuado com um valor NULL o resultado será sempre NULL. Portanto para obter algum valor ou
evitar divisão por um valor desconhecido, o que geraria um erro, utilizamos a função NVL que transforma o valor
NULL em um número ou string desejado.
Sintaxe:
NVL ( expr1, expr2 )
Onde:
Expr1 – colunas que contem valores nulos.
Expr2 – valor o qual os valores nulos serão transformados.
Exemplo:
SQL> SELECT
FROM
WHERE
NOME
Andréa
Simone
Junior
Mauro
nome , salario , NVL(perc_comissao,0) PORCENTAGEM,
Salario * NVL( PERC_COMISSAO,0) / 100 COMISSAO
funcionario
salário >= 1500;
SALARIO
2500
1550
1515
1525
PORCENTAGEM
0
0
10
15
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
COMISSAO
0
0
151.5
228.75
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Executando cálculos com datas
Podemos utilizar os operadores aritméticos também nas datas
Expressão valida:
Data + numero – Soma um numero a uma data e devolve uma data posterior
Data – numero – Subtrai um numero da data e devolve uma data anterior.
Data – data – Subtrai uma data de oura data e retorna o numero de dias entre uma data e outra.
Exemplo;
SQL> SELECT
FROM
WHERE
nome , data_final , data_final + 90 “Aprovacao”
funcionario
cod_depto = 42 ;
NOME
Mary
Jorge
Nelson
DATA_FINAL
14-MAY-1990
09-FEB-1991
06-AUG-1991
Aprovacao
12-AUG-1990
10-MAY-1991
04-NOV-1991
Funções de formatação
Elemento
DD
DY
DAY
DDSP
MM
MON
MONTH
Descrição
Dia do mês (1-31)
As três primeiras letras do dia da
semana (FRI)
Dia da semana (MONDAY)
Numero de dias do mês (TWELVE)
Numero do mês (1-12)
As três primeiras letras do mês (JAN)
Nome do mes (JANUARY)
YY
Dois dígitos do ano (03)
YYYY
Quatro dígitos do ano (2003)
HH:MM:SS
Hora minuto e segundos (10:00: 33)
Fm
Supressão de espaços em branco
HH24
TH
AM ou PM
Hora de 0-23
Numero Ordinal
Para hora padrão ericano
Para modificar o formato de datas devemos utilizar a função TO_CHAR.
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Página
20 de 27
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Sintaxe:
TO_CHAR (coluna tipo data, máscara).
Exemplo
SQL> SELECT num_docto,
FROM
WHERE
safx08
cod_empresa
TO_CHAR(data_fiscal, ‘MM/YYYY’) DATA_NF
= ‘001’;
Resultado:
Num_docto
45670
87644
98374
SQL> SELECT
FROM
WHERE
DATA_NF
02/2003
03/2003
05/2003
num_docfis, TO_CHAR( data_fiscal, ‘fmDD MONTH YYYY’) data_nf
safx08
cod_empresa = ‘001’;
Num_docfis
45670
87644
98374
DATA_NF
10 February 2003
23 March 2003
15 May 2003
Concatenação de caracteres string
Para concatenar caracteres string utilizamos o símbolo ||.
Exemplo:
SQL > SELECT
2 FROM
3 WHERE
num_docfis || ‘ - ‘ || serie_docfis nota_fiscal
safx07
cod_empresa = ‘001’;
NOTA_FISCAL
1234 - 1
89777 - 4
83646 - 9
Funções com caracteres
Mostra um caracter string com a primeira letra maiúscula.
Exemplo:
SQL > SELECT INITCAP( nome) nome_alterado , nome
FROM
WHERE
x15_funcionario
municipio LIKE 'RIO%';
NOME_ALTERADO
NOME
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Página
21 de 27
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Luisa Brunet Da Silva
Carla Perez De Oliveira Machado
Maria Das Dores Silva Araujo
LUISA BRUNET DA SILVA
CARLA PEREZ DE OLIVEIRA MACHADO
MARIA DAS DORES SILVA ARAUJO
UPPER
Transforma todos os caracteres em maiúsculos.
Exemplo:
SQL> SELECT
FROM
WHERE
nome
x15_funcionario
UPPER(nome) like '%SILVA%';
NOME
LUISA BRUNET DA SILVA
MARIA DAS DORES SILVA ARAUJO
JOAO PEDRO DA SILVA JUNIOR
SUBSTR
Retorna parte de uma string
Sintaxe
SUBSTR( STRING, m [ , n ] )
Onde:
String: palavra ou coluna do tipo caracter que deve ser cortada.
M : Especifica a posição inicial
N : Especifica quantos carateres serão cortados a partir da posição inicial.
Exemplo:
SQL> SELECT descricao
FROM
WHERE
safx2013
SUBSTR(descricao,1,3) = ‘Cad’;
PRODUTO
----------------Caderno
Cadeira
LENGTH
Retorna o comprimento, em caracteres, de um string.
Exemplo:
SQL> SELECT num_docfis,
FROM
WHERE
NUM_DOCFIS
LENGTH (num_docfis) “num_caracteres”
safx07
cod_empresa = '001’
Num_caracteres
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Página
22 de 27
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
109876
4398764356
Página
23 de 27
6
10
Função de substituição (REPLACE)
Substitui um ou mais caracteres numa string:
SQL> UPDATE SAFX07
SET SERIE_DOCFIS = REPLACE(SERIE_DOCFIS, ‘-‘, ‘’)
WHERE NUM_DOCFIS = ‘000123’;
Função DECODE
Tem a mesma função do comando “IF” (condição), porém só pode ser utilizado numa instrução SQL. Por exemplo,
pode ser utilizado para apresentar o conteúdo de uma coluna de uma forma mais “amigável”.
SQL> SELECT DECODE(IND_PRODUTO,
FROM
‘1’, ‘PRODUTO ACABADO’
‘2’, ‘INSUMO’,
‘3’, ‘EMBALAGEM’,
‘OUTROS’) IND_PRODUTO,
COD_PRODUTO
SAFX2013;
Funções de agrupamento
As funções de agrupamento são médias (AVG), valor máximo (MAX), valor mínimo (MIN), e soma (SUM)
SQL> SELECT AVG(salario) Media, MAX(salario) Máximo
FROM funcionario
WHERE UPPER (cargo)
Media
1476
like
Máximo
1525
MIN(salario) Minimo, SUM (salario) Soma
‘DIRETOR%’;
Mínimo
1400
Soma
7380
Contando linhas de uma tabela
A função de agrupamento que retorna o número de linhas existentes em uma tabela é o COUNT.
SQL> SELECT count(1) TOTAL
from safx01
TOTAL
---------256
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
24 de 27
Cláusula GROUP BY
Quando desejamos obter resultados agrupados o SQL nos fornece a cláusula GROUP BY que permite que os
valores singulares possam ser combinados com valores agrupados na cláusula SELECT .
Sintaxe:
SELECT nome da coluna singular 1 [ , nome da coluna singular2, ...], função de agrupamento [, função de
agrupamento...]
FROM nome da tabela
WHERE condição ( opcional)
GROUP BY nome da coluna singular 1 [ , nome da coluna singular2, ...]
Exemplo
SQL> SELECT cod_empresa, COUNT(*)
“tot_emp”
FROM safx08
GROUP BY cod_empresa
Cod_empresa
AFT
OSA
tot_emp
330
2344
SQL> SELECT cod_empresa,
cod_estab , COUNT(*)
FROM safx08
GROUP BY cod_empresa, cod_estab;
Cod_empresa
AFT
AFT
OSA
cod_estab
001
002
001
total
total
330
450
2344
Cláusula HAVING
Esta cláusula restringe linhas mostradas pela cláusula GROUP BY. Podemos comparar analogamente a cláusula
HAVING com a cláusula WHERE do SELECT.
Sintaxe:
SELECT nome da coluna singular 1 [ , nome da coluna singular2, ...], função de agrupamento [, função de
agrupamento...]
FROM nome da tabela
WHERE condição ( opcional)
GROUP BY nome da coluna singular 1 [ , nome da coluna singular2, ...]
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
25 de 27
Exemplo
SQL> SELECT X02.COD_EMPRESA
X02.COD_ESTAB
X02.DATA_SALDO
X02.COD_CONTA
COUNT(*)
FROM
SAFX02
X02
GROUP BY X02.COD_EMPRESA
X02.COD_ESTAB
X02.DATA_SALDO
X02.COD_CONTA
HAVING COUNT(*) > 1
EMP,
EST,
DATA,
IDCONTA,
,
,
,
Exercícios:
Tabela: SAFX03
1)
2)
3)
4)
Somar valor de movimentação agrupando pelo indicador de débito e crédito (listar as colunas indicador e valor)
Listar a menor data de emissão existente na tabela
Listar o maior número de nota fiscal existente na tabela
Contar a quantidade de registros para cada código de pessoa física / jurídica (listar quantidade e código).
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Página
26 de 27
Manual Usuário SQL
6. Mostrando dados de múltiplas tabelas
Objetivos:
Selecionando dados de múltiplas tabelas
Para selecionar dados que se encontram em mais de uma tabela utilizamos uma regra chamada JOIN
CONDITION. Esta regra aconselha que as tabelas devem estar relacionadas.
Por exemplo:
Mostre o numero da nota fiscal e todos os itens.
Para executar esta query devemos fazer um JOIN entre a tabela SAFX07 E SAFX08 utilizando como comparação
os campos chaves das tabelas.
EQUI JOIN
É a comparação simples entre colunas relacionadas.
Sintaxe
SELECT tabela1.coluna, tabela1.coluna , tabela2.coluna , tabela2.coluna
FROM tabela1, tabela2
WHERE tabela1.coluna_chave = tabela2.coluna_chave
Exemplo:
SELECT
FROM
WHERE
AND
AND
AND
AND
AND
AND
AND
AND
GROUP BY
X07.COD_EMPRESA
X07.COD_ESTAB
X07.DATA_FISCAL
X07.MOVTO_E_S
X07.IDENT_FIS_JUR
X07.NUM_DOCFIS
X07.SERIE_DOCFIS
X07.VLR_TOT_NOTA
X07.VLR_PRODUTO
SUM(X08.VLR_CONTAB_ITEM)
X07_DOCTO_FISCAL
X08_ITENS_MERC
X07.COD_EMPRESA
X07.COD_ESTAB
X07.DATA_FISCAL
X07.MOVTO_E_S
X07.NORM_DEV
X07.IDENT_DOCTO
X07.IDENT_FIS_JUR
X07.NUM_DOCFIS
X07.SERIE_DOCFIS
X07.COD_EMPRESA
X07.DATA_FISCAL
X07.IDENT_FIS_JUR
X07.SERIE_DOCFIS
X07.VLR_PRODUTO
=
=
=
=
=
=
=
=
=
,
,
,
,
EMP,
ESTAB,
DATA,
E_S,
PFJ,
NF,
SER,
VL_NF,
VL_PROD,
VL_ITEM
X07,
X08
X08.COD_EMPRESA
X08.COD_ESTAB
X08.DATA_FISCAL
X08.MOVTO_E_S
X08.NORM_DEV
X08.IDENT_DOCTO
X08.IDENT_FIS_JUR
X08.NUM_DOCFIS
X08.SERIE_DOCFIS
X07.COD_ESTAB
X07.MOVTO_E_S
X07.NUM_DOCFIS
X07.VLR_TOT_NOTA
,
,
,
,
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br
Título do Documento / Descrição
Manual de Comandos Básicos – SQL
Manual do Usuário
Nome do Arquivo
Manual Usuário SQL
Página
27 de 27
UNIÕES
Podemos eventualmente unir duas linhas de consultas simplesmente utilizando a palavra reservada UNION.
Exemplo:
Liste todos os clientes/fornecedores que tenham indicador = 3 e seja de SP ou que tenham indicador = 5 e seja de
RJ
SELECT
FROM
WHERE
AND
UNION
SELECT
FROM
WHERE
AND
*
X04_PESSOA_FIS_JUR
IND_FIS_JUR = 5
IDENT_ESTADO = 69
*
X04_PESSOA_FIS_JUR
IND_FIS_JUR = 3
IDENT_ESTADO = 76;
SUB-CONSULTAS
Uma sub-consulta é um comando "select" que é aninhado dentro de outro "select" e que devolve resultados
intermediários.
Exemplo:
Listar todos os itens de nota que estejam cadastrado no Mastersaf como embalagem.
SELECT
FROM
WHERE
COD_EMPRESA, COD_ESTAB, DATA_FISCAL, NUM_DOCFIS, SERIE_DOCFIS
X08_ITENS_MERC
IDENT_PRODUTO IN (SELECT IDENT_PRODUTO
FROM
X2013_PRODUTO
WHERE IND_PRODUTO = 3)
Sidney V idal – Instrutor ABAP/4
Cel 11 97128-2414 / SKYPE : VIDAL1500
http://www.mastersoft-ti.com.br