Curso PL / SQL
Resolución de ejercicios

Contenido

Ejercicio Nº 1 : Estructuras IF ELSE

Desarrollar un bloque en anónimo que utilice una variable de sustitución.
El programa solicitará la inclusión de un número entre 0 y 100 (si el valor esta fuera de este rango, se mostrara un mensaje que indicará que valor no es válido).
En el caso de que el valor estén en el rango de 0 a 100, comprobaremos el número y si es igual a 10, 20 o 30, el programa nos mostrara un mensaje indicándonos que hemos ganado el primer premio, segundo premio o tercer premio respectivamente, finalmente si el número es diferente a estos 3, nos indicará que el número no ha sido agraciado.

				
					-- Declaramos la variable
DECLARE 
    -- EL valor de la variable será introducido por el usuario.
    valor NUMBER(3) := &introducir_valor; 
    -- El número introducido se guardará en la variable 'valor'.

-- Flujo del programa.
BEGIN
    IF valor >=0 AND <=100 THEN -- Si el número está entre el 0 y el 100 entonces:
        IF(valor=10) THEN 
            -- Si el valor es igual a 10 entonces:
            DBMS_OUTPUT.PUT_LINE('Primer premio.');
            -- Reportamos el mensaje por pantalla.
        ELSIF(valor=20) THEN
            -- Si el valor no es igual a 10 pero es igual a 20, entonces:
            DBMS_OUTPUT.PUT_LINE('Segundo Premio');
            -- Si es igual a 20 reportaremos el segundo, y así sucesivamente.
        ELSIF(valor=30) THEN
            DBMS_OUTPUT.PUT_LINE('Tercer Premio');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Oh que lástima, el número '||valor||' no ha sido premiado');
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE('El número introducido no esta entre el 0 y el 100');
    END IF;
END;

Ejercicio Nº 2 : Estructuras IF ELSE

En este ejercicio realizaremos un bloque anónimo en PL/SQL con los bucles LOOP, WHILE y FOR. Cada bucle se ejecutará de forma cíclica 10 veces y mostrara por pantalla el tipo de bucle que estamos usando y la iteración ejecutada, es decir, para cada bucle obtendremos la siguiente salida:
Estoy dentro del bucle LOOP en la iteración: 1
Estoy dentro del bucle LOOP en la iteración: 2
…
Bucle LOOP en la iteración: 10

Bucle WHILE en la iteración: 1
Bucle WHILE en la iteración: 2
…
Bucle WHILE en la iteración: 10

Bucle FOR en la iteración: 1
Estoy dentro del bucle FOR en la iteración: 2
…
Estoy dentro del bucle FOR en la iteración: 10
En definitiva los 3 bloques ejecutarán exactamente la misma tarea.

				
					-- Activamos la salida del servidor
SET SERVEROUTPUT ON;

DECLARE
    contador NUMBER(6) := 1;
BEGIN
    -- Bucle LOOP
    LOOP
        DBMS_OUTPUT.PUT_LINE('Bucle LOOP en la iteración: ' || TO_CHAR(contador));
        contador := contador + 1;
        EXIT WHEN contador > 10;
    END LOOP;
END;
				
			
				
					-- Activamos la salida del servidor
SET SERVEROUTPUT ON;

DECLARE
    contador NUMBER(6) := 1;
BEGIN
    --Bucle WHILE
    WHILE contador <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE('Bucle WHILE en la iteración: ' || TO_CHAR(contador));
        contador := contador +1;
    END LOOP;

END;

				
			
				
					-- Activamos la salida del servidor
SET SERVEROUTPUT ON;

DECLARE
    contador NUMBER(6) := 1;
BEGIN
    -- BULE FOR
    FOR contador IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Bucle FOR en la iteración: '|| TO_CHAR(contador));
        contador = contador +1;
    END LOOP;
END;

Ejercicio Nº 3 : Cursores

El siguiente caso práctico se basará en esta base de datos, sobre el cual se solicitará crear un cursor.

  • Crear un cursor, basado en el primer esquema, que muestre la relación de cursos y notas obtenidas por curso (Código alumno, Nombre, Apellidos, Curso, Nota Parcial, Practicas, Nota Final y Promedio Final). Ingresar como parámetro el código del alumno. En caso de no encontrar registros, indicar que el alumno no existe.
				
					-- Declaración del cursor 'c_cursos'
DECLARE CURSOR c_cursos(cod_alumno IN VARCHAR2) IS
        -- Seleccionamos los datos del alumno
        SELECT a.codigoalumno, a.nombre, a.apellidos, c.cursos, n.notaparcial, n.notapracticas, n.notafinal, n.promediofinal
        FROM alumnos a 
        INNER JOIN notas n ON a.codigoalumno = n.codigoalumno
        INNER JOIN cursos c ON c.codigocurso = n.codigocurso
        WHERE a.codigoalumno = cod_alumno;
    
    -- Declaramos la variable donde almacenar los datos    
    c_registro c_cursos%ROWTYPE;
    
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Relación de cursos con notas ' || c_registro.curso);
    FOR c_registro IN c_cursos(&cod_alumno)
    LOOP
        DBMS_OUTPUT.PUT_LINE (c_registro.codigoalumno || ' ' ||
                              c_registro.nombre || ' ' ||
                              c_registro.curso || ' ' ||
                              c_registro.notaparcial || ' ' ||
                              c_registro.notafinal || ' ' ||
                              c_registro.promediofinal);
    END LOOP;
    
-- Declaramos una excepción para el caso en el que no exista un alumno.
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('El alumno no existe');

END;

Ejercicio Nº 4 : Funciones

Sobre el caso práctico de la base de datos del diagrama HR crear las diferentes funciones que se solicitan.

  • Creación de una función para sumar dos números, visualizando el resultado directamente y también almacenándolo para mostrarlo posteriormente.
  •  Almacenar la función anterior en la base de datos.
  •  Utilizar la función almacenada anteriormente en bloques anónimos.
  •  Realizar una consulta que nos muestre el código de trabajo, el nombre del trabajo, el salario mínimo, el salario máximo junto con la suma de estos dos trabajos, utilizando la función almacenada.

Punto 1

Creación de una función para sumar dos números, visualizando el resultado directamente y también almacenándolo para mostrarlo posteriormente.

				
					DECLARE
-- Declaramos las variables.
    var_suma NUMBER;
    
-- Declaramos la función Suma.    
FUNCTION suma(n1 NUMBER, n2 NUMBER)
RETURN NUMBER IS
-- Creamos la variable 'result' para almacenar el resultado de la operación
    result NUMBER; 
BEGIN
    -- Realizamos la operación
    result := n1 + n2;
    RETURN result; -- Devolvemos el valor del resultado
END;
BEGIN
    -- Caso1: Utilizando la función directamente.
    DBMS_OUTPUT.PUT_LINE('La suma es: ' || suma(20,40));
    
    -- Caso2: Almacenando el valor en una variable.
    var_sumar := suma(100,200);
    --Reportarlo por pantalla.
    DBMS_OUTPUT.PUT_LINE('La suma es: ' || var_suma); 
END;

Punto 2

Almacenar la función anterior en la base de datos.

				
					 -- Utilizamos 'CREATE OR REPLACE' para almacenar la función. 
CREATE OR REPLACE FUNCTION suma(n1 NUMBER, n2 NUMBER)
RETURN NUMBER IS
    result NUMBER; 
BEGIN
    result := n1 + n2;
    RETURN result; 
END;

Punto 3

 Utilizar la función almacenada anteriormente en bloques anónimos.

				
					DECLARE
    var_suma NUMBER;
BEGIN
    var_sumar := suma(300,50);
    DBMS_OUTPUT.PUT_LINE('La suma es: ' || var_suma); 
END;

Punto 4

Realizar una consulta que nos muestre el código de trabajo, el nombre del trabajo, el salario mínimo, el salario máximo junto con la suma de estos dos trabajos, utilizando la función almacenada.

				
					SELECT job_id,
       job_name,
       min_salary,
       max_salary,
       suma_salary(min_salary,max_salary) as "Suma Salarios" 
FROM jobs;

Ejercicio Nº 5 : Procedimientos en PL / SQL

Durante esta unidad se ha estudiado como implementar procedimientos como bloques PL/SQL que realizan una tarea concreta u acción sobre los datos almacenados en las tablas de un esquema.
Para practicar todos estos conceptos se propone la realización de los siguientes casos prácticos.
Lista de operaciones:

  • Cree un procedimiento denominado ADD_JOB para insertar un nuevo trabajo en la tabla JOBS. Se deben proporcionar el identificador del trabajo (JOB_ID) y el título del trabajo (JOB_TITLE) por medio de parámetros. Pruebe el procedimiento implementado anteriormente mediante la sentencia EXECUTE y mediante un bloque anónimo.
  • Cree un procedimiento denominado UP_JOB para actualizar el título asignado a un trabajo (JOB_TITLE) a partir de si identificador de trabajo (JOB_ID), esta información debe ser enviada a través de parámetros. Una vez compilador el procedimiento UP_JOB, pruebe dicho procedimiento a través del paso de parámetro por nombre.
  • Cree un procedimiento denominado info_emp que obtenga el nombre y el salario de un empleado a través de su id. Cree tantos parámetros como crea necesario y establezca si son parámetros de tipo IN o de tipo OUT en función de sus necesidades.

Punto 1

Cree un procedimiento denominado ADD_JOB para insertar un nuevo trabajo en la tabla JOBS. Se deben proporcionar el identificador del trabajo (JOB_ID) y el título del trabajo (JOB_TITLE) por medio de parámetros. Pruebe el procedimiento implementado anteriormente mediante la sentencia EXECUTE y mediante un bloque anónim. 

				
					CREATE OR REPLACE PROCEDURE add_job(
    j_id IN jobs.job_id%TYPE, 
    j_tit IN jobs.job_title%TYPE
) IS
BEGIN
    INSERT INTO jobs(job_id, job_title) 
    VALUES(j_id, j_tit);
    DBMS_OUTPUT.PUT_LINE('Se ha insertado un nuevo trabajador con ID ' || j_id || ' y título ' || j_tit);
END add_job;

  • CREATE OR REPLACE PROCEDURE add_job(…)

    • Esta es la declaración principal del procedimiento. Comienza con la palabra clave CREATE que se utiliza para crear un nuevo objeto en la base de datos. El OR REPLACE indica que si ya existe un procedimiento con el mismo nombre, se reemplazará por el nuevo procedimiento.
    • add_job es el nombre del procedimiento que estás creando.
    • (j_id IN jobs.job_id%TYPE, j_tit IN jobs.job_title%TYPE) son los parámetros de entrada del procedimiento. El parámetro  j_id es de tipo jobs.job_id%TYPE, que significa que su tipo de datos es el mismo que el de la columna job_id de la tabla jobs. Y el parámetro j_tit es de tipo jobs.job_title%TYPE, similar al parámetro anterior pero para la columna job_title.

  • IS

    • Indica el comienzo del bloque PL/SQL del procedimiento.

  • BEGIN

    • Marca el inicio del bloque anónimo de PL/SQL donde se define la lógica del procedimiento. Aqúi es donde introducimos intrucción de la inserción en este caso.

  • INSERT INTO jobs(job_id, job_title) VALUES(j_id, j_tit);

    • Esta es una sentencia SQL que inserta una nueva fila en la tabla jobs.
    • Utiliza los valores pasados como parámetros (j_id y j_tit) para las columnas job_id y job_title respectivamente.

  • DBMS_OUTPUT.PUT_LINE(‘Se ha insertado un nuevo trabajador con ID ‘ || j_id || ‘ y título ‘ || j_tit);

    • DBMS_OUTPUT.PUT_LINE es un procedimiento de la biblioteca estándar de Oracle que imprime un mensaje en la consola de salida.
    • Este mensaje incluye información sobre el ID del trabajo (j_id) y su título (j_tit), indicando que se ha insertado correctamente un nuevo trabajo en la tabla jobs.

  • END add_job;

    • Marca el final del bloque PL/SQL del procedimiento.
    • add_job es el nombre del procedimiento al que se está poniendo fin.

 

Probamos el procedimiento anterior mediante la sentencia EXECUTE y mediante un bloque anónimo:

				
					/* CREATE OR REPLACE PROCEDURE add_job(
    j_id IN jobs.job_id%TYPE, 
    j_tit IN jobs.job_title%TYPE
) IS
BEGIN
    INSERT INTO jobs(job_id, job_title) 
    VALUES(j_id, j_tit);
    DBMS_OUTPUT.PUT_LINE('Se ha insertado un nuevo trabajador con ID ' || j_id || ' y título ' || j_tit);
END add_job; */

EXECUTE add_job('Prof', 'Profesor');
DECLARE
    v_job_id jobs.job_id%TYPE := 'Alm'; 
    v_job_title jobs.job_title%TYPE := 'Alumno';
BEGIN
    add_job(v_job_id, v_job_title);
END;
EXECUTE add_jobs('ST_MAN','Stock Manager');

  • EXECUTE add_job(‘Prof’, ‘Profesor’);

    • Esta línea ejecuta el procedimiento add_job con los valores 'Prof' y 'Profesor' como argumentos. Esto significa que se intentará insertar un nuevo trabajo con job_id = 'Prof' y job_title = 'Profesor'.

  • DECLARE…BEGIN…END;

    • Esta es una sección anónima de PL/SQL.
    • DECLARE
      • Aquí se declaran variables locales para ser utilizadas dentro del bloque BEGIN...END;.
      • v_job_id se declara como una variable de tipo jobs.job_id%TYPE con el valor 'Alm'.
      • v_job_title se declara como una variable de tipo jobs.job_title%TYPE con el valor 'Alumno'.
    • BEGIN…END;
      • Este es el bloque principal donde se ejecutan las sentencias.
      • Llama al procedimiento add_job con los valores de las variables v_job_id y v_job_title. Esto significa que se intentará insertar un nuevo trabajo con job_id = 'Alm' y job_title = 'Alumno'.

  • EXECUTE add_jobs(‘ST_MAN’,’Stock Manager’);

    • Parece haber un error de sintaxis aquí. La llamada debería ser a add_job en lugar de add_jobs, ya que el procedimiento se llama add_job. Suponiendo que es un error tipográfico, la línea intenta insertar un nuevo trabajo con job_id = 'ST_MAN' y job_title = 'Stock Manager'.

 

En resumen, este código ejecuta tres llamadas al procedimiento add_job. La primera inserta un trabajo con job_id = 'Prof' y job_title = 'Profesor'. La segunda inserta un trabajo con job_id = 'Alm' y job_title = 'Alumno', utilizando variables. Y la tercera, parece ser un intento de inserción con job_id = 'ST_MAN' y job_title = 'Stock Manager', aunque contiene un error de sintaxis (add_jobs en lugar de add_job).

 
 

Punto 2

Cree un procedimiento denominado UP_JOB para actualizar el título asignado a un trabajo (JOB_TITLE) a partir de su identificador de trabajo (JOB_ID), esta información debe ser enviada a través de parámetros. Una vez compilador el procedimiento UP_JOB, pruebe dicho procedimiento a través del paso de parámetro por nombre.

				
					CREATE OR REPLACE PROCEDURE up_job(
    j_id IN jobs.job_id%TYPE,
    j_tit IN jobs.job_title%TYPE
) IS
BEGIN
    UPDATE jobs SET job_title = j_tit WHERE job_id = j_id;
    DBMS.OUTPUT_LINE('Se ha actualizado el trabajo cuyo id es ' || j_id || ' con nombre ' || j_tit);
END up_job;
				
			
				
					EXECUTE up_job('Prof', 'Profesor 2')

EXECUTE up_job(j_tit => 'Alumnos', j_id => 'Alm')

Punto 3

Cree un procedimiento denominado info_emp que obtenga el nombre y el salario de un empleado a través de su id. Cree tantos parámetros como crea necesario y establezca si son parámetros de tipo IN o de tipo OUT en función de sus necesidades.

				
					-- Creamos el procedimiento
CREATE OR REPLACE PROCEDURE info_emp(
    p_id IN employees.employees_id%TYPE,
    p_name IN employees.firts_name%TYPE,
    p_salary IN employees.salary%TYPE
) IS 
BEGIN
    SELECT first_name, salary INTO p_name, p_salary FROM employees WHERE eemployees_id = p_id;
END info_emp;

				
			
				
					-- Activamos el modo de salida 
SET SERVEROUTPUT ON;

-- Bloque anónimo
DECLARE
    nom employees.first_name%TYPE;
    sal employees.salary%TYPE;
BEGIN
    info_emp(100,nom,sal);
    SYS.DBMS_OUTPUT.PUT_LINE(nom|| ' gana '||sal||'€');
END;

Ejercicio Nº 6 : Procedimientos en PL / SQL

Sobre el caso práctico de la base de datos del diagrama HR crear las diferentes funciones que se solicitan.

  • Creación de procedimiento almacenado que permita mostrar los datos de un empleado y su salario, pasando como parámetro el código de empleado.
  • Creación de un procedimiento almacenado que muestre los registros EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID de cualquiera de los empleados según el EMPLOYEE_ID, así mismo, debemos mostrar el campo DEPARTMENT_NAME sólo cuando el DEPARTMENT_ID sea igual a 50.

Punto 1

Creación de procedimiento almacenado que permita mostrar los datos de un empleado y su salario, pasando como parámetro el código de empleado.

				
					-- Creación del procedimineto.
CREATE OR REPLACE PROCEDURE emp_sal(
    -- Asignación de procedimientos
    p_id IN employees.employee_id%TYPE,
    p_nombre IN employees.first_name%TYPE,
    p_apellido IN employees.last_name%TYPE,
    p_salario IN employees.salary%TYPE
) IS
-- Función del procedimineto.
BEGIN
    -- Consulta en la BD.
    SELECT first_name, last_name, salary INTO p_nombre, p_apellido, p_salario WHERE employee_id = p_id;
    -- Mostrar resultados por pantalla.
    BDSM.OUTPUT.PUT_LINE('Nombre: '||p_nombre);
    BDSM.OUTPUT.PUT_LINE('Apellido: '||p_apellido);
    BDSM.OUTPUT.PUT_LINE('Salario: '||p_salario);
END emp_sal;

Punto 2

Creación de un procedimiento almacenado que muestre los registros EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID de cualquiera de los empleados según el EMPLOYEE_ID, así mismo, debemos mostrar el campo DEPARTMENT_NAME sólo cuando el DEPARTMENT_ID sea igual a 50.

				
					-- Creamos el procedimiento view_emp
CREATE OR REPLACE PROCEDURE view_emp(
    in_id IN NUMBER,
    out_id OUT employees.employee_id%TYPE,
    out_man OUT employees.manager_id%TYPE,
    out_dep_id OUT employees.department_id%TYPE,
    out_dep_name OUT departments.department_name%TYPE
) AS
BEGIN
    BEGIN
        SELECT employee_id, manager_id, department_id INTO out_id, out_man, out_dep_id FROM employees WHERE employee_id = in_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        out_id:=NULL;
        out_man:=NULL;
        out_dep_id:=NULL;
    END;
    BEGIN
        IF(out_dep_id = 50) THEN
            BEGIN
                SELECT department_name INTO out_dep_name FROM departments     WHERE department_id=out_dep_id;
            EXCEPTION
                WHEN NO_DATA_FOUND
                    THEN out_dep_name:=NULL;
            END;
        ELSE
            BEGIN
                out_dep_name:=0;
            END;
        END IF;
    END;
END view_emp;
Volver
Siguenos
Github Cube Linkedin