Oracle通过函数生成表主键

╰半夏微凉° 2022-05-15 00:30 244阅读 0赞

-- 调用：SELECT FUN_GET_TABLE_ID('TEST','ID','A',23) FROM DUAL
    CREATE OR REPLACE 
    FUNCTION FUN_GET_TABLE_ID(IN_TABLE_NAME  VARCHAR2,
    	                            IN_COLUMN_NAME VARCHAR2,
    	                            IN_PK_KSBS     VARCHAR2,
    	                            IN_PK_LEGTH    NUMBER) RETURN VARCHAR2 IS
    	    PRAGMA AUTONOMOUS_TRANSACTION;
    	    TMP_CONT NUMBER(1);       --临时查询结果
    	    TMP_VAL NUMBER(8);        --临时的序号尾号值
    	    TMP_DATE VARCHAR2(8);     --上次生成日期
    	    TMP_DATE_VAL VARCHAR2(8); --日期值
    	    TMP_PK_VAL NUMBER(10);    --当前的序号尾号值
    	    TMP_PK_LEGTH NUMBER(4);   --流水号长度
    	    TMP_PK_KSZ VARCHAR2(40);  --主键开始值（去除流水号）
    	    TMP_LEN NUMBER(8);        --临时长度
    	  BEGIN
    	    --查询当前请求在SYS_ID表中是否存在记录
    	    SELECT COUNT(*) INTO TMP_CONT FROM SYS_ID WHERE TABLE_NAME=IN_TABLE_NAME AND COLUMN_NAME=IN_COLUMN_NAME;
    	    IF TMP_CONT=0 THEN
    	      --在SYS_ID表中没有记录时，自动新增一条记录
    	      TMP_VAL := 1;
    	      TMP_DATE:=TO_CHAR(SYSDATE,'YYYYMMDD');
    	      INSERT INTO SYS_ID
    	         (TABLE_NAME, COLUMN_NAME, PK_VAL, PK_LEGTH, PK_DATE, PK_KSBS, PK_BZ,PK_ZJ)
    	      VALUES
    	         (IN_TABLE_NAME, IN_COLUMN_NAME, TMP_VAL, IN_PK_LEGTH, TMP_DATE, IN_PK_KSBS, '系统生成',SYS_GUID());
    	      COMMIT;
    	    ELSE
    	      --在SYS_ID表中存在记录，查询当前主键值加1
    	      SELECT PK_VAL,PK_DATE,PK_LEGTH INTO TMP_PK_VAL,TMP_DATE,TMP_PK_LEGTH FROM SYS_ID WHERE TABLE_NAME=IN_TABLE_NAME AND COLUMN_NAME=IN_COLUMN_NAME FOR UPDATE;
    	      --根据日期规则生成当前日期值
    	      TMP_DATE_VAL := TO_CHAR(SYSDATE,'YYYYMMDD');
    	      --判断当前日期和数据库中的日期值是否相等:如不相等或者数据库中日期为空，则从1开始生成主键值
    	      IF TMP_DATE IS NULL OR TMP_DATE<>TMP_DATE_VAL THEN
    	         TMP_DATE := TMP_DATE_VAL;
    	         TMP_VAL := 1;
    	      ELSE
    	         TMP_VAL := TMP_PK_VAL + 1;
    	      END IF;
    	      --更新值和日期
    	      UPDATE SYS_ID SET PK_VAL=TMP_VAL,PK_DATE=TMP_DATE WHERE TABLE_NAME=IN_TABLE_NAME AND COLUMN_NAME=IN_COLUMN_NAME;
    	      COMMIT;
    	    END IF;
    	    --去除流水号外的主键值
    	    TMP_PK_KSZ := IN_PK_KSBS||TMP_DATE;
    	    --去除流水号外的主键长度
    	    TMP_LEN:=IN_PK_LEGTH-LENGTH(TMP_PK_KSZ);
    	    --返回主键值
    	    RETURN TMP_PK_KSZ||LPAD(TMP_VAL,TMP_LEN,'0');
    	  EXCEPTION
    	    WHEN NO_DATA_FOUND THEN
    	      ROLLBACK;
    	      RAISE_APPLICATION_ERROR(-20999,SQLERRM || '没有查到数据!');
    	    WHEN OTHERS THEN
    	      ROLLBACK;
    	      RAISE_APPLICATION_ERROR(-20999,SQLERRM||'编号生成出错!');
    	  END FUN_GET_TABLE_ID;