DAI-C8-EC: Cursors. Exercicis de classe

Contingut

1 Objectius
2 Desenvolupament
3 Entrega
4 Recursos
5 Durarda

Objectius

Unitat Didàctica: UD 2. Cursors

L'alumne ja haurà instal.lat l'Oracle (client o servidor) en el seu ordinador, i podrà treballar en local o en el servidor 192.168.0.10. Així mateix, s'espera que ja estigui en condicions per poder treballar a casa.

Desenvolupament

Els exercicis vistos a classe són:

NO oblidar-se de la instrucció
SQL>SET SERVEROUTPUT ON
per poder visualitzar la sortida, ni de la "/" per a executar el bloc anònim.

DECLARE
CURSOR cur1 IS
	SELECT dnom,loc FROM depart;
	v_nom VARCHAR2(14);
	v_localitat VARCHAR2(14);
BEGIN
	OPEN cur1;
	LOOP
	FETCH cur1 INTO v_nom, v_localitat;
	EXIT WHEN cur1%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE (v_nom || '*' || v_localitat);
	END LOOP;
	CLOSE cur1;
END;
/

Atributs del cursor:
%FOUND: torna TRUE si l'últim FETCH ha retornat algun valor
%NOTFOUND: el contrari. S'utilitza com a condició de sortida dels bucles.
%ROWCOUNT: torna el número de files recuperades fins el moment pel cursor.
%ISOPEN: torna TRUE si el cursor està obert.

Els cursors es declaren, s'obren (OPEN), es recorren mitjançant un bucle i la clàusula FETCH, i finalment es tanquen
El mateix exemple utilitzant WHILE:

DECLARE
CURSOR cur1 IS
	SELECT dnom,loc FROM depart;
	v_nom VARCHAR2(14);
	v_localitat VARCHAR2(14);
BEGIN
OPEN cur1;
FETCH cur1 INTO v_nom, v_localitat;
WHILE cur1%FOUND LOOP
	DBMS_OUTPUT.PUT_LINE (v_nom || '*' || v_localitat);
	FETCH cur1 INTO v_nom, v_localitat;
END LOOP;
CLOSE cur1;
END;
/

Variables d'acoblament en l'ús de cursors

CREATE OR REPLACE PROCEDURE veure_empleat_per_dept(dep NUMBER)
AS
v_dept NUMBER(2);
CURSOR c1 IS
	SELECT cognom FROM emple WHERE dept_no=v_dept;
	v_cognom VARCHAR2(10);
BEGIN
	v_dept := dep;
	OPEN c1;
	FETCH c1 INTO v_cognom;
	WHILE c1%FOUND LOOP
	DBMS_OUTPUT.PUT_LINE(v_cognom);
	FETCH c1 INTO v_cognom;
	END LOOP;
	CLOSE c1;
END;
/

SQL>EXECUTE  veure_empleat_per_dept(30)

Cursor FOR...LOOP

DECLARE
	CURSOR cur2 IS
		SELECT cognom, ofici, comissio FROM emple WHERE comissio > 100;
BEGIN
	FOR v_reg IN cur2 LOOP
	DBMS_OUTPUT.PUT_LINE(v_reg.cognom || '*' || v_reg.ofici || '*' ||TO_CHAR(v_reg.comissio,'999,999'));
	END LOOP;
END;
/

Exemple: escriure un bloc PL/SQL que visualitzi el cognom i data d'alta de l'empresa ordenats per data d'alta,mitjançant un cursor FOR...LOOP i mitjançant un cursor amb bucle WHILE.
a) FOR..LOOP

DECLARE
	CURSOR c_emple IS
		SELECT cognom, ofici, data_alt FROM emple ORDER BY data_alt;
BEGIN
	FOR v_reg_emp IN c_emple LOOP
	DBMS_OUTPUT.PUT_LINE(v_reg_emp.cognom || '*' || v_reg_emp.ofici || '*' || v_reg_emp.data_alt);
	END LOOP;
END;
/

b) WHILE

DECLARE
	CURSOR c_emple IS
		SELECT cognom,ofici,data_alt FROM emple ORDER BY data_alt;
	v_reg_emp c_emple%ROWTYPE;
BEGIN
	OPEN c_emple;
	FETCH c_emple INTO v_reg_emp;
	WHILE c_emple%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(v_reg_emp.cognom || '*' || v_reg_emp.ofici || '*' || v_reg_emp.data_alt);
		FETCH c_emple INTO v_reg_emp;
	END LOOP;
	CLOSE c_emple;
END;
/

Cursors amb paràmetres

El cursor es declara amb paràmetres, que es passen quan l'obrim amb OPEN: OPEN nom_cursor [(parametre1,parametre2,...)]; En aquest exemple, creem un procediment que li passem 2 paràmetres, que alhora aquests paràmetres se li passen al cursor:

CREATE OR REPLACE PROCEDURE veure_per_dept_i_ofici(vdept NUMBER,Vofici VARCHAR DEFAULT 'DIRECTOR')
AS
	v_departament emple.dept_no%TYPE;
	v_ofici emple.ofici%TYPE;
	v_cognom emple.cognom%TYPE;
	v_salari emple.salari%TYPE;
	CURSOR cur1(v_departament NUMBER, v_ofici VARCHAR2 DEFAULT 'DIRECTOR')
		IS SELECT cognom,salari FROM EMPLE WHERE dept_no = v_departament AND ofici=v_ofici;
BEGIN
	OPEN cur1(vdept,vofici);
	FETCH cur1 INTO v_cognom, v_salari;
	WHILE cur1%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(v_cognom || '*' || v_salari);
		FETCH cur1 INTO v_cognom, v_salari;
	END LOOP;
	CLOSE cur1;
END veure_per_dept_i_ofici;
/

SQL>EXECUTE veure_per_dept_i_ofici(30,'VENEDOR')
SQL>EXECUTE veure_per_dept_i_ofici(30)

per obrir el cursor:
OPEN nom_cursor [(par1, par2,...)]
En el nostre exemple:
OPEN cur1(v_dep); --la variable v_dep ha de tenir un valor inicialitzat
OPEN cur1(v_dep,v_ofici);
OPEN cur1(20,'VENEDOR');

Atributs en cursors implícits

Oracle obre de forma implícita un cursor quan processa una comanda SQL que no està associada a un cursor explícit.
El cursor implícit (cursor SQL) ens proporciona informació en les comandes SELECT INTO, INSERT, UPDATE i DELETE.
Els atributs del cursor SQL són:

SQL%NOTFOUND: dóna TRUE si l'últim INSERTM, UPDATE, DELETE o SELECT INTO han fallat (no han afectat a cap fila).
SQL%FOUND
SQL%ROWCOUNT: número de files afectades
SQL%ISOPEN: sempre retorna FALS

DECLARE
	v_dept DEPART.DNOM%TYPE;
	v_loc DEPART.LOC%TYPE;
BEGIN
	v_dept := 'MARKETING'; --no existeix MARKETING
	UPDATE DEPART SET loc='SEVILLA' WHERE dnom=v_dept; --fallarà
	IF SQL%NOTFOUND THEN
	DBMS_OUTPUT.PUT_LINE('Error dactualització');
	END IF;
	SELECT loc INTO v_loc FROM depart WHERE dnom=v_dept; --fallarà i s'aixeca l'excepció NO_DATA_FOUND
	IF SQL%NOTFOUND THEN
	DBMS_OUTPUT.PUT_LINE('mai passa per aquí');
	END IF;
END;
/

CURSOR FOR UPDATE

Els cursors no només serveixen per seleccionar files d’una select, sino que també podem utilitzar el nom del cursor que apunta a una fila per realitzar una operació d’actualització en aquesta fila.
La sintaxi queda reflexada en el següent exemple:

create or replace procedure pujar_salari_departament(
vp_num_dpt NUMBER,
vp_pct_pujada NUMBER)
as
	cursor c_emple is select ofici,salari from emple 
	where dept_no=vp_num_dpt FOR UPDATE;
	
	vc_reg_emple c_emple%rowtype;
	v_inc number(8,2);
begin
	open c_emple;
	fetch c_emple into vc_reg_emple;
	while c_emple%found loop
		v_inc := vc_reg_emple.salari/100 * vp_pct_pujada;
	update emple set salari=salari + v_inc where current of c_emple;
	fetch c_emple into vc_reg_emple;
	end loop;
end pujar_salari_departament;
/

SQL> SELECT cognom, salari from emple where dept_no=20;
SQL> EXECUTE  pujar_salari_departament(20,5)
SQL> SELECT cognom, salari from emple where dept_no=20;

A partir d'aquest codi vist a classe, hauràs d'implementar un script que executi tot aquest codi, i també hauràs de fer un fitxer amb la sortida del log.

A més, implementaràs el procediment pujar_salari_antiguitat que es mostra a continuació:

REM Enunciat: Pujar el salari dels empleats un 5% + (anys d'antiguitat)*0,5% 
REM Aquest exercici ha de servir de model per als exercicis que es fan a classe, i és la manera de procedir que es demanarà per a l'examen.

REM El_Teu_Nom num: dai2axx

REM la resolució d'un problema, tal com es demanarà en l'examen, consta de:
REM 1. enunciat
REM 2. pensar el problmea
REM 3. codificació - compilació
REM 4. depuració dels errors
REM 5. Joc de proves que mostri les diferents causístiques de l'enunciat
REM 6. Ampliació del problema, si s'escau.

REM enunciat (exercici sobre cursors)
1. Pujar el salari dels empleats un 5% + (anys d'antiguitat)*0,5%

REM proves prèvies

update emple set salari=salari*(1.05);
rollback;

select data_alt, trunc((sysdate-data_alt)/365) num_anys from emple;


SET SERVEROUTPUT ON

REM farem l'exercici de dues maneres: cursor normal i 'cursor for update'
REM la idea és que recorrem el cursor fila a fila, i en cada fila fem un update personalitzat segons els anys d'antiguitat de l'empleat en qüestió

REM 1a manera

CREATE OR REPLACE PROCEDURE pujar_salari_antiguitat
AS
	CURSOR cur1 IS
	SELECT EMP_NO,SALARI,trunc((sysdate-data_alt)/365) FROM EMPLE;
	v_emp_no emple.emp_no%TYPE;
	v_salari emple.salari%TYPE;
	v_anys_antiguitat NUMBER(2);
	v_salari_nou NUMBER(6,2);
BEGIN
	OPEN cur1;
	LOOP
	FETCH cur1 INTO v_emp_no ,v_salari,v_anys_antiguitat;
	EXIT WHEN cur1%NOTFOUND;
	v_salari_nou:=v_salari*(1.05)+v_salari*0.005* v_anys_antiguitat;
	UPDATE EMPLE SET SALARI=v_salari_nou WHERE EMP_NO=v_emp_no;
	--DBMS_OUTPUT.PUT_LINE ('num empleat: ' || v_emp_no);
	--DBMS_OUTPUT.PUT_LINE ('salari antic: ' || v_salari);
	--DBMS_OUTPUT.PUT_LINE ('salari nou: ' || v_salari_nou);
	--DBMS_OUTPUT.PUT_LINE ('--------------------');
	END LOOP;
	CLOSE cur1;
END;
/

--execute pujar_salari_antiguitat


REM 2a manera. Cursor for update


CREATE OR REPLACE PROCEDURE pujar_salari_antiguitat2
AS
	CURSOR cur1 IS
	SELECT EMP_NO,SALARI,trunc((sysdate-data_alt)/365) FROM EMPLE FOR UPDATE;
	v_emp_no emple.emp_no%TYPE;
	v_salari emple.salari%TYPE;
	v_anys_antiguitat NUMBER(2);
	v_salari_nou NUMBER(6,2);
BEGIN
	OPEN cur1;
	LOOP
	FETCH cur1 INTO v_emp_no ,v_salari,v_anys_antiguitat;
	EXIT WHEN cur1%NOTFOUND;
	v_salari_nou:=v_salari*(1.05)+v_salari*0.005* v_anys_antiguitat;
	UPDATE EMPLE SET SALARI=v_salari_nou where current of cur1;
	--DBMS_OUTPUT.PUT_LINE ('num empleat: ' || v_emp_no);
	--DBMS_OUTPUT.PUT_LINE ('salari antic: ' || v_salari);
	--DBMS_OUTPUT.PUT_LINE ('salari nou: ' || v_salari_nou);
	--DBMS_OUTPUT.PUT_LINE ('--------------------');
	END LOOP;
	CLOSE cur1;
END;
/

--execute pujar_salari_antiguitat2

REM Joc de Proves

prompt Salaris inicials
select emp_no, salari from emple where emp_no=7566 or emp_no=7654;

pause

execute pujar_salari_antiguitat2

prompt Salaris després de la pujada
select emp_no, salari from emple where emp_no=7566 or emp_no=7654;

Entrega

entregar els fitxers DAI2AXX_script_NA3.sql i DAI2AXX_NA3.log. Si has treballat per parelles: DAI2AXXXX_script_NA3.sql i DAI2AXXXX_NA3.log, indicant a dins clarament el nom i el número de classe (comentaris amb REM). Exercici individual o per parelles.
entregar al Moodle: http://192.168.0.15/moodle

Recursos

En el Moodle també pots descarregar el codi per crear les taules, i el codi que s'ha vist a classe.

Durarda

1,5 hores

creat per Joan Quintana Compte, octubre 2009

DAI-C8-EC: Cursors. Exercicis de classe

Contingut

Objectius

Desenvolupament

Entrega

Recursos

Durarda

Menú de navegació

Eines de l'usuari

Espais de noms

Variants

Vistes

Més

Cerca

Navegació

Institut Jaume Balmes

Màquines recreatives

CNC

Informàtica musical

joanillo.org Planet

Eines