case语句,求助高手

显示全部楼层 · 2011-10-19 18:13:09

几个when分支执行相同操作,怎么合并到一块
比如:
caseselector
when a then A;
when b then A;
when c then B;
when d then B;
when e then C
end case;

千问 · 2011-10-19 18:13:09

case selector
when a or b then A;
when c or d then B;
when e then C
end case;

千问 · 2011-10-19 18:13:09

最初由 zhouwf0726 发布
[B]case selector
when a or b then A;
when c or d then B;
when e then C
end case; [/B]

以前试过,不好用
PLS-00382 expression is of wrong type

千问 · 2011-10-19 18:13:09

set serverout on
create table test (id varchar2(10));
insert into test values('1');
insert into test values('2');
insert into test values('3');
insert into test values('4');
insert into test values('5');
commit;
declare
cursor cur_test is select id from test;
begin

for row_cur in cur_test loop

case when row_cur.id='1' or row_cur.id='2' then

dbms_output.put_line('A');

when row_cur.id='3' or row_cur.id='4' then

dbms_output.put_line('B');

else

dbms_output.put_line('C');

end case;

end loop;
end;
/

declare
cursor cur_test is select id from test;
begin

for row_cur in cur_test loop

dbms_output.put_line(

case when row_cur.id='1' or row_cur.id='2' then

'A'

when row_cur.id='3' or row_cur.id='4' then

'B'

else

'C'

end);

end loop;
end;
/

declare
cursor cur_test is

select case

when id='1' or id='2' then 'A'

when id='3' or id='4' then 'B'

else 'C' end id
from test;
begin

for row_cur in cur_test loop

dbms_output.put_line(row_cur.id);

end loop;
end;
/

千问 · 2011-10-19 18:13:09

四楼,多谢~~~~~~~~

千问 · 2011-10-19 18:13:09

最初由 wildcatqfj 发布
[B]几个when分支执行相同操作,怎么合并到一块
比如:
caseselector
when a then A;
when b then A;
when c then B;
when d then B;
when e then C
end case; [/B]
使用DECODE函数也许会更好一些!

千问 · 2011-10-19 18:13:09

最初由淡若煙飛发布
[B]
使用DECODE函数也许会更好一些! [/B]

不行啊
Compilation errors for FUNCTION MSS.TEST
Error: PLS-00204: 函数或伪列 'DECODE' 只能在 SQL 语句中使用

PLS-00204 function or pseudo-column 'string' may be used inside a SQL statement only
Cause: A pseudocolumn or proscribed function was used in a procedural statement. The SQL pseudocolumns (CURRVAL, LEVEL, NEXTVAL, ROWID, ROWNUM) can be used only in SQL statements. Likewise, certain functions such as DECODE, DUMP, and VSIZE and the SQL group functions (AVG, MIN, MAX, COUNT, SUM, STDDEV, VARIANCE) can be used only in SQL statements.
Action: Remove the pseudocolumn reference or function call from the procedural statement. Or, replace the procedural statement with a SELECT INTO statement; for example, replace:
bonus := DECODE(rating, 1, 5000, 2, 2500, ...);

with the following statement:
SELECT DECODE(rating, 1, 5000, 2, 2500, ...) INTO bonus FROM dual;

千问 · 2011-10-19 18:13:09

最初由 wildcatqfj 发布
[B]四楼,多谢~~~~~~~~ [/B]
觉得很好

千问 · 2011-10-19 18:13:09

最初由 wildcatqfj 发布
[B]

不行啊
Compilation errors for FUNCTION MSS.TEST
Error: PLS-00204: 函数或伪列 'DECODE' 只能在 SQL 语句中使用

PLS-00204 function or pseudo-column 'string' may be used inside a SQL statement only
Cause: A pseudocolumn or proscribed function was used in a procedural statement. The SQL pseudocolumns (CURRVAL, LEVEL, NEXTVAL, ROWID, ROWNUM) can be used only in SQL statements. Likewise, certain functions such as DECODE, DUMP, and VSIZE and the SQL group functions (AVG, MIN, MAX, COUNT, SUM, STDDEV, VARIANCE) can be used only in SQL statements.
Action: Remove the pseudocolumn reference or function call from the procedural statement. Or, replace the procedural statement with a SELECT INTO statement; for example, replace:
bonus := DECODE(rating, 1, 5000, 2, 2500, ...);

with the following statement:
SELECT DECODE(rating, 1, 5000, 2, 2500, ...) INTO bonus FROM dual; [/B]
SELECT DECODE(rating, 1, 5000, 2, 2500, ...) INTO bonus FROM dual;
这句是不是有些问题?
能把你的函数代码贴出来看看么?