Funciones integradas de PL/SQL

PL/SQL tiene un gran número de funciones incorporadas, sumamente útiles. A continuación vamos a ver algunas de las más utilizadas.

SYSDATE

Devuelve la fecha del sistema:

SELECT SYSDATE FROM DUAL;

NVL

Devuelve el valor recibido como parámetro en el caso de que expresión sea NULL,o expresión en caso contrario.

NVL(<expresion>, <valor>)

El siguiente ejemplo devuelve 0 si el precio es nulo, y el precio cuando está informado:

SELECT CO_PRODUCTO, NVL(PRECIO, 0) FROM PRECIOS;

DECODE

Decode proporciona la funcionalidad de una sentencia de control de flujo if-elseif-else.

DECODE(<expr>, <cond1>, <val1>[, ..., <condN>, <valN>], <default>)

Esta función evalúa una expresión "<expr>", si se cumple la primera condición "<cond1>" devuelve el valor1 "<val1>", en caso contrario evalúa la siguiente condición y así hasta que una de las condiciones se cumpla. Si no se cumple ninguna condición se devuelve el valor por defecto.

Es muy común escribir la función DECODE identada como si se tratase de un bloque IF.

SELECT DECODE (co_pais, /* Expresion a evaluar */
'ESP', 'ESPAÑA', /* Si co_pais = 'ESP' ==> 'ESPAÑA' */
'MEX', 'MEXICO', /* Si co_pais = 'MEX' ==> 'MEXICO' */
'PAIS '||co_pais)/* ELSE ==> concatena */
FROM PAISES;

TO_DATE

Convierte una expresión al tipo fecha. El parámetro opcional formato indica el formato de entrada de la expresión no el de salida.

TO_DATE(<expresion>, [<formato>])

En este ejemplo convertimos la expresion '01/12/2006' de tipo CHAR a una fecha (tipo DATE). Con el parámetro formato le indicamos que la fecha está escrita como día-mes-año para que devuelve el uno de diciembre y no el doce de enero.

SELECT TO_DATE('01/12/2006',
'DD/MM/YYYY')
FROM DUAL;

Este otro ejemplo muestra la conversión con formato de día y hora.

SELECT TO_DATE('31/12/2006 23:59:59',
'DD/MM/YYYY HH24:MI:SS')
FROM DUAL;

TO_CHAR

Convierte una expresión al tipo CHAR. El parámetro opcional formato indica el formato de salida de la expresión.

TO_CHAR(<expresion>, [<formato>])

SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYYY')
FROM DUAL;

TO_NUMBER

Convierte una expresion alfanumérica en numerica. Opcionalmente podemos especificar el formato de salida.

TO_NUMBER(<expresion>, [<formato>])

SELECT TO_NUMBER ('10')
FROM DUAL;

TRUNC

Trunca una fecha o número.

Si el parámetro recibido es una fecha elimina las horas, minutos y segundos de la misma.

SELECT TRUNC(SYSDATE)FROM DUAL;

Si el parámetro es un número devuelve la parte entera.

SELECT TRUNC(9.99)FROM DUAL;

LENGTH

Devuelve la longitud de un tipo CHAR.

SELECT LENGTH('HOLA MUNDO')FROM DUAL;

INSTR

Busca una cadena de caracteres dentro de otra. Devuelve la posicion de la ocurrencia de la cadena buscada.

Su sintaxis es la siguiente:

INSTR(<char>, <search_string>, <startpos>, <occurrence> )

SELECT INSTR('AQUI ES DONDE SE BUSCA', 'BUSCA', 1, 1 )
FROM DUAL;

REPLACE

Reemplaza un texto por otro en un expresion de busqueda.

REPLACE(<expresion>, <busqueda>, <reemplazo>)

El siguiente ejemplo reemplaza la palabra 'HOLA' por 'VAYA' en la cadena 'HOLA MUNDO'.

SELECT REPLACE ('HOLA MUNDO','HOLA', 'VAYA')-- devuelve VAYA MUNDO
FROM DUAL;

SUBSTR

Obtiene una parte de una expresion, desde una posición de inicio hasta una determinada longitud.

SUBSTR(<expresion>, <posicion_ini>, <longitud> )

SELECT SUBSTR('HOLA MUNDO', 6, 5) -- Devuelve MUNDO
FROM DUAL;

UPPER

Convierte una expresion alfanumerica a mayúsculas.

SELECT UPPER('hola mundo') -- Devuelve HOLA MUNDO
FROM DUAL;

LOWER

Convierte una expresion alfanumerica a minúsculas.

SELECT LOWER('HOLA MUNDO') -- Devuelve hola mundo
FROM DUAL;

ROWIDTOCHAR

Convierte un ROWID a tipo caracter.

SELECT ROWIDTOCHAR(ROWID)
FROM DUAL;

RPAD

Añade N veces una determinada cadena de caracteres a la derecha una expresión. Muy util para generar ficheros de texto de ancho fijo.

RPAD(<expresion>, <longitud>, <pad_string> )

El siguiente ejemplo añade puntos a la expresion 'Hola mundo' hasta alcanzar una longitud de 50 caracteres.

SELECT RPAD('Hola Mundo', 50, '.')
FROM DUAL;

LPAD

Añade N veces una determinada cadena de caracteres a la izquierda de una expresión. Muy util para generar ficheros de texto de ancho fijo.

LPAD(<expresion>, <longitud>, <pad_string> )

El siguiente ejemplo añade puntos a la expresion 'Hola mundo' hasta alcanzar una longitud de 50 caracteres.

SELECT LPAD('Hola Mundo', 50, '.')
FROM DUAL;

RTRIM

Elimina los espacios en blanco a la derecha de una expresion

SELECT RTRIM ('Hola Mundo ')
FROM DUAL;

LTRIM

Elimina los espacios en blanco a la izquierda de una expresion

SELECT LTRIM (' Hola Mundo')
FROM DUAL;

TRIM

Elimina los espacios en blanco a la izquierda y derecha de una expresion

SELECT TRIM (' Hola Mundo ')
FROM DUAL;

MOD

Devuelve el resto de la división entera entre dos números.

MOD(<dividendo>, <divisor> )

SELECT MOD(20,15) -- Devuelve el modulo de dividir 20/15
FROM DUAL

Inicio | Tutorial PL/SQL

Versión para imprimir

Foros de consulta

		Mejor resto por Skent Respuesta recibida el [24/05/2007 01:24:58]
		En MOD, yo no diría que devulve el módulo ("Devuelve el modulo de dividir 20/15"). Esta función devuleve el resto.

		MOD devuelve el modulo ... y e... por Devjoker Respuesta recibida el [28/05/2007 06:47:29]
		MOD devuelve el modulo ... y el modulo es el resto de la division entera!

		Devuelve el resto. El uso de m... por skent Respuesta recibida el [06/06/2007 06:30:37]
		Devuelve el resto. El uso de módulo se dice por extensión del nombre de la función (MOD --> Módulo) y no es una manera correcta de llamarlo.

El nombre de módulo no se le d... por Devjoker
Respuesta recibida el [06/06/2007 09:41:59]

El nombre de módulo no se le da por extensión de la función, es la función quien recibe el nombre de la operación matemática módulo, que devuelve el resto de la division entera entre dos números.

Definición de módulo por Hunty
Respuesta recibida el [02/07/2007 07:46:45]

Matemáticamente se define el módulo entre dos números como el resto de dividir el primero entre el segundo. La función MOD de PL/SQL calcula el módulo de los dos números que se le pasan como parámetros, es por esto por lo que recibe ese nombre. Skent, a ver si antes de aseverar de esa forma te coges un buscador y te informas...

		Saben mucho... por efagut Respuesta recibida el [25/07/2007 02:46:51]
		Señores les estan enseñando y ahora quieren parecer todos como que supieran más que el escritor de este buen tutorial?? Cuidado no entendieron...

localizar espacio en un registro por Principiante
Respuesta recibida el [30/07/2007 11:26:46]

Hola a todos, me gustaria saber como localizo un espacio en blanco de un registro en una tabla?... necesito saber esto ya que necesito tomar las iniciales de un campo de la tabla donde esta el nombre completo de personas, es decir no esta separa do por apellido y nombre, todo esta en un campo y necesito sus iniciales solamente, como lo podria hacer?... gracias de antemano y pues yo no soy experto pero quiero serlo algun dia.... gracias...

		No era mas fácil... por garraxxi Respuesta recibida el [25/10/2007 07:21:43]
		Decir que devuelve 5???

		Espacios en un registro por Dark Respuesta recibida el [01/11/2007 02:19:36]
		ya solucionaste este problemilla?

		Gracias Dark por Principiante Respuesta recibida el [29/11/2007 08:08:16]
		No

Campos Date Nulos por Cristobal
Respuesta recibida el [19/12/2007 10:16:25]

Tengo el siguiente problema:

Necesito hacer un sp que pueda extraer información de una tabla que contiene un campo tipo date que permite nulos.

Se requiere que el sp pueda hacer un filtro por ese campo.

El sp deberá seleccionar el registro si el valor de ese campo está entre 2 fechas ( periodo inicio y fin ) que se envían como parámetros.

Si los parámetros recibidos por el sp son nulos, entonces deberá seleccionar todos.

intente con la siguiente condicion pero no satisface todos los requerimientos:

nvl( fecha,sysdate ) between nvl( vd_fec_ini, nvl( fecha_activacion, sysdate ) ) and nvl( vd_fecfin, nvl( fecha_activacion, sysdate ) )

donde:
fecha : campo de la tabla
vd_fec_ini : parametro fecha inicio
vd_fecfin : parámetro fecha fin


Cualquier tip o sugerencia es bienvenido.

Gracias

		RESP. Campos Date Nulos por Cristobal por BartySoft Respuesta recibida el [02/01/2008 09:24:46]
		espero que esto te sirva de algo. Saludos select fecha from tabla c where (c.fecha >= vd_fec_ini or vd_fec_ini is NULL) and (c.fecha <= vd_fec_fin or vd_fec_fin is NULL) order by c.fecha

		(+) por ARIEL Respuesta recibida el [06/02/2008 12:42:35]
		HOLA PARA QUE SIRVE ESTO T.ID(+) = XXX SALUDOS GRACIAS

		Es un outer join , pero las pr... por Devjoker Respuesta recibida el [06/02/2008 01:13:11]
		Es un outer join , pero las preguntas en los foros.

RESP: localizar espacio en un registro por Principiante por Chuky
Respuesta recibida el [15/02/2008 05:42:07]

Mira a ver si te vale esto, se que llego un poco tarde pero por si aun no lo habias solucionado...

select substr(campo,1,1)||substr(campo,instr(campo,' ',1,1)+1,1)||substr(campo,instr(campo,' ',1,2)+1,1)
from tabla

no data found por Danier Marante Jacas
Respuesta recibida el [15/05/2008 09:20:46]

Hola tengo el siguiente problema, tengo una tabla COMUNICACION que tiene un ID numeric(15) y un numero varchar2(20)  y una relación con funcionario de uno a mucho es decir que le comunicación tiene el ID del funcionario, quiero que una consulta como esta.

select comunicacion.nocomunicacion from dcomunicacion 
where dcomunicacion.funcionario = 1 

SI no hay ninguna comunicación relacionada con el funcionario 1 me retorne un valor por defecto y no ORA-01403: no data found. 

Probé de esta forma 
select nvl(comunicacion.nocomunicacion,0) from dcomunicacion 
where dcomunicacion.funcionario = 1 
pero el resultado es el mismo. 

La solución que le doy a esto es hacer un:
select count(comunicacion.nocomunicacion) 
from dcomunicacion 
where dcomunicacion.funcionario = 1 
y liego con la estructura de control IF validar la función pero  esto suele ser un poco costoso en caso de que la data sea un poco grande.  

desde ahora gracias por su ayuda.

		is null por marcbil Respuesta recibida el [27/06/2008 11:30:39]
		prueba el is null

		Mod por DJPucho Respuesta recibida el [29/11/2008 09:28:10]
		Muy buena ayuda para la funcion MOD

		sysobjects en PL/SQL por K-Devil Respuesta recibida el [01/12/2008 06:51:18]
		bueno dias, alguno sabe como pasar y mostrar datos de la tabla sysobjects, como tengo que colocar en el PL/SQL

		obtener mes y año de un campo fecha por Wvilchez Respuesta recibida el [09/02/2009 05:44:54]
		buen dia DJ y posteros, necesito obtener el mes y el año cada uno por separado para poderlos comparar con parametros de entrada en un store, por favor alguno q sepa de alguna funcion gracias

		MOD!!! por Camila Respuesta recibida el [18/02/2009 09:15:26]
		La función MOD devuelve el residuo!!!!!!!

		MOD!! por WTF Respuesta recibida el [19/02/2009 10:24:03]
		q manera de peliar por idioteces... gracias por el tutorial, esta rebueno.

		ayuda con los salarios por Serg Respuesta recibida el [23/03/2009 04:08:12]
		tengo un record dectro del qual hay un salario definido que esta en format 2262000 i tengo que mostrarlo como 2.262.000,00 alguen save una funcion o un truco para poder hacerlo?

		Para WTK por Camila Respuesta recibida el [23/03/2009 11:33:40]
		se dice pelear no "peliar" primero aprende a escribir y luego juzgas por unos simples comentarios

Como obtener el mes de una fecha por Jerry
Respuesta recibida el [08/04/2009 01:17:29]

Hola para obtener el mes de la fecha hice lo siguiente:

SELECT TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE('08-04-2009','DD/MM/YYYY')),4,2)) AS FECHA FROM DUAL;

Donde TO_DATE('08-04-2009','DD/MM/YYYY') puede ser la fecha pero se necesita ese formato para que no me de problemas al tratarlo como cadena y obtener el mes.

		Separador de miles por JAIME Respuesta recibida el [14/04/2009 04:15:46]
		Hola, tengo un valor numerico almacenado como varchar y al mostrarlo quiero agregar los separadores de miles, alguien sabe como hacerlo? bye

		SHUPENLO por YO Respuesta recibida el [29/05/2009 09:20:31]
		SHUPENLO HABLAN PURAS WEAS, ME SIRVIO LA FUNCION....

Ayuda a desifrar consulta por Anonuy
Respuesta recibida el [06/06/2009 05:39:26]

hola soy nuevo en esto en sql y tengo una tarea  saber qeu significa este codigo porfa si alguien me ayuda se lo agradeceria mucho. aki esta el codigo

sELECT b.cod_tprofe,
DECODE(a.IND_CATEGO,'1','C-1','2','C-2','3','C-3','4','C-4','SIN_CAT') CAT,
TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_INGRES,'0000'))),'HH24:MI'),'HH24:MI') HOR_INGR,
TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_EGRESO,'0000'))),'HH24:MI'),'HH24:MI') HOR_EGRE,
((TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_EGRESO,'0000'))),'HH24:MI'),'HH24:MI'),1,2))*60)+
(TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_EGRESO,'0000'))),'HH24:MI'),'HH24:MI'),4,5))))-
((TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_INGRES,'0000'))),'HH24:MI'),'HH24:MI'),1,2))*60)+
(TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_INGRES,'0000'))),'HH24:MI'),'HH24:MI'),4,5)))) TPO_ESP,
TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_PARAME,'0000'))),'HH24:MI'),'HH24:MI') HOR_PARA,
tO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_MEDICO,'0000'))),'HH24:MI'),'HH24:MI') HOR_MED,
((TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_MEDICO,'0000'))),'HH24:MI'),'HH24:MI'),1,2))*60)+
(TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_MEDICO,'0000'))),'HH24:MI'),'HH24:MI'),4,5))))-
((TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_PARAME,'0000'))),'HH24:MI'),'HH24:MI'),1,2))*60)+
(TO_NUMBER(SUBSTR(TO_CHAR(TO_DATE(LTRIM(RTRIM(TO_CHAR(a.HOR_PARAME,'0000'))),'HH24:MI'),'HH24:MI'),4,5)))) TPO_ESP_ENM,
a.FEC_INGRES,
a.FEC_EGRESO,
a.IND_PREVIS Prev
FROM UR_TREGPAC a, gg_tprome b
WHERE a.FEC_inGRES BETWEEN TO_DATE('&Desde','DD-MM-YYYY')
AND TO_DATE ('&Hasta','DD-MM-YYYY')
and a.cod_profes = b.cod_promed
AND a.COD_DIAGNO IS not NULL
and a.ind_estado='V'
ORDER BY a.FEC_EGRESO

		Respuesta a Anonuy por Currante Respuesta recibida el [04/07/2009 03:01:38]
		Significa que no deberias haber aceptado ese trabajo, te queda grande, deja a los profesionales. S2.

		Funcion json por camilocho Respuesta recibida el [08/07/2009 02:08:05]
		Hola como puedo hacer una función para tener una tabla en estructura json.

		currante eres un soplapollas... por peter el listo Respuesta recibida el [15/07/2009 02:36:01]
		currante eres un soplapollas

Funcion MOD Devuelve una diferencia por Carlos
Respuesta recibida el [22/09/2009 02:18:19]

La Función MOD no devuelve ningun resultado de división, solo devuelve la resta del primer con respecto al segundo parámetro, y solamente funciona bien si los dos números tienen el mismo número de dígitos.

Alguien sabe como hacer una división que devuelva todos los decimales, si los hay?
Gracias

		quitar enter por manianna Respuesta recibida el [26/11/2009 11:10:43]
		como puedo eliminar los enter que tenga una exprecion tipo char

obstener fecha minima por miguel
Respuesta recibida el [15/02/2010 03:16:06]

como puedeo obtener un fecha minima que solo me muestre del rango betwwen 01/01/2007 al 31/03/2007, segun listado solo debe mostrarme el 02/01/2007 14:01 19 como lo hago ejm
FCH_MOV	CTD_INI
02/01/2007 14:01:57	19
02/01/2007 16:01:18	18
02/01/2007 19:01:30	17
03/01/2007 11:01:22	16
03/01/2007 16:01:22	20
03/01/2007 16:01:31	19
03/01/2007 16:01:08	18
03/01/2007 18:01:52	17
04/01/2007 15:01:24	16
04/01/2007 17:01:53	15
05/01/2007 14:01:09	13
05/01/2007 17:01:22	12
05/01/2007 17:01:14	11
06/01/2007 17:01:42	10
06/01/2007 17:01:05	8
08/01/2007 16:01:36	6
09/01/2007 16:01:03	5

Contenidos

Foros