数据库知识

拷贝表 
create table  table_name   as   select   *   from   Source_table   where   1=1;

复制表结构 
create table  table_name   as   select   *   from   Source_table   where   1 <> 1;

Oracle 序列：

CREATE SEQUENCE example_sequence

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

NOMAXVALUE -- 不设置最大值

NOCYCLE -- 一直累加，不循环

NOCACHE -- 不建缓冲区；

 

触发器：

再创建一个触发器：

CREATE TRIGGER example_triger BEFORE

INSERT ON example FOR EACH ROW WHEN (new.id is null)--只有在id为空时，启动该触发器生成id号

begin

select example_sequence.nextval into: new.id from dual;

end;

 

复杂的触发器：

if inserting then
    insert into CC02_ZJ (
a_rid,
b_rid,
cdc001,
bdc001,
aac001,
aac009,
cdc004,
cdc299,
cab001,
cdc002,
cdc003,
cdc006,
ajc093,
cdc005,
acc740,
aca112,
bdc010,
aae030,
aae031,
hba201,
cdc007,
cdc009,
cdc010,
cdc012,
cdc013,
cdc159,
cdc194,
cdc294,
cdc198,
cdc047,
cdc048,
acc0k6,
cdc014,
acc0k2,
aae013,
aae019,
cdc356,
cdc355,
cdc015,
aae011,
aae017,
cdc045,
aae043,
aae012,
aae018,
cdc016,
cdc700,
acc02d,
cdc065,
bcc020,
cdc017,
aaf017,
aab301,
aaf013,
aaf030,
aaz002,
cdc288,
seqlogid,
cdc018,
acc0k3,
aae014,
aae020,
cdc030,
cdc901,
cdc902,
cdc903,
cca011,
acc020,
aae999,
cdc160,
zone1,
aae100,
aac999,
aac998,
cdc260,
cdc601,
cdc602,
cdc603,
cdc604,
cdc605,
cdc606,
cdc607,
cdc608,
cdc609,
cdc610,
cdc611,
cdc612,
cdc613,
cdc614,
s_state
  ) values(
:NEW.a_rid,:NEW.b_rid,:NEW.cdc001,:NEW.bdc001,:NEW.aac001,:NEW.aac009,:NEW.cdc004,:NEW.cdc299,:NEW.cab001,:NEW.cdc002,:NEW.cdc003,:NEW.cdc006,:NEW.ajc093,:NEW.cdc005,:NEW.acc740,:NEW.aca112,:NEW.bdc010,:NEW.aae030,:NEW.aae031,:NEW.hba201,:NEW.cdc007,:NEW.cdc009,:NEW.cdc010,:NEW.cdc012,:NEW.cdc013,:NEW.cdc159,:NEW.cdc194,:NEW.cdc294,:NEW.cdc198,:NEW.cdc047,:NEW.cdc048,:NEW.acc0k6,:NEW.cdc014,:NEW.acc0k2,:NEW.aae013,:NEW.aae019,:NEW.cdc356,:NEW.cdc355,:NEW.cdc015,:NEW.aae011,:NEW.aae017,:NEW.cdc045,:NEW.aae043,:NEW.aae012,:NEW.aae018,:NEW.cdc016,:NEW.cdc700,:NEW.acc02d,:NEW.cdc065,:NEW.bcc020,:NEW.cdc017,:NEW.aaf017,:NEW.aab301,:NEW.aaf013,:NEW.aaf030,:NEW.aaz002,:NEW.cdc288,:NEW.seqlogid,:NEW.cdc018,:NEW.acc0k3,:NEW.aae014,:NEW.aae020,:NEW.cdc030,:NEW.cdc901,:NEW.cdc902,:NEW.cdc903,:NEW.cca011,:NEW.acc020,:NEW.aae999,:NEW.cdc160,:NEW.zone1,:NEW.aae100,:NEW.aac999,:NEW.aac998,:NEW.cdc260,:NEW.cdc601,:NEW.cdc602,:NEW.cdc603,:NEW.cdc604,:NEW.cdc605,:NEW.cdc606,:NEW.cdc607,:NEW.cdc608,:NEW.cdc609,:NEW.cdc610,:NEW.cdc611,:NEW.cdc612,:NEW.cdc613,:NEW.cdc614
, 'I');
    elsif updating then

     select  count(*) into sourceUserTpCount from CC02_ZJ where A_RID=:OLD.A_RID;
     if sourceUserTpCount >0  then

      update CC02_ZJ set
 a_rid=:NEW.a_rid,b_rid=:NEW.b_rid,cdc001=:NEW.cdc001,bdc001=:NEW.bdc001,aac001=:NEW.aac001,aac009=:NEW.aac009,cdc004=:NEW.cdc004,cdc299=:NEW.cdc299,cab001=:NEW.cab001,
 cdc002=:NEW.cdc002,cdc003=:NEW.cdc003,cdc006=:NEW.cdc006,ajc093=:NEW.ajc093,cdc005=:NEW.cdc005,acc740=:NEW.acc740,aca112=:NEW.aca112,bdc010=:NEW.bdc010,aae030=:NEW.aae030,
 aae031=:NEW.aae031,hba201=:NEW.hba201,cdc007=:NEW.cdc007,cdc009=:NEW.cdc009,cdc010=:NEW.cdc010,cdc012=:NEW.cdc012,cdc013=:NEW.cdc013,cdc159=:NEW.cdc159,cdc194=:NEW.cdc194,
 cdc294=:NEW.cdc294,cdc198=:NEW.cdc198,cdc047=:NEW.cdc047,cdc048=:NEW.cdc048,acc0k6=:NEW.acc0k6,cdc014=:NEW.cdc014,acc0k2=:NEW.acc0k2,aae013=:NEW.aae013,aae019=:NEW.aae019,
 cdc356=:NEW.cdc356,cdc355=:NEW.cdc355,cdc015=:NEW.cdc015,aae011=:NEW.aae011,aae017=:NEW.aae017,cdc045=:NEW.cdc045,aae043=:NEW.aae043,aae012=:NEW.aae012,aae018=:NEW.aae018,
 cdc016=:NEW.cdc016,cdc700=:NEW.cdc700,acc02d=:NEW.acc02d,cdc065=:NEW.cdc065,bcc020=:NEW.bcc020,cdc017=:NEW.cdc017,aaf017=:NEW.aaf017,aab301=:NEW.aab301,aaf013=:NEW.aaf013,
 aaf030=:NEW.aaf030,aaz002=:NEW.aaz002,cdc288=:NEW.cdc288,seqlogid=:NEW.seqlogid,cdc018=:NEW.cdc018,acc0k3=:NEW.acc0k3,aae014=:NEW.aae014,aae020=:NEW.aae020,cdc030=:NEW.cdc030,
 cdc901=:NEW.cdc901,cdc902=:NEW.cdc902,cdc903=:NEW.cdc903,cca011=:NEW.cca011,acc020=:NEW.acc020,aae999=:NEW.aae999,cdc160=:NEW.cdc160,zone1=:NEW.zone1,aae100=:NEW.aae100,
 aac999=:NEW.aac999,aac998=:NEW.aac998,cdc260=:NEW.cdc260,cdc601=:NEW.cdc601,cdc602=:NEW.cdc602,cdc603=:NEW.cdc603,cdc604=:NEW.cdc604,cdc605=:NEW.cdc605,cdc606=:NEW.cdc606,
 cdc607=:NEW.cdc607,cdc608=:NEW.cdc608,cdc609=:NEW.cdc609,cdc610=:NEW.cdc610,cdc611=:NEW.cdc611,cdc612=:NEW.cdc612,cdc613=:NEW.cdc613,cdc614=:NEW.cdc614,s_state='U'
      where A_RID=:OLD.A_RID;

        else

       insert into CC02_ZJ (
 a_rid,
b_rid,
cdc001,
bdc001,
aac001,
aac009,
cdc004,
cdc299,
cab001,
cdc002,
cdc003,
cdc006,
ajc093,
cdc005,
acc740,
aca112,
bdc010,
aae030,
aae031,
hba201,
cdc007,
cdc009,
cdc010,
cdc012,
cdc013,
cdc159,
cdc194,
cdc294,
cdc198,
cdc047,
cdc048,
acc0k6,
cdc014,
acc0k2,
aae013,
aae019,
cdc356,
cdc355,
cdc015,
aae011,
aae017,
cdc045,
aae043,
aae012,
aae018,
cdc016,
cdc700,
acc02d,
cdc065,
bcc020,
cdc017,
aaf017,
aab301,
aaf013,
aaf030,
aaz002,
cdc288,
seqlogid,
cdc018,
acc0k3,
aae014,
aae020,
cdc030,
cdc901,
cdc902,
cdc903,
cca011,
acc020,
aae999,
cdc160,
zone1,
aae100,
aac999,
aac998,
cdc260,
cdc601,
cdc602,
cdc603,
cdc604,
cdc605,
cdc606,
cdc607,
cdc608,
cdc609,
cdc610,
cdc611,
cdc612,
cdc613,
cdc614,
s_state
  ) values(
:NEW.a_rid,:NEW.b_rid,:NEW.cdc001,:NEW.bdc001,:NEW.aac001,:NEW.aac009,:NEW.cdc004,:NEW.cdc299,:NEW.cab001,:NEW.cdc002,:NEW.cdc003,:NEW.cdc006,:NEW.ajc093,:NEW.cdc005,:NEW.acc740,:NEW.aca112,:NEW.bdc010,:NEW.aae030,:NEW.aae031,:NEW.hba201,:NEW.cdc007,:NEW.cdc009,:NEW.cdc010,:NEW.cdc012,:NEW.cdc013,:NEW.cdc159,:NEW.cdc194,:NEW.cdc294,:NEW.cdc198,:NEW.cdc047,:NEW.cdc048,:NEW.acc0k6,:NEW.cdc014,:NEW.acc0k2,:NEW.aae013,:NEW.aae019,:NEW.cdc356,:NEW.cdc355,:NEW.cdc015,:NEW.aae011,:NEW.aae017,:NEW.cdc045,:NEW.aae043,:NEW.aae012,:NEW.aae018,:NEW.cdc016,:NEW.cdc700,:NEW.acc02d,:NEW.cdc065,:NEW.bcc020,:NEW.cdc017,:NEW.aaf017,:NEW.aab301,:NEW.aaf013,:NEW.aaf030,:NEW.aaz002,:NEW.cdc288,:NEW.seqlogid,:NEW.cdc018,:NEW.acc0k3,:NEW.aae014,:NEW.aae020,:NEW.cdc030,:NEW.cdc901,:NEW.cdc902,:NEW.cdc903,:NEW.cca011,:NEW.acc020,:NEW.aae999,:NEW.cdc160,:NEW.zone1,:NEW.aae100,:NEW.aac999,:NEW.aac998,:NEW.cdc260,:NEW.cdc601,:NEW.cdc602,:NEW.cdc603,:NEW.cdc604,:NEW.cdc605,:NEW.cdc606,:NEW.cdc607,:NEW.cdc608,:NEW.cdc609,:NEW.cdc610,:NEW.cdc611,:NEW.cdc612,:NEW.cdc613,:NEW.cdc614
, 'U');

end if;

     elsif deleting then
     update  CC02_ZJ set s_state='D' where A_RID=:OLD.A_RID;

     end if;


end;