oracle触发器+存储过程发送http请求

项目遇到这样一个需求&＃xff1a;  由于数据库是第三方系统的Oracle数据库&＃xff0c;我们需要时刻同步该数据库中的告警记录表&＃xff0c;并在我平台中存储或通知。鉴于这样的需求&＃xff0c;考虑通过oracle 的 触发器加存储过程实现发送http请求的方案实现。

1、在Oracle中编写存储过程&＃xff0c;实现调用http接口。

2、在Oracle中编写触发器&＃xff0c;实现告警表的行变化触发存储过程。

3、我们项目提供一个http接口供存储过程调用&＃xff0c;并实现进一步定制操作。

1、创建存储过程

直接上代码

在oracle 的sql执行窗口中执行  存储过程创建命令 

create or replace PROCEDURE "PRO_POSTREQ" ( guid in varchar2,res out varchar2) asbeginDECLAREreq UTL_HTTP.REQ;resp UTL_HTTP.RESP;value VARCHAR2(1024); -- URL to post tov_url VARCHAR2(4000) :&＃61; &＃39;http://127.0.0.1:8902/api/service/getservicestatus?uid&＃61;&＃39;||guid;v_param VARCHAR2(4000) :&＃61; &＃39;1&＃39;;v_param_length NUMBER :&＃61; LENGTHB(v_param);BEGINDBMS_OUTPUT.ENABLE (buffer_size&＃61;>null);req :&＃61; UTL_HTTP.BEGIN_REQUEST (url&＃61;> v_url, method &＃61;> &＃39;POST&＃39;);UTL_HTTP.SET_BODY_CHARSET(&＃39;UTF-8&＃39;);UTL_HTTP.SET_HEADER (r &＃61;> req,name &＃61;> &＃39;Content-Type&＃39;,value &＃61;> &＃39;application/x-www-form-urlencoded&＃39;);UTL_HTTP.SET_HEADER(req, &＃39;Keep-Alive&＃39;, &＃39; timeout&＃61;1&＃39;);UTL_HTTP.SET_HEADER (r &＃61;> req,name &＃61;> &＃39;Content-Length&＃39;,value &＃61;> v_param_length);UTL_HTTP.WRITE_RAW (r &＃61;> req,data &＃61;> UTL_RAW.CAST_TO_RAW(v_param)); resp :&＃61; UTL_HTTP.GET_RESPONSE(req);LOOPUTL_HTTP.READ_LINE(resp, value, TRUE);DBMS_OUTPUT.PUT_LINE(value);END LOOP;UTL_HTTP.END_RESPONSE(resp);EXCEPTIONWHEN UTL_HTTP.END_OF_BODY THENUTL_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 rowdeclare 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;

解释&＃xff1a;

1. &＃61;> 是 Oracle 中调用 存储过程的时候,  指定 参数名进行调用。:&＃61; 是赋值语句。 &＃61; 是if的判断语句。

2.触发器中的【:new】是 oracle PL/ sql的关键字。

NEW关键字在什么情况下使用&＃xff1f;
------最佳解决方案--------------------
oracle默认的 用old代表老数据 new代表新数据 不过二者在使用时是有限制的
insert时 只有new 没有old 
delete时 只有old 没有new
update时 二者都可用
————————————————

3. 定义存储过程时&＃xff0c;参数中 in/out 代表输出/输出参数。 varchar/varchar2不用指定参数长度。

4. 触发器中res 的类型是varchar&＃xff0c;此时就需要定义长度。

5. 触发器和存储过程可以调试,F5 插入断点&＃xff0c;点击 运行即可进行步进调试。

6.触发器中少了 for each row会抛出  NEW 或 OLD 引用不允许在表级触发器中错误

3. 增加访问控制权限

在调试和调用存储过程的访问http接口时 提示 HTTP 请求失败&＃xff0c;网络访问被访问控制列表 (ACL) 拒绝。可执行如下代码&＃xff1a;YONGHU是我当前的用户&＃xff0c;一定要大写&＃xff0c;不然会提示  ACL 无效:无法解析的主用户。

--添加acl和权限控制&＃xff08;sql语句执行的方式来执行&＃xff09;begindbms_network_acl_admin.create_acl ( -- 创建访问控制文件&＃xff08;ACL&＃xff09;acl &＃61;> &＃39;utl_http.xml&＃39;, -- 文件名称description &＃61;> &＃39;HTTP Access&＃39;, -- 描述principal &＃61;> &＃39;YONGHU&＃39;, -- 授权或者取消授权账号&＃xff0c;大小写敏感is_grant &＃61;> TRUE, -- 授权还是取消授权privilege &＃61;> &＃39;connect&＃39;, -- 授权或者取消授权的权限列表start_date &＃61;> null, -- 起始日期end_date &＃61;> null -- 结束日期);dbms_network_acl_admin.add_privilege ( -- 添加访问权限列表项acl &＃61;> &＃39;utl_http.xml&＃39;, -- 刚才创建的acl名称 principal &＃61;> &＃39;YONGHU&＃39;, -- 授权或取消授权用户is_grant &＃61;> TRUE, -- 与上同 privilege &＃61;> &＃39;resolve&＃39;, -- 权限列表start_date &＃61;> null, end_date &＃61;> null);dbms_network_acl_admin.assign_acl ( -- 该段命令意思是允许访问acl名为utl_http.xml下授权的用户&＃xff0c;使用oracle网络访问包&＃xff0c;所允许访问的目的主机&＃xff0c;及其端口范围。acl &＃61;> &＃39;utl_http.xml&＃39;,host &＃61;> &＃39;127.0.0.1&＃39;, -- ip地址或者域名&＃xff0c;填写https://localhost:9000/hello与https://localhost:9000/是会报host无效的-- 且建议使用ip地址或者使用域名&＃xff0c;若用localhost&＃xff0c;当oracle不是安装在本机上的情况下&＃xff0c;会出现问题lower_port &＃61;> 8902, -- 允许访问的起始端口号upper_port &＃61;> Null -- 允许访问的截止端口号);commit;end;

4. 插入数据测试

insert into test (id,name,value) values(1,&＃39;张三&＃39;,&＃39;zhangsan&＃39;);
insert into test (id,name,value) values(1,&＃39;李四&＃39;,&＃39;lisi&＃39;);
insert into test (id,name,value) values(1,&＃39;王五&＃39;,&＃39;wangwu&＃39;);

这样就成功收到 存储过程的http接口调用啦

参考链接&＃xff1a;

通过oracle触发器调用存储过程发送http请求

Oracle触发器中的NEW和Old关键字说明