[SQL] prosta funkcja obsługująca bazę danych

[Ser Cubus]

witam,
próbuję napisać funkcję, która zwróci mi karę za przetrzymanie płyty. Kara wynosi 2 zł za dzień, a książkę można wypożyczyć na 10 dni. Tutaj jest mój kod.

Ukryta treść:    

Kod: Zaznacz cały

CREATE OR REPLACE FUNCTION licz_kare(varchar, varchar) returns int
LANGUAGE plpgsql
AS
'
DECLARE

kara int;
nr_klienta int;
data_wyp date;
data_zw date;
dni int;

BEGIN
select id_klienta from klient where imie = $1 and nazwisko = $2 into nr_klienta;
select data_wypozyczenia from wypozyczenie where kto_wypozyczyl = nr_klienta into data_wyp;
select data_zwrotu from wypozyczenie where kto_wypozyczyl into data_zw;

dni = to_days(data_zwrotu) - to_days(data_wypozyczenia);

if dni > 10
then kara = 2 * dni;
else kara = 0;
end if;

return kara;
END;
';

[dexter90]

select id_klienta from klient where imie = $1 and nazwisko = $2 into nr_klienta;
select data_wypozyczenia from wypozyczenie where kto_wypozyczyl = nr_klienta into data_wyp;
select data_zwrotu from wypozyczenie where kto_wypozyczyl into data_zw;

Moim zdaniem to troszeczkę WTF bo można to zrobić inaczej, kwestia tego abyś podał kawałek schematu relacyjnej bazy danych. Naliczone kary można wyświetlać w tabeli tymczasowej przy każdym wypożyczeniu. W Twoim zapytaniu:

1. Zaznaczasz id klienta,
2. potem zaznaczasz wszystkie daty wypożyczenia tego klienta,
3. Potem zaznaczasz zwroty tego klienta

Masz 3 zapytania, które może działają, ale jakby ktoś to zobaczył to by powiedział, że to rozwiązanie nie jest super ładne.

Różnice można obliczać od razy w zapytaniach.

Podaj schemat.

Pozdrawiam

PS Nawet nie napisałeś co jest nie tak.

PS2 No i chyba z PHP to nie ma narazie nic wspólnego, bo to funkcja w Postgres jak się domyślam.

[Ser Cubus]

masz rację

baza wygląda mniejwięcej tak:

tabela klient:
imie varchar,
nazwisko varchar,
id_klienta serial,
cos tam jeszcze

tabela plyty:
id_plyty serial,
cos tam

tabela wypozyczenie
kto_wypozyczyl int,
co_wypozyczyl int,
data_wypozyczenia date,
data_zwrotu date

widze już błąd w nazwach, pisałem np id_klienta, a w tabeli jest kto_wypozyczył

mam pytanie co to znaczy:
REFERENCES plyta ON DELETE RESTRICT ON UPDATE CASCADE

Oraz czy da się zliczyć karę za wszytkie wypozyczenia? Nawet jeżeli zrobiłem to nieświadomie to rozumiem, że zliczyłem ilość dni wszystkich dat oddania i odejmuje wypozyczenia. Może i taki sposób nie jest zbyt finezyjny, ale powinien chyba zadziałać. W innym wypadku domyślam się, że trzeba by iterować po kolejnych rekordach i dodawać kolejne kary do już naliczonych. Tylko, że zupelnie nie wiem jak się zabrać za ten wariant.

[dexter90]

Kod: Zaznacz cały

ON DELETE RESTRICT

uniemożliwia usunięcie rekordów z tablicy macierzystej jeżeli odwołują się do niej rekordy z tabel potomnych.

Oraz czy da się zliczyć karę za wszytkie wypozyczenia?

Jasne, jak najbardziej. Podałeś nazwy tabel, ale nie podałeś relacji. Wtedy bym sobie to zrobił u siebie, ale tak to robię po swojemu:

Klient może mieć wiele wypożyczeń ( 1 do wiele )
Wypożyczenie - płyty ( wiele do wiele )

Kod: Zaznacz cały

create table klient
(
id_k int primary key,
imie ntext not null
)

create table wypozyczenia
(
id_w int primary key,
data_wy DATE not null,
data_od DATE null,
id_k int not null
)

alter table wypozyczenia add constraint fk_k foreign key(id_k) references klient(id_k);

create table plyty
(
id_p int primary key,
nazwa ntext not null
)

create table wypozyczenia_plyty 
(
id_p int not null,
id_w int not null
)

alter table wypozyczenia_plyty add constraint fk_pww foreign key(id_p) references plyty(id_p);
alter table wypozyczenia_plyty add constraint fk_pwww foreign key(id_w) references wypozyczenia(id_w);

Kod: Zaznacz cały

insert into klient values(1,'Guest1')
insert into klient values(2,'Guest2')

insert into plyty values(1,'ABBA');
insert into plyty values(2,'Perfect');

insert into wypozyczenia values(1,'2012-04-1','2012-04-11',1);
insert into wypozyczenia values(2,'2012-04-6','2012-04-14',2);
insert into wypozyczenia values(3,'2012-04-7','2012-04-8',1);

insert into wypozyczenia_plyty values(1,1)
insert into wypozyczenia_plyty values(2,2)

Zapytanie:

Kod: Zaznacz cały

select 
k.imie, w.data_wy, w.data_od, p.nazwa , (SELECT DATEDIFF(day,w.data_wy,w.data_od)) as Czas_wypozyczenia,
CASE 
WHEN (SELECT DATEDIFF(day,w.data_wy,w.data_od)) >=10 then CONVERT(VARCHAR(10),(SELECT DATEDIFF(day,w.data_wy,w.data_od)*2))
ELSE 'Nie ma kary'
END
from klient k, wypozyczenia w, plyty p, wypozyczenia_plyty wp where k.id_k=w.id_k 
and 
p.id_p=wp.id_p 
and 
wp.id_w=w.id_w;

Uwagi: dużo błędów z mojej strony ( nazwy tabel w języku Polskim, liczba mnoga, brak sensowanych atrybutów )

Uwagi2: Zapytanie długie, nieprzemyślane, ale ma Ci poszerzyć horyzonty. Zawiera nadmiarowe dane do wyświetlenia, ale powtarzam: zerknij z czym to się je

Uwagi3: nie wiem czy znasz JOIN-y więc ich nie stosowałem.

Baza to SQL Server.

Pozdrawiam

PS. Twoje zadanie to poprawić moje zapytanie. Dodałem bajery w postaci Cas-ów i ogólnie dostosuj je do siebie. Zrobiłem celowo pare błędów aczkolwiek zapytanie działa.

[Ser Cubus]

super, dzięki

mam jeszcze tylko jedno pytanie, co oznacza ten fragment? Wiem tylko jak dodawać kolumny do tabeli za pomocą tej komendy, a w necie same przykłady, bez tłumaczenia

Kod: Zaznacz cały

alter table wypozyczenia add constraint fk_k foreign key(id_k) references klient(id_k);

ta komenda edytuje tabelę wypozyczenia, dodaje (co tutaj robie constraint?) zmienną fk_k która jest kluczem obcym odnoszącym się do tabeli klient, czy tak?

[dexter90]

Nazwa ograniczenia: fk_k
Zadanie: Utrzymywanie referencji do klucza głównego ( id_k ) z tabeli klient. Wprowadza to relację 1- wiele ( wiele po stronie wypozyczenia ).

[Ser Cubus]

co to oznacza?

Kod: Zaznacz cały

select k.imie, w.data_wy ... as ...

chodzi o coś w stylu

Kod: Zaznacz cały

select klient.imie, wypozyczenie.data_wy ...  from klient, wypozyczenie (?)

i czy

Kod: Zaznacz cały

as

, dziala tak samo jak

Kod: Zaznacz cały

into

[dexter90]

Kod: Zaznacz cały

as

Założmy, że masz w swojej aplikacji klasę Product

Kod: Zaznacz cały

class Product
{
public int ID_P { get; set;}
public string Name { get; set;}
}

a tabela w bazie danych o nazwie Products ma taką strukturę:

Kod: Zaznacz cały

Product[b]s[/b]
(
id_p,
name
)

To jeżeli aplikacja kliencka będzie miała zwracać jako wynik wszystkie obiekty klasy Product z bazy danych z tabeli Products

to aby osiągnąć zamierzony efekt, dasz takie zapytanie:

Kod: Zaznacz cały

select id_p as ID_P, name as Name from Products

wtedy w aplikacji twoja funkcja wyglądała by tak:

Kod: Zaznacz cały

IEnumerable<Product> GetAll()
{
return Database.Open().GetAll().Cast<Product>();
}

gdyby nie alias, zapytanie by nie zwróciło żadnych wyników bo rzutowanie na obiekt klasy Product nie powiodło się poprzez niezgodność nazw atrybutów.

Kod: Zaznacz cały

INTO

służy do przekopiowywania wyników/całych tabel.

Edit:

Tak pierwsze dwa cytowania właśnie to oznaczają, dobrze interpretujesz.

[Ser Cubus]

nie do końca wiem jak wpisać datę zwrotu i nie wiem czy dobrze napisałem ta funkcję

sądzę, że ten screen pokazuje wszytko co chcę (ale niepotrafie ) zrobić:


kod funkcji:

Ukryta treść:    

Kod: Zaznacz cały

CREATE OR REPLACE FUNCTION licz_kare(varchar, varchar) returns int
LANGUAGE plpgsql
AS
'
DECLARE

kara int;
nr_klienta int;
czas_wyp int;
data_wyp date;
data_zw date;

BEGIN

SELECT id_klienta FROM klient WHERE imie = $1 AND nazwisko = $2 into nr_klienta;
SELECT data_wypozyczenia FROM wypozyczenie WHERE kto_wypozyczyl = nr_klienta INTO data_wyp;
SELECT data_zwrotu FROM wypozyczenie WHERE kto_wypozyczyl = nr_klienta INTO data_zw;
SELECT DATEDIFF(day, data_wyp, data_zw) as czas_wyp;

IF czas_wyp > 10
THEN kara = 10 * czas_wyp;
ELSE kara = 0;
END IF;

return kara;
END;
';

i jeszcze jedno pytanie, mianowicie czy moglibyśmy się przenieść na gg? Mam jeszcze jedno zadanie do zrobienia, które pewnie jest równie banalne, ale wolałbym się na tym skupić i zrobić to na raz, niż tak co chwilę wracać.

[dexter90]

GG puszczam na PW. W takim razie tu już nie odpowiadam.

Pozdrawiam

-- 2 maja 2013, o 21:03 --

Date zwrotu daj w 'DATA'

Nawiązujesz do procedury bez gwiazdki.