Viernes, 12 Junio 2020 02:26

Procedimientos y Funciones

Rate this item
(0 votes)

Objetivos:

  • Diferenciar entre bloques anónimos y subprogramas.
  • Crear un procedimiento e invocarlo desde un bloque anónimo.
  • Crear funciones/procedimientos con y sin parámetros.
  • Diferenciar entre procedimientos y funciones.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.


SubProgramas

Como su nombre indica, los bloques anónimos son bloques ejecutables de PL/SQL que no tienen nombre. Debido a esto, no pueden ser reutilizados ni almacenados para un posterior uso.

Los procedimientos y funciones son bloques PL/SQL también conocidos como subprogramas. Estos subprogramas son compilados y almacenados en la Base de Datos. La estructura de los subprogramas es similar a la estructura de los bloques anónimos. Los subprogramas se pueden declarar tanto a nivel de esquema como también dentro de cualquier otro bloque PL/SQL (es decir pueden estar dentro de otro subprograma: función, procedimiento, paquete, trigger, etc.). Un subprograma contiene las siguientes secciones:

  • Parte declarativa: Los subprogramas pueden tener una sección declarativa opcional. Sin embargo, a diferencia de bloques anónimos, la parte declarativa de un subprograma no inicia con la palabra clave DECLARE. La sección declarativa(opcional) sigue la palabra clave IS AS en la declaración del subprograma.
  • Sección ejecutable: Esta es la parte obligatoria del subprograma, la cual contiene la aplicación de la lógica de negocio. Si examina el código en esta sección, puede determinar fácilmente la funcionalidad de negocio del subprograma. Esta sección comienza y termina con las palabras clave BEGIN END, respectivamente.
  • Sección EXCEPTION: Esta es una sección opcional que se incluye para manejar excepciones.

Diferencias entre Los Bloques Anónimos y los SubProgramas

Bloques Anónimos

Subprograms

Bloques de PL/SQL sin nombre.

Bloques de PL/SQL con nombre.

Compilados por cada corrida.

Compilados una sola vez.

No se almacenan en la Base de Datos.

Almacenados en la Base de Datos.

No pueden ser invocados por otras aplicaciones.

Gracias al nombre, pueden ser invocados por otras aplicaciones.

No retornan Valores.

En el caso de las funciones, deben retornar un valor; adicionalmente pueden(ambos) retornar parámetros de salida.

No reciben parámetros.

Pueden recibir parámetros.

 

La tabla no sólo muestra las diferencias entre los bloques anónimos y subprogramas, sino que también puntualiza las ventajas generales de los subprogramas.

Los bloques anónimos no son objetos de Base de Datos persistentes. Ellos se compilan cada vez que son ejecutados. No se almacenan en la Base de Datos para su reutilización. Si desea volver a utilizarlos, debe volver a ejecutar la secuencia de comandos que crea el bloque anónimo, lo cual causa recompilación y ejecución.

Los Procedimientos y funciones son compilados y almacenados en la Base de Datos. Estos son recompilados sólo cuando se modifican. Ya que se almacenan en la Base de Datos, cualquier aplicación  con  los permisos apropiados puede hacer uso de estos subprogramas. A su vez, la aplicación puede pasar parámetros a los subprogramas si estos están diseñados para aceptar parámetros. Del mismo modo, una aplicación puede recuperar datos al invocar una función o procedimiento.


Procedimientos

Sintaxis:

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name
   [ (argument1 [ mode1 ]
               [ NOCOPY ]
               datatype [ DEFAULT expr ]
       [, argument2 [ mode2 ]
                   [ NOCOPY ]
                   datatype [ DEFAULT expr ]
       ]...
     )
   ]
   [ invoker_rights_clause ]
   { IS | AS }
   { pl/sql_subprogram_body};

 

En la sintaxis:

procedure_name

Es el nombre del procedimiento a ser creado.

OR REPLACE

Especifica OR REPLACE para volver a crear el procedimiento si ya existe. Utilice esta cláusula para cambiar la definición de un procedimiento existente sin tener que eliminarlo, re-crearlo, ni otorgar los privilegios de objeto que ya poseía. Si se re-define un procedimiento, entonces la Base de Datos Oracle lo recompila.

argument

Es el nombre dado a los parámetros del procedimiento. Cada argumento está asociado con un modo y tipo de datos. Un procedimiento puede tener cualquier número de argumentos separados por comas.

mode

Tipo de argumento:

IN (default)  -- De entrada.

OUT -- De salida.

IN OUT -- De entrada y salida

datatype              

Es el tipo de dato del parámetro. El tipo de dato de los parámetros no puede tener el tamaño explícito; en cambio, utilizar %TYPE.

NOCOPY

Especifica NOCOPY para pedir a la Base de Datos que pase este argumento tan rápido como sea posible. Esta cláusula puede mejorar significativamente el rendimiento cuando se pasa un valor grande (un Record,  index-by table, o un VARRAY) a un parámetro OUT IN-OUT . Los valores de los parámetros IN se pasan siempre como NOCOPY.

invoker_rights_clause 

El invoker_rights_clause le permite especificar si el procedimiento se ejecuta con los privilegios y en el esquema del usuario que lo posee (owner) o con los privilegios y en el esquema del usuario que lo invoca.

Esta cláusula también determina la forma que la Base de Datos resuelve los nombres externos en consultas, operaciones DML, y sentencias de SQL dinámico en el procedimiento.

EjemploAUTHID CURRENT_USER DEFINER }

pl/sql_subprogram_body

Es el bloque de PL/SQL que ejecuta la acción.

 

Ejemplos:

CREATE OR REPLACE PROCEDURE pro_simple	IS
      v_number      NUMBER;
      v_varchar2    VARCHAR2(10);
BEGIN
      v_number  := 16;
      v_varchar2 := 'Hola Mundo';

      DBMS_OUTPUT.PUT_LINE(v_varchar2||', tengo '||v_number||' años');
END	pro_simple;

/*Este ejemplo Simple muestra como crear un procedimiento.*/

 

SET SERVEROUTPUT ON

BEGIN
      pro_simple;
END;

/*Ejecutamos el procedimiento en un bloque anónimo.*/

---OUTPUT:

 

CREATE OR REPLACE PROCEDURE p_sal_increase
                                  (
                                     p_dept       IN departments.department_id%TYPE,
                                     p_job        IN jobs.job_id%TYPE,
                                     p_por_incr   IN NUMBER  
                                  )    AUTHID  CURRENT_USER IS
     v_por_increase NUMBER (3,2);
     v_dept_job  CHAR(1);

     CURSOR c_dept_job IS
      SELECT 'A'
      FROM employees
      WHERE department_id = p_dept
      AND job_id = p_job;
BEGIN
     v_por_increase := p_por_incr/100;

     IF v_por_increase > 2 THEN
      RAISE_APPLICATION_ERROR(-20001, 'El Aumento no puede ser mayor del 200%');
     END IF;

     OPEN c_dept_job;
     FETCH c_dept_job INTO v_dept_job;
     CLOSE c_dept_job;

     IF  v_dept_job IS NOT NULL THEN
        UPDATE employees
        SET salary = salary+(salary*v_por_increase)
        WHERE department_id = p_dept
        AND job_id = p_job;
     ELSE
      RAISE_APPLICATION_ERROR(-20002, 'Departamento o Tipo de empleo invalido, Favor Validar la información.');
     END IF;
END;
/

SHOW ERROR

/*El procedimiento recibe como parámetros: el numero de departamento, el tipo de empleo y un valor en términos porcentuales; El porciento recibido equivale al aumento que se le va aplicar a los empleados con el departamento y empleo recibido. Debido a que especifiqué: AUTHID CURRENT_USER, el usuario a ejecutar el proceso debe tener los privilegios necesarios para hacerlo.*/

 

Antes de ejecutar el siguiente SCRIPT es recomendable consultar la tabla employees para notar el salario actual de los empleados a actualizar.

BEGIN
      p_sal_increase
                    (
                      50,
                      'ST_MAN',
                      10
                    );
END;

/*Ejecutamos el Procedure y luego consultamos la tabla una vez mas.*/

---OUTPUT:


Funciones

Sintaxis:

CREATE [ OR REPLACE ] FUNCTION [ schema. ]function_name
  [ (argument1 [ mode1 ]
     [ NOCOPY ] datatype
       [, argument2 [ mode2 ]
          [ NOCOPY ] datatype
       ]...
    )
  ]
  RETURN datatype
  [ { invoker_rights_clause
    | DETERMINISTIC
    | parallel_enable_clause
    }
      [ invoker_rights_clause
      | DETERMINISTIC
      | parallel_enable_clause
      ]...
  ]
  { { AGGREGATE | PIPELINED }
    USING [ schema. ]implementation_type
  | [ PIPELINED]
    { IS | AS }
    { pl/sql_function_body }
  } ;

En la sintaxis:

function_name

Es el nombre de la función a ser creada.

OR REPLACE

Especifica OR REPLACE para volver a crear la función si ya existe. Utilice esta cláusula para cambiar la definición de una función existente sin tener que eliminarla, re-crearla, ni otorgar los privilegios de objeto que ya poseía. Si se re-define una función, entonces la Base de Datos Oracle la recompila.

argument

Es el nombre dado a los parámetros de la función. Cada argumento está asociado con un modo y tipo de datos. Una función puede tener cualquier número de argumentos   separados por comas.

mode

Tipo de argumento:

IN (default)  -- De entrada.

OUT -- De salida.

IN OUT -- De entrada y salida

datatype              

Es el tipo de dato del parámetro. El tipo de dato de los parámetros no puede tener el tamaño explícito; en cambio, utilizar %TYPE.

NOCOPY

Especifica NOCOPY para pedir a la Base de Datos que pase este argumento tan rápido como sea posible. Esta cláusula puede mejorar significativamente el rendimiento cuando se pasa un valor grande (un Record,  index-by table, o un VARRAY) a un parámetro OUT IN-OUT . Los valores de los parámetros IN se pasan siempre como NOCOPY.

RETURN

Es el tipo de dato del valor de retorno de la función. Esta cláusula es requerida debido a que cada función debe devolver un valor. El valor de retorno puede tener cualquier tipo de datos PL/SQL excepto booleano(BOOLEAN).

invoker_rights_clause 

El invoker_rights_clause le permite especificar si la función se ejecuta con los privilegios y en el esquema del usuario que lo posee (owner) o con los privilegios y en el esquema del usuario que lo invoca.

Esta cláusula también determina la forma que la Base de Datos resuelve los nombres externos en consultas, operaciones DML, y sentencias de SQL dinámico en la función.

EjemploAUTHID CURRENT_USER DEFINER }

DETERMINISTIC

Especificar DETERMINISTIC para indicar que la función devuelve el mismo valor de resultado cada vez que se llama con los mismos valores en sus argumentos.

Debe especificar esta palabra clave si su intención es de llamar a la función en la expresión de un índice basado en funciones o de una consulta de una vista materializada marcada como REFRESH FAST o ENABLE QUERY REWRITE.

PARALLEL_ENABLE

Es un indicio de optimización que indica que la función se puede ejecutar desde un servidor en paralelo de una operación de consulta en paralelo. La función no debe utilizar el estado de sesión, como variables de paquete, ya que dichas variables no son necesariamente compartidas entre los servidores de ejecución en paralelo.

PIPELINE

Especificar PIPELINE para instruir a Oracle que retorne los resultados de una función de tabla de forma iterativa. Una función de tabla devuelve un tipo de colección (una tabla anidada o VARRAY). Se consulta la tabla de funciones mediante el uso de la palabra clave TABLE antes de que el nombre de la función en la cláusula FROM de la consulta.

AGGREGATE USING

Especificar AGGREGATE USING para identificar esta función como una función de agregado, o una que evalúa un grupo de filas y devuelve una sola fila. Puede especificar las funciones de agregado en la lista select, cláusula HAVING, y la cláusula ORDER BY.

pl/sql_subprogram_body

Es el bloque de PL/SQL que ejecuta la acción.

Ejemplos:

CREATE OR REPLACE FUNCTION simple_function RETURN VARCHAR2 IS
BEGIN
RETURN 'Ejemplo de una Función Simple.';
END;

/*Este ejemplo muestra como crear un simple función en Oracle.*/

 

SELECT simple_function
FROM dual;

/*En este SELECT le damos uso a nuestra funcion.*/

---OUTPUT:

 

CREATE OR REPLACE FUNCTION low_high_salary
                                        (
                                          p_dept      IN  departments.department_id%TYPE,
                                          p_job_id    IN  jobs.job_id%TYPE,
                                          p_sal       IN  CHAR  DEFAULT 'B'    -- L: Mas Bajo; H: Mas Alto, B: Ambos
                                        )  RETURN VARCHAR2	IS
    CURSOR  c_high_low  IS
        SELECT
                MIN(salary) AS bajo,
                MAX(salary) AS alto
        FROM employees
        WHERE department_id = p_dept
        AND job_id =  p_job_id;

    v_high_low_rec    c_high_low%ROWTYPE;

    e_wrong_entry     EXCEPTION;
BEGIN
    OPEN c_high_low;
    FETCH c_high_low INTO v_high_low_rec;
    CLOSE c_high_low;

    IF v_high_low_rec.bajo IS NULL THEN
        RAISE e_wrong_entry;
    END IF;

    CASE
      WHEN UPPER(p_sal) = 'B' THEN
          RETURN 'Salario Mas Alto: '||v_high_low_rec.alto||
                 ', Salario Mas Bajo: '||v_high_low_rec.bajo;
      WHEN UPPER(p_sal) = 'L' THEN
          RETURN 'Salario Mas Bajo: '||v_high_low_rec.bajo;
      WHEN UPPER(p_sal) = 'H' THEN
          RETURN 'Salario Mas Alto: '||v_high_low_rec.alto;
      ELSE
          RETURN 'Valores Validos para p_sal: B,L,H.';
    END CASE;

    EXCEPTION
      WHEN e_wrong_entry THEN
          RETURN 'Debe introducir un departamento/empleo valido.';
END;
/

SHOW ERROR

/*Creamos una función que recibe como parámetro, el numero de departamento, el código de empleo y una variable que especifica el tipo de salario que se quiere retornar; Según se especifique en el tercer parámetro, la función retornaría el salario mayor, menor o ambos de dicho empleo en el departamento indicado.*/

 

SELECT low_high_salary(50, 'ST_MAN','H')
FROM dual;

/*Realizamos una consulta en la cual usamos la función antes definida.*/

---OUTPUT:


Fuentes: Oracle Database: PL/SQL Fundamentals,

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm,

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm

Read 2464 times Last modified on Sábado, 11 Julio 2020 15:32

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL / SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis de Manejo de Base de Datos. Aprende a programar, a crear aplicaciones para empresas y negocios y gana dinero. Hosting y Tecnología.