PLS-00103: 遇到了符号 "DECLARE"

24 浏览
0 Comments

PLS-00103: 遇到了符号 "DECLARE"

我正在尝试执行以下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 errors 后才能看到）：

       PLS-00103: 遇到符号 "DECLARE"

这个 DECLARE 有什么问题？

0

1 答案

0 Comments

PLS-00103错误是因为在存储过程的创建和运行存储过程的匿名块之间缺少了一个斜杠（/）。

解决方法是在存储过程的创建和匿名块之间添加斜杠（/），示例如下：

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 errors"命令应该在创建存储过程后立即运行，INTO子句应该指定OUT参数。

0