Joan: New page: ==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. A...

2009-11-05T17:48:28Z

New page: ==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. A...

Pàgina nova

==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==
Exercicis a realitzar:

1. Desenvolupar un procediment que visualitzi el cognom i la data d'alta de tots els empleats, ordenats per cognom.

2. Codificar un procediment que mostri el nom de cada departament i el número d'empleats que té.

3. Escriure un procediment que rebi una cadena i visualitzi el cognom i el número d'empleat de tots els empleats el cognom dels quals contingui la cadena especificada. Al final visualitzar el número d'empleats que es mostren.

4. Escriure un programa que visualitzi el cognom i el salari dels cinc empleats que tenen el salari més alt.

5. Codificar un programa que visualitzi els dos empleats que guanyen menys de cada ofici.

6. Escriure un programa que mostri, en un format adequat, la següent informació:

- Per cada empleat: cognom i salari.
- Per cada departament: Número d'empleats i suma dels salaris del departament.
- Al final del llistat: Número total d'empleats i suma de tots els salaris

==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==
===Solució dels exercicis===
<pre>
1. Desenvolupar un procediment que visualitzi el cognom i la data d'alta de tots els empleats, ordenats per cognom.

CREATE OR REPLACE PROCEDURE veure_empleat
AS
CURSOR c_emple IS
SELECT cognom, DATA_ALT
FROM EMPLE
ORDER BY COGNOM;
v_cognom VARCHAR2(10);
v_data DATE;
BEGIN
OPEN c_emple;
FETCH c_emple into v_cognom, v_data;
WHILE c_emple%FOUND LOOP
DBMS_OUTPUT.PUT_LINE( v_cognom||' * '||v_data);
FETCH c_emple into v_cognom,v_data;
END LOOP;
CLOSE c_emple;
END veure_empleat;
/

2. Codificar un procediment que mostri el nom de cada departament i el número d'empleats que té.

CREATE OR REPLACE PROCEDURE veure_emple_depart
AS
CURSOR c_emple IS
SELECT dnom, COUNT(emp_no)
FROM emple e, depart d
WHERE d.dept_no = e.dept_no(+)
GROUP BY dnom;
v_dnom depart.dnom%TYPE;
v_num_emple BINARY_INTEGER;
BEGIN
OPEN c_emple;
FETCH c_emple into v_dnom, v_num_emple;
WHILE c_emple%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_dnom||' * '||v_num_emple);
FETCH c_emple into v_dnom,v_num_emple;
END LOOP;
CLOSE c_emple;
END veure_emple_depart;
/

3. Escriure un procediment que rebi una cadena i visualitzi el cognom i el número d'empleat de tots els empleats el cognom dels quals contingui la cadena especificada. Al final visualitzar el número d'empleats que es mostren.

CREATE OR REPLACE PROCEDURE veure_empleat_cognom(
cadena VARCHAR2)
AS
cad VARCHAR2(10);
CURSOR c_emple IS
SELECT cognom, emp_no FROM emple
WHERE cognom LIKE cad;
vr_emple c_emple%ROWTYPE;
BEGIN
cad :='%'||cadena||'%';
OPEN c_emple;
FETCH c_emple INTO vr_emple;
WHILE (c_emple%FOUND) LOOP
DBMS_OUTPUT.PUT_LINE(vr_emple.emp_no||' * '
||vr_emple.cognom);
FETCH c_emple INTO vr_emple;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMERO DE EMPLEATS: '
|| c_emple%ROWCOUNT);
CLOSE c_emple;
END veure_empleat_cognom;
/

4. Escriure un programa que visualitzi el cognom i el salari dels cinc empleats que tenen el salari més alt.

CREATE OR REPLACE PROCEDURE emp_5maxsal
AS
CURSOR c_emp IS
SELECT cognom, salari FROM emple
ORDER BY salari DESC;
vr_emp c_emp%ROWTYPE;
i NUMBER;
BEGIN
i:=1;
OPEN c_emp;
FETCH c_emp INTO vr_emp;
WHILE c_emp%FOUND AND i<=5 LOOP
DBMS_OUTPUT.PUT_LINE(vr_emp.cognom ||
' * '|| vr_emp.salari);
FETCH c_emp INTO vr_emp;
i:=I+1;
END LOOP;
CLOSE c_emp;
END emp_5maxsal;
/

5. Codificar un programa que visualitzi els dos empleats que guanyen menys de cada ofici.

CREATE OR REPLACE PROCEDURE emp_2minsal
AS
CURSOR c_emp IS
SELECT cognom, ofici, salari FROM emple
ORDER BY ofici, salari;
vr_emp c_emp%ROWTYPE;
ofici_ant EMPLE.OFICI%TYPE;
i NUMBER;
BEGIN
OPEN c_emp;
ofici_ant:='*';
FETCH c_emp INTO vr_emp;
WHILE c_emp%FOUND LOOP
IF ofici_ant <> vr_emp.ofici THEN
ofici_ant := vr_emp.ofici;
i := 1;
END IF;
IF i <= 2 THEN
DBMS_OUTPUT.PUT_LINE(vr_emp.ofici||' * '
||vr_emp.cognom||' * '
||vr_emp.salari);
END IF;
FETCH c_emp INTO vr_emp;
i:=I+1;
END LOOP;
CLOSE c_emp;
END emp_2minsal;
/

6. Escriure un programa que mostri, en un format adequat, la següent informació:
- Per cada empleat: cognom i salari.
- Per cada departament: Número d'empleats i suma dels salaris del departament.
- Al final del llistat: Número total d'empleats i suma de tots els salaris

CREATE OR REPLACE PROCEDURE llistar_emple
AS
CURSOR c1 IS
SELECT cognom, salari, dept_no FROM emple
ORDER BY dept_no, cognom;
vr_emp c1%ROWTYPE;
dep_ant EMPLE.DEPT_NO%TYPE;
cont_emple NUMBER(4) DEFAULT 0;
sum_sal NUMBER(9) DEFAULT 0;
tot_emple NUMBER(4) DEFAULT 0;
tot_sal NUMBER(10) DEFAULT 0;
BEGIN
OPEN c1;
FETCH c1 INTO vr_emp;
IF c1%FOUND THEN
dep_ant := vr_emp.dept_no;
END IF;
WHILE c1%FOUND LOOP

/* Comprovació nou departament i resum */
IF dep_ant <> vr_emp.dept_no THEN
DBMS_OUTPUT.PUT_LINE('*** DEPT: ' || dep_ant ||
' NUM. EMPLEATS: '||cont_emple ||
' SUM. SALARIS: '||sum_sal);
dep_ant := vr_emp.dept_no;
tot_emple := tot_emple + cont_emple;
tot_sal:= tot_sal + sum_sal;
cont_emple:=0;
sum_sal:=0;
END IF;

/* Línies de detall */
DBMS_OUTPUT.PUT_LINE(RPAD(vr_emp.cognom,10)|| ' * '
||LPAD(TO_CHAR(vr_emp.salari,'9,999,999'),12));

/* Incrementar i acumular */
cont_emple := cont_emple + 1;
sum_sal:=sum_sal + vr_emp.salari;

FETCH c1 INTO vr_emp;
END LOOP;
CLOSE c1;

IF cont_emple > 0 THEN

/* Escriure dades de l'últim departament */
DBMS_OUTPUT.PUT_LINE('*** DEPT: ' || dep_ant ||
' NUM EMPLEATS: '|| cont_emple ||
' SUM. SALARIS: '||sum_sal);
dep_ant := vr_emp.dept_no;
tot_emple := tot_emple + cont_emple;
tot_sal:= tot_sal + sum_sal;
cont_emple:=0;
sum_sal:=0;

/* Escriure totals informe */
DBMS_OUTPUT.PUT_LINE(' ****** NUMERO TOTAL EMPLEATS: '
||tot_emple ||
' TOTAL SALARIS: '|| tot_sal);
END IF;
END llistar_emple;
/

/* Nota: aquest procediment pot escriure's de forma que la visualització dels resultats resulti més clara incloent línies de separació, capçaleres de columnes, etc. */
</pre>

===Altres exercicis per estudiar===
Les taules que necessites
<pre>
rem
rem $Header: exampbld.sql,v 1.1 1992/12/02 16:27:31 GCLOSSMA Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem exampbld.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem gclossma 12/02/92 - Creation
set compatibility V7
/
drop table accounts
/
create table accounts(
account_id number(4) not null,
bal number(11,2))
/
create unique index accounts_index on accounts (account_id)
/
drop table action
/
create table action(
account_id number(4) not null,
oper_type char(1) not null,
new_value number(11,2),
status char(45),
time_tag date not null)
/
drop table bins
/
create table bins(
bin_num number(2) not null,
part_num number(4),
amt_in_bin number(4))
/
drop table data_table
/
create table data_table(
exper_num number(2),
n1 number(5),
n2 number(5),
n3 number(5))
/
drop table emp
/
create table emp(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
/
drop table inventory
/
create table inventory(
prod_id number(5) not null,
product char(15),
quantity number(5))
/
drop table journal
/
create table journal(
account_id number(4) not null,
action char(45) not null,
amount number(11,2),
date_tag date not null)
/
drop table num1_tab
/
create table num1_tab(
sequence number(3) not null,
num number(4))
/
drop table num2_tab
/
create table num2_tab(
sequence number(3) not null,
num number(4))
/
drop table purchase_record
/
create table purchase_record(
mesg char(45),
purch_date date)
/
drop table ratio
/
create table ratio(
sample_id number(3) not null,
ratio number)
/
drop table result_table
/
create table result_table(
sample_id number(3) not null,
x number,
y number)
/
drop table sum_tab
/
create table sum_tab(
sequence number(3) not null,
sum number(5))
/
drop table temp
/
create table temp(
num_col1 number(9,4),
num_col2 number(9,4),
char_col char(55))
/
create or replace package personnel as
type charArrayTyp is table of varchar2(10)
index by binary_integer;
type numArrayTyp is table of float
index by binary_integer;
procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job_title out charArrayTyp,
salary out numArrayTyp);
end personnel;
/
create or replace package body personnel as
cursor get_emp (dept_number integer) is
select ename, job, sal from emp
where deptno = dept_number;
procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job_title out charArrayTyp,
salary out numArrayTyp) is
begin
if not get_emp%isopen then
open get_emp(dept_number);
end if;
done_fetch := 0;
found := 0;
for i in 1..batch_size loop
fetch get_emp into emp_name(i),
job_title(i), salary(i);
if get_emp%notfound then
close get_emp;
done_fetch := 1;
exit;
else
found := found + 1;
end if;
end loop;
end get_employees;
end personnel;
/

rem
rem $Header: examplod.sql,v 1.1 1992/12/02 16:27:53 GCLOSSMA Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examplod.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem gclossma 12/02/92 - Creation
delete from accounts
/
insert into accounts values (1,1000.00)
/
insert into accounts values (2,2000.00)
/
insert into accounts values (3,1500.00)
/
insert into accounts values (4,6500.00)
/
insert into accounts values (5,500.00)
/
delete from action
/
insert into action values
(3,'u',599,null,sysdate)
/
insert into action values
(6,'i',20099,null, sysdate)
/
insert into action values
(5,'d',null,null, sysdate)
/
insert into action values
(7,'u',1599,null, sysdate)
/
insert into action values
(1,'i',399,null,sysdate)
/
insert into action values
(9,'d',null,null,sysdate)
/
insert into action values
(10,'x',null,null,sysdate)
/
delete from bins
/
insert into bins values (1, 5469, 650)
/
insert into bins values (2, 7243, 450)
/
insert into bins values (3, 5469, 120)
/
insert into bins values (4, 5469, 300)
/
insert into bins values (5, 6085, 415)
/
insert into bins values (6, 5469, 280)
/
insert into bins values (7, 8159, 619)
/
delete from data_table
/
insert into data_table values
(1, 10, 167, 17)
/
insert into data_table values
(1, 16, 223, 35)
/
insert into data_table values
(2, 34, 547, 2)
/
insert into data_table values
(3, 23, 318, 11)
/
insert into data_table values
(1, 17, 266, 15)
/
insert into data_table values
(1, 20, 117, 9)
/
delete from emp
/
insert into emp values
(7369,'SMITH','CLERK',7902,TO_DATE('12-17-80','MM-DD-YY'),
800,NULL,20)
/
insert into emp values
(7499,'ALLEN','SALESMAN',7698,TO_DATE('02-20-81','MM-DD-YY'),
1600,300,30)
/
insert into emp values
(7521,'WARD','SALESMAN',7698,TO_DATE('02-22-81','MM-DD-YY'),
1250,500,30)
/
insert into emp values
(7566,'JONES','MANAGER',7839,TO_DATE('04-02-81','MM-DD-YY'),
2975,NULL,20)
/
insert into emp values
(7654,'MARTIN','SALESMAN',7698,TO_DATE('09-28-81','MM-DD-YY'),
1250,1400,30)
/
insert into emp values
(7698,'BLAKE','MANAGER',7839,TO_DATE('05-1-81','MM-DD-YY'),
2850,NULL,30)
/
insert into emp values
(7782,'CLARK','MANAGER',7839,TO_DATE('06-9-81','MM-DD-YY'),
2450,NULL,10)
/
insert into emp values
(7788,'SCOTT','ANALYST',7566,SYSDATE-85,3000,NULL,20)
/
insert into emp values
(7839,'KING','PRESIDENT',NULL,TO_DATE('11-17-81','MM-DD-YY'),
5000,NULL,10)
/
insert into emp values
(7844,'TURNER','SALESMAN',7698,TO_DATE('09-8-81','MM-DD-YY'),
1500,0,30)
/
insert into emp values
(7876,'ADAMS','CLERK',7788,SYSDATE-51,1100,NULL,20)
/
insert into emp values
(7900,'JAMES','CLERK',7698,TO_DATE('12-3-81','MM-DD-YY'),
950,NULL,30)
/
insert into emp values
(7902,'FORD','ANALYST',7566,TO_DATE('12-3-81','MM-DD-YY'),
3000,NULL,20)
/
insert into emp values
(7934,'MILLER','CLERK',7782,TO_DATE('01-23-82','MM-DD-YY'),
1300,NULL,10)
/
delete from inventory
/
insert into inventory values
(1234, 'TENNIS RACKET', 3)
/
insert into inventory values
(8159, 'GOLF CLUB', 4)
/
insert into inventory values
(2741, 'SOCCER BALL', 2)
/
delete from journal
/
delete from num1_tab
/
insert into num1_tab values (1, 5)
/
insert into num1_tab values (2, 7)
/
insert into num1_tab values (3, 4)
/
insert into num1_tab values (4, 9)
/
delete from num2_tab
/
insert into num2_tab values (1, 15)
/
insert into num2_tab values (2, 19)
/
insert into num2_tab values (3, 27)
/
delete from purchase_record
/
delete from ratio
/
delete from result_table
/
insert into result_table values (130, 70, 87)
/
insert into result_table values (131, 77, 194)
/
insert into result_table values (132, 73, 0)
/
insert into result_table values (133, 81, 98)
/
delete from sum_tab
/
delete from temp
/
commit
/
</pre>
===Solució===
I els procediments:
<pre>
em
rem $Header: examp4.sql,v 1.1 1992/05/12 22:03:08 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp4.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block finds all employees whose monthly wages (salary plus
** commission) are higher than $2000.
**
** An alias is used in the cursor declaration so that the subsequent
** use of %ROWTYPE is allowed. (Column names in a cursor declaration
** must have aliases if they are not simple names.)
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename
FROM emp;
my_rec my_cursor%ROWTYPE;
BEGIN
OPEN my_cursor;
LOOP
FETCH my_cursor INTO my_rec;
EXIT WHEN my_cursor%NOTFOUND;
IF my_rec.wages > 2000 THEN
INSERT INTO temp VALUES (NULL, my_rec.wages,
my_rec.ename);
END IF;
END LOOP;
CLOSE my_cursor;
END;
/

rem
rem $Header: examp5.sql,v 1.1 1992/05/12 22:03:18 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp5.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block does some numeric processing on data that
** comes from experiment #1. The results are stored in
** the TEMP table.
**
** Copyright (c) 1989,1992 Oracle Corporation
*/

DECLARE
num1 data_table.n1%TYPE; -- Declare variables
num2 data_table.n2%TYPE; -- to be of same type as
num3 data_table.n3%TYPE; -- database columns
result temp.num_col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table
WHERE exper_num = 1;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO num1, num2, num3;
EXIT WHEN c1%NOTFOUND;
-- the c1%NOTFOUND condition evaluates
-- to TRUE when FETCH finds no more rows
/* calculate and store the results */
result := num2/(num1 + num3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
CLOSE c1;
COMMIT;
END;
/

rem
rem $Header: examp6.sql,v 1.1 1992/05/12 22:03:28 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp6.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block accumulates 1000 units of part number 5469 from
** various storage bins.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/
DECLARE
CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin
FROM bins
WHERE part_num = part_number AND
amt_in_bin > 0
ORDER BY bin_num
FOR UPDATE OF amt_in_bin;
bin_amt bins.amt_in_bin%TYPE;
total_so_far NUMBER(5) := 0;
amount_needed CONSTANT NUMBER(5) := 1000;
bins_looked_at NUMBER(3) := 0;
BEGIN
OPEN bin_cur(5469);
WHILE total_so_far < amount_needed LOOP
FETCH bin_cur INTO bin_amt;
EXIT WHEN bin_cur%NOTFOUND;
/* If we exit, there's not enough to *
* satisfy the order. */
bins_looked_at := bins_looked_at + 1;
IF total_so_far + bin_amt < amount_needed THEN
UPDATE bins SET amt_in_bin = 0
WHERE CURRENT OF bin_cur;
-- take everything in the bin
total_so_far := total_so_far + bin_amt;
ELSE -- we finally have enough
UPDATE bins SET amt_in_bin = amt_in_bin
- (amount_needed - total_so_far)
WHERE CURRENT OF bin_cur;
total_so_far := amount_needed;
END IF;
END LOOP;
CLOSE bin_cur;
INSERT INTO temp VALUES (NULL, bins_looked_at, '<- bins looked at');
COMMIT;
END;
/

rem
rem $Header: examp7.sql,v 1.1 1992/05/12 22:03:40 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp7.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block does some numeric processing on data that comes
** from experiment #1. The results are stored in the TEMP table.
**
** Copyright (c) 1989,1992 Oracle Corporation
*/

DECLARE
result temp.num_col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table
WHERE exper_num = 1;
BEGIN
FOR c1rec IN c1 LOOP
/* calculate and store the results */
result := c1rec.n2 / (c1rec.n1 + c1rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END;
/

rem
rem $Header: examp8.sql,v 1.1 1992/05/12 22:03:53 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp8.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block calculates the total wages (salary plus commission)
** paid to employees in department 20. It also determines how
** many of the employees have salaries higher than $2000, and how
** many have commissions larger than their salaries.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows *
* returned by emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
'Total Wages: ' || TO_CHAR(total_wages));
COMMIT;
END;
/

rem
rem $Header: examp12.sql,v 1.1 1992/05/12 22:02:25 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp12.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block returns a number from each of two tables, then
** inserts the sum of the numbers into a third table. It stops
** when all rows have been fetched from either of the two tables.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
CURSOR num1_cur IS SELECT num FROM num1_tab
ORDER BY sequence;
CURSOR num2_cur IS SELECT num FROM num2_tab
ORDER BY sequence;
num1 num1_tab.num%TYPE;
num2 num2_tab.num%TYPE;
pair_num NUMBER := 0;
BEGIN
OPEN num1_cur;
OPEN num2_cur;
LOOP -- loop through the two tables and get
-- pairs of numbers
FETCH num1_cur INTO num1;
FETCH num2_cur INTO num2;
IF (num1_cur%FOUND) AND (num2_cur%FOUND) THEN
pair_num := pair_num + 1;
INSERT INTO sum_tab VALUES (pair_num, num1 + num2);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE num1_cur;
CLOSE num2_cur;
END;
/

rem
rem $Header: examp13.sql,v 1.1 1992/05/12 22:02:32 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp13.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block returns a number from each of two tables, then
** inserts the sum of the numbers into a third table. It stops
** when all rows have been fetched from either of the two tables.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
CURSOR num1_cur IS SELECT num FROM num1_tab
ORDER BY sequence;
CURSOR num2_cur IS SELECT num FROM num2_tab
ORDER BY sequence;
num1 num1_tab.num%TYPE;
num2 num2_tab.num%TYPE;
pair_num NUMBER := 0;
BEGIN
OPEN num1_cur;
OPEN num2_cur;
LOOP -- loop through the two tables and get
-- pairs of numbers
FETCH num1_cur INTO num1;
FETCH num2_cur INTO num2;
EXIT WHEN (num1_cur%NOTFOUND) OR (num2_cur%NOTFOUND);
pair_num := pair_num + 1;
INSERT INTO sum_tab VALUES (pair_num, num1 + num2);
END LOOP;
CLOSE num1_cur;
CLOSE num2_cur;
END;
/

rem
rem $Header: examp14.sql,v 1.1 1992/05/12 22:02:45 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp14.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block uses a cursor to select the 5 highest-paid employees
** from the EMP table.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest-paid employee
my_ename CHAR(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);

BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND);
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;
/

rem
rem $Header: sample2.sql,v 1.1 1992/05/12 22:04:17 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem sample2.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This program uses a cursor to select the 5 highest-paid employees
** from the EMP table.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest-paid employee
my_ename CHAR(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);

BEGIN
OPEN c1;

FOR i IN 1..5 LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND; /* in case the number requested is more *
* than the total number of employees */
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;

CLOSE c1;
END;
/

rem
rem $Header: sample4.sql,v 1.1 1992/05/12 22:04:38 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem sample4.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This program modifies the ACCOUNTS table based on instructions
** stored in the ACTION table. Each row of the ACTION table
** contains an account number to act upon, an action to be taken
** (insert, update, or delete), an amount by which to update the
** account, and a time tag.
**
** On an insert, if the account already exists, an update is
** performed instead. On an update, if the account does not exist,
** it is created by an insert. On a delete, if the row does not
** exist, no action is taken.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
CURSOR c1 IS
SELECT account_id, oper_type, new_value FROM action
ORDER BY time_tag
FOR UPDATE OF status;

BEGIN
FOR acct IN c1 LOOP -- process each row one at a time

acct.oper_type := upper(acct.oper_type);

/*----------------------------------------*
* Process an UPDATE. If the account to *
* be updated doesn't exist, create a new *
* account. *
*----------------------------------------*/
IF acct.oper_type = 'U' THEN
UPDATE accounts SET bal = acct.new_value
WHERE account_id = acct.account_id;

IF SQL%NOTFOUND THEN -- account didn't exist. Create it.
INSERT INTO accounts
VALUES (acct.account_id, acct.new_value);
UPDATE action SET status =
'Update: ID not found. Value inserted.'
WHERE CURRENT OF c1;
ELSE
UPDATE action SET status = 'Update: Success.'
WHERE CURRENT OF c1;
END IF;

/*--------------------------------------------*
* Process an INSERT. If the account already *
* exists, do an update of the account *
* instead. *
*--------------------------------------------*/
ELSIF acct.oper_type = 'I' THEN
BEGIN
INSERT INTO accounts
VALUES (acct.account_id, acct.new_value);
UPDATE action set status = 'Insert: Success.'
WHERE CURRENT OF c1;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN -- account already exists
UPDATE accounts SET bal = acct.new_value
WHERE account_id = acct.account_id;
UPDATE action SET status =
'Insert: Acct exists. Updated instead.'
WHERE CURRENT OF c1;
END;

/*--------------------------------------------*
* Process a DELETE. If the account doesn't *
* exist, set the status field to say that *
* the account wasn't found. *
*--------------------------------------------*/
ELSIF acct.oper_type = 'D' THEN
DELETE FROM accounts
WHERE account_id = acct.account_id;

IF SQL%NOTFOUND THEN -- account didn't exist.
UPDATE action SET status = 'Delete: ID not found.'
WHERE CURRENT OF c1;
ELSE
UPDATE action SET status = 'Delete: Success.'
WHERE CURRENT OF c1;
END IF;

/*--------------------------------------------*
* The requested operation is invalid. *
*--------------------------------------------*/
ELSE -- oper_type is invalid
UPDATE action SET status =
'Invalid operation. No action taken.'
WHERE CURRENT OF c1;

END IF;

END LOOP;
COMMIT;
END;
/
</pre>

==Durarda==
1,5 hores

{{Autor}}, octubre 2009
[[Categoria: IES Jaume Balmes]]
[[Categoria: Pràctiques DAI-C8-EC]]

DAI-C8-EC: Exercicis proposats - Historial de revisió

Joan: New page: ==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. A...