Jak napisać procedurę

0

Mam takie zadanie do zrobienia ale nie wiem jak się za nie chwycić. Treść to w skrócie " Napisać procedurę identyfikującej bestsellery, czyli takiego typu książki, której egzemplarze są wypożyczane częściej niż dwa razy w miesiącu. Dalej takie wytyczne :

1.Napisz skrypt, który do tabeli BOOKS doda kolumnę BESTSELLER typu BOOLEAN. Kolumna ta będzie przyjmowała wartość true, jeżeli książka jest wypożyczana częściej niż dwa razy w miesiącu
2.Do skryptu dodaj procedurę UpdateBestsellers(), która zaktualizuje kolumnę BESTSELLER przy każdej książce w tabeli BOOKS na podstawie danych zawartych w tabeli RENTS. Użyj w tym celu kursora.

Kolumne do tabeli BOOKS już dodałem. Teraz myślenie mi się troche mija z celem. Nie rozumiem po co ma być ten skrypt ktory dodaje kolumne, skoro już sobie recznie taka dodałem. Wystarczy aby byla logika zawarta ktora identyfikuje ktora ksiazka jest wypozyczana w razy w miesiacu. I z tym mam problem nie wiem jak to sformułowac aby właściwą ilość ksiazek mi zwracało.

Z podpunktu 2 to wiem że musze użyć kursora i poiterowac po zbiorze. Mogłbym liczyć na jakąś pomoc. Załaczam tabele które mam aby łatwiej było łatwiej o rozeznanie.

Tabela Rents: https://scr.hu/Jjy1ZdA
I tabela Books w której kolumna bestseller ma być uzupełniona: https://scr.hu/Naw1Py3

A tutaj to co na razie mam taki pseudo szablon na którym sie wzorowałem jak napisać kursor. To wszystko co mam.

DROP PROCEDURE IF EXISTS UpdateBestsellers;

DELIMITER $$

CREATE PROCEDURE UpdateBestsellers()
BEGIN
	DECLARE BK_ID INT;
    
    
    DECLARE ALL_BESTSELLERS CURSOR FOR SELECT BOOK_ID FROM RENTS;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
    OPEN ALL_BESTSELLERS;
    WHILE (FINSIHED = 0) DO
		FETCH ALL_BOOKS INTO BK_ID;
        IF(FINISHED = 0) THEN
			
			END IF;
        END WHILE;
        CLOSE ALL_BESTSELLERS;
END $$

DELIMITER ;

Aha i mentor zaproponował mi coś takiego ale kompletnie nie wiem jak to wpasować ;/ :

declare ALL_BESTSELLERS cursor for select book_id from rents t group by  EXTRACT( YEAR_MONTH FROM rent_date),book_id having count(*) > 2 ;
0

Powiem ci, że dziwne zadanie dostałeś... Rozumiem, że na uczelnię. W normalnej pracy z bazą na 99% w życiu nie użyjesz kursora. A już na pewno nie w takim celu, jak opisany... Do tego wystarczy (naprawdę proste) VIEW.
Poza tym myślałem, że na uczelniach używa się innych baz danych niż MySQL...

0

@Marcin.Miga: Nie. To nie na uczelnie. Jestem na bootcampie Javy. Ale 'liznąć' sql też podobno trzeba :) wcześniej ogarniałem sqla to co zostało nam przedstawione ale w tym przypadku stanąłem. Masz może jakiś pomysł ? Byłbym wdzięczny. Musze iść dalej a nie pójde poki tego nie zrobie... Wiem że więcej się człowiek nauczy jak sam dojdzie do problemu ale mam ciemno przed oczami :/ Nie mam pojęcia jak stworzyć zapytanie które bedzie rozpoznawało ilość wypożyczanych książek wiecej niż 2 razy w miesiącu i użyć kursora aby w tabeli Books ustawiło po kolei statusy kolumny 'Bestseller' dla każdej książki(rekordu). Mentor jak wspomniałem coś zasugerował ale niezbyt wiem jak to wpleść w kod i ta literka 't'. Ktoś ma jakiś pomysł , podpowiedź ? Nie wiem może to jest banalne ale praktycznie przedwczoraj zacząłem przerabiać ten dział i takie mieszane uczucia....

0

w TSQL (w tabeli BOOKS u mnie kluczem głównym jest ID. Zawsze trzymam się konwencji BOOKS:ID -----< RENTS:BOOK_ID)

UPDATE BOOKS SET BESTSELLER =N'true'
WHERE ID in
(SELECT Book_ID
FROM RENTS
GROUP BY Book_ID, YEAR(Rent_Date), MONTH(Rent_Date)
HAVING (YEAR(Rent_Date) = 2018) AND (MONTH(Rent_Date) = 1) AND (SUM(1) > 2)
)

0

Pytanie podstawowe, to zanim zaczniesz pisać rozwiązanie to odpowiedz na pytanie:
Czy bestseller to jest książka która w każdym miesiącu jest wypożyczona przynajmniej 2 razy, czy w jakimkolwiek miesiącu była wypożyczona przynajmniej 2 razy?

0

@Panczo Nie jestem ekspertem :) ale myślę że chodzi o ostatni miesiąc. To miałoby sens, prawda? Po co w ogóle brać pod uwage okres załóżmy że sprzed roku skoro status bestsellera może sie zmieniać. Chociaż z drugiej strony nie wiem jakby ten skrawek kodu co mentor mi podesłał czy rozwiązanie @cw (może też dobre nie wiem, choć niby mam użyc prcedury i kursora) zachowałoby sie przy wiekszej ilości danych. Myślę że aby nie utrudniać w kolumnie RENT_DATE po prostu podane sa daty z ostaniego miesiaca czyli września i ten miesiąc jest rozliczany.

0

A mnie się wydaje, że OSTATNI miesiąc, czyli -= 30 dni.

-- pseudo-SQL
UPDATE Books Set Bestseller=(SELECT Count(Rent_date) FROM Rents WHERE Book_id=Books.ID And Rent_date>Current_date - INTERVAL '1 month')>=2
-- jeśli by miało byc w Cursorze, to wykonujesz to dla pojedynczego rekordu, czyli dodajesz na koncu
WHERE ID=(aktualne ID)
1

No to trochę ewangelizacji ;) Jeżeli używasz kursora w sql do aktualizacji danych to znaczy, że powinieneś myśleć o zmianie specjalizacji. Nie znam sytuacji, kiedy w operacjach CRUD jest on potrzebny. Skoro mentor zaproponował kursor od razu po wypożyczeniach > 2, to spytaj go co z rekordami, które przestały być bestsellerami? chyba, że zaczniemy procedurę od:

update books set bestseller=0

co jakby mija się z kursorem, bo skoro uaktualniamy całą tabelę to możemy od razu przypisać prawidłową wartość:

update 
     books 
     left join (select bookid, 1 bestseller from rents where rent_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() group by bookid having count(*)>2) r
on books.bookid=r.bookid
set
books.bestseller = ifnull(r.bestseller,0)

I teraz chcąc to ubrać w kursor musisz uwzględnić ksiązki które nie były nigdy wypożyczone, aby ustawić bestseller na 0, nie znam konstrukcji kursora w mysql poszedłbym w coś takiego:

DROP PROCEDURE IF EXISTS UpdateBestsellers;

DELIMITER $$

CREATE PROCEDURE UpdateBestsellers()
BEGIN
    DECLARE BK_ID INT;
    DECLARE bs INT;
    DECLARE ALL_BESTSELLERS CURSOR FOR SELECT BOOK_ID,ifnull(r.bestseller,0)  FROM RENTS 
     left join (select book_id, 1 bestseller from rents where rent_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() group by bookid having count(*)>2) r
on books.bookid=r.bookid;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
    OPEN ALL_BESTSELLERS;
    WHILE (FINSIHED = 0) DO
        FETCH  ALL_BESTSELLERS CURSOR INTO BK_ID, bs;
        IF(FINISHED = 0) THEN
                 update books set bestseller = bs where book_id= BK_ID
            END IF;
        END WHILE;
        CLOSE ALL_BESTSELLERS;
END $$

DELIMITER ;

Przeanalizuj i pogadaj z mentorem po co w założeniach kazał użyć kursora, bo nawet ograniczając to having mamy sytuacje, że zamiast 1 zapytania wykonamy na bazie ich kilka tysięcy. Chętnie poznam odpowiedź...

0

@Panczo Dzięki Wielkie. Mimo iż nie jest to raczej poprawna konstrukcja bo nie zadziałało i błąd składni wyskoczył i zauważyłem chyba kilka nieścisłości to jednak poszedłem troche na łatwizne :) i zrobiłem coś takiego co zadziałało i chyba zmierza w dobrą stronę :

DROP PROCEDURE IF EXISTS UpdateBestsellers;
 
DELIMITER $$
 
CREATE PROCEDURE UpdateBestsellers()
BEGIN
    DECLARE BK_ID INT;
    DECLARE FINISHED INT DEFAULT 0;
    DECLARE ALL_BESTSELLERS cursor for select book_id from rents ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
    OPEN ALL_BESTSELLERS;
    WHILE (FINISHED = 0) DO
        FETCH ALL_BESTSELLERS INTO BK_ID;
        IF(FINISHED = 0) THEN
                 UPDATE BOOKS SET BESTSELLER = true WHERE BOOK_ID = BK_ID;
		COMMIT;
        END IF;
        END WHILE;
        CLOSE ALL_BESTSELLERS;
END $$
 
DELIMITER ;

Na razie na sztywno mi uzupełnia dla każdej książki wartośc true czyli w bazie danych 1. Przez chwile sie martwiłem bo w Tabeli BOOKS wszystkie rekordy bez id = 3 zmieniły status na 1 czyli poiterowało mi ale po chwili spostrzegłem że tak ma być bo wczytuje id z tabeli RENTS gdzie ksiazka z id = 3 nie jest wypozyczona :) Myślę że postęp jakiś jest. Małymi krokami teraz wpleść ten warunek jesli wiecej niz 2 ksiazki miesiacu sa wypozyczone to ustawia bestseller na true jesli nie to false. Myslałem już żeby zrobić jakoś osobną funkcje która by to indentyfikowała i wywołać ją w tej procedurze, ale coś tam mi sie nie zgadza miałem błedy coś typu "subquery returns more than 1 row" i chyba to zostawie i spróbuję pomyśleć żeby tu w tej procedurze jakoś zawrzeć tą logikę choć już mi to spędza sen z powiek. Jeśli ktoś coś wie to chętnie posłucham opinii. I spokojnie pogadam z mentorem, dopiero poznałem tą konstrukcje jak mówiłem i też nie za bardzo przypadła mi do gustu :/

0

Kursor to nigdy nie jest krok w dobrą stronę ;)
Walcz, ale podpytaj mentora i podziel się odpowiedzią sam jestem ciekaw co odpowie.

Jako gość, który za pisanie kursora ucina ręce nie dziwię się, że mój kod nie zadziałał ;)

0
Panczo napisał(a):

Kursor to nigdy nie jest krok w dobrą stronę ;)
Walcz, ale podpytaj mentora i podziel się odpowiedzią sam jestem ciekaw co odpowie.

Jako gość, który za pisanie kursora ucina ręce nie dziwię się, że mój kod nie zadziałał ;)

Chyba miałem przebłysk geniuszu :D bo dodałem ten kawałek kodu co zalecił i chyba zadziałało bo w ten sposób kursor od razu operuje na zbiorze ktory zawiera id własciwych książek. ```

DROP PROCEDURE IF EXISTS UpdateBestsellers;
 
DELIMITER $$
 
CREATE PROCEDURE UpdateBestsellers()
BEGIN
    DECLARE BK_ID INT;
    DECLARE FINISHED INT DEFAULT 0;
    DECLARE ALL_BESTSELLERS cursor for select book_id from rents group by  EXTRACT( YEAR_MONTH FROM rent_date),book_id having count(*) > 2 ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
    OPEN ALL_BESTSELLERS;
    WHILE (FINISHED = 0) DO
        FETCH ALL_BESTSELLERS INTO BK_ID;
        IF(FINISHED = 0) THEN
                 UPDATE BOOKS SET BESTSELLER = true WHERE BOOK_ID = BK_ID;
		COMMIT;
        END IF;
        END WHILE;
        CLOSE ALL_BESTSELLERS;
END $$
 
DELIMITER ;

No i ogólnie dziękuję tobie i innym za chęci i starania w udzieleniu pomocy takiemu nieukowi jak ja :) Wyślę mu to i zobaczymy. Konsultacje mam w sobote wiec go na pewno podpytam. Jednakże tak w gruncie rzeczy jak mógłbym sprecyzować pytania o te kursory bo raczej większoć komentarzy w stronę kursora było negatywnych . Nie mam jeszcze doświadczenia ani wiedzy jakie ma on wady lub zalety ( czy nawet jakiekolwiek ma ) aby samodzielnie móc wysuwać sensowne wnioski. Chodzi o aspekt dlaczego trzeba było użyć kursora w tym zadaniu bo można było lepiej bez, czy bardziej w sensie jakie rodzi on problemy, kiedy warto go używać, coś w ten deseń. @Panczo Jeśli miałbyś robić burze mózgów i troche się posprzeczać to w co dokładnie byś uderzył :)

0

Jak pisałem wcześniej Twoja procedura ustawi wartość dla aktualnych bestsellerów, natomiast nie zdejmuje oznaczenia dla tych, które przestały nimi być.

Nie chodzi o to byś się sprzeczał, zapytaj tylko dlaczego chciał to robić kursorem, bo on tu jest niepotrzebny.

0

@Panczo Faktycznie. A co jeśli by dodać aby zmieniało statusy książek które przestały nimi być ( nie wiem, nie zastanawiałem się jak by to miało być zrobione ) ale tak teoretycznie to czy wtedy kursor miałby sens. Tak ciężko mi sobie to wyobrazić, bo skoro przedstawili nam taką konstrukcję i trzeba było użyć jej w tym konkretnym zadaniu to chyba musi mieć jakieś zastosowania. Nie trzeba od razu rąk ucinać :)

1 użytkowników online, w tym zalogowanych: 0, gości: 1