ORACLE存储过程调用Web Service
1. 概述
最近在ESB项目中，客户在各个系统之间的服务调用大多都是在oracle存储过程中进行的，本文就oracle存储过程调用web service来进行说明。其他主流数据库，比如mysql和sql service，调用web service的方法这里就不做介绍了，本文主要用来介绍oracle存储过程调用Web Service的方法。
众所周知，在Web Service通过HTTP协议发送请求和接收结果时，发送的请求内容和结果内容都采用XML格式封装，并增加了一些特定的HTTP消息头，以说明HTTP消息的内容格式，这些特定的HTTP消息头和XML内容格式就是SOAP协议。SOAP协议是基于HTTP协议的，两者的关系就好比高速公路是基于普通公路改造的，在一条公路上加上隔离栏后就成了高速公路。
同理，在oracle的存储过程中能不能也通过创建XML格式的报文+HTTP协议来调用Web Service呢？答案是肯定的，在ORACLE中有一个名叫UTL_HTTP的工具包，我们可以通过这个工具包来实现存储过程调用Web Service。
上文提到的AEAI ESB是数通畅联的核心产品之一，可以实现WEB服务开发和WEB服务注册等功能，本文的接口样例，本考虑使用ESB创建的WEB服务，但是由于涉及到的需要介绍的内容和本文主题太远，所以采用了AEAI DP开发平台自带的Web服务样例来说明。有对AEAI ESB感兴趣的读者可以通过本文最后的相关链接查找了解。
2. 预期读者
- 数通畅联新员工
- 广大技术爱好者
3. 环境信息
操作系统：Windows7
Oracle: 版本为oracle11g
Mysql：版本为mysql5.1
Jdk： jdk1.6.0_10
4. 名词解释
AEAI ESB：应用集成平台主要作为企业信息系统的“龙骨”来集成各业务系统，一般称之为企业服务总线（Enterprise Service BUS，ESB），在数通畅联软件的产品家族中应用集成平台命名为AEAI ESB。
AEAI DP：AEAI DP应用开发平台专门用于开发MIS类的Java Web应用，也称Miscdp（Misc Develope Platform）综合应用开发平台。 AEAI DP应用开发平台在数通畅联软件产品家族中也作为扩展开发的支撑工具，比如：为AEAI Portal门户平台扩展开发Portlet组件、Web Service和Http Service；为AEAI BPM流程集成平台扩展开发业务流程表单及功能等。
存储过程：大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
UTL_HTTP：oracle中自带的HTTP协议工具包，可以用来发送post请求。
PL/SQL Developer:一个集成开发环境，由Allround Automations公司开发，专门面向Oracle数据库存储的程序单元的开发
5. 操作步骤
5.1 创建样例接口
使用AEAI DP开发平台，创建自带WS服务的应用，如下图：
选择数据库信息
部署应用后，查看刚刚创建的应用自带的WS服务
5.2 创建存储过程
5.2.1 基本语法
以下为创建存储过程的基本语法
|
CREATE OR REPLACE PROCEDURE存储过程名 ( --定义参数 ) IS 定义变量 BEGIN 开始PL/SQL体 END 说明PL/SQL体结束 |
5.2.2 创建步骤
1、打开PL/SQL，并打开一个sql窗口
2、将创建存储过程的语句放入其中并执行
这样一个调用web service的存储过程样例就创建了，以下为详细的样例sql体（里面的xml格式请使用soap UI 软件探查对应的webservice接口，获取xml格式）
CREATE OR REPLACE Procedure Sp_Wmstoics_Ws(Out_Return_Code Out Varchar2) Is
--定义四个变量，http请求，http返回，请求报文，返回报文
Http_Req Utl_Http.Req;
Http_Resp Utl_Http.Resp;
Request_Env Varchar2(32767);
l_Replyline Varchar2(1000);
r_Count Int;
Begin
--查询出需要传给ics的数据
For x In (Select d.Sku As Materno,
--物料编码
Ad.Lotnum Batchno, Bs.Sku_Group4 Unit,
--基本单位
--批次号
Lot.Lotatt02 Fpexpirydatetime,
--失效日期--供应商有效期
Bs.Sku_Group6 Materialtype,
--物料类型（品名）
--物料类型描述
Sum(Ad.Qty) Confqty,
--数量
--供应商物料编码
'ICS' Factory,
--工厂
d.Lotatt10 || ' ' || Bs.Descr_c Specification,
--规格
Lot.Lotatt01 Productdate,
--生产日期
To_Char(h.Ordertime, 'YYYY-MM-DD') Postdate --发料日期
From Doc_Order_Header h
Left Join Doc_Order_Details d
On h.Orderno = d.Orderno
Left Join Bas_Sku Bs
On Bs.Sku = d.Sku
And d.Customerid = Bs.Customerid
Left Join Act_Allocation_Details Ad
On Ad.Orderlineno = d.Orderlineno
And Ad.Orderno = d.Orderno
Left Join Inv_Lot_Att Lot
On Lot.Customerid = d.Customerid
And Lot.Sku = d.Sku
And Lot.Lotnum = Ad.Lotnum
Left Join Doc_Order_Printcode Dp
On Dp.Ordertime = h.Ordertime
And Dp.Orderno = h.Orderno
And Dp.Sku = d.Sku
And Dp.Lotnum = Ad.Lotnum
Where 1 = 1
And h.Soreference1 Is Not Null
And Trunc(h.Ordertime) = Trunc(Sysdate)
And h.Customerid = 281
And Bs.Sku_Group5 In ('6001', '6003', '6004', '6005', '6006')
-- And lot.LOTATT02 = '2021-02-11'
--And h.orderno = 'SO201206000148'
Group By Ad.Lotnum, d.Sku, Bs.Bs.Sku_Group4, Lpad(Dp.Printcode, 3, '0'), h.Orderno, d.Lotatt10, Bs.Descr_c, Lot.Lotatt01, Lot.Lotatt02,
Bs.Sku_Group5, Bs.Sku_Group6, h.Soreference1, h.Ordertime)
Loop
--开始pl/sql体
Request_Env := '
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:exam="http://www.example.org" xmlns:mes="http://MESWebSvc/">
<soapenv:Header>
<exam:SOAHeader>
<exam:APP_CODE>MES_ICS_001</exam:APP_CODE>
<!--Optional:-->
<exam:MOD_ID>MES_ICS_001</exam:MOD_ID>
<exam:SUBMITDATE>' || To_Char(Sysdate, 'YYYY-MM-DD') || '</exam:SUBMITDATE>
</exam:SOAHeader>
</soapenv:Header>
<soapenv:Body>
<mes:ERP_MESICSMater>
<!--Optional:-->
<mes:materlot>
';
--拼接数据
Request_Env := Request_Env || '
<mes:ErpToMesICSMater>
<mes:MATERNO>' || x.Materno || '</mes:MATERNO>
<mes:BATCHNO>' || x.Batchno || '</mes:BATCHNO>
<mes:FPEXPIRYDATETIME>' || x.Fpexpirydatetime || '</mes:FPEXPIRYDATETIME>
<mes:MATERIALTYPE>' || x.Materialtype || '</mes:MATERIALTYPE>
<mes:MATERIALSPEC>' || x.Specification || '</mes:MATERIALSPEC>
<mes:CONFQTY>' || x.Confqty || '</mes:CONFQTY>
<mes:FACTORY>' || x.Factory || '</mes:FACTORY>
<mes:UNIT>' || x.Unit || '</mes:UNIT>
<mes:SPECIFICATION>' || x.Specification || '</mes:SPECIFICATION>
<mes:PRODUCTDATE>' || x.Productdate || '</mes:PRODUCTDATE>
<mes:POSTDATE>' || x.Postdate || '</mes:POSTDATE>
<mes:APPROVEDTIME>' || To_Char(Sysdate, 'YYYY-MM-DD') || '</mes:APPROVEDTIME>
<mes:REVALUE>0</mes:REVALUE>
</mes:ErpToMesICSMater>
';
Request_Env := Request_Env || '
</mes:materlot>
</mes:ERP_MESICSMater>
</soapenv:Body>
</soapenv:Envelope>
';
--打印请求报文
-- Dbms_Output.Put_Line(Request_Env);
--请求WS地址
Http_Req := Utl_Http.
Begin_Request('http://192.168.230.167:7003/MES/ERP_MESICSMater/Pipeline/ERP_MESICSMater_Proxy',
'POST',
Utl_Http.Http_Version_1_1);
-- 保持连接状态
Utl_Http.Set_Persistent_Conn_Support(Http_Req, True);
--设置编码
Utl_Http.Set_Header(Http_Req, 'Content-Type', 'text/xml;charset=utf-8');
Utl_Http.Set_Header(Http_Req, 'SOAPAction', '');
--设置字符集
Utl_Http.Set_Body_Charset(Http_Req, 'utf-8');
--该参数代表我发送的POST报文多长，不可少
Utl_Http.Set_Header(Http_Req, 'Content-Length', Lengthb(Request_Env));
Utl_Http.Write_Line(Http_Req, Request_Env);
--赋值http返回
Http_Resp := Utl_Http.Get_Response(Http_Req);
--将请求报文赋值给 l_Replyline
Utl_Http.Read_Text(Http_Resp, l_Replyline);
Utl_Http.End_Response(Http_Resp);
-- Dbms_Output.Put_Line(l_Replyline);
--付给存储过程出参
Select Instr(l_Replyline, '<Status>true</Status>')
Into r_Count
From Dual;
If r_Count > 0 Then
--更新以sku、批次号、发运日期 分组的这些数据的状态 c_toics ='y'
Out_Return_Code := Out_Return_Code; --占位
Else
Out_Return_Code := Out_Return_Code + '调用ics接口（ERP_MESICSMater_Proxy）发送数据失败！';
End If;
End Loop;
If Nvl(Out_Return_Code, '*') = '*' Then
Out_Return_Code := '000';
End If;
End Sp_Wmstoics_Ws;
5.2.3 关键点说明
在存储过程中，使用UTL_HTTP工具包调用web服务时，几个关键方法的使用说明
1. 通过设置请求地址、方式、协议版本，得到http请求对象
|
http_req := UTL_HTTP. begin_request(' http://localhost:6060/wstest_project/services/HelloWorld?wsdl ', 'POST', UTL_HTTP.http_version_1_1); |
2. 设置协议保持连接状态
|
Utl_Http.Set_Persistent_Conn_Support(http_req, TRUE); |
3. 设置请求编码，SOAPAction header的值为空串("")表示SOAP消息的目的地由HTTP请求的URI标识；无值则表示没有指定这条消息的目的地。
|
Utl_Http.Set_Header(http_req, 'Content-Type', 'text/xml;charset=utf-8'); Utl_Http.Set_Header(http_req, 'SOAPAction', ''); |
4. 设置字符集
|
Utl_Http.Set_Body_Charset(http_req, 'utf-8'); |
5. 报文长度
|
Utl_Http.Set_Header(http_req, 'Content-Length', Lengthb(request_env)); |
6. 调用服务，发送报文
|
Utl_Http.Write_Line(http_req, request_env); |
7. 得到返回体
|
http_Resp := Utl_Http.Get_Response(http_req); |
8. 将返回报文赋值给变量
|
Utl_Http.Read_Text(http_Resp, l_Replyline); |
5.3 调用存储过程
5.3.1 使用PL/SQL Developer测试
1) 选中存储过程的名字，右键选择测试，进入测试页面
2) 添加响应的参数值，F9或者点击按钮开始执行，执行后可以得到看到返回值
3) 切换到DBMS输出页面，可以看到打印的内容
4) 如果报错网络访问被访问控制列表(ACL)拒绝
5）配置当前需要执行存储的oracle用户的相关webservice接口的ip地址访问权限即可。
/*WMS_PRD 用户需要访问192.168.230.167的7003端口的权限 */
BEGIN--创建acl列表
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => '/sys/acls/CUXTEST2.xml',--acl 列表名称
description => 'CUX_TEST', --描述
principal => 'WMS_PRD',--用户
is_grant => TRUE,--是否授权
privilege => 'connect');--连接权限
END;
/
Begin
--把用户添加到acl列表里面去
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => '/sys/acls/CUXTEST2.xml',
principal => 'WMS_PRD',
is_grant => true,
privilege => 'connect');
End;
/
BEGIN--授权
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => '/sys/acls/CUXTEST2.xml',--acl 列表名称
host => '192.168.224.120',--ip
lower_port => 7003,--端口
upper_port => 7003);--端口
END;
/
--查询
SELECT host, lower_port, upper_port, acl FROM dba_network_acls
5.3.2 使用sql代码调用
|
DECLARE resmark varchar2(1000); BEGIN pro_test_ws(''小郑',resmark); DBMS_OUTPUT.PUT_LINE(resmark); END; |
1) 打开sql窗口，执行上面的sql语句
2) 查看输出信息
1处为存储过程打印的信息，2为调用时打印输出的信息
6. 总结说明
本文介绍了在oracle存储过程中，使用UTL_HTTP工具包，通过创建请求报文以及使用HTTP协议来调用Web Service，从创建oracle存储过程以及UTL_HTTP相关参数的配置，到通过PL/SQL Developer测试调用以及sql代码进行调用来详细说明。
附件为存储过程创建sql、调用sql以及接口程序和相关的数据库文件。
7. 相关链接
AEAI DP开发平台/ AEAI ESB集成平台相关介质以及文档资料地址：http://www.agileai.com/portal/website/01/res-share.ptml
文档及代码附件下载：http://pan.baidu.com/s/1kVyMVQn
参照原文（感谢！）：https://www.cnblogs.com/agileai/p/6030612.html
