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

T5- Solución 5: Estructures de control

1) Ejecute el comando en el archivo lab_05_01.sql para crear la tabla messages. Escriba un bloque PL/SQL para insertar números en la tabla messages.

  CREATE TABLE messages(
    results VARCHAR2(100));

a) Inserte los números del 1 al 10, excluyendo el 6 y el 8.

b) Realice la confirmación antes del final del bloque.

BEGIN
   FOR i in 1..10 LOOP
      IF i = 6 or i = 8 THEN
         null;
      ELSE
         INSERT INTO messages(results)
         VALUES (i);
      END IF;
   END LOOP;
   COMMIT;
END;
/

c) Ejecute una sentencia SELECT para verificar que el bloque PL/SQL ha funcionado.

SELECT * FROM messages;

Resultado: debe ver la siguiente salida:

2) Ejecute el script lab_05_02.sql. Este script crea una tabla emp, que es una réplica de la tabla employees. Modifica la tabla emp para agregar una nueva columna, stars, de tipo de dato VARCHAR2 y tamaño 50. Cree un bloque PL/SQL que inserte un asterisco en la columna stars por cada $1000 del salario del empleado. Guarde el script como lab_05_02_soln.sql.

   CREATE TABLE employees2
   AS
   SELECT *
   FROM employees;

   ALTER TABLE employees2 
   ADD (stars VARCHAR2(50));

a) En la sección de declaraciones del bloque PL/SQL, declare una variable v_empno de tipo emp.employee_id e inicialícela en 176. Declare una variable v_asterisk de tipo emp.stars e inicialícela en NULL. Cree una variable v_sal de tipo emp.salary.

DECLARE

   v_empno employees2.employee_id%TYPE := 176;
   v_asterisk employees2.stars%TYPE := NULL;
   v_sal employees2.salary%TYPE;

b) En la sección ejecutable, escriba lógica para agregar un asterisco (*) a la cadena por cada 1.000 dólares del salario. Por ejemplo, si el empleado gana 8.000 dólares, la cadena de asteriscos debe incluir ocho asteriscos. Si el empleado gana 12.500 dólares, la cadena de asteriscos debe incluir 13 asteriscos.

BEGIN

   SELECT NVL(ROUND(salary/1000), 0) INTO v_sal
   FROM employees2 WHERE employee_id = v_empno;

   FOR i IN 1..v_sal LOOP
      v_asterisk := v_asterisk ||'*';
   END LOOP;

c) Actualice la columna stars para el empleado con la cadena de asteriscos. Realice la confirmación antes del final del bloque.

   UPDATE employees2 SET stars = v_asterisk
   WHERE employee_id = v_empno;
   COMMIT;
END;
/

d) Muestre la fila de la tabla emp para verificar que el bloque PL/SQL se ha ejecutado correctamente.

SELECT employee_id,salary, stars
FROM employees2 WHERE employee_id =176;

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