CODEWITHSUNDEEP
oracle-databaseplsql
user8029928
user8029928

Reputation: 27

CASE in PL/SQL is controlled by logical expression

I'm new to PL/SQL. Trying to create a simple code which assigns grade according to range in which marks lie. my code is as follows: 

SET SERVEROUTPUT ON ;
ACCEPT N PROMPT 'ENTER NAME: ' ;
ACCEPT M PROMPT 'ENTER MARKS: ' ;
DECLARE
X CHAR ;
BEGIN
CASE TRUE
WHEN (M BETWEEN 0 AND 40) THEN X := 'E' ;
WHEN ((M>40)AND (M<=50)) THEN X := 'D' ;
WHEN ((M>50)AND (M<=60)) THEN X := 'C' ;
WHEN ((M>60)AND (M<=80)) THEN X := 'B' ;
WHEN ((M>80)AND (M<=100)) THEN X := 'A' ;
ELSE X := 'Z' ;
END CASE ;
IF(X = Z) THEN
DBMS_OUTPUT.PUT_LINE('ERROR!!!! MARKS NOT IN RANGE');
ELSE
DBMS_OUTPUT.PUT_LINE(N || ' HAVING GRADE ' || X);
END IF;
END;
/

i keep getting the following errors when I try to run it:

ENTER NAME: D
ENTER MARKS: 300
WHEN (M BETWEEN 0 AND 40) THEN X := 'E' ;
      *
ERROR at line 5:
ORA-06550: line 5, column 7:
PLS-00201: identifier 'M' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 8:
PLS-00306: wrong number or types of arguments in call to 'Z'
ORA-06550: line 12, column 1:
PL/SQL: Statement ignored

where am i going wrong? I am entering the value of M using prompt, why isn't it being used?

Upvotes: 0

Views: 76

Answers (2)

APC
APC

Reputation: 146239

There is nothing syntactically wrong with your CASE statement. You get errors because you need to reference the inputs as substitution variables. ACCEPT is a SQL*Plus plus command for acquiring values from a user. In the code itself a substitution variable must be prefixed with an ampersand. When we run the code the SQL*Plus client will inject the input value at each instance of the substitution variable.

Two things to note:

  1. substitution variables are literals. So we must enclose a string variable with single quotes.
  2. by default SQL*Plus will echo each substitution, which is quite annoying when a variable is referenced as often as &M is in your code. Suppress this behaviour with the SQL*Plus command set verify off.

Here is your code with the substitution variables fixed.

DECLARE
    X CHAR ;
BEGIN
    CASE TRUE
        WHEN (&M BETWEEN 0 AND 40) THEN X := 'E' ;
        WHEN ((&M>40)AND (&M<=50)) THEN X := 'D' ;
        WHEN ((&M>50)AND (&M<=60)) THEN X := 'C' ;
        WHEN ((&M>60)AND (&M<=80)) THEN X := 'B' ;
        WHEN ((&M>80)AND (&M<=100)) THEN X := 'A' ;
    ELSE 
         X := 'Z' ;
    END CASE ;
    IF(X = 'Z') THEN
        DBMS_OUTPUT.PUT_LINE('ERROR!!!! MARKS NOT IN RANGE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('&N' || ' HAVING GRADE ' || X);
    END IF;
END;
/

Upvotes: 1

MT0
MT0

Reputation: 168081

ACCEPT reads a line of input and stores it in a substitution variable. If you want to use a substitution variable then use &variable_name. You also, can't assign a variable in a CASE statement:

SET SERVEROUTPUT ON ;
ACCEPT N CHAR   PROMPT 'ENTER NAME: ';
ACCEPT M NUMBER PROMPT 'ENTER MARKS: ';

DECLARE
  X CHAR(1) :=  CASE
                  WHEN &M BETWEEN  0 AND  40 THEN 'E'
                  WHEN &M BETWEEN 40 AND  50 THEN 'D'
                  WHEN &M BETWEEN 50 AND  60 THEN 'C'
                  WHEN &M BETWEEN 60 AND  80 THEN 'B'
                  WHEN &M BETWEEN 80 AND 100 THEN 'A'
                                             ELSE 'Z'
                END;
BEGIN
  IF X = 'Z' THEN
    DBMS_OUTPUT.PUT_LINE('ERROR!!!! MARKS NOT IN RANGE');
  ELSE
    DBMS_OUTPUT.PUT_LINE( '&N HAVING GRADE ' || X);
  END IF;
END;
/

Upvotes: 0

Related Questions