Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table cotxes(
- matricula char(10) primary key,
- marca char(20) not null,
- model char(20) not null,
- categoria integer not null,
- color char(10),
- any_fab integer
- );
- create table treballadors(
- dni char(8) primary key,
- nom char(30) not null,
- sou_base real not null,
- plus real not null
- );
- create table lloguers_actius(
- matricula char(10) primary key references cotxes,
- dni char(8) not null references treballadors,
- num_dies integer not null,
- preu_total real not null
- );
- create table missatgesExcepcions(
- num integer,
- texte varchar(50)
- );
- insert into missatgesExcepcions values(1,'No hi ha cap tupla dins del interval demanat');
- insert into missatgesExcepcions values(2, 'Error intern');
- insert into cotxes values ('1111111111','Audi','A4',1,'Vermell',1998);
- insert into cotxes values ('2222222222','Audi','A3',2,'Blanc',1998);
- insert into cotxes values ('3333333333','Volskwagen','Golf',2,'Blau',1990);
- insert into cotxes values ('4444444444','Toyota','Corola',3,'groc',1999);
- insert into cotxes values ('5555555555','Honda','Civic',3,'Vermell',2000);
- insert into cotxes values ('6666666666','BMW','Mini',2,'Vermell',2000);
- insert into treballadors values ('22222222','Joan',1700,150);
- insert into lloguers_actius values ('1111111111','22222222',7,750);
- insert into lloguers_actius values ('2222222222','22222222',5,550);
- insert into lloguers_actius values ('3333333333','22222222',4,450);
- insert into lloguers_actius values ('4444444444','22222222',8,850);
- insert into lloguers_actius values ('5555555555','22222222',2,250);
- CREATE TYPE FILA_TREB AS (
- dni_treb VARCHAR(8),
- nom_treb VARCHAR(30),
- sou_treb real,
- plus_sou_treb real,
- matricula_treb VARCHAR(10));
- CREATE OR REPLACE FUNCTION llistat_treb(dni_i varchar(8), dni_s varchar(8)) RETURN SETOF
- RETURNS setof FILA_TREB AS $$
- DECLARE treb FILA_TREB;
- BEGIN
- FOR treb IN SELECT *
- FROM treballadors t
- WHERE t.dni >= dni_i
- AND t.dni<= dni_s
- ORDER BY t.dni
- LOOP
- IF((SELECT COUNT (*)
- FROM lloguers l
- WHERE t.DNI = l.dni) >= 5)
- THEN
- FOR treb.matricula IN SELECT L1.matricula
- from lloguers L1
- where t.dni = L1.dni
- order by L1.matricula
- LOOP
- RETURN NEXT treb
- END LOOP
- ELSE THEN
- RETURN NEXT tr;
- END LOOP;
- IF (quants < 1) THEN
- SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
- RAISE EXCEPTION '%',missatge;
- END IF;
- RETURN;
- EXCEPTION
- WHEN RAISE_EXCEPTION THEN
- RAISE EXCEPTION '%',SQLERRM;
- WHEN OTHERS THEN
- SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=2;
- RAISE EXCEPTION '%',missatge;
- END;
- $$LANGUAGE plpgsql;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement