CREATE TABLE TARGET
(
PK INTEGER NOT NULL PRIMARY KEY,
C1 INTEGER
);
CREATE TABLE EXCEPT1
(
PK INTEGER NOT NULL PRIMARY KEY,
C1 INTEGER
);
CREATE TABLE SOURCE
(
PK INTEGER NOT NULL PRIMARY KEY,
C1 INTEGER
);
SELECT * FROM TARGET;
SELECT * FROM EXCEPT1;
SELECT * FROM SOURCE;

-- 创建一个方法
DROP FUNCTION DISCRETIZE;
--<ScriptOptions statementTerminator="@"/>
CREATE FUNCTION DISCRETIZE(RAW INTEGER) RETURNS INTEGER
RETURN CASE
    WHEN RAW < 0 THEN CAST(NULL AS INTEGER)
    WHEN RAW > 1000 THEN NULL
    ELSE ((RAW/10)*10)+5
END

-- 
INSERT INTO SOURCE(PK, C1)
VALUES(1,-5),
(2,NULL),
(3,1200),
(4,23),
(5,10),
(6,876);

SELECT * FROM SOURCE;
SELECT * FROM TARGET;
SELECT * FROM EXCEPT1;

BEGIN ATOMIC
    FOR ROW AS 
        SELECT PK,C1,DISCRETIZE(C1) AS D FROM SOURCE
    DO
        IF ROW.D IS NULL THEN
            INSERT INTO EXCEPT1 VALUES(ROW.PK,ROW.C1);
        ELSE
            INSERT INTO TARGET VALUES(ROW.PK,ROW.D);
        END IF;
    END FOR;
END

--
SELECT * FROM EXCEPT1;
SELECT * FROM TARGET;