建立oracle資料表示例sql

標籤：

CREATE TABLE "BRAND_RELATION" 
   (    
    "ID" NUMBER(10,0) NOT NULL ENABLE, 
    "CATID" NUMBER(10,0) NOT NULL ENABLE, 
    "BRAND_ID" NUMBER(10,0) NOT NULL ENABLE, 
    PRIMARY KEY ("ID")
    );
CREATE SEQUENCE "BR_ID_SEQ" NOCACHE;


CREATE INDEX "BR_CATID" ON "BRAND_RELATION" ("CATID"); 
CREATE INDEX "BR_BRAND_ID" ON "BRAND_RELATION" ("BRAND_ID"); 

CREATE OR REPLACE TRIGGER "BR_ID_TRG" BEFORE INSERT ON "BRAND_RELATION" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW ENABLE
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.id IS NULL THEN
    SELECT  BR_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 BRAND_RELATION;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT BR_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;

建立oracle資料表示例sql