当前位置： 移动技术网 > IT编程>数据库>Oracle > Oracle case函数使用介绍

Oracle case函数使用介绍

2017年12月12日  | 移动技术网IT编程  | 我要评论

相声大兵,本山师徒秀,移动iphone5合约机

1.创建测试表:

复制代码 代码如下:

drop sequence student_sequence;
create sequence student_sequence  start with 10000  increment by 1;

drop table students;
create table students (
  id               number(5) primary key,
  first_name       varchar2(20),
  last_name        varchar2(20),
  major            varchar2(30),
  current_credits  number(3),
  grade     varchar2(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_name           last_name            major                          current_credits gr
---------- -------------------- -------------------- ------------------------------ --------------- --
     10000 scott                smith                computer science                            98
     10001 margaret             mason                history                                     88
     10002 joanne               junebug              computer science                            75
     10003 manish               murgratroid          economics                                   66

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_name            major                          current_credits gr
---------- -------------------- -------------------- ------------------------------ --------------- --
     10000 scott                smith                computer science                            98 a
     10001 margaret             mason                history                                     88 b
     10002 joanne               junebug              computer science                            75 c
     10003 manish               murgratroid          economics                                   66 d

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网