Oracle操作总结

• Oracle操作总结
  • 一、Oracle表操作
    • 1.建表
    • 2.修改表名
    • 3.修改字段名
    • 4.修改数据类型
    • 5.增加字段
    • 6.删除字段
    • 7.多表关联更新
  • 二、sqlldr导入数据到Oracle库中
    • 1.sqlldr语句
    • 2.执行语句

参考文档：https://blog.csdn.net/dingguanyi/article/details/82259685

一、Oracle表操作

1.建表

-- Create table
create table DB.TABLENAME
(
  DATA_DATE    VARCHAR2(8 CHAR)
  COL1         VARCHAR2(100 CHAR) not null,
  COL2         VARCHAR2(200 CHAR)
)
partition by list (DATA_DATE)
(
  partition P_INIT values ('20000101')
    tablespace DB_DATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition P_20191013 values ('20191013')
    tablespace DB_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    )
);

-- Add comments to the table 
comment on table DB.TABLENAME is '表名';

-- Add comments to the columns 
comment on column DB.TABLENAME.COL1 is '字段1';
comment on column DB.TABLENAME.COL2 is '字段2';

-- Create/Recreate indexes 
create index IDX2_DB_TABLENAME on DB.TABLENAME (COL1)
  tablespace DB_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

2.修改表名

--将student修改成STUDENTS
ALTER TABLE STUDENT RENAME TO STUDENTS;

--将students修改成student
ALTER TABLE STUDENTS RENAME TO STUDENT;

3.修改字段名

--将字段name修改成names
ALTER TABLE STUDENT RENAME COLUMN NAME TO NAMES;

4.修改数据类型

--将字段sex修改成char类型
ALTER TABLE STUDENT MODIFY SEX CHAR(3);

5.增加字段

ALTER TABLE STUDENT ADD AGE NUMBER;

6.删除字段

ALTER TABLE STUDENT DROP COLUMN AGE;

7.多表关联更新

update t1 
   set t1.money = (select t2.money 
                     from t2 
                    where t2.name = t1.name
                   )
where exists (select 1 from t2 where t2.name = t1.name);

merge into t1
     using (select t2.name,t2.money from t2) t
        on (t.name = t1.name)
when matched then 
update set t1.money = t.money;

二、sqlldr导入数据到Oracle库中

1.sqlldr语句

文件名：/sqlldr_test/db.tablename.ctl

OPTIONS (skip=1,rows=20000)
load data     
CHARACTERSET AL32UTF8
infile      "/u01/data/db.tablename.csv"     
badfile     "/u01/data/db.tablename.bad"
discardfile "/u01/data/db.tablename.disc" 
TRUNCATE into table db.tablename
Fields terminated by ","
Optionally enclosed by '"'
TRAILING NULLCOLS 
(
 col1
,col2
,col3
)

代码说明：

2.执行语句

sqlldr userid=username/password control=/sqlldr_test/db.tablename.ctl log=/sqlldr_test/db.tablename.ctl.log