oracle脚本示例,ORACLE常用脚本示例

create table DBO.INDEX_POLICY_TBL

(

ID NUMBER(10) NOT NULL PRIMARY KEY,

POLICY_ID NUMBER(10,0) default (-1) NOT NULL,

ALARM_COUNT NUMBER(10) default (0) NOT NULL

);

ALTER TABLE DBO.INDEX_POLICY_TBL ADD(C_NAME_1 VARCHAR2(64 CHAR) NOT NULL, C_NAME_2 VARCHAR2(64 CHAR) NOT NULL);

ALTER TABLE DBO.INDEX_POLICY_TBL RENAME COLUMN C_NAME_1 TO C_NAME_1_NEW;

ALTER TABLE DBO.INDEX_POLICY_TBL MODIFY C_NAME_2 VARCHAR2(128 CHAR);

create table DBO.INDEX_POLICY_TBL

(

ID NUMBER(10) NOT NULL PRIMARY KEY,

POLICY_ID NUMBER(10,0) default (-1) NOT NULL,

ALARM_COUNT NUMBER(10) default (0) NOT NULL

);

CREATE SEQUENCE DBO.INDEX_POLICY_TBL_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCACHE NOCYCLE;

create or replace

TRIGGER DBO.INDEX_POLICY_TBL_ID_IDENTITY BEFORE INSERT ON DBO.INDEX_POLICY_TBL

FOR EACH ROW

DECLARE

v_newVal NUMBER(10) := 0;

v_incval NUMBER(10) := 0;

BEGIN

IF INSERTING AND :new.ID IS NULL THEN

SELECT DBO.INDEX_POLICY_TBL_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 DBO.INDEX_POLICY_TBL;

v_newVal := v_newVal + 1;

--set the sequence to that value

LOOP

EXIT WHEN v_incval>=v_newVal;

SELECT DBO.INDEX_POLICY_TBL_SEQ.nextval INTO v_incval FROM dual;

END LOOP;

END IF;

-- save this to emulate @@identity

sqlserver_utilities.identity := v_newVal;

-- assign the value from the sequence to emulate the identity column

:new.ID := v_newVal;

END IF;

END;

INSERT INTO DBO.INDEX_POLICY_TBL(POLICY_ID, ALARM_COUNT, C_NAME_1_NEW, C_NAME_2)

VALUES(100, 1, ‘A‘, ‘B‘);

INSERT INTO DBO.INDEX_POLICY_TBL(POLICY_ID, ALARM_COUNT, C_NAME_1_NEW, C_NAME_2)

VALUES(101, 2, ‘B‘, ‘C‘);

INSERT INTO DBO.INDEX_POLICY_TBL(POLICY_ID, ALARM_COUNT, C_NAME_1_NEW, C_NAME_2)

VALUES(102, 3, ‘C‘, ‘D‘);

COMMIT;

SELECT * FROM DBO.INDEX_POLICY_TBL;

drop TRIGGER DBO.INDEX_POLICY_TBL_ID_IDENTITY;

drop SEQUENCE DBO.INDEX_POLICY_TBL_SEQ;

drop table DBO.INDEX_POLICY_TBL;

原文：http://www.cnblogs.com/chriskwok/p/3974610.html