我正在編寫一個存儲過程，如下所示

我得到的錯誤是在第3行和第4行，這兩個錯誤是第3行是“意外的'發(fā)布者'標識符”，第4行是“意外的AS(as)”，但我不知道我是怎么得到這些錯誤或如何解決的他們。 有人可以解釋一下為什么我收到這些錯誤以及可能的解決方法嗎

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

總結(jié)

以上是生活随笔為你收集整理的mysql存储过程中as_mysql - 存储过程mySQL语法错误意外“ AS” - 堆栈内存溢出的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。