Explicación detallada de las funciones de fila única y de grupo de PL/SQL. Una función es un programa que tiene cero o más parámetros y un valor de retorno. Oracle tiene una serie de funciones integradas en SQL,
Estas funciones pueden denominarse sentencias SQL o PL/SQL. Las funciones se dividen principalmente en dos categorías:
Funciones de grupo de funciones de una sola fila Este artículo analiza cómo utilizar funciones de una sola fila y las reglas para su uso.
Función de fila única en SQL
SQL y PL/SQL vienen con muchos tipos de funciones, incluidas funciones de caracteres, numéricas, de fecha, de conversión y mixtas para procesar filas individuales de datos.
Por lo tanto, estas pueden denominarse colectivamente funciones de una sola fila. Estas funciones se pueden utilizar en SELECT, WHERE, ORDER BY y otras cláusulas,
Por ejemplo, el siguiente ejemplo contiene funciones de una sola línea como TO_CHAR, UPPER y SOUNDEX.
SELECCIONE nombre,TO_CHAR(fecha de contratación,'día,DD-lunes-AAAA')
DE emp
Donde SUPERIOR(nombre) Como 'AL%'ORDER BY SOUNDEX(nombre)
Las funciones de una sola fila también se pueden utilizar en otras declaraciones, como la cláusula SET de actualización, la cláusula VALUES de INSERT y la cláusula WHERE de DELET.
El examen de certificación presta especial atención al uso de estas funciones en la declaración SELECT, por lo que nuestra atención también se centra en la declaración SELECT.
Funciones NULL y de una sola fila Comprender NULL puede resultar difícil al principio, e incluso una persona con mucha experiencia puede confundirse.
El valor NULL representa datos desconocidos o un valor nulo. Cualquier operando de un operador aritmético es un valor NULL y el resultado es un valor NULL.
Esta regla también se aplica a muchas funciones. Solo CONCAT, DECODE, DUMP, NVL y REPLACE pueden devolver valores no NULL cuando se llaman con parámetros NULL.
Entre ellas, la función NVL es la más importante porque puede manejar valores NULL directamente.
NVL tiene dos parámetros: NVL (x1, x2), tanto x1 como x2 son expresiones. Cuando x1 es nulo, se devuelve X2; de lo contrario, se devuelve x1.
Echemos un vistazo a la tabla de datos emp. Contiene salario y bonificación. Necesitamos calcular la compensación total.
nombre de la columna
emp_id tipo de clave de bonificación salarial
pk nulls/unique nn,u nnfk tabla tipo de datos número número longitud del número 11.2 11.2
En lugar de simplemente sumar el salario y la bonificación, si una determinada fila tiene un valor nulo, el resultado será nulo, como en el siguiente ejemplo:
actualizar salario empset=(salario+bonificación)*1.1
En esta declaración, el salario y la bonificación del empleado se actualizarán a un nuevo valor, pero si no hay bonificación,
Es decir, salario + nulo, se sacará una conclusión incorrecta. En este momento, se debe utilizar la función nvl para eliminar la influencia del valor nulo.
Entonces la afirmación correcta es:
actualizar salario empset=(salario+nvl(bonus,0)*1.1
Funciones de cadena de una sola línea Las funciones de cadena de una sola línea se utilizan para manipular datos de cadena. La mayoría de ellas tienen uno o más parámetros y la mayoría devuelve cadenas.
ASCII()
c1 es una cadena, devuelve el código ASCII de la primera letra de c1 y su función inversa es CHR()
SELECCIONE ASCII('A') GRANDE_A,ASCII('z') GRANDE_z
DESDE empBIG_A BIG_z65 122
CHR(<i>)[NCHAR_CS]
i es un número y la función devuelve la representación decimal del carácter
seleccione CHR(65),CHR(122),CHR(223)
DESDE empCHR65 CHR122 CHR223A z B
CONCAT(,)
Tanto c1 como c2 son cadenas. La función conecta c2 con la parte posterior de c1. Si c1 es nulo, se devolverá c2. Si c2 es nulo, se devolverá c1.
Si tanto c1 como c2 son nulos, devuelve nulo. Devuelve el mismo resultado que el operador ||
seleccione concat('slobo ','Svoboda') nombre de usuario
de dualusernameslobo Syoboda
INITCAP()
c1 es una cadena. La función devuelve la primera letra de cada palabra en mayúscula y el resto de letras en minúscula. Las palabras se componen de espacios, caracteres de control,
Restricciones de puntuación.
seleccione INITCAP('veni,vedi,vici') Ceasar
de dualCeasarVeni,Vedi,Vici
INSTR(,[,＜i＞[,]])
c1 y c2 son cadenas, i y j son números enteros. La función devuelve la posición de la j-ésima aparición de c2 en c1, y la búsqueda comienza desde el i-ésimo carácter de c1.
Cuando no se encuentra el carácter requerido, se devuelve 0. Si i es un número negativo, la búsqueda se realizará de derecha a izquierda, pero la posición aún se calcula de izquierda a derecha.
El valor predeterminado para i y j es 1.
SELECCIONAR INSTR('Mississippi','i',3,3)
DESDE dual INSTR('MISSISSIPPI','I',3,3)
11
seleccione INSTR('Mississippi','i',-2,3)
de doble INSTR('MISSISSIPPI','I',3,3)
2
INSTRB(,[,i[,j])
Lo mismo que la función INSTR(), excepto que devuelve bytes. Para un solo byte, INSTRB() es igual a INSTR().
LONGITUD()
c1 es una cadena y se devuelve la longitud de c1. Si c1 es nulo, se devolverá un valor nulo.
seleccione LONGITUD ('Ipso Facto') ergo
de dualergo10
LONGITUDb()
Al igual que LENGTH(), devuelve bytes.
más bajo()
Devuelve el carácter minúsculo de c, que suele aparecer en la subcadena dónde.
seleccione INFERIOR (nombre de color)
desde el detalle del artículo
DONDE LOWER(nombrecolor) COMO '%blanco%'NOMBRECOLORWinterwhite
LPAD(,<i>[,])
c1 y c2 son cadenas e i es un número entero. Utilice la cadena c2 para complementar la longitud i en el lado izquierdo de c1, que se puede repetir varias veces si i es menor que la longitud de c1.
Luego, solo se devolverán los caracteres c1 siempre que i, y los demás se truncarán. El valor predeterminado de c2 es un espacio único, consulte RPAD.
seleccione LPAD(respuesta,7,'') rellenado,respuesta sin relleno
de pregunta;
ACOLCHADO SIN ACOLCHADO Sí SíNO NOTal vez tal vez
LTRIM(,)
Elimine el carácter más a la izquierda en c1 para que el primer carácter no esté en c2. Si no hay c2, entonces c1 no cambiará.
seleccione LTRIM('Mississippi','Mis') de dualLTRppi
RPAD(,<i>[,])
Utilice la cadena c2 para complementar la longitud i en el lado derecho de c1, que se puede repetir varias veces. Si i es menor que la longitud de c1, solo se devolverán los caracteres c1 siempre que i.
Otros quedarán truncados. El valor predeterminado de c2 es un espacio único y los demás son similares a LPAD.
RTRIM(,)
Elimine el carácter más a la derecha en c1 para que el último carácter no esté en c2. Si no hay c2, entonces c1 no cambiará.
REEMPLAZAR(,[,])
c1, c2 y c3 son todas cadenas. La función reemplaza c2 que aparece en c1 con c3 y devuelve.
seleccione REEMPLAZAR ('alta ciudad', 'arriba', 'abajo')
desde dualREPLACEcentro
STBSTR(,<i>[,])
c1 es una cadena, i y j son números enteros y se devuelve una subcadena de longitud j comenzando desde la i-ésima posición de c1, o hasta el final de la cadena si j está vacía.
seleccione SUBSTR('Mensaje',1,4)
de dualSUBSMess
SUBSTRB(,<i>[,])
Es más o menos lo mismo que SUBSTR, excepto que I y J se calculan en bytes.
SONIDO()
Devuelve palabras que suenan similares a c1
seleccione SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson')
de dualDawes Daws DawsonD200 D200 D250
TRADUCIR(,,)
Reemplace los mismos caracteres en c1 que c2 con c3
seleccione la prueba TRADUCIR ('fumble', 'uf', 'ar') de dualTEXTramble
RECORTAR([[]] de c3)
Elimine el primero, el último o ambos en la cadena c3.
seleccione TRIM('espacio acolchado') recorte de doble TRIMespacio acolchado
SUPERIOR()
Devuelve la versión mayúscula de c1, que suele aparecer en la subcadena dónde.
seleccione el nombre de dual donde SUPERIOR (nombre) COMO 'KI%'NAMEKING
Funciones numéricas de una sola fila Las funciones numéricas de una sola fila operan con datos numéricos y realizan operaciones matemáticas y aritméticas. Todas las funciones toman parámetros numéricos y devuelven valores numéricos.
Los operandos y valores de todas las funciones trigonométricas son radianes en lugar de ángulos. Oracle no proporciona una función de conversión incorporada para radianes y ángulos.
ABS()
Devuelve el valor absoluto de n
ACOS()
La función cofactor inversa devuelve un número entre -1 y 1. n representa radianes
seleccione ACOS(-1) pi,ACOS(1) CERO
DESDE dualPI ZERO3.14159265 0
ASIN()
De todos modos, la misteriosa función devuelve -1 a 1, n representa radianes
ATAN()
Función arcotangente, devuelve el valor arcotangente de n, donde n representa radianes.
FORTIFICAR TECHO()
Devuelve el entero más pequeño mayor o igual que n.
porque()
Devuelve el covalor de n, donde n es radianes
APORREAR()
Devuelve el cofactor hiperbólico de n, donde n es un número.
seleccione COSH(<1.4>)
DESDE dualCOSH(1.4)2.15089847
EXP()
Devuelve la enésima potencia de e, e=2,71828183.
PISO()
Devuelve el mayor entero menor o igual a N.
LN()
Devuelve el logaritmo natural de N, que debe ser mayor que 0
REGISTRO(,)
Devuelve el logaritmo de n1 a base n2
MODO()
Devuelve el resto de n1 dividido por n2,
FUERZA(,)
Devuelve n1 elevado a la potencia de n2
REDONDO(,)
Devuelve el valor de n1 redondeado a n2 lugares a la derecha del punto decimal. El valor predeterminado de n2 es 0. Esta vez, se redondea el entero más cercano al punto decimal.
Si n2 es un número negativo, se redondea al dígito correspondiente a la izquierda del punto decimal. n2 debe ser un número entero.
seleccione REDONDEAR(12345,-2),REDONDEAR(12345.54321,2)
DESDE dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54
FIRMAR()
Si n es un número negativo, se devuelve -1, si n es un número positivo, se devuelve 1 y si n=0, se devuelve 0.
PECADO()
Devuelve el valor positivo de n, donde n es radianes.
SINH()
Devuelve el valor positivo hiperbólico de n, donde n es radianes.
CUADRADO()
Devuelve la raíz cuadrada de n, donde n es radianes
BRONCEARSE()
Devuelve la tangente de n, donde n es radianes
tanh()
Devuelve la tangente hiperbólica de n, donde n es radianes
TRONCO(,)
Devuelve el valor de n1 truncado a n2 decimales. La configuración predeterminada de n2 es 0. Cuando n2 es la configuración predeterminada, n1 se truncará a un número entero.
Si n2 es un valor negativo, se trunca en la posición correspondiente a la izquierda del punto decimal.
Función de fecha de una sola fila
La función de fecha de una sola línea opera en el tipo de datos DATOS y la mayoría de ellas tienen parámetros del tipo de datos DATOS.
La mayoría de los valores devueltos también son valores de tipo de datos DATOS.
ADD_MONTHS(,＜i＞)
Devuelve el resultado de la fecha d más i meses. Puedo ser cualquier número entero. Si i es un decimal,
Luego, la base de datos lo convertirá implícitamente en un número entero y truncará la parte después del punto decimal.
ÚLTIMO_DÍA()
La función devuelve el último día del mes que contiene la fecha d
MESES_ENTRE(,)
Devuelve el número de meses entre d1 y d2, si las fechas de d1 y d2 son iguales, o ambas son el último día del mes,
Luego se devolverá un número entero; de lo contrario, el resultado devuelto contendrá una fracción.
NUEVA_HORA(,,)
d1 es un tipo de datos de fecha Cuando la fecha y la hora en la zona horaria tz1 son d, devuelve la fecha y la hora en la zona horaria tz2.
tz1 y tz2 son cadenas.
SIGUIENTE_DÍA(,)
Devuelve el primer día siguiente a la fecha d dada por dow, que especifica el día de la semana usando el idioma proporcionado en la sesión actual.
El componente de tiempo devuelto es el mismo que el componente de tiempo de d.
seleccione NEXT_DAY('01-Ene-2000','Lunes') "1er lunes",
NEXT_DAY('01-Nov-2004','Martes')+7 "segundo martes")
de dual;
1er lunes 2do martes03-ene-2000 09-nov-2004
REDONDO([,])
Redondea la fecha d según el formato especificado por fmt, que es una cadena.
SYADATO
La función no toma parámetros y devuelve la fecha y hora actuales.
TRUNC([,])
Devuelve la fecha d en las unidades especificadas por fmt.
Función de conversión de una sola fila La función de conversión de una sola fila se utiliza para operar múltiples tipos de datos y convertir entre tipos de datos.
GRÁFICOWID()
c crea una cadena y la función convierte c al tipo de datos RWID.
SELECCIONE test_id
de test_case
donde ID de fila = CHARTORWID ('AAAA0SAACAAAALiAAA')
CONVERTIR(,[,])
c tail string, dset y sset son dos juegos de caracteres. La función convierte la cadena c del juego de caracteres sset al juego de caracteres dset.
La configuración predeterminada de sset es el juego de caracteres de la base de datos.
HEXTORA()
x es una cadena hexadecimal y la función convierte la x hexadecimal en un tipo de datos RAW.
RAWTOHEX()
x es una cadena de tipo de datos RAW y la función convierte el tipo de datos RAW en un tipo de datos hexadecimal.
ROWIDTOCHAR()
La función convierte el tipo de datos ROWID al tipo de datos CHAR.
TO_CHAR([[,)
x es un tipo de datos numérico o de datos. La función convierte x en un tipo de datos char en el formato especificado por fmt.
Si x es una fecha, nlsparm= NLS_DATE_LANGUAGE controla el idioma utilizado para el mes y el día devuelto.
Si x es un número, nlsparm=NLS_NUMERIC_CHARACTERS se utiliza para especificar separadores decimales y de miles, así como símbolos de moneda.
NLS_NUMERIC_CHARACTERS="dg", NLS_CURRENCY="cadena"
HASTA LA FECHA([,[,)
c representa una cadena y fmt representa una cadena en un formato especial. Devuelve c mostrado en formato fmt y nlsparm indica el idioma utilizado.
La función convierte la cadena c en un tipo de datos de fecha.
TO_MULTI_BYTE()
c representa una cadena y la función convierte el carácter de truncamiento de c en un carácter de varios bytes.
TO_NUMBER([,[,)
c representa una cadena, fmt representa una cadena en un formato especial y el valor de retorno de la función se muestra en el formato especificado por fmt.
nlsparm representa el idioma y la función devolverá el número representado por c.
TO_SINGLE_BYTE()
Convierta caracteres de varios bytes en la cadena c en caracteres equivalentes de un solo byte.
Esta función se utiliza sólo cuando el juego de caracteres de la base de datos contiene caracteres de un solo byte y de varios bytes.
Otras funciones de una sola línea
BNOMBRE DE ARCHIVO(,)
dir es un objeto de tipo directorio y file es un nombre de archivo. La función devuelve un indicador de valor de posición BFILE vacío,
La función se utiliza para inicializar variables BFILE o columnas BFILE.
DESCODIFICAR(,,[,,,[])
x es una expresión, m1 es una expresión coincidente, x se compara con m1, si m1 es igual a x, entonces se devuelve r1; de lo contrario, x se compara con m2,
Y así sucesivamente m3, m4, m5.... hasta que se devuelva el resultado.
VERTEDERO(,[,[,[,]]])
x es una expresión o carácter y fmt representa octal, decimal, hexadecimal o un solo carácter.
La función devuelve un valor de tipo VARCHAR2 que contiene información sobre la representación interna de x.
Si se especifican n1, n2, se devolverán bytes de longitud n2 a partir de n1.
VACÍO_BLOB()
Esta función no tiene parámetros y devuelve un indicador de posición BLOB vacío. Función utilizada para inicializar una variable BLOB o una columna BLOB.
CLOB_VACÍO()
Esta función no tiene parámetros y devuelve un indicador de posición CLOB vacío. La función se utiliza para inicializar una variable CLOB o una columna CLOB.
MAYOR()
exp_list es una lista de expresiones que devuelve la expresión más grande. Cada expresión se convierte implícitamente al tipo de datos de la primera expresión.
Si la primera expresión es de cualquiera de los tipos de datos de cadena, entonces el resultado devuelto es del tipo de datos varchar2,
La comparación utilizada al mismo tiempo es un tipo de comparación que no ocupa espacios.
EL MENOS()
exp_list es una lista de expresiones que devuelve la expresión más pequeña entre ellas. Cada expresión se convierte implícitamente al tipo de datos de la primera expresión.
Si la primera expresión es de cualquiera de los tipos de datos de cadena, el resultado devuelto es del tipo de datos varchar2,
La comparación utilizada al mismo tiempo es un tipo de comparación que no ocupa espacios.
UID
Esta función no tiene parámetros y devuelve un número entero que identifica de forma única al usuario actual de la base de datos.
USUARIO
Devuelve el nombre de usuario del usuario actual.
USUARIOV()
Basado en opt return contiene información de la sesión actual. Los valores opcionales para optar son:
El rol SYSDBA responde en la sesión ISDBA y devuelve VERDADERO.
SESSIONID devuelve el identificador de la sesión de auditoría.
ENTRYID devuelve identificadores de entrada de auditoría disponibles
INSTANCIA Devuelve el identificador de instancia después de que se conecta la sesión.
Este valor solo se usa si está ejecutando un servidor paralelo y tiene varias instancias.
IDIOMA devuelve el juego de caracteres de idioma, región y configuración de la base de datos.
LANG devuelve la abreviatura ISO del nombre del idioma.
TERMINAL Devuelve el identificador del sistema operativo para el terminal o computadora utilizada por la sesión actual.
Tamaño VS()
x es una expresión. Devuelve el número de bytes representados internamente por x.
Las funciones de grupo en SQL también se denominan funciones agregadas. Devuelven un resultado único basado en varias filas. No se puede determinar el número exacto de filas.
A menos que se ejecute la consulta y se incluyan todos los resultados. A diferencia de las funciones de una sola fila, todas las filas se conocen en el momento del análisis.
Debido a esta diferencia, las funciones de grupo tienen requisitos y comportamiento ligeramente diferentes a los de las funciones de una sola fila.
Funciones de grupo (varias filas) En comparación con las funciones de una sola fila, Oracle proporciona un amplio conjunto de funciones de varias filas basadas en grupos.
Estas funciones se pueden usar en select o en la cláusula have de select, y a menudo se usan con GROUP BY cuando se usan para seleccionar subcadenas.
PROMEDIO([{DISYINCT|TODOS}])
Devuelve el promedio de valores numéricos. La configuración predeterminada es TODO.
SELECCIONAR PROMEDIO(sal),PROMEDIO(TODO sal),PROMEDIO(sal DISTINTO)
DESDE scott.empAVG(SAL) PROMEDIO(TODO SAL) PROMEDIO(SAL DISTINTO)1877.94118 1877.94118 1916.071413
CONTAR({*|DISTINCT|TODOS} )
Devuelve el número de filas de la consulta. La configuración predeterminada es TODAS, * significa devolver todas las filas.
MAX([{DISTINCT|TODOS}])
Devuelve el valor máximo del elemento de la lista de selección. Si x es un tipo de datos de cadena, devuelve un tipo de datos VARCHAR2.
Si X es un tipo de datos DATOS, devuelve una fecha, si X es un tipo de datos numérico, devuelve un número.
Tenga en cuenta que distintos y todos no tienen ningún efecto; el valor máximo debe ser el mismo para ambas configuraciones.
MIN([{DISTINCT|TODOS}])
Devuelve el valor mínimo de un elemento de la lista de selección.
STDDEV([{DISTINCT|TODOS}])
Devuelve la desviación estándar de los elementos de la lista del selector, donde la desviación estándar es la raíz cuadrada de la varianza.
SUMA([{DISTINCT|TODOS}])
Devuelve la suma de los valores numéricos de los elementos de la lista de selección.
VARIANZA([{DISTINCT|ALL}])
Devuelve la varianza estadística de un elemento de la lista de selección.
Utilice GROUP BY para agrupar datos. Como sugiere el título, la función de grupo opera con datos que han sido agrupados.
Le decimos a la base de datos cómo agrupar o clasificar datos usando GROUP BY. Cuando usamos la función de grupo en la cláusula SELECT de la declaración SELECT,
Debemos colocar columnas agrupadas o no constantes en la cláusula GROUP BY. Si group by no se utiliza para procesamiento especial,
Entonces, la clasificación predeterminada es establecer todo el resultado en una categoría.
seleccione estadística, contador (*) zip_count
de códigos postales GRUPO POR estado;
ST ZIP_COUNTER----------AK 360AL 1212AR 1309AZ 768CA 3982
En este ejemplo, usamos el campo de estado para clasificar, si queremos ordenar los resultados según códigos postales, podemos usar la instrucción ORDER BY;
La cláusula ORDER BY puede utilizar funciones de columna o de grupo.
seleccione estadística, contador (*) zip_count
de códigos postales
GRUPO POR estado ORDEN POR CONTEO(*) DESC;
RECUENTO DE ST(*)----------NY 4312PA 4297TX 4123CA 3982
Limitar datos agrupados con cláusula HAVING
Ahora que sabe cómo usar la función principal en la instrucción SELECT y la cláusula ORDER BY de una consulta, la función de grupo solo se puede usar en dos subcadenas.
Las funciones de grupo no se pueden utilizar en subcadenas WHERE. Por ejemplo, la siguiente consulta es incorrecta:
error
SELECCIONE vendedor_empleado, SOL(monto_venta)
DE ventas_brutas
DONDE sales_dept='FUERA' Y SUM(monto_venta)>10000
GRUPO POR vendedor_empleado
En esta declaración, la base de datos no sabe qué es SUM() Cuando necesitamos indicarle a la base de datos que agrupe filas y luego limite la salida de las filas agrupadas,
La forma correcta es utilizar la declaración HAVING:
SELECCIONE vendedor_empleado, SOL(monto_venta)
DE ventas_brutas
DONDE sales_dept='FUERA'
GRUPO POR vendedor_empleado
TENER SUMA(monto_venta)>10000;
Funciones anidadas Las funciones se pueden anidar. La salida de una función puede ser la entrada de otra función. Los operandos tienen un proceso de ejecución heredable.
Pero la prioridad de las funciones se basa únicamente en la posición, y las funciones siguen el principio de adentro hacia afuera y de izquierda a derecha.
La tecnología de anidamiento se usa generalmente para funciones como DECODIFICAR que se pueden usar en declaraciones de juicio lógico SI... ENTONCES... ELSE.