Oracle 存储过程 根据机构编码生成序列号

CREATE OR REPLACE PROCEDURE PROCEDURE_GETSNBYORGCODE(P_ORGCODE IN VARCHAR2,
                                                     P_STATUS  OUT INTEGER,
                                                     P_ERRMSG  OUT VARCHAR2,
                                                     P_RESULT  OUT VARCHAR2) IS
  --
  -- 根据机构编码生成序列号
  -- LewJun
  -- 2018年9月28日 10点25分
  -- 最后修改时间 2018年9月28日 11点53分
  --
  --

  /*orgcode是否存在*/
  V_EXISTS_ORGCODE NUMBER;
  /*序列名称*/
  V_SEQNAME VARCHAR2(255);
  /*今天*/
  V_YYMMDD VARCHAR2(6);
  /*后缀后3位*/
  V_SUFFIX3 VARCHAR2(3);
  /*序列号开始*/
  V_SEQ_START_WITH VARCHAR2(50);
  /*sql语句*/
  V_SQL VARCHAR2(255);
  /*seq下一个值*/
  V_SEQ_NEXT_VAL NUMBER;
  /*seq中的日期*/
  V_SEQ_YYMMDD VARCHAR(6);
  /*异常：机构不存在*/
  V_EX_NOTFOUND_ORGCODE EXCEPTION;
  /*异常：该机构序列日期大于今天 */
  V_EX_SEQDATEGTNOW EXCEPTION;

BEGIN
  P_STATUS := 1;
  P_ERRMSG := 'ok';

  V_SEQNAME := 'SEQ_SN_' || P_ORGCODE;
  V_SUFFIX3 := '001';
  -- 1 判断orgcode在数据库中是否存在
  SELECT COUNT(1)
    INTO V_EXISTS_ORGCODE
    FROM TS_DEPT T
   WHERE T.MCHCODE = P_ORGCODE;
  IF V_EXISTS_ORGCODE <> 1 THEN
    /*抛出异常 机构不存在*/
    RAISE V_EX_NOTFOUND_ORGCODE;
  END IF;
  -- 存在

  /*是否存在这个V_SEQNAME*/
  SELECT COUNT(1)
    INTO V_EXISTS_ORGCODE
    FROM DBA_OBJECTS T
   WHERE LOWER(T.OBJECT_NAME) = LOWER(V_SEQNAME)
     AND T.STATUS = 'VALID'
     AND T.OBJECT_TYPE = 'SEQUENCE'
     AND T.OWNER = 'MCHIS';

  /*得到当天年月日*/
  SELECT TO_CHAR(SYSDATE, 'yyMMdd') INTO V_YYMMDD FROM DUAL;
  V_SEQ_START_WITH := P_ORGCODE || '' || V_YYMMDD || '' || V_SUFFIX3;
  /*如果不存在*/
  IF V_EXISTS_ORGCODE = 0 THEN
    /*创建序列*/
    EXECUTE IMMEDIATE 'create sequence ' || V_SEQNAME ||
                      ' minvalue 1 maxvalue 999999999999999999999999999 start with ' ||
                      V_SEQ_START_WITH || ' increment by 1';
    /*查询下一个seq*/
    V_SQL := 'SELECT ' || V_SEQNAME || '.NEXTVAL FROM DUAL';
    /*执行*/
    EXECUTE IMMEDIATE V_SQL
      INTO V_SEQ_NEXT_VAL;
  ELSE
    /*如果存在*/
    DBMS_OUTPUT.PUT_LINE('存在该seq');
    /*查询下一个seq*/
    V_SQL := 'SELECT ' || V_SEQNAME || '.NEXTVAL FROM DUAL';
    /*执行*/
    EXECUTE IMMEDIATE V_SQL
      INTO V_SEQ_NEXT_VAL;
  
    V_SEQ_YYMMDD := SUBSTR(V_SEQ_NEXT_VAL, LENGTH(P_ORGCODE) + 1, 6);
    IF V_YYMMDD < V_SEQ_YYMMDD THEN
      /*数据库序列大于了当天*/
      RAISE V_EX_SEQDATEGTNOW;
    ELSIF V_YYMMDD > V_SEQ_YYMMDD THEN
      /*判断序列当前值中的日期是否和今天一样*/
      DBMS_OUTPUT.PUT_LINE('seq 不是当天的');
      /*删除序列*/
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || V_SEQNAME;
    
      /*创建序列*/
      EXECUTE IMMEDIATE 'create sequence ' || V_SEQNAME ||
                        ' minvalue 1 maxvalue 999999999999999999999999999 start with ' ||
                        V_SEQ_START_WITH || ' increment by 1';
    
      /*查询下一个seq*/
      V_SQL := 'SELECT ' || V_SEQNAME || '.NEXTVAL FROM DUAL';
      /*执行*/
      EXECUTE IMMEDIATE V_SQL
        INTO V_SEQ_NEXT_VAL;
    END IF;
  END IF;

  /*给P_RESULT赋值*/
  P_RESULT := TO_CHAR(V_SEQ_NEXT_VAL);
EXCEPTION
  WHEN V_EX_NOTFOUND_ORGCODE THEN
    DBMS_OUTPUT.PUT_LINE('机构编码无效');
    P_STATUS := 0;
    P_ERRMSG := '机构编码无效';
  WHEN V_EX_SEQDATEGTNOW THEN
    DBMS_OUTPUT.PUT_LINE('该机构序列日期大于今天');
    P_STATUS := 0;
    P_ERRMSG := '该机构序列日期大于今天';
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('其它运行时错误');
    P_STATUS := 0;
    P_ERRMSG := '其它运行时错误';
    /*    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    DBMS_OUTPUT.PUT_LINE(SQLCODE);
    DBMS_OUTPUT.PUT_LINE(SQLERRM);*/
    ROLLBACK;
END PROCEDURE_GETSNBYORGCODE;

 

MyBatis调用：

<select id="callProcedure1" parameterMap="PROCEDURE_GETSNBYORGCODE_MAP" statementType="CALLABLE">
    CALL PROCEDURE_GETSNBYORGCODE(?, ?, ?, ?)
</select>

<parameterMap type="java.util.Map" id="PROCEDURE_GETSNBYORGCODE_MAP">
    <parameter property="P_ORGCODE" mode="IN" jdbcType="VARCHAR"/>
    <parameter property="P_STATUS" mode="OUT" jdbcType="INTEGER"/>
    <parameter property="P_ERRMSG" mode="OUT" jdbcType="VARCHAR"/>
    <parameter property="P_RESULT" mode="OUT" jdbcType="VARCHAR"/>
</parameterMap>
<select id="callProcedure2" parameterType="java.util.Map" statementType="CALLABLE">
    {CALL PROCEDURE_GETSNBYORGCODE(
        #{P_ORGCODE, mode=IN, jdbcType=VARCHAR},
        #{P_STATUS, mode=OUT, jdbcType=INTEGER},
        #{P_ERRMSG, mode=OUT, jdbcType=VARCHAR},
        #{P_RESULT, mode=OUT, jdbcType=VARCHAR}
    )}
 </select>