Oracle

Registros en PL/SQL

Por : Jhons_1101
2017-08-31
Tags :
Las colecciones y registros son tipos compuestos que tienen componentes internos que se pueden manipular individualmente, como los elementos de una matriz, registro o tabla.
Una colección es un grupo ordenado de elementos, todos del mismo tipo. Es un concepto general que abarca listas, matrices y otros tipos de datos utilizados en algoritmos clásicos de programación. Cada elemento es dirigido por un subíndice único.

UN registro es un grupo de datos relacionados almacenados en campos, cada uno con su propio nombre y tipo de datos. Puede pensar en un registro como una variable que puede contener una fila de tabla o algunas columnas de una fila de tabla. Los campos corresponden a columnas de tabla.

Colecciones de PL / SQL


PL/SQL ofrece estos tipos de colección:
  • Los arrays asociativos, también conocidos como tablas de índice, permiten buscar elementos usando números y cadenas arbitrarias para valores de subíndice.
    Estos son similares a las tablas hash en otros lenguajes de programación.

  • Las tablas anidadas contienen un número arbitrario de elementos. Utilizan números secuenciales como subíndices. Puede definir tipos SQL equivalentes, permitiendo que las tablas anidadas se almacenen en tablas de bases de datos y se manipulen a través de SQL.

  • Los Varrays (abreviado para matrices de tamaño variable) contienen un número fijo de elementos (aunque puede cambiar el número de elementos en tiempo de ejecución). Utilizan números secuenciales como subíndices.
    Puede definir tipos SQL equivalentes, permitiendo que los varrays se almacenen en tablas de la base de datos. Pueden almacenarse y recuperarse a través de SQL, pero con menos flexibilidad que las tablas anidadas.

  • Aunque las colecciones sólo tienen una dimensión, puede modelar matrices multidimensionales creando colecciones cuyos elementos son también colecciones.


    Para utilizar colecciones en una aplicación, se deben definir y, a continuación, se deben definir las variables de esos tipos. Puede definir tipos de coleciones en un procedimiento, en una función o un paquete. Para ello, puede pasar variables de colecciónes como parámetros a subprogramas almacenados.

    Para buscar datos más complejos que los valores individuales, puede almacenar registros (Records) o tipos de objetos en las colecciones (Colections). Incluso. Las tablas anidadas y varrays también pueden ser atributos de tipos de objetos.

    Tablas Anidadas


    Las tablas anidadas representan conjuntos de valores. Puede pensar en ellos como matrices unidimensionales sin número declarado de elementos.

    Puede modelar matrices multidimensionales creando tablas anidadas cuyos elementos también son tablas anidadas.

    Dentro de la base de datos, las tablas anidadas son tipos de columna que contienen conjuntos de valores. Oracle almacena las filas de una tabla anidada sin orden particular.
    Cuando recupera una tabla anidada de la base de datos en una variable, las filas reciben subíndices consecutivos comenzando en 1. Esto le da acceso a filas individuales a una matriz.

  • Las tablas anidadas se diferencian de las matrices en estos factores importantes:

  • Las tablas anidadas no tienen un número declarado de elementos, mientras que las matrices tienen un número predefinido.
  • El tamaño de una tabla anidada puede aumentar dinámicamente; sin embargo, se impone un límite máximo.
  • Las tablas anidadas pueden no tener subíndices consecutivos, mientras que las matrices siempre tendrán subíndices consecutivos.
  • Inicialmente, las tablas anidadas son densas (todos los subíndices son consecutivos), pero pueden o no tener subíndices no consecutivos.
  • Se puede eliminar elementos de una tabla anidada utilizando el procedimiento incorporado DELETE. La función incorporada NEXT permite iterar sobre todos los subíndices de una tabla anidada, incluso si la secuencia tiene brechas o espacios en blanco.


  • Array vs tablas anidadas

    Usando Varrays


    Permite hacer referencia a elementos individuales para operaciones con matrices o para manipular una colección como un todo. Para hacer referencia a un elemento, se utiliza la sintaxis Grade.

    Por ejemplo, para hacer referencia al tercer elemento de un Varray utilizaremos [Grade(3)]
    Un varray tiene un tamaño máximo fijo, que se especifica en su definición. Su índice tiene un límite inferior fijo de 1 y un límite superior extensible.
    Por ejemplo, el límite superior actual para varray Grades es 7, pero puede aumentar su límite superior hasta un máximo de 10 o cualquier otro valor. Un Varray puede contener un número variable de elementos, desde cero (cuando está vacío) hasta el máximo especificado en su definición. Siempre se debe definir su limite máximo, por eso se dice que es 'De tamaño máximo fijo, pero extendible'.


    Array Grades en PL/SQL

    
    DECLARE
      type namesarray IS VARRAY(5) OF VARCHAR2(10);
      type grades IS VARRAY(5) OF INTEGER;
      names  namesarray;
      clave  grades;
      total  integer;
    BEGIN
      names  := namesarray('Stiven', 'Juan', 'Pedro', 'Nicole', 'Aurora');
      clave  := grades(98, 97, 78, 87, 92);
      total  := names.count;
      dbms_output.put_line('Total '|| total || ' Estudiantes');
      FOR i in 1 .. total LOOP
        dbms_output.put_line('Estudiante: ' || names(i) || ', Tiene la clave: ' || clave(i));
      END LOOP;
    END;
    
    ------------------------------------------------------------------------------------------------
    -- SALIDA --------------------------------------------------------------------------------------
    Total 5 Estudiantes
    Estudiante: Stiven clave: 98
    Estudiante: Juan clave: 97
    Estudiante: Pedro clave: 78
    Estudiante: Nicole clave: 87
    ------------------------------------------------------------------------------------------------
    -- Recuperado de https://www.tutorialspoint.com/plsql/plsql_arrays.htm
    
    


    ¿Cómo entender las matrices asociativas (Índice por tablas)

    Las matrices asociativas son conjuntos de pares de clave-valor, donde cada clave es única y se usa para ubicar un valor correspondiente en la matriz. La clave puede ser un entero o una cadena.
    Asignar un valor usando la clave (key) por primera vez agrega esa clave a la matriz asociativa. Las asignaciones subsiguientes que utilizan la misma clave actualizan la misma entrada. Es importante elegir una clave que sea única.
    Por ejemplo, los valores clave pueden provenir de la clave primaria de una tabla de base de datos, de una función de hash numérica o de concatenar cadenas para formar un valor de cadena único.


    Lo importante es que la clave sea única en toda la matriz, de lo contrario encontrarás fallos y los valores se mezclarán.


    Por ejemplo, aquí está la declaración de un tipo de matriz asociativa, y dos arreglos de ese tipo, utilizando claves que son cadenas:

    
    DECLARE  TYPE ArrayVentas IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
      ventas_x_ciudad  ArrayVentas;
      ventas_x_pais    ArrayVentas;
      venta            NUMBER;
      which            VARCHAR2(64);
    BEGIN
    -------------------------------------------------------------
    -- Crea una nueva posicion en el array al no exitir los KEY
    -------------------------------------------------------------
      ventas_x_ciudad('Bogotá')           := 100000;
      ventas_x_ciudad('Ciudad de mexico') := 750000;
    
    --------------------------------------------------------------
    -- Buscamos el valor asociado a la KEY
    --------------------------------------------------------------
      venta := ventas_x_ciudad('Bogotá');
      DBMS_OUTPUT.PUT_LINE(venta);
    
      -- Añadimos de nuevo a la matriz
      ventas_x_pais('Colombia')    := 30000000;
      ventas_x_pais('Mexico')      := 100000; -- Crea una KEY nueva
      ventas_x_pais('Mexico')      := 100110; -- Reemplaza el valor en la KEY al ya existir
    
    ------------------------------------------------------------------------------------------------
    -- (FIRST) Devuelve 'Colombia' como aparece primero alfabéticamente.
    ------------------------------------------------------------------------------------------------
      which := ventas_x_pais.FIRST;
      DBMS_OUTPUT.PUT_LINE(which);
    
    ------------------------------------------------------------------------------------------------
    -- (LAST) Devuelve 'Mexico' como viene último alfabéticamente.
    -- De la KEy 'Mexico' dvuelve el valor correspondiente.
    ------------------------------------------------------------------------------------------------
      venta := ventas_x_pais(ventas_x_pais.LAST);
      DBMS_OUTPUT.PUT_LINE(venta);
    END;
    
    ------------------------------------------------------------------------------------------------
    -- SALIDA --------------------------------------------------------------------------------------
    -- 100000
    -- Colombia
    -- 100110
    ------------------------------------------------------------------------------------------------
    
    

    Las matrices asociativas lo ayudan a representar conjuntos de datos de tamaño arbitrario, con búsqueda rápida para un elemento individual sin conocer su posición dentro de la matriz y sin tener que recorrer todos los elementos de la matriz. Es como una versión simple de una tabla SQL donde puede recuperar valores basados ​​en la clave principal.
    Debido a que las matrices asociativas están diseñadas para datos temporales en lugar de almacenar datos persistentes, no puede usarlas con sentencias SQL como INSERTy SELECT INTO.


    Cuando se declara una matriz asociativa utilizando una cadena como clave (KEY), la declaración debe utilizar un tipo de dato VARCHAR2, STRING o LONG. Puede usar un tipo de dato diferente, como NCHAR o NVARCHAR2. Incluso puede utilizar un tipo de dato DATE.
    compartir en facebook compartir en Google compartir en Twitter compartir en Blogger compartir como código embebido compartir la url

    Registros en PL/SQL

    Para crear registros, defina un tipo RECORD y luego declare registros de ese tipo. También puede crear o encontrar una tabla, vista o cursor PL/SQL con los valores que desee, y usar el atributo %ROWTYPE para crear un registro coincidente.

    Puede definir tipos RECORD en la parte declarativa de cualquier bloque, subprograma o paquete PL/SQL. Cuando defina su propio tipo RECORD, puede especificar una restricción NOT NULL en los campos o darles valores predeterminados.
    
    DECLARE
      TYPE RowCiudad IS RECORD (
        codCiudad    NUMBER(4) NOT NULL,
        codDepto     Departamento.cod_departamento%TYPE,
        coPostal     NUMBER (6) DEFAULT NULL,
        nomCiudad    VARCHAR2(100)
      );
      recordCiudad   RowCiudad;
    BEGIN
      recordCiudad.codCiudad  := 76001;
      recordCiudad.codDepto   := 76;
      recordCiudad.coPostal   := 76001;
      recordCiudad.nomCiudad  := 'Santiago de Cali';
      -- Imprimimos cualquier campo del Record
      DBMS_OUTPUT.PUT_LINE(recordCiudad.nomCiudad);
    END;
    
    


    Para almacenar un registro en la base de datos, puede especificarlo en una declaración INSERT o UPDATE si sus campos coinciden con las columnas de la tabla.


    Incluso podemos cargar en un Record información de un Cursor, en donde el Record contendrá el tipo de dato, los valores por defecto y la longitud de las columnas del Cursor.
    Veamos..


    
    DECLARE
      TYPE RowCiudad IS RECORD (
        codCiudad    NUMBER(4) NOT NULL,
        codDepto     Departamento.cod_departamento%TYPE,
        coPostal     NUMBER (6) DEFAULT NULL,
        nomCiudad    VARCHAR2(100)
      );
      recordCiudad   RowCiudad;
    BEGIN
      recordCiudad.codCiudad  := 76001;
      recordCiudad.codDepto   := 76;
      recordCiudad.coPostal   := 76001;
      recordCiudad.nomCiudad  := 'Santiago de Cali';
      -- Imprimimos cualquier campo del Record
      DBMS_OUTPUT.PUT_LINE(recordCiudad.nomCiudad);
    END;
    
    


    También podemos devolver un registro desde una función PL/SQL. Para hacer que el tipo de registro sea visible en múltiples funciones almacenadas y procedimientos almacenados, tenemos que declarar el tipo del registro en la especificación del paquete.
    Veamos...


    
    DECLARE
      -------------------------------------------------------------
      -- Ejemplo de carga de un CURSOR dentro de un ROWTYPE
      -------------------------------------------------------------
      CURSOR curCiudad IS
          SELECT cod_ciudad, nom_ciudad, cod_depto
          FROM ciudad;
      rec1  curCiudad%ROWTYPE;
    
      --------------------------------------------------------------
      -- Podemos definir RECORD con tipos de datos de tabla
      --------------------------------------------------------------
      TYPE DeptRec2 IS RECORD (
          lnu_cod_ciudad      TBL_CIUDAD.cod_ciudad%TYPE,
          lvc_nom_ciudad TBL_CIUDAD.nom_ciudad%TYPE,
          lnu_cod_depto   TBL_CIUDAD.cod_depto%TYPE
      );
      rec2  DeptRec2;
    
      ---------------------------------------------------------------
      -- Podemos definir RECORD con Tipos de Datos especifico
      ---------------------------------------------------------------
      TYPE DeptRec3 IS RECORD (
          lnu_cod_ciudad      NUMBER,
          lvc_nom_ciudad VARCHAR2(50),
          lnu_cod_depto    NUMBER
      );
      rec3 DeptRec3;
    BEGIN
       NULL;
    END;
    
    


    También puede usar la instrucción SELECT o FETCH para obtener valores de columna en un registro. Las columnas en la lista de selección deben aparecer en el mismo orden que los campos en su registro.

    
    DECLARE
      TYPE RecordUsuers IS RECORD (
          lvc_user USUARIOS.usaurio%TYPE,
          lnu_user USUARIOS.cod_usuario%TYPE
      );
     rec1 RecordUsuers;
    BEGIN
     SELECT lvc_user, lnu_user INTO rec1 FROM USUARIOS;
     DBMS_OUTPUT.PUT_LINE('Usuario #' || rec1.lnu_user || ' = ' || rec1.lvc_user);
    END;
    
    

    Los registros no pueden ser probados por nulidad, o comparados por igualdad o desigualdad. Si desea hacer tales comparaciones, escriba su propia función que acepte dos registros como parámetros y haga las comprobaciones o comparaciones apropiadas en los campos correspondientes.



    Inserts con Rowtype


    Podemos trabajar tambien los registros para insertar y modificar registros de una tabla... Se puede insertar esta variable sin especificar una lista de columnas. La declaración %ROWTYPEasegura que los atributos de registro tienen exactamente los mismos nombres y tipos que las columnas de la tabla.
    Miremos el ejemplo práctico..

    
    DECLARE
       rowdepto departamento%ROWTYPE;
    BEGIN
    
    -- depto_id, depto_nombre, y pais_id son las columnas de la tabla
    -- El registro recoge estos nombres del %ROWTYPE
    
      rowdepto.depto_id     := 76;
      rowdepto.depto_nombre := 'valle';
      rowdepto.pais_id      := 57;
    
    -- Usar el %ROWTYPE significa que podemos omitir la lista de columnas.
    -- La fila tendrá valores para las columnas.
    
      INSERT INTO departamento VALUES rowdepto;
    END;
    
    

    También podemos insertar registros por medio de rowtype en tablas que tengan campos (columnas) como NULL y no habrá necesidad de mencionarlos en el Rowtype. Ya que el mapea la tabla con los atributos y propiedades de cada campo.
    Vemos un ejemplo..

    Tabla estudiante PL/SQL
    
    DECLARE
       rowEst estudiante%ROWTYPE;
    BEGIN
    
    -- Los campos nom2_estudiante y ap2_estudiante permite nulos, 
    -- por lo cual, no es necesario nombralos al momento de definir el registro.
    
      rowEst.codEstudiente   := 10;
      rowEst.nom1_estudiante := 'stiven';
      rowEst.ap1_estudiante  := 'vargas';
    
       INSERT INTO estudiante VALUES rowEst;
    END;
    
    



    Update con Rowtype


    La instrucción UPDATE le permite actualizar las filas de la base de datos usando una sola variable de tipo RECORD o ROWTYPE en el lado derecho de la cláusula SET, en lugar de una lista de campos.
    El número de campos en el registro debe ser igual al número de columnas enumeradas en la cláusula SET, y los campos y columnas correspondientes deben tener tipos de datos compatibles.
    Miremos el ejemplo práctico..

    Por defecto, puede usar esta cláusula sólo cuando se use en una fila a la vez.

    Tabla departamento antes de actualizar
    
    DECLARE
      rowdepto departamento%ROWTYPE;
    BEGIN
    
    -- depto_id, depto_nombre, y pais_id son las columnas de la tabla
    -- El registro recoge estos nombres del %ROWTYPE.
    
      rowdepto.depto_id     := 76;
      rowdepto.depto_nombre := 'Valle del cauca';
      rowdepto.pais_id      := 57;
    
    -- Los campos de %ROWTYPE pueden reemplazar completamente el valor de las columnas de la tabla
    -- La fila tendrá valores para las columnas completadas y las demás columnas no nombras 
    -- en el Record tendrán el valor de NULL. Debemos de tener en cuenta si una columna es NOT NULL
    -- ya que nos arrojará un error.. ORA-01407: no se puede actualizar...
    
       UPDATE departamento SET ROW = rowdepto WHERE depto_id = 76;
    END;
    
    

    * Revisemos como quedó el registro en la tabla...
    * Si actualizamos un registro sin definir los campos de tipo NOT NULL, encontraremos el error ORA-01407.

    Tabla departamento actualizado

    Podremos también probar un UPDATE que devuelva los valores aplicados para enviar un mensaje o para usarlo más adelante..
    Miremos un ejemplo claro, en donde modificamos el estudiante que ya existía en DB.

    Tabla estudiante antes de actualizar
    
    DECLARE
      TYPE EmpRec IS RECORD (
        nombreEst     ESTUDIANTE.NOM1_ESTUDIANTE%TYPE, 
        apellidoEst   ESTUDIANTE.AP1_ESTUDIANTE%TYPE
      );
      emp_info EmpRec;
    BEGIN
    
    -- Primero actualizamos el registro normalmente y después de ello,
    -- retornamos cualquier columna declarada en el registro %RECORD...
    
      UPDATE ESTUDIANTE SET NOM1_ESTUDIANTE = UPPER(NOM1_ESTUDIANTE) WHERE CODESTUDIENTE = 10
        RETURNING NOM1_ESTUDIANTE, AP1_ESTUDIANTE INTO emp_info;
      DBMS_OUTPUT.PUT_LINE('La salida es: ' || emp_info.nombreEst || ', ' || emp_info.apellidoEst);
    END;
    
    

    Tabla estudiante antes de actualizar

    Tabla estudiante antes de actualizar

    Condiciones para usar los registros %RECORD en Inserts y Updates


    Existen usa serie de restricciones que aplican a las inserciones / actualizaciones de registros..

    1. Las variables de registro %RECORD solo se permiten en los siguientes lugares.
    * Se podrá usar en el lado derecho de la cláusula SET en una instrucción UPDATE.
    * Se podrá usar en la cláusula VALUES de una instrucción INSERT.
    * Se podrá usar en la subcláusula INTO de una cláusula de RETORNO.
    * La palabra clave ROW está permitida sólo en el lado izquierdo de una cláusula SET. Además, no puede usar ROW con una subconsulta.
    * En una instrucción UPDATE, solo se permite una cláusula SET si se usa ROW.
    * Si la cláusula VALUES de una instrucción INSERT contiene una variable de registro, no se permite ninguna otra variable o valor en la cláusula.
    * Si la subcláusula INTO de una cláusula RETURNING contiene una variable de registro, no se permite ninguna otra variable o valor en el subcláusulas.

    Como lo vimos en los ejemplos anteriores.

    Las variables de registro %RECORD no están permitidas en una lista SELECT, en una cláusula WHERE, en una cláusula GROUP BY o en una cláusula ORDER BY.
    Los siguientes no son compatibles: Tipos de registros anidados, funciones que devuelven un registro, Registro de inserciones y actualizaciones utilizando la sentencia EXECUTE IMMEDIATE.







    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)

    WxI5yYE





    st3v3n1101
    2016-02-16 00:00:00
    viejito, gracias, bien explicado...
    Esta entrada no cuenta con imágenes adjuntas

    Unete al grupo de whatsApp +57 316 392 6456

    Sigue el grupo en facebook

    Siguenos.....