--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;
标签：置靓,NO,LEVEL,LUCKY,ITEM,随机,BILL,BRD,ID From： https://www.cnblogs.com/ayumie/p/17043192.html