¿Cuándo debo construir Procedures y Function en Oracle?

Saber cuando se debe desarrollar Procedures en Oracle y Function en Oracle es una decisión muy importante que se debe tomar a la hora de construir un sistema que sea eficiente, modular, ordenado y fácil de mantener.

Decidir si hacer Procedures en Oracle  o  Function en Oracle  te permitirá ordenar tú código fuente para que sea legible y entendible por cualquier persona que lo revise, podrás evitar tener código fuente duplicado lo que permite realizar una fácil mantención de este, podrás reutilizar código fuente en otros procesos lo que te permitirá ahorrar tiempo y trabajo. Todos estos conceptos son muy importantes para construir procesos que puedan ser mantenidos y/o desarrollados por cualquier persona que tenga los conocimientos para construir código en la Base de Datos Oracle. Esto es algo que no puede faltar en un Tutorial de Oracle que se refiera a la construcción de Packages en Oracle.

Revisemos ahora el significado de Procedure y cuando lo debemos utilizar:

Un Procedure (Procedimiento) es el conjunto de etapas, métodos o definiciones paso a paso, que se utilizan para hacer algo o resolver un problema. En la programación estructurada es un subprograma o parte de un programa principal, para nuestro caso se denominan Packages en Oracle. El concepto de Stored Procedure (Procedimiento Almacenado) se debe a que el programa se guarda o almacena en el motor de Base Datos de Oracle, esto permite que cuando el procedimiento sea invocado o llamado este se ejecute directamente en el Servidor de la Base Datos de Oracle logrando así una respuesta mucho más rápida.

Para determinar si debes utilizar Procedures en Oracle tienes que responder de forma afirmativa a las siguientes preguntas:

1. ¿El proceso que debes construir tendrá parámetros de Entrada?                                         2. ¿Necesitas que el proceso te retorne varios parámetros de Salida?

Por ejemplo, si necesitas un proceso que te permita obtener los datos de un empleado en particular, las respuestas a las preguntas 1 y 2 es SI, por lo tanto debes construir Procedures en Oracle como el siguiente:

/* Packages en Oracle */                                                                                                         Procedure Prc_Datos_Empleado(  p_Rut      In Number,                                                                p_Nombre Out Varchar2,                                                                                                                   p_Direccion Out Varchar2,                                                                                                                 p_Edad Out Number,                                                                                                                         p_Id_Departamento Out Number) Is                               Begin                                                                                                                                                             Select emp.nombre, emp.direccion, emp.edad, emp.id_departamento                                      Into p_Nombre, p_Direccion, p_Edad, p_Id_Departamento                                                      From T_Empleados Emp                                                                                                                    Where Emp.Rut = p_Rut;                                                                                                     Exception                                                                                                                                                    When No_Data_Found Then p_Nombre:= ‘Empleado no encontrado’;                                End Prc_Datos_Empleado;

Los Procedures en Oracle siempre deben recibir al menos un parámetro de entrada y deberá tener uno o varios parámetros de salida, pueden haber excepciones donde tal vez no necesites parámetros de salida, pero por lo menos se debe tener un parámetro que indique si el proceso termino correctamente.

TÚ REPORTE: “Cómo Construir un SELECT Eficiente en ORACLE en Solo 7 Pasos”  ¡ Haz Click Aquí y Descárgalo GRATIS Ahora !

Revisemos ahora el significado de Function y cuando la debemos utilizar:

Una Function (Función) es un conjunto de instrucciones que tienen un objetivo en particular y permiten retornar un resultado, pueden ser ejecutadas desde cualquier parte del código. Una Function en Oracle a diferencia de los Procedures en Oracle siempre devuelve un valor como respuesta.

Para determinar si debes utilizar una Function en Oracle tienes que responder de forma afirmativa a las siguientes preguntas:

1. ¿Lo que debo construir será usado de forma masiva por otros procesos?                            2. ¿Necesito obtener siempre un solo resultado?

Por ejemplo, si te das cuenta que en varios procesos que debes desarrollar en tu sistema siempre necesitas obtener la edad de un empleado para poder determinar ciertas acciones, las respuestas a las preguntas 1 y 2 es SI,  por lo tanto debes construir una Function en Oracle como la siguiente:

/* Packages en Oracle */                                                                                                            Function Fnc_Edad_Empleado(p_Rut      In Number) Return Number Is                             l_Edad Number;                                                                                                                           Begin                                                                                                                                                             Select emp.edad Into l_Edad From T_Empleados Emp Where Emp.Rut = p_Rut;                  Return l_Edad;                                                                                                                              Exception                                                                                                                                                    When No_Data_Found Then Return 0;                                                                                End Fnc_Edad_Empleado;

Una Function en Oracle puede recibir uno o varios parámetros de entrada pero siempre retornara un solo valor, en este caso la edad del empleado. Esta Function puede ser utilizada por cualquier proceso que requiera obtener la edad del empleado.

En resumen para poder determinar si se debe utilizar Procedures en Oracle o una Function en Oracle solo se deben responder dos preguntas fáciles, para el caso del Procedure debes preguntarte ¿La cantidad de parámetros de entrada y salida que necesitas obtener?, y para el caso de la Function debes preguntarte si ¿Lo que se necesita será usado masivamente por otros procesos y si solo requieres un valor como resultado?. Practica estas preguntas y conocimientos que aprendiste en tu Base de Datos Oracle.

Estoy muy interesado en conocer tu opinión y/o comentario sobre este artículo, también cuéntame sobre qué es lo que quieres saber de Packages en Oracle y/o dime cuál es tu principal frustración a la hora de trabajar con Packages en Oracle. Estaré muy atento respondiendo tus preguntas y/o comentarios, gracias.

Saludos

Roberto Vicencio – Escritor del Reporte:

Cómo Construir un SELECT Eficiente en ORACLE en 7 Pasos

Esta entrada fue publicada en Function en Oracle, Packages en Oracle y etiquetada , , , . Guarda el enlace permanente.

35 respuestas a ¿Cuándo debo construir Procedures y Function en Oracle?

  1. Alfredo dijo:

    Muy buena explicación, bastante practica y didáctica

  2. raul dijo:

    Buen blog. una pregunta tengo un parametro periodo declarado en mi procedure
    tal valor yo lo ingreso manualmente al ejecutar el procedure
    pero como hago para q me muestre un mensaje q me diga q me obligue a ingresar un valor a ese parametro llamado periodo

    use el exception pero no me muestra el mensaje

    procedure ejemplo(periodo,…,…,.)
    is
    y exception;
    begin
    if periodo is not null then
    …..
    else
    raise y;
    end if;

    EXCEPTION
    when y then
    dbms_output.put_line(‘mensaje’)
    end,

    • Hola Raul, tu procedure esta bien construido y el mensaje se debe mostrar correctamente, yo lo probe en mi base de datos, solo que con dbms_output el mensaje se imprime en la consola por donde estas ejecutando el procedure (ej. toad, sql-developer, sql-plus, etc.), yo solo cambie dbms_output por la función Raise_Application_Error la que permite generar un mensaje de error al usuario indicando que no ha ingresado el Período, el procedure quedo así:

      procedure ejemplo(periodo in number) is
      y exception;
      begin
      if periodo is not null then
      null;
      else
      raise y;
      end if;
      EXCEPTION
      when y then
      —dbms_output.put_line(‘mensaje’);
      Raise_Application_Error(-20501,’Periodo no ingresado’);
      end;
      /

      Espero que este ejemplo te ayude en lo que quieres resolver.

      Saludos
      Roberto Vicencio.

  3. Alvaro Alvarez dijo:

    Q Genial explicacion… empiezo a entender desde aca……
    Q buena explicacion….. ya que es algo que me cuesta entender
    Actualmente debo desarrollar unos cambios en un reporte en developer 6i, y lo que se me ha asignado es colocar al final del reporte la cantidad de documentos que estan anulados y la cantidad de los que no lo estan,,,,, mas alla de no saber q procede si es procedimiento o funcion
    es saber en que momento la base de datos me puede dar una pista de lo que necesito desarrollar…. muchas gracias….

    • Hola Alvaro, gracias por escribir, en cuanto a tu consulta si necesitas obtener la suma de documentos anulados debes crear una función que te entregue el resultado de la suma. En cuanto a la pista que necesitas, la base de datos no te va ha entregar ninguna ya que eres tú el que debes analizar en que lugar del reporte es conveniente realizar la modificación que te han pedido desarrollar.

      Espero haberte ayudado y no dudes en hacer tus consultas.

      Saludos
      Roberto Vicencio.

  4. Alvaro Alvarez dijo:

    de lo anterior lo que debo hacer es evaluar el registro que se encuentra en el campo de la tabla la cual solo acepta 2 valores en dado caso A o N,,, y en el reporte debo hacer el conteo de los Docs. con el estado y hacer la sumas de estos al final…
    Gracias..

    • Hola Alvaro, para solucionar esto puedes crear una función que cuente los datos anulados y no anulados de tu tabla y retornar el resultado, algo como esto:

      function contar(pst_valor in varchar2) return number is
      lin_cantidad
      begin
      select count(1) into lin_cantidad from tu_tabla where tu_campo_anulado = pst_valor;
      return lin_cantidad;
      end contar;

      Espero haberte ayudado.
      Saludos
      Roberto Vicencio.

  5. Leidy dijo:

    Muchas gracias!! a pesar que tenia mucho tiempo trabajando con oracle, hasta ahora es que finalmente entendí bien este tema.

    Excelente tu explicación!

  6. Erlin Garcia dijo:

    Hola!! muy buena tu explicación… te quiero consultar, tengo un reporte que agrupa por tipo de cuenta (ahorro , corriente) necesito un acumulador por cuetna que se inicialice por tipo de cuenta….me explico…. cuenta de ahorro tiene 3 paginas , la primera suma 500 , la 2da suma 100 y la 3ra suma 100… necesito mostrar al final de cada pagina, en la primera 500 , en la segunda 600 (500+100) y en la tercera 700 (600 + 100) ……….. esto es posible?

    • Hola Erlin, en los Packages en Oracle todo es posible, para resolver lo que necesitas puedes hacer algo como lo siguiente:

      — crear un cursor que te entregue los datos para el reporte
      cursor tu_cursor is
      select tipo_cuenta, dato1, dato2, dato3, dato4
      from tus_tablas
      where tus_condiciones
      order by tipo_cuenta;

      — Aqui imprimes la cabecera de la página
      dbms_output.put_line(‘titulo’);
      dbms_output.put_line(‘campo1 campo2 campo3 campo4’);

      — Leer el cursor con un ciclo For
      lst_tipo_cuenta:= null;
      lin_lineas_x_pagina:= 1;
      lin_total_cuenta:= 0;
      for tu_reg in tu_cursor loop
      if lst_tipo_cuenta in not null and lst_tipo_cuenta tu_reg.tipo_cuenta then
      — Se inicia el acumulador solo cuando cambia la cuenta.
      lin_total_cuenta:= 0;
      — Se cambia el valor a 51 para hacer salto de página al cambiar la cuenta.
      lin_lineas_x_pagina:= 51;
      end if;
      — Aqui coloco las lineas a imprimir por página, tu lo debes ajustar.
      if lin_lineas_x_pagina > 50 then
      — Aqui se totaliza por página
      dbms_output.put_line(‘total página: ‘||to_char(lin_total_cuenta));
      — Aqui imprimes la cabecera de la página
      dbms_output.put_line(‘titulo’);
      dbms_output.put_line(‘campo1 campo2 campo3 campo4′);
      — Se inicia el contador de lineas.
      lin_lineas_x_pagina:= 1;
      end if;
      — Aqui imprimes el detalle de la cuenta
      dbms_output.put_line(tu_reg.dato1||’ ‘||tu_reg.dato2||’ ‘||tu_reg.dato3||’ ‘||tu_reg.dato2);
      — Aqui se actializan las variables
      lst_tipo_cuenta:= tu_reg.tipo_cuenta;
      lin_lineas_x_pagina:= lin_lineas_x_pagina + 1;
      lin_total_cuenta:= lin_total_cuenta + tu_reg.monto_cuenta;
      end loop;

      Espero que esta solución te sirva.

      Saludos
      Roberto Vicencio.

  7. Erlin Garcia dijo:

    ah! y gracias……..

  8. Mayra Garcia dijo:

    Excelente explicación🙂

  9. Emiliano dijo:

    Hola, me ha resultado MUY útil tu aporte, estoy trabajando con stored procedure y retornar valores con los mismos, una vez creado y sin errores de compilación, como debería hacer para ejecutarlo? es un procedimiento el cual me interesa ingresar la cédula del alumno y que me devuelva si la materia la tiene baja, como es la sintaxis de ejecución? intente “execute bajas” y si lanza error lógicamente porque no le paso la cédula, pero si pongo execute bajas (123) (existe una cédula 123) también me tira error.

    create or replace procedure bajas(
    cedula in number,
    p_Alumno_Ci out number,
    p_Promedio_Calif out number,
    p_Asignatura_Nom out varchar2)
    is
    begin
    select a.Alumno_Ci, p.Promedio_Calif, p.Asignatura_Nom
    into p_Alumno_Ci, p_Promedio_Calif ,p_Asignatura_Nom
    from Alumno a, Promedio p
    where cedula=p_Alumno_Ci and p_Promedio_Calif<7;
    end bajas;
    /

    • Hola Emiliano, gracias por comunicarte y excelente tu pregunta, para ejecutar un procedimiento almacenado que tiene variables de retorno usa lo siguiente:

      declare
      p_Alumno_Ci number();
      p_Promedio_Calif number();
      p_Asignatura_Nom varchar2();
      begin
      bajas(123,p_Alumno_Ci,p_Promedio_Calif,p_Asignatura_Nom);
      dbms_output.put_line(p_Alumno_Ci);
      dbms_output.put_line(p_Promedio_Calif);
      dbms_output.put_line(p_Asignatura_Nom);
      end;

      PD: Ya obtuviste mi reporte gratuito de ‘Cómo Construir un Select Eficiente en Oracle en Solo 7 Pasos’, si aún no lo has hecho puedes descargarlo desde aquí:
      http://dominatuspackagesenoracle.com/

      Saludos
      Roberto Vicencio – Para Tu Éxito con Oracle.

  10. Kevin Orellana dijo:

    Por gente como tu poco a poco estoy mas cerca de graduarme de mi carrera universitaria… muchas gracias, estoy en esto de PL/SQL con package, stored procedures y functions, si tengo algun trabe espero poder molestarte, muchas gracias de ante mano y que bueno lo que haces por todos, gracias!… saludos desde Guatemala.

    • Hola Kevin, gracias por comunicarte y excelente tu comentario, seguire publicando información de utilidad.

      PD: Pronto voy a dar un Seminario gratuito sobre como construir un Select de múltiples tablas, debes estar atento para que reserves tu cupo, publicaré el día y la hora en el blog y también por email.

      Saludos
      Roberto Vicencio – Para Tu Éxito con Oracle.

  11. Rolando dijo:

    Excelente y muy bien explicado .

    • Hola Rolando, gracias por comunicarte y por tu comentario, me da mucho gusto que el articulo te alla sido util, seguire excribiendo información valiosa para los lectores, gracias.

      PD: Ya obtuviste mi reporte gratuito sobre “Como Construir un SELECT Eficiente en Oracle en Solo 7 Pasos”, si aún no lo has hecho puedes obtenerlo desde aquí:
      http://dominatuspackagesenoracle.com

      Saludos
      Roberto Vicencio – Para Tu Éxito con Oracle.

  12. jhonSmith dijo:

    Muy buenos los conceptos, me gustaron las preguntas para saber diferenciar esto. pues nada a seguir leyendo y gracias por la informacion =)

  13. JoshuaHoom dijo:

    Como poder hacer un procedimiento o función para saber el EAN-13 (Para los que no conocen… Menos yo, es un sistema de códigos de barras), La verdad no se como hacerlo soy muy novato en este tema

    El EAN-13 está compuesto de 13 dígitos; donde los dos primeros representan el código
    del país, los siguientes 5 el código del fabricante, los otros 5 el producto y el último es el
    dígito verificador, este último permite detectar que el código no haya sido modificado. El
    método utilizado para calcular el dígito verificador es el 1-3-1 con módulo 10 el cual se
    determina de la siguiente manera:
    ejemplo: 75 0612941214

    7 5 0 6 1 2 9 4 1 2 1 4 ?

    a) se suman aquellos que ocupan las posiciones impares (7 + 0 + 1 + 9 + 1 +1)
    b) se multiplican los que ocupan posiciones pares por 3 y luego se suman (5*3 + 6*3 + 2*3
    +4*3+2*3 + 4*3)
    c) se suman pares e impares
    d) a la suma se aplica un módulo de 10. Si el número resultante es igual con 0, el 0 será
    el dígito verificador en caso contrario hay que restar el numero resultante al número 10
    (10-X) y finalmente se ha de obtener el dígito verificador

    Nota: cuando decimos pares o impares no se refiere a los números propiamente,
    sino la posición que ocupa dentro de la celda

    a) impares= (7 + 0 + 1 + 9 + 1 +1) = 19
    b) pares = 15 + 18+ 6+12+6+12= 69
    c) 88
    d) 88 % 10 = 8 → (10-8)= 2 (este el dígito verificador)

    • Hola JoshuaHoom, gracias por comunicarte, creo que tienes muy claro el problema que debes resolver, lo que esta muy bien porque así resulta mas fácil construir la función, bueno para resolverlo puedes usar un ciclo for y algunas funciones de Oracle, por ejemplo:

      lst_ean:= ‘750612941214’;
      lin_largo:= lenght(lst_ean);
      lin_suma_pares:= 0;
      lin_suma_impares:= 0;
      for lin_pos in 1..lin_largo loop
      lin_num:= to_number(substr(lst_ean,lin_pos,1));
      if lin_pos in (1,3,5,7,9,11) then — posiciones pares
      lin_suma_pares:= lin_suma_pares + lin_lun;
      else — posiciones impares
      lin_suma_impares:= lin_suma_impares + (lin_num * 3);
      end if;
      end loop;
      lin_resul:= mod(lin_suma_pares+lin_suma_impares,10);
      if lin_resul 0 then
      lin_resul:= (10 – lin_resul);
      end if;
      return (lin_resul);

      Creo que ese es todo el código que deberías usar, pegalo en una función, retorna el resultado, compila y prueba, espero haberte ayudado.

      Saludos
      Roberto Vicencio – Para Tu Éxito con Oracle.

      • JoshuaHoom dijo:

        Hola… Muchas gracias lo implementare y espero aprender mucho más en este tema de las funciones, procedimientos y disparadores en ORACLE y en muchos otros SGBD gracias a tu ayuda y a las demás publicaciones

  14. JoshuaHoom dijo:

    Hola disculpa no sabrás cual es la forma en que se crean los usuarios (Con password, con privilegios de crear una Base de Datos, un usuario que sea valido en una fecha dada o modificada) y como modificarlos a través de la instrucción ALTER

    • Hola JoshuaHoom, para crear un usuario, asignar privilegios y roles debes usar la siguientes instrucciones:

      CREATE USER nombre_usuario IDENTIFIED BY clave_usuario; — crear un usuario
      GRANT CONNECT TO nombre_usuario; –asignar privilegio de conexión
      ALTER USER nombre_usuario DEFAULT ROLE ALL; — asignar todos los roles

      Saludos
      Roberto Vicencio – Para Tu Éxito con Oracle.

  15. JoshuaHoom dijo:

    Vale muchas gracias ya aplique mis usuarios algunos quería ponerlos por ejemplo que sean validos en una fecha especifica pero muchas gracias pase todo muy bien, entendí muchas cosas necesarias con otros tutoriales

  16. elisvan dijo:

    Ola una consulta es bueno usar en el select el llamado a varias funciones???

    • Hola Elisvan, gracias por comunicarte, generalmente no es bueno porque puede afectar el rendimiento de la consulta, pero todo depende de lo que haga la función, porque si esta hace un pequeño calculo que sea rápido esto no afecta, pero si la función realiza una búsqueda en otras tablas esto puede tener un costo muy alto, por eso en un Select hay usar las funciones con precación, gracias.

      Saludos
      Roberto Vicencio – Para Tu Éxito con Oracle.
      http://dominatuspackagesenoracle.com/blog/

  17. veronica dijo:

    HOLA.. excelente informacion se me es mas facil poder compreder las functions.. pero una pregunta como puedo saber las edades de mis usuarios con la fecha de nacimiento que ya esta guardada en la tabla con la fecha actual del ordenador… de antemano Gracias

    • Hola Verónica, gracias por comunicarte y por tu comentario, bueno para saber la edad de tus usuarios puedes hacer algo como lo siguiente:

      — 365= Días del año —
      select ((sysdate – tu_fecha_nac) / 365) edad
      from tu_tabla;

      PD: Ya obtuviste mi libro-reporte sobre “Como Construir un Select Eficiente en Oracle en Solo 7 Pasos”, si aún no lo tienes puedes obtenerlo desde este enlace: http://dominatuspackagesenoracle.com/

      Saludos
      Roberto Vicencio – Para Tu Éxito con Oracle.

  18. arturo dijo:

    excelente explicacion. saludos!

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s