udlaDBgrupo: TAREA6.

Con su grupo de trabajo crear (inventar) ejemplos, desarrollar y publicar en su blog, el código correspondiente del o los procedimientos almacenados que utilicen las siguientes instrucciones: ( publique el código y las referencias en donde se inspiró),

Varray,
Record,
Table,
instruccion case
%type
%Rowtype

VARRAYS

Definición de VARRAYS.

Un varray se manipula de forma muy similar a las tablas de PL, pero se implementa de forma diferente. Los elementos en el varray se almacenan comenzando en el índice 1 hasta la longitud máxima declarada en el tipo varray.

La sintaxis general es la siguiente:


TYPE <nombre_tipo> IS VARRAY (<tamaño_maximo>) OF <tipo_elementos>;

Una consideración a tener en cuenta es que en la declaración de un varray el tipo de datos no puede ser de los siguientes tipos de datos:

BOOLEAN
NCHAR
NCLOB
NVARCHAR(n)
REF CURSOR
TABLE
VARRAY

Sin embargo se puede especificar el tipo utilizando los atributos %TYPE y %ROWTYPE.

Los VARRAY deben estar inicializados antes de poder utilizarse. Para inicializar un VARRAY se utiliza un constructor (podemos inicializar el VARRAY en la sección DECLARE o bien dentro del cuerpo del bloque):

DECLARE
    /* Declaramos el tipo VARRAY de cinco elementos VARCHAR2*/
    TYPE t_cadena IS VARRAY(5) OF VARCHAR2(50);
    /* Asignamos los valores con un constructor */
    v_lista t_cadena:= t_cadena('Aitor', 'Alicia', 'Pedro','','');
BEGIN
    v_lista(4) := 'Tita';
    v_lista(5) := 'Ainhoa';
END;

El tamaño de un VARRAY se establece mediante el número de parámetros utilizados en el constructor, si declaramos un VARRAY de cinco elementos pero al inicializarlo pasamos sólo tres parámetros al constructor, el tamaño del VARRAY será tres. Si se hacen asignaciones a elementos que queden fuera del rango se producirá un error.

El tamaño de un VARRAY podrá aumentarse utilizando la función EXTEND, pero nunca con mayor dimensión que la definida en la declaración del tipo. Por ejemplo, la variable v_lista que sólo tiene 3 valores definidos por lo que se podría ampliar hasta cinco elementos pero no más allá.

Un VARRAY comparte con las tablas de PL todas las funciones válidas para ellas, pero añade las siguientes:

LIMIT . Devuelve el número maximo de elementos que admite el VARRAY.
EXTEND .Añade un elemento al VARRAY.
EXTEND(n) .Añade (n) elementos al VARRAY.

RECORDS

Declaración de un registro(Record).

Un registro es una estructura de datos en PL/SQL, almacenados en campos, cada uno de los cuales tiene su propio nombre y tipo y que se tratan como una sola unidad lógica.

Los campos de un registro pueden ser inicializados y pueden ser definidos como NOT NULL. Aquellos campos que no sean inicializados explícitamente, se inicializarán a NULL.

La sintaxis general es la siguiente:

TYPE <nombre> IS RECORD 
(
campo <tipo_datos> [NULL | NOT NULL] 
[,<tipo_datos>...]
);

El siguiente ejemplo crea un tipo PAIS, que tiene como campos el código, el nombre y el continente.


TYPE PAIS IS RECORD 
(
CO_PAIS     NUMBER  ,
DESCRIPCION VARCHAR2(50),
CONTINENTE  VARCHAR2(20)

);

Los registros son un tipo de datos, por lo que podremos declarar variables de dicho tipo de datos.


DECLARE
 
  TYPE PAIS IS RECORD 
  (
    CO_PAIS     NUMBER  ,
    DESCRIPCION VARCHAR2(50),
    CONTINENTE  VARCHAR2(20)
  );

/* Declara una variable identificada por miPAIS de tipo PAIS 
   Esto significa que la variable miPAIS tendrá los campos 
   ID, DESCRIPCION y CONTINENTE.

 */
  miPAIS PAIS;
BEGIN
/* Asignamos valores a los campos de la variable.

 */
  miPAIS.CO_PAIS := 27;
  miPAIS.DESCRIPCION := 'ITALIA';
  miPAIS.CONTINENTE  := 'EUROPA';
END;

Los registros pueden estar anidados. Es decir, un campo de un registro puede ser de un tipo de dato de otro registro.

DECLARE 
 TYPE PAIS IS RECORD 
 (CO_PAIS     NUMBER  ,
  DESCRIPCION VARCHAR2(50),
  CONTINENTE  VARCHAR2(20)
 );

 TYPE MONEDA IS RECORD
 ( DESCRIPCION VARCHAR2(50),
   PAIS_MONEDA PAIS ); 
 
 miPAIS   PAIS; 
 miMONEDA MONEDA;
BEGIN
     /* Sentencias
      */
END;

Pueden asignarse todos los campos de un registro utilizando una sentencia SELECT. En este caso hay que tener cuidado en especificar las columnas en el orden conveniente según la declaración de los campos del registro. Para este tipo de asignación es muy frecuente el uso del atributo %ROWTYPE que veremos más adelante.


SELECT  CO_PAIS, DESCRIPCION, CONTINENTE
INTO miPAIS
FROM PAISES
WHERE CO_PAIS = 27;

Puede asignarse un registro a otro cuando sean del mismo tipo:


DECLARE
 
  TYPE PAIS IS RECORD ...
  miPAIS PAIS;
  otroPAIS PAIS;
BEGIN

  miPAIS.CO_PAIS := 27;
  miPAIS.DESCRIPCION := 'ITALIA';
  miPAIS.CONTINENTE  := 'EUROPA';
  otroPAIS := miPAIS;  
END;

TABLAS

Declaración de tablas de PL/SQL

Las tablas de PL/SQL son tipos de datos que nos permiten almacenar varios valores del mismo tipo de datos.

Una tabla PL/SQL :

Es similar a un array
Tiene dos componenetes: Un índice de tipo BINARY_INTEGER que permite acceder a los elementos en la tabla PL/SQL y una columna de escalares o registros que contiene los valores de la tabla PL/SQL
Puede incrementar su tamaño dinámicamente.

La sintaxis general para declarar una tabla de PL es la siguiente:

TYPE <nombre_tipo_tabla> IS TABLE OF
<tipo_datos> [NOT NULL]
INDEX BY BINARY_INTEGER ;

Una vez que hemos definido el tipo, podemos declarar variables y asignarle valores.


DECLARE
   /* Definimos el tipo PAISES como tabla PL/SQL */
   TYPE PAISES IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ; 
   /* Declaramos una variable del tipo PAISES */
   tPAISES PAISES;
BEGIN   tPAISES(1) := 1;
   tPAISES(2) := 2;
   tPAISES(3) := 3;
END;

No es posible inicializar las tablas en la inicialización.

El rango de binary integer es –2147483647.. 2147483647, por lo tanto el índice puede ser negativo, lo cual indica que el índice del primer valor no tiene que ser necesariamente el cero.

Tablas PL/SQL de registros

Es posible declarar elementos de una tabla PL/SQL como de tipo registro.


DECLARE
 
  TYPE PAIS IS RECORD 
  (
    CO_PAIS     NUMBER NOT NULL ,
    DESCRIPCION VARCHAR2(50),
    CONTINENTE  VARCHAR2(20)
  );  
  TYPE PAISES IS TABLE OF PAIS INDEX BY BINARY_INTEGER ;
  tPAISES PAISES;
BEGIN

  tPAISES(1).CO_PAIS := 27;
  tPAISES(1).DESCRIPCION := 'ITALIA';
  tPAISES(1).CONTINENTE  := 'EUROPA';
END;

Funciones para el manejo de tablas PL/SQL

Cuando trabajamos con tablas de PL podemos utilizar las siguientes funciones:

FIRST. Devuelve el menor índice de la tabla. NULL si está vacía.
LAST. Devuelve el mayor índice de la tabla. NULL si está vacía.

El siguiente ejemplo muestra el uso de FIRST y LAST :

DECLARE 
 TYPE ARR_CIUDADES IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
 misCiudades ARR_CIUDADES;

BEGIN
     misCiudades(1) := 'MADRID';
     misCiudades(2) := 'BILBAO';
     misCiudades(3) := 'MALAGA';
     
     FOR i IN misCiudades.FIRST..misCiudades.LAST
     LOOP
         dbms_output.put_line(misCiudades(i));
     END LOOP;
END;

EXISTS(i). Utilizada para saber si en un cierto índice hay almacenado un valor. Devolverá TRUE si en el índice i hay un valor.

DECLARE 
 TYPE ARR_CIUDADES IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
 misCiudades ARR_CIUDADES;

BEGIN     misCiudades(1) := 'MADRID';     
     misCiudades(3) := 'MALAGA';
     
     FOR i IN misCiudades.FIRST..misCiudades.LAST
     LOOP         IF misCiudades.EXISTS(i) THEN
           dbms_output.put_line(misCiudades(i));
         ELSE
           dbms_output.put_line('El elemento no existe:'||TO_CHAR(i));
         END IF; 
     END LOOP;
END;

COUNT. Devuelve el número de elementos de la tabla PL/SQL.

DECLARE 
 TYPE ARR_CIUDADES IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
 misCiudades ARR_CIUDADES;

BEGIN
     misCiudades(1) := 'MADRID';     
     misCiudades(3) := 'MALAGA';
     /* Devuelve 2, ya que solo hay dos elementos con valor */
     dbms_output.put_line(
     'El número de elementos es:'||misCiudades.COUNT);    
END;

PRIOR (n). Devuelve el número del índice anterior a n en la tabla.

DECLARE
  TYPE ARR_CIUDADES IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  misCiudades ARR_CIUDADES;
BEGIN  misCiudades(1) := 'MADRID';
  misCiudades(3) := 'MALAGA'; 
  /* Devuelve 1, ya que el elemento 2 no existe */
  dbms_output.put_line(
  'El elemento previo a 3 es:' || misCiudades.PRIOR(3));
END;

NEXT (n). Devuelve el número del índice posterior a n en la tabla.

DECLARE
  TYPE ARR_CIUDADES IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  misCiudades ARR_CIUDADES;
BEGIN  misCiudades(1) := 'MADRID';
  misCiudades(3) := 'MALAGA'; 
  /* Devuelve 3, ya que el elemento 2 no existe */  dbms_output.put_line(
  'El elemento siguiente es:' || misCiudades.NEXT(1));
END;

TRIM. Borra un elemento del final de la tabla PL/SQL.
TRIM(n) borra n elementos del final de la tabla PL/SQL.
DELETE. Borra todos los elementos de la tabla PL/SQL.
DELETE(n) borra el correspondiente al índice n.
DELETE(m,n) borra los elementos entre m y n.

CASE

Estructura condicional CASE

La estructura condicional CASE permite evaluar una expresion y devolver un valor u otro.

La sintaxis general de case es:

    CASE <expresion>
        WHEN <valor_expresion> THEN <valor_devuelto>
        WHEN <valor_expresion> THEN <valor_devuelto>
        ELSE <valor_devuelto>  -- Valor por defecto
    END

Ejemplo de CASE.

    DECLARE @Web varchar(100),
            @diminutivo varchar(3)

    SET @diminutivo = 'DJK'    SET @Web = (CASE @diminutivo
                    WHEN 'DJK' THEN 'www.devjoker.com'
                    WHEN 'ALM' THEN 'www.aleamedia.com'
                    ELSE 'www.devjoker.com'
                END)

    PRINT @Web

Otra sintaxis de CASE nos permite evaluar diferentes expresiones:

    CASE 
        WHEN <expresion> = <valor_expresion> THEN <valor_devuelto>
        WHEN <expresion> = <valor_expresion> THEN <valor_devuelto>
        ELSE <valor_devuelto> -- Valor por defecto
    END

El mismo ejemplo aplicando esta sintaxis:

    DECLARE @Web varchar(100),
            @diminutivo varchar(3)    SET @diminutivo = 'DJK'
     SET @Web = (CASE 
                    WHEN @diminutivo = 'DJK' THEN 'www.devjoker.com'
                    WHEN @diminutivo = 'ALM' THEN 'www.aleamedia.com'
                    ELSE 'www.devjoker.com'
                END)
    PRINT @Web

Otro aspecto muy interesante de CASE es que permite el uso de subconsultas.

    DECLARE @Web varchar(100),
            @diminutivo varchar(3)    SET @diminutivo = 'DJK'
     SET @Web = (CASE 
                    WHEN @diminutivo = 'DJK' THEN (SELECT  web
                                                   FROM WEBS 
                                                   WHERE id=1)
                    WHEN @diminutivo = 'ALM' THEN (SELECT  web
                                                   FROM WEBS 
                                                   WHERE id=2)
                    ELSE 'www.devjoker.com'
                END)
    PRINT @Web

%TYPE

El atributo %TYPE permite conocer el tipo de una variable, constante o campo de la base de datos.

La sintaxis %TYPE:

tipo_campo|variable%TYPE

customers.name%TYPE

Por ejemplo:

DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = '|| v_EmpName);
END;

%ROWTYPE

El atributo%ROWTYPE permite obtener los tipos de todos los campos de una tabla de la base de datos, de una vista o de un cursor.

Por ejemplo, si tengo una tabla PAISES declarada como:

CREATE TABLE PAISES(
CO_PAIS          NUMBER,
DESCRIPCION      VARCHAR2(50),
CONTINENTE       VARCHAR2(20) );

Puedo declarar una variable de tipo registro como PAISES%ROWTYPE;

DECLARE  miPAIS PAISES%ROWTYPE;
BEGIN
  /* Sentencias ... */
END;

Lo cual significa que el registro miPAIS tendrá la siguiente estructura: CO_PAIS NUMBER, DESCRIPCION VARCHAR2(50), CONTINENTE VARCHAR2(20).

De esta forma se crea el registro de forma dinamic y se podrán asignar valores a los campos de un registro a través de un select sobre la tabla, vista o cursor a partir de la cual se creo el registro.

http://www.devjoker.com/gru/tutorial-PL-SQL/PLSQ/Tutorial-PL-SQL.aspx

udlaDBgrupo

sábado, 14 de junio de 2014

TAREA6.