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

Answer 1

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>

Answer 2

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.