常用SQL方法

 eg：select  substr(‘Hello World‘,-3,3) from dual;    ----->rld

SELECT TRIM(leading|trailing|both string1 FROM string2) FROM dual;

 --创建唯一的索引  create unique index index_name on table_name（column_name）;

declare --声明游标
  CURSOR C_PCINFO IS
    SELECT
      TO_CHAR(SUBSTR(f.forminfo, instr(f.FORMINFO,‘<date>‘,1)+6, (instr(f.FORMINFO,‘</date>‘,1) -instr(f.FORMINFO,‘<date>‘,1))-6)) AS DATESTR,
 
      TO_CHAR(SUBSTR(f.forminfo, instr(f.FORMINFO,‘<areaCode>‘,1)+10, (instr(f.FORMINFO,‘</areaCode>‘,1) - instr(f.FORMINFO,‘<areaCode>‘,1))-10)) AS AREACODE ,
 
      f.PROCESSID AS PID
    FROM
      BPMUSER2.FORM_MASTER_INFO f
    WHERE
      f.FORMID=‘GL_PC‘ AND f.CREATETIME BETWEEN SYSDATE-365 AND SYSDATE+1;
    C_ROW C_PCINFO%ROWTYPE; --声明变量类型为行类型
    c_count number:=0;
  BEGIN
    FOR C_ROW IN C_PCINFO LOOP --打开游标 并开始循环
      IF C_ROW.DATESTR IS NOT NULL
        AND C_ROW.AREACODE IS NOT NULL
          AND C_ROW.PID IS NOT NULL
      THEN
        INSERT INTO BPMUSER2.PC_DATE_INFO(ID,PROCESSID,USERDATE,SCOPEID) VALUES(SEQ_PC_DATE_INFO.nextval,C_ROW.PID,to_date(C_ROW.DATESTR,‘yyyy-mm-dd‘),C_ROW.AREACODE);
 
        c_count:=c_count+1;
        if c_count>1000 then  --每次提交100条
          commit;
          c_count:=0;
        end if;
      END IF;
    END LOOP;
    COMMIT;
  END;

CREATE SEQUENCE  "BPMUSER"."SEQ_PC_DATE_INFO"  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 60074 NOCACHE  NOORDER  NOCYCLE