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

De wikiserver
Dreceres ràpides: navegació, cerca

T11- Creació de procediments

  • Exercici 1

a) Crea un procediment anomenat ADD_PROD per insertar un producte nou a la taula PRODUCT

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

b) Compila el codi, cridar al procediment i consulta la taula PRODUCT per veure els resultats.

SQL> START p11q1.sql
SQL> EXECUTE add_prod(999, 'SP TENNIS BALLS')

c) Crida de nou el procediment, passat l'identificador de producte 100860 ¿què passa i per què?

SQL> EXECUTE add_prod(100860, 'SP ADULT TENNIS RACKET')
  • Exercici 2

a) Crea un procediment anomenat UPD_PROD per actualitzar la descripció d'un producte de la taula PRODUCT. Inclou la manipulació d'excepcions necessària.

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

b) Compila el codi, crida al procediment i consulta la taula PRODUCT per veure els resultats. Comprova el control d'excepcions, intentant modificar un producte que no existeix.

SQL> START p11q2.sql
SQL> EXECUTE upd_prod(9999, 'SP TENNIS NETS')
  • Exercici 3

a) Crea un procediment anomenat DEL_PROD per esborrar un producte de la taula PRODUCT. Inclou la manipulació d'excepcions necessària.

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

b) Compila el codi, crida al procediment i consulta la taula PRODUCT per veure els resultats. Verifica també el control d'excepcions intentant esborrar un producte que no existeix.

SQL> START p11q3.sql
SQL> EXECUTE del_prod(9999)
  • Exercici 4

a) Crea un procediment que a partir del codi d'un empleat (empno), recuperi de la taula EMP el seu salari i ofici.

CREATE OR REPLACE PROCEDURE query_emp
(v_empno IN emp.empno%TYPE,
v_sal OUT emp.sal%TYPE,
v_job OUT emp.job%TYPE)
IS
BEGIN
SELECT sal, job
INTO v_sal, v_job
FROM emp
WHERE empno = v_empno;
END query_emp;
/

b) Compila el codi, crida al procediment i visualitza el salari i l'ofici per a l'empleat amb codi 7839·

SQL> START p10q4.sql
SQL> VARIABLE g_sal NUMBER
SQL> VARIABLE g_job VARCHAR2(15)
SQL> EXECUTE query_emp (7839, :g_sal, :g_job)
SQL> PRINT g_sal
G_SAL
-----------
5000
SQL> PRINT g_job
G_JOB
--------------------
PRESIDENT

c) Crida una altra vegada al procediment passant ara el número de empleat 9898. Què passa? Per què?

SQL> EXECUTE query_emp (9898, :g_sal, :g_job)

Error que empieza en la línea 5 del comando: EXECUTE query_emp (9898, :g_sal, :g_job) Informe de error: ORA-01403: No se ha encontrado ningún dato ORA-06512: en "SCOTT.QUERY_EMP", línea 9 ORA-06512: en línea 1 01403. 00000 - "no data found"

  • Cause:
  • Action:
Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF3:_Llenguatges_SQL_/_Solucions_PL-SQL_T11&oldid=4838»