PLS-00103:遇到符号“声明”

Question

我正在尝试执行或运行以下pl/sql脚本：

SET serveroutput on;

CREATE OR REPLACE PROCEDURE findAvg
(p_category IN products.category_id% TYPE, c OUT NUMBER)
AS
BEGIN
SELECT NVL ((SELECT AVG(LIST_PRICE) FROM products
WHERE p_category = category_id), -1) into p_category
from dual;
END findAvg;
DECLARE
 cat  products.category_id%TYPE;
 Price   products.List_price%TYPE;
BEGIN
cat := &p_category;
findAvg (cat, price); 
if (price = -1) then
    dbms_output.put_line('Wrong Category ');
ELSE
    dbms_output.put_line('the average price for category' || cat || ' is ' || price);
 END IF;
END;
/
show errors

但是当我尝试运行它时，我得到了这个错误消息(我只能在show error之后看到它)：

       PLS-00103: Encountered the symbol "DECLARE" 

这个声明有什么问题？

Answer 1

在创建过程和运行该过程的匿名块的开头之间缺少一个"/“：

SET serveroutput on;

CREATE OR REPLACE PROCEDURE findAvg
(p_category IN products.category_id% TYPE, c OUT NUMBER)
AS
BEGIN
  SELECT NVL(AVG(LIST_PRICE),-1)
  INTO c
  FROM products
  WHERE p_category = category_id;
END findAvg;
/

show errors

DECLARE
 cat  products.category_id%TYPE;
 Price   products.List_price%TYPE;
BEGIN
cat := &p_category;
findAvg (cat, price); 
if (price = -1) then
    dbms_output.put_line('Wrong Category ');
ELSE
    dbms_output.put_line('the average price for category' || cat || ' is ' || price);
 END IF;
END;
/

同样，"show error“命令应该在创建过程之后立即运行，就像我上面所做的那样，INTO子句应该指定OUT参数。