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;
最新文章
- Theano conv2d的border_mode
- faster alter table add column
- 【学】SoapExtension 学习
- Opencv step by step - 基本数据类型
- 12. Integer to Roman
- jQuery在IE7和8下setInterval失效的问题
- MySQL::SQL_MODE
- Oracle用户管理
- poj Candies
- ECSTORE2.0 去页面底部版权
- P5057 [CQOI2006]简单题
- 刷题的model格式(请用英文)
- 分享一个爬取HUST(哈理工)学生成绩的Python程序(OCR自动识别验证码)
- FutureTask类
- 推荐系统之协同过滤的原理及C++实现
- python3实现mysql导出excel
- 集成学习总结 &; Stacking方法详解
- 华为交换机 查看 ip和mac对应关系
- idea 本地调用zookeeper配置
- BZOJ P1188 HNOI2007 分裂游戏——solution