Der er ikke sikkert at det er så nemt som Hr. Lastein prøver at gøre det.
Det kommer jo an på hvor vel struktureret ens data er. Faktisk ligner dine
spøgsmål rimeligt meget nogle af spørgsmålne fra en opgave jeg engang fik på
DIKU - de blev stillet af en RUC mand... anyway her er en hurtig
cut-and-paste fra vores løsning (husk at krediterer hvis du anvender den) -
held og lykke:
P7 PL/SQL Programmer
Problem 7 er løst med 2 seperate programmer; "Bedstebud" og "Relaterede
bøger (Top 10)".
P7.A "Bedste bud"
Variabelt antal søgeord i Bedstebud
Da tekst indlæst med &-konstruktionen i PL/SQL, interpoleres ind i
kildeteksten, kan man ikke indlæse et variabelt antal ord, ved at loope over
den samme indlæsning igen og igen. I stedet kan man lade brugeren indtaste
samtlige ord i samme streng, og så pille ordene ud af denne streng med de
indbyggede strengoperationer i PL/SQL.
Programmet Bedstebud.sql implemetere denne løsning. Eksempel på brug af
programmet følger:
Enter value for kriterier: naturhelbredelse helbredelse behandling
old 21: kriterie := '&kriterier';
new 21: kriterie := 'naturhelbredelse helbredelse behandling';
hits isbn
3 87-16-10632-6
3 87-595-0562-1
2 0-7050-0096-6
2 87-7739-141-1
2 87-7739-280-9
2 87-7466-223-6
2 87-418-5987-1
2 87-7783-152-7
2 87-89019-77-6
2 87-88864-06-5
PL/SQL procedure successfully completed.
Bedstebud metode
Programmet Bedstebud undersøger for hvert ord i relationen Emneord, om det
findes i relationen Kriterier som holder brugerens søgeord. Hvis det gør, så
noteres den pågældende bog for et hit i relationen Match. Til sidst ordnes
Match nedafgående efter antal hits, for at få de bøger først, som har flest
hits. Så udskrives de 10 første tupler.
Da metoden benytter en tabel Match til temporære resultater, kan programmet
kun udføres af en bruger af gangen.
PL/SQL-koden for "Bedstebud"
SET SERVEROUTPUT ON;
-- Brugerens søgeord samles i denne tabel:
DROP TABLE kriterier;
CREATE TABLE kriterier (sord VARCHAR2(100));
-- Antallet af søgeord som matcher en bog noteres i denne tabel:
DROP TABLE match;
CREATE TABLE match (isbn VARCHAR2(20), antal INT);
DECLARE
tempstr varchar2(20);
indeks INT := 1;
pos INT;
len INT;
kriterie varchar2(200);
cursor joinPtr is
select HandlerOm.isbn, EmneOrd.ord
from HandlerOm, EmneOrd
where HandlerOm.oid = EmneOrd.oid;
tempisbn VARCHAR2(20);
tempmatch INT;
tempord VARCHAR2(100);
tal INT;
cursor matchPtr is
select isbn, antal
from match
order by antal desc;
taeller INT := 0;
BEGIN
-- Brugeren indtaster søgeord sepereret med mellerum i en og samme
-- streng. Strengen parses med de indbyggende strengoperationer
-- og ordene lægges ned i tabellen Kriterier.
kriterie := '&kriterier';
len := length(kriterie);
LOOP
pos := instr(kriterie,' ',indeks);
if (pos=0) then
if (indeks>=len) then
exit;
else
tempstr := substr(kriterie,indeks,len+1-indeks);
insert into kriterier values (tempstr);
commit;
exit;
end if;
else
tempstr := substr(kriterie,indeks,pos-indeks);
insert into kriterier values (tempstr);
commit;
indeks := indeks+(pos-indeks+1);
end if;
end loop;
-- Relationerne HandlerOm og EmneOrd joines. Der indlæses en tuppel
-- af gangen fra den resulterende relation. For hvert ord i relationen
-- noteres et hit for der pågældende isbn, hvis ordet findes i
-- relationen Kriterier. Hit noteres i relationen Match.
open joinPtr;
loop
fetch joinPtr into tempisbn,tempord;
exit when joinPtr%NOTFOUND;
select count(sord) into tal from kriterier where sord = tempord;
if (tal > 0) then
select count(isbn) into tal from match where isbn = tempisbn;
if (tal > 0) then
select antal into tempmatch from match where isbn = tempisbn;
tempmatch := tempmatch+1;
update match
set antal = tempmatch
where isbn = tempisbn;
else
insert into match values (tempisbn,1);
end if;
commit;
end if;
end loop;
close joinPtr;
-- Tuplerne i Match ordnes nedafgående efter antal hits og
-- de 10 første udskrives som resultatet.
dbms_output.put_line('hits isbn');
open matchPtr;
loop
fetch matchPtr into tempisbn,tempmatch;
exit when matchPtr%NOTFOUND or (taeller = 10);
taeller := taeller+1;
dbms_output.put_line(tempmatch || ' ' || tempisbn);
end loop;
close matchPtr;
end;
..
run;
P7.B Relaterede bøger (Top 10)
Med følgende SQL er det muligt at finde alle bøger der er tilknyttet en
delmængde af en bogs emneord, og sortere dem i rækkefølge.
ACCEPT titel CHAR PROMPT 'Indtast titel:';
SELECT tit, COUNT(DISTINCT oid) AS hitrate
FROM (
SELECT a.tit, b.oid
FROM Bogklasse a, (
SELECT a.isbn, b.oid
FROM HandlerOm a,
HandlerOm b, Bogklasse c
WHERE c.tit = '&titel'
AND b.isbn = c.isbn
AND a.oid = b.oid
) b
WHERE a.isbn = b.isbn
)
GROUP BY tit
ORDER BY COUNT(distinct oid) ASC;
Da rownum desværre angiver rækkenumre på den ikke-sorterede liste kan en
WHERE
rownum < 10 ikke benyttes til at begrænds resultatet til de ti bedste. En
løsning kunne være at hælde linsen over i en ny realtion og anvende rownum
på
den. Desvære lader det sig ikke gøre, idet vi har anvendt GROUP på
reationen.
I stedet for pakker vi vores SQL sætning ind i en PL/SQL-blok, hvor vi har
mulighed for at styre en courser. Resultatet bliver som følger:
SET SERVEROUTPUT ON;
ACCEPT titel CHAR PROMPT 'Indtast bog: ';
DECLARE
title VARCHAR(300);
rate INT;
counter INT := 0;
CURSOR hits IS
SELECT tit, COUNT(DISTINCT oid) AS hitrate
FROM (
SELECT a.tit, b.oid
FROM Bogklasse a, (
SELECT a.isbn, b.oid
FROM HandlerOm a,
HandlerOm b, Bogklasse c
WHERE c.tit = '&titel'
AND b.isbn = c.isbn
AND a.oid = b.oid
) b
WHERE a.isbn = b.isbn
)
GROUP BY tit
ORDER BY COUNT(distinct oid) DESC;
BEGIN
OPEN hits;
LOOP
EXIT WHEN hits%NOTFOUND;
EXIT WHEN counter > 10;
FETCH hits INTO title, rate;
dbms_output.put_line('Nr. ' || counter || ' Rate: ' || rate || '
Titel: '
|| title);
counter := counter + 1;
END LOOP;
CLOSE hits;
END;
..
run
|