MAGIC PL/SQL ORACLE: Métodos en Tipos de Objetos Oracle

Objetivos:

• Introducirnos más a fondo en el mundo de los Tipos de Objetos de Oracle (Object Types).
• Describir e implementar los tipos de Métodos disponibles.
• Personalizar y Describir los Constructores de Objetos.
• El Concepto de Herencia en los Objetos.

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

____________________________________________________________________________________
Sintaxis:

object_type_declaration ::=

CREATE [OR REPLACE] TYPE [schema_name .] type_name

[AUTHID {CURRENT_USER | DEFINER}]

{IS | AS} OBJECT (member_list);

object_type_body ::=

CREATE [OR REPLACE] TYPE BODY [schema_name .] type_name

{IS | AS}

{MEMBER | STATIC} {subprogram_body | call_spec};

[{MEMBER | STATIC} {subprogram_body | call_spec};]...

[{MAP | ORDER} MEMBER function_body;] END;

Los Métodos

Esta publicación es una continuación de Tipos de Objetos de Oracle. Ahora nos enfocaremos en el constructor y los métodos que podemos definir en un Tipo de Objeto Oracle.

Ya habíamos puntualizado que un método es un subprograma declarado en una especificación de tipo de objeto utilizando la palabra clave MEMBER o STATIC. El método no puede tener el mismo nombre que el tipo de objeto o que cualquiera de sus atributos. Los métodos MEMBER se invocan en instancias y los métodos STATIC se invocan en el tipo de objeto, no en sus instancias.

Al igual que los subprogramas contenidos en paquetes, los métodos tienen dos partes: una especificación y un cuerpo. La especificación consiste del nombre, una lista de parámetros (opcional) y, para las funciones, un tipo de retorno. El cuerpo es el código que se ejecuta para llevar a cabo una tarea específica.

Por cada especificación de método contenido en una especificación de objeto, debe haber un cuerpo de método contenido en el cuerpo del objeto o el método como tal debe ser declarado NOT INSTANTIABLE, esto último para indicar que el cuerpo sólo está presente en subtipos de este tipo.

Métodos MEMBER

Los métodos MEMBER se invocan en instancias de objetos(variables del objeto en cuestión) y pueden leer o cambiar los atributos de esa instancia particular.

Métodos STATIC
Los métodos STATIC se invocan en el tipo de objeto, no en ninguna instancia de objeto específica, por lo que deben limitarse a operaciones "globales" que no inciden en los atributos de objeto.

Métodos CONSTRUCTOR

Los métodos CONSTRUCTOR crean nuevas instancias de objetos y rellenan algunos o todos los atributos.

En Oracle, todos los tipos de objetos tienen asociado por defecto un método que construye nuevos objetos de ese tipo de acuerdo a la especificación del tipo. El nombre del método coincide con el nombre del tipo, y sus parámetros son los atributos del tipo.

PL/SQL nunca llama implícitamente a un constructor. Debes llamarlo explícitamente.

Los métodos CONSTRUCTOR y MEMBER aceptan un parámetro built-in denominado SELF, que es una instancia del tipo de objeto. El parámetro SELF es el primero que se le pasa a un método MEMBER. Si no lo específica, se declara automáticamente.

Directrices:
• El parámetro SELF debe ser del mismo tipo de dato que el objeto original.
• En los métodos CONSTRUCTOR, SELF siempre tiene el modo de parámetro IN OUT.
• En funciones MEMBER, si no se específica SELF, su modo(MODE) de parámetro predeterminado es IN.

• En procedimientos MEMBER, si no se específica SELF, su modo de parámetro predeterminado es IN OUT.

• No es posible especificar el modo de parámetro OUT para SELF.
• Los métodos STATIC no pueden aceptar o referirse a SELF.

• En el cuerpo del método, SELF denota el objeto cuyo método fue invocado. Puede hacer referencia a SELF.attribute_name o SELF.member_name, para dejar claro que se está refiriendo a ese objeto en lugar de otro contenido en algún supertipo.

Método MAP

Un método de MAP sirve para indicar cuál de los atributos del tipo se va a utilizar para ordenar los objetos del tipo, y por lo tanto se puede utilizar para comparar los objetos de ese tipo por medio de los operadores de comparación típicos (<, >).

Un tipo de objeto puede contener sólo un método de MAP, que debe ser una función sin parámetros que tenga el tipo de retorno: DATE, NUMBER, VARCHAR2 o un tipo ANSI SQL como CHARACTER, INTEGER o REAL.

Método ORDER

Un método ORDER utiliza los atributos del objeto sobre el que se ejecuta para realizar un cálculo y compararlo con otro objeto del mismo tipo que toma como argumento de entrada(Cada método ORDER toma dos parámetros: el parámetro built-in SELF y otro objeto del mismo tipo). Este método debe devolver un valor negativo si el primero es mayor que el segundo, un valor positivo si ocurre lo contrario y un cero si ambos son iguales. Si cualquiera de los parámetros pasados a un método ORDER es nulo, el método devuelve un valor nulo.

Un tipo de objeto puede contener sólo un método ORDER, que debe ser una función que devuelva un resultado numérico.

____________________________________________________________________________________

Introducción a la herencia de Tipo en PL/SQL

PL/SQL admite un modelo de herencia única. Puede definir subtipos de tipos de objetos. Estos subtipos contienen todos los atributos y métodos del tipo padre (o supertipo). Los subtipos también pueden contener atributos adicionales y métodos adicionales, y pueden sustituir métodos del supertipo.

Puede definir si se pueden derivar o no subtipos de un tipo particular. También puede definir tipos y métodos que no se pueden instanciar directamente, solo declarando subtipos que los instancien(NOT INSTANTIABLE).

Algunas de las propiedades de tipo se pueden cambiar dinámicamente con la instrucción ALTER TYPE. Cuando se realizan cambios en el supertipo, ya sea a través de ALTER TYPE o redefiniendo el supertipo, los subtipos reflejan automáticamente esos cambios.

Puede utilizar el operador TREAT para devolver sólo los objetos que son de un subtipo especificado.

Los valores de las funciones REF y DEREF pueden representar el tipo declarado de la tabla o vista o uno o más de sus subtipos.

____________________________________________________________________________________

Ejemplos

CREATE TABLE hr.account_holder

(

identity_id VARCHAR2(12) --NÚMERO DE IDENTIDAD

CONSTRAINT pk_ident PRIMARY KEY,

first_name VARCHAR2(20), --NOMBRE

last_name VARCHAR2(20), --APELLIDO

sex CHAR(1)

CONSTRAINT ck_sex CHECK(sex IN ('M','F')),

birth_date DATE, --FECHA DE NACIMIENTO

address VARCHAR2(100), --DIRECCIÓN

contact_number VARCHAR2(10), --NÚMERO DE CONTACTO

email VARCHAR2(60)

);

/*Creamos una tabla para almacenar algunos datos de un titular de cuenta bancaria.*/

---

INSERT INTO hr.account_holder(identity_id,first_name,last_name,sex,birth_date,address,contact_number,email)

VALUES('0120105','Manuel','Lima','M',TO_DATE('05/06/1981','DD/MM/YYYY'),'Dirección1','8092225555','mp@ya.d');

INSERT INTO hr.account_holder(identity_id,first_name,last_name,sex,birth_date,address,contact_number,email)

VALUES('0010105','Carla','Lopez','F',TO_DATE('03/09/1989','DD/MM/YYYY'),'Dirección2','8090005522','cl@ho.m');

INSERT INTO hr.account_holder(identity_id,first_name,last_name,sex,birth_date,address,contact_number,email)

VALUES('0020108','Alexs','Jimenes','M',TO_DATE('08/01/1975','DD/MM/YYYY'),'Dirección3','8099998888','aj@g.t');

COMMIT;

/*Insertamos algunos registros para probar el tipo objeto que crearemos a continuación.*/
---
CREATE OR REPLACE TYPE hr.typ_account_holder IS OBJECT
(
identity_id VARCHAR2(12),
first_name VARCHAR2(20),
last_name   VARCHAR2(20),
sex CHAR(1),
birth_date DATE,
address   VARCHAR2(100),
contact_number VARCHAR2(10),
email   VARCHAR2(60),

CONSTRUCTOR FUNCTION typ_account_holder(p_identity VARCHAR2)
RETURN SELF AS RESULT
);
/
SHOW ERROR

/*Creamos el tipo de objeto: typ_account_holder que representa un titular de una cuenta bancaria. Notar que sobrecargamos la función CONSTRUCTOR para que solo acepte un parámetro y a partir de este genere los valores de cada atributo. Nota: el CONSTRUCTOR original aun es valido.*/

---
CREATE OR REPLACE TYPE BODY hr.typ_account_holder AS
CONSTRUCTOR FUNCTION typ_account_holder(p_identity VARCHAR2)
RETURN SELF AS RESULT
AS
CURSOR cur_acct_holder IS
SELECT h.*
FROM hr.account_holder h
WHERE h.identity_id = p_identity;

v_rec_holder cur_acct_holder%ROWTYPE;
BEGIN
OPEN cur_acct_holder;
FETCH cur_acct_holder INTO v_rec_holder;
CLOSE cur_acct_holder;

SELF.identity_id := v_rec_holder.identity_id;
  SELF.first_name := v_rec_holder.first_name;
  SELF.last_name := v_rec_holder.last_name;
  SELF.sex := v_rec_holder.sex;
  SELF.birth_date := v_rec_holder.birth_date;
  SELF.address := v_rec_holder.address;
  SELF.contact_number := v_rec_holder.contact_number;
  SELF.email := v_rec_holder.email;

RETURN;
END;
END;
/
SHOW ERROR

/*Como el objeto tiene un método, creamos un cuerpo para así desarrollar la lógica del mismo. Notar que usamos un CURSOR para buscar los datos del cliente y luego lo asignamos al atributo correspondiente.*/

---
SET SERVEROUTPUT ON
DECLARE
v_holder hr.typ_account_holder;
BEGIN
v_holder := hr.typ_account_holder('0010105');

DBMS_OUTPUT.PUT_LINE
(
'Cliente: '||v_holder.first_name||' '||v_holder.last_name||CHR(10)||
'Sexo: '||v_holder.sex||CHR(10)||
'Edad: '||TRUNC(MONTHS_BETWEEN(SYSDATE,v_holder.birth_date)/12)
);
END;

/*En este ejemplo probamos la funcionalidad de la sobrecarga del CONSTRUCTOR perteneciente al tipo typ_account_holder.*/

---OUTPUT:

CREATE TABLE hr.account_details
(
account_number NUMBER(9)
CONSTRAINT pk_acct PRIMARY KEY, --NÚMERO DE CUENTA
account_title   VARCHAR2(30), --TITULO DE LA CUENTA
account_type CHAR(1), --TIPO DE CUENTA
open_date   DATE, --FECHA APERTURA
balance   NUMBER, --SALDO
last_tranc CHAR(1), --ÚLTIMA TRANSACCIÓN
last_tranc_date   DATE, --FECHA ÚLTIMA TRANSACCIÓN
interest_rate   NUMBER, --TASA DE INTERÉS
status CHAR(1) --A: Activa, E: Emisión, C: Cancelada
  CONSTRAINT ck_status CHECK(status IN ('A','E','C')),
status_date   DATE,

  CONSTRAINT ck_status_date
  CHECK(status_date >= open_date)
);

/*En el pasado ejemplo creamos la tabla: account_details para almacenar algunos datos de una cuenta bancaria.*/

---

DECLARE

v_balance NUMBER;

BEGIN

--Eliminamos para que no se viole el PRIMARY KEY en caso de ejecutar el SCRIPT más de una vez

DELETE FROM hr.account_details

WHERE account_number BETWEEN 11045 AND 11050;

FOR i IN 11045..11050 LOOP

v_balance := TRUNC(DBMS_RANDOM.VALUE(11000,65111));

INSERT INTO hr.account_details

VALUES(i,'Cuenta '||i,'D',TRUNC(SYSDATE),v_balance,NULL,NULL,0.01,'E',TRUNC(SYSDATE));

END LOOP;

COMMIT;

END;

/*Este SCRIPT inserta 6 registro en la tabla: account_details con balances aleatorios entre: 11000 y 65111.*/

---REGISTROS:

CREATE OR REPLACE TYPE hr.typ_account_details IS OBJECT
(
account_number NUMBER, --NÚMERO DE CUENTA
account_title   VARCHAR2(30), --TITULO DE LA CUENTA
account_type CHAR(1), --TIPO DE CUENTA
open_date   DATE, --FECHA APERTURA
balance   NUMBER, --SALDO
last_tranc CHAR(1), --ÚLTIMA TRANSACCIÓN
last_tranc_date   DATE, --FECHA ÚLTIMA TRANSACCIÓN
interest_rate   NUMBER, --TASA DE INTERÉS
status CHAR(1),
status_date   DATE,

CONSTRUCTOR FUNCTION typ_account_details
RETURN SELF AS RESULT,

MEMBER PROCEDURE p_status_change(
p_acct_num IN NUMBER,
p_new_sta   IN CHAR,
p_result OUT NUMBER
)
);
/
SHOW ERROR

/*Creamos el Tipo: hr.typ_account_details el cual representara los detalles de una cuenta bancaria. En esta especificacion definimos algunos atributos, una sobrecarga del CONSTRUCTOR sin parámetros y un procedimiento: p_status_change*/

---

CREATE OR REPLACE TYPE BODY hr.typ_account_details IS

CONSTRUCTOR FUNCTION typ_account_details

RETURN SELF AS RESULT IS

BEGIN

RETURN;

END typ_account_details;

MEMBER PROCEDURE p_status_change(

p_acct_num IN NUMBER,

p_new_sta IN CHAR,

p_result OUT NUMBER

) IS

CURSOR cur_valid_acct IS

SELECT 'A'

FROM hr.account_details ad

WHERE ad.account_number = p_acct_num;

v_valid_acct CHAR(1);

BEGIN

CASE

WHEN UPPER(p_new_sta) NOT IN ('A','E','C') THEN

p_result := 0; --El estatus es invalido

RETURN;

ELSE

OPEN cur_valid_acct;

FETCH cur_valid_acct INTO v_valid_acct;

CLOSE cur_valid_acct;

IF v_valid_acct IS NULL THEN

p_result := 1; --La cuenta no es valida

RETURN;

END IF;

END CASE;

UPDATE hr.account_details ad

SET ad.status = UPPER(p_new_sta),

ad.status_date = TRUNC(SYSDATE)

WHERE ad.account_number = p_acct_num;

COMMIT;

p_result := 2; --Actualización Exitosa

END p_status_change;

END;

SHOW ERROR

/*En el cuerpo del Objeto desarrollamos la sobrecarga del CONSTRUCTOR para inicialice los atributos en NULL y para que p_status_change actualice el estatus de una cuenta recibida como parámetro. */

---

SET SERVEROUTPUT ON

DECLARE

v_acct hr.typ_account_details;

v_result NUMBER(1);

BEGIN

v_acct := hr.typ_account_details;

v_acct.p_status_change

(

11045,

'A',

v_result

);

DBMS_OUTPUT.PUT_LINE('Resultado: '||v_result);

END;

/*En este ejemplo probamos la funcionalidad del método: p_status_change de typ_account_details. Notar en el OUTPUT el resultado 2, indicando que se actualizo el registro, más abajo podemos ver el nuevo valor en el campo status del registro en cuestión.*/

---OUTPUT y REGISTROS:

ALTER TABLE hr.account_details

ADD holder_id VARCHAR2(12)

CONSTRAINT fk_acct_holder

REFERENCES hr.account_holder(identity_id);

/*Procedemos a alterar la tabla: account_details para así agregar el código del titular(holder_id) de la cuenta.*/

---

UPDATE hr.account_details ad

SET ad.holder_id =

CASE

WHEN ad.account_number < 11047 THEN '0010105'

WHEN ad.account_number > 11049 THEN '0120105'

ELSE '0020108'

END;

COMMIT;

/*Con este SCRIPT asignamos los detalles de cuentas a los clientes existentes.*/

---REGISTROS:

CREATE OR REPLACE TYPE hr.t_account_details
IS TABLE OF hr.typ_account_details;

/*Ahora creamos un tipo tabla: hr.t_account_details que contendrá registros del tipo: hr.typ_account_details antes creado. */

---

CREATE OR REPLACE TYPE hr.typ_bank_accounts IS OBJECT
(
account_holder hr.typ_account_holder, --TITULAR DE LA CUENTA
account hr.t_account_details, --CUENTA

CONSTRUCTOR FUNCTION typ_bank_accounts
RETURN SELF AS RESULT,
MEMBER PROCEDURE p_bring_details
(
p_param VARCHAR2,
p_typ VARCHAR2 DEFAULT 'H'
)
);
/

/*Por último re-creamos el tipo: hr.typ_bank_account para que contenga dos atributos referentes a los tipos antes creados(hr.typ_account_holder y hr.t_account_details)*/

---

CREATE OR REPLACE TYPE BODY hr.typ_bank_accounts IS

CONSTRUCTOR FUNCTION typ_bank_accounts
RETURN SELF AS RESULT IS
BEGIN
RETURN;
END typ_bank_accounts;

MEMBER PROCEDURE p_bring_details
(
p_param VARCHAR2,
p_typ VARCHAR2 DEFAULT 'H'
) IS
CURSOR cur_acct_details IS
SELECT *
FROM hr.account_details ad
WHERE ad.account_number = p_param;

v_rec_acct cur_acct_details%ROWTYPE;

PROCEDURE p_acct_details(p_holder VARCHAR2) IS
CURSOR cur_acct_det IS
SELECT *
FROM hr.account_details ad
WHERE ad.holder_id = p_holder;

v_count NUMBER := 0;
BEGIN
SELF.account := hr.t_account_details();

FOR i IN cur_acct_det LOOP
v_count := v_count+1;

  SELF.account.EXTEND;
  SELF.account(v_count) := hr.typ_account_details
(
i.account_number,
i.account_title,
i.account_type,
i.open_date,
i.balance,
i.last_tranc,
i.last_tranc_date,
i.interest_rate,
i.status,
i.status_date
);
END LOOP;
  END p_acct_details;

BEGIN
IF UPPER(p_typ) = 'H' THEN
  SELF.account_holder := typ_account_holder(p_param);
p_acct_details(p_param);
ELSIF UPPER(p_typ) = 'A' THEN
OPEN cur_acct_details;
FETCH cur_acct_details INTO v_rec_acct;
CLOSE cur_acct_details;

  SELF.account_holder := typ_account_holder(v_rec_acct.holder_id);

  SELF.account := t_account_details();
  SELF.account.EXTEND;

  SELF.account(1) := typ_account_details
(
v_rec_acct.account_number,
v_rec_acct.account_title,
v_rec_acct.account_type,
v_rec_acct.open_date,
v_rec_acct.balance,
v_rec_acct.last_tranc,
v_rec_acct.last_tranc_date,
v_rec_acct.interest_rate,
v_rec_acct.status,
v_rec_acct.status_date
);
ELSE
  SELF.account_holder := typ_account_holder(NULL);

  SELF.account := t_account_details();
END IF;
END p_bring_details;
END;
/

/*E aquí el cuerpo del tipo: hr.typ_bank_accounts; Notar que el mismo tiene una sobrecarga del CONSTRUCTOR que no recibe parámetros y que inicializa los atributos con NULL, el método: p_bring_details perteneciente al tipo, se encarga de extraer los datos de las tablas: account_details y account_holder de acuerdo a los parámetros recibidos: p_param que puede ser tanto un número de cuenta como un número de identidad de cliente y p_typ que indica si el primer parámetro es un cliente(H) o una cuenta(A).
Este tipo es también de un ejemplo de herencia de objetos ya que como vimos hr.typ_bank_accounts contiene los atributos y métodos de los tipos: hr.typ_account_holder y hr.typ_account_details.
*/

---

SELECT
identity_id AS CEDULA,
first_name||' '||last_name AS NOMBRE,
d.account_number AS "CUENTA #",
d.account_title AS TITULO,
d.balance
FROM hr.account_holder h, account_details d
WHERE h.identity_id = d.holder_id;

/*Realizamos esta consulta para ver los datos de que usaremos mas adelante para probar los tipo de objetos.*/

---OUTPUT:

SET SERVEROUTPUT ON

DECLARE

v_param VARCHAR2(12) := '0020108';

v_type CHAR(1) := 'H';

v_account hr.typ_bank_accounts;

BEGIN

v_account := hr.typ_bank_accounts;

v_account.p_bring_details(v_param,v_type);

DBMS_OUTPUT.PUT_LINE

(

'Cliente: '||v_account.account_holder.first_name||' '||v_account.account_holder.last_name||CHR(10)||

'Cuenta/as:'||CHR(10)||RPAD('=',30,'=')

);

IF v_account.account.COUNT > 0 THEN

FOR i IN v_account.account.FIRST..v_account.account.LAST LOOP

DBMS_OUTPUT.PUT_LINE

(

'Cuenta #: '||v_account.account(i).account_number||CHR(10)||

'Tipo: '||v_account.account(i).account_type||CHR(10)||

'Balance: '||v_account.account(i).balance||CHR(10)||

'...'

);

END LOOP;

END IF;

END;

/*Notar aquí la implementación de los objectos antes creamos, ver como podemos usar los métodos: COUNT, FIRST, LAST... de los tipo de objetos tabla(NESTED TABLE) para así iterar los registros contenidos en esas colecciones.*/

---OUTPUT:

No olvidar que para eliminar los objetos creamos usamos la sentencia DROP:

DROP TYPE hr.typ_account_holder FORCE;
DROP TYPE hr.typ_account_details FORCE;
DROP TYPE hr.t_account_details FORCE;
DROP TYPE hr.typ_bank_accounts;
/*Al Indicar FORCE se eliminan los objetos aunque tengan dependencias de otros objectos.*/

____________________________________________________________________________________

Fuentes: https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/10_objs.htm#CHDEFBEA
https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems031.htm

MAGIC PL/SQL ORACLE

sábado, 10 de diciembre de 2016

Métodos en Tipos de Objetos Oracle

Escribiendo Código Flexible en Oracle Forms

Buscar este blog