ORACLE-CASE requires INTO?

Question

When I try the following:

declare var_type VARCHAR2(10);

begin

  var_type := 'B';

  select case var_type 
           when 'B' then 'Beans' 
           when 'L' then 'Legumes' 
         end 
    from tableOfBeans ;

end;

I get an error

ORA-06550: row 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
An error was detected at position #: 62

But when I do not use var_type, but instead use "B" as a condition, which works well. Can someone tell me why this is happening and how to fix it so that I can use it correctly var_type?

Answer 1

SQL begin/end, PL-SQL-. PL-SQL Oracle SQL. ORA-06550. , PL-SQL , INTO.

SELECT . , , "tableOfBeans" FROM.

- ?

declare var_type VARCHAR2(10);

begin

    select case tableOfBeans.beanType
               when 'B' then 'Beans'
               when 'L' then 'Legumes'
           end
      into var_type
      from tableOfBeans;

end;

, , tableOfBeans . , tableOfBeans, tableOfBeans.beanType = var_type. . , , . .

Answer 2

CASE INTO.
ORA-6550 , SQL . , , INTO, , SELECT INTO, :

SELECT t.column
  INTO var_type
  FROM tableOfBeans

Oracle , , Oracle 9i/10g CASE, END CASE, END. END CASE Oracle .

DUAL , , ​​ :

var_type := 'B';

SELECT CASE var_type 
         WHEN 'B' then 'Beans' 
         WHEN 'L' then 'Legumes' 
       END CASE
  FROM DUAL;

Answer 3

, , .

declare 
    var_type VARCHAR2(10);
    var_into VARCHAR2(10);
begin
    var_type := 'B';

    select case var_type 
           when 'B' then 'Beans'
           when 'L' then 'Legumes'
    end
    into var_into
    from tableOfBeans;
end;

If your tableOfBeans table contains more than one row, you will encounter another error ORA-01422: exact fetch returns more than one row. Use the dual pseudo table instead of or after the pl / sql code.

declare 
    var_type VARCHAR2(10);
    var_into VARCHAR2(10);
begin
    var_type := 'B';

    var_into := case var_type 
           when 'B' then 'Beans'
           when 'L' then 'Legumes'
    end;
end;