--创建类型t_table
create or replace type t_table as object (
name1 varchar2(50),
value1 varchar2(50)
);
/
--创建以t_table类型的数组t_table_tb
create or replace type t_table_tb as table of t_table;
/
--创建普通函数 返回数组类型,用数组的实例存放结果集(t_array),结果集全部处理完成后一起--返回 select * from table(F_NODE_21('user_oid=0;A1=1;A2=2;A3=3;'));
create or replace function F_NODE_21(val varchar2) return t_table_tb as
t_array t_table_tb := t_table_tb();
name1 varchar2(50);
value1 varchar2(50);
tmpVal varchar2(2000);
val_new varchar2(4000);
begin
val_new:=val;
while instr(val_new,';')>0 loop
val_new := substr(val_new,instr(val_new,';')+1,2000);--去掉已经计算过的节点
tmpVal := substr(val_new,0,instr(val_new,';')-1); --得到单个节点的数量 A1=X
name1:=substr(tmpVal,0,instr(tmpVal,'=')-1);
value1:=substr(tmpVal,instr(tmpVal,'=')+1,10);
if length(name1)>0 and length(value1)>0 then
t_array.extend();
t_array(t_array.count) := t_table(name1,value1);
end if;
end loop;
return t_array;
end;
/
/*
创建插入节点表的存储过程
*/
create or replace procedure sp_insert_node_21(
p_content VARCHAR2
) as
p_num integer;
tmpVal varchar2(2000);
val varchar2(2000);
user_oid varchar2(50);
mapStr varchar2(2000);
nameStr varchar2(2000);
valueStr varchar2(2000);
sqlstr varchar(200);
sqlstring varchar(2000);
begin
if p_content is not null then
val:=p_content;
tmpVal:=substr(val,0,INSTR(val,';')-1);
user_oid:=substr(tmpVal,INSTR(tmpVal,'=')+1,length(tmpVal));
DECLARE Cursor CustomCursor IS (Select name1,value1 From table(F_NODE_21(p_content))) ;
c_data CustomCursor%ROWTYPE;
begin
OPEN CustomCursor ;
LOOP
FETCH CustomCursor INTO c_data;
Exit when CustomCursor%NOTFOUND;
if(c_data.name1='user_oid' or c_data.name1='branch_no' or c_data.name1='operate_type') then
mapStr:=mapStr||c_data.name1||'='''||c_data.value1||''',';
nameStr:=nameStr||c_data.name1||',';
valueStr:=valueStr||''''||c_data.value1||''',';
else
mapStr:=mapStr||c_data.name1||'='||c_data.value1||',';
nameStr:=nameStr||c_data.name1||',';
valueStr:=valueStr||c_data.value1||',';
end if;
END LOOP;
CLOSE CustomCursor;
END;
if mapStr is not null then
mapStr:=subStr(mapStr,0,length(mapStr)-1);
end if;
if nameStr is not null then
nameStr:=subStr(nameStr,0,length(nameStr)-1);
end if;
if valueStr is not null then
valueStr:=subStr(valueStr,0,length(valueStr)-1);
end if;
sqlstr := 'select count(1) from node_info_test where user_oid='''||user_oid||'''';
EXECUTE IMMEDIATE sqlstr INTO p_num ;
if p_num>0 then
sqlstring:='update node_info_test set '||mapStr||' where user_oid='''||user_oid||'''';
else
sqlstring:='insert into node_info_test (count_date,user_oid,'||nameStr||')
values (to_char(sysdate,''yyyy-mm-dd hh24:mm:ss''),'''||user_oid||''','||valueStr||')';
end if;
--LOG_RP_PROC(sqlstring,'','','sp_insert_node_21_V3.5.3.0','通用节点统计');
execute immediate sqlstring;
end if;
end sp_insert_node_21;
/