Oracle SQL 总结

Oracle SQL 总结

序列篇

--创建-序列

CREATE SEQUENCE SEQ_ID_APPS INCREMENT BY 1 START WITH 1; --SEQ_ID_APPS  序列名称

--删除-序列

DROP SEQUENCE SEQ_ID_APPS; --SEQ_ID_APPS  序列名称

--查询-序列当前值

select SEQ_ID_APPS.currval from dual;  --SEQ_ID_APPS  序列名称  currval序列当前值

--查询并获取-序列下个值

select SEQ_ID_APPS.nextval from dual;  --SEQ_ID_APPS nextval先增加序列的值，然后返回序列值

--查找所有SEQUENCE

select * from user_objects ubs where ubs.OBJECT_TYPE='SEQUENCE';  

--查找所有TABLE

select * from user_objects ubs where ubs.OBJECT_TYPE='TABLE';

--更新-序列

DECLARE  
   V_NUM NUMBER;  
BEGIN 
   V_NUM := 0;  
  SELECT COUNT(0) INTO V_NUM FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_ID_APPS';
    IF V_NUM > 0 THEN   
      EXECUTE IMMEDIATE 'DROP SEQUENCE  SEQ_ID_APPS';   
    END IF;
   
    V_NUM := 0;  
  SELECT COUNT(0) INTO V_NUM FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_ID_APPS';
   IF V_NUM = 0 THEN
      EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_ID_APPS INCREMENT BY 1 START WITH 1';
  END IF;
END;

触发器

--更新触发器

CREATE OR REPLACE
TRIGGER TRG_APPS BEFORE INSERT ON TBL_OPERATION_APPS FOR EACH ROW 
BEGIN
        SELECT SEQ_ID_APPS.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

--删除-触发器

DROP TRIGGER TRG_APPS;

Oracle 正则表达式：

  select * from dual  where regexp_like ('1xxxx1234cdfd$',  '^[0-9A-Za-z]+$')