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;