Stored procedure variable error in PLSQL when declaring variables

Question

Using Oracle 11g when creating the following stored procedure

    create or replace PROCEDURE sp_EqualVote(AREA IN NVARCHAR2, DATEOFVOTE IN DATE)
IS
  DECLARE test nvarchar(255);
  BEGIN
    SELECT
      AREA,
      DATEOFVOTE,
    CASE
      WHEN (REMAINVOTES = LEAVEVOTES) THEN REMAINVOTES
    END AS EqualVote
    INTO test
    FROM VOTING
    WHERE REMAINVOTES = LEAVEVOTES;
    END;
  END;

I encounter the following error, I'm not quite sure where to go

PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

I'm a university student and not really that familiar with PLSQL. The idea is the stored procedure should display if an an area has equal votes, given the area and date in the procedure then display an equalvotes labeled column with a value of 50

Littlefoot · Accepted Answer · 2019-03-10T21:11:17.867

Quite a few mistakes.

you don't need DECLARE within the named PL/SQL procedure
parameters names should differ from column names, so you'd rather use - for example - p_area in nvarchar2, p_dateofvote in date
if you select 3 columns, you have to put them INTO 3 variables - you've declared only one, so either declare two more, or remove AREA and DATEOFOTE from SELECT
what are those parameters used for? Usually, as a part of the WHERE clause - which is not the case in your code
pay attention to number of rows returned by the SELECT statement. If you're selecting into a scalar variable, make sure that it returns only one row
what will you do with TEST variable, once you get its value? Currently, nothing
you've got an END that is a surplus.

Therefore, consider something like this which should at least compile (depending on table description):

SQL> create table voting (area nvarchar2(10),
  2                       dateofvote date,
  3                       remainvotes nvarchar2(10),
  4                       leavevotes nvarchar2(10));

Table created.

SQL> create or replace procedure
  2    sp_equalvote(p_area in nvarchar2, p_dateofvote in date)
  3  is
  4    test nvarchar2(255);
  5  begin
  6    select
  7      case when remainvotes = leavevotes then remainvotes end
  8      into test
  9      from voting
 10      where remainvotes = leavevotes
 11        and area = p_area
 12        and dateofvote = p_dateofvote;
 13  end;
 14  /

Procedure created.

SQL>

[EDIT]

After reading the comment, perhaps you'd rather use a function.

Some sample values:

SQL> insert into voting values (1, date '2019-02-20', 100, 15);

1 row created.

SQL> insert into voting values (1, date '2019-03-10', 300, 300);

1 row created.

Function:

SQL> create or replace function
  2    sp_equalvote(p_area in nvarchar2, p_dateofvote in date)
  3  return nvarchar2
  4  is
  5    test nvarchar2(255);
  6  begin
  7    select
  8      case when remainvotes = leavevotes then 'draw'
  9           else 'not equal'
 10      end
 11    into test
 12    from voting
 13    where area = p_area
 14      and dateofvote = p_dateofvote;
 15
 16    return test;
 17  end;
 18  /

Function created.

SQL>

Testing:

SQL> select * From voting;

AREA       DATEOFVOTE REMAINVOTE LEAVEVOTES
---------- ---------- ---------- ----------
1          20.02.2019 100        15
1          10.03.2019 300        300

SQL> select sp_equalvote(1, date '2019-02-20') res from dual;

RES
--------------------
not equal

SQL> select sp_equalvote(1, date '2019-03-10') res from dual;

RES
--------------------
draw

SQL>

Thanks for your reply littlefoot, I apologise for being stupid, but I'm not quite sure 1. How to then call this procedure (it works btw and your table description is correct) 2. The idea is that it would display the results of the query as to show the columns or more usefully a single column saying draw as the two values are the same. Thanks again for you help — Luke Greenwood, Mar 10 '19 at 21:01
You're welcome and - no, you aren't stupid. I edited my message and added an example; have a look, please. — Littlefoot, Mar 10 '19 at 21:11

score 2 · Answer 2 · answered Mar 10 '19 at 20:28

DECLARE is not allowed in the body of a PL/SQL procedure. The IS or AS serves the purpose of delimiting where the variable declaration section starts - so your procedure should be

create or replace PROCEDURE sp_EqualVote(AREA IN NVARCHAR2, DATEOFVOTE IN DATE)
IS
  test nvarchar(255);
BEGIN
  SELECT
    AREA,
    DATEOFVOTE,
    CASE
      WHEN (REMAINVOTES = LEAVEVOTES) THEN REMAINVOTES
    END AS EqualVote
  INTO test
  FROM VOTING
  WHERE REMAINVOTES = LEAVEVOTES;
END;

You also had an extra END, which I removed.

Best of luck.

Stored procedure variable error in PLSQL when declaring variables

2 Answers2