M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T6

T6- Solución 6: Trabajar con Tipos de Dato Compuestos

1) Escriba un bloque PL/SQL para imprimir información sobre un país determinado.

a) Declare una variable v_countryid. Asigne CA a v_countryid.

  SET SERVEROUTPUT ON
 
  SET VERIFY OFF
  DECLARE
    v_countryid varchar2(20):= 'CA';

b) En la sección de declaraciones, utilice el atributo %ROWTYPE y declare la variable v_country_record del tipo countries.

  v_country_record countries%ROWTYPE;

c) En la sección ejecutable, obtenga toda la información de la tabla countries mediante v_countryid. Muestre la información seleccionada sobre el país. La salida de ejemplo es la siguiente:

  BEGIN
    SELECT *
    INTO v_country_record
    FROM countries
    WHERE country_id = UPPER(v_countryid);

    DBMS_OUTPUT.PUT_LINE ('Country Id: ' || v_country_record.country_id ||
                          ' Country Name: ' || v_country_record.country_name ||
                          ' Region: ' || v_country_record.region_id);
END;

d) Puede que desee ejecutar y probar el bloque PL/SQL para los países con los identificadores DE, UK y US.

Solució:

SET SERVEROUTPUT ON
 
SET VERIFY OFF
DECLARE
  v_country_record countries%ROWTYPE;
  v_countryid varchar2(20):= 'CA';
BEGIN
  SELECT *
  INTO v_country_record
  FROM countries
  WHERE country_id = UPPER(v_countryid);

  DBMS_OUTPUT.PUT_LINE ('Country Id: ' || v_country_record.country_id ||
                        ' Country Name: ' || v_country_record.country_name ||
                        ' Region: ' || v_country_record.region_id);
END;

2) Cree un bloque PL/SQL para recuperar los nombres de algunos departamentos de la tabla departments e imprimir el nombre de cada departamento en la pantalla, incorporando una matriz asociativa. Guarde el script como lab_06_02_soln.sql.

a) Declare una tabla INDEX BY dept_table_type del tipo departments.department_name. Declare una variable my_dept_table del tipo dept_table_type para almacenar temporalmente los nombres de los departamentos.

  SET SERVEROUTPUT ON
  DECLARE
    TYPE dept_table_type is table of
         departments.department_name%TYPE
         INDEX BY PLS_INTEGER;
    my_dept_table dept_table_type;

b) Declare dos variables: f_loop_count y v_deptno del tipo NUMBER. Asigne 10 a f_loop_count y 0 a v_deptno.

  f_loop_count NUMBER (2):=10;
  v_deptno NUMBER (4):=0;

c) Con un bucle, recupere los nombres de 10 departamentos y almacene los nombres en la matriz asociativa. Empiece por department_id 10. Aumente v_deptno en 10 para cada iteración del bucle. La siguiente tabla muestra department_id para los que se debe recuperar department_name y almacenar en la matriz asociativa.

  BEGIN
    FOR i IN 1..f_loop_count
    LOOP
      v_deptno:=v_deptno+10;
      SELECT department_name
      INTO my_dept_table(i)
      FROM departments
      WHERE department_id = v_deptno;
    END LOOP;

d) Si utiliza otro bucle, puede recuperar los nombres de los departamentos de la matriz asociativa y mostrarlos.

  FOR i IN 1..f_loop_count
    LOOP
      DBMS_OUTPUT.PUT_LINE (my_dept_table(i));
    END LOOP;
  END;

e) Ejecute y guarde el script como lab_06_02_soln.sql. La salida es la siguiente:

Solució:

SET SERVEROUTPUT ON
 
SET VERIFY OFF
DECLARE
  TYPE dept_table_type is table of
       departments.department_name%TYPE
       INDEX BY PLS_INTEGER;
  my_dept_table dept_table_type;
  f_loop_count NUMBER (2):=10;
  v_deptno NUMBER (4):=0;

BEGIN
  FOR i IN 1..f_loop_count
  LOOP
    v_deptno:=v_deptno+10;
    SELECT department_name
    INTO my_dept_table(i)
    FROM departments
    WHERE department_id = v_deptno;
  END LOOP;

  FOR i IN 1..f_loop_count
  LOOP
    DBMS_OUTPUT.PUT_LINE (my_dept_table(i));
  END LOOP;
END;

3) Modifique el bloque creado en la Práctica 2 para recuperar toda la información acerca de cada departamento de la tabla departments y mostrarla. Utilice una matriz asociativa con el método de tabla de registros INDEX BY.

a) Cargue el script lab_06_02_soln.sql.

b) Ha declarado que la matriz asociativa sea del tipo departments.department_name. Modifique la declaración de la matriz

asociativa para almacenar temporalmente el número, el nombre y la ubicación de todos los departamentos. Utilice el atributo %ROWTYPE.

  SET SERVEROUTPUT ON

  DECLARE
    TYPE dept_table_type is table of departments%ROWTYPE
      INDEX BY PLS_INTEGER;
    my_dept_table dept_table_type;
    f_loop_count NUMBER (2):=10;
    v_deptno NUMBER (4):=0;

c) Modifique la sentencia SELECT para recuperar toda la información del departamento que está en la tabla departments y almacenarla en la matriz asociativa.

BEGIN
  FOR i IN 1..f_loop_count
  LOOP
    v_deptno := v_deptno + 10;
    SELECT *
    INTO my_dept_table(i)
    FROM departments
    WHERE department_id = v_deptno;
  END LOOP;

d) Si utiliza otro bucle, puede recuperar la información de los departamentos de la matriz asociativa y mostrarla.

  FOR i IN 1..f_loop_count
    LOOP
      DBMS_OUTPUT.PUT_LINE ('Department Number: ' || my_dept_table(i).department_id || ' Department Name: ' ||
                            my_dept_table(i).department_name || ' Manager Id: '|| my_dept_table(i).manager_id || 
                            ' Location Id: ' || my_dept_table(i).location_id);
    END LOOP;
  END;

La salida de ejemplo es la siguiente:

Solució:

SET SERVEROUTPUT ON
 
SET VERIFY OFF
DECLARE
  TYPE dept_table_type is table of departments%ROWTYPE
       INDEX BY PLS_INTEGER;
  my_dept_table dept_table_type;
  f_loop_count NUMBER (2):=10;
  v_deptno NUMBER (4):=0;

BEGIN
  FOR i IN 1..f_loop_count
  LOOP
    v_deptno:=v_deptno+10;
    SELECT *
    INTO my_dept_table(i)
    FROM departments
    WHERE department_id = v_deptno;
  END LOOP;

  FOR i IN 1..f_loop_count
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Department Number: ' || my_dept_table(i).department_id || ' Department Name: ' ||
                          my_dept_table(i).department_name || ' Manager Id: '|| my_dept_table(i).manager_id || 
                          ' Location Id: ' || my_dept_table(i).location_id);
  END LOOP;
END;