ORACLE之建表、建序列、建触发器SQL语句

今天在生产上准备把新表加入到库中，运维在建表等反面给了一些案例和建议和大家共勉

1 标准TIGGER写法参考如下


  CREATE OR REPLACE TRIGGER "QRPAY"."APP_INFO_ID_TRG" BEFORE INSERT OR UPDATE ON  app_info
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.id IS NULL THEN
    SELECT   app_info_id_seq.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN
      --get the max indentity value from the table
      SELECT NVL(max(id),0) INTO v_newVal FROM  app_info;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT  app_info_id_seq.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    --used to emulate LAST_INSERT_ID()
    --mysql_utilities.identity := v_newVal;
   -- assign the value from the sequence to emulate the identity column
   :new.id := v_newVal;
  END IF;
END;

ALTER TRIGGER "QRPAY"."APP_INFO_ID_TRG" ENABLE


select dbms_metadata.get_ddl('TABLE','BANK_CARD') from dual;

少主健

  CREATE TABLE "QRPAY"."BANK_CARD" 
   (	"ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ACCOUNT_NO" VARCHAR2(21 CHAR), 
	"ACCOUNT_NAME" VARCHAR2(20 CHAR), 
	"ID_NUMBER" VARCHAR2(20 CHAR), 
	"MERCHANT_NO" VARCHAR2(30 CHAR), 
	"STATUS" VARCHAR2(2 CHAR), 
	"RESERVED_PHONE" VARCHAR2(12 CHAR), 
	"CREATE_TIME" DATE, 
	"CARD_TYPE" VARCHAR2(10 CHAR), 
	"BANK_NAME" VARCHAR2(10 CHAR)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "QRPAY" 
--BANK_CARD_ID_SEQ
--BANK_CARD_ID_TRG


select dbms_metadata.get_ddl('SEQUENCE','BANK_CARD_ID_SEQ') from dual;  获取序列

   CREATE SEQUENCE  "QRPAY"."BANK_CARD_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE 

select dbms_metadata.get_ddl('TRIGGER','BANK_CARD_ID_TRG') from dual;  获取触发器

参考前面标准写法修改TRIGGER

  CREATE OR REPLACE TRIGGER "QRPAY"."BANK_CARD_ID_TRG" 
  before insert
  on bank_card 
  for each row
declare
  -- local variables here
begin
   select bank_card_id_seq.nextval into:New.id from dual;
end bank_card_id_trg;

ALTER TRIGGER "QRPAY"."BANK_CARD_ID_TRG" ENABLE