#!/bin/bash
sqlplus -s $DB_NAME <<EXP
set pages 50000;
set lines 200;
set trims on;
set heading off;
set feedback off
spool "lwc_test.csv";
select test_id||','||test_name||','||test_sex from lwc_test;
spool off;
EXP
echo export successfully!
echo -e "load data\ninfile lwc_test.csv\ninto table lwc_test\nFields terminated by ','\n
(TEST_ID,\nTEST_NAME,\nTEST_SEX)" > "lwc_test.ctl"
#!/bin/bash
echo ----begin to insert into imp table----
sqlldr $DB_NAME control="lwc_test.ctl" log="lwc_test.log" bad="bad.log"
if [ -f "bad.log" ]; then
echo '导入imp有错误参考文件bad.lg'
exit
else
echo '导入imp表successfully.'
fi
echo ----import successfully----
select * from lwc_test
create table LWC_TEST
(
TEST_ID NUMBER(5),
TEST_NAME VARCHAR2(10),
TEST_SEX VARCHAR2(20)
);
alter table lwc_test add (
beizhu varchar2(100) default 'beizhu',
constraint lt_pk primary key (test_id),
constraint lt_unuque unique (beizhu)
);
commit;
create index lt_idx on lwc_test (test_name);
insert into lwc_test values(1001,'liwenchao','','')
insert into lwc_test values(1002,'xuetuo','','')
insert into lwc_test values(1003,'yinpinyong','','')
update lwc_test lt set lt.test_sex ='male';
drop index lt_idx;
对ID列序列进行自增:
1.创建序列:
create sequence seq_lt
increment by 1
start with 1
minvalue 1
maxvalue 900
NOCYCLE;
2.创建触发器
create trigger trg_lt before insert on lwc_test
for each row
begin
select seq_lt.nextval into :new.TEST_ID from dual;
end;
insert into lwc_test lt values(55455,'liwenchao','male','')
//即可完成。注:(1.只对插入时的数据有效,对已存在的,不合理的数据不做约束。2.对手动,非insert操作的数据不做约束)。
或者插入时直接关联序列项,不去创建触发器如:
insert into lwc_test lt values(seq_lt.nextval,'xuetuo','male','')。