DAI-C8-EC: Cursors. Exercicis proposats

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

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

Recursos

Solució dels exercicis

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. */

Altres exercicis per estudiar

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

Solució

I els procediments:

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

Durarda

1,5 hores



creat per Joan Quintana Compte, octubre 2009

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines