PL/SQL : Tipos de datos compuestos

Los tipos de datos compuestos o colecciones pueden ser o bien registros PL/SQL o bien tablas PL/SQL

Un registro PL/SQL es un grupo de elementos de datos relacionados en campos. Cada uno con su propio nombre.

• Cada registro debe tener al menos un campo y tantos como sea necesario
• Un registro NO es una fila de una tabla de sql
• Son tratados como unidades lógicas
• Son apropiados para recuperar el contenido de una fila de una tabla sql.
• Son tipos de variables. Por lo que hay que declarar primero el tipo de variable para después poder usarla

TYPE nombre IS RECORD (DECLARACIÓN DE CAMPOS)

donde (declaración de campos) es:

nombre_de_campo {tipo de dato}

donde {tipo de dato} puede ser:

nombre_de_campo{tipo de dato | variable%TYPE | tabla.columna%TYPE| tabla%ROWTYPE} [ [NOT NULL]{:= | DEFAULT} expresion]

DECLARE            
      TYPE currante_tipo_de_record IS RECORD 
	(nombre varchar2(20),
	curro varchar2(10),
	cobra number(7,2));  -- hasta aqui he declarado el tipo de variable
      currantes  currante_tipo_de_record ; -- aqui uso la variable

BEGIN
	SELECT first_name, job_id, salary	
	INTO currantes
	FROM employees
	WHERE employee_id = 200;	
END;
/

A los campos de un registro pl/sql se accede por el nombre. Así en el registro usado en el ejemplo anterior se accedería mediante currantes.cobra.

Es lo mismo que %TYPE pero en este caso sirve para declarar que una variable es igual que una linea de una tabla. Osea, que la variable contiene tantos campos y del mismo tipo que una tabla. Tiene la ventaja que no tienes que declarar el tipo de varable y puedes usarlo directamente. Ademas de estar seguro de que no vas a tener problemas de tipos distintos entre el origen de los datos y tu registro

DECLARE            
      currantes  employees%ROWTYPE; -- aqui uso la variable
BEGIN
	SELECT *	
	INTO currantes
	FROM employees
	WHERE employee_id = 200;	
END;
/

Una tabla PL/SQL es un como una tabla normal de la base de datos con las siguientes carácteristicas

• Debe contener una clave primaria del tipo BINARY_INTEGER
• Debe contener una columna de tipo escalar o de registro

TYPE nombre_de_tipo_de_tabla IS TABLE OF
{tipo de columna | variable%TYPE | tabla.columna%TYPE }[NOT NULL]  INDEX BY BINARY_INTEGER;

SET PAGESIZE 100000
DECLARE           
	TYPE tabla_como_empleados IS TABLE OF employees%ROWTYPE
	INDEX BY BINARY_INTEGER;
	currelas  tabla_como_empleados;
	c_max INTEGER;
BEGIN
	select  count(*)
	into  c_max
	from employees;
	DBMS_OUTPUT.PUT_LINE('');
	DBMS_OUTPUT.PUT_LINE('number of employees' || c_max);

	-- start of the bucle....

	FOR i IN 1..c_max  -- tantas vueltas como valores
	LOOP
    	   SELECT * INTO currelas(i) FROM employees -- en cada vuelta inserto una linea
    	   WHERE employee_id=(99+i); -- el primer empleado es el 100...
	   DBMS_OUTPUT.PUT_LINE('getting employe : ' || (99+i));
	END LOOP;
	
-- printing employees...

	FOR i IN 1..c_max  -- tantas vueltas como valores
	LOOP
    	        DBMS_OUTPUT.PUT_LINE(currelas(i).last_name);


	END LOOP;
END;
/

Al ejecutar una sentencia SQL Oracle abre siempre un cursor para ejecutar operaciones con cada registro que devuelve. Igualmente el programador también puede declarar explícitamente un cursor que le servirá para controlar el resultado de una sentencia SQL linea a linea. Un programa PL/SQL abre un cursor, procesa las filas devueltas por la consulta y después cierra el cursor. El cursor marca la posición actual.

  RESULTADO DE UN SELECT:
  1, AA, PEPE 
  2, BB, TOTO        <== CURSOR =======  (fila actual)
  3, CC, TATA
  4, DD, CACA 

Funciones del cursor:

• Puede procesar más allá de la primera fila devuelta por la consulta, fila por fila
• Comprueba que fila está siendo procesada actualmente
• Permite el control manual del bloque PL/SQL

Control del cursor:

1. Declaración explícita del cursor definiendo la estructura de la consulta que va a ejecutar.
2. Opertura del cursor por medio de la sentencia OPEN que ejecuta la consulta.
3. Por medio de la sentencia FETCH se recupera la fila actual del cursor y se carga en las variables. Cada recuperación hace que el cursor mueva el puntero hacia la fila siguiente. En caso de llegar al final cierra el cursor
4. Mediante la sentencia CLOSE se cierra el cursor y se libera la memoria

CURSOR nombre_del_cursor IS
       consulta select SQL;

Mediante la sentencia open se analiza y ejecuta el select definido en el cursor y se cargan los resultados en memoria.

• Recupera los valores de la fila actual y los introduce en las variables de salida
• Relaciona posicionalmente las variables y las columnas
• Mueve el cursor a la fila siguiente con cada llamada. En caso de llegar al final cierra el cursor.

IMPORTANTE: Un cursor carga los valores recuperados en variables

Cierra el cursor una vez completado su procesamiento y libera la memoria.

• %ISOPEN devuelve BOOLEANO indicando si el cursor esta abierto
• %NOTFOUND devuelve BOOLEANO indicando si se ha recuperado alguna fila
• %FOUND devuelve BOOLEANO indicando si se ha recuperado alguna fila
• %ROWCOUNT devuelve NUMERO con la cantidad de filas devueltas

SET PAGESIZE 10000;
SET SERVEROUTPUT ON;
DECLARE
	v_nombre 	employees.first_name%TYPE;  -- declaro las variables donde voy a cargar los resultados
	v_apellido 	employees.last_name%TYPE;
	CURSOR cursor1 IS        	            -- declaro el cursor
		select first_name, last_name  
		from employees
		where salary < 3000
		order by last_name;
BEGIN
	OPEN cursor1;  -- abro el cursor
	
	LOOP 
		FETCH cursor1 into v_nombre, v_apellido;     -- carga de la linea en las variables
	 	DBMS_OUTPUT.PUT_LINE('Nombre : ' || v_nombre || ' Apellido : ' || v_apellido); -- tratamineto de las variables yo las saco por pantalla, pero evidentemente puedes  hacer lo que te de la gana.
		EXIT WHEN  cursor1%NOTFOUND;  -- clausula de salida
	END LOOP;

	CLOSE cursor1; -- lo cierro
END;
/

DECLARE
 CURSOR cur_emp IS
 SELECT num_empleado, nomb_empleado FROM empleado;
BEGIN
 FOR emp_registro IN cur_emp LOOP
  -- Apertura y declaración del cursor ímplitamente
   IF emp_registro.edad>30 THEN 
    ..
   END IF;
 END LOOP;
 -- Cierre ímplicito del cursor
END;