oracle笔记一（sql语句方面）-10

Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移动分区的语法
alter table tablename move (partition partname)
[update global indexes]
之后之后必须重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，可以采用如下的方法移动Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts);

54.怎么样修改表的列名
[A]9i以上版本可以采用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以采用create table …… as select * from SourceTable的方式。
另外，8i以上可以支持删除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS

55.case的用法
在sql语句中
CASE test_value
WHEN expression1 THEN value1
[[WHEN expression2 THEN value2] [...]]
[ELSE default_value]
END

比如1
SELECT last_name, job_id, salary
      CASE job_id
           WHEN 'IT_PROG' THEN 1.10*salary
           WHEN 'ST_CLERK' THEN 1.15*salary
           WHEN 'SA_REP' THEN 1.20*salary
     ELSE salary END "REVISED_SALARY"
FROM employees 

比如2
select
    case
        when  real_charge>=20000 and real_charge<30000 then 5000
        when  real_charge>=30000 and real_charge<40000 then 9000
        when  real_charge>=40000 and real_charge<50000 then 10000
        when  real_charge>=50000 and real_charge<60000 then 14000
        when  real_charge>=60000 and real_charge<70000 then 18000
        when  real_charge>=70000 and real_charge<80000 then 19000
        when  real_charge>=80000 and real_charge<90000 then 24000
        when  real_charge>=90000 and real_charge<100000 then 27000                                                       
        when  real_charge>=100000 and real_charge<110000 then 27000
        when  real_charge>=110000 and real_charge<120000 then 29000              
        when  real_charge>=120000                      then 36000

        else
            0 
    end ,acc_id,user_id,real_charge from okcai_jh_charge_200505

在存储过程中
               case v_strGroupClassCode
                    when  '1'   then
                          v_nAttrNum := v_nAttrNum + 300;
                          v_strAttrFlag := '1'substr(v_strAttrFlag,2,7);
                    when  '2'           then
                          v_nAttrNum := v_nAttrNum + 200;
                          v_strAttrFlag := '2'substr(v_strAttrFlag,2,7);
                    else
                        NULL;
               end case;
注意的是存储过程和sql语句有的细微差别是用end case，而不是end。语句后面跟";"