Oracle

Cursores implícitos en PL/SQL

Por : Jhons_1101
2018-03-22
Tags :
PL/SQL usa cursores implícitos y explícitos. PL/SQL declara un cursor implícitamente para todas las declaraciones de manipulación de datos SQL. incluidas las consultas que devuelven solo una fila. Si desea un control preciso sobre el procesamiento de consultas, puede declarar un cursor explícito en la parte declarativa de cualquier bloque PL/SQL, subprograma o paquete.

Debe declarar un cursor explícito para las consultas que devuelven más de una fila.
Un cursor impícito desplegará el error TOO_MANY_ROWS si la consulta devuelve más de un registro.

Los cursores implícitos son gestionados automáticamente por PL/SQL, por lo que no es necesario escribir ningún código para manejar estos cursores. Sin embargo, puede rastrear información sobre la ejecución de un cursor implícito a través de sus atributos de cursor.
Los atributos del cursor son %FOUND, %ISOPEN, %NOTFOUND y %ROWCOUNT. Los valores de los atributos del cursor siempre se refieren a la declaración SQL ejecutada más recientemente.

El cursor SQL tiene otro atributo, %BULK_ROWCOUNT, diseñado para su uso con la instrucción FORALL



  • Cómo usar SQL%FOUND
  • SQL,%FOUND generará NULL hasta que se ejecute una instrucción de manipulación de datos. A partir de entonces, %FOUND producirá TRUE si una instrucción INSERT, UPDATE o DELETE afectó a una o más filas, o si una instrucción SELECT INTO devolvió una o más filas. De lo contrario, %FOUND produce FALSE.

    Parece complicado cierto... Pues no lo es. Veamos un ejemplo.
    
    -- Siguiendo con los ejemplos de la tabla departamento...
    -- Los scripts de tabla pueden descargar aqui.. 
    DECLARE
      depto_no NUMBER(4) := 5;
    BEGIN
      DELETE FROM DEPARTAMENTO WHERE DEPTO_ID = depto_no;
      IF SQL%FOUND THEN  -- Si borró correctamente..
        INSERT INTO DEPARTAMENTO VALUES (5, 'ANTIOQUIA', 57);
      END IF;
    END;
    
    
    Explicando un poco a detalle, veremos que el atributo del cursor evaluó como TRUE al ejecutar correctamente el DELETE, y por ello es que entra en la condicional ejecutando el bloque de código del nuevo Insert.



  • Cómo usar %NOTFOUND
  • %NOTFOUND es el opuesto lógico de %FOUND. %NOTFOUND produce TRUE si una instrucción INSERT, UPDATE o DELETE no afectó las filas, o si una instrucción SELECT INTO no devolvió ninguna fila.
    De lo contrario, el atributo %NOTFOUND retornará FALSE.



  • El atributo %ISOPEN
  • Siempre será falso (FALSE) para cursores implícitos. Ya que Oracle cierra el cursor SQL automáticamente después de ejecutar su declaración SQL asociada.
    Como resultado, %ISOPEN siempre retornará FALSE.

    Tener en cuenta que retoraná FALSE sólo en cursores implícitos. Reitero... Sólo en cursores Implicitos...



  • El atributo %ROWCOUNT
  • Evalua cuántas filas afectadas hasta ahora por una instrucción INSERT, UPDATE o DELETE, o las devuelve una instrucción SELECT INTO. %ROWCOUNT produce cero (0) si una instrucción INSERT, UPDATE o DELETE no afectó a ninguna fila o una instrucción SELECT INTO no devolvió ninguna fila.
    
    BEGIN
        DELETE FROM DEPARTAMENTO;
        DBMS_OUTPUT.PUT_LINE('Número de registros borrados: ' || TO_CHAR(SQL%ROWCOUNT));
        -- SALIDA: Número de registros borrados: 2
    END
    
    

    Si una instrucción SELECT INTO devuelve más de una fila, PL/SQL levanta la excepción predefinida TOO_MANY_ROWS y %ROWCOUNT produce 1, no el número real de filas que satisfacen la consulta.

    El valor del atributo SQL%ROWCOUNT se refiere a la instrucción SQL ejecutada más recientemente desde PL/SQL.
    Si se requiere guardar un valor de atributo para su uso posterior, asígnelo a una variable local que después podrá usar.
    compartir en facebook compartir en Google compartir en Twitter compartir en Blogger compartir como código embebido compartir la url

    Declaración de cursores implícitos


    Los cursores implicitos se utilizan para realizar consultas SELECT que devuelven un único registro.

    Se deben de tener en cuenta estas condiciones para poder usar correctamente un cursor implícito.
    • En un cursor implicito debe existir la palabra clave INTO.
    • Las variables que reciben los datos devueltos por el cursor deben de tener el mismo tipo de dato que las columnas de la tabla. De no ser así, se generará un error de tipo de dato.
    • Los cursores implicitos solo pueden devolver una única fila. En caso de que se devuelva más de una fila (o ninguna fila) se producirá una excepcion. ya sea TOO_MANY_ROWS o NO_DATA_FOUND respectivamente.
    
    DECLARE
        Nombre_Depto VARCHAR2(50);
    BEGIN
        SELECT DEPTO_NOMBRE INTO Nombre_Depto
        FROM DEPARTAMENTO WHERE DEPTO_ID = 76;       
        DBMS_OUTPUT.PUT_LINE('El nombre del departamento es: ' || Nombre_Depto);
        -- SALIDA: El nombre del departamento es: valle
    END;
    
    

    cursor implicito en PL/SQL

    Excepciones para el uso de los cursores implícitos


    Como los cursores implícitos sólo pueden devolver una única fila, las excepcionesque podemos encontrar son no_data_found y too_many_rows

    Excepción Explicación
    NO_DATA_FOUND Se produce cuando una sentencia SELECT intenta recuperar datos pero ninguna fila satisface sus condiciones. Es decir, cuando "no hay datos"
    TOO_MANY_ROWS Dado que cada cursor implicito sólo es capaz de recuperar una fila , esta excepcion detecta la existencia de más de una fila.



    Ejemplos de Cursores impícitos

    Forma Fácil de usar un cursor impícito es guardando las columnas en una variable definida del tipo de dato especifico...
    
    -- Manera sencilla de usar un cursor implícito....
    DECLARE
        CURSOR cur_paises IS
        SELECT COD_PAIS, NOM_PAIS, COD_CONTINENTE FROM PAISES;
        lnu_codPais       NUMBER(4);
        lvc_nomPais       VARCHAR2(100);
        lnu_codContinente NUMBER(4);
    BEGIN
        OPEN cur_paises;
            FETCH cur_paises INTO lnu_codPais, lvc_nomPais, lvc_nomPais;
            DBMS_OUTPUT.PUT_LINE(lnu_codPais || '::' || lvc_nomPais);
        CLOSE cur_paises;
    END;
    
    


    Podemos usar tambíen un %RECORD para almacenar las columnas de datos, esta opción es muy buena, ya que ahorramos líneas de código y evitamos errores por tipos de dato. al ser este de tipo columna de la tabla. O si se define en el DECLARE.
    
    DECLARE
        CURSOR cur_paises IS
            SELECT COD_PAIS, NOM_PAIS, COD_CONTINENTE FROM PAISES;
        reg_paises cur_paises%ROWTYPE;
    BEGIN
        OPEN cur_paises;
            FETCH cur_paises INTO reg_paises;
            DBMS_OUTPUT.PUT_LINE(reg_PAIS.COD_PAIS || '::' || reg_PAIS.NOM_PAIS);
        CLOSE cur_paises;
    END;
    
    


    Podemos usar los cursores impícitos para actualizar registros o insertarlos, según lo definamos...
    Veamos un ejemplo...
    
    DECLARE
        reg_paises PAISES%ROWTYPE;
    BEGIN
        FOR reg_paises IN ( SELECT * FROM PAISES ) LOOP
            DBMS_OUTPUT.PUT_LINE(reg_paises.NOM_PAIS);
        END LOOP;
    END;
    
    BEGIN
        UPDATE PAISES SET NOM_PAIS = 'COLOMBIA' WHERE COD_PAIS = 57;
        -- También se puede validar con : SQL%ROWCOUNT = 0
        IF SQL%NOTFOUND THEN 
            INSERT INTO PAISES (COD_PAIS, NOM_PAIS, COD_CONTINENTE) VALUES (57,'COLOMBIA', 2);
            COMMIT;
        END IF;
    END;
    
    


    Podemos usar pasarle parámetros al cursor para que haga un filtro especifico o tambíen que sea dinámico el cursor.
    Veamos un ejemplo de cada uno de ellos...
    
    -- Cursor con parámetros...
    DECLARE
        CURSOR cur_pais (
            pty_cod_pais PAIS.COD_PAIS%TYPE
        )IS 
            SELECT * FROM PAIS WHERE COD_PAIS = pty_cod_pais;
    
        lnu_codPais PAIS.COD_PAIS%TYPE;
        lvc_nomPais PAIS.NOM_PAIS%TYPE;
        lnu_codCont PAIS.COD_CONTINENTE%TYPE;
    BEGIN
        OPEN cur_pais(57);
        LOOP
            FETCH cur_pais INTO lnu_codPais, lvc_nomPais, lnu_codCont;
            EXIT WHEN cur_pais%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE (lnu_codPais || '-'|| lvc_nomPais);
            END LOOP;
        CLOSE cur_pais;
    END;
    
    

    
    /* Cursor dinámico con parámetros... */
    DECLARE
        TYPE paisCursorType IS REF CURSOR;
        cur_pais   paisCursorType;
        reg_pais  pais%ROWTYPE;
        lvc_query VARCHAR2(4000);
        
        -- Variable que hace el papel de parámetro  
        lnu_parametro   NUMBER(4) := 57;
    BEGIN
     
        lvc_query := 'SELECT * FROM pais WHERE cod_pais = :j';
         
        OPEN cur_pais FOR lvc_query USING lnu_parametro;
        LOOP
            FETCH cur_pais INTO reg_pais;
            EXIT WHEN cur_pais%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(reg_pais.nom_pais);
        END LOOP;
        CLOSE cur_pais;
    END;
    
    ------------------------------------------------------------------------
    -- Puedes susar la cantidad de parámetros necesarios, siempre y cuando
    -- se declaren en el USING, el nombre asignado en la cadena dinámica
    -- no influye, por lo que le puedes colocar cualquier letra o número
    ------------------------------------------------------------------------
    DECLARE
        TYPE paisCursorType IS REF CURSOR;
        cur_pais   paisCursorType;
        reg_pais  pais%ROWTYPE;
        lvc_query VARCHAR2(4000);
        
        -- Variable que hace el papel de parámetro  
        lnu_parametroPais   NUMBER(4) := 57;
        lnu_parametroCont   NUMBER(4) := 2;
    BEGIN
     
        lvc_query := 'SELECT * FROM pais WHERE cod_pais = :a AND COD_CONTINENTE = :b';
         
        OPEN cur_pais FOR lvc_query USING lnu_parametroPais, lnu_parametroCont;
        LOOP
            FETCH cur_pais INTO reg_pais;
            EXIT WHEN cur_pais%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(reg_pais.nom_pais);
        END LOOP;
        CLOSE cur_pais;
    END;
    
    






    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)

    sgLAhxC





    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.....