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

De wikiserver
La revisió el 12:37, 28 feb 2020 per Rsort (Discussió | contribucions) (T4- Solución : Ineracción con Oracle Server)
Dreceres ràpides: navegació, cerca

T4- Solución : Ineracción con Oracle Server

1) Cree un bloque PL/SQL que seleccione el identificador de departamento superior en la tabla departments y lo almacene en la variable v_max_deptno. Muestre el identificador de departamento superior.

a) Declare una variable v_max_deptno de tipo NUMBER en la sección de declaraciones.
DECLARE

   v_max_deptno NUMBER;
b) Inicie la sección ejecutable con la palabra clave BEGIN e incluya una sentencia SELECT para recuperar el valor máximo de department_id de la tabla departments.
BEGIN

   SELECT MAX(department_id) INTO v_max_deptno FROM departments;
c) Muestre v_max_deptno y termine el bloque ejecutable.
   DBMS_OUTPUT.PUT_LINE('The maximum department_id is : ' || v_max_deptno);
END;
d) Ejecute y guarde el script como lab_04_01_soln.sql. La salida de ejemplo es la siguiente:
Interacción Oracle Server

SOLUCIÓ:

SET SERVEROUTPUT ON
DECLARE
   v_max_deptno NUMBER;
  
BEGIN
   SELECT MAX(department_id) INTO v_max_deptno FROM departments;
   DBMS_OUTPUT.PUT_LINE('The maximum department_id is : ' || v_max_deptno);
END;


2) Modifique el bloque PL/SQL creado en el paso 1 para insertar un nuevo departamento en la tabla departments.

a) Cargue el script lab_04_01_soln.sql. Declare dos variables:
v_dept_name de tipo departments.department_name y
v_dept_id de tipo NUMBER

Asigne ‘Education’ a v_dept_name en la sección de declaraciones.

   v_dept_name departments.department_name%TYPE:= 'Education';
   v_dept_id NUMBER;
b) Ya ha recuperado el número de departamento superior actual de la tabla departments. Agréguele 10 y asigne el resultado a v_dept_id.
   v_dept_id := 10 + v_max_deptno;
c) Incluya una sentencia INSERT para insertar datos en las columnas department_name, department_id y location_id de la tabla departments.

Utilice valores en dept_name y dept_id para department_name y department_id, respectivamente, y utilice NULL para location_id.

...
   INSERT INTO departments (department_id, department_name, location_id)
   VALUES (v_dept_id, v_dept_name, NULL);
d) Utilice el atributo SQL SQL%ROWCOUNT para mostrar el número de filas que se ven afectadas.
   DBMS_OUTPUT.PUT_LINE (' SQL%ROWCOUNT gives ' || SQL%ROWCOUNT);
   ...
e) Ejecute una sentencia SELECT para comprobar si se ha insertado el nuevo departamento. Termine el bloque PL/SQL con “/” e incluya la sentencia SELECT en el script.
...
/
   SELECT * FROM departments
   WHERE department_id= 280;
f) Ejecute y guarde el script como lab_04_02_soln.sql. La salida de ejemplo es la siguiente:
Interacción Oracle Server

SOLUCIÓ:

SET SERVEROUTPUT ON
DECLARE
   v_max_deptno NUMBER;
   v_dept_name departments.department_name%TYPE:= 'Education';
   v_dept_id NUMBER;
  
BEGIN
   SELECT MAX(department_id) INTO v_max_deptno FROM departments;
   DBMS_OUTPUT.PUT_LINE('The maximum department_id is : ' || v_max_deptno);
   v_dept_id := 10 + v_max_deptno;
   INSERT INTO departments (department_id, department_name, location_id)
   VALUES (v_dept_id, v_dept_name, NULL);
   DBMS_OUTPUT.PUT_LINE (' SQL%ROWCOUNT gives ' || SQL%ROWCOUNT);
END;
/
   SELECT * FROM departments
   WHERE department_id= 280;

3) En el paso 2, defina location_id en NULL. Cree un bloque PL/SQL que actualice location_id a 3000 para el nuevo departamento.

Nota: si ha terminado correctamente el paso 2, continúe con el paso 3a. De lo contrario, ejecute primero el script de solución /soln/sol_04_02.sql.

a) Inicie el bloque ejecutable con la palabra clave BEGIN. Incluya la sentencia UPDATE para definir location_id en 3000 para el nuevo departamento (dept_id =280).
BEGIN

   UPDATE departments SET location_id=3000 WHERE department_id=280;
b) Termine el bloque ejecutable con la palabra clave END. Termine el bloque PL/SQL con “/” e incluya una sentencia SELECT para mostrar el departamento que ha actualizado.
END;
/
SELECT * FROM departments WHERE department_id=280;
c) Incluya una sentencia DELETE para suprimir el departamento agregado.
DELETE FROM departments WHERE department_id=280;
d) Ejecute y guarde el script como lab_04_03_soln.sql. La salida de ejemplo es la siguiente:
Interacción Oracle Server

SOLUCIÓ:

SET SERVEROUTPUT ON
BEGIN

   UPDATE departments SET location_id=3000 WHERE department_id=280;
   
   --DELETE FROM departments WHERE department_id=280; --primer executar-ho sense aquesta instrucció.
END;
/
   SELECT * FROM departments
   WHERE department_id= 280;

ADDICIONALS

EXERCICI 1

Per a aquest exercici, es necessita una taula temporal per emmagatzemar els resultats.

1.- Executa el script lab_ap_04.sql que crea la taula descrita a continuació:

Interacció Oracle Server

2.- Escriu un bloc PL/SQL que realitzi les següents accions:

a) Declara dues variables i assigna el següents valors a aquestes variables:
Interacció Oracle Server
b) Emmagatzema els valors d'aquestes variables en les columnes adients de la tabla TEMP.

3.- Verifica els resultats consultant la taula TEMP. La sortida resultant ha de ser com la següent:

Interacció Oracle Server

EXERCICI 2

En aquest exercici, utilitzaràs dades de la taula employees.

1.- Escriu un bloc PL/SQL per determinar el nombre d'empleats que treballen en un determinat departament. El bloc PL/SQL haurà de:

  • Utilitzar una variable de substitució per emmagatzemar un número de departament
  • Imprimir el nombre de persones que treballen en el departament especificat

2.- Quan s'execute el bloc , apareixerà una finestra de variable de substitució. Introdueix un número de departament vàlid i fes clic en OK. La sortida resultant haurà de tenir un aspecte similar al següent:

Interacció Oracle Server