oracle触发器+存储过程发送http请求
背景
项目遇到这样一个需求： 由于数据库是第三方系统的Oracle数据库，我们需要时刻同步该数据库中的告警记录表，并在我平台中存储或通知。鉴于这样的需求，考虑通过oracle 的 触发器加存储过程实现发送http请求的方案实现。
方案
1、在Oracle中编写存储过程，实现调用http接口。
2、在Oracle中编写触发器，实现告警表的行变化触发存储过程。
3、我们项目提供一个http接口供存储过程调用，并实现进一步定制操作。
实现
1、创建存储过程
直接上代码
在oracle 的sql执行窗口中执行 存储过程创建命令
create or replace PROCEDURE "PRO_POSTREQ" ( guid in varchar2,res out varchar2) as
begin
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(4000) := 'http://127.0.0.1:8902/api/service/getservicestatus?uid='||guid;
v_param VARCHAR2(4000) := '1';
v_param_length NUMBER := LENGTHB(v_param);
BEGIN
DBMS_OUTPUT.ENABLE (buffer_size=>null);
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(req, 'Keep-Alive', ' timeout=1');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_RAW (r => req,
data => UTL_RAW.CAST_TO_RAW(v_param));
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
end PRO_POSTREQ;2. 创建触发器
执行 存储过程创建命令
create or replace TRIGGER "TR_AFTER_INSERT_EMPLOYEE" after insert or update on TEST for each row
declare res varchar2(2000);
begin
--PRO_POSTREQ(RTRIM(:new.SRVTRANSDATETIME),RTRIM(:new.CARDNO),RTRIM(:new.MID),RTRIM(:new.TRANAMT),RTRIM(:new.CARDKIND),RTRIM(:new.SRVSTAN));
PRO_POSTREQ(:new.Name,res);
END TR_BEFORE_INSERT_EMPLOYEE;解释：
1. => 是 Oracle 中调用 存储过程的时候, 指定 参数名进行调用。:= 是赋值语句。 = 是if的判断语句。
2.触发器中的【:new】是 oracle PL/ sql的关键字。
NEW关键字在什么情况下使用？
------最佳解决方案--------------------
oracle默认的 用old代表老数据 new代表新数据 不过二者在使用时是有限制的
insert时 只有new 没有old
delete时 只有old 没有new
update时 二者都可用
————————————————
3. 定义存储过程时，参数中 in/out 代表输出/输出参数。 varchar/varchar2不用指定参数长度。
4. 触发器中res 的类型是varchar，此时就需要定义长度。
5. 触发器和存储过程可以调试,F5 插入断点，点击 运行即可进行步进调试。
6.触发器中少了 for each row会抛出 NEW 或 OLD 引用不允许在表级触发器中错误
3. 增加访问控制权限
在调试和调用存储过程的访问http接口时 提示 HTTP 请求失败，网络访问被访问控制列表 (ACL) 拒绝。可执行如下代码：YONGHU是我当前的用户，一定要大写，不然会提示 ACL 无效:无法解析的主用户。
--添加acl和权限控制（sql语句执行的方式来执行）
begin
dbms_network_acl_admin.create_acl ( -- 创建访问控制文件（ACL）
acl => 'utl_http.xml', -- 文件名称
description => 'HTTP Access', -- 描述
principal => 'YONGHU', -- 授权或者取消授权账号，大小写敏感
is_grant => TRUE, -- 授权还是取消授权
privilege => 'connect', -- 授权或者取消授权的权限列表
start_date => null, -- 起始日期
end_date => null -- 结束日期
);
dbms_network_acl_admin.add_privilege ( -- 添加访问权限列表项
acl => 'utl_http.xml', -- 刚才创建的acl名称
principal => 'YONGHU', -- 授权或取消授权用户
is_grant => TRUE, -- 与上同
privilege => 'resolve', -- 权限列表
start_date => null,
end_date => null
);
dbms_network_acl_admin.assign_acl ( -- 该段命令意思是允许访问acl名为utl_http.xml下授权的用户，使用oracle网络访问包，所允许访问的目的主机，及其端口范围。
acl => 'utl_http.xml',
host => '127.0.0.1', -- ip地址或者域名，填写https://localhost:9000/hello与https://localhost:9000/是会报host无效的
-- 且建议使用ip地址或者使用域名，若用localhost，当oracle不是安装在本机上的情况下，会出现问题
lower_port => 8902, -- 允许访问的起始端口号
upper_port => Null -- 允许访问的截止端口号
);
commit;
end;4. 插入数据测试
insert into test (id,name,value) values(1,'张三','zhangsan');
insert into test (id,name,value) values(1,'李四','lisi');
insert into test (id,name,value) values(1,'王五','wangwu');这样就成功收到 存储过程的http接口调用啦
参考链接：