oracle的case函数

oracle的case函数

下面对ORACLE的CASE函数作一些说明性的事例,当然为了省事,从网上找的事例,再作的功能说明:

create sequence STUDENT_SEQUENCE;

CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
stu_name VARCHAR2(20),
score NUMBER(10,0),
grade varchar2(2));

INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Scott', 98,null);

INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Margaret', 88,null);

INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Joanne', 75,null);

INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Manish', 66,null);

在作update时作CASE WHEN的多条件判断

update students
set grade =
case when score > 90 then 'A'
when score > 80 then 'B'
when score > 70 then 'C'
else 'D' end

 

即此时的CASE WHEN语句变为了

Case when  条件1 THEN 赋值1

         when  条件2 THEN 赋值2

         when  条件3 THEN 赋值3

ELSE 赋值4

END

 

oracle的case结构

此种CASE WHEN是与上面的有所不同的

begin
case when 2<1 then
dbms_output.put_line('Y');
else
dbms_output.put_line('N');
end case;
end;