Oracle常用脚本示例

CREATE SEQUENCE s_report_id     INCREMENT BY 1   MAXVALUE 999999    START WITH 1;  
CREATE SEQUENCE checkup_no_seq  NOCYCLE          MAXVALUE 999999    START WITH 2;
 
CREATE OR REPLACE TRIGGER  bef_ins_report  
BEFORE INSERT ON busi_report
referencing old as old new as new
FOR EACH ROW
BEGIN 
         new.rep_id = s_report_id.nextval;  
END;  

CREATE OR REPLACE TRIGGER  bef_ins_report
BEFORE INSERT ON busi_report
FOR EACH ROW
DECLARE next_id NUMBER;
BEGIN
      SELECT s_report_id.NEXTVAL INTO next_id FROM dual;
      :new.rep_id := next_id;
END;

-------------------------------------------------------------------------
-----触发示例-----
--------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER set_checkup_no
BEFORE INSERT ON checkup_history
FOR EACH ROW DECLARE next_checkup_no NUMBER;
BEGIN
      SELECT checkup_no_seq.NEXTVAL INTO next_checkup_no FROM dual;
      :new.checkup_no := next_checkup_no;
END;

 

--------------------------------------------------------------------------
-----建表删表-----
--------------------------------------------------------------------------
CREATE TABLE busi_report
(
  rep_id   number(6)   NOT NULL,
  content  varchar2(80) ,
  adder    varchar2(20) ,
  rep_src    varchar2(20) ,
  add_time  date ,
  rep_time  date ,
  PRIMARY KEY  (rep_id)
);
drop table BUSI_REPORT
select * from busi_report

insert into busi_report (rep_id, content, adder, rep_src, add_time, rep_time)  values(21,'天P有雨','zss','adf',to_date('2002-3-2   12:31:02','yyyy-mm-dd   hh24:mi:ss'),to_date('2003-3-2   12:31:02','yyyy-mm-dd   hh24:mi:ss'));