1、创建序列
CREATE SEQUENCE SEQUENCE_STUDENT_ID MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 NOCACHE;
2、创建触发器
CREATE OR REPLACE TRIGGER TRG_ON_INS_STUDENT BEFORE INSERT ON STUDENT FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
SELECT SEQUENCE_STUDENT_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
3、查询表的所有约束
SELECT * FROM USER_CONS_COLUMNS where TABLE_NAME = 'USER_INFO';
SELECT * FROM USER_CONSTRAINTS;
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
AND AU.CONSTRAINT_TYPE = 'U'
AND AU.TABLE_NAME = 'USER_INFO';
4、查询表的触发器：
select trigger_name from all_triggers where table_name = 'USER_INFO';
5、查询触发器的详细信息：
select text from all_source where type='TRIGGER' AND name='USER_INFO';
6、查询表的序列的当前值：
select USER_INFO_seq.nextval from dual;
7、修改表的序列的当前值：
alter sequence USER_INFO_seq increment by 100;
select USER_INFO_seq.nextval from dual;
alter sequence USER_INFO_seq increment by 1;
