例子：数据库传快递信息到外部系统post

huangwenkangz

于 2022-12-16 15:20:40 发布

阅读量503

点赞数

分类专栏： Technicals DB 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/huangwenkangz/article/details/128343301

版权

Technicals 同时被 2 个专栏收录

24 篇文章 0 订阅

订阅专栏

12 篇文章 0 订阅

订阅专栏

procedure cux_sf_prod_procedure(l_om_express_rec cux_om_express_t%rowtype,l_out_sf_number out varchar2,l_out_flag out varchar2) is
    l_soap_request varchar2(32767);
    l_soap_request_header varchar2(32767);
    l_soap_request_header1 varchar2(32767);
    l_leng             NUMBER;
    l_http_req         utl_http.req;
    l_http_resp        utl_http.resp;
    v_timestamp number;
    v_msgDigest varchar2(100);
  --  l_soap_respond_val CLOB;--VARCHAR2(32767);
  --  l_soap_end_val     CLOB;--VARCHAR2(32767);
    l_soap_respond_val VARCHAR2(32767);
    l_soap_end_val     VARCHAR2(32767);
   -- l_resp             CLOB;
   -- l_req_blob      blob;
  --  l_clob CLOB;
    v_isSignBack number;
    v_isReturnSignBackRoutelabel number;
    v_attrval varchar2(5);
    l_raw_content raw(1024);
    v_requestID varchar2(100);
    v_sj_tel varchar2(30);
    l_test_code varchar2(100) := '111111';  --校验码
  begin
     utl_http.set_wallet('file:/home/oracle/test13', 'password');---网页证书  test14 测试
    
  l_soap_request_header := 'partnerID=1234&&requestID=v_requestID&&msgDigest=v_msgDigest&&serviceCode=EXP_RECE_CREATE_ORDER&&timestamp=v_timestamp&&msgData=';
    
    
    
    l_soap_request := '{"orderId":"v_order_id",  
         "cargoDetails":{"name":"v_product_name","count":"v_count"},
         "contactInfoList":[{"contactType":1,
                           "company":"v_jj_company",
                           "contact":"v_jj_contact",
                           "tel":"v_jj_tel",
                           "province":"v_jj_province",
                           "city":"v_jj_city",
                           "county":"v_jj_county",
                           "address":"v_jj_address"},
                          {"contactType":2,
                          "company":"v_sj_company",
                          "contact":"v_sj_contact",
                          "tel":"v_sj_tel",
                          "province":"v_sj_province",
                          "city":"v_sj_city",
                          "county":"v_sj_county",
                          "address":"v_sj_address"}],
         "payMethod":1,                 
         "monthlyCard":"7553226638",
         "expressTypeId":v_expressTypeId,
         "parcelQty":v_parcelQty,
         "isSignBack":v_isSignBack,
         "isReturnSignBackRoutelabel":v_isReturnSignBackRoutelabel,
         "extraInfoList":[{"attrName": "attr014", "attrVal": "singBackInfo"},{"attrName": "attr015", "attrVal": "v_attrval"}],
         "remark":"v_remark"                
        }';
        

    dbms_output.put_line(l_leng);
    l_http_req := utl_http.begin_request('https://eps-kxxxxx.com',
                                         'POST',
                                         utl_http.http_version_1_1);--正式

    dbms_output.put_line(1);                                      
    utl_http.set_header(l_http_req, 'Content-Type','application/x-www-form-urlencoded;charset=UTF-8'/* 'application/json'*/); 
    utl_http.set_header(l_http_req,
                        'Accept',
                       -- 'application/x-www-form-urlencoded;charset=UTF-8'
                        'application/json; charset=utf-8');     

    select replace(get_uuid(),'-') into v_requestID from dual;

    SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) into v_timestamp FROM DUAL;
 
    v_msgDigest := md5_base64(l_soap_request || v_timestamp || l_test_code);
  
    l_soap_request_header := replace(l_soap_request_header,'v_requestID',v_requestID);                  
    l_soap_request_header := replace(l_soap_request_header,'v_timestamp',v_timestamp);
    l_soap_request := replace(l_soap_request,'v_order_id',l_om_express_rec.pl_delivery_name);
    l_soap_request := replace(l_soap_request,'v_product_name',l_om_express_rec.product_name);
    l_soap_request := replace(l_soap_request,'v_count',l_om_express_rec.attribute2);
    l_soap_request := replace(l_soap_request,'v_jj_company',l_om_express_rec.jj_company);
    l_soap_request := replace(l_soap_request,'v_jj_contact',l_om_express_rec.jj_name);
    l_soap_request := replace(l_soap_request,'v_jj_tel',l_om_express_rec.jj_tel);
    l_soap_request := replace(l_soap_request,'v_jj_province',l_om_express_rec.jj_province);
    l_soap_request := replace(l_soap_request,'v_jj_city',l_om_express_rec.jj_city);
    l_soap_request := replace(l_soap_request,'v_jj_county',l_om_express_rec.jj_county);
    l_soap_request := replace(l_soap_request,'v_jj_address',l_om_express_rec.jj_address);
    
    l_soap_request := replace(l_soap_request,'v_sj_company',l_om_express_rec.sj_company);
    l_soap_request := replace(l_soap_request,'v_sj_contact',l_om_express_rec.sj_name);
    select replace(l_om_express_rec.sj_tel,' ') into v_sj_tel from dual;
    l_soap_request := replace(l_soap_request,'v_sj_tel',v_sj_tel);
    l_soap_request := replace(l_soap_request,'v_sj_province',l_om_express_rec.sj_province);
    l_soap_request := replace(l_soap_request,'v_sj_city',l_om_express_rec.sj_city);
    l_soap_request := replace(l_soap_request,'v_sj_county',l_om_express_rec.sj_county);
    l_soap_request := replace(l_soap_request,'v_sj_address',l_om_express_rec.sj_address);
    
    l_soap_request := replace(l_soap_request,'v_expressTypeId',l_om_express_rec.expresstypeid);
    l_soap_request := replace(l_soap_request,'v_parcelQty',l_om_express_rec.parcelqty);
    
    if NVL(l_om_express_rec.issignback_1,'N') = 'Y' then
      v_isSignBack := 1;
      v_isReturnSignBackRoutelabel := 1;
    else
      v_isSignBack := 0;
      v_isReturnSignBackRoutelabel := 0;
    end if;
    l_soap_request := replace(l_soap_request,'v_isSignBack',v_isSignBack);
    l_soap_request := replace(l_soap_request,'v_isReturnSignBackRoutelabel',v_isReturnSignBackRoutelabel);
    
    if (NVL(l_om_express_rec.issignback_1,'N') = 'Y') AND (NVL(l_om_express_rec.issignback_2,'N') = 'Y') THEN
      v_attrval := '1,2';
    ELSIF (NVL(l_om_express_rec.issignback_1,'N') = 'Y') AND (NVL(l_om_express_rec.issignback_2,'N') = 'N') THEN
      v_attrval := '1';
    ELSIF  (NVL(l_om_express_rec.issignback_1,'N') = 'N') AND (NVL(l_om_express_rec.issignback_2,'N') = 'Y') THEN
      v_attrval := '2';
    ELSE
       v_attrval := NULL;
    END IF;
    l_soap_request := replace(l_soap_request,'v_attrval',v_attrval);
    l_soap_request := replace(l_soap_request,'v_remark',l_om_express_rec.remark);
    
    l_soap_request_header := replace(l_soap_request_header,'v_msgDigest',v_msgDigest);

    utl_http.set_body_charset(l_http_req, 'UTF-8');
    l_leng := lengthb(l_soap_request_header || l_soap_request);
    utl_http.set_header(l_http_req, 'Content-Length', l_leng);
    dbms_output.put_line(2);
    
    utl_http.write_raw(r => l_http_req, data => UTL_RAW.CAST_TO_RAW(l_soap_request_header || l_soap_request ));
    --utl_http.write_text(l_http_req, l_soap_request_header || l_soap_request);
    dbms_output.put_line(l_soap_request_header  || l_soap_request );   
    l_http_resp := utl_http.get_response(l_http_req);  
    dbms_output.put_line('l_http_resp.status_code:' ||l_http_resp.status_code || chr(10) ||
                         'l_http_resp.reason_phrase:' ||l_http_resp.reason_phrase || chr(10) ||
                         'l_http_resp.http_version:' ||l_http_resp.http_version || chr(10) ||
                         'l_http_resp.private_hndl:' ||l_http_resp.private_hndl || chr(10) 
                 );
    
   IF l_http_resp.status_code = '200' THEN
      utl_http.read_line(l_http_resp, l_soap_respond_val, TRUE);
      if (q_json(REPLACE(SUBSTR(l_soap_respond_val,1,300),'\'),'apiResultCode',1) = 'A1000') and (q_json(REPLACE(SUBSTR(l_soap_respond_val,1,300),'\'),'apiErrorMsg',1) is null) then
         l_out_flag := 'Y';
         l_out_sf_number := q_json(REPLACE(SUBSTR(l_soap_respond_val,1,300),'\'),'waybillNo',2);
      ELSE
         l_out_flag := 'E';
         l_out_sf_number := q_json(REPLACE(SUBSTR(l_soap_respond_val,1,300),'\'),'apiErrorMsg',1);
      end if;
      dbms_output.put_line(l_soap_respond_val);
   ELSE
      l_out_flag := 'E';
      l_out_sf_number := '网络不通';
   END IF;
    utl_http.end_response(l_http_resp);
    utl_http.end_request(l_http_req);
    -----LOG 记录下保文
    INSERT INTO CUX_OM_EXPRESS_T_LOG
    VALUES(l_om_express_rec.pl_delivery_name,SUBSTR(l_soap_request_header || l_soap_request ,1,4000),SUBSTR(l_soap_respond_val,1,4000));
    COMMIT;
 
  exception
    when others then
      utl_http.end_response(l_http_resp);                                       
      utl_http.end_request(l_http_req);                              
  end cux_sf_prod_procedure;