Funkcje w MYSQL

Question

Mam za zadanie stworzenie funkcji która dla podanego nazwiska autora zwróci liczbę książek przez niego napisanych. Funkcja ma operować na danych z trzech tabel

ksiazka             ID_KS | TYTUL  | CENA  | ID_GAT | ID_WYD | DATA_WYD   | L_STRON |

autor                 ID_AUT | NAZWISKO| IMIE| NARODOWOSC

autor_tytul         ID_AUTOR | ID_KSI

Stworzyłem zapytanie które działa jednak żadna próba zaimplementowania go w funkcję się nie powiodła.

Oto dwa kody których próbowałem użyć:

CREATE FUNCTION iloscKsiazek (x varchar(200)) RETURNS char(200)
begin
declare zmienna varchar(200)
select a.nazwisko, count(*) as "Liczba Ksiazek" from autor as a, ksiazka as k, autor_tytul as at where a.id_aut = at.id_autor and k.id_ks = at.id_ksi and a.nazwisko=x into zmienna
return zmienna
end;

CREATE FUNCTION iloscKsiazek (x varchar(200)) RETURNS varchar(200)
select 
a.nazwisko, 
count(*) as "Liczba Ksiazek" 
from 
autor as a, 
ksiazka as k, 
autor_tytul as at 
where 
a.id_aut = at.id_autor 
and k.id_ks = at.id_ksi 
and a.nazwisko=x;

 

 

Answer 1

Przy tworzeniu funkcji w MySQL łatwo popełnić ten frustrujący błąd (a zwracany komunikat błędu dla przeciętnego "zjadacza sql" jest "mało czytelny"), ponieważ w dokumentacji MySQL jest słaby nacisk aby na to zwrócić uwagę, chodzi o polecenie DELIMITER (MySQL CREATE FUNCTION Syntax)
Demo on-line. Mam nadzieje, że to wyjaśnienie będzie pomocne. 

DELIMITER $$

CREATE FUNCTION f_ile_ksiazek (autor_nazwisko varchar(50)) RETURNS INTEGER
    BEGIN
        DECLARE parm_ile_ksiazek int;

            SELECT COUNT(*) AS 'Liczba Książek' INTO parm_ile_ksiazek FROM autor AS a, ksiazka AS k, autor_ksiazka AS ak
                WHERE a.id = ak.id_autor AND k.id = ak.id_ksiazka AND a.nazwisko = autor_nazwisko;

        RETURN parm_ile_ksiazek;
    END $$

DELIMITER ;

Użycie:

SELECT a.imie, a.nazwisko, f_ile_ksiazek(a.nazwisko) AS 'Liczba Książek' FROM autor AS a, ksiazka AS k, autor_ksiazka AS ak
    WHERE a.id = ak.id_autor AND k.id = ak.id_ksiazka GROUP BY ak.id_autor;

Struktura danych do testów:

# CREATE DATABASE demo_use_function;

DROP TABLE IF EXISTS ksiazka;
CREATE TABLE ksiazka (
    id int NOT NULL PRIMARY KEY,
    tytul varchar(50) NOT NULL,
    cena decimal(8,2) NOT NULL,
    w_data year NOT NULL,
    l_stron smallint unsigned DEFAULT '0'
) CHARACTER SET utf8 COLLATE utf8_polish_ci;

INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (1, 'Czysty kod w Pythonie', 31.90, '2020', 176); # Sunil Kapil
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (2, 'Czysty kod. Podręcznik dobrego programisty', 69.00, '2010', 424); # Robert Martin
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (3, 'Mistrz czystego kodu', 29.40, '2017', 216); # Robert Martin
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (4, 'Git. Rozproszony system kontroli wersji', 46.60, '2013', 328); # Włodzimierz Gajda
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (5, 'GIMP. Praktyczne projekty', 52.90, '2015', 480); # Włodzimierz Gajda
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (6, 'Napisz kod 1. Programowanie dla najmłodszych', 14.99, '2016', 32); # Max Wainewright
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (7, 'Napisz kod 2. Programowanie dla najmłodszych', 14.99, '2016', 32); # Max Wainewright
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (8, 'Napisz kod. Przewodnik dla dorosłych', 20.19, '2016', 64); # Max Wainewright
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (9, 'Opus magnum C++11. Programowanie w języku C++', 112.20, '2017', 1696); # Jerzy Grębosz
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (10, 'Opus magnum C++. Misja w nadprzestrzeń C++14-17', 41.65, '2020', 144); # Jerzy Grębosz
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (11, 'Opus Magnum C++ 11. w.2020', 152.10, '2020', 1648); # Jerzy Grębosz
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (12, 'Środowiska programowania robotów', 59.00, '2020', 284); # Wojciech Kaczmarek
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (13, 'Programowanie robotów przemysłowych', 59.00, '2020', 281); # Wojciech Kaczmarek
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (14, 'Praktyczny kurs SQL', 36.90, '2015', 336); # Danuta Mendrala
INSERT INTO ksiazka (id, tytul, cena, w_data, l_stron) VALUE (15, 'Access 2016 PL. Kurs', 33.90, '2016', 312); # Danuta Mendrala

# SELECT * FROM ksiazka;

DROP TABLE IF EXISTS autor;
CREATE TABLE autor (
    id int NOT NULL PRIMARY KEY,
    imie varchar(50) NOT NULL,
    nazwisko varchar(50) NOT NULL
) CHARACTER SET utf8 COLLATE utf8_polish_ci;

INSERT INTO autor (id, imie, nazwisko) VALUE (1, 'Sunil', 'Kapil');
INSERT INTO autor (id, imie, nazwisko) VALUE (2, 'Robert', 'Martin');
INSERT INTO autor (id, imie, nazwisko) VALUE (3, 'Włodzimierz', 'Gajda');
INSERT INTO autor (id, imie, nazwisko) VALUE (4, 'Max', 'Wainewright');
INSERT INTO autor (id, imie, nazwisko) VALUE (5, 'Jerzy', 'Grębosz');
INSERT INTO autor (id, imie, nazwisko) VALUE (6, 'Wojciech', 'Kaczmarek');
INSERT INTO autor (id, imie, nazwisko) VALUE (7, 'Danuta', 'Mendrala');

# SELECT * FROM autor;

DROP TABLE IF EXISTS autor_ksiazka;
CREATE TABLE autor_ksiazka (
    id_autor int NOT NULL,
    id_ksiazka int NOT NULL,
    FOREIGN KEY (id_autor) REFERENCES autor(id),
    FOREIGN KEY (id_ksiazka) REFERENCES ksiazka(id)
);

INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (1, 1);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (2, 2);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (2, 3);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (3, 4);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (3, 5);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (4, 6);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (4, 7);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (4, 8);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (5, 9);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (5, 10);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (5, 11);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (6, 12);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (6, 13);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (7, 14);
INSERT INTO autor_ksiazka (id_autor, id_ksiazka) VALUE (7, 15);

# SELECT * FROM autor_ksiazka;

DROP FUNCTION IF EXISTS f_ile_ksiazek;

DELIMITER $$

CREATE FUNCTION f_ile_ksiazek (autor_nazwisko varchar(50)) RETURNS INTEGER
    BEGIN
        DECLARE parm_ile_ksiazek int;

            SELECT COUNT(*) AS 'Liczba Książek' INTO parm_ile_ksiazek FROM autor AS a, ksiazka AS k, autor_ksiazka AS ak
                WHERE a.id = ak.id_autor AND k.id = ak.id_ksiazka AND a.nazwisko = autor_nazwisko;

        RETURN parm_ile_ksiazek;
    END $$

DELIMITER ;

SELECT a.imie, a.nazwisko, f_ile_ksiazek(a.nazwisko) AS 'Liczba Książek' FROM autor AS a, ksiazka AS k, autor_ksiazka AS ak
    WHERE a.id = ak.id_autor AND k.id = ak.id_ksiazka GROUP BY ak.id_autor;

 

Comments

Dziękuję, zadziałało