Funciones y paquetes

Publicado: julio 13, 2011 en Oracle

FUNCIONES PL/SQL

PL/SQL
Definición de Función

  • Una función es un bloque nombrado PL/SQL que devuelve un valor.
  • Una función puede estar almacenada en la B.D., como objeto de la B.D., para repetidas ejecuciones.
  • Una función puede ser llamada como parte de una expresión.

PL/SQL
Sintaxis Creación Funciones

PL/SQL
Creación de una Función

PL/SQL
Desde dónde llamarlas

  • Como columna de un SELECT.
  • Condiciones en cláusulas WHERE y HAVING.
  • Cláusulas ORDER BY y GROUP BY.
  • Cláusula VALUES de un comando INSERT.
  • Cláusula SET de un comando UPDATE.

PL/SQL
Restricciones en Funciones

  • No se permiten comandos INSERT, UPDATE o DELETE.
  • La función no puede llamar a otro subprograma que rompa una de las restricciones arriba indicadas.

PL/SQL
Borrado de Funciones

  • Para quitar una función de la parte del servidor, utilizando SQL*Plus, ejecutar el comando DROP FUNCTION.
  • Sintaxis:

 

 

Paquetes

 

Los paquetes en Oracle se utilizan para guardar subprogramas y otros objetos de la base de datos.

Un paquete consta de los siguientes elementos:

  • Especificación o cabecera: contiene las declaraciones públicas (es decir, accesibles desde cualquier parte de la aplicación) de sus programas, tipos, constantes, variables, cursores, excepciones, etc.
  • Cuerpo: contiene los detalles de implementación y declaraciones privadas, es decir, accesibles solamente desde los objetos del paquete.

La sintaxis de la cabecera es la siguiente:

   create [or replace] package nombre_paquete as
      <declaraciones públicas>
      <especificaciones de subprogramas>
   end nombre_paquete;

La sintaxis del cuerpo sería la siguiente:

   create [or replace] package body nombre_paquete as
      <declaraciones privadas>
      <código de subprogramas>
      [begin
         <instrucciones iniciales>]
   end nombre_paquete;

Como podéis observar la cabecera se compila independientemente del cuerpo. Os dejo un ejemplo de paquete para que lo veáis más claro.

/* Cabecera */
create or replace package busar_emple as
   TYPE t_reg_emple is RECORD
      (num_empleado emple.emp_no%TYPE,
      apellido emple.apellido%TYPE,
      salario emple.salario%TYPE,
      departamento emple.dept_no%TYPE);
   procedure ver_por_numero(v_cod emple.emp_no%TYPE);
   procedure ver_por_apellido(v_ape emple.apellido%TYPE);
   function datos (v_cod emple.emp_no%TYPE)
      return t_reg_emple;
end buscar_emple;
/* Cuerpo */
create or replace package body buscar_emple as
   vg_emple t_reg_emple;
   procedure ver_emple; /* procedimiento privado*/
   procedure ver_por_numero (v_cod emple.emp_no%TYPE)
   is
   begin
      select emp_no, apellido, salario, dept_no into vg_emple from emple where emp_no=v_cod;
      ver_emple;
   end ver_por_numero;
   procedure ver_por_apellido (v_ape emple.apellido%TYPE)
   is
   begin
      select emp_no,apellido,salario,dept_no into vg_emple from emple where apellido=v_apellido;
      ver_emple;
   end ver_por_apellido;
   function datos (v_cod emple.emp_no%TYPE)
      return t_reg_emple
   is
   begin
      select emp_no,apellido,salario,dept_no into vg_emple from emple where emp_no=v_cod;
   procedure ver_emple
      is
      begin
         DBMS_OUTPUT.PUT_LINE(vg_emple.num_empleado || ‘*’ || vg_emple.apellido || ‘*’ || vg_emple.salario || ‘*’|| vg_emple.departamento);
   end ver_emple;
end buscar_emple;

Como podéis ver este paquete nos permite buscar un empleado de tres formas distintas y visualizar sus datos.

Utilización de los objetos definidos en los paquetes

Podemos utilizar los objetos definidos en los paquetes básicamente de dos maneras distintas:

  • Desde el mismo paquete: esto quiere decir que cualquier objeto puede ser utilizado dentro del paquete por otro objeto declarado en el mismo.
    Para utilizar un objeto dentro del paquete tan sólo tendríamos que llamarlo. La llamada sería algo así: v_emple :=buscar_emple.datos(v_n_ape); (como veis no utilizamos el execute ya que nos encontramos dentro del paquete).
  • Desde fuera del paquete: Podemos utilizar los objetos de un paquete siempre y cuando haya sido declarado en la especificación del mismo. Para llamar a un objeto o procedimiento desde fuera del paquete utilizaríamos la siguiente notación: execute nombre_paquete.nombre_procedimiento(lista de parametros);
Declaración de cursores en paquetes

En los paquetes también podemos introducir cursores, para ello debemos declararlo en la cabecera del paquete indicando su nombre, los parámetros y tipo devuelto. Para que lo veáis más claro os dejo un ejemplo a continuación:

CREATE or REPLACE PACKAGE empleados AS
   …..
   CURSOR a RETURN empleado%ROWTYPE;
   …
   END empleados;
CREATE or REPLACE PACKAGE BODY empleados AS
   ….
   CURSOR a RETURN empleado%ROWTYPE
      SELECT * FROM empleado WHERE salario < 10000;
   ….
END empleados;

Los paquetes suministrados por Oracle son:

Standard : tiene la función to_char y abs
dbms_standard: tiene la función raise_aplication_error
dbms_output: con la función put_line
dbms_sql: que utiliza sql de forma dinámica.

NOTA: sql dinámico significa que el programa es capaz de ejecutar órdenes de definición y manipulación sobre objetos que sólo se conocen al ejecutar el paquete.
Un ejemplo de la utilización de dbms_sql es el siguiente:
   BEGIN
      ……
      id_cursor := DBMS_SQL.OPEN_CURSOR;
      DMBS_SQL.PARSE(id_cursor, instrucción,DMBS_SQL.V3);
      v_dum :=DMBS_SQL.EXECUTE(id_cursor);
      DMBS_SQL.CLOSE_CURSOR(id_cursor);
   ……

Lo que hacemos es abrir el cursor y nos devuelve el id del mismo para poder trabajar con él.
Después tenemos el DMBS_SQL.PARSE que analiza la instrucción que se va a ejecutar. Ya en la siguiente linea ejecutamos la sentencia y por último cerramos el cursor.

Anuncios

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