如何使用Oracle case函数

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

通过实例简要介绍case函数的用法。

1.创建测试表:

DROP SEQUENCE student_sequence;

CREATE SEQUENCE student_sequenceSTART WITH 10000INCREMENT BY 1;

DROP TABLE students;

CREATE TABLE students (

id NUMBER(5) PRIMARY KEY,

first_name VARCHAR2(20),

last_nameVARCHAR2(20),

majorVARCHAR2(30),

current_creditsNUMBER(3),

gradevarchar2(2));

INSERT INTO students (id, first_name, last_name, major, current_credits,grade)

VALUES (student_sequence.NEXTVAL, 'Scott', 'Smith', 'Computer Science', 98,null);

INSERT INTO students (id, first_name, last_name, major, current_credits,grade)

VALUES (student_sequence.NEXTVAL, 'Margaret', 'Mason', 'History', 88,null);

INSERT INTO students (id, first_name, last_name, major, current_credits,grade)

VALUES (student_sequence.NEXTVAL, 'Joanne', 'Junebug', 'Computer Science', 75,null);

INSERT INTO students (id, first_name, last_name, major, current_credits,grade)

VALUES (student_sequence.NEXTVAL, 'Manish', 'Murgratroid', 'Economics', 66,null);

commit;

2.查看相应数据

SQL> select * from students;

ID FIRST_NAMELAST_NAMEMAJOR CURRENT_CREDITS GR

------ ----------------- ------------------- ----------- ----------------- -----

10000ScottSmithComputer Science 98

10001Margaret MasonHistory88

10002Joanne JunebugComputer Science 75

10003ManishMurgratroid Economics66

3.更新语句

update students

set grade = (

select grade from

(

select id,

case when current_credits > 90 then 'a'

when current_credits > 80 then 'b'

when current_credits > 70 then 'c'

else 'd' end grade

from students

) a

where a.id = students.id

)

/

4.更新后结果

SQL> select * from students;

ID FIRST_NAME LAST_NAMEMAJOR CURRENT_CREDITS GR

----------------------------- -------------------- ---------------------------- ----

10000 Scott SmithComputer Science98 a

10001 MargaretMasonHistory 88 b

10002 JoanneJunebugComputer Science75 c

10003 ManishMurgratroidEconomics 66 d

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航