0eKHFLx8

1DELIMITER //
2
3/* THE INNER PROCEDURE */
4
5CREATE PROCEDURE spGetPubStats(IN pubCode CHAR(3), OUT publisherName CHAR(25), OUT num_of_distinct_authors INT,
6    OUT distinct_books_published INT, OUT book_highest_num_on_hand CHAR(40), OUT num_on_hand INT, OUT total_books_on_hand INT)
7BEGIN
8
9    /* First, check whether the Publisher code passed in
10    exists in the Database */
11    IF EXISTS
12    (
13        SELECT publisherCode
14        FROM Publisher
15        WHERE publisherCode = pubCode
16    ) 
17
18    /* If the Publisher Code IS in the database,
19    calculate the required statistics */
20    THEN
21        BEGIN
22
23        /* 1.) Set the publisherName */
24
25        SET publisherName =
26        (SELECT publisherName
27         FROM Publisher
28         WHERE publisherCode = pubCode);
29
30        /* 2.) Set the number of distinct authors who have
31        written books for the publisher */
32
33        SET num_of_distinct_authors =
34        (SELECT COUNT(Author.authorNum)
35        FROM ((Author INNER JOIN Wrote ON Author.authorNum = Wrote.authorNum) INNER JOIN Book ON Wrote.bookCode = Book.bookCode) 
36            INNER JOIN Publisher ON Book.publisherCode = Publisher.publisherCode
37        WHERE Publisher.publisherCode = pubCode);
38
39        /* 3.) Set the number of different books published by this publisher */
40
41        SET distinct_books_published =
42        (SELECT COUNT(bookCode)
43        FROM Book
44        WHERE publisherCode = pubCode);
45
46        /* 4.) AND 5.) Set the title of the book published by this publisher that has
47        the highest number of onHand units collectively in all branches
48        of Henry Books AND Set number of OnHand books for the book mentioned*/
49
50        SELECT *
51        FROM
52        (
53            SELECT MaxTitle, MAX(OnHandUnits)
54            FROM
55            (
56                SELECT Inventory.BookCode, SUM(OnHand) AS OnHandUnits, Book.title AS MaxTitle
57                    FROM (Inventory INNER JOIN Branch ON Inventory.BranchNum = Branch.branchNum)
58                        INNER JOIN Book ON Inventory.BookCode = Book.bookCode
59                    WHERE Inventory.BookCode IN
60                        (
61                        SELECT DISTINCT Inventory.BookCode
62                        FROM ((Book INNER JOIN Publisher ON Book.publisherCode = Publisher.publisherCode) INNER JOIN Inventory
63                        ON Book.bookCode = Inventory.BookCode) INNER JOIN Branch ON Inventory.BranchNum = Branch.branchNum
64                        WHERE Publisher.publisherCode = pubCode
65                        )
66                    GROUP BY Inventory.BookCode
67                    ORDER BY OnHandUnits DESC
68            ) AS MaxOnHandTitle
69        ) AS SelectorStatement
70        INTO book_highest_num_on_hand, num_on_hand;
71
72        /* 6.) Set the cumulative sum of onHand books from all branches
73        for all books published by this publisher */
74
75        SET total_books_on_hand =
76        (SELECT SUM(OnHand)
77        FROM ((Inventory INNER JOIN Branch ON Inventory.BranchNum = Branch.branchNum) INNER JOIN Book
78            ON Inventory.BookCode = Book.bookCode) INNER JOIN Publisher ON Book.publisherCode = Publisher.publisherCode
79        WHERE Publisher.publisherCode = pubCode);
80
81        END;
82
83    /* Otherwise, notify the caller */
84    ELSE
85        BEGIN
86            /* Raise an error signal and provide descriptive error text */
87            SIGNAL SQLSTATE '45000'
88            SET MESSAGE_TEXT = 'The specified publisher code was not found in the database.';
89        END;
90
91    END IF;
92END //
93
94/* THE OUTER PROCEDURE */
95
96CREATE PROCEDURE spGetAllPubStatsRE()
97BEGIN 
98
99    /* Variable that will store the
100    publisher codes that will be passed to
101    the inner function */
102    DECLARE pubCode CHAR(3);
103
104    /* Cursor that will iterate through all the
105    publisher codes in the Publisher table */
106    DECLARE pub_cursor CURSOR FOR
107        SELECT publisherCode
108        FROM Publisher;
109
110    /* First, drop permanent database table
111    containing publisher stats if it exists */
112    DROP TABLE IF EXISTS Publisher_Stats;
113
114    /* Create the permanent database table
115    (or recreate it if it was deleted above */
116    CREATE TABLE Publisher_Stats
117    (
118        publisherName CHAR(25),
119        num_of_distinct_authors INT,
120        distinct_books_published INT,
121        book_highest_num_on_hand CHAR(40),
122        num_on_hand INT,
123        total_books_on_hand INT
124    );
125
126    /* Open the cursor so we can use it */
127    OPEN pub_cursor;
128
129    /* Loop for iterating through the publisher codes */
130    LOOP
131
132        /* Fetch each Publisher code into pubCode */
133        FETCH pub_cursor INTO pubCode;
134
135        /* Call the inner procedure, passing 
136        it the current publisher code as input */
137
138        CALL spGetPubStats(pubCode, @publisherName, @num_of_distinct_authors, @distinct_books_published,
139            @book_highest_num_on_hand, @num_on_hand, @total_books_on_hand);
140
141        /* Store the results of spGetPubStats
142        in the Publisher_Stats table */
143        INSERT INTO Publisher_Stats(publisherName, num_of_distinct_authors, distinct_books_published,
144        book_highest_num_on_hand, num_on_hand, total_books_on_hand)
145        SELECT @publisherName, @num_of_distinct_authors, @distinct_books_published, @book_highest_num_on_hand,
146            @num_on_hand, @total_books_on_hand;
147
148    END LOOP;
149
150    /* At this point, we're done with
151    the cursor, so close it */
152    CLOSE pub_cursor;
153END //
154
155/* CHANGE DELIMITER BACK */
156DELIMITER ;
157	
158CALL spGetAllPubStatsRE;
159
160SELECT * 
161FROM Publisher_Stats;
162	
163Error Code: 1329. No data - zero rows fetched, selected, or processed
164	
165SELECT * 
166FROM Publisher_Stats;
167	
168SET publisherName =
169    (SELECT publisherName
170     FROM Publisher
171     WHERE publisherCode = pubCode);