Explicação detalhada de funções de linha única e funções de grupo PL/SQL Uma função é um programa que possui zero ou mais parâmetros e um valor de retorno. Oracle possui uma série de funções integradas em SQL,
Essas funções podem ser chamadas de instruções SQL ou PL/SQL. As funções são divididas principalmente em duas categorias:
Funções de grupo de funções de linha única Este artigo discute como utilizar funções de linha única e as regras para seu uso.
Função de linha única em SQL
SQL e PL/SQL vêm com muitos tipos de funções, incluindo caracteres, numéricos, data, conversão e funções mistas para processamento de linhas únicas de dados.
Portanto, elas podem ser chamadas coletivamente de funções de linha única. Essas funções podem ser usadas em SELECT, WHERE, ORDER BY e outras cláusulas,
Por exemplo, o exemplo a seguir contém funções de linha única, como TO_CHAR, UPPER e SOUNDEX.
SELECT nome,TO_CHAR(data de contratação,'dia,DD-Seg-AAAA')
A PARTIR DE emp
Onde UPPER(ename) como 'AL%'ORDER BY SOUNDEX(ename)
Funções de linha única também podem ser usadas em outras instruções, como a cláusula SET de atualização, a cláusula VALUES de INSERT e a cláusula WHERE de DELET.
O exame de certificação dá atenção especial ao uso dessas funções na instrução SELECT, portanto nossa atenção também está voltada para a instrução SELECT.
Funções NULL e de linha única Compreender NULL pode ser difícil no início, e mesmo uma pessoa muito experiente ainda pode ficar confusa com isso.
O valor NULL representa um dado desconhecido ou um valor nulo. Qualquer operando de um operador aritmético é um valor NULL e o resultado é um valor NULL.
Esta regra também se aplica a muitas funções. Somente CONCAT, DECODE, DUMP, NVL e REPLACE podem retornar valores não NULL quando chamados com parâmetros NULL.
Dentre elas, a função NVL é a mais importante porque pode lidar diretamente com valores NULL.
NVL tem dois parâmetros: NVL(x1,x2), x1 e x2 são expressões. Quando x1 é nulo, X2 é retornado, caso contrário, x1 é retornado.
Vamos dar uma olhada na tabela de dados de empregos. Ela contém salário e bônus. Precisamos calcular a remuneração total.
nome da coluna
tipo de chave de bônus salarial emp_id
pk nulos/exclusivo nn,u nnfk tabela tipo de dados número número número comprimento 11,2 11,2
Em vez de simplesmente somar o salário e o bônus, se determinada linha tiver valor nulo, o resultado será nulo, como no exemplo a seguir:
atualizar salário do empset = (salário + bônus) * 1,1
Neste extrato o salário e bônus do funcionário serão atualizados para um novo valor, mas caso não haja bônus,
Ou seja, salário + nulo, então será tirada uma conclusão errada. Neste momento, a função nvl deve ser usada para eliminar a influência de valores nulos.
Então a afirmação correta é:
atualizar salário do empset = (salário + nvl (bônus, 0) * 1,1
Funções de string de linha única As funções de string de linha única são usadas para manipular dados de string.
ASCII()
c1 é uma string, retorna o código ASCII da primeira letra de c1 e sua função inversa é CHR()
SELECIONE ASCII('A') BIG_A,ASCII('z') BIG_z
DE empBIG_A BIG_z65 122
CHR(<i>)[NCHAR_CS]
i é um número e a função retorna a representação decimal do caractere
selecione CHR(65),CHR(122),CHR(223)
DE empCHR65 CHR122 CHR223A z B
CONCAT(,)
Tanto c1 quanto c2 são strings. A função conecta c2 à parte traseira de c1. Se c1 for nulo, c2 será retornado.
Se c1 e c2 forem nulos, retorne nulo. Ele retorna o mesmo resultado que o operador ||
selecione concat('slobo ','Svoboda') nome de usuário
de dualusernameslobo Syoboda
INITCAP()
c1 é uma string. A função retorna a primeira letra de cada palavra em maiúscula e as demais letras em minúsculas. As palavras são compostas por espaços, caracteres de controle,
Restrições de pontuação.
selecione INITCAP('veni,vedi,vici') César
de dualCeasarVeni,Vedi,Vici
INSTR(,[,＜i＞[,]])
c1 e c2 são strings, i e j são inteiros. A função retorna a posição da j-ésima ocorrência de c2 em c1, e a pesquisa começa a partir do i-ésimo caractere de c1.
Quando o caractere necessário não for encontrado, 0 será retornado. Se i for um número negativo, a pesquisa será realizada da direita para a esquerda, mas a posição ainda será calculada da esquerda para a direita.
O valor padrão para i e j é 1.
SELECT INSTR('Mississippi','i',3,3)
FROM dual INSTR('MISSISSIPPI','I',3,3)
11
selecione INSTR('Mississippi','i',-2,3)
de INSTR duplo('MISSISSIPPI','I',3,3)
2
INSTRB(,[,i[,j])
O mesmo que a função INSTR(), exceto que retorna bytes. Para byte único, INSTRB() é igual a INSTR().
COMPRIMENTO()
c1 é uma string e o comprimento de c1 é retornado. Se c1 for nulo, um valor nulo será retornado.
selecione LENGTH('Ipso Facto') logo
de dualergo10
COMPRIMENTOb()
Assim como LENGTH(), retorna bytes.
mais baixo()
Retorna o caractere minúsculo de c, que geralmente aparece na substring where.
selecione INFERIOR (nome da cor)
de detalhe do item
WHERE LOWER (nome da cor) LIKE '% branco%'COLORNAMEWinterwhite
LPAD(,＜i＞[,])
c1 e c2 são strings e i é um número inteiro. Use a string c2 para complementar o comprimento i no lado esquerdo de c1, que pode ser repetido várias vezes se i for menor que o comprimento de c1,
Então, apenas os caracteres c1, desde que i, serão retornados e os demais serão truncados. O valor padrão de c2 é um espaço único, consulte RPAD.
selecione LPAD(resposta,7,'') preenchido,resposta não preenchida
da pergunta;
PADDED UNPADDED Sim Sim NÃO NÃOTalvez talvez
LTRIM(,)
Remova o caractere mais à esquerda em c1 para que o primeiro caractere não esteja em c2. Se não houver c2, então c1 não será alterado.
selecione LTRIM('Mississippi','Mis') de dualLTRppi
RPAD(,＜i＞[,])
Use a string c2 para complementar o comprimento i no lado direito de c1, que pode ser repetido várias vezes. Se i for menor que o comprimento de c1, apenas caracteres c1 contanto que i serão retornados.
Outros serão truncados. O valor padrão de c2 é um espaço único e os demais são semelhantes ao LPAD.
RTRIM(,)
Remova o caractere mais à direita em c1 para que o último caractere não esteja em c2. Se não houver c2, então c1 não será alterado.
SUBSTITUIR(,[,])
c1, c2 e c3 são todos strings. A função substitui c2 que aparece em c1 por c3 e retorna.
selecione REPLACE('cidade alta','cima','baixo')
de dualREPLACEcentro da cidade
STBSTR(,＜i＞[,])
c1 é uma string, i e j são inteiros e uma substring de comprimento j é retornada começando na i-ésima posição de c1 ou até o final da string se j estiver vazio.
selecione SUBSTR('Mensagem',1,4)
de dualSUBSMess
SUBSTRB(,<i>[,])
É aproximadamente igual a SUBSTR, exceto que I e J são calculados em bytes.
SOUNDEX()
Retorna palavras com som semelhante a c1
selecione SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson')
de dualDawes Daws DawsonD200 D200 D250
TRADUZIR(,,)
Substitua os mesmos caracteres em c1 que c2 por c3
selecione o teste TRANSLATE('fumble','uf','ar') do dualTEXTramble
TRIM([[]] de c3)
Exclua o primeiro, o último ou ambos na string c3.
selecione TRIM('espaço acolchoado') aparar de TRIMspace acolchoado duplo
SUPERIOR()
Retorna a versão maiúscula de c1, que geralmente aparece na substring where.
selecione o nome de dual onde UPPER(nome) LIKE 'KI%'NAMEKING
Funções numéricas de linha única As funções numéricas de linha única operam em dados numéricos, realizando operações matemáticas e aritméticas. Todas as funções aceitam parâmetros numéricos e retornam valores numéricos.
Os operandos e valores de todas as funções trigonométricas são radianos em vez de ângulos. O Oracle não fornece uma função de conversão integrada para radianos e ângulos.
ABS()
Retorna o valor absoluto de n
ACOS()
A função cofator inverso retorna um número entre -1 e 1. n representa radianos
selecione ACOS(-1) pi,ACOS(1) ZERO
DE dualPI ZERO3.14159265 0
ASIN()
De qualquer forma, a função misteriosa retorna -1 a 1, n representa radianos
ATAN()
Função arco tangente, retorna o valor do arco tangente de n, onde n representa radianos.
CEIL()
Retorna o menor número inteiro maior ou igual a n.
COS()
Retorna o covalor de n, onde n é radianos
COSH()
Retorna o cofator hiperbólico de n, onde n é um número.
selecione COSH(<1,4>)
DE dualCOSH(1.4)2.15089847
EXP()
Retorna a enésima potência de e, e=2,71828183.
CHÃO()
Retorna o maior número inteiro menor ou igual a N.
LN()
Retorna o logaritmo natural de N, que deve ser maior que 0
REGISTRO(,)
Retorna o logaritmo de n1 na base n2
MOD()
Retorna o resto de n1 dividido por n2,
PODER(,)
Retorna n1 elevado à potência n2
REDONDO(,)
Retorna o valor de n1 arredondado para n2 casas à direita da vírgula decimal. O valor padrão de n2 é 0. Desta vez, o número inteiro mais próximo da vírgula decimal é arredondado.
Se n2 for um número negativo, será arredondado para o dígito correspondente à esquerda da vírgula decimal. n2 deve ser um número inteiro.
selecione REDONDO(12345,-2), REDONDO(12345.54321,2)
DE dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345,54
SINAL()
Se n for um número negativo, -1 será retornado, se n for um número positivo, 1 será retornado e se n=0, 0 será retornado.
PECADO()
Retorna o valor positivo de n, onde n é radianos.
SINH()
Retorna o valor positivo hiperbólico de n, onde n é radianos.
QUADRADO()
Retorna a raiz quadrada de n, onde n é radianos
TAN()
Retorna a tangente de n, onde n é radianos
TANH()
Retorna a tangente hiperbólica de n, onde n é radianos
TRUNC(,)
Retorna o valor de n1 truncado para n2 casas decimais. A configuração padrão de n2 é 0. Quando n2 for a configuração padrão, n1 será truncado para um número inteiro.
Se n2 for um valor negativo, ele será truncado na posição correspondente à esquerda da vírgula decimal.
Função de data de linha única
A função de data de linha única opera no tipo de dados DATA e a maioria delas possui parâmetros do tipo de dados DATA.
A maioria dos valores retornados também são valores do tipo de dados DATA.
ADD_MONTHS(,＜i＞)
Retorna o resultado da data d mais i meses. eu posso ser qualquer número inteiro. Se i for um decimal,
Em seguida, o banco de dados irá convertê-lo implicitamente em um número inteiro e truncar a parte após o ponto decimal.
ÚLTIMO_DIA()
Função retorna o último dia do mês contendo a data d
MESES_ENTRE(,)
Retorna o número de meses entre d1 e d2, se as datas de d1 e d2 forem iguais ou se ambas forem o último dia do mês,
Então um número inteiro será retornado, caso contrário o resultado retornado conterá uma fração.
NOVO_HORÁRIO(,,)
d1 é um tipo de dados de data. Quando a data e a hora no fuso horário tz1 forem d, retorne a data e a hora no fuso horário tz2.
tz1 e tz2 são strings.
NEXT_DAY(,)
Retorna o primeiro dia seguinte à data d fornecida por dow, que especifica o dia da semana usando o idioma fornecido na sessão atual,
O componente de tempo retornado é igual ao componente de tempo de d.
selecione NEXT_DAY('01-Jan-2000','Segunda-feira') "1ª segunda-feira",
NEXT_DAY('01-Nov-2004','Terça-feira')+7 "2ª terça-feira")
de duplo;
1ª segunda-feira 2ª terça-feira03 de janeiro de 2000 09 de novembro de 2004
REDONDO([,])
Arredonde a data d de acordo com o formato especificado por fmt, que é uma string.
SYADATE
A função não aceita parâmetros e retorna a data e hora atuais.
TRUNC([,])
Retorna a data d nas unidades especificadas por fmt.
Função de conversão de linha única A função de conversão de linha única é usada para operar vários tipos de dados e converter entre tipos de dados.
CHARTORWID()
c cria uma string e a função converte c no tipo de dados RWID.
SELECIONE test_id
de test_case
onde rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
CONVERTER(,[,])
c string final, dset e sset são dois conjuntos de caracteres A função converte a string c do conjunto de caracteres sset para o conjunto de caracteres dset.
A configuração padrão de set é o conjunto de caracteres do banco de dados.
HEXTORAW()
x é uma string hexadecimal e a função converte o x hexadecimal em um tipo de dados RAW.
RAWTOHEX()
x é uma string de tipo de dados RAW e a função converte o tipo de dados RAW em um tipo de dados hexadecimal.
ROWIDTOCHAR()
A função converte o tipo de dados ROWID em tipo de dados CHAR.
TO_CHAR([[,)
x é um tipo de dados de dados ou número. A função converte x em um tipo de dados char no formato especificado por fmt.
Se x for uma data, nlsparm= NLS_DATE_LANGUAGE controla o idioma usado para o mês e dia retornados.
Se x for um número, nlsparm=NLS_NUMERIC_CHARACTERS será usado para especificar separadores decimais e de milhar, bem como símbolos de moeda.
NLS_NUMERIC_CHARACTERS="dg", NLS_CURRENCY="string"
A DATA([,[,)
c representa uma string e fmt representa uma string em um formato especial. Retorna c exibido no formato fmt e nlsparm indica o idioma usado.
A função converte a string c em tipo de dados de data.
TO_MULTI_BYTE()
c representa uma string e a função converte o caractere de truncamento de c em um caractere multibyte.
TO_NUMBER([,[,)
c representa uma string, fmt representa uma string em um formato especial e o valor de retorno da função é exibido no formato especificado por fmt.
nlsparm representa o idioma e a função retornará o número representado por c.
TO_SINGLE_BYTE()
Converta caracteres multibyte na string c em caracteres equivalentes de byte único.
Esta função é usada somente quando o conjunto de caracteres do banco de dados contém caracteres de byte único e de byte múltiplo
Outras funções de linha única
BNOME DO ARQUIVO(,)
dir é um objeto do tipo diretório e arquivo é um nome de arquivo. A função retorna um indicador de valor de posição BFILE vazio,
A função é usada para inicializar variáveis BFILE ou colunas BFILE.
DECODIFICAR(,,[,,,[])
x é uma expressão, m1 é uma expressão correspondente, x é comparado com m1, se m1 for igual a x, então r1 é retornado, caso contrário, x é comparado com m2,
E assim por diante m3, m4, m5.... até que o resultado seja retornado.
JOGAR FORA(,[,[,[,]]])
x é uma expressão ou caractere e fmt representa octal, decimal, hexadecimal ou um único caractere.
A função retorna um valor do tipo VARCHAR2 que contém informações sobre a representação interna de x.
Se n1,n2 forem especificados, serão retornados bytes de comprimento n2 começando em n1.
EMPTY_BLOB()
Esta função não possui parâmetros e retorna um indicador de posição BLOB vazio. Função usada para inicializar uma variável BLOB ou coluna BLOB.
EMPTY_CLOB()
Esta função não possui parâmetros e retorna um indicador de posição CLOB vazio. A função é usada para inicializar uma variável CLOB ou coluna CLOB.
O MELHOR()
exp_list é uma lista de expressões, retornando a maior expressão. Cada expressão é convertida implicitamente no tipo de dados da primeira expressão.
Se a primeira expressão for de qualquer um dos tipos de dados string, o resultado retornado será do tipo de dados varchar2,
A comparação usada ao mesmo tempo é um tipo de comparação sem espaço.
AO MENOS()
exp_list é uma lista de expressões, retornando a menor expressão entre elas. Cada expressão é convertida implicitamente no tipo de dados da primeira expressão.
Se a primeira expressão for de qualquer um dos tipos de dados string, o resultado retornado será do tipo de dados varchar2,
A comparação usada ao mesmo tempo é um tipo de comparação sem espaço.
UID
Esta função não possui parâmetros e retorna um número inteiro que identifica exclusivamente o usuário atual do banco de dados.
USUÁRIO
Retorna o nome de usuário do usuário atual
USERENV()
Com base em opt return contém informações da sessão atual. Os valores opcionais para opt são:
A função SYSDBA responde na sessão ISDBA e retorna TRUE.
SESSIONID retorna o identificador da sessão de auditoria
ENTRYID retorna identificadores de entrada de auditoria disponíveis
INSTANCE Retorna o identificador da instância após a conexão da sessão.
Este valor só será usado se você estiver executando um servidor paralelo e tiver diversas instâncias.
LANGUAGE retorna o conjunto de caracteres de idioma, região e configurações de banco de dados.
LANG retorna a abreviatura ISO do nome do idioma.
TERMINAL Retorna o identificador do sistema operacional do terminal ou computador usado pela sessão atual.
VTAMANHO()
x é uma expressão. Retorna o número de bytes representados internamente por x.
As funções de grupo em SQL também são chamadas de funções agregadas. Elas retornam um único resultado com base em várias linhas.
A menos que a consulta seja executada e todos os resultados sejam incluídos. Ao contrário das funções de linha única, todas as linhas são conhecidas no momento da análise.
Devido a essa diferença, as funções de grupo têm requisitos e comportamentos ligeiramente diferentes das funções de linha única.
Funções de grupo (várias linhas) Em comparação com funções de linha única, o Oracle fornece um rico conjunto de funções de múltiplas linhas baseadas em grupo.
Essas funções podem ser usadas em select ou na cláusula have de select e são frequentemente usadas com GROUP BY quando usadas para selecionar substrings.
AVG([{DISYINCT|TODOS}])
Retorna a média dos valores numéricos. A configuração padrão é TODOS.
SELECIONE AVG(sal),AVG(ALL sal),AVG(DISTINCT sal)
DE scott.empAVG(SAL) AVG(TODOS SAL) AVG(SAL DISTINTO)1877.94118 1877.94118 1916.071413
CONTAR({*|DISTINTO|TODOS} )
Retorna o número de linhas na consulta. A configuração padrão é ALL, * significa retornar todas as linhas.
MAX([{DISTINTO|TODOS}])
Retorna o valor máximo do item da lista de seleção. Se x for um tipo de dados string, ele retornará um tipo de dados VARCHAR2.
Se X for um tipo de dados DATA, retorna uma data, se X for um tipo de dados numérico, retorna um número.
Observe que distintos e todos não têm efeito, o valor máximo deve ser o mesmo para ambas as configurações.
MIN([{DISTINTO|TODOS}])
Retorna o valor mínimo de um item da lista de seleção.
STDDEV([{DISTINTO|TODOS}])
Retorna o desvio padrão dos itens da lista do seletor, onde o desvio padrão é a raiz quadrada da variância.
SOMA([{DISTINTO|TODOS}])
Retorna a soma dos valores numéricos dos itens da lista de seleção.
VARIÂNCIA([{DISTINCT|TODOS}])
Retorna a variação estatística de um item da lista de seleção.
Use GROUP BY para agrupar dados Como o título sugere, a função de grupo opera em dados que foram agrupados.
Dizemos ao banco de dados como agrupar ou classificar dados usando GROUP BY. Quando usamos a função group na cláusula SELECT da instrução SELECT,
Devemos colocar colunas agrupadas ou não constantes na cláusula GROUP BY. Se group by não for usado para processamento especial,
Então a classificação padrão é definir todo o resultado em uma categoria.
selecione estatística, contador (*) zip_count
do estado zip_codes GROUP BY;
ST ZIP_COUNT----------AK 360AL 1212AR 1309AZ 768CA 3982
Neste exemplo, usamos o campo state para classificar; se quisermos classificar os resultados de acordo com zip_codes, podemos usar a instrução ORDER BY,
A cláusula ORDER BY pode usar funções de coluna ou grupo.
selecione estatística, contador (*) zip_count
de CEPs
GROUP BY estado ORDER BY COUNT(*) DESC;
CONTAGEM DE ST(*)----------NY 4312PA 4297TX 4123CA 3982
Limitar dados agrupados com cláusula HAVING
Agora que você sabe como usar a função principal na instrução SELECT e na cláusula ORDER BY de uma consulta, a função de grupo só pode ser usada em duas substrings.
Funções de grupo não podem ser usadas em substrings WHERE. Por exemplo, a seguinte consulta está errada:
erro
SELECIONE vendedor_vendedor,SUN(valor_venda)
DE vendas_brutas
ONDE sales_dept='OUTSIDE' AND SUM(sale_amount)>10000
GRUPO POR vendedor_vendedor
Nesta instrução, o banco de dados não sabe o que é SUM(). Quando precisamos instruir o banco de dados para agrupar linhas e então limitar a saída das linhas agrupadas.
A maneira correta é usar a instrução HAVING:
SELECIONE vendedor_vendedor,SUN(valor_venda)
DE vendas_brutas
ONDE sales_dept='FORA'
GRUPO POR vendedor_vendedor
TENDO SOMA(valor_venda)>10000;
Funções aninhadas As funções podem ser aninhadas. A saída de uma função pode ser a entrada de outra função. Os operandos possuem um processo de execução herdável.
Mas a prioridade das funções baseia-se apenas na posição, e as funções seguem o princípio de dentro para fora e da esquerda para a direita.
A tecnologia de aninhamento é geralmente usada para funções como DECODE, que podem ser usadas em declarações de julgamento lógico IF...THEN...ELSE.