case语句,求助高手

wildcatqfj

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

zhouwf0726

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

wildcatqfj

最初由 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

zhouwf0726

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;
/

wildcatqfj

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

淡若煙飛

最初由 wildcatqfj 发布
[B]几个when分支执行相同操作,怎么合并到一块
比如:
case  selector
when a then A;
when b then A;
when c then B;
when d then B;
when e then C
end case; [/B]

使用DECODE函数也许会更好一些!

wildcatqfj

最初由 淡若煙飛 发布
[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;

zhycjun

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

觉得很好

淡若煙飛

最初由 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;

这句是不是有些问题?
能把你的函数代码贴出来看看么?