Introduction to the use of Oracle case functions _oracle

1. Create a Test table:

Copy Code code as follows:

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. View the corresponding data

Copy Code code as follows:

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 statements

Copy Code code as follows:

Update students
Set grade = (
Select grade from
(
Select ID,
Case when Current_credits > Then ' a '
When current_credits > then ' B '
When current_credits > Then ' C '
Else ' d ' End grade
From students
) A
where a.id = Students.id
)
/

4. Post-Update results

Copy Code code as follows:

Sql> SELECT * from students;

ID first_name last_name MAJOR current_credits GR
---------- -------------------- -------------------- ------------------------------ --------------- --
10000 Scott Smith Computer Science
10001 Margaret Mason History b
10002 Joanne Junebug Computer Science C
10003 Manish murgratroid Economics D