IG和collection结合使用

[[Oracle APEX]]

背景

在录入人员信息时,存在人员信息与入职记录/证照信息,这样的主从或一对多的同时录入场景.具体信息如下
人员信息

ID	姓名	性别	年龄
1	三儿	男	19
2	四儿	男	49
3	五儿	女	29

人员证件信息

ID	userid	证件	证件号	开始时间	结束时间
1	1	身份证	xxxxxx	2020-01-01	2030-01-01
2	1	健康证	xxxxxx	2020-01-01	2030-01-01
3	2	身份证	xxxxxx	2020-01-01	2030-01-01
4	2	健康证	xxxxxx	2020-01-01	2030-01-01
5	2	护照	xxxxxx	2020-01-01	2030-01-01
6	3	电工证	xxxxxx	2020-01-01	2030-01-01

表语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67


CREATE TABLE demo666_user_info
(
    ID         NUMBER        NOT NULL
        CONSTRAINT demo666_user_info_PK PRIMARY KEY,
    NAME       VARCHAR2(255),
    sex        VARCHAR2(255),
    age        VARCHAR2(255),
    REMARK     VARCHAR2(4000),
    CREATED    DATE          NOT NULL,
    CREATED_BY VARCHAR2(255) NOT NULL,
    UPDATED    DATE          NOT NULL,
    UPDATED_BY VARCHAR2(255) NOT NULL
)
/
CREATE SEQUENCE demo666_user_info_SEQ;
CREATE OR REPLACE TRIGGER demo666_user_info_BIU
    BEFORE INSERT OR UPDATE
    ON demo666_user_info
    FOR EACH ROW
BEGIN
    IF :new.id IS NULL THEN
        :new.id := demo666_user_info_SEQ.nextval;
    END IF;
    IF INSERTING THEN
        :new.created := SYSDATE;
        :new.created_by := NVL(SYS_CONTEXT('APEX$SESSION', 'APP_USER'), USER);
    END IF;
    :new.updated := SYSDATE;
    :new.updated_by := NVL(SYS_CONTEXT('APEX$SESSION', 'APP_USER'), USER);
END demo666_user_info_BIU;

CREATE TABLE demo666_user_certificate
(
    ID         NUMBER        NOT NULL
        CONSTRAINT demo666_user_certificate_PK PRIMARY KEY,
    NAME       VARCHAR2(255),
    code       VARCHAR2(255),
    user_id number,
    start_date date,
    end_date   date,
    REMARK     VARCHAR2(4000),
    CREATED    DATE          NOT NULL,
    CREATED_BY VARCHAR2(255) NOT NULL,
    UPDATED    DATE          NOT NULL,
    UPDATED_BY VARCHAR2(255) NOT NULL
)
/
ALTER TABLE  "DEMO666_USER_CERTIFICATE" ADD CONSTRAINT "DEMO666_USER_CERTIFICATE_CON" FOREIGN KEY ("USER_ID")
	  REFERENCES  "DEMO666_USER_INFO" ("ID") ON DELETE CASCADE ENABLE;
CREATE SEQUENCE demo666_user_certificate_SEQ;

CREATE OR REPLACE TRIGGER demo666_user_certificate_BIU
    BEFORE INSERT OR UPDATE
    ON demo666_user_certificate
    FOR EACH ROW
BEGIN
    IF :new.id IS NULL THEN
        :new.id := demo666_user_certificate_SEQ.nextval;
    END IF;
    IF INSERTING THEN
        :new.created := SYSDATE;
        :new.created_by := NVL(SYS_CONTEXT('APEX$SESSION', 'APP_USER'), USER);

    END IF;
    :new.updated := SYSDATE;
    :new.updated_by := NVL(SYS_CONTEXT('APEX$SESSION', 'APP_USER'), USER);
END demo666_user_certificate_BIU;

操作

人员信息的录入.

先将人员的基础信息录入建好

OsXM1-2024-08-23-13-17-30 OsXM1-2024-08-23-13-19-58 OsXM1-2024-08-23-13-21-52

将证件信息录入

OsXM1-2024-08-23-13-36-13

将证件信息嵌入人员信息中

初始化时将该人的信息加载到collection中

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44


DECLARE
v_rule_ID    number         := :P80_ID;
v_table_name varchar2(1000) := 'USER_INFO_CERT';
l_exists     boolean;
BEGIN
    l_exists := APEX_COLLECTION.COLLECTION_EXISTS(
            p_collection_name => v_table_name);
    IF l_exists THEN
        APEX_COLLECTION.DELETE_COLLECTION(
                p_collection_name => v_table_name);
    END IF;

    IF v_rule_ID IS NOT NULL THEN
        APEX_COLLECTION.CREATE_COLLECTION(
                p_collection_name => v_table_name);
        FOR i IN (SELECT id,
                        user_id,
                        NAME,
                        code,
                        start_date,
                        end_date,
                        remark
                FROM demo666_user_certificate
                WHERE user_id = v_rule_ID
            )
            LOOP
                APEX_COLLECTION.ADD_MEMBER(
                        p_collection_name => v_table_name,
                        p_c001 => i.ID,
                        p_c002 => i.user_id,
                        p_c003 => i.NAME,
                        p_c004 => i.code,
                        p_c005 => i.REMARK,
                        p_d001=> i.start_date,
                        p_d002=> i.end_date

                );
            END LOOP;
        COMMIT;
    ELSE
        APEX_COLLECTION.CREATE_COLLECTION(
                p_collection_name => v_table_name);
    END IF;
END;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


SELECT 
    seq_id,
    c001 AS ID,
        c002 AS user_id,
        c003 AS NAME,
        c004 AS code,
        d001 AS start_date,
        d002 AS end_date,
        c005 AS REMARK,
        c049 as 标志
    FROM APEX_COLLECTIONS
    WHERE collection_name = 'USER_INFO_CERT'
    AND (c049 != 'delete' or c049 is  null) ;

复制证件的录入页
源改为sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


SELECT seq_id,
    c001 AS cert_ID,
    c002 AS user_id,
    c003 AS NAME,
    c004 AS code,
    d001 AS start_date,
    d002 AS end_date,
    c005 AS REMARK

FROM APEX_COLLECTIONS
WHERE collection_name = 'USER_INFO_CERT'

seq_id 设为主键. seq_id,cert_ID,user_id 设为隐藏 OsXM1-2024-08-23-13-59-12

处理添加

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


BEGIN
    APEX_COLLECTION.ADD_MEMBER(
            p_collection_name => 'USER_INFO_CERT',
            p_c001 => :P83_CERT_ID,
            p_c002 => :P83_USER_ID,
            p_c003 => :P83_NAME,
            p_c004 => :P83_CODE,
            p_c005 => :P83_REMARK,

            p_d001 => :P83_START_DATE,
            p_d002 => :P83_END_DATE,
            p_c049 =>'add'
    );
END;

更新

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


DECLARE
    v_flag varchar2(100);
BEGIN
    IF :P83_CERT_ID IS NOT NULL THEN
        v_flag := 'save';
    ELSE
        v_flag := 'add';
    END IF;
    APEX_COLLECTION.UPDATE_MEMBER(
            p_collection_name => 'USER_INFO_CERT',
            p_seq => :P83_SEQ_ID,
            p_c001 => :P83_CERT_ID,
            p_c002 => :P83_USER_ID,
            p_c003 => :P83_NAME,
            p_c004 => :P83_CODE,
            p_c005 => :P83_REMARK,
            p_d001 => :P83_START_DATE,
            p_d002 => :P83_END_DATE,
            p_c049 => v_flag
    );
END;

删除

1
2
3
4
5
6
7
8


BEGIN
    APEX_COLLECTION.update_member_attribute(
        p_collection_name => 'USER_INFO_CERT',
        p_seq => :P83_SEQ_ID,
        p_attr_number => 49,
        p_attr_value => 'delete'
);
END;

OsXM1-2024-08-23-14-09-19

员工保存后将记录证件信息

OsXM1-2024-08-23-17-34-08

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37


DECLARE
    v_user_id varchar2(1000) := :P80_ID;
    v_coll    varchar2(1000) := 'USER_INFO_CERT';
BEGIN

    FOR c1 IN (
        SELECT seq_id,
            c001 AS cert_ID,
            c002 AS user_id,
            c003 AS NAME,
            c004 AS code,
            d001 AS start_date,
            d002 AS end_date,
            c005 AS REMARK,
            c049 AS ap
        FROM APEX_COLLECTIONS
        WHERE collection_name = v_coll)
        LOOP
            IF c1.ap = 'add' THEN
                INSERT INTO demo666_user_certificate (user_id, name, code, start_date, end_date, remark)
                VALUES (v_user_id, c1.NAME, c1.code, c1.start_date, c1.end_date, c1.REMARK);
            ELSIF c1.ap = 'save' THEN
                UPDATE demo666_user_certificate
                SET name       = c1.NAME,
                    code       = c1.code,
                    start_date = c1.start_date,
                    end_date   = c1.end_date,
                    remark     = c1.REMARK
                WHERE id = c1.cert_ID;
            ELSIF c1.ap = 'delete' THEN
                -- 2. 删除本数据
                DELETE FROM demo666_user_certificate WHERE ID = c1.cert_ID;
            END IF;
        END LOOP;
    apex_collection.delete_collection(p_collection_name => v_coll);
    COMMIT;
END ;