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ł ;)

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