The follwoing stored procedure spGetAllPubStatsFL, (where F and L refer to your first and last name) and load it into your MySQL Database where you also created the Henry Book Store database tables. The stored procedure is to output various statistics for all publishers in the database. This stored procedure will call another stored procedure named (spGetPubStatsFL) that accepts a publisher code as input and returns the required statistics only if the given publisher code exists

is seen below

DELIMITER //

CREATE FUNCTION uspGetPubStatsMEP (pcode publisher.publishercode%TYPE)

RETURNS text AS $$

DECLARE

eachrecord text; -- Variable to hold all the records retrieve

booktitle book.title%TYPE;

bcode book.bookcode%TYPE;

maxonhand text;

testtext text:='N/A';

totonhand text;

BEGIN

-- Retrieve the name of thepublisher

SELECT publishername INTO eachrecord

FROM PUBLISHER

WHERE publishercode = pcode;

IF (eachrecordISnull)

THEN

RAISE NOTICE 'No publisher exists for the given code(%)',pcode;

RETURN (-1);

END IF;

-- The number of distinct authors who have written book(s)forthispublisher.

SELECT eachrecord||' No.Authors:'||COUNT(DISTINCTauthornum)INTO

eachrecord

FROM BOOK B, WROTE W

WHERE publishercode=pcodeANDB.bookcode=W.bookcode;

-- The number of different books published by this publisher.

SELECT eachrecord||' No.Books:'||COUNT(*)INTOeachrecord

FROM BOOK

WHERE publishercode=pcode;

-- The title of the book published by this publisher,that has the highest number

-- of onHand (Inventory) values from all branchesof Henry Books.

CREATE TABLE book_onhandAS

SELECT bookcode,SUM(onhand)ASsum_onhand

FROM INVENTORYI,BRANCHB

WHERE I.branchnum=B.branchnum

GROUP BY bookcode;

CREATE TABLE pubbook_onhand AS

SELECT bookcode, sum_onhand

FROM book_onhand

WHERE bookcodeIN

( SELECT bookcode

FROM BOOK

WHERE publishercode = pcode

)

GROUP BY bookcode,sum_onhand;

SELECT bookcode INTO bcode

FROM pubbook_onhand

WHERE sum_onhand = (SELECT MAX(sum_onhand)

FROM pubbook_onhand

);

SELECT title INTO booktitle

FROM BOOK

WHERE bookcode=bcode;

-- The number of on Hand values for the above book.

SELECT sum_onhand INTO maxonhand

FROM pubbook_onhand

WHERE bookcodeIN ( SELECT bookcode

FROM BOOK

WHERE title = booktitle

);

-- The cumulative sum of on Hand values from all branches for all books published by thispublisher.

-- use table book_onhand

SELECT sum_onhand INTO totonhand

FROM book_onhand bh, BOOK B

WHERE publishercode = pcode AND

bh.bookcode = B.bookcode;

IF (booktitle IS NULL)

THEN SELECT testtext INTO booktitle;

SELECT testtext INTO maxonhand;

SELECT testtext INTO totonhand;

END IF;

SELECT eachrecord || ' onhandVal:' ||maxonhand ||'

Totalonhand:'|| totonhand ||' onHandBook:'||booktitle INTO eachrecord;

DROP TABLE book_onhand;

DROP TABLE pubbook_onhand;

RETURN eachrecord;

END;

$$ language plpgsql;

-- Various statistics for all publishers in the database

CREATE OR REPLACE FUNCTION uspGetAllPubStatsMEP()

RETURNS SETOF text as $$

DECLARE

pcode publisher%rowtype;

pubstat text;

BEGIN

FOR pcode IN SELECT * FROM PUBLISHER

LOOP

SELECT pcode.publishercode||': '|| uspGetPubStatsMEP (pcode.publishercode)

INTO pubstat;

RETURN NEXT pubstat;

END LOOP;

RETURN;

END;

$$ language plpgsql;

SELECT uspGetAllPubStatsMEP ();

but has been written to work in postgre SQL can someone help me tweak / edit it so that it works in MYSQL

please help