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;
All for u