FUNCIONES

La clase de funciones esta después de de ver esta lista de funciones propias del PL/SQL.

Funciones integradas de PL/SQL

http://www.devjoker.com/contenidos/Tutorial-PLSQL/67/Funciones-integradas-de-PLSQL.aspx

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 posición 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 MUNDOFROM 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 MUNDOFROM DUAL;

UPPER: Convierte una expresion alfanumerica a mayúsculas.

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

 LOWER: Convierte una expresion alfanumerica a minúsculas.

SELECT LOWER('HOLA MUNDO') -- Devuelve hola mundoFROM 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

 

Creación de funciones

Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.

Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.

Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:- Verificar si el usuario tiene permiso de ejecución.- Verificar la validez del procedimiento o función.- Y finalmente ejecutarlo.

Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:- Facilidad para gestionar la seguridad.- Mejor rendimiento al estar compilados y almacenados en la base de datos.- Mejor gestión de la memoria.- Mayor productividad e integridad.

Sintaxis de un procedimiento PLSQL:

   CREATE OR REPLACE PROCEDURE [esquema].nombre-procedim       (nombre-parámetro {IN, OUT, IN OUT} tipo de dato, ..) {IS, AS}          Declaración de variables;         Declaración de constantes;         Declaración de cursores;         BEGIN            Cuerpo del subprograma PL/SQL;            EXCEPTION            Bloque de excepciones PL/SQL;         END;

Sintaxis de una función PLSQL:

   CREATE OR REPLACE FUNCTION [esquema].nombre-funcion      (nombre-parámetro {IN, OUT, IN OUT} tipo-de-dato, ..)      RETURN tipo-de-dato {IS, AS}          Declaración de variables;         Declaración de constantes;         Declaración de cursores;         BEGIN            Cuerpo del subprograma PL/SQL;            EXCEPTION            Bloque de excepciones PL/SQL;         END;

Aclaraciones sobre la sintaxis:

Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.

IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.

OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.

IN OUT: Son parámetros de entrada y salida a la vez.

Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).

Ejemplos prácticos

Ejemplo de creación de un procedimiento PL/SQL:

   CREATE OR REPLACE PROCEDURE       procedimiento1 (a IN NUMBER, b IN OUT NUMBER) IS         vmax NUMBER;         BEGIN            SELECT salario, maximo             INTO b, vmax            FROM empleados            WHERE empleado_id=a;            IF b < vmax THEN               b:=b+100;            END IF;            EXCEPTION            WHEN NO_DATA_FOUND THEN               b:=-1;               RETURN;            WHEN OTHERS THEN               RAISE;         END;

Ejemplo de SQL script que llama a un procedimiento PLSQL:

   DECLARE      vsalario NUMBER;   BEGIN      procedimiento1 (3213, vsalario)      dbms_output.put_line         ('El salario del empleado 3213 es ', vsalario);   END;

Ejemplo de ejecución desde SQL de un procedimiento PL/SQL que sólo utiliza parámetros de entrada:

   sql> exec proc_solo_parametros_entrada (2000, 2, 'Pepe');

EJEMPLOS PARA CLASES

1. Función para sumar dos números enteros

Creamos la función

Create or Replace FUNCTION SumaAB(a IN Number, B IN Number) RETURN numberAS res number;Begin res := a + b; return res;End SumaAB

Ejecutamos la función

Select SumaAB(2,3) From DUAL;

2. Crear la función para calcular la edad de una persona. Como parámetro de entrada se tiene la fecha de nacimiento.

3. Función que retorna la cantidad de pedidos que realizado un cliente. Como parámetro se envía el número de cliente y como salida se espera la cantidad de pedidos que ha realizado.

Creación de la función

CREATE OR REPLACE FUNCTION nroPedidosCliente (XnroCliente IN NUMBER) RETURN NUMBERIS cantidad NUMBER;BEGIN BEGIN SELECT Count(*) INTO cantidad FROM Solicita S WHERE S.nroCliente = XnroCliente Group BY nroCliente; Return (cantidad); EXCEPTION WHEN OTHERS THEN RETURN 0; END;END nroPedidosCliente;

Ejecución de la función

Select nroPedidosCliente(2103) From DUAL;

4. Obtener por cada producto el mensaje “Solicitar Producto” si la existencia es menor a 40; caso contrario desplegar “Cantidad Suficiente”

Creación de la función

CREATE OR REPLACE FUNCTION AvisoStock(XidProducto IN Varchar2) RETURN Varchar2AS cantidad number; xaviso varchar2(30);BEGIN SELECT Sum(existencia) INTO cantidad FROM Producto P WHERE P.idProducto = XidProducto GROUP BY P.idProducto; If(cantidad < 100) then xaviso:='Solicitar Producto'; Else xaviso:='Cantidad Suficiente';

End If; return (xaviso);END AvisoStock;

Ejecución de la funciónSelect Distinct idProducto,AvisoStock(idProducto) as AvisoFrom ProductoOrder By idProducto

5. Desplegar el monto total a cancelar por pedido

Primera Función a crear:

obtPrecioProducto(XidProducto IN Varchar2, XidFabrica IN Varchar2): Obtiene el precio del producto XidProducto que esta relacionado con la fábrica XidFabrica

obtMonto_por_pedido(XnroPedido IN number): Obtiene el monto total a facturar del XnroPedido

Ejecutar la consulta

Select nropedido, obtMonto_por_Pedido(nropedido),fechaFrom Pedido

SOLUCION

Paso 1 Creamos la función obtPrecioProducto(XidProducto,XidFabrica) esta nos permitirá obtener directamente el precio por producto

Una vez verificada la función que funcione bien nos vamos a crear la siguiente función.

Paso 2 Una vez que tengamos la 1ra función la segunda lo que tiene que realizar es obtener el monto total por pedido, recuerde en la relación CONTIENE están los idproducto, cantidad y los nropedido. Entonces no es mas que multiplicar la 1ra función por la cantidad y todo esto sumarlo.

create or replace function obtPrecioxProducto(idp in varchar2, idf in varchar2)return number as monto number;begin select precio into monto from producto p where idp=p.idproducto and idf=p.idfabrica; return monto;end obtPrecioxProducto;

create or replace function obtMonto(xnropedido in number)return number ismonto number;

beginselect sum(c.cantidad*obtPrecioxProducto(c.idproducto,c.idfabrica))into montofrom contiene cwhere c.nropedido=xnropedidogroup by c.nropedido;return monto;

end;

Y listo ya tenemos ambas funciones ahora nos falta llamar a esta ultima ya que en esta se incluye la 1ra función.

select p.nropedido,obtMonto(p.nropedido) montofrom pedido p

El resultado debería salir como este

6. De la pregunta 5 obtener el o los (nro de pedidos)con costo más alto de todos los pedidos.

Solucion Como ya tenemos las funciones obtCostoXProducto() y obtMonto() tan solo es colocar un max a la ultima función y ya está.

7. Obtenga el monto a pagar de todos los clientes(TOME en cuenta que un cliente puede realizar varios pedidos, y el monto por pedido ya realizaron una función en el anterior ejercicio)

select p.nropedido, obtMonto(p.nropedido) montofrom pedido pwhere obtPrecioXpedido(p.nropedido) = (select max(obtPrecioXpedido(p.nropedido))

from pedido p)

SOLUCION Paso 1 Recuerde que el ejercicio 5 realizaron funciones para obtener el monto por pedido ahora les piden en base a eso realizar el monto a cancelar por cliente, RECUERDE en la relación SOLICITA esta el nrocliente y el nropedido tal vez sea la relación clave con la cual trabajar!!

Y por ultimo deben mostrar el nrocliente y el monto a pagar por cliente

select c.nrocliente,nvl(obtcliente(c.nrocliente),0)monto_pagarfrom cliente c

8. Desplegar el cliente que tiene el menor importe(o el minimo monto a pagar) en sus pedidos

create or replace function obtcliente(nroc in number)return numberas monto_cancelar number;begin select nvl(sum(obtMonto(nropedido)),0) into monto_cancelar from solicita where nrocliente = nroc group by nrocliente; return monto_cancelar;end obtcliente;

SOLUCIONRecuerde que en ejercicio 7 obtuvimos el monto a pagar por cliente ahora nos interesa saber cuál es mínimo monto o importe.

9. Desplegar el jefe que tiene la menor cantidad de empleados a su cargo y ha participado en por los menos 2 pedidos.

SOLUCION

Paso1 Primeramente quienes son los llamados jefes, pues son aquellos que en la tabla EMPLEADO en el atributo nrojefe tienen un valor. Entonces la primera función será encontrar la cantidad de empleados que tienen a su cargo cada jefe.

Paso 2 La pregunta también nos pide que también este nrojefe haya participado en al menos 2 pedidos, entonces esto se lo debe realizar esta en la relación SOLICITA de la cual hay que contar y agrupar y listo!

select distinct(c.nrocliente),obtcliente(c.nrocliente) monto_pagarfrom solicita cwhere obtcliente(c.nrocliente)=(select min(obtcliente(c.nrocliente)) from cliente c)

create or replace function cant_empleado(xnrojefe in number) return number is cant_emp number;begin select count(e.nroempleado)into cant_emp from empleado e where e.nrojefe=xnrojefe group by e.nrojefe; return cant_emp;end;

create or replace function part_jefe(xnrojefe in number)return number isparticipa number;begin select count(*) into participa from solicita s where s.nroempleado = xnrojefe group by s.nroempleado; return participa;end;

Y por último hay que unir ambas funciones y juntarlas en el where y ya esta el ejercicio.

10. Desplegar la cantidad de pedidos por mes y años. La información de las fechas se obtiene de la base de datos

SOLUCION Al parecer se ve complicado realizarlo pero no es asi cuando ya tienes en claro que vas a resultado debes obtener.

PASO 1 Primero obtenemos el reporte por año con la siguiente función.

PASO 2 Ahora debemos obtener el reporte por mes creamos una función muy parecida al del paso 1.

select distinct(nrojefe)

from empleado

where part_jefe(nrojefe)<=2 and cant_empleado(nrojefe)=(select

min(cant_empleado(nroempleado)) from empleado)

create or replace function reporte_año(año in number)return number is cantidad number;beginselect count(p.fecha) into cantidadfrom pedido pwhere extract(year from p.fecha)=añogroup by extract(year from p.fecha);return cantidad;end;

create or replace function reporte_mes(año in number, mes in number)return number is cantidad number;beginselect count(p.fecha) into cantidadfrom pedido pwhere extract(month from p.fecha)=mes and extract(year from p.fecha)=año

Y por último desplegamos el reporte

create or replace function reporte_mes(año in number, mes in number)return number is cantidad number;beginselect count(p.fecha) into cantidadfrom pedido pwhere extract(month from p.fecha)=mes and extract(year from p.fecha)=año

select distinct(extract(year from fecha)) año,to_char(fecha,'month') mes,reporte_mes(extract(year from fecha),extract(month from fecha)) nro_pedidos

from pedido