Oracle操作总结

Oracle操作总结

目录

参考文档：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;

8.指定逗号分割字符串，并行转列

select regexp_replace('aaa,bbb,ccc','[^,]+',1,level)
from dual
connect by level <= length('aaa,bbb,ccc') - length(replace('aaa,bbb,ccc',','))+1
;

9.指定符号分割字符串

--指定@符号将字符串分割成两部分，获取第一部分
select substr('aaa@bbb',instr('aaa@bbb','@')+1,length('aaa@bbb')) from dual;
--指定@符号将字符串分割成两部分，获取第二部分
select substr('aaa@bbb',1,instr('aaa@bbb','@')-1) from dual;

二、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
)

代码说明：

代码 说明
OPTIONS (skip=1,rows=10000) sqlldr 的内容可以写在cotrol文件 load_data的前面，此处跳过前1行,每次提交10000行
load data 加载数据
CHARACTERSET AL32UTF8 字符集编码（如果出现乱码要考虑一下）
infile 加载的文件，* 表示本文件
badfile 错误的数据所放的文件（校验错误）
discardfile 丢弃的数据放的路径（记录的格式错误或过滤行）
TRUNCATE into table db.tablename 先TRUNCATE db.tablename再将记录插入表
Fields terminated by "," 多个字段间用“,”隔开
Optionally enclosed by '"' 单个字段用“"”开始，“"”开始结束
TRAILING NULLCOLS 对于值为空的字段允许为空

2.执行语句

sqlldr userid=username/password control=/sqlldr_test/db.tablename.ctl log=/sqlldr_test/db.tablename.ctl.log
posted @ 2023-04-07 16:34  咕噜噜~  阅读(46)  评论(0)  编辑  收藏  举报