Joan: /* Exercicis proposats */

2011-10-26T14:31:02Z

Exercicis proposats

Pàgina nova

==Objectius==
Unitat Didàctica: UD 1. Introducció i Fonaments del llenguatge Pl/SQL

Exercicis complementaris

==Desenvolupament==
===Exercicis proposats===
1. Indicar los errores que aparecen en las siguientes instrucciones y la forma de corregirlos.
<pre>
DECLARE
Num1 NUMBER(8,2) := 0
Num2 NUMBER(8,2) NOT NULL DEFAULT 0;
Num3 NUMBER(8,2) NOT NULL;
quantitat INTEGER(3);
preu, descompte NUMBER(6);
Num4 Num1%ROWTYPE;
Dte CONSTANT INTEGER;
BEGIN
...
END;
</pre>
Solució:
<pre>
Num3 NUMBER(8,2) NOT NULL DEFAULT 0;
quantitat INTEGER;
preu NUMBER(6);
descompte NUMBER(6);
Num4 Num1%TYPE;
Dte CONSTANT INTEGER := 0;
</pre>
2. Escribir un procedimiento que reciba dos números y visualice su suma.
<pre>
CREATE OR REPLACE PROCEDURE sumar_numeros (
num1 NUMBER,
num2 NUMBER)
IS
suma NUMBER(6);
BEGIN
suma := num1 + num2;
DBMS_OUTPUT.PUT_LINE('Suma: '|| suma);
END sumar_numeros;
/
</pre>
3. Codificar un procedimiento que reciba una cadena y la visualice al revés.
<pre>
CREATE OR REPLACE PROCEDURE cadena_reves(
vcadena VARCHAR2)
AS
vcad_reves VARCHAR2(80);
BEGIN
FOR i IN REVERSE 1..LENGTH(vcadena) LOOP
vcad_reves := vcad_reves || SUBSTR(vcadena,i,1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(vcad_reves);
END cadena_reves;
/
</pre>
4. Escribir una función que reciba una fecha y devuelva el año, en número, correspondiente a esa fecha.
<pre>
CREATE OR REPLACE FUNCTION anio (data DATE)
RETURN NUMBER
AS
v_any NUMBER(4);
BEGIN
v_any := TO_NUMBER(TO_CHAR(data, 'YYYY'));
RETURN v_any;
END anio;
/
</pre>
5. Escribir un bloque PL/SQL que haga uso de la función anterior.
<pre>
DECLARE
n NUMBER(4);
BEGIN
n := anio(SYSDATE);
DBMS_OUTPUT.PUT_LINE('ANY : '|| n);
END;
/
</pre>
6. Dado el siguiente procedimiento:
<pre>
CREATE OR REPLACE PROCEDURE crear_depart (
v_num_dept depart.dept_no%TYPE,
v_dnom depart.dnom%TYPE DEFAULT 'PROVISIONAL',
v_loc depart.loc%TYPE DEFAULT 'PROVISIONAL')
IS
BEGIN
INSERT INTO depart
VALUES (v_num_dept, v_dnom, v_loc);
END crear_depart;
</pre>
<pre>
Indicar cuáles de las siguientes llamadas son correctas y cuáles incorrectas, en este último caso escribir la llamada correcta usando la notación posicional (en los casos que se pueda):
crear_depart; -- 1º
crear_depart(50); -- 2º
crear_depart('COMPRAS'); -- 3º
crear_depart(50,'COMPRAS'); -- 4º
crear_depart('COMPRAS', 50); -- 5º
crear_depart('COMPRAS', 'VALENCIA'); -- 6º
crear_depart(50, 'COMPRAS', 'VALENCIA'); -- 7º
crear_depart('COMPRAS', 50, 'VALENCIA'); -- 8º
crear_depart('VALENCIA', 'COMPRAS'); -- 9º
crear_depart('VALENCIA', 50); -- 10º
</pre>
Solució:
<pre>
1º Incorrecta: hay que pasar al menos el número de departamento.
2º Correcta.
3º Incorrecta: hay que pasar también el número de departamento.
4º Correcta.
5º Incorrecta: los argumentos están en orden inverso.
Solución: crear_depart(50, 'COMPRAS');
6º Incorrecta: hay que pasar también el número.
7º Correcta.
8º Incorrecta: el orden de los argumentos es incorrecto.
Solución: crear_depart(50, 'COMPRAS', 'VALENCIA');
9º Incorrecta: hay que pasar también el número de departamento.
10º Incorrecta: los argumentos están en orden inverso.
Solución: crear_depart(50, NULL, 'VALENCIA');
</pre>
7. Desarrollar una función que devuelva el número de años completos que hay entre dos fechas que se pasan como argumentos.
<pre>
CREATE OR REPLACE FUNCTION anys_dif (
data1 DATE, data2 DATE)
RETURN NUMBER
AS
v_anys_dif NUMBER(6);
BEGIN
v_anys_dif := ABS(TRUNC(MONTHS_BETWEEN(data2,data1)/ 12));
RETURN v_anys_dif;
END anys_dif;
/
</pre>
8. Escribir una función que, haciendo uso de la función anterior devuelva los trienios que hay entre dos fechas. (Un trienio son tres años completos).
<pre>
CREATE OR REPLACE FUNCTION trienis (
data1 DATE, data2 DATE)
RETURN NUMBER
AS
v_trienis NUMBER(6);
BEGIN
v_trienis := TRUNC(anys_dif(data1,data2) / 3);
RETURN v_trienis;
END;
/
</pre>
9. Codificar un procedimiento que reciba una lista de hasta 5 números y visualice su suma.
<pre>
CREATE OR REPLACE PROCEDURE sumar_5numeros (
Num1 NUMBER DEFAULT 0,
Num2 NUMBER DEFAULT 0,
Num3 NUMBER DEFAULT 0,
Num4 NUMBER DEFAULT 0,
Num5 NUMBER DEFAULT 0)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(Num1 + Num2 + Num3 + Num4 + Num5);
END sumar_5numeros;
/
</pre>
10. Escribir una función que devuelva solamente caracteres alfabéticos sustituyendo cualquier otro carácter por blancos a partir de una cadena que se pasará en la llamada.
<pre>
CREATE OR REPLACE FUNCTION subst_per_blancs (cad VARCHAR2) RETURN VARCHAR2
AS
nova_cad VARCHAR2(30);
car CHARACTER;
BEGIN
FOR i IN 1..LENGTH(cad) LOOP
car:=SUBSTR(cad,i,1);
IF (ASCII(car) NOT BETWEEN 65 AND 90) AND (ASCII(car) NOT BETWEEN 97 AND 122) THEN
car :=' ';
END IF;
nova_cad := nova_cad || car;
END LOOP;
RETURN nova_cad;
END subst_per_blancs;
/
</pre>
11. Implementar un procedimiento que reciba un importe y visualice el desglose del cambio en unidades monetarias de 1, 5, 10, 25, 50, 100, 200, 500, 1000, 2000, 5000 Ptas. en orden inverso al que aparecen aquí enumeradas.
<pre>
CREATE OR REPLACE PROCEDURE desglos_canvi(
import NUMBER)
AS
canvi NATURAL := import;
moneda NATURAL;
v_uni_moneda NATURAL;
BEGIN
DBMS_OUTPUT.PUT_LINE('***** DESGLOS DE: ' || import );
WHILE canvi > 0 LOOP
IF canvi >= 5000 THEN
moneda := 5000;
ELSIF canvi >= 2000 THEN
moneda := 2000;
ELSIF canvi >= 1000 THEN
moneda := 1000;
ELSIF canvi >= 500 THEN
moneda := 500;
ELSIF canvi >= 200 THEN
moneda := 200;
ELSIF canvi >= 100 THEN
moneda := 100;
ELSIF canvi >= 50 THEN
moneda := 50;
ELSIF canvi >= 25 THEN
moneda := 25;
ELSIF canvi >= 10 THEN
moneda := 10;
ELSIF canvi >= 5 THEN
moneda := 5;
ELSE
moneda := 1;
END IF;
v_uni_moneda := TRUNC(canvi / moneda);
DBMS_OUTPUT.PUT_LINE(v_uni_moneda || ' Unitats de: ' || moneda || ' euros. ');
canvi := MOD(canvi, moneda);
END LOOP;
END desglos_canvi;
/
</pre>
12. Codificar un procedimiento que permita borrar un empleado cuyo número se pasará en la llamada.
<pre>
CREATE OR REPLACE PROCEDURE borrar_emple(
num_emple emple.emp_no%TYPE)
AS
BEGIN
DELETE FROM emple WHERE emp_no = num_emple;
END borrar_emple;
/

Nota: El procedimiento anterior devolverá el mensaje
<< Procedimiento PL/SQL terminado con éxito >> aunque no exista el número y, por tanto, no se borre el empleado. Para evitarlo se puede escribir:

CREATE OR REPLACE PROCEDURE borrar_emple(
num_emple emple.emp_no%TYPE)
AS
v_row ROWID;
BEGIN
SELECT ROWID INTO v_row FROM emple
WHERE emp_no = num_emple;
DELETE FROM emple WHERE ROWID = v_row;
END borrar_emple;
/
</pre>
13. Escribir un procedimiento que modifique la localidad de un departamento. El procedimiento recibirá como parámetros el número del departamento y la localidad nueva.
<pre>
CREATE OR REPLACE
PROCEDURE modificar_localitat(
num_depart NUMBER,
localitat VARCHAR2)
AS
BEGIN
UPDATE depart SET loc = localitat
WHERE dept_no = num_depart;
END modificar_localitat;
/

Nota: Lo indicado en la nota del ejercicio anterior se puede aplicar también a este.

</pre>
14. Visualizar todos los procedimientos y funciones del usuario almacenados en la base de datos y su situación (valid o invalid).
<pre>
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION');

Nota: También se puede utilizar la vista ALL_OBJECTS.
</pre>

===Exercicis complementaris===
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>
I els exercicis (i solucions) són:
<pre>
rem
rem $Header: examp1.sql,v 1.1 1992/05/12 22:02:06 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp1.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block processes an order for tennis rackets. It decrements
** the quantity of rackets on hand only if there is at least one
** racket left in stock.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = 'TENNIS RACKET'
FOR UPDATE OF quantity;

IF qty_on_hand > 0 THEN -- check quantity
UPDATE inventory SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record
VALUES ('Tennis racket purchased', SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES ('Out of tennis rackets', SYSDATE);
END IF;

COMMIT;
END;
/

rem
rem $Header: examp2.sql,v 1.2 1992/12/02 16:16:15 GCLOSSMA Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp2.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem gclossma 12/02/92 - cuz
Rem rvasired 05/12/92 - Creation
/*
** This block debits account 3 by $500 only if there are sufficient
** funds to cover the withdrawal.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = acct
FOR UPDATE OF bal;

IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt
WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES
(acct, acct_balance, 'Insufficient funds');
-- insert account, current balance, and message
END IF;

COMMIT;
END;
/

rem
rem $Header: examp3.sql,v 1.1 1992/05/12 22:03:01 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp3.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This block finds the first employee who has a salary over $4000
** and is higher in the chain of command than employee 7902.
**
** Copyright (c) 1989,1992 Oracle Corporation
*/

DECLARE
salary emp.sal%TYPE;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno CONSTANT NUMBER(4) := 7902;
BEGIN
SELECT sal, mgr INTO salary, mgr_num FROM emp
WHERE empno = starting_empno;
WHILE salary < 4000 LOOP
SELECT sal, mgr, ename INTO salary, mgr_num, last_name
FROM emp
WHERE empno = mgr_num;
END LOOP;

INSERT INTO temp VALUES (NULL, salary, last_name);
COMMIT;
END;
/

rem
rem $Header: examp11.sql,v 1.1 1992/05/12 22:02:16 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem examp11.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 ratio between the X and Y columns of
** the RATIO table. If the ratio is greater than 0.72, the block
** inserts the ratio into RESULT_TABLE. Otherwise, it inserts -1.
** If the denominator is zero, ZERO_DIVIDE is raised, and a
** zero is inserted into RESULT_TABLE.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER := 0.72;
samp_num CONSTANT NUMBER := 132;
BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;
the_ratio := numerator/denominator;
IF the_ratio > lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);
ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;
/

rem
rem $Header: sample1.sql,v 1.1 1992/05/12 22:04:07 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem sample1.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 simple FOR loop to insert 10 rows into a table.
** The values of a loop index, counter variable, and either of two
** character strings are inserted. Which string is inserted
** depends on the value of the loop index.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;

x := x + 100;
END LOOP;
COMMIT;
END;
/

rem
rem $Header: sample3.sql,v 1.1 1992/05/12 22:04:28 RVASIRED Exp $
rem
Rem Copyright (c) 1991 by Oracle Corporation
Rem NAME
Rem sample3.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem rvasired 05/12/92 - Creation
/*
** This example illustrates block structure and scope rules. An
** outer block declares two variables named X and COUNTER, and loops four
** times. Inside the loop is a sub-block that also declares a variable
** named X. The values inserted into the TEMP table show that the two
** X's are indeed different.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
x NUMBER := 0;
counter NUMBER := 0;
BEGIN
FOR i IN 1..4 LOOP
x := x + 1000;
counter := counter + 1;
INSERT INTO temp VALUES (x, counter, 'in OUTER loop');

/* start an inner block */
DECLARE
x NUMBER := 0; -- this is a local version of x
BEGIN
FOR i IN 1..4 LOOP
x := x + 1; -- this increments the local x
counter := counter + 1;
INSERT INTO temp VALUES (x, counter, 'inner loop');
END LOOP;
END;

END LOOP;
COMMIT;
END;
/
</pre>
==Entrega==
* Són exercicis voluntaris, no s'ha d'entregar

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

==Durarda==
2,5 hores

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

DAI-C8-EC: Introducció al Pl/SQL. Exercicis proposats - Historial de revisió

Joan: /* Exercicis proposats */