tipos de datos

ejemplos.sql

**********
Ejemplo de tipos de datos
**********
CREATE OR REPLACE TYPE          pk_licencia AS OBJECT
(
    cod_tipo_licencia       VARCHAR2(10)
,   num_licencia            VARCHAR2(8)
,   CONSTRUCTOR FUNCTION pk_licencia(licencia VARCHAR2) RETURN SELF AS RESULT
)
INSTANTIABLE FINAL;
/


CREATE OR REPLACE TYPE BODY          pk_licencia
IS
    CONSTRUCTOR FUNCTION pk_licencia(licencia VARCHAR2) RETURN SELF AS RESULT IS
    BEGIN
        SELF.cod_tipo_licencia := util.extraer_valor(licencia, 1);   
        SELF.num_licencia := util.extraer_valor(licencia, 2);         
        RETURN;
    END pk_licencia;
END;
/

*********
Utilidad para extraer los campos
*********

CREATE OR REPLACE PACKAGE BODY util
IS
    FUNCTION extraer_valor(cadena_valores VARCHAR2, posicion NUMBER DEFAULT 1, separador VARCHAR2 DEFAULT '@@') RETURN VARCHAR2 IS
        desde NUMBER;
        hasta NUMBER;    
    BEGIN
        IF posicion > 1 THEN
            desde := INSTR(cadena_valores, separador, 1, posicion -1);
            IF desde = 0 THEN
                RAISE NO_DATA_FOUND;
            ELSE
                desde := desde + LENGTH(separador);
            END IF;
        ELSE
            desde := 1;
        END IF; 
            
        hasta := INSTR(cadena_valores, separador, 1, posicion);
        
        IF hasta = 0 THEN
            hasta := LENGTH(cadena_valores) + 1;
        END IF;
        
        RETURN SUBSTR(cadena_valores, desde, hasta-desde);    
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;    
    END extraer_valor;
END util;
/

***********
Otro ejemplo de tipos de datos
***********

CREATE OR REPLACE TYPE         xsl_transformer AS OBJECT (
    xml XMLTYPE,
    CONSTRUCTOR FUNCTION xsl_transformer RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION xsl_transformer(p_xml XMLTYPE) RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION xsl_transformer(proced VARCHAR2) RETURN SELF AS RESULT,
    MEMBER PROCEDURE set_xml(p_xml XMLTYPE),
    MEMBER PROCEDURE set_xml(p_id VARCHAR2),
    MEMBER PROCEDURE append_xml(p_xml XMLTYPE),
    MEMBER PROCEDURE append_xml(p_xml XMLTYPE, p_xPath VARCHAR2),
    MEMBER PROCEDURE append_xml(p_id VARCHAR2),
    MEMBER PROCEDURE append_xml(p_id VARCHAR2, p_xPath VARCHAR2),
    MEMBER PROCEDURE add_verifica_firma( continuar   IN VARCHAR2,
                                         tipo        IN VARCHAR2 DEFAULT 'E',
                                         idioma      IN VARCHAR2 DEFAULT 'ES'),
    MEMBER PROCEDURE add_firma,
    MEMBER PROCEDURE add_menu,
    MEMBER PROCEDURE add_menu(p_proced VARCHAR2),
    MEMBER PROCEDURE add_migas,
    MEMBER PROCEDURE add_migas(p_proced VARCHAR2),
    MEMBER PROCEDURE add_menu_y_migas,
    MEMBER PROCEDURE add_menu_y_migas(p_proced VARCHAR2),
    MEMBER PROCEDURE add_content(p_xml XMLTYPE),
    MEMBER PROCEDURE add_content(p_html CLOB),
    MEMBER FUNCTION get_xml RETURN XMLTYPE,
    MEMBER FUNCTION get_html RETURN CLOB
)
INSTANTIABLE FINAL;
/


CREATE OR REPLACE TYPE BODY          xsl_transformer
IS
    CONSTRUCTOR FUNCTION xsl_transformer RETURN SELF AS RESULT IS
    BEGIN
        SELF.xml := xsl_util.default_xml; 
        add_menu_y_migas;       
        RETURN;
    END xsl_transformer;

    CONSTRUCTOR FUNCTION xsl_transformer(p_xml XMLTYPE) RETURN SELF AS RESULT IS
    BEGIN
        SELF.xml := p_xml;               
        RETURN;
    END xsl_transformer;

    CONSTRUCTOR FUNCTION xsl_transformer(proced VARCHAR2) RETURN SELF AS RESULT IS
    BEGIN
        SELF.xml := xsl_util.default_xml;    
        add_menu_y_migas(proced);        
        RETURN;
    END xsl_transformer;

    MEMBER PROCEDURE set_xml(p_xml XMLTYPE) IS
    BEGIN
        SELF.xml := p_xml;    
    END set_xml;
                
    MEMBER PROCEDURE set_xml(p_id VARCHAR2) IS
    BEGIN
        SELF.xml := xsl_util.get_xml_template(p_id);    
    END set_xml;
    
    MEMBER PROCEDURE append_xml(p_xml XMLTYPE) IS
    BEGIN
        SELF.xml := xsl_util.append_xml(SELF.xml, '/AESA', p_xml); 
    END append_xml;

    MEMBER PROCEDURE append_xml(p_xml XMLTYPE, p_xPath VARCHAR2) IS
    BEGIN
        SELF.xml := xsl_util.append_xml(SELF.xml, p_xPath, p_xml); 
    END append_xml;

    MEMBER PROCEDURE append_xml(p_id VARCHAR2) IS
    BEGIN
        SELF.xml := xsl_util.append_xml(SELF.xml, '/AESA', xsl_util.get_xml_template(p_id));
    END append_xml;

    MEMBER PROCEDURE append_xml(p_id VARCHAR2, p_xPath VARCHAR2) IS
    BEGIN
        SELF.xml := xsl_util.append_xml(SELF.xml, p_xPath, xsl_util.get_xml_template(p_id));
    END append_xml;

    MEMBER PROCEDURE add_verifica_firma( continuar   IN VARCHAR2,
                                         tipo        IN VARCHAR2 DEFAULT 'E',
                                         idioma      IN VARCHAR2 DEFAULT 'ES')
    IS
    BEGIN        
        append_xml(xsl_util.add_verifica_firma_js(continuar, tipo, idioma));
        add_content(xsl_util.content_verifica_firma);
    END add_verifica_firma;

    MEMBER PROCEDURE add_firma
    IS
    BEGIN        
        append_xml(xsl_util.add_firma_js);
    END add_firma;
    
    MEMBER PROCEDURE add_menu IS
    BEGIN      
        append_xml(xsl_util.default_menu);
    END add_menu;
    
    MEMBER PROCEDURE add_menu(p_proced VARCHAR2) IS
    BEGIN        
        append_xml(xsl_util.get_menu(p_proced));
    END add_menu;

    MEMBER PROCEDURE add_migas IS
    BEGIN        
        append_xml(xsl_util.default_migas);        
    END add_migas;
    
    MEMBER PROCEDURE add_migas(p_proced VARCHAR2) IS
    BEGIN
        append_xml(xsl_util.get_migas(p_proced));
    END add_migas;

    MEMBER PROCEDURE add_menu_y_migas IS
    BEGIN        
        add_menu;
        add_migas;
    END add_menu_y_migas;
    
    MEMBER PROCEDURE add_menu_y_migas(p_proced VARCHAR2) IS
    BEGIN
        add_menu(p_proced);
        add_migas(p_proced);
    END add_menu_y_migas;
                            
    MEMBER PROCEDURE add_content(p_xml XMLTYPE) IS
    BEGIN
        append_xml(p_xml, 'AESA/contenido/div');
    END add_content;
    
    MEMBER PROCEDURE add_content(p_html CLOB) IS
    BEGIN
        add_content(XMLTYPE(p_html));
    EXCEPTION
        WHEN OTHERS THEN
            add_content(XMLTYPE('<span>' || p_html || '</span>'));
    END add_content;
    
    MEMBER FUNCTION get_xml RETURN XMLTYPE IS
    BEGIN 
        RETURN SELF.xml;
    END get_xml;

    MEMBER FUNCTION get_html RETURN CLOB IS
    BEGIN
        RETURN pspgen_xsl_transform.TransformXML2HTML(SELF.xml.getclobval());
    END get_html;
END;
/