Exercicis resolts de PL/SQL: procediments, funcions, cursors i triggers

Exercicis de PL/SQL

Blocs anònims i bucles

Exercici 3: mostrar nº i son cuadrado:

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Número '||i||' cuadrado de '||i||' es igual a '||i**2);
END LOOP;
END;

Exercici 4: mostrar nº imparells:

BEGIN
FOR i IN 1..50 LOOP
IF i MOD 2 != 1 THEN
DBMS_OUTPUT.PUT_LINE('Número imparell '||i);
END IF;
END LOOP;
END;

Exercici 5: taula multiplicar del 7:

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i||' per 7 és igual a '||i*7);
END LOOP;
END;

Exercici 6: taula de multiplicar de qualsevol nombre:

DECLARE
x NUMBER;
BEGIN
x := &Introdueix_numero;
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i||' per '||x||' és igual a '||i*x);
END LOOP;
END;

Procediments i funcions

Exercici 1: suma de 2 nombres:

CREATE OR REPLACE PROCEDURE suma(a NUMBER, b NUMBER) IS
s NUMBER;
BEGIN
s := a + b;
DBMS_OUTPUT.PUT_LINE('La suma de '||a||' i '||b||' és '||s);
END;

Exercici 2: invertir una cadena:

CREATE OR REPLACE PROCEDURE voltear(p VARCHAR2) IS
destino VARCHAR2(50);
letra VARCHAR2(1);
BEGIN
FOR i IN REVERSE 1..LENGTH(p) LOOP
letra := SUBSTR(p,i,1);
destino := destino || letra;
END LOOP;
DBMS_OUTPUT.PUT_LINE(destino);
END;

Exercici 3: suma de 2 nombres amb funció:

CREATE OR REPLACE FUNCTION suma(a NUMBER, b NUMBER) RETURN NUMBER IS
s NUMBER;
BEGIN
s := a + b;
RETURN s;
END;

Crida a la funció:

BEGIN
DBMS_OUTPUT.PUT_LINE(suma(1,2));
END;

Exercici 4: any en nombre:

CREATE OR REPLACE FUNCTION fecha(fecha DATE) RETURN NUMBER IS
a NUMBER;
an VARCHAR2(10);
BEGIN
an := TO_CHAR(fecha,'YYYY');
a := TO_NUMBER(an);
RETURN a;
END;

Exercici 6: anys de diferència:

CREATE OR REPLACE FUNCTION ano(ano1 DATE, ano2 DATE) RETURN NUMBER IS
d NUMBER;
BEGIN
d := TRUNC(ABS(MONTHS_BETWEEN(ano1,ano2)/12),0);
RETURN d;
END;

Exercici 7: trienis:

CREATE OR REPLACE FUNCTION trienio(ano1 DATE, ano2 DATE) RETURN NUMBER IS
a NUMBER;
BEGIN
a := TRUNC(ano(ano1,ano2)/3,0);
RETURN a;
END;

Exercici 8: suma de 5 nombres:

CREATE OR REPLACE PROCEDURE suma(n1 NUMBER, n2 NUMBER, n3 NUMBER DEFAULT 0, n4 NUMBER DEFAULT 0, n5 NUMBER DEFAULT 0) IS
total NUMBER;
BEGIN
total := n1+n2+n3+n4+n5;
DBMS_OUTPUT.PUT_LINE(total);
END;

Exercici 9: mostrar lletres:

CREATE OR REPLACE FUNCTION letras(p VARCHAR2) RETURN VARCHAR2 IS
destino VARCHAR2(50);
letra VARCHAR2(1);
BEGIN
FOR i IN 1..LENGTH(p) LOOP
letra := SUBSTR(p,i,1);
IF (letra BETWEEN 'a' AND 'z') OR (letra BETWEEN 'A' AND 'Z') THEN
destino := destino || letra;
ELSE
destino := destino || ' ';
END IF;
END LOOP;
RETURN destino;
END;

Exercici 10: esborrar empleat:

CREATE OR REPLACE PROCEDURE bemple(n NUMBER) IS
a NUMBER;
BEGIN
SELECT COUNT(*) INTO a FROM emple WHERE emp_no = n;
IF a = 1 THEN
DELETE FROM emple WHERE emp_no = n;
END IF;
END;

Cursors

Exercici 1: ordenar empleats per cognom:

CREATE OR REPLACE PROCEDURE listar IS
CURSOR cur_emp IS SELECT apellido, fecha_alt FROM emple ORDER BY apellido;
emp_reg cur_emp%ROWTYPE;
BEGIN
FOR emp_reg IN cur_emp LOOP
DBMS_OUTPUT.PUT_LINE(emp_reg.apellido||' '||emp_reg.fecha_alt);
END LOOP;
END;

Exercici 2: nº d'empleats per departament:

CREATE OR REPLACE PROCEDURE dept IS
CURSOR cur_dept IS
SELECT dnombre, COUNT(emp_no) AS num
FROM depart d, emple e
WHERE d.dept_no = e.dept_no(+)
GROUP BY dnombre;
BEGIN
FOR dept_reg IN cur_dept LOOP
DBMS_OUTPUT.PUT_LINE(dept_reg.dnombre||' '||dept_reg.num);
END LOOP;
END;

Exercici 3: 5 salaris més alts:

CREATE OR REPLACE PROCEDURE sal IS
CURSOR cur_emp IS SELECT apellido, salario FROM emple ORDER BY salario DESC;
emp_sal cur_emp%ROWTYPE;
a NUMBER := 0;
BEGIN
OPEN cur_emp;
WHILE a < 5 LOOP
FETCH cur_emp INTO emp_sal;
DBMS_OUTPUT.PUT_LINE(emp_sal.apellido||' '||emp_sal.salario);
a := a + 1;
END LOOP;
CLOSE cur_emp;
END;

Exercici 4: 2 empleats per ofici que cobren menys:

DECLARE
CURSOR cur_emp IS SELECT apellido, salario, oficio FROM emple ORDER BY oficio, salario;
emp_reg cur_emp%ROWTYPE;
anterior emple.oficio%TYPE;
cont NUMBER;
BEGIN
OPEN cur_emp;
anterior := ' ';
FETCH cur_emp INTO emp_reg;
WHILE cur_emp%FOUND LOOP
IF anterior != emp_reg.oficio THEN
anterior := emp_reg.oficio;
cont := 1;
END IF;
IF cont <= 2 THEN
DBMS_OUTPUT.PUT_LINE(emp_reg.apellido||' '||emp_reg.salario||' '||emp_reg.oficio);
END IF;
FETCH cur_emp INTO emp_reg;
cont := cont + 1;
END LOOP;
CLOSE cur_emp;
END;

Inserció i actualització

Exercici 5: inserir departament:

CREATE OR REPLACE PROCEDURE in_depart(n_depart VARCHAR2, n_loc VARCHAR2) IS
maxim NUMBER;
BEGIN
SELECT NVL(MAX(dept_no),0)+10 INTO maxim FROM depart;
INSERT INTO depart VALUES (maxim, n_depart, n_loc);
END;

Exercici 6: pujar salari:

CREATE OR REPLACE PROCEDURE pujar_salari(num NUMBER, importe NUMBER, porcentaje NUMBER) IS
CURSOR cur IS SELECT emp_no, apellido, salario FROM emple WHERE dept_no = num;
salario_actual NUMBER;
salario_importe NUMBER;
salario_porcentaje NUMBER;
BEGIN
FOR emp IN cur LOOP
salario_actual := emp.salario;
salario_importe := salario_actual + importe;
salario_porcentaje := (salario_actual*(porcentaje/100)) + salario_actual;
IF salario_importe > salario_porcentaje THEN
DBMS_OUTPUT.PUT_LINE('A '||emp.apellido||' pujada per import. Anterior '||salario_actual||' Ara: '||salario_importe);
UPDATE emple SET salario = salario_importe WHERE emp_no = emp.emp_no;
ELSIF salario_importe = salario_porcentaje THEN
DBMS_OUTPUT.PUT_LINE('A '||emp.apellido||' pujada per import. Anterior '||salario_actual||' Ara: '||salario_importe);
ELSE
DBMS_OUTPUT.PUT_LINE('A '||emp.apellido||' pujada per import. Anterior '||salario_actual||' Ara: '||salario_importe);
UPDATE emple SET salario = salario_importe WHERE emp_no = emp.emp_no;
END IF;
END LOOP;
END;

Exercici 8: llistat de liquidació:

DECLARE
CURSOR cur IS
SELECT apellido, dnombre, oficio, salario, fecha_alt, comision, dir, emp_no
FROM emple e, depart d
WHERE e.dept_no = d.dept_no
ORDER BY apellido;
ast VARCHAR2(40) := '*';
dif NUMBER DEFAULT 0;
tri NUMBER(3) DEFAULT 0;
res NUMBER(3) DEFAULT 0;
tot NUMBER(9,2) DEFAULT 0;
com NUMBER;
BEGIN
FOR emp IN cur LOOP
dif := TRUNC(ABS(MONTHS_BETWEEN(SYSDATE,emp.fecha_alt))/(12*3));
tri := dif*50;
SELECT COUNT(*) INTO res FROM emple WHERE dir = emp.emp_no;
res := res*100;
com := NVL(emp.comision,0);
tot := tri + res + emp.salario + com;
ast := RPAD(ast,30,'*');
DBMS_OUTPUT.PUT_LINE(ast);
DBMS_OUTPUT.PUT_LINE('Liquidació de l''empleat '||emp.apellido);
DBMS_OUTPUT.PUT_LINE('Departament '||emp.dnombre);
DBMS_OUTPUT.PUT_LINE('Ofici '||emp.oficio);
DBMS_OUTPUT.PUT_LINE('Salari '||emp.salario);
DBMS_OUTPUT.PUT_LINE('Trienis '||tri);
DBMS_OUTPUT.PUT_LINE('Comp. Responsabilitat '||res);
DBMS_OUTPUT.PUT_LINE('Comissió '||com);
DBMS_OUTPUT.PUT_LINE(ast);
DBMS_OUTPUT.PUT_LINE('Total '||tot);
DBMS_OUTPUT.PUT_LINE(ast);
END LOOP;
END;

Triggers

Trigger de salari total per departament:

CREATE OR REPLACE TRIGGER Sal_Total
AFTER INSERT OR DELETE OR UPDATE OF (SALARIO, DEPT_NO) ON emple
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE depart SET sal_total = NVL(sal_total,0) + NVL(:NEW.salario,0) WHERE dept_no = :NEW.dept_no;
ELSIF DELETING THEN
UPDATE depart SET sal_total = NVL(sal_total,0) - NVL(:OLD.salario,0) WHERE dept_no = :OLD.dept_no;
ELSIF UPDATING THEN
UPDATE depart SET sal_total = NVL(sal_total,0) - NVL(:OLD.salario,0) WHERE dept_no = :OLD.dept_no;
UPDATE depart SET sal_total = NVL(sal_total,0) + NVL(:NEW.salario,0) WHERE dept_no = :NEW.dept_no;
END IF;
END;

Trigger de control d'estoc:

CREATE OR REPLACE TRIGGER controlstock
BEFORE INSERT ON ventaprod
FOR EACH ROW
DECLARE
a EXCEPTION;
st NUMBER;
BEGIN
SELECT stock INTO st FROM productos WHERE cod_producto = :NEW.cod_producto;
IF st < :NEW.unidades THEN
RAISE a;
END IF;
END;

Paquets

Especificació del paquet GEST_DEPART:

CREATE OR REPLACE PACKAGE GEST_DEPART IS
PROCEDURE INS_DEPART(NOM VARCHAR2, L VARCHAR2);
PROCEDURE ELIM_DEPART(DEPT1 NUMBER, DEPT2 NUMBER);
PROCEDURE MODI_LOC_DEPART(DEPT NUMBER, L VARCHAR2);
PROCEDURE VISU_DEPART(DEPT NUMBER);
PROCEDURE VISU_DEPART(NOM VARCHAR2);
END GEST_DEPART;

Cos del paquet GEST_DEPART:

CREATE OR REPLACE PACKAGE BODY GEST_DEPART IS
FUNCTION BUSC_NOM_DEPART(NOM VARCHAR2) RETURN NUMBER IS
d NUMBER;
BEGIN
SELECT dept_no INTO d FROM depart WHERE dnombre = NOM;
RETURN d;
END BUSC_NOM_DEPART;

PROCEDURE INS_DEPART(NOM VARCHAR2, L VARCHAR2) IS
cont NUMBER;
dnum NUMBER;
BEGIN
SELECT COUNT(*) INTO cont FROM depart WHERE dnombre = NOM;
IF cont = 0 THEN
SELECT NVL(MAX(dept_no),0)+10 INTO dnum FROM depart;
INSERT INTO depart (dept_no, dnombre, loc) VALUES (dnum, NOM, L);
ELSE
DBMS_OUTPUT.PUT_LINE('El nom ja existeix');
END IF;
END INS_DEPART;

PROCEDURE ELIM_DEPART(DEPT1 NUMBER, DEPT2 NUMBER) IS
d1 NUMBER;
d2 NUMBER;
BEGIN
SELECT COUNT(dept_no) INTO d1 FROM depart WHERE dept_no = DEPT1;
SELECT COUNT(dept_no) INTO d2 FROM depart WHERE dept_no = DEPT2;
IF d1 = 1 AND d2 = 1 THEN
UPDATE emple SET dept_no = DEPT2 WHERE dept_no = DEPT1;
DELETE FROM depart WHERE dept_no = DEPT1;
ELSE
DBMS_OUTPUT.PUT_LINE('El departament '||DEPT1||' o el '||DEPT2||' no existeix');
END IF;
END ELIM_DEPART;

PROCEDURE MODI_LOC_DEPART(DEPT NUMBER, L VARCHAR2) IS
cont NUMBER;
BEGIN
SELECT COUNT(dept_no) INTO cont FROM depart WHERE dept_no = DEPT;
IF cont = 1 THEN
UPDATE depart SET loc = L WHERE dept_no = DEPT;
ELSE
DBMS_OUTPUT.PUT_LINE('No existeix el departament '||DEPT);
END IF;
END MODI_LOC_DEPART;

PROCEDURE VISU_DEPART(DEPT NUMBER) IS
nom depart.dnombre%TYPE;
l depart.loc%TYPE;
cont NUMBER;
n NUMBER;
BEGIN
SELECT COUNT(dept_no) INTO n FROM depart WHERE dept_no = DEPT;
IF n = 1 THEN
SELECT dnombre, loc INTO nom, l FROM depart WHERE dept_no = DEPT;
SELECT COUNT(*) INTO cont FROM emple WHERE dept_no = DEPT;
DBMS_OUTPUT.PUT_LINE('Departament número: '||DEPT);
DBMS_OUTPUT.PUT_LINE('Departament: '||nom);
DBMS_OUTPUT.PUT_LINE('Localitat: '||l);
DBMS_OUTPUT.PUT_LINE('Número d''empleats: '||cont);
ELSE
DBMS_OUTPUT.PUT_LINE('El departament no existeix');
END IF;
END VISU_DEPART;

PROCEDURE VISU_DEPART(NOM VARCHAR2) IS
d NUMBER;
n NUMBER;
BEGIN
SELECT COUNT(dept_no) INTO n FROM depart WHERE dnombre = NOM;
IF n = 1 THEN
d := BUSC_NOM_DEPART(NOM);
VISU_DEPART(d);
ELSE
DBMS_OUTPUT.PUT_LINE('El departament no existeix');
END IF;
END VISU_DEPART;

END GEST_DEPART;