SQL PLS-00103 Oracle 存储过程错误

PLS-00103 Oracle stored procedure error

提问by jon

I am new to stored procedures. I am trying to run stored procedure and getting these errors: I am getting PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function pragma procedure... PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: * & = - + < / > at in is mod remainder not rem then... I have tried searching for what causes these errors and for examples similar to this, but results were not sufficient. Any clues as to why these errors are happening? here is the code:

我是存储过程的新手。我正在尝试运行存储过程并收到这些错误：我收到 PLS-00103：在期望以下情况之一时遇到符号“SELECT”：开始函数 pragma procedure... PLS-00103：遇到符号“RETURN”时期待以下之一： * & = - + < /> at in 是 mod 余数不是 rem then... 关于为什么会发生这些错误的任何线索？这是代码：

CREATE OR REPLACE PROCEDURE LIST_ACTIONS_CHECK_ADD 
(
  LISTNAME IN VARCHAR2  
) AS 
BEGIN
 DECLARE CNT NUMBER;
 SELECT COUNT(LIST_NAME) INTO CNT FROM LISTS_MASTER WHERE LIST_NAME = LISTNAME;
IF (CNT > 0)
 RETURN 1
ELSE
 RETURN 0
END IF;
END LIST_ACTIONS_CHECK_ADD;

New Code:

新代码：

CREATE OR REPLACE PROCEDURE LIST_ACTIONS_CHECK_ADD 
(
  P_LISTNAME IN VARCHAR2  
) 
AS 
 L_CNT NUMBER;
BEGIN
 SELECT COUNT(LIST_NAME) 
   INTO L_CNT 
   FROM LISTS_MASTER 
  WHERE LIST_NAME = P_LISTNAME;
 IF (L_CNT > 0)
   RETURN 1;
 ELSE
   RETURN 0;
  END IF;
END LIST_ACTIONS_CHECK_ADD;

回答by Justin Cave

The skeleton of a stored procedure declaration is

存储过程声明的骨架是

CREATE OR REPLACE PROCEDURE procedure_name( <<parameters>> ) 
AS
  <<variable declarations>>
BEGIN
  <<code>>
END procedure_name;

In the code you posted,

在您发布的代码中，

1. You put the BEGINbefore the variable declarations
2. You have an extraneous DECLARE-- you would only use that if you are declaring a PL/SQL block that doesn't involve a CREATE.
3. You are missing semicolons after your RETURNstatements.
4. A procedure cannot return a value. If you want to return either a 1 or a 0, you probably want a function, not a procedure. If you need a procedure, you can declare an OUTparameter.
5. You are missing the THENafter the IF

1. 你把BEGIN变量声明放在前面
2. 你有一个无关紧要的DECLARE——你只会在你声明一个不涉及CREATE.
3. 您的语句后缺少分号RETURN。
4. 过程不能返回值。如果您想返回 1 或 0，您可能需要一个函数，而不是一个过程。如果你需要一个过程，你可以声明一个OUT参数。
5. 你错过了THEN之后IF

It sounds like you want something like

听起来你想要类似的东西

CREATE OR REPLACE FUNCTION LIST_ACTIONS_CHECK_ADD 
(
  LISTNAME IN VARCHAR2  
) 
  RETURN NUMBER
AS 
 CNT NUMBER;
BEGIN
 SELECT COUNT(LIST_NAME) 
   INTO CNT 
   FROM LISTS_MASTER 
  WHERE LIST_NAME = LISTNAME;
 IF (CNT > 0)
 THEN
   RETURN 1;
 ELSE
   RETURN 0;
  END IF;
END LIST_ACTIONS_CHECK_ADD;

Note that as a general matter, you are generally better off using some sort of naming convention to ensure that parameters and local variables do not share the name of a column. Trying to figure out whether LISTNAMEis a parameter or a column name and what the difference between LIST_NAMEand LISTNAMEis will generally confuse future programmers. Personally, I use a p_prefix for parameters and a l_prefix for local variables. I would also suggested using anchored types-- lists_master.list_name%typeif that is what is being passed in

请注意，一般而言，您通常最好使用某种命名约定来确保参数和局部变量不共享列的名称。试图找出是否LISTNAME是一个参数或列名和之间有什么区别LIST_NAME和LISTNAME是一般会迷惑未来的程序员。就个人而言，我p_对参数使用前缀，l_对局部变量使用前缀。我还建议使用锚定类型——lists_master.list_name%type如果这是传入的

CREATE OR REPLACE FUNCTION LIST_ACTIONS_CHECK_ADD 
(
  P_LIST_NAME IN lists_master.list_name%type
) 
  RETURN NUMBER
AS 
 L_CNT NUMBER;
BEGIN
 SELECT COUNT(LIST_NAME) 
   INTO L_CNT 
   FROM LISTS_MASTER 
  WHERE LIST_NAME = P_LIST_NAME;
 IF (L_CNT > 0)
 THEN
   RETURN 1;
 ELSE
   RETURN 0;
  END IF;
END LIST_ACTIONS_CHECK_ADD;

回答by A B

• (Correction #1) You cannot return a value in a procedure; LIST_ACTIONS_CHECK_ADD should be dropped and declared as a function in order to return a NUMBER
• (Correction #2) You need to move the declaration of CNT as follows (see below)
• (Correction #3) You need semicolons on the return statements:
• (Correction #4) You need a THEN after IF (CNT > 0) (see below):

• （更正 #1）您不能在过程中返回值；LIST_ACTIONS_CHECK_ADD 应该被删除并声明为一个函数以返回一个 NUMBER
• （更正＃2）您需要按如下方式移动 CNT 的声明（见下文）
• （更正 #3）您需要在 return 语句上使用分号：
• （更正 #4）在 IF (CNT > 0) 之后需要一个 THEN（见下文）：

DROP PROCEDURE LIST_ACTIONS_CHECK_ADD;
CREATE OR REPLACE FUNCTION LIST_ACTIONS_CHECK_ADD
(
   LISTNAME IN VARCHAR2  
) 

RETURN NUMBER AS 

  CNT NUMBER;
BEGIN
    SELECT COUNT(LIST_NAME) INTO CNT FROM LISTS_MASTER WHERE LIST_NAME = LISTNAME;
    IF (CNT > 0) THEN
       RETURN 1;
    ELSE
       RETURN 0;
    END IF;
END LIST_ACTIONS_CHECK_ADD;

---

---

This Can be executed from SQLPLUS as:

这可以从 SQLPLUS 执行为：

SET SERVEROUTPUT ON SIZE 100000;
DECLARE
    V_RESULT NUMBER;
BEGIN

    V_RESULT := LIST_ACTIONS_CHECK_ADD('X');
    DBMS_OUTPUT.PUT_LINE('RESULT: ' || V_RESULT);

END;