17,089
社区成员
发帖
与我相关
我的任务
分享
ID NOT NULL VARCHAR2(37)
EVENT_ID NOT NULL NUMBER(20)
EVENT_ID NOT NULL NUMBER(20)
EVENT_TYPE NUMBER(2)
............
CORRELATED_EVENT_ID NOT NULL NUMBER(20)
BASE_EVENT_IDS NOT NULL VARCHAR2(4000)
CREATE PROCEDURE get_event_info(correlate_event_id IN A.event_id%TYPE,case_id IN A.id%TYPE) IS
CURSOR get_event_info(input_event_id NUMBER) IS
SELECT * FROM B WHERE event_id =input_event_id;
CURSOR get_correlate_info(input_event_id VARCHAR2) IS
SELECT * FROM C WHERE correlated_event_id =TO_NUMBER(input_event_id);
correlate_file_name VARCHAR2(64);
event_file_name VARCHAR2(64);
correlate_file_handle utl_file.file_type;
event_file_handle utl_file.file_type;
TYPE event_id_table IS TABLE OF NUMBER(20);
event_list event_id_table;
h_event_info B%ROWTYPE;
h_correlate C%ROWTYPE;
temp_event_id VARCHAR2(64);
i NUMBER(20);
next_event_id A.event_id%TYPE;
next_case_id A.id%TYPE;
BEGIN
i :=0;
next_case_id :=case_id;
event_file_name := 'Event_Info_'||case_id||'.csv';
correlate_file_name := 'Correlate_'||case_id||'.csv';
event_file_handle := utl_file.fopen('UTL_TEMP',event_file_name,'W');
correlate_file_handle := utl_file.fopen('UTL_TEMP',correlate_file_name,'W');
FOR h_event_info IN get_event_info(correlate_event_id) LOOP
utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
END LOOP;
FOR h_correlate IN get_correlate_info(correlate_event_id) LOOP
event_list :=event_id_table(substr(h_correlate.base_event_ids,2,length(h_correlate.base_event_ids)-2));
FOR i in event_list.FIRST .. event_list.LAST LOOP
FOR h_event_info IN get_event_info(event_list(i)) LOOP
IF h_event_info.EVENT_TYPE=0 THEN
utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
ELSE
next_event_id :=h_event_info.EVENT_ID;
get_event_info(next_event_id,next_case_id);
END IF;
END LOOP;
END LOOP;
END LOOP;
utl_file.fclose(event_file_handle);
utl_file.fclose(correlate_file_handle);
END get_event_info;
43 END get_event_info;
44 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERR PROCEDURE get_event_info;
Errors for PROCEDURE GET_EVENT_INFO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
36/6 PL/SQL: Statement ignored
36/6 PLS-00306: wrong number or types of arguments in call to
'GET_EVENT_INFO'
get_event_info(55290146209546,'7YYymfSMBABDL0zY6LSWNag==');
CREATE PROCEDURE get_event_info(correlate_event_id IN A.event_id%TYPE,case_id IN A.id%TYPE) IS
--定义游标
CURSOR get_event_info(input_event_id NUMBER) IS
SELECT * FROM B WHERE event_id =input_event_id;
CURSOR get_correlate_info(input_event_id VARCHAR2) IS
SELECT * FROM C WHERE correlated_event_id =TO_NUMBER(input_event_id);
--声明一个Table类型
TYPE event_id_table IS TABLE OF NUMBER(20);
--声明变量
correlate_file_name VARCHAR2(64);
event_file_name VARCHAR2(64);
correlate_file_handle utl_file.file_type;
event_file_handle utl_file.file_type;
event_list event_id_table;
h_event_info B%ROWTYPE;
h_correlate C%ROWTYPE;
temp_event_id VARCHAR2(64);
i NUMBER(20);
--这两个变量打算用来作为递归时的输入参数
next_event_id A.event_id%TYPE;
next_case_id A.id%TYPE;
BEGIN
--给变量赋予初始值
i :=0;
--定义输出文件的名字(简称文件A和文件B)
next_case_id :=case_id;
event_file_name := 'Event_Info_'||case_id||'.csv';
correlate_file_name := 'Correlate_'||case_id||'.csv';
--打开文件
event_file_handle := utl_file.fopen('UTL_TEMP',event_file_name,'W');
correlate_file_handle := utl_file.fopen('UTL_TEMP',correlate_file_name,'W');
--打开游标1,将在B表中获取与参数Event_id所对应的数据,导出到文件A中
FOR h_event_info IN get_event_info(correlate_event_id) LOOP
utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
END LOOP;
--打开游标2
FOR h_correlate IN get_correlate_info(correlate_event_id) LOOP
--将对应关系,输出到文件中
utl_file.put_line(correlate_file_handle,h_correlate.event_id||','||h_correlate.base_event_ids);
--获取C表中与参数Event_id所对应base_event_ids字段,并将经过处理后的字符串仍进上面所定义的Table类型的变量中(表述有点烂,反正将一串东东扔进数组中)
event_list :=event_id_table(substr(h_correlate.base_event_ids,2,length(h_correlate.base_event_ids)-2));
--迭代数组
FOR i in event_list.FIRST .. event_list.LAST LOOP
--从数组中获取一个元素,作为游标1的参数,打开游标
FOR h_event_info IN get_event_info(event_list(i)) LOOP
--检查对应的数据的Event_type字段,如等于0,输出到文件中
IF h_event_info.EVENT_TYPE=0 THEN
utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
--如不等于0,递归
ELSE
next_event_id :=h_event_info.EVENT_ID;
get_event_info(next_event_id,next_case_id);
END IF;
END LOOP;
END LOOP;
END LOOP;
--关闭打开的文件
utl_file.fclose(event_file_handle);
utl_file.fclose(correlate_file_handle);
--结束
END get_event_info;