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

De wikiserver
Dreceres ràpides: navegació, cerca
Línia 14: Línia 14:
 
9%: AD_PRES, AD_VP  
 
9%: AD_PRES, AD_VP  
 
</pre>
 
</pre>
−
<!---->
+
<!--
 
<pre>
 
<pre>
 
CREATE OR REPLACE FUNCTION Total_Salari_Dept (p_dept_num departments.department_id%type) RETURN NUMBER IS
 
CREATE OR REPLACE FUNCTION Total_Salari_Dept (p_dept_num departments.department_id%type) RETURN NUMBER IS
Línia 39: Línia 39:
 
END;
 
END;
 
</pre>
 
</pre>
 +
-->
 
b) Fes un bloc PL/SQL anònim per provar la funció. Mostra el total dels salaris revisats dels departaments 10, 50 i 75.
 
b) Fes un bloc PL/SQL anònim per provar la funció. Mostra el total dels salaris revisats dels departaments 10, 50 i 75.
−
<!---->
+
<!--
 
<pre>
 
<pre>
 
SET SERVEROUTPUT ON
 
SET SERVEROUTPUT ON
Línia 55: Línia 56:
 
Total Salari Departament 75: 0
 
Total Salari Departament 75: 0
 
</pre>
 
</pre>
 +
-->
 
c) Crea la funció Total_Salari_Dept2 per tal que en cas de que no existeixi el número de departament utilitzi el procediment RAISE_APPLICATION_ERROR per generar una excepció amb el codi: -20215 i missatge: 'No existeix aquest departament!!'.
 
c) Crea la funció Total_Salari_Dept2 per tal que en cas de que no existeixi el número de departament utilitzi el procediment RAISE_APPLICATION_ERROR per generar una excepció amb el codi: -20215 i missatge: 'No existeix aquest departament!!'.
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
CREATE OR REPLACE FUNCTION Total_Salari_Dept2 (p_dept_num departments.department_id%type) RETURN NUMBER IS
 
CREATE OR REPLACE FUNCTION Total_Salari_Dept2 (p_dept_num departments.department_id%type) RETURN NUMBER IS
Línia 89: Línia 91:
 
END;
 
END;
 
</pre>
 
</pre>
−
 
+
-->
  
 
d) Fes un bloc anònim PL/SQL per provar el funcionament correcte de l'excepció de la funció.
 
d) Fes un bloc anònim PL/SQL per provar el funcionament correcte de l'excepció de la funció.
−
<!---->
+
<!--
 
<pre>
 
<pre>
 
BEGIN
 
BEGIN
Línia 100: Línia 102:
 
END;
 
END;
 
</pre>
 
</pre>
−
<!---->
+
-->
 +
<!--
 
<pre>
 
<pre>
 
Informe de error -
 
Informe de error -
Línia 110: Línia 113:
 
Total Salari Departament 50: 123600
 
Total Salari Departament 50: 123600
 
</pre>
 
</pre>
−
 
+
-->
 
e) En un bloc anònim PL/SQL crea un cursor que recuperi tots els departaments que tenen empleats (manager_id not null) ordenats per departament i utilitza la funció de l'apartat a) per omplir la taula PRESSUPOST convenientment.
 
e) En un bloc anònim PL/SQL crea un cursor que recuperi tots els departaments que tenen empleats (manager_id not null) ordenats per departament i utilitza la funció de l'apartat a) per omplir la taula PRESSUPOST convenientment.
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
DECLARE
 
DECLARE
Línia 132: Línia 135:
 
END;
 
END;
 
</pre>
 
</pre>
−
 
+
-->
  
 
f) Mostreu el contingut de la taula PRESSUPOST.
 
f) Mostreu el contingut de la taula PRESSUPOST.
−
<!---->
+
<!--
 
<pre>
 
<pre>
 
   SELECT *
 
   SELECT *
Línia 155: Línia 158:
 
   Shipping               123600
 
   Shipping               123600
 
</pre>
 
</pre>
−
 
+
-->
 
g) Crea la taula PRESSUPOST2 (CREATE TABLE) que contingui els següent camps: Nom_Department, First_Name, Last_Name i Total_Salaris.  
 
g) Crea la taula PRESSUPOST2 (CREATE TABLE) que contingui els següent camps: Nom_Department, First_Name, Last_Name i Total_Salaris.  
 
<pre>
 
<pre>
Línia 166: Línia 169:
  
 
h) Crea el procediment Afegir_Pressupost_Dept que rebrà com a paràmetres el nom del departament, el identificador del manager del departament i el total dels salaris revisats del departament, i obtindrà el nom i cognom del manager del departament i afegirà un nou registre a la taula PRESSUPOST2 amb la informació corresponent.
 
h) Crea el procediment Afegir_Pressupost_Dept que rebrà com a paràmetres el nom del departament, el identificador del manager del departament i el total dels salaris revisats del departament, i obtindrà el nom i cognom del manager del departament i afegirà un nou registre a la taula PRESSUPOST2 amb la informació corresponent.
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
CREATE OR REPLACE PROCEDURE Afegir_Pressupost_Dept (
 
CREATE OR REPLACE PROCEDURE Afegir_Pressupost_Dept (
Línia 186: Línia 189:
 
END;
 
END;
 
</pre>
 
</pre>
−
 
+
-->
  
 
i) Refès el bloc anònim anterior per tal de que utilitzant el procediment anterior afegeixi tots els departaments a la taula PRESSUPOST2. Tracta l'excepció que es produiria si el procediment no troba el manager per a un departament ('Error: No existeix el manager d'aquest departament') i qualsevol altre excepció que es pugui produir (codi d'error i missatge d'oracle).  
 
i) Refès el bloc anònim anterior per tal de que utilitzant el procediment anterior afegeixi tots els departaments a la taula PRESSUPOST2. Tracta l'excepció que es produiria si el procediment no troba el manager per a un departament ('Error: No existeix el manager d'aquest departament') i qualsevol altre excepció que es pugui produir (codi d'error i missatge d'oracle).  
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
DECLARE
 
DECLARE
Línia 218: Línia 221:
 
END;
 
END;
 
</pre>
 
</pre>
−
 
+
-->
 
j) Mostreu el contingut de la taula PRESSUPOST2.
 
j) Mostreu el contingut de la taula PRESSUPOST2.
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
SELECT *  
 
SELECT *  
Línia 240: Línia 243:
 
   Accounting        Shelley      Higgins          21563,56  
 
   Accounting        Shelley      Higgins          21563,56  
 
</pre>  
 
</pre>  
−
 
+
-->
 
2.- Fes un bloc PL/SQL per omplir una taula de registres indexada per VARCHAR2.  
 
2.- Fes un bloc PL/SQL per omplir una taula de registres indexada per VARCHAR2.  
  
Línia 246: Línia 249:
  
 
a) En un bloc PL/SQL declara una taula de registres per recollir el job_title, quants departaments tenen aquest ofici i quants empleats tenen aquest ofici. La taula estarà indexada per VARCHAR2 (job_id). Declara també les variables respectives.  
 
a) En un bloc PL/SQL declara una taula de registres per recollir el job_title, quants departaments tenen aquest ofici i quants empleats tenen aquest ofici. La taula estarà indexada per VARCHAR2 (job_id). Declara també les variables respectives.  
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
DECLARE
 
DECLARE
Línia 259: Línia 262:
  
 
</pre>
 
</pre>
−
 
+
-->
  
 
b) Mitjançant un cursor recupera el job_id i job_title de la taula JOBS i inicialitza les dades de la taula de l'apartat a) convenientment (numdepts = 0, num_emps=0). Mostra el resultat.  
 
b) Mitjançant un cursor recupera el job_id i job_title de la taula JOBS i inicialitza les dades de la taula de l'apartat a) convenientment (numdepts = 0, num_emps=0). Mostra el resultat.  
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
DECLARE
 
DECLARE
Línia 320: Línia 323:
 
OFICI: Stock Manager                            DEPARTAMENTS: 0  EMPLEATS: 0
 
OFICI: Stock Manager                            DEPARTAMENTS: 0  EMPLEATS: 0
 
</pre>
 
</pre>
−
 
+
-->
 
c) Utilitzant un cursor que recuperi tots els departaments amb els seus oficis i el número de treballadors que fan cada ofici, i actualitza la taula convenientment. Mostra el resultat.
 
c) Utilitzant un cursor que recuperi tots els departaments amb els seus oficis i el número de treballadors que fan cada ofici, i actualitza la taula convenientment. Mostra el resultat.
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
DECLARE
 
DECLARE
Línia 393: Línia 396:
 
OFICI: Stock Manager                            DEPARTAMENTS: 1  EMPLEATS: 6
 
OFICI: Stock Manager                            DEPARTAMENTS: 1  EMPLEATS: 6
 
</pre>
 
</pre>
−
 
+
-->
 
3.- Utilitzant dos cursors, un que recuperi els departaments i un segon parametritzat que a partir d'un department_id recupera els empleats, fes el següent informe:
 
3.- Utilitzant dos cursors, un que recuperi els departaments i un segon parametritzat que a partir d'un department_id recupera els empleats, fes el següent informe:
  
Línia 419: Línia 422:
 
</pre>
 
</pre>
  
−
<!-- -->
+
<!--  
 
<pre>
 
<pre>
 
DECLARE
 
DECLARE
Línia 461: Línia 464:
 
</pre>
 
</pre>
  
−
 
+
-->
  
  
 
'''Esquema SCOTT ampliat'''
 
'''Esquema SCOTT ampliat'''

Revisió del 20:01, 3 març 2020

Esquema HR

1.- Crea la taula PRESSUPOST (CREATE TABLE) que contingui els següent camps: Nom_Department i Total_Salaris. 

CREATE TABLE PRESSUPOST(
Nom_Department VARCHAR2(30) PRIMARY KEY, 
Total_Salaris NUMBER(8,2));

a) Crea la funció Total_Salari_Dept que a partir d'un número de departament ens retorni el total de salaris pressupostats per a aquell departament, tenint en compte els següents augments: 

3%: AD_ASST, SA_REP, SH_CLERK, PU_CLERK, ST_CLERK
5%: AC_ACCOUNT, FI_ACCOUNT, HR_REP, PR_REP, IT_PROG, MK_REP
7%: MK_MAN, AC_MGR, FI_MGR, PU_MAN, SA_MAN, ST_MANAGER
9%: AD_PRES, AD_VP

b) Fes un bloc PL/SQL anònim per provar la funció. Mostra el total dels salaris revisats dels departaments 10, 50 i 75. c) Crea la funció Total_Salari_Dept2 per tal que en cas de que no existeixi el número de departament utilitzi el procediment RAISE_APPLICATION_ERROR per generar una excepció amb el codi: -20215 i missatge: 'No existeix aquest departament!!'.

d) Fes un bloc anònim PL/SQL per provar el funcionament correcte de l'excepció de la funció. e) En un bloc anònim PL/SQL crea un cursor que recuperi tots els departaments que tenen empleats (manager_id not null) ordenats per departament i utilitza la funció de l'apartat a) per omplir la taula PRESSUPOST convenientment.

f) Mostreu el contingut de la taula PRESSUPOST. g) Crea la taula PRESSUPOST2 (CREATE TABLE) que contingui els següent camps: Nom_Department, First_Name, Last_Name i Total_Salaris. 

CREATE TABLE PRESSUPOST2(
Nom_Department VARCHAR2(30) PRIMARY KEY, 
First_Name VARCHAR2(20),
Last_Name VARCHAR2(25),
Total_Salaris NUMBER(8,2));

h) Crea el procediment Afegir_Pressupost_Dept que rebrà com a paràmetres el nom del departament, el identificador del manager del departament i el total dels salaris revisats del departament, i obtindrà el nom i cognom del manager del departament i afegirà un nou registre a la taula PRESSUPOST2 amb la informació corresponent.

i) Refès el bloc anònim anterior per tal de que utilitzant el procediment anterior afegeixi tots els departaments a la taula PRESSUPOST2. Tracta l'excepció que es produiria si el procediment no troba el manager per a un departament ('Error: No existeix el manager d'aquest departament') i qualsevol altre excepció que es pugui produir (codi d'error i missatge d'oracle). j) Mostreu el contingut de la taula PRESSUPOST2. 2.- Fes un bloc PL/SQL per omplir una taula de registres indexada per VARCHAR2.

que crei una taula indexada per job_id i crea un cursor que recuperi tots els empleats ordenats per departament i recorre'l per omplir la taula amb la següent informació: número d'empleats que tenen aquest ofici i el número de departaments que tenen aquest ofici. Mostra el resultat.

a) En un bloc PL/SQL declara una taula de registres per recollir el job_title, quants departaments tenen aquest ofici i quants empleats tenen aquest ofici. La taula estarà indexada per VARCHAR2 (job_id). Declara també les variables respectives.

b) Mitjançant un cursor recupera el job_id i job_title de la taula JOBS i inicialitza les dades de la taula de l'apartat a) convenientment (numdepts = 0, num_emps=0). Mostra el resultat. c) Utilitzant un cursor que recuperi tots els departaments amb els seus oficis i el número de treballadors que fan cada ofici, i actualitza la taula convenientment. Mostra el resultat. 3.- Utilitzant dos cursors, un que recuperi els departaments i un segon parametritzat que a partir d'un department_id recupera els empleats, fes el següent informe: 


                     - EMPLEATS PER DEPARTAMENT -

Departament: <nom del departament>

Cap: <nom i cognom del cap del departament>

Empleats 

<nom i cognom dels empleats>

- - - - - - - - - - - - - - - - - - - - 

Departament: <nom del departament>

Cap: <nom i cognom del cap del departament>

Empleats 

<nom i cognom dels empleats>


Esquema SCOTT ampliat

Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_conceptes_fundamentals_UF3:_PL-SQL&oldid=12925»