oracle基本

1.oracle中创建序列：

CREATE SEQUENCE "CONFIGUSER"."AALARM_DATA_AALARM_SEQUENC_SEQ" 

MINVALUE 1

 MAXVALUE 999999999999999999999999

 INCREMENT BY 1

 START WITH 1

 CACHE 20

 NOORDER 

NOCYCLE ;

2.oracle11g 中创建触发器：知道：new 和 ：old变量的用法 标准的触发器

create or replace
TRIGGER aalarm_data_AALARM_SEQUEN_TRIG BEFORE INSERT OR UPDATE ON aalarm_data
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.AALARM_SEQUENCE IS NULL THEN
    SELECT  aalarm_data_AALARM_SEQUENC_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(AALARM_SEQUENCE),0) INTO v_newVal FROM aalarm_data;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT aalarm_data_AALARM_SEQUENC_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.AALARM_SEQUENCE := v_newVal;
  END IF;
END

create or replace
trigger DELETE_DEVICE_TRIGGER 
before delete on device for each row
begin
   Delete  from device where PAR_DEVICE_ID = :OLD.DEVICE_ID;
   Delete  from data_object where  DEVICE_ID_PARENT = :OLD.DEVICE_ID;
end;

3.oracle中创建存储过程：

create or replace
PROCEDURE move_alarm_to_history as
  BEGIN
    INSERT INTO HISTORYUSER.alarm_history  SELECT * FROM AALARM_DATA WHERE sysdate-COALESCE(ALARM_END,sysdate)>2;
    DELETE FROM AALARM_DATA WHERE sysdate-COALESCE(ALARM_END,sysdate) > 2;
    commit;

END move_alarm_to_history;

create or replace
procedure get_alarm_count as
v_newVal NUMBER(12) := 0;
begin
  SELECT max(AALARM_SEQUENCE) INTO v_newVal FROM aalarm_data;
end get_alarm_count;

4.oracle表空间创建：

create tablespace t_spacename
datafile 'D:\janely\spaceName.dbf'--默认大写文件名:SPACENAME.DBF
size 100M AutoExtend On Next 10M Maxsize 2048M
extent management local

segment space management auto

5.oracle批量删除表

BEGIN
  FOR T IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'MD_%' ) LOOP
   execute immediate 'drop table '||T.TABLE_NAME||' purge';
  end loop;
END;
/

查询出来要删除的表然后执行drop记录集。