我怎么能把查询解码功能?

Question

create table ss( no number, filepath varchar2(300) )

我想在这个表中有5个或更少的'no'重复值

当select count(no) from ss where no=#{no} <5,insert into ss values({no},{filepath})

所以'no'的重复值不能超过5。我该怎么做呢?

Answer 1

您可以创建一个类似的触发器来实现这个逻辑:

CREATE OR REPLACE TRIGGER set_no_ss_tbl_trg
BEFORE INSERT ON ss_tbl
FOR EACH ROW
BEGIN
DECLARE
l_cnt_no NUMBER;
BEGIN
SELECT COUNT(1)
INTO l_exceeding
FROM g_piece
WHERE refdoss = :new.no;

IF l_cnt_no > 5 THEN 
SELECT MIN(no)
INTO :new.no
FROM (SELECT COUNT(1), no 
FROM ss_tbl
GROUP BY no 
HAVING COUNT(1) + 1 <= 5);      

END IF;
END;
END;