Bogos

Lista de Funções SQL

Obtendo como plataforma a base de dados Oracle, abaixo falarei sobre funções SQL, seus tipos, categorias e listarei todas elas. Com o tempo estarei explicando a funcionalidade, usuabilidade e sintaxe de cada uma delas. Em mais de 90% dos casos, o tratamento de excessões e utilização das funções é comum comparado a outras bases de dados portanto serve como referência não só para Oracle.

Bom basicamente as funções são recursos usados em meio a query SQL
No Oracle, se você chamar uma função SQL passando um argumento de tipo incompatível com o tipo esperado, o sistema tentará converter o argumento para o tipo válido. Se você chamar uma função com um argumento “vazio”, ou seja, NULL, então automaticamente a função retorna NULL.

As funções SQL são divididas em categorias, segue:

– Funções de uma linha
– Funções de agregaçoes (ou agregadas)
– Funções analíticas
– Funções de referência à objeto
– Funções de modelo
– Funções definidas pelo usuário

As funções de uma linha são dividias em sub-categorias, segue:

– Funções Numéricas
– Funções Alfanuméricas
– Funções de Data e Hora
– Funções de conversão
– Funções de coleção
– Funções variadas

Segue agora uma lista de todas as funções (da base de dados Oracle) linkadas com suas respectivas explicações de uso e sintaxe. A lista ainda não está 100% linkada, mas é atualizada diariamente. Para acessar o link é precisa estar cadastrado no fórum TecnoLive, é de graça, REGISTRE-SE JÁ!

Funções de Uma Linha

Numéricas
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND(numero)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC(numero)
WIDTH_BUCKET

Caracter
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER

Caracter com retorno numérico
ASCII
INSTR
LENGTH
REGEXP_INSTR

Data/Hora
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT(data/hora)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND(data)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR(data/hora)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC(data)
TZ_OFFSET

Conversão
ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
RAWIDTOCHAR
RAWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR(caracter)
TO_CHAR(data/hora)
TO_CHAR(numero)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR(data/hora)
TO_NCHAR(numero)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRANSLATE … USING
UNISTR

Coleção
CARDINALITY
COLLECT
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
SET

Variadas
BFILENAME
COALESCE
CV
DECODE
DEPTH
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXTRACT(XML)
EXTRACTVALUE
GREATES
LEAST
LNNVL
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
ORA_HASH
PATH
PRESENTNNV
PRESENTV
PREVIOUS
SYS_CONNECT_BY_PATH
SYS_CONTEXT
SYS_DBURIGEN
SYS_EXTRACT_UTC
SYS_GUID
SYS_TYPEID
SYS_XMLAGG
SYS_XMLGEN
UID
UPDATEXML
USER
USERENV
VSIZE
XMLAGG
XMLCOLATTVAL
XMLCONCAT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM

Funções de agregação (ou agregadas)
Funções de grupo

AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_Funções (Regressão Linear)
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

Funções de Referência a Objeto

DEREF
MAKE_REF
REF
REFTOHEX
VALUE

Funções Modelo

CV
ITERATION_NUMBER
PRESENTNNV
PRESENTV
PREVIOUS

Funções Analíticas

As funções analíticas são utilizadas principalmente para por exemplo a extração de dados gerenciais na base de dados. O Oracle é o banco que mais possui dessas funções, mas muitos SGBDs atuais já contém essas funções disponíveis em suas novas versões.
Vamos dar um exemplo para utilizar essas funções:

Imagine que você tenha um sistema de cobrança na sua empresa. Seus recebimentos são baseados em contratos, ou seja, todos os meses você realiza vendas e, como as vendas são financiadas, você tem um fluxo freqüente de parcelas.
Você recebe a seguinte missão do diretor financeiro: “preciso saber quais são os contratos com os maiores valores de prestação a cada mês”. Seria relativamente simples oferecer esta resposta caso o diretor não tivesse incluído “contratos” na frase. Saber o valor das prestações a cada mês é muito simples.

Para resolver este problema, você precisará utilizar a partícula OVER do comando SELECT. Com esta opção é possível, além de extrair dados sumarizados, mostrar as linhas analíticas (detalhe) ao mesmo tempo.

Ao utilizar o OVER você poderá estabelecer uma “quebra” (ou particionamento) para sua análise. Veja no comando a seguir:

Código:
SELECT to_char( dtvenc, ‘MM-YYYY’) dtvenc, cdcontr,
MAX( vlprest ) OVER (PARTITION BY to_char(dtvenc,’YYYYMM’))
max_prest, vlprest
FROM vRECEBER
WHERE dtvenc > sysdate;

O destaque mostra a atuação da função de grupo (MAX) e do particionamento da pesquisa (OVER). Neste caso, está sendo mostrado o maior valor de prestação de cada mês. Este resultado será mostrado na coluna MAX_PREST. O resultado pode ser observado na imagem abaixo:

Figura 1

Outro ponto que merece destaque é a possibilidade de mostrarmos os dados analíticos (VLPREST, CDCONTR e DTVENC) ao mesmo tempo que trabalhamos o maior valor do mês (MAX_PREST). Desta forma é possível observarmos quais são os contratos, os valores originais das prestações e o maior valor daquele mês.

Naturalmente não é exatamente isto que o diretor pediu… Ele quer apenas o maior valor do mês e qual é o contrato. Podemos notar que o maior valor do mês 10/2006 é o contrato 56 cujo valor é R$ 695,09 (aliás, o único contrato deste mês). Já nos meses seguintes nota-se que o maior valor do mês 11/2006 é R$ 11.452,35 do contrato 33. E assim sucessivamente.

Para oferecermos apenas o resultado esperado pelo diretor, precisamos ir além no nosso comando:

Código:
SELECT dtvenc, cdcontr, vlprest
FROM (SELECT to_char( dtvenc, ‘MM-YYYY’) dtvenc, cdcontr,
MAX( vlprest ) OVER (PARTITION BY to_char(dtvenc,’YYYYMM’))
max_prest, vlprest
FROM vRECEBER
WHERE dtvenc > sysdate
) WHERE vlprest = max_prest;

Ao se utilizar a subquery na cláusula FROM, pode-se extrair somente as linhas que interessam, ou seja, aqueles contratos e prestações que têm o maior valor de prestação a cada mês (comparação do MAX_PREST e do VLPREST) na segunda cláusula WHERE. O resultado pode ser visto na imagem abaixo:

Figura 2

Podem-se utilizar diversos particionamentos em uma única busca e também há outras funções de grupo para se utilizar com a partícula OVER, mas isso fica por sua conta.

Bom por enquanto é isso. Com o passar do temp vou escrevendo as descrições de cada função.

Fonte: Usei como referência alguns manuais de treinamento da Oracle.

Anúncios

3 Comentários »

  1. Bom Dia !

    Imagine a seguinte tabela:

    Codigo Nome Limite
    1 Jose 500,00
    2 Pedro 700,00
    3 Paulo 800,00
    4 Marcos 1200,00
    5 João 1800,00
    6 Marcio 2000,00

    Gostaria de montar um sql que me traga o agrupamento de cadastros de acordo com uma faixa de valores.
    Exemplo

    Faixa Pessoas Tot. Lim
    De 0 a 1.000 3 2.000,00
    De 1.001 a 2.000 2 3.000,00
    De 2.001 a 3.000 1 2.000,00

    Nesse caso, para a columa faixa, fosse possível passar
    o parâmetro 1.000, ou seja, agrupar de 1,000 em 1.000.
    Se passar valor 500, agruparia de 500 em 500.

    Att

    Comentário por Leandro Vignoto — outubro 22, 2010 @ 11:03 am

    • Só estudar carinha, mas tente um Sub-select que vai!

      Comentário por Troll Face — março 29, 2012 @ 1:53 pm

    • Procure pela função CASE.

      Comentário por Gustavo Sardenberg — maio 25, 2017 @ 8:14 pm


RSS feed for comments on this post. TrackBack URI

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Crie um website ou blog gratuito no WordPress.com.

%d blogueiros gostam disto: