为什么此PL/SQL不起作用? [英] Why this PL/SQL doesn't work?

问题描述

我正在尝试学习PL/SQL.这段代码令我震惊.请通知我我哪里出错了.我在命令行中使用Oracle 10g.

Im trying to learn PL/SQL. I am struck with this code. Please notify me where im going wrong. I use Oracle 10g in a command line .

declare
grade char(1):='&v_grade';
app varchar(15);
begin
app:=case v_grade
when 'a' then
dbms_output.put_line('Excellent');
when 'b' then
dbms_output.put_line('Good');
else
dbms_output.put_line('Bad');
end case;
dbms_output.put_line('Grade'||grade||' Appraisal:'||app);
end;
/

它显示

Enter value for v_grade: a
old   2: grade char(1):='&v_grade';
new   2: grade char(1):='a';
dbms_output.put_line('Excellent');
                                 *
ERROR at line 7:
ORA-06550: line 7, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * % & = - + < / > at else end in is mod remainder not rem
when <an exponent (**)> <> or != or ~= >= <= <> and or like
LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol ";" was ignored.
ORA-06550: line 9, column 29:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * % & = - + < / > at else end in is mod remainder not rem
when <an exponent (**)> <> or != or ~= >= <= <> and or like
LIKE2_ LIKE4_ LIKEC_ between ||
ORA-06550: line 11, column 28:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * % & = - + < / > at end in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset

请通知我我要去哪里了.

please notify me where i am going wrong.

推荐答案

T.J. Crowder是正确的，在SQL中的case语句中不应使用分号，但这是PL/SQL版本，因此稍有不同.

T.J. Crowder is sort of right, you shouldn't have a semicolon within a case statement in SQL, but this is the PL/SQL version so it's slightly different.

您当前正在尝试将dbms_output.put_line过程(非函数)调用的(不存在)返回值分配给您的app变量.

You are currently trying to assign the (non-existent) return from the dbms_output.put_line procedure (not function) call to your app variable.

为使分配正常工作，您需要case求值为字符串，因此您只能使用文本文字；但是分配必须在每个分支中:

For the assignment to work you need the case to evaluate to a string, so you can just use text literals; but the assignment needs to be in each branch:

declare
  grade char(1):='&v_grade';
  app varchar(15);
begin
  case grade
    when 'a' then app:= 'Excellent';
    when 'b' then app:= 'Good';
    else app := 'Bad';
  end case;

  dbms_output.put_line('Grade '||grade||' Appraisal: '||app);
end;
/

运行何时获得:

Enter value for v_grade: a
Grade b Appraisal: Excellent

PL/SQL procedure successfully completed.

或使用查询进行分配，尽管效率不高:

Or use a query to do the assignment, though this is not quite as efficient:

declare
  grade char(1):='&v_grade';
  app varchar(15);
begin
  select case grade
    when 'a' then 'Excellent'
    when 'b' then 'Good'
    else 'Bad'
  end case
  into app
  from dual;
  dbms_output.put_line('Grade '||grade||' Appraisal: '||app);
end;
/

Enter value for v_grade: b
Grade b Appraisal: Good

PL/SQL procedure successfully completed.

或者您可以直接在每个分支中进行输出:

Or you can do the output directly in each branch:

declare
  grade char(1):='&v_grade';
begin
  dbms_output.put('Grade '||grade||' Appraisal: ');
  case grade
    when 'a' then
      dbms_output.put_line('Excellent');
    when 'b' then
      dbms_output.put_line('Good');
    else
       dbms_output.put_line('Bad');
  end case;
end;
/

Enter value for v_grade: c
Grade c Appraisal: Bad

PL/SQL procedure successfully completed.

输出的第一部分是现在的情况.

The first part of your output is before the case now.

您基本上是在混淆各种方法.

You're basically mixing up the different approaches.

您可能希望将第一行更改为:

You might want to change the first line to:

  grade char(1):= upper('&v_grade');

...然后使大小写查找A，B，C而不是a，b，c-那么输入的大小写无关紧要.

... and then make the case look for A,B,C instead of a,b,c - then it won't matter what case the input is in.

您可以了解有关PL/SQL case状态的更多信息此处.

You can read more about the PL/SQL case statemnt here and here.

这篇关于为什么此PL/SQL不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！