过程中的case

chunming · 发表于 2003-7-22 08:57

版本问题？？？
SQL> declare v number;
  2  begin
  3  select sum(case when age=1 then 1 else 3 end) into v from table_main;
  4  dbms_output.put_line(v);
  5  end;
  6  /
select sum(case when age=1 then 1 else 3 end) into v from table_main;
         *
ERROR at line 3:
ORA-06550: line 3, column 12:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current distinct max min prior sql stddev sum unique
variance cast
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

Lodgy · 发表于 2003-7-22 08:58

9i 环境下的测试结果:

SQL> create table table_main (age float);

表已创建。

SQL> insert into table_main values(2);

已创建 1 行。

SQL>  select
  2 sum(case when age between 10 and 19 then 1 else 0 end) a1,
  3 sum(case when age between 20 and 29 then 1 else 0 end) a2,
  4 sum(case when age between 30 and 39 then 1 else 0 end) a3,
  5 sum(case when age between 40 and 49 then 1 else 0 end) a4,
  6 sum(case when age between 50 and 59 then 1 else 0 end) a5,
  7 sum(case when age between 60 and 69 then 1 else 0 end) a6,
  8 sum(case when age between 70 and 79 then 1 else 0 end) a7,
  9 sum(case when age between 80 and 89 then 1 else 0 end) a8
10 from table_main;

      A1       A2       A3       A4       A5       A6       A7
---------- ---------- ---------- ---------- ---------- ---------- ----------
      A8
----------
      0       0       0       0       0       0       0
      0