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

De wikijoan
Salta a la navegació Salta a la cerca

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.

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;

Solució:

Num3 NUMBER(8,2) NOT NULL DEFAULT 0;
quantitat INTEGER;
preu NUMBER(6);
descompte NUMBER(6);
Num4 Num1%TYPE;
Dte CONSTANT INTEGER := 0;	

2. Escribir un procedimiento que reciba dos números y visualice su suma.

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;
/

3. Codificar un procedimiento que reciba una cadena y la visualice al revés.

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;
/

4. Escribir una función que reciba una fecha y devuelva el año, en número, correspondiente a esa fecha.

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;
/

5. Escribir un bloque PL/SQL que haga uso de la función anterior.

DECLARE
 	n NUMBER(4);
BEGIN
 	n := anio(SYSDATE);
 	DBMS_OUTPUT.PUT_LINE('ANY : '|| n);
END;
/

6. Dado el siguiente procedimiento:

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;
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º

Solució:

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');

7. Desarrollar una función que devuelva el número de años completos que hay entre dos fechas que se pasan como argumentos.

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;
/

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).

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;
/

9. Codificar un procedimiento que reciba una lista de hasta 5 números y visualice su suma.

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;
/

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.

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;
/

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.

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;
/

12. Codificar un procedimiento que permita borrar un empleado cuyo número se pasará en la llamada.

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;
/

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.

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.

14. Visualizar todos los procedimientos y funciones del usuario almacenados en la base de datos y su situación (valid o invalid).

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.

Exercicis complementaris

Taules que necessites:

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
/

I els exercicis (i solucions) són:

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;
/

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



creat per Joan Quintana Compte, octubre 2009