oracle12C数据库JSON的应用 --PL/SQL存储过程中JSON解析应用篇

心已赠人 2022-05-09 01:14 1618阅读 0赞

最近有些项目在数据库oracle中使用到json格式的数据交互，对于oracle12以上的版本都支持JSON格式数据的使用，刚刚接触的猿们可以到上一篇[oracle数据库使用json的简单入门][oracle_json]，最好参考官方的API文档[关于oracle官方API文档对JSON的支持][oracle_API_JSON]；  
下面就比较详细的说一下，关于oracle数据库在存储过程中的JSON解析应用：  
***特别说明一下在目前版本下，存储过程里面解析的JSON里面key对应的value不能包含中文与其他字符混合，纯中文是可以的***

**一：先上一段存储过程解析JSON的存储过程，看你们能不能看的懂。**  
看不懂没关系，后面我会慢慢的分析怎么解析json。

/* JSON_IN 的json格式(仅供参考)： { "Flag": "xxx", "param_in": [{ "PROD_PLANT": "xxx", "MODEL_NAME": "xxx" }], "After_action": "xxx", "tableName": "xxx", "tableIndex": "xxx", "curSchema": "xxx", "funCode": "xxx", "Button": "xxx", "empNo": "xxx" }*/
    CREATE OR REPLACE PROCEDURE  PRC_JSON_TEST(JSON_IN    IN VARCHAR2, RES_OUT     OUT VARCHAR2)  IS
      JSON_OBJ             JSN;
      PARAM_IN_LIST        JSN_LIST;
      PARAM_OBJ            JSN;
      PROD_PLANT_P         C_EMP_T.PLANT%TYPE;
      MODEL_NAME_P         C_NAME_T.NAME%TYPE;
      cnt_p                NUMBER(10);
      LOCATION_P VARCHAR2(10);
      OPERATE_P VARCHAR2(10);
      PROD_CATEGORY_NAME_p VARCHAR2(40);
      O_PROD_CATEGORY_NAME_p VARCHAR2(40);
      I NUMBER(5);
    begin
      RES_OUT := '{"dd":"aa","ss":"ff"}';
      --解析JSON參數
      BEGIN
        JSON_OBJ := JSN(JSON_IN);
        IF NOT
            ((REPLACE(JSON_OBJ.get('Flag').TO_CHAR, '"', '') = 'action1') AND
            ((REPLACE(JSON_OBJ.get('Button').TO_CHAR, '"', '') = 'ADD') OR
             (REPLACE(JSON_OBJ.get('Button').TO_CHAR, '"', '') = 'UPDATE'))) THEN
            IF NOT
                ((REPLACE(JSON_OBJ.get('Flag').TO_CHAR, '"', '') = 'action1') AND
                (REPLACE(JSON_OBJ.get('Button').TO_CHAR, '"', '') = 'ADD' )) THEN      
              RETURN;--返回OUT参数RES
            END IF;     
         END IF;
        LOCATION_P := REPLACE(JSON_OBJ.get('Flag').TO_CHAR, '"', '');    
        OPERATE_P := REPLACE(JSON_OBJ.get('Button').TO_CHAR, '"', '');
        PARAM_IN_LIST := JSN_LIST(JSON_OBJ.get('param_in').TO_CHAR); 
        I := 1;
        WHILE I <= PARAM_IN_LIST.COUNT LOOP
          PARAM_OBJ := JSN(PARAM_IN_LIST.GET(I).TO_CHAR);
          MODEL_NAME_P := PARAM_OBJ.get('MODEL_NAME').TO_CHAR;
          MODEL_NAME_P := REPLACE(MODEL_NAME_P, '"', '');
          PROD_PLANT_P := PARAM_OBJ.get('PROD_PLANT').TO_CHAR;
          PROD_PLANT_P := REPLACE(PROD_PLANT_P, '"', '');
          PROD_CATEGORY_NAME_P := PARAM_OBJ.get('PROD_CATEGORY_NAME').TO_CHAR;
          PROD_CATEGORY_NAME_P := REPLACE(PROD_CATEGORY_NAME_P, '"', ''); 
          IF ((LOCATION_P='action1') AND (OPERATE_P='ADD')) THEN
            BEGIN
              SELECT count(model_name)
                INTO cnt_p
                FROM C_MODEL_DESC_T A
               WHERE A.MODEL_NAME = MODEL_NAME_P AND ROWNUM=1;
              IF cnt_p=0 THEN
                  INSERT INTO C_MODEL_DESC_T
                    (MODEL_NAME, CREATE_EMP)
                    SELECT A.MODEL_NAME, A.CREATE_EMP
                      FROM C_PROD_PLANT_MODEL_T A
                     WHERE PROD_PLANT = PROD_PLANT_P
                       AND MODEL_NAME = MODEL_NAME_P;
              END IF;
            EXCEPTION
              WHEN OTHERS THEN
               DELETE FROM C_PROD_PLANT_MODEL_T A
                     WHERE PROD_PLANT = PROD_PLANT_P
                       AND MODEL_NAME = MODEL_NAME_P;
            END;
                UPDATE C_PROD_PLANT_MODEL_T
                SET PROD_CATEGORY_NAME = PROD_CATEGORY_NAME_P
                WHERE MODEL_NAME = MODEL_NAME_P
                AND PROD_CATEGORY_NAME IS NULL;    
            COMMIT;
          ELSIF ((LOCATION_P='action1') AND (OPERATE_P='UPDATE')) THEN  
            BEGIN
              SELECT PROD_CATEGORY_NAME 
              INTO O_PROD_CATEGORY_NAME_p 
              FROM C_PROD_PLANT_MODEL_T
              WHERE MODEL_NAME = MODEL_NAME_P
              AND PROD_CATEGORY_NAME IS NOT NULL
              AND ROWNUM = 1;
              IF O_PROD_CATEGORY_NAME_P <> PROD_CATEGORY_NAME_P THEN
                UPDATE C_PROD_PLANT_MODEL_T
                SET PROD_CATEGORY_NAME = PROD_CATEGORY_NAME_P
                WHERE MODEL_NAME = MODEL_NAME_P;
              ELSE 
                UPDATE C_PROD_PLANT_MODEL_T
                SET PROD_CATEGORY_NAME = PROD_CATEGORY_NAME_P
                WHERE MODEL_NAME = MODEL_NAME_P
                AND PROD_CATEGORY_NAME IS NULL;         
              END IF;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                UPDATE C_PROD_PLANT_MODEL_T
                SET PROD_CATEGORY_NAME = PROD_CATEGORY_NAME_P
                WHERE MODEL_NAME = MODEL_NAME_P
                AND PROD_CATEGORY_NAME IS NULL;    
            END;    
            COMMIT;
          ELSIF ((LOCATION_P='action2') AND (OPERATE_P='ADD')) THEN  
            BEGIN
              SELECT PROD_CATEGORY_NAME 
              INTO O_PROD_CATEGORY_NAME_p 
              FROM C_PROD_PLANT_MODEL_T
              WHERE MODEL_NAME = MODEL_NAME_P
              AND PROD_CATEGORY_NAME IS NOT NULL
              AND ROWNUM = 1;
              IF O_PROD_CATEGORY_NAME_P <> PROD_CATEGORY_NAME_P THEN
                RES_O := '{"aa":"bb","cc":"dd '||O_PROD_CATEGORY_NAME_P||'"}';
                RETURN;
              END IF;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                RETURN;
            END; 
          END IF;      
          I := I+1;
        END LOOP;    
      EXCEPTION
        WHEN OTHERS THEN
          RES_O := '{"aa":"bb","cc":"dd:' ||
                 REPLACE(dbms_utility.format_error_backtrace(), '"', '') || '"}';
          ROLLBACK;
          RETURN;
      END;
    end;

**二.详细解析在oracle中使用json**  
从简单的说起。。。  
1.先创建一个存储过程：

CREATE OR REPLACE PROCEDURE PRC_JSON_TEST(param_in    IN VARCHAR2, result_out  OUT VARCHAR2)

包含两个参数，一个IN参数：用来接受传入json格式字符串数据的参数；一个OUT参数：用来返回输出执行存储过程之后的结果信息。  
2.声明变量，用于接受解析json变量(需要根据自己实际需要去声明变量)

count             BINARY_INTEGER;                            --声明一个有符号整型变量count
      json_obj      JSN;                                                     --声明一个JSON对象变量json_obj
      param_obj     JSN;                                                   --声明一个JSON对象变量param_obj
      KIND_NAME_V   C_DUTY_T.KIND_NAME%TYPE;  --声明一个类型是某张表某个字段类型属性的变量
      PARAM_IN_LIST JSN_LIST;                                     --声明一个JSON数组变量
      i NUMBER(5);

说明一下：param\_in传入的JSON字符数据的参数，其格式可以包含多种（基本的格式只有两种，一种是json数组，另一种是json对象；两者可以相互嵌套），有JSONArray嵌套JSONObject或相互嵌套、或JSONObject中的value值又嵌套JSONObject或JSONArray等等。本次我使用的json格式是JSONObject中的value嵌套JSONArray和JSONObject:

/* param_in 传入的json参数*/
      {
        "param_in": [{
          "PROD": "CA00",
          "NAME": "123123"
        }],
        "name": "tiger",
        "age": "one",
        "sex": "1",
        "address": "232",
      }

3.解析json过程，实现自己的功能逻辑

--解析传入json格式字符串并赋值给json对象变量,使用JSN()解析
    json_obj :=JSN(param_in);
    
    --使用get()方法获取json_obj中key对应的value值，其value值是个json数组列表,使用JSN_LIST()解析
    PARAM_IN_LIST  ：=JSN_LIST(json_obj.get('param_in').TO_CHAR);
    
    --获取json数组下标为1的值，其值是json对象，使用JSN()解析；
    param_obj  ：=JSN(PARAM_IN_LIST.GET(1).TO_CHAR);
    
    --当然如果是json数组多个值的话，可以使用循环遍历,下面使用 WHILE ..LOOP..方法，你也可以使用FOR..IN(..)...LOOP方法遍历
    --PARAM_IN_LIST.COUNT获取json数组的长度
    WHILE i<=PARAM_IN_LIST.COUNT LOOP
         --自己的逻辑功能
         END LOOP

**后面就可以参考上面的基本方法，回头自己研究上上面的存储过程了**

[oracle_json]: https://blog.csdn.net/use_admin/article/details/81485317
[oracle_API_JSON]: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-9B4D323C-079F-488A-A675-81B9730C4CE3