Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T4»

De wikiserver
Dreceres ràpides: navegació, cerca
(T4- Solución : Ineracción con Oracle Server)
 
(Hi ha 11 revisions intermèdies del mateix usuari que no es mostren)
Línia 1: Línia 1:
 
==T4- Solución : Ineracción con Oracle Server==
 
==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.
 
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.
Línia 29: Línia 30:
  
 
[[Imatge:m2uf3_ios_1.png |300px|center| Interacción Oracle Server]]
 
[[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.
 
2) Modifique el bloque PL/SQL creado en el paso 1 para insertar un nuevo departamento en la tabla departments.
Línia 44: Línia 58:
 
</pre>
 
</pre>
  
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.
+
: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>
Línia 50: Línia 64:
 
</pre>
 
</pre>
  
c) Incluya una sentencia INSERT para insertar datos en las columnas department_name, department_id y location_id de la tabla departments.
+
: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.
 
Utilice valores en dept_name y dept_id para department_name y department_id, respectivamente, y utilice NULL para location_id.
Línia 60: Línia 74:
 
</pre>
 
</pre>
  
d) Utilice el atributo SQL SQL%ROWCOUNT para mostrar el número de filas que se ven afectadas.
+
:d) Utilice el atributo SQL SQL%ROWCOUNT para mostrar el número de filas que se ven afectadas.
  
 
<pre>
 
<pre>
Línia 67: Línia 81:
 
</pre>
 
</pre>
  
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.
+
: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>
Línia 76: Línia 90:
 
</pre>
 
</pre>
  
f) Ejecute y guarde el script como lab_04_02_soln.sql. La salida de ejemplo es la siguiente:
+
:f) Ejecute y guarde el script como lab_04_02_soln.sql. La salida de ejemplo es la siguiente:
  
[[Imatge:m2uf3_ios_2.png |300px|center| Interacción Oracle Server]]
+
[[Imatge:m2uf3_ios_2.png |400px|center| Interacción Oracle Server]]
  
==T4- Sentencies executables ==
+
'''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;
  
*'''Exercici 1'''
+
</pre>
  
:Observa el següent bloc PL/SQL i determina els valors de les variables següents d'acord amb les regles d'àmbit
+
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.
  
::DECLARE
+
'''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.
  
::v_weight NUMBER(3):=600;
+
: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).
  
::v_message VARCHAR2(255):='Product 10012';
+
<pre>
 
+
BEGIN
::BEGIN
 
 
 
::::DECLARE
 
  
:::::v_weight NUMBER(3):=1;
+
  UPDATE departments SET location_id=3000 WHERE department_id=280;
 
 
:::::v_message VARCHAR2(255):='Product 11001';
 
 
 
:::::v_new_locn VARCHAR2(50):='Europe';
 
 
 
:::::BEGIN
 
 
 
:::::::v_weight:= v_weight+1;
 
 
 
:::::::v_new_locn:='Western'|| v_new_locn;
 
 
 
:::::END;
 
 
 
:::::v_weight:= v_weight+1;
 
 
 
:::::v_message:=v_message||'is in stock';
 
 
 
:::::v_new_locn:='Western'|| v_new_locn;
 
 
 
::END;
 
 
 
 
 
:a) El valor de v_weight en el subbloc és:
 
<pre>
 
"2" i el tipus de dada és NUMBER.
 
</pre>
 
:b) El valor de v_new_locn en el subbloc és:
 
<pre>
 
"Wetern Europe" i el tipus de dada és VARCHAR2.
 
 
</pre>
 
</pre>
  
:c) El valor de v_weight en el bloc principal és:
+
: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>
 
"601" i el tipus de dada és NUMBER.
 
</pre>
 
  
:d) El valor de v_message en el bloc principal és:
 
 
<pre>
 
<pre>
"Product 10012 is in stock" i el tipus de dada és VARCHAR2.
+
END;
 +
/
 +
SELECT * FROM departments WHERE department_id=280;
 
</pre>
 
</pre>
 +
:c) Incluya una sentencia DELETE para suprimir el departamento agregado.
  
:e) El valor de v_new_locn en el bloc principal es
 
 
<pre>
 
<pre>
Ilegal perquè v_new_locn no és visible fora del sub-bloc.
+
DELETE FROM departments WHERE department_id=280;
 
</pre>
 
</pre>
  
*'''Exercici 2'''
+
:d) Ejecute y guarde el script como lab_04_03_soln.sql. La salida de ejemplo es la siguiente:
  
Suposem que incloem un subbloc en un bloc tal com es mostra tot seguit. Determina els valors en els casos següents:
+
[[Imatge:m2uf3_ios_3.png |400px|center| Interacción Oracle Server]]
  
::DECLARE
+
'''SOLUCIÓ:'''
 +
<pre>
 +
SET SERVEROUTPUT ON
 +
BEGIN
  
::::v_customer   VARCHAR2(50):='Womansport';
+
   UPDATE departments SET location_id=3000 WHERE department_id=280;
 
+
 
::::v_credit_rating   VARCHAR2(50):='excellent';
+
   --DELETE FROM departments WHERE department_id=280; --primer executar-ho sense aquesta instrucció.
 
+
END;
::BEGIN
+
/
 +
  SELECT * FROM departments
 +
  WHERE department_id= 280;
 +
</pre>
  
::::DECLARE
+
'''ADDICIONALS'''
  
::::::v_customer  NUMBER(7):=201;
+
'''EXERCICI 1'''
  
::::::v_name        VARCHAR2(25):='unisports';
+
Per a aquest exercici, es necessita una taula temporal per emmagatzemar els resultats.
  
::::::BEGIN
+
'''1.-''' Executa el script lab_ap_04.sql que crea la taula descrita a continuació:
  
::::::::vcustomer      v_name        v_credit_rating     
+
[[Imatge:M2UF3_IOS_1.png |600px|center| Interacció Oracle Server]]
  
::::::END;
+
'''2.-''' Escriu un bloc PL/SQL que realitzi les següents accions:
  
::::::::vcustomer      v_name        v_credit_rating
+
:a) Declara dues variables i assigna el següents valors a aquestes variables:  
  
::END;
+
[[Imatge:M2UF3_IOS_2.png |600px|center| Interacció Oracle Server]]
  
:a) El valor de v_customer en el subbloc és:
 
<pre>
 
"201" i el tipus de dada és NUMBER.
 
</pre>
 
:b) El valor de v_name en el subbloc és:
 
<pre>
 
"Unisports" i el tipus de dada és VARCHAR2.
 
</pre>
 
:c) El valor de v_credit_rating en el subbloc és:
 
<pre>
 
"EXCELLENT" i el tipus de dada és VARCHAR2.
 
</pre>
 
:d) El valor de v_customer en el bloc principal és:
 
<pre>
 
"Womansport" i el tipus de dada és VARCHAR2.
 
</pre>
 
:e) El valor de v_name en el bloc principal es
 
<pre>
 
V_NAME no és visible en el bloc principal i per tant es veuria un error.
 
</pre>
 
:f) El valor de v_credit_rating en el bloc principal és:
 
 
<pre>
 
<pre>
"EXCELLENT" i el tipus de dada és VARCHAR2.
+
CREATE TABLE TEMP(
 +
  NUM_STORE NUMBER(7,2),
 +
  CHAR_STORE VARCHAR2(35),
 +
  DATE_STORE DATE);
 
</pre>
 
</pre>
*'''Exercici 3'''
 
 
Crea i executa un bloc PL/SQL anomenat “T3E3.sql” que accepti dos números mitjançant variables  SQL*PLUS. S'ha de dividir el primer número pel segon i després afegir el segon número al resultat. El resultat s'ha d'escriure en una variable PL/SQL i s'ha d'imprimir en pantalla amb una variable SQL*PLUS.
 
 
::Please enter the first number: 2
 
 
::Please enter the second number: 4
 
 
::PL/SQL procedure successfully completed._
 
  
::V_RESULT
+
:b) Emmagatzema els valors d'aquestes variables en les columnes adients de la tabla TEMP.
::--------
 
::  4.5
 
  
 
<pre>
 
<pre>
SET VERIFY OFF
 
VARIABLE g_result NUMBER
 
ACCEPT p_num1 PROMPT 'Please enter the first number: '
 
ACCEPT p_num2 PROMPT 'Please enter the second number: '
 
 
DECLARE
 
DECLARE
   v_num1    NUMBER(9,2) := &p_num1;
+
   V_MESSAGE VARCHAR2(35);
   v_num2    NUMBER(9,2) := &p_num2;
+
   V_DATE_WRITTEN DATE;
 
BEGIN
 
BEGIN
   :g_result := (v_num1/v_num2) + v_num2;
+
   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;
 
END;
 
/
 
/
PRINT g_result
 
SET VERIFY ON
 
 
SQL> START p4q3.sql
 
 
</pre>
 
</pre>
  
 +
'''3.-''' Verifica els resultats consultant la taula TEMP. La sortida resultant ha de ser com la següent:
 
<pre>
 
<pre>
ACCEPT p_num1 PROMPT 'Please enter the first number: '
+
   SELECT * FROM TEMP;
ACCEPT p_num2 PROMPT 'Please enter the second number: '
 
DECLARE
 
   v_num1    NUMBER(9,2) := &p_num1;
 
  v_num2    NUMBER(9,2) := &p_num2;
 
BEGIN
 
  dbms_output.put_line(TO_CHAR(v_num1/v_num2) + v_num2);
 
END;
 
/
 
 
</pre>
 
</pre>
 +
[[Imatge:M2UF3_IOS_3.png |600px|center| Interacció Oracle Server]]
  
*'''Exercici 4'''
+
'''EXERCICI 2'''
  
:Genereu un bloc PL/SQL que calculi la compensació total per un any. El salari anual i el percentatge anual de bonificacions es passaran  al bloc PL/SQL mitjançant variables de substitució SQL*PLUS i l'import de bonificació haurà de ser convertit de número enter a decimal (per exemple 15 a 0,15). Si el salari és nul assigneu-li zero abans de calcula la compensació. Utilitzeu la funció NVL per gestionar els valors nuls.
+
En aquest exercici, utilitzaràs dades de la taula employees.
  
::Please enter the salary amount: 50000
+
'''1.-''' Escriu un bloc PL/SQL per determinar el nombre d'empleats que treballen en un determinat departament. El bloc PL/SQL haurà de:
  
::Please enter the bonus percentage: 10
+
:* Utilitzar una variable de substitució per emmagatzemar un número de departament
 
+
:* Imprimir el nombre de persones que treballen en el departament especificat
::PL/SQL procedure successfully completed._
 
 
 
::G_TOTAL
 
::--------
 
::  55000
 
  
 
<pre>
 
<pre>
SET VERIFY OFF
+
SET SERVEROUTPUT ON;
VARIABLE g_total NUMBER
 
ACCEPT p_salary PROMPT 'Please enter the salary amount: '
 
ACCEPT p_bonus PROMPT 'Please enter the bonus percentatge: '
 
DECLARE
 
  v_salary    NUMBER := &p_salary;
 
  v_bonus      NUMBER := &p_bonus;
 
BEGIN
 
  :g_total := NVL(v_salary,0) * (1 + NVL(v_bonus,0) / 100);
 
END;
 
/
 
PRINT g_total
 
SET VERIFY ON
 
  
SQL> START p4q4.sql
 
</pre>
 
<pre>
 
SET SERVEROUTPUT ON
 
ACCEPT p_salary PROMPT 'Please enter the salary amount: '
 
ACCEPT p_bonus PROMPT 'Please enter the bonus percentatge: '
 
 
DECLARE
 
DECLARE
   v_salary    NUMBER := &p_salary;
+
   V_HOWMANY NUMBER(3);
   v_bonus      NUMBER := &p_bonus;
+
   V_DEPTNO DEPARTMENTS.department_id%TYPE := &P_DEPTNO;
 
BEGIN
 
BEGIN
   dbms_output.put_line(TO_CHAR(NVL(v_salary,0) * (1 + NVL(v_bonus,0) / 100)));
+
   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;
 
END;
 
/
 
/
 
</pre>
 
</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:
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
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;
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
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:

Interacció Oracle Server