oracle数据库常用的脚本语句

一、创建表

declare
  i_count integer;
begin
  select count(*) into i_count from user_tables a where upper(a.table_name) = 'T_STOCK_HGT';
  if i_count = 0 then 
    execute immediate 'create table T_STOCK_HGT
(
  N_ID                   NUMBER(18) not null,
  C_SEC_CODE           VARCHAR2(20) not null,
  C_SEC_NAME           VARCHAR2(50) not null,
  C_ISIN_CODE          VARCHAR2(50),
  C_MKT_CODE           VARCHAR2(5),
  C_SEC_LB             VARCHAR2(5),
  N_CAPITAL            NUMBER(30,4),
  N_CIR_CAPITAL        NUMBER(30,4),
  C_PLATE_CODE         VARCHAR2(20) NOT NULL,
  D_UPDATE_TIME        DATE NOT NULL,
  C_UPDATE_BY		   VARCHAR2(20) NOT NULL
)';  
execute immediate 'comment on column T_STOCK_HGT.C_SEC_CODE
  is ''证券代码''';
execute immediate 'comment on column T_STOCK_HGT.C_SEC_NAME
  is ''证券名称''';
execute immediate 'comment on column T_STOCK_HGT.C_ISIN_CODE
  is ''ISINcode''';
execute immediate 'comment on column T_STOCK_HGT.C_MKT_CODE
  is ''市场代码''';
execute immediate 'comment on column T_STOCK_HGT.C_SEC_LB
  is ''证券类别''';
execute immediate 'comment on column T_STOCK_HGT.N_CAPITAL
  is ''总股本''';
execute immediate 'comment on column T_STOCK_HGT.N_CIR_CAPITAL
  is ''流通股本''';
execute immediate 'comment on column T_STOCK_HGT.C_PLATE_CODE
  is ''板块代码''';
execute immediate 'comment on column T_STOCK_HGT.D_UPDATE_TIME
  is ''更新时间''';
execute immediate 'comment on column T_STOCK_HGT.C_UPDATE_BY
  is ''更新人''';

  
execute immediate 'alter table T_STOCK_HGT
  add constraint PK_STOCK_HGT primary key (N_ID,C_SEC_CODE)
  using index';
  
  end if;
end;
/

二、创建序列

declare
  i_count integer;
begin
  select count(*) into i_count from user_sequences where SEQUENCE_NAME='SEQ_T_STOCK_HGT' ;
  if i_count = 0 then 
    execute immediate 'create sequence SEQ_T_STOCK_HGT
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20';  
  end if;
end;
/

三、创建触发器

CREATE OR REPLACE TRIGGER "TRIG_T_STOCK_HGT_ID"
BEFORE INSERT ON T_STOCK_HGT
FOR EACH ROW
BEGIN
    Select SEQ_T_STOCK_HGT.NEXTVAL INTO :NEW.N_ID
    FROM DUAL;
END;
/