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

DECLARE
    v_request_clob            clob;
    l_response_object         json_object_t;
    l_response_object_array   JSON_ARRAY_T;
    l_response_array          json_array_t;
    l_response_array2         json_array_t;
    l_response_array2_element json_element_t;
    li_obj                    JSON_OBJECT_T;
    li_obj2                   JSON_OBJECT_T;
    keys                      JSON_KEY_LIST;
    keys2                     JSON_KEY_LIST;
    l_1id                     number;
    l_2id                     number;
    l_3id                     number;
    l_4id                     number;
BEGIN
    v_request_clob := q'~
    {  "item": [   { "上海": [ { "浦东新区": ["张江1","张江2"]},{"闵行": ["闵行1","张江2"]}]}]}
    ~';
    --     v_request_clob1 := q'~
    --     {  "item": [   { "上海": [ { "浦东新区": ["张江1","张江2"],"我是34行的示例":"我是34行的示例1"},{"闵行": ["闵行1","张江2"]}]}]}
    --     ~';
    l_response_object := JSON_OBJECT_T.parse(v_request_clob);
    l_response_object_array := l_response_object.get_Array('item'); -- "item":[{"上海":[]}]
    FOR i IN 0 .. (l_response_object_array.get_size - 1)
        LOOP
            li_obj := JSON_OBJECT_T(l_response_object_array.get(i)); -- {"上海":[]}
            keys := li_obj.get_keys;
            FOR i1 IN 1..keys.COUNT
                LOOP
                    dbms_output.put_line('----------1---------------' || keys(i1));-- 上海
                    l_response_array := li_obj.get_Array(keys(i1)); -- []
                    FOR i2 IN 0 .. (l_response_array.get_size - 1)
                        LOOP
                            li_obj2 := JSON_OBJECT_T(l_response_array.get(i2)); -- {"浦东新区":[]}
                            keys2 := li_obj2.get_keys;
                            FOR i3 IN 1..keys2.COUNT
                                LOOP
                                    dbms_output.put_line('----------2---------------' || keys2(i3));
                                    -- 浦东新区
                                    --                                    dbms_output.put_line( '----------2.1------我是34行的示例---------' || li_obj2.get_string(keys2(i3)));
                                    l_response_array2 := li_obj2.get_Array(keys2(i3)); -- []
                                    FOR i4 IN 0 .. (l_response_array2.get_size - 1)
                                        LOOP
                                            l_response_array2_element := l_response_array2.get(i4); -- 张江
                                            dbms_output.put_line('----------3---------------' || l_response_array2_element.to_string);
                                            SELECT MAX(id) INTO l_1id FROM covid_regional WHERE parent_id IS NULL AND NAME = KEYS(i1);
                                            IF l_1id IS NOT NULL THEN
                                                SELECT MAX(id) INTO l_2id FROM covid_regional WHERE parent_id = l_1id AND NAME = keys2(i3);
                                                IF l_2id IS NOT NULL THEN
                                                    SELECT MAX(id) INTO l_2id FROM covid_regional WHERE parent_id = l_1id AND NAME = keys2(i3);
                                                ELSE
                                                    INSERT INTO covid_regional (parent_id, level_tag, name)
                                                    VALUES (l_1id, 2, keys2(i3));
                                                END IF;


                                            END IF;

                                        END LOOP;
                                END LOOP;
                        END LOOP;
                END LOOP;


        END LOOP;
END;

 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

DECLARE
    v_request_clob     json_array_t;
    l_response_clob    clob;
    l_response_clob1   clob;
    l_response_array   json_array_t;
    l_response_element json_element_t;
    li_obj             JSON_OBJECT_T;
    l_1id              number;
    l_2id              number;
    l_3id              number;
    l_4id              number;
    l_1id_name         varchar2(4000);
    l_2id_name         varchar2(4000);
    l_3id_name         varchar2(4000);
    l_4id_name         varchar2(4000);
    B64_DE             RAW(32767);
BEGIN

    SELECT -- jt.*
           MAX(jt.City)
    INTO l_4id_name
    FROM JSON_TABLE(:body_text, '$.items[*]' COLUMNS (
        City varchar2(4000) PATH '$.City',
        Region varchar2(4000) PATH '$.Region',
        Street varchar2(4000) PATH '$.Street',
        Committee varchar2(4000) PATH '$.Committee'
        )) jt;
    apex_json.open_object();
    apex_json.write('errcode', '200');
    apex_json.write('errmsg', l_4id_name);
    apex_json.close_object();
    l_response_clob := apex_json.get_clob_output;
    apex_json.free_output;
    sys.HTP.P(l_response_clob);

    :status := 200;
    -- :location := :id;
    --
EXCEPTION
    WHEN OTHERS THEN
        :status := 400;
END;

 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
68
69
70
71
72

DECLARE
    v_request_clob     clob;
    l_response_clob    clob ;
    l_response_array   json_array_t;
    l_response_element json_element_t;
    li_obj             JSON_OBJECT_T;
    l_1id              varchar2(4000);
    l_2id              varchar2(4000);
    l_3id              varchar2(4000);
    l_4id              varchar2(4000);
    l_1id_name         varchar2(4000);
    l_2id_name         varchar2(4000);
    l_3id_name         varchar2(4000);
    l_4id_name         varchar2(4000);
BEGIN
    v_request_clob := :body_text;
    li_obj := JSON_OBJECT_T.parse(v_request_clob);
    l_response_array := li_obj.get_Array('items');
    FOR i IN 0 .. (l_response_array.get_size - 1)
        LOOP
            li_obj := JSON_OBJECT_T(l_response_array.get(i));
            l_1id_name := li_obj.get_string('City'); -- 上海 --1
            l_2id_name := li_obj.get_string('Region');-- 浦东 -- 2
            l_3id_name := li_obj.get_string('Street');-- 镇 -- 3
            l_4id_name := li_obj.get_string('Committee');-- 居委 -- 4
            IF l_3id_name IS NOT NULL THEN
                SELECT MAX(id) INTO l_1id FROM covid_regional WHERE parent_id IS NULL AND NAME = l_1id_name;
                IF l_1id IS NOT NULL THEN
                    SELECT MAX(id) INTO l_2id FROM covid_regional WHERE parent_id = l_1id AND NAME = l_2id_name;
                    IF l_2id IS NOT NULL THEN
                        SELECT MAX(id) INTO l_3id FROM covid_regional WHERE parent_id = l_2id AND NAME = l_3id_name;
                        IF l_3id IS NOT NULL THEN
                            SELECT MAX(id) INTO l_4id FROM covid_regional WHERE parent_id = l_3id AND NAME = l_4id_name;
                            IF l_4id IS NULL THEN
                                INSERT INTO covid_regional (parent_id, level_tag, name, REMARK)
                                VALUES (l_3id, '4', l_4id_name, TO_CHAR(SYSDATE, 'yyyy-mm-dd')); -- 4

                            END IF;
                        ELSE
                            INSERT INTO covid_regional (parent_id, level_tag, name, REMARK)
                            VALUES (l_2id, '3', l_3id_name, TO_CHAR(SYSDATE, 'yyyy-mm-dd')) -- 3
                            RETURNING id INTO l_3id;
                            INSERT INTO covid_regional (parent_id, level_tag, name, REMARK)
                            VALUES (l_3id, '4', l_4id_name, TO_CHAR(SYSDATE, 'yyyy-mm-dd')) -- 4
                            RETURNING id INTO l_4id;

                        END IF;
                    ELSE
                        INSERT INTO covid_regional (parent_id, level_tag, name, REMARK)
                        VALUES (l_1id, '2', l_2id_name, TO_CHAR(SYSDATE, 'yyyy-mm-dd')) -- 2
                        RETURNING id INTO l_2id;

                        INSERT INTO covid_regional (parent_id, level_tag, name, REMARK)
                        VALUES (l_2id, '3', l_3id_name, TO_CHAR(SYSDATE, 'yyyy-mm-dd')) -- 3
                        RETURNING id INTO l_3id;

                        INSERT INTO covid_regional (parent_id, level_tag, name, REMARK)
                        VALUES (l_3id, '4', l_4id_name, TO_CHAR(SYSDATE, 'yyyy-mm-dd')) -- 4
                        RETURNING id INTO l_4id;
                    END IF;
                END IF;
            END IF;
            COMMIT;
        END LOOP;
    :status := 200;
    sys.HTP.P(q'~ {"status":200} ~');
EXCEPTION
    WHEN OTHERS THEN
        :status := 400;
END;