Oracle

Cursores explícitos en PL/SQL

Por : Jhons_1101
2018-03-22
Tags :
Cuando se necesita un control preciso sobre un proceso de consulta, se puede declarar explícitamente un cursor en la parte declarativa de cualquier bloque, subprograma o paquete de PL/SQL. Utilizando tres comandos para controlar el cursor OPEN, FETCH y CLOSE.

  • 1: Se debe de inicializar el cursor con la instrucción OPEN, que identifica el conjunto de resultados.
  • 2: Luego, puede ejecutar FETCH repetidamente hasta que se hayan recuperado todas las filas, también puede usar la cláusula BULK COLLECT para buscar todas las filas a la vez.
  • 3: Cuando se haya procesado la última fila, cierre el cursor con la instrucción CLOSE.

Existe otra técnica para el uso de cursores explicitos, con algó mas de complejidad pero que nos proporciona más caracteristicas de manipulación de datos y su principal ventaja es la flexibilidad es el atributo FOR.
  • * Procesa varias consultas en paralelo declarando y abriendo múltiples cursores.
  • * Procesa varias filas en una iteración de un bucle único.
  • * Puede saltar filas o dividir el procesamiento en más de un bucle de iteración.

Debe declarar un cursor explícito para las consultas que devuelven más de una fila.

Se debe declarar un cursor antes de hacer referencia a él en otras declaraciones o en la parte del BEGIN. Se le debe de dar un nombre único al cursor y asociarlo con una consulta específica.

Opcionalmente puede declarar un tipo de devolución para el cursor. Como por ejemplo nombre_de_tabla%ROWTYPE.
Opcionalmente, puede especificar parámetros que use en la cláusula WHERE en lugar de referirse a variables locales. Estos parámetros pueden tener valores predeterminados.

DECLARE
    cod_pais        NUMBER(4);
    nom_pais        VARCHAR2(100);
    cod_continente  NUMBER(4);
    dpto            departamento%ROWTYPE;

    CURSOR cursor1 
    IS 
        SELECT cod_pais, nom_pais, cod_continente 
        FROM PAIS
        WHERE cod_pais > 57; 

    CURSOR cursor2 
    RETURN departamento%ROWTYPE
    IS 
        SELECT * 
        FROM departamento 
        WHERE DEPTO_ID = 76;


Un cursor puede tomar parámetros que pueden aparecer en la consulta asociada. Tambíen pueden usar constantes cómo parámetros.
Los parámetros formales de un cursor deben ser parámetros de tipo entrada (IN); estos parámetro proporcionan valores en la consulta, pero no devuelven ningún valor.

No puede poner la restricción NOT NULL en un parámetro de cursor.


  • Cómo usar %NOTFOUND
  • Como se muestra en el siguiente ejemplo, puede inicializar los parámetros del cursor a los valores predeterminados. Puede pasar diferentes números de parámetros reales a un cursor, aceptando o anulando los valores predeterminados a su gusto. Además, puede agregar nuevos parámetros formales sin tener que cambiar las referencias existentes al cursor.

    Los parámetros del cursor se pueden referenciar sólo dentro de la consulta especificada en la declaración del cursor.
    Los valores de los parámetros son utilizados por la consulta asociada cuando se abre el cursor.
    
    DECLARE
    CURSOR cursor1 (
        lnu_min  NUMBER DEFAULT 0,
        lnu_max  NUMBER DEFAULT 99
    ) IS
        SELECT * 
        FROM departamento 
        WHERE DEPTO_ID > lnu_min
        AND DEPTO_ID < lnu_max;
    
    
    compartir en facebook compartir en Google compartir en Twitter compartir en Blogger compartir como código embebido compartir la url

    Declaración de cursores explícitos


    Al abrir el cursor, se ejecuta la consulta e identifica el conjunto de resultados, que consta de todas las filas que cumplen los criterios de búsqueda.
    Las filas en el conjunto de resultados son recuperadas por la instrucción FETCH, no cuando se ejecuta la instrucción OPEN.

    Para los cursores declarados utilizando la cláusula FOR UPDATE, la instrucción OPEN bloquea esas filas.

    A menos que use la cláusula BULK COLLECT, en la Obtención de los datos del Cursor, la instrucción FETCH recupera las filas en el conjunto de resultados de a una por vez.

    Cada extracción recupera la fila actual y avanza el cursor a la siguiente fila en el conjunto de resultados.

    Puede almacenar cada columna en una variable separada o almacenar toda la fila en un registro que tenga los campos apropiados, generalmente declarados usando %ROWTYPE.

    Para cada valor de columna devuelto por la consulta asociada con el cursor, debe haber una variable correspondiente compatible con el tipo de dato en la clausula INTO.
    Normalmente se utiliza la instrucción FETCH con las instrucciones LOOP y EXIT WHEN .. NOTFOUND.

    Puede usar expresiones regulares en las consultas, si se requiere...
    La consulta puede hacer referencia a variables dentro de su alcance. Cualquier variable en la consulta se evalúa solo cuando se abre el cursor.

    
    DECLARE
        cod_pais  PAIS.COD_PAIS%TYPE;
        nom_pais  PAIS.NOM_PAIS%TYPE;
        
        CURSOR cursor1
        IS
            SELECT COD_PAIS, NOM_PAIS 
            FROM PAIS 
            WHERE REGEXP_LIKE (NOM_PAIS, '[CO]'); 
            /* BUSCA LOS PAISES QUE TENGAN "CO" EN SU NOMBRE*/
        reg_pais PAIS%ROWTYPE;
    
        CURSOR cursor2
        IS 
            SELECT * 
            FROM PAIS 
            WHERE REGEXP_LIKE (NOM_PAIS, 'CO(L|R)EA'); 
            /* BUSCA LOS PAISES QUE EMPIECEN POR "CO" Y TENGAN "L" O "R" Y TERMINEN EN "EA"*/
    BEGIN
    DBMS_OUTPUT.PUT_LINE( '---------------Cursor 1--------------' );
        OPEN cursor1;
        LOOP
            FETCH cursor1 INTO cod_pais, nom_pais;
            EXIT WHEN cursor1%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(nom_pais);
        END LOOP;
        CLOSE cursor1;
    
        DBMS_OUTPUT.PUT_LINE( ' ' );
        DBMS_OUTPUT.PUT_LINE( '---------------Cursor 2--------------' );
    
        OPEN cursor2;
        LOOP
            FETCH cursor2 INTO reg_pais;
            EXIT WHEN cursor2%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(nom_pais);
        END LOOP;
        CLOSE cursor2;
    END;
    
    

    La salida por DBMS_OUTPUT.PUT_LINE será......

    expresion regular en cursor explicito en PL/SQL



    Para cambiar el conjunto de resultados o los valores de las variables en la consulta, se debe cerrar y volver a abrir el cursor con las variables de entrada establecidas en sus nuevos valores. sin embargo
    Puede usar una lista INTO diferente en recuperaciones separadas con el mismo cursor. Cada extracción recupera otra fila y asigna valores a las variables de destino.
    
    DECLARE
        CURSOR CURSOR_PAIS 
            IS 
            SELECT NOM_PAIS FROM PAIS ORDER BY NOM_PAIS ASC;
                
        nom_pais1 PAIS.NOM_PAIS%TYPE;
        nom_pais2 PAIS.NOM_PAIS%TYPE;
        nom_pais3 PAIS.NOM_PAIS%TYPE;
    BEGIN
        OPEN CURSOR_PAIS;
        FETCH CURSOR_PAIS INTO nom_pais1;
        FETCH CURSOR_PAIS INTO nom_pais2;
        FETCH CURSOR_PAIS INTO nom_pais3;
               
        DBMS_OUTPUT.PUT_LINE(nom_pais1); -- imprime la primera línea de la consulta
        DBMS_OUTPUT.PUT_LINE(nom_pais2); -- imprime la segunda línea de la consulta
        DBMS_OUTPUT.PUT_LINE(nom_pais3); -- imprime la tercera línea de la consulta
               
        CLOSE CURSOR_PAIS;
    
        /*
        ----------------------------------- 
        -- SALIDA DBMS_OUTPUT
        ----------------------------------- 
            COLOMBIA
            COREA DEL NORTE
            COREA DEL SUR
        */
    END;
    
    

    tabla PAIS del curso de PL/SQL


    Cargando los datos en un BULK COLLECT


    La cláusula BULK COLLECT permite buscar todas las filas del conjunto de resultados a la vez. Para realizar una búsqueda masiva desde un cursor en dos colecciones.
    
    DECLARE
        TYPE codPaisTab IS TABLE OF PAIS.COD_PAIS%TYPE;
        TYPE nomPaisTab IS TABLE OF PAIS.NOM_PAIS%TYPE;
        lnu_codPais  codPaisTab;
        lvc_nomPais nomPaisTab;
        
        CURSOR cursor1 
            IS
            SELECT COD_PAIS, NOM_PAIS FROM PAIS;
    BEGIN
    
        OPEN cursor1;
            FETCH cursor1 BULK COLLECT INTO lnu_codPais, lvc_nomPais;
        CLOsE cursor1;
        
        -- Recorremos el BULK COLLECT
        FOR i IN lnu_codPais.FIRST .. lnu_codPais.LAST
            LOOP
                DBMS_OUTPUT.PUT_LINE( lnu_codPais(i) );
        END LOOP;
        
        FOR i IN lvc_nomPais.FIRST .. lvc_nomPais.LAST
            LOOP
            -- Fijemonos que hice el uso de UPPER() para que transforme el texto a mayusculas 
            -- y encuentre fácil las coincidencias con el nombre del país
                IF lvc_nomPais(i) LIKE UPPER('%col%') THEN
                    DBMS_OUTPUT.PUT_LINE( lvc_nomPais(i) );
                END IF;
        END LOOP;
    END;
    /*
    ----------------------------------- 
    -- SALIDA DBMS_OUTPUT
    ----------------------------------- 
        57
        82
        850
        COLOMBIA
    */
    
    


    La instrucción CLOSE desactiva el cursor y el conjunto de resultados queda indefinido. Cualquier otra operación en un cursor cerrado plantea la excepción predefinida INVALID_CURSOR.






    Si este contenido te fue útil, no olvides compartirlo en redes sociales, Considéralo. Puede ser la manera de agradecer!


    Agrega tu comentario...

    Puedes utilizar etiquetas <pre></pre>, <p></p>, <div></div>, + (Nombre usuario, para responderle a alguien)

    OIvDf6A





    Este post no tiene comentarios, sé el primero en hacerlo
    Esta entrada no cuenta con imágenes adjuntas

    Unete al grupo de whatsApp +57 316 392 6456

    Sigue el grupo en facebook

    Siguenos.....