--测试表
CREATE TABLE AA(
ID NUMBER(10),
PARAMS VARCHAR2(200)
);
-- 测试更新流水表
CREATE TABLE AA_FLOW(
ID NUMBER(10),
PARAMS VARCHAR2(200),
OPER VARCHAR(200)
);
-- 删除时触发
CREATE OR REPLACE TRIGGER AA_TRIGGER
AFTER UPDATE ON AA -- AFTER-之后,BEFORE-之前;INSERT,DELETE,UPDATE-增删改;INSTEAD OF-对视图的操作
FOR EACH ROW -- 加上FOR EACH ROW 即为行级触发器,不加时为语句级触发器
-- [WHEN (CONDITION)] 满足条件才触发
DECLARE
BEGIN
-- OLD-原数据;NEW:新数据;新增只有NEW,删除只有OLD,修改有NEW和OLD
INSERT INTO AA_FLOW(ID,PARAMS,OPER)VALUES(:NEW.ID,:NEW.PARAMS,'UPDATE_NEW');
INSERT INTO AA_FLOW(ID,PARAMS,OPER)VALUES(:OLD.ID,:OLD.PARAMS,'UPDATE_OLD');
END;
-- 触发器触发时发送http请求,可做实时缓存刷新
CREATE OR REPLACE TRIGGER AA_TRIGGER_HTTP
AFTER INSERT ON AA
FOR EACH ROW
DECLARE
BEGIN
-- 调用储存过程
PRO_POSTREQ(:NEW.ID,:NEW.PARAMS);
END;
CREATE OR REPLACE PROCEDURE PRO_POSTREQ(R_ID IN NUMBER, R_PARAMS IN VARCHAR2) AS
REQ UTL_HTTP.REQ;
RESP UTL_HTTP.RESP;
VALUE VARCHAR2(1024);
-- post请求参数也拼装到url上
V_URL VARCHAR2(4000):= 'http://192.168.24.153/mall/api/CRH-MALL0000?rid=' || R_ID || '&¶ms=' || R_PARAMS;
V_PARAM VARCHAR2(4000):= '1';
V_PARAM_LENGTH NUMBER:= LENGTHB(V_PARAM);
BEGIN
-- 记录流水
INSERT INTO AA_FLOW(ID,PARAMS,OPER)VALUES(R_ID,V_URL,'INSERT');
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => null);
-- 请求url,类型为POST
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); -- VALUE-返回
INSERT INTO AA_FLOW(ID,PARAMS,OPER)VALUES(R_ID, VALUE,'INSERT1');
END LOOP;
UTL_HTTP.END_RESPONSE(RESP);
--异常处理
EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(RESP);
END;
/
--创建acl
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
ACL => 'httprequestpermission.xml',-- acl名称
DESCRIPTION => 'Normal Access', -- 描述
PRINCIPAL => 'CONNECT',
IS_GRANT => TRUE, -- 授权
PRIVILEGE => 'connect',
START_DATE => NULL,
END_DATE => NULL
);
END;
--查看ACL是否增加成功
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE ANY_PATH LIKE '/sys/acls/%.xml';
--给用户增加acl权限
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
ACL => 'httprequestpermission.xml',-- acl名称
PRINCIPAL => 'CRH_MALL',-- 赋予权限给用户,注意是大写,小写不识别
IS_GRANT => TRUE,-- 授权
PRIVILEGE => 'connect',
START_DATE => null,
END_DATE => null
);
END;
--添加对应主机 ,将对应主机和端口添加到ACL。这里是 192.168.0.156 和 8080 ,这个ip和端口要和上面存储过程中定义的地址一致
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
ACL => 'httprequestpermission.xml',-- acl名称
HOST => '192.168.24.153', -- ip
LOWER_PORT => 80,-- 端口
UPPER_PORT => NULL
);
END;
COMMIT;
-- 检查acl
SELECT ACL, PRINCIPAL, PRIVILEGE, IS_GRANT,
TO_CHAR(START_DATE, 'DD-MON-YYYY') AS START_DATE,
TO_CHAR(END_DATE, 'DD-MON-YYYY') AS END_DATE
FROM DBA_NETWORK_ACL_PRIVILEGES;
-- 测试数据
INSERT INTO AA(ID,PARAMS)VALUES(2,2);
DELETE AA WHERE ID =1;
UPDATE AA SET ID= 3 ;
UPDATE AA SET ID= 2 WHERE ID = 1;
CALL PRO_POSTREQ(1,'5235');
COMMIT;
SELECT * FROM AA;
SELECT * FROM AA_FLOW FOR UPDATE;
/*
-- 删除测试表
DROP TABLE AA;
-- 删除测试更新流水表
DROP TABLE AA_FLOW;
-- 删除触发器
DROP TRIGGER AA_TRIGGER;
DROP TRIGGER AA_TRIGGER_HTTP;
-- 删除acl
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('httprequestpermission.xml');
COMMIT;
END;
*/