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

T12- Creació de funcions

• Exercici 1

a) Crea una funció anomenada Q_PROD que retorni la descripció d'un producte a una variable host (una variable global).

CREATE OR REPLACE 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;

/

b) Compila el codi, crida a la funció i tot seguit consulta la variable host per a veure el resultat.

SQL> START p12q1.sql

SQL> VARIABLE g_descrip VARCHAR2(30)

SQL> EXECUTE :g_descrip := q_prod (101863)

SQL> PRINT g_descrip

G_DESCRIP

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

SP JUNIOR RACKET

• Exercici 2

a) Crea una funció emmagatzemada ANNUAL_COMP que ens retorni el salari anual quan li passem el salari mensual i la comissió d'un empleat. La funció ha de retornar el salari anual definit per (sal*12)+comm. Assegura't que la funció controla valors NULL.

CREATE OR REPLACE FUNCTION annual_comp

(v_sal IN emp.sal%TYPE,

v_comm IN emp.comm%TYPE)

RETURN NUMBER

IS

BEGIN

RETURN (NVL(v_sal,0) * 12 + NVL(v_comm,0));

END annual_comp;

/

b) Utilitza la funció anterior en una sentència SELECT contra la taula EMP.

SQL> SELECT empno, ename, annual_comp(sal,comm) "Annual Compensation"

2 FROM emp;

EMPNO   ENAME      Annual Compensation
-----   ------     -------------------
7839    KING                      6000
7698    BLAKE                    34200
7782    CLARK                    29400
7566    JONES                    35700
...
14 rows selected.       

• Exercici 3

Crea un procediment, NEW_EMP, per inssertar un empleat nou dins de la taula EMP. El procediment haurà de contenir una crida a la funció VALID_DEPTNO per comprovar si existeix en la taula del departament especificat per al nou empleat.

a) Crea la funció VALID_DEPNO per tal que ens validi un número de departament especificat. La funció ha de retornar un BOOLEÀ.

CREATE OR REPLACE 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;

/

b) Crea el procediment NEW_EMP per afegir un nou empleat a la taula EMP. S'hauria d'afegir un nou registre a la taula EMP si la funció retorna TRUE. Si retorna FALSE, el procediment hauria d'alertar a l'usuari amb un missatge apropiat. Defineix valors DEFAULT per a la majoria d'arguments. La comissió per defecte és 0, el salari per defecte és 1000, el número de departament per defecte és 30, l'ofici per defecte és SALESMAN i el cap per defecte és 7839.

CREATE OR REPLACE 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;

/

c) Comprova el procediment NEW_EMP afegint un nou empleat (7777) al departament 99 (HARRIS). Deixar la resta de paràmetres amb el seus valors per defecte. Quin és el resultat?

SQL> EXECUTE new_emp(v_empno => 7777, v_ename => 'HARRIS', v_deptno => 99)

d) Comprovar el nou procediment NEW_EMP afegint un nou empleat (8888) al departament 30 (MIKEL). Deixar la resta de paràmetres amb els seus valors per defecte. Quin és el resultat?

SQL> EXECUTE new_emp(v_empno => 8888, v_ename => 'MIKEL')