oracle使用总结

1、创建表：

　　CREATE TABLE Student(
　　　　id varchar2(32) primary key,
　　　　name varchar2(8) not null,
　　　　age number
　　);
2、修改字段名：
　　alter table Student rename name to StuName;
3、修改数据类型：
　　alter table Student modify (id varchar2(64));

alter table tableName rename column oldCName to newCName; -- 修改字段名
alter table tableName modify (cloumnName 数据类型); -- 修改数据类型

添加一列：

alter table carapplication add (pay NUMBER(8,2));

comment on column carapplication.pay is '花费';

commit;

创建序列：

 CREATE SEQUENCE  "SYSTEM"."S_CAR" 

 MINVALUE 1 MAXVALUE 9999999999

 INCREMENT BY 1 START WITH 121 CACHE 20 NOORDER  NOCYCLE ;

分页：

 select * from   (   select A.*, rownum rn   from (select    
 cc.id as id,cc.carNumber as carNumber,cc.brand as brand,cc.purchaseDate as purchaseDate,  
 cc.state as state,cc.orgId as orgId,cc.orgCode as orgCode,cc.phone as phone,org.orgName as orgName   
 from car cc   left join organizations org   on cc.orgid=org.id      WHERE                                                         
 cc.orgCode like '.'                                

 ) A   where rownum <= 20   )   where rn >= 1  

ibatis里面写法:()

批量更新：

<update id="updateCarsStatusByIds" parameterClass="java.util.HashMap">
    update car set state=#state#
    where carnumber in
    <iterate property="carNumbers" close=")" open="("
             conjunction=",">
        #carNumbers[]#
    </iterate>
</update>

批量插入：

<insert id="insertMultiCars" parameterClass="java.util.List">
    insert into car(
    id,
    carnumber,
    brand,
    purchasedate,
    state,
    orgid,
    orgcode,
    phone,
    createuser,
    createdate
    )
    select s_car.NEXTVAL, t.* from ( /* // 使用序列生成主键*/
    <iterate conjunction=" union all " > /*// conjunction属性表示每次循环结束添加的字符*/
        select
        #test[].carNumber#,#test[].brand.id#,#test[].purchaseDate#,#test[].state#,#test[].organization.id#,
        #test[].organization.orgInternalCode#,#test[].phone#,#test[].createUser#,sysdate
        from dual
    </iterate>
    ) t
</insert>

//查询

SELECT s_carrecord.NEXTVAL as id from dual   //生成下一个序列