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

De wikiserver
Dreceres ràpides: navegació, cerca

T13- Creació de paquets

  • Exercici 1

Crea la especificació i el cos d'un paquet anomenat PROD_PACK que contingui els procediments creats ADD_PROD, UPD_PROD i DEL_PROD i la funció Q_PROD.

a) Fes tots els programes públics. (considereu si seguiu necessitant els procediments i funcions empaquetats com objectes independents)


CREATE OR REPLACE PACKAGE prod_pack IS
PROCEDURE add_prod
(v_prodid IN product.prodid%TYPE,
v_descrip IN product.descrip%TYPE);
PROCEDURE upd_prod
(v_prodid IN product.prodid%TYPE,
v_descrip IN product.descrip%TYPE);
PROCEDURE del_prod
(v_prodid IN product.prodid%TYPE);
FUNCTION q_prod
(v_prodid IN product.prodid%TYPE)
RETURN VARCHAR2;
END prod_pack;
/


CREATE OR REPLACE PACKAGE BODY prod_pack IS


PROCEDURE add_prod
(v_prodid IN product.prodid%TYPE,
v_descrip IN product.descrip%TYPE)
IS
BEGIN
INSERT INTO product (prodid, descrip)
VALUES (v_prodid, v_descrip);
COMMIT;
END add_prod;


PROCEDURE upd_prod
(v_prodid IN product.prodid%TYPE,
v_descrip IN product.descrip%TYPE)
IS
BEGIN
UPDATE product
SET descrip = v_descrip
WHERE prodid = v_prodid;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,'Cap producte actualitzat.');
END IF;
END upd_prod;


PROCEDURE del_prod
(v_prodid IN product.prodid%TYPE)
IS
BEGIN
DELETE FROM product
WHERE prodid = v_prodid;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20203,'Cap producte eliminat.');
END IF;
END del_prod;


FUNCTION q_prod
(v_prodid IN product.prodid%TYPE)
RETURN VARCHAR2
IS
v_descrip product.descrip%TYPE;
BEGIN
SELECT descrip
INTO v_descrip
FROM product
WHERE prodid = v_prodid;
RETURN (v_descrip);
END q_prod;


END prod_pack;
/

b) Crida al procediment DEL_PROD. 

 SQL> execute prod_pack.del_prod(100860)

c) Consulta la taula PRODUCT per veure el resultat. 

SQL> SELECT * from product
 2   WHERE prodid = 100860;
  • Exercici 2

a) Crea l'especificació i el cos d'un paquet anomenat EMP_PACK que contingui els procediment NEW_EMP com a construcció pública i la funció VALID_DEPTNO com a construcció privada.

CREATE OR REPLACE PACKAGE emp_pack IS
PROCEDURE new_emp
(v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_job emp.job%TYPE DEFAULT 'SALESMAN',
v_mgr emp.mgr%TYPE DEFAULT 7839,
v_sal emp.sal%TYPE DEFAULT 1000,
v_comm emp.comm%TYPE DEFAULT 0,
v_deptno emp.deptno%TYPE DEFAULT 30);
END emp_pack;
/
CREATE OR REPLACE PACKAGE BODY emp_pack IS


FUNCTION valid_deptno
(v_deptno IN dept.deptno%TYPE)
RETURN BOOLEAN
IS
v_dummy VARCHAR2(1);
BEGIN
SELECT 'x'
INTO v_dummy
FROM dept
WHERE deptno = v_deptno;
RETURN (TRUE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN (FALSE);
END valid_deptno;


PROCEDURE new_emp
(v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_job emp.job%TYPE DEFAULT 'SALESMAN',
v_mgr emp.mgr%TYPE DEFAULT 7839,
v_sal emp.sal%TYPE DEFAULT 1000,
v_comm emp.comm%TYPE DEFAULT 0,
v_deptno emp.deptno%TYPE DEFAULT 30)
IS
BEGIN
IF valid_deptno(v_deptno) THEN
INSERT INTO emp
VALUES (v_empno, v_ename, v_job, v_mgr, TRUNC (SYSDATE, 'DD'), v_sal, v_comm, v_deptno);
ELSE
DBMS_OUTPUT.PUT_LINE ('Número de departament invalid. Intenta-ho de nou.');
END IF;
END new_emp;


END emp_pack;
/


b) Crida al procediment NEW_EMP utiitzant el valor 99 com a número de departament. 

 SQL> execute emp_pack.new_emp(v_empno => 7777,v_name => 'HARRIS', v_deptno => 99)

c) Crida al procediment NEW_EMP utiitzant el valor 30 com a número de departament. 

 SQL> execute emp_pack.new_emp(v_empno => 8888, v_name => 'JONES', v_deptno => 30)
  • Exercici 3

Crea un paquet anomenat CHK_PACK que contingui els procediments CHK_HIREDATE i CHK_DEPT_MGR. Fes-los com a programes públics.

a) El procediment CHK_HIREDATE comprova si la data de contractació d'un empleat està dins el rang següent (sysdate-50 years, sysdate+3months)

Nota: si la data és invalida ha d'aparèixer un missatge d'error indicant perquè no s'accepta aquesta data. Utilitza una constant per a referir-se al límit de 50 anys. Si el valor de la data de contractació és un valor nul, aquesta serà considerada una data de contractació invalida.

CREATE OR REPLACE PACKAGE chk_pack IS
PROCEDURE chk_hiredate
(v_date IN emp.hiredate%TYPE);
PROCEDURE chk_dept_mgr
(v_empno IN emp.empno%TYPE,
v_mgr IN emp.mgr%TYPE);
END chk_pack;
/

b) El procediment CHK_DEPT_MGR comprova la combinació del departament i del director per a un empleat donat. Això significa que el número de director facilitat ha de ser igual al número de director que supervisa el departament de l'empleat.

Nota: Si la combinació número/director és invalida ha d'aparèixer un missatge d'error. Assegura't de controlar el cas en el que no hi ha director pel departament.


CREATE OR REPLACE PACKAGE chk_pack IS
PROCEDURE chk_hiredate
(v_date IN emp.hiredate%TYPE);
PROCEDURE chk_dept_mgr
(v_empno IN emp.empno%TYPE,
v_mgr IN emp.mgr%TYPE);
END chk_pack;
/


CREATE OR REPLACE PACKAGE BODY chk_pack IS


PROCEDURE chk_hiredate(v_date IN emp.hiredate%TYPE)
IS
v_low date emp.hiredate%TYPE := sysdate - (50 * 365);
v_high date emp.hiredate%TYPE := add_moths(sysdate,3);
BEGIN
IF TRUNC(v_date) NOT BETWEEN v_low and v_high OR v_date IS NULL THEN
RAISE_APPLICATION_ERROR(-20200,'No és una data de contractació correcte.');
END IF;
END chk_hiredate;


PROCEDURE chk_dept_mgr(v_empno IN emp.empno%TYPE,
v_mgr IN emp.mgr%TYPE)
IS
v_empnr emp.empno%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
--obtenir el número del departament.
SELECT deptno
INTO v_deptno
FROM emp
WHERE empno = v_empno;
--comprova si la combinació deptno/mgr és correcte.
SELECT empno
INTO v_empnr
FROM emp
WHERE deptno = v_deptno AND empno = v_mgr AND job = 'MANAGER';
IF v_empnr IS NULL THEN
DBMS_OUTPUT.PUT_LINE('No existeix cap Manager per aquest departament');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20201,'No hi ha cap mgr per a aquest departament.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20202,'S'ha produït un altre error.');
END chk_dept_mgr;


END chk_pack;
/


c) Prova el procediment CHK_HIREDATE mitjançant la comanda següent: 

   SQL> execute chk_pack.chk_hiredate('01-ENE-47')

d) Prova el procediment CHK_HIREDATE mitjançant la comanda següent: 

   SQL> execute chk_pack.chk_hiredate(NULL)

e) Prova el procediment CHK_HIREDATE mitjançant la comanda següent: 

   SQL> execute chk_pack.chk_hiredate('01-ENE-98')



CREATE OR REPLACE PACKAGE chk_pack IS
PROCEDURE chk_hiredate
(v_date IN emp.hiredate%TYPE);
PROCEDURE chk_dept_mgr
(v_empno IN emp.empno%TYPE,
v_mgr IN emp.mgr%TYPE);
END chk_pack;
Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF3:_Llenguatges_SQL_/_Solucions_PL-SQL_T13&oldid=4853»