Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T4»
(Hi ha 20 revisions intermèdies del mateix usuari que no es mostren) | |||
Línia 1: | Línia 1: | ||
− | ==T4- | + | ==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. |
− | + | <pre> | |
+ | DECLARE | ||
− | + | v_max_deptno NUMBER; | |
+ | </pre> | ||
− | : | + | :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. |
− | + | <pre> | |
+ | BEGIN | ||
− | + | SELECT MAX(department_id) INTO v_max_deptno FROM departments; | |
+ | </pre> | ||
− | : | + | :c) Muestre v_max_deptno y termine el bloque ejecutable. |
− | + | <pre> | |
+ | DBMS_OUTPUT.PUT_LINE('The maximum department_id is : ' || v_max_deptno); | ||
+ | END; | ||
+ | </pre> | ||
− | :: | + | :d) Ejecute y guarde el script como lab_04_01_soln.sql. La salida de ejemplo es la siguiente: |
− | : | + | [[Imatge:m2uf3_ios_1.png |300px|center| Interacción Oracle Server]] |
− | :: | + | '''SOLUCIÓ:''' |
− | + | <pre> | |
− | + | 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; | ||
+ | </pre> | ||
− | |||
− | + | 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. | ||
− | |||
<pre> | <pre> | ||
− | + | v_dept_name departments.department_name%TYPE:= 'Education'; | |
+ | v_dept_id NUMBER; | ||
</pre> | </pre> | ||
− | :b) | + | |
+ | :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. | ||
+ | |||
<pre> | <pre> | ||
− | + | v_dept_id := 10 + v_max_deptno; | |
</pre> | </pre> | ||
− | :c) | + | :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. | ||
+ | |||
<pre> | <pre> | ||
− | + | ... | |
+ | INSERT INTO departments (department_id, department_name, location_id) | ||
+ | VALUES (v_dept_id, v_dept_name, NULL); | ||
</pre> | </pre> | ||
− | :d) | + | :d) Utilice el atributo SQL SQL%ROWCOUNT para mostrar el número de filas que se ven afectadas. |
+ | |||
<pre> | <pre> | ||
− | + | DBMS_OUTPUT.PUT_LINE (' SQL%ROWCOUNT gives ' || SQL%ROWCOUNT); | |
+ | ... | ||
</pre> | </pre> | ||
− | :e) | + | :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. |
+ | |||
<pre> | <pre> | ||
− | + | ... | |
+ | / | ||
+ | SELECT * FROM departments | ||
+ | WHERE department_id= 280; | ||
</pre> | </pre> | ||
− | + | :f) Ejecute y guarde el script como lab_04_02_soln.sql. La salida de ejemplo es la siguiente: | |
− | + | [[Imatge:m2uf3_ios_2.png |400px|center| Interacción Oracle Server]] | |
− | :: | + | '''SOLUCIÓ:''' |
+ | <pre> | ||
+ | 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; | ||
− | + | </pre> | |
− | + | 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). |
− | + | <pre> | |
+ | BEGIN | ||
− | + | UPDATE departments SET location_id=3000 WHERE department_id=280; | |
+ | </pre> | ||
− | : | + | :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. |
− | : | + | <pre> |
+ | END; | ||
+ | / | ||
+ | SELECT * FROM departments WHERE department_id=280; | ||
+ | </pre> | ||
+ | :c) Incluya una sentencia DELETE para suprimir el departamento agregado. | ||
− | + | <pre> | |
+ | DELETE FROM departments WHERE department_id=280; | ||
+ | </pre> | ||
− | :: | + | :d) Ejecute y guarde el script como lab_04_03_soln.sql. La salida de ejemplo es la siguiente: |
− | : | + | [[Imatge:m2uf3_ios_3.png |400px|center| Interacción Oracle Server]] |
− | : | + | '''SOLUCIÓ:''' |
<pre> | <pre> | ||
− | + | 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; | ||
</pre> | </pre> | ||
− | : | + | |
+ | '''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ó: | ||
+ | |||
+ | [[Imatge:M2UF3_IOS_1.png |600px|center| 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: | ||
+ | |||
+ | [[Imatge:M2UF3_IOS_2.png |600px|center| Interacció Oracle Server]] | ||
+ | |||
<pre> | <pre> | ||
− | + | CREATE TABLE TEMP( | |
+ | NUM_STORE NUMBER(7,2), | ||
+ | CHAR_STORE VARCHAR2(35), | ||
+ | DATE_STORE DATE); | ||
</pre> | </pre> | ||
− | : | + | |
+ | :b) Emmagatzema els valors d'aquestes variables en les columnes adients de la tabla TEMP. | ||
+ | |||
<pre> | <pre> | ||
− | + | DECLARE | |
+ | V_MESSAGE VARCHAR2(35); | ||
+ | V_DATE_WRITTEN DATE; | ||
+ | BEGIN | ||
+ | V_MESSAGE := 'This is my first PLSQL Program'; | ||
+ | V_DATE_WRITTEN := SYSDATE; | ||
+ | INSERT INTO temp(CHAR_STORE,DATE_STORE) | ||
+ | VALUES (V_MESSAGE,V_DATE_WRITTEN); | ||
+ | END; | ||
+ | / | ||
</pre> | </pre> | ||
− | + | ||
− | + | '''3.-''' Verifica els resultats consultant la taula TEMP. La sortida resultant ha de ser com la següent: | |
− | |||
− | |||
− | : | ||
<pre> | <pre> | ||
− | + | SELECT * FROM TEMP; | |
</pre> | </pre> | ||
− | : | + | [[Imatge:M2UF3_IOS_3.png |600px|center| 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 | ||
− | + | <pre> | |
− | + | SET SERVEROUTPUT ON; | |
− | |||
− | * | + | DECLARE |
− | + | V_HOWMANY NUMBER(3); | |
− | + | V_DEPTNO DEPARTMENTS.department_id%TYPE := &P_DEPTNO; | |
− | + | BEGIN | |
− | + | SELECT COUNT(*) INTO V_HOWMANY FROM employees | |
− | + | WHERE department_id = V_DEPTNO; | |
− | + | DBMS_OUTPUT.PUT_LINE (V_HOWMANY || ' employee(s) work for department number ' ||V_DEPTNO); | |
+ | END; | ||
+ | / | ||
+ | </pre> | ||
− | : | + | '''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: |
− | : | + | [[Imatge:M2UF3_IOS_4.png |600px|center| Interacció Oracle Server]] |
− | |||
− |
Revisió de 13:49, 28 feb 2020
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:
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:
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:
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ó:
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:
CREATE TABLE TEMP( NUM_STORE NUMBER(7,2), CHAR_STORE VARCHAR2(35), DATE_STORE DATE);
- b) Emmagatzema els valors d'aquestes variables en les columnes adients de la tabla TEMP.
DECLARE V_MESSAGE VARCHAR2(35); V_DATE_WRITTEN DATE; BEGIN V_MESSAGE := 'This is my first PLSQL Program'; V_DATE_WRITTEN := SYSDATE; INSERT INTO temp(CHAR_STORE,DATE_STORE) VALUES (V_MESSAGE,V_DATE_WRITTEN); END; /
3.- Verifica els resultats consultant la taula TEMP. La sortida resultant ha de ser com la següent:
SELECT * FROM TEMP;
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
SET SERVEROUTPUT ON; DECLARE V_HOWMANY NUMBER(3); V_DEPTNO DEPARTMENTS.department_id%TYPE := &P_DEPTNO; BEGIN SELECT COUNT(*) INTO V_HOWMANY FROM employees WHERE department_id = V_DEPTNO; DBMS_OUTPUT.PUT_LINE (V_HOWMANY || ' employee(s) work for department number ' ||V_DEPTNO); END; /
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: