PLS-00103 Oracle存储过程错误 [英] PLS-00103 Oracle stored procedure error

问题描述

我是存储过程的新手. 我正在尝试运行存储过程并得到以下错误: 我正在获取PLS-00103:遇到以下情况之一时遇到符号"SELECT":begin function pragma procedure.... PLS-00103:在预期以下情况之一时遇到符号"RETURN":*& =-+< />在in是mod余数而不是rem然后... 我曾尝试寻找导致这些错误的原因以及与此类似的示例，但是结果还不够.关于为什么会发生这些错误的任何线索? 这是代码:

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:

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;

新代码:

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;

推荐答案

• (更正#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;

---

这可以从SQLPLUS中以以下方式执行:

---

This Can be executed from SQLPLUS as:

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;    

这篇关于PLS-00103 Oracle存储过程错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！