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接口调用啦

参考链接：

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

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