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

Revisió del 15:17, 10 abr 2014 (mostra codi font) Rsort (Discussió | contribucions) (→‎T4- Sentencies executables) ← Vés a l'edició anterior		Revisió de 13:49, 28 feb 2020 (mostra codi font) Rsort (Discussió | contribucions)
(Hi ha 19 revisions intermèdies del mateix usuari que no es mostren)
Línia 1:		Línia 1:
−	==T4- Sentencies executables ==	+	==T4- Solución : Ineracción con Oracle Server==
−	*'''Exercici 1'''
−	:Observa el següent bloc PL/SQL i determina els valors de les variables següents d'acord amb les regles d'àmbit	+	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.
−	::DECLARE	+	:a) Declare una variable v_max_deptno de tipo NUMBER en la sección de declaraciones.
−	::v_weight NUMBER(3):=600;	+	<pre>
		+	DECLARE
−	::v_message VARCHAR2(255):='Product 10012';	+	v_max_deptno NUMBER;
		+	</pre>
−	::BEGIN	+	: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.
−	::::DECLARE	+	<pre>
		+	BEGIN
−	:::::v_weight NUMBER(3):=1;	+	SELECT MAX(department_id) INTO v_max_deptno FROM departments;
		+	</pre>
−	:::::v_message VARCHAR2(255):='Product 11001';	+	:c) Muestre v_max_deptno y termine el bloque ejecutable.
−	:::::v_new_locn VARCHAR2(50):='Europe';	+	<pre>
		+	DBMS_OUTPUT.PUT_LINE('The maximum department_id is : ' || v_max_deptno);
		+	END;
		+	</pre>
−	:::::BEGIN	+	:d) Ejecute y guarde el script como lab_04_01_soln.sql. La salida de ejemplo es la siguiente:
−	:::::::v_weight:= v_weight+1;	+	[[Imatge:m2uf3_ios_1.png |300px|center| Interacción Oracle Server]]
−	:::::::v_new_locn:='Western'|| v_new_locn;	+	'''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>
−	:::::END;
−	:::::v_weight:= v_weight+1;	+	2) Modifique el bloque PL/SQL creado en el paso 1 para insertar un nuevo departamento en la tabla departments.
−	:::::v_message:=v_message||'is in stock';	+	:a) Cargue el script lab_04_01_soln.sql. Declare dos variables:
−	:::::v_new_locn:='Western'|| v_new_locn;	+	::v_dept_name de tipo departments.department_name y
		+	::v_dept_id de tipo NUMBER
−	::END;	+	Asigne ‘Education’ a v_dept_name en la sección de declaraciones.
−
−	:a) El valor de v_weight en el subbloc és:
	<pre>		<pre>
−	"2" i el tipus de dada és NUMBER.	+	v_dept_name departments.department_name%TYPE:= 'Education';
		+	v_dept_id NUMBER;
	</pre>		</pre>
−	:b) El valor de v_new_locn en el subbloc és:	+
		+	: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>
−	"Wetern Europe" i el tipus de dada és VARCHAR2.	+	v_dept_id := 10 + v_max_deptno;
	</pre>		</pre>
−	:c) El valor de v_weight en el bloc principal és:	+	: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>
−	"601" i el tipus de dada és NUMBER.	+	...
		+	INSERT INTO departments (department_id, department_name, location_id)
		+	VALUES (v_dept_id, v_dept_name, NULL);
	</pre>		</pre>
−	:d) El valor de v_message en el bloc principal és:	+	:d) Utilice el atributo SQL SQL%ROWCOUNT para mostrar el número de filas que se ven afectadas.
		+
	<pre>		<pre>
−	"Product 10012 is in stock" i el tipus de dada és VARCHAR2.	+	DBMS_OUTPUT.PUT_LINE (' SQL%ROWCOUNT gives ' || SQL%ROWCOUNT);
		+	...
	</pre>		</pre>
−	:e) El valor de v_new_locn en el bloc principal es	+	: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>
−	Ilegal perquè v_new_locn no és visible fora del sub-bloc.	+	...
		+	/
		+	SELECT * FROM departments
		+	WHERE department_id= 280;
	</pre>		</pre>
−	*'''Exercici 2'''	+	:f) Ejecute y guarde el script como lab_04_02_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_2.png |400px|center| Interacción Oracle Server]]
−	::DECLARE	+	'''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;
−	::::v_customer    VARCHAR2(50):='Womansport';	+	</pre>
−	::::v_credit_rating    VARCHAR2(50):='excellent';	+	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.
−	::BEGIN	+	'''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.
−	::::DECLARE	+	: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_customer  NUMBER(7):=201;	+	<pre>
		+	BEGIN
−	::::::v_name        VARCHAR2(25):='unisports';	+	UPDATE departments SET location_id=3000 WHERE department_id=280;
		+	</pre>
−	::::::BEGIN	+	: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.
−	::::::::vcustomer      v_name        v_credit_rating	+	<pre>
		+	END;
		+	/
		+	SELECT * FROM departments WHERE department_id=280;
		+	</pre>
		+	:c) Incluya una sentencia DELETE para suprimir el departamento agregado.
−	::::::END;	+	<pre>
		+	DELETE FROM departments WHERE department_id=280;
		+	</pre>
−	::::::::vcustomer      v_name        v_credit_rating	+	:d) Ejecute y guarde el script como lab_04_03_soln.sql. La salida de ejemplo es la siguiente:
−	::END;	+	[[Imatge:m2uf3_ios_3.png |400px|center| Interacción Oracle Server]]
−	:a) El valor de v_customer en el subbloc és:	+	'''SOLUCIÓ:'''
	<pre>		<pre>
−	"201" i el tipus de dada és NUMBER.	+	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>
−	: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>
−	"EXCELLENT" i el tipus de dada és VARCHAR2.
−	</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.	+	'''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:
−	::Please enter the first number: 2	+	:a) Declara dues variables i assigna el següents valors a aquestes variables:
−	::Please enter the second number: 4	+	[[Imatge:M2UF3_IOS_2.png |600px|center| Interacció Oracle Server]]
−	::PL/SQL procedure successfully completed._	+	<pre>
		+	CREATE TABLE TEMP(
		+	NUM_STORE NUMBER(7,2),
		+	CHAR_STORE VARCHAR2(35),
		+	DATE_STORE DATE);
		+	</pre>
−	::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 v_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
−	:v_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 v_result	+	</pre>
−	SET VERIFY ON
−	SQL> START p4q3.sql	+	'''3.-''' Verifica els resultats consultant la taula TEMP. La sortida resultant ha de ser com la següent:
		+	<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>		<pre>
−	ACCEPT p_num1 PROMPT 'Please enter the first number: '	+	SET SERVEROUTPUT ON;
−	ACCEPT p_num2 PROMPT 'Please enter the second number: '	+
	DECLARE		DECLARE
−	v_num1    NUMBER(9,2) := &p_num1;	+	V_HOWMANY NUMBER(3);
−	v_num2    NUMBER(9,2) := &p_num2;	+	V_DEPTNO DEPARTMENTS.department_id%TYPE := &P_DEPTNO;
	BEGIN		BEGIN
−	dbms_output.put_line(TO_CHAR(v_num1/v_num2) + v_num2);	+	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>
−	*'''Exercici 4'''	+	'''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:
−
−	: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.
−
−	::Please enter the salary amount: 50000
−
−	::Please enter the bonus percentage: 10
−
−	::PL/SQL procedure successfully completed._
−	::G_TOTAL	+	[[Imatge:M2UF3_IOS_4.png |600px|center| Interacció Oracle Server]]
−	::--------
−	::  55000

---

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: