命令:
1、导入dmp文件
impdp TDDCYWK/TDDCYWK@south130 directory=DATA_PUMP_DIR dumpfile=HS_PRJGIS.dmp REMAP_SCHEMA=HS_PRJGIS:TDDCYWK
REMAP_SCHEMA=A:B 表示从A方案到B方案
SQL:
1、查询一张树状结构的表某条记录下的所有节点
select A from B where C = ‘D’
start with Id = ‘1’ connect by PreId = prior Id
详见:http://blog.csdn.net/loveyou388i/article/details/78676666
2、插入大量模拟数据
DECLARE
a number := 1;
BEGIN
while(a<10000) LOOP
INSERT INTO “PRJ_RKCW” (“BSM”,”CWLX”,”CSFS”,”CWXX”,”CWJB”,”CWSJ”,”SJLJ”,”RWBSM”,”QDBSM”,”SFYCL”,”CLFS”,”CLLY”,”BZ”) VALUES ((select count(t.BSM)+1 from PRJ_RKCW t), ‘系统异常’, ‘入库’, ‘错误消息’, ‘错误一级’, 时间, ‘文件路径’, 35, 69652, 0, NULL, NULL, NULL);
a :=a+1;
END LOOP;
END;
/
3、使用触发器+序列生成自增长的主键ID
create sequence SEQ_PRJ_RKRW minvalue 1 nomaxvalue increment by 1 start with 1 nocache;
create or replace TRIGGER prj_rkrw_ins_trg before INSERT
on PRJ_RKRW for each row when (new.bsm is null)
BEGIN
select SEQ_PRJ_RKRW .nextval into :new.bsm from dual;
END;
/
序列SEQ_PRJ_RKRW 最小值为1,无上限,增量为1,其实值为1,无缓存。
创建触发器,在插入PRJ_RKRW之前,对于所有行,当新建行的bsm为空的时候,就会选择SEQ_PRJ_RKRW 的下一个值,填入到新行的bsm中。
需注意 select 数字 into 后 ,需要冒号 :new.BSM from dual