BRD:根据渠道安排随机分配靓号的实现

--BRD:根据渠道安排随机分配置靓号
-- Create table
create table T_LUCKY_ITEM
(
  BILL_ID    VARCHAR2(20),
  BILL_LEVEL VARCHAR2(10),
  ORG_ID     VARCHAR2(20),
  FREE_01    VARCHAR2(100),
  FREE_02    VARCHAR2(100),
  FREE_03    VARCHAR2(100),
  FREE_04    VARCHAR2(100),
  FREE_05    VARCHAR2(100)
);
-- Add comments to the table 
comment on table T_LUCKY_ITEM is '吉祥号等级明细表';
-- Add comments to the columns 
comment on column T_LUCKY_ITEM.BILL_ID is '吉祥号';
comment on column T_LUCKY_ITEM.BILL_LEVEL is '吉祥号等级';
comment on column T_LUCKY_ITEM.ORG_ID is '渠道';  
  
-- Create table
create table T_ORG
(
  ORG_ID          VARCHAR2(20),
  BILL_LEVEL_1_NO NUMBER(10),
  BILL_LEVEL_2_NO NUMBER(10),
  BILL_LEVEL_3_NO NUMBER(10),
  BILL_LEVEL_4_NO NUMBER(10),
  BILL_LEVEL_5_NO NUMBER(10),
  FREE_01         VARCHAR2(100),
  FREE_02         VARCHAR2(100),
  FREE_03         VARCHAR2(100),
  FREE_04         VARCHAR2(100),
  FREE_05         VARCHAR2(100)
);
-- Add comments to the table 
comment on table T_ORG is '渠道分配表';
-- Add comments to the columns 
comment on column T_ORG.ORG_ID is '渠道ID';
comment on column T_ORG.BILL_LEVEL_1_NO is '1级靓号分配个数';
comment on column T_ORG.BILL_LEVEL_2_NO is '2级靓号分配个数';
comment on column T_ORG.BILL_LEVEL_3_NO is '3级靓号分配个数';
comment on column T_ORG.BILL_LEVEL_4_NO is '4级靓号分配个数';
comment on column T_ORG.BILL_LEVEL_5_NO is '5级靓号分配个数';

create table T_LUCKY_ASSIGN
(
  BILL_ID    VARCHAR2(20),
  BILL_LEVEL VARCHAR2(10),
  ORG_ID     VARCHAR2(20),
  FREE_01    VARCHAR2(100),
  FREE_02    VARCHAR2(100),
  FREE_03    VARCHAR2(100),
  FREE_04    VARCHAR2(100),
  FREE_05    VARCHAR2(100)
);
-- Add comments to the table 
comment on table T_LUCKY_ITEM is '渠道吉祥号等级分配临时表（可建成会话级临时表）';
-- Add comments to the columns 
comment on column T_LUCKY_ITEM.BILL_ID is '吉祥号';
comment on column T_LUCKY_ITEM.BILL_LEVEL is '吉祥号等级';
comment on column T_LUCKY_ITEM.ORG_ID is '渠道'; 


SELECT ROWID,T.* FROM T_ORG T;
SELECT ROWID,T.* FROM T_LUCKY_ITEM T WHERE T.ORG_ID IS NOT NULL;
SELECT ROWID,T.* FROM T_LUCKY_ASSIGN T;

SELECT ROWID,T.* FROM T_LUCKY_ITEM T WHERE BILL_LEVEL = 1;

--调用
BEGIN
 SP_LUCKY_NO_ASSIGN;
END;
/

--结果查询
SELECT T.* FROM T_LUCKY_ITEM T WHERE T.ORG_ID IS NOT NULL;


SELECT T.ORG_ID,T.BILL_LEVEL_1_NO,T.BILL_LEVEL_2_NO,T.BILL_LEVEL_3_NO,T.BILL_LEVEL_4_NO,T.BILL_LEVEL_5_NO,
       T.BILL_LEVEL_1_NO+T.BILL_LEVEL_2_NO+T.BILL_LEVEL_3_NO+T.BILL_LEVEL_4_NO+T.BILL_LEVEL_5_NO AS SUM_NO
  FROM T_ORG T
  ORDER BY T.BILL_LEVEL_1_NO+T.BILL_LEVEL_2_NO+T.BILL_LEVEL_3_NO+T.BILL_LEVEL_4_NO+T.BILL_LEVEL_5_NO DESC;


SELECT *
 FROM (SELECT * FROM T_LUCKY_ITEM T
 ORDER BY DBMS_RANDOM.VALUE())
WHERE ROWNUM<5;

SELECT TB_ITEM.BILL_ID,TB_ITEM.SEQ
 FROM (SELECT T.BILL_ID,ROWNUM AS SEQ
         FROM T_LUCKY_ITEM T 
        WHERE T.ORG_ID IS NULL 
          AND T.BILL_LEVEL = 1
         ORDER BY T.BILL_ID
        )TB_ITEM
 WHERE TB_ITEM.SEQ <= 15;

/*
SELECT * FROM DBA_SOURCE T WHERE NAME = 'DBMS_RANDOM';
SELECT DBMS_RANDOM.VALUE(1,9999)FROM DUAL;
*/
/*
INSERT INTO T_LUCKY_ITEM(BILL_ID,BILL_LEVEL)
SELECT '5'||BILL_ID, 5 
FROM T_LUCKY_ITEM
WHERE BILL_LEVEL = 1
AND ROWNUM <=20;*/

　　

CREATE OR REPLACE PROCEDURE SP_LUCKY_NO_ASSIGN
/*
  功能：渠道靓号分配
  日期：2023-01-11
*/
AS

  V_LEVEL_1 PLS_INTEGER;
  V_LEVEL_2 PLS_INTEGER;
  V_LEVEL_3 PLS_INTEGER;
  V_LEVEL_4 PLS_INTEGER;
  V_LEVEL_5 PLS_INTEGER;
  V_CNT     PLS_INTEGER;
  
BEGIN
  
   --渠道优先级：靓号越多越优先
   FOR V IN(SELECT T.ORG_ID,T.BILL_LEVEL_1_NO,T.BILL_LEVEL_2_NO,T.BILL_LEVEL_3_NO,T.BILL_LEVEL_4_NO,T.BILL_LEVEL_5_NO
              FROM T_ORG T
              ORDER BY T.BILL_LEVEL_1_NO+T.BILL_LEVEL_2_NO+T.BILL_LEVEL_3_NO+T.BILL_LEVEL_4_NO+T.BILL_LEVEL_5_NO DESC
             ) LOOP
             
       V_LEVEL_1 := V.BILL_LEVEL_1_NO; 
       V_LEVEL_2 := V.BILL_LEVEL_2_NO;
       V_LEVEL_3 := V.BILL_LEVEL_3_NO;
       V_LEVEL_4 := V.BILL_LEVEL_4_NO;
       V_LEVEL_5 := V.BILL_LEVEL_5_NO; 
       
       --判断号码是否全部分配
       SELECT COUNT(1)
         INTO V_CNT
         FROM T_LUCKY_ITEM T
        WHERE T.ORG_ID IS NULL;
     
      IF V_CNT = 0 THEN
         RETURN;  --结束循环
         
      ELSE        
        --一级分配
        IF V_LEVEL_1 > 0 THEN
        
           DELETE FROM T_LUCKY_ASSIGN;
           INSERT INTO T_LUCKY_ASSIGN(BILL_ID)          
           SELECT TB_ITEM.BILL_ID
           FROM (SELECT T.BILL_ID,ROWNUM AS SEQ
                   FROM T_LUCKY_ITEM T 
                  WHERE T.ORG_ID IS NULL 
                    AND T.BILL_LEVEL = 1
                   ORDER BY T.BILL_ID
                  )TB_ITEM
           WHERE TB_ITEM.SEQ <= V_LEVEL_1;
           COMMIT;
            
            UPDATE T_LUCKY_ITEM T
               SET T.ORG_ID = V.ORG_ID
            WHERE T.BILL_ID IN(SELECT BILL_ID FROM T_LUCKY_ASSIGN);
            COMMIT;  
        END IF;
        
        --二级分配
        IF V_LEVEL_2 > 0 THEN
        
           DELETE FROM T_LUCKY_ASSIGN;
           INSERT INTO T_LUCKY_ASSIGN(BILL_ID)          
           SELECT TB_ITEM.BILL_ID
           FROM (SELECT T.BILL_ID,ROWNUM AS SEQ
                   FROM T_LUCKY_ITEM T 
                  WHERE T.ORG_ID IS NULL 
                    AND T.BILL_LEVEL = 2
                   ORDER BY T.BILL_ID
                  )TB_ITEM
           WHERE TB_ITEM.SEQ <= V_LEVEL_2;
           COMMIT;
            
            UPDATE T_LUCKY_ITEM T
               SET T.ORG_ID = V.ORG_ID
            WHERE T.BILL_ID IN(SELECT BILL_ID FROM T_LUCKY_ASSIGN);
            COMMIT;
        END IF;
        
        --三级分配
        IF V_LEVEL_3 > 0 THEN
        
           DELETE FROM T_LUCKY_ASSIGN;
           INSERT INTO T_LUCKY_ASSIGN(BILL_ID)          
           SELECT TB_ITEM.BILL_ID
           FROM (SELECT T.BILL_ID,ROWNUM AS SEQ
                   FROM T_LUCKY_ITEM T 
                  WHERE T.ORG_ID IS NULL 
                    AND T.BILL_LEVEL = 3
                   ORDER BY T.BILL_ID
                  )TB_ITEM
           WHERE TB_ITEM.SEQ <= V_LEVEL_3;
           COMMIT;
            
            UPDATE T_LUCKY_ITEM T
               SET T.ORG_ID = V.ORG_ID
            WHERE T.BILL_ID IN(SELECT BILL_ID FROM T_LUCKY_ASSIGN);
            COMMIT;
        END IF;
        
        --四级分配
        IF V_LEVEL_4 > 0 THEN
        
           DELETE FROM T_LUCKY_ASSIGN;
           INSERT INTO T_LUCKY_ASSIGN(BILL_ID)          
           SELECT TB_ITEM.BILL_ID
           FROM (SELECT T.BILL_ID,ROWNUM AS SEQ
                   FROM T_LUCKY_ITEM T 
                  WHERE T.ORG_ID IS NULL 
                    AND T.BILL_LEVEL = 4
                   ORDER BY T.BILL_ID
                  )TB_ITEM
           WHERE TB_ITEM.SEQ <= V_LEVEL_4;
           COMMIT;
            
            UPDATE T_LUCKY_ITEM T
               SET T.ORG_ID = V.ORG_ID
            WHERE T.BILL_ID IN(SELECT BILL_ID FROM T_LUCKY_ASSIGN);
            COMMIT;
        END IF;
        
        --五级分配
        IF V_LEVEL_5 > 0 THEN
        
           DELETE FROM T_LUCKY_ASSIGN;
           INSERT INTO T_LUCKY_ASSIGN(BILL_ID)          
           SELECT TB_ITEM.BILL_ID
           FROM (SELECT T.BILL_ID,ROWNUM AS SEQ
                   FROM T_LUCKY_ITEM T 
                  WHERE T.ORG_ID IS NULL 
                    AND T.BILL_LEVEL = 5
                   ORDER BY T.BILL_ID
                  )TB_ITEM
           WHERE TB_ITEM.SEQ <= V_LEVEL_5;
           COMMIT;
            
            UPDATE T_LUCKY_ITEM T
               SET T.ORG_ID = V.ORG_ID
            WHERE T.BILL_ID IN(SELECT BILL_ID FROM T_LUCKY_ASSIGN);
            COMMIT;
        END IF;
         
     END IF;

   END LOOP;
END;