spool导出及sqllder导入。

来源：互联网发布：the windows live ID 编辑：程序博客网时间：2024/06/08 11:03

#!/bin/bashsqlplus -s $DB_NAME  <<EXPset pages 50000;set lines 200;    set trims on;set heading off;set feedback offspool "lwc_test.csv";select test_id||','||test_name||','||test_sex from lwc_test; spool off;EXPecho 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/bashecho ----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'exitelseecho '导入imp表successfully.'fiecho ----import successfully----

select * from lwc_testcreate 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_ltincrement by 1start with 1minvalue 1 maxvalue 900NOCYCLE;

2.创建触发器

create trigger trg_lt before insert on lwc_testfor each rowbeginselect 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','')。