熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何使用Oracle case函數

2013-11-13 15:32:44  來源: Oracle 

  通過實例簡要介紹case函數的用法
  
  創建測試表:
  
  DROP SEQUENCE student_sequence;
  CREATE SEQUENCE student_sequence START WITH  INCREMENT BY ;
  
  DROP TABLE students;
  CREATE TABLE students (
  id        NUMBER() PRIMARY KEY
  first_name    VARCHAR()
  last_name    VARCHAR()
  major      VARCHAR()
  current_credits NUMBER()
  grade      varchar());
  
  INSERT INTO students (id first_name last_name major current_creditsgrade)
  VALUES (student_sequenceNEXTVAL Scott Smith Computer Science null);
  
  INSERT INTO students (id first_name last_name major current_creditsgrade)
  VALUES (student_sequenceNEXTVAL Margaret Mason History null);
  
  INSERT INTO students (id first_name last_name major current_creditsgrade)
  VALUES (student_sequenceNEXTVAL Joanne Junebug Computer Science null);
  
  INSERT INTO students (id first_name last_name major current_creditsgrade)
  VALUES (student_sequenceNEXTVAL Manish Murgratroid Economics null);
  
  commit;
  
  查看相應數據
  
  SQL> select * from students;
  
  ID    FIRST_NAME    LAST_NAME      MAJOR    CURRENT_CREDITS  GR
   
    Scott        Smith       Computer Science     
    Margaret       Mason       History          
    Joanne        Junebug      Computer Science     
    Manish       Murgratroid     Economics         
  
  更新語句
  
  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 aid = studentsid
  )
  /
  
  更新後結果
  
  SQL> select * from students;
  
  ID FIRST_NAME  LAST_NAME      MAJOR         CURRENT_CREDITS GR
   
   Scott    Smith      Computer Science               a
   Margaret  Mason      History                   b
   Joanne   Junebug     Computer Science               c
   Manish   Murgratroid   Economics                  d
From:http://tw.wingwit.com/Article/program/Oracle/201311/16889.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.