17,081
社区成员
发帖
与我相关
我的任务
分享
--这个才是最终版:
CREATE OR REPLACE TRIGGER trg_insert_my_tb
BEFORE INSERT ON my_tb
FOR EACH ROW
DECLARE
v_status my_tb.status%TYPE;
cnt1 NUMBER;
cnt2 NUMBER;
cnt3 NUMBER;
BEGIN
BEGIN
select count(*) into cnt1 from my_tb where status = 1;
EXCEPTION WHEN OTHERS THEN
cnt1:=0;
END;
BEGIN
select count(*) into cnt2 from my_tb where status = 2;
EXCEPTION WHEN OTHERS THEN
cnt2:=0;
END;
BEGIN
select count(*) into cnt3 from my_tb where status = 3;
EXCEPTION WHEN OTHERS THEN
cnt3:=0;
END;
IF cnt1=cnt2 AND cnt1=cnt3 AND cnt1=0 THEN
select ceil(dbms_random.value(0,3)) INTO v_status from dual;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3!=0 THEN
BEGIN
IF cnt1<=cnt2 AND cnt1<=cnt3 THEN
v_status:=1;
ELSIF cnt2<=cnt1 AND cnt2<=cnt3 THEN
v_status:=2;
ELSE
v_status:=3;
END IF ;
END;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3=0 THEN
v_status:=3;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3!=0 THEN
v_status:=2;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3!=0 THEN
v_status:=1;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3=0 THEN
select ceil(dbms_random.value(1,3)) into v_status from dual;
ELSIF cnt1=0 AND cnt2=0 AND cnt3!=0 THEN
select ceil(dbms_random.value(0,2)) into v_status from dual;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3=0 THEN
begin
select ceil(dbms_random.value(0,2)) into v_status from dual;
IF v_status=1 THEN --如果为1则取1,否则取3
v_status:=1;
ELSE
v_status:=3;
END IF;
END;
END IF;
:NEW.status:=v_status;
END;
/
--楼主,我的还有个小问题,就是在随机取值的时候不对,改成我下面这样随机取:
--1、2、3中随即取一个:
SELECT Ceil(dbms_random.value(0,3)) into v_status FROM dual;
--2、3之间随即取一个
select CEIL(dbms_random.value(1,3)) into v_status from dual;
--1、2之间随即取一个
select CEIL(dbms_random.value(0,2)) into v_status from dual;
--1、3之间随即取一个,则外层套一个连续区间
BEGIN
select CEIL(dbms_random.value(0,2)) into v_status from dual;
IF v_status=1 THEN --如果为1则取1,否则取3
v_status:=1;
ELSE
v_status:=3;
END;
你将我上面13楼中trigger里作相应的改动下就OK了
--这个是最终结果,我测试通过了,具体你再测试下:
CREATE OR REPLACE TRIGGER trg_insert_my_tb
BEFORE INSERT ON my_tb
FOR EACH ROW
DECLARE
v_status my_tb.status%TYPE;
cnt1 NUMBER;
cnt2 NUMBER;
cnt3 NUMBER;
BEGIN
BEGIN
select count(*) into cnt1 from my_tb where status = 1;
EXCEPTION WHEN OTHERS THEN
cnt1:=0;
END;
BEGIN
select count(*) into cnt2 from my_tb where status = 2;
EXCEPTION WHEN OTHERS THEN
cnt2:=0;
END;
BEGIN
select count(*) into cnt3 from my_tb where status = 3;
EXCEPTION WHEN OTHERS THEN
cnt3:=0;
END;
IF cnt1=cnt2 AND cnt1=cnt3 AND cnt1=0 then
select ceil(dbms_random.value(1,3)) INTO v_status from dual;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3!=0 THEN
BEGIN
IF cnt1<=cnt2 AND cnt1<=cnt3 THEN
v_status:=1;
ELSIF cnt2<=cnt1 AND cnt2<=cnt3 THEN
v_status:=2;
ELSE
v_status:=3;
END IF ;
END;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3=0 THEN
v_status:=3;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3!=0 THEN
v_status:=2;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3!=0 THEN
v_status:=1;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3=0 THEN
select ceil(dbms_random.value(2,3)) INTO v_status from dual;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3=0 THEN
select ceil(dbms_random.value(1,3)) INTO v_status from dual;
ELSIF cnt1=0 AND cnt2=0 AND cnt3!=0 THEN
select ceil(dbms_random.value(1,2)) INTO v_status from dual;
END IF;
:NEW.status:=v_status;
END;
--上面的忧郁没测试,创建trigger时候有问题,下面的是创建成功了的,你试试
CREATE OR REPLACE TRIGGER trg_insert_my_tb
BEFORE INSERT ON my_tb
FOR EACH ROW
DECLARE
v_status my_tb.status%TYPE;
cnt1 NUMBER;
cnt2 NUMBER;
cnt3 NUMBER;
BEGIN
BEGIN
select count(*) into cnt1 from my_tb where status = 1;
EXCEPTION WHEN OTHERS THEN
cnt1:=0;
END;
BEGIN
select count(*) into cnt2 from my_tb where status = 2;
EXCEPTION WHEN OTHERS THEN
cnt2:=0;
END;
BEGIN
select count(*) into cnt3 from my_tb where status = 3;
EXCEPTION WHEN OTHERS THEN
cnt3:=0;
END;
IF cnt1=cnt2 AND cnt1=cnt3 AND cnt1=0 then
select ceil(dbms_random.value(1,3)) INTO v_status from dual;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3!=0 THEN
SELECT status INTO v_status FROM (
SELECT status,Count(status) cnt,Row_Number()over(PARTITION BY status ORDER BY Count(status)) rn FROM my_tb GROUP BY status)
WHERE rn=1;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3=0 THEN
v_status:=3;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3!=0 THEN
v_status:=2;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3!=0 THEN
v_status:=1;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3=0 THEN
select ceil(dbms_random.value(1,2)) INTO v_status from dual;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3!=0 THEN
select ceil(dbms_random.value(2,3)) INTO v_status from dual;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3=0 THEN
select ceil(dbms_random.value(1,3)) INTO v_status from dual;
END IF;
:NEW.status:=v_status;
END;
/
--初步写了个,思路跟楼上一样,我用trigger写的,试试,我还没测试过
CREATE OR REPLACE TRIGGER trg_insert_my_tb
BEFORE INSERT ON my_tb
DECLARE
v_status my_tb.status%TYPE;
cnt1 NUMBER;
cnt2 NUMBER;
cnt3 NUMBER;
BEGIN
BEGIN
select count(*) into cnt1 from my_tb where status = 1;
EXCEPTION WHEN OTHERS THEN
cnt1:=0;
END;
BEGIN
select count(*) into cnt2 from my_tb where status = 2;
EXCEPTION WHEN OTHERS THEN
cnt2:=0;
END;
BEGIN
select count(*) into cnt3 from my_tb where status = 3;
EXCEPTION WHEN OTHERS THEN
cnt3:=0;
END;
IF cnt1=cnt2 AND cnt1=cnt3 AND cnt1=0 then
select ceil(dbms_random.value(1,3)) INTO v_status from dual;
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3!=0 THEN
SELECT status INTO v_status FROM (SELECT status,Count(status) cnt,Row_Number()over(PARTITION BY status ORDER BY Count(status)) rn FROM a GROUP BY status) WHERE rn=1);
ELSIF cnt1!=0 AND cnt2!=0 AND cnt3=0 THEN
v_status:=3;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3!=0 THEN
v_status:=2;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3!=0 THEN
v_status:=1;
ELSIF cnt1=0 AND cnt2=0 AND cnt3!=0 THEN
select ceil(dbms_random.value(1,2)) INTO v_status from dual;
ELSIF cnt1!=0 AND cnt2=0 AND cnt3=0 THEN
select ceil(dbms_random.value(2,3)) INTO v_status from dual;
ELSIF cnt1=0 AND cnt2!=0 AND cnt3=0 THEN
select ceil(dbms_random.value(1,3)) INTO v_status from dual;
END IF;
:NEW.status:=v_status;
END;