--eidt by luwenbing
--2014/1-27
create or replace procedure
p_result
(t_u_aliquat_id in varchar2,t_result_alias in varchar2,t_result in
varchar2,p_rows out varchar2)
is
v_exp
varchar2(4000);
v_format varchar2(255);
v_des
varchar2(4000);
v_min varchar2(4000);
v_max
varchar2(4000);
v_n number;
cursor c
is
select * from result where result_id
in
(select result_id from result_user t where u_aliquat_id
= t_u_aliquat_id) and result_template_id in
(select
result_template_id from result_alias where result_alias=t_result_alias)
for update;
cursor c2 is
select * from result
where result_id in
(select result_id from
result_user t where u_aliquat_id = t_u_aliquat_id) for update;
cursor c3 is
select * from result where test_id in
(select test_id
from result where
result_id in
(select result_id from result_user t where
u_aliquat_id = t_u_aliquat_id) and
result_template_id in
(select result_template_id from result_alias where
result_alias=t_result_alias)) and result_id not in
(select result_id
from result where result_id
in
(select result_id from result_user t where u_aliquat_id
= t_u_aliquat_id) and
result_template_id in
(select result_template_id from result_alias where
result_alias=t_result_alias)) for update;
--查找性别
cursor c4
is
select u_subject_sex from sample_user where sample_id
in (
select sample_id from aliquot where
aliquot_id in (
select aliquot_id
from test where test_id in
(
select test_id from
result where result_id
in
(select result_id
from result_user t where u_aliquat_id = t_u_aliquat_id ) and result_template_id
in
(select
result_template_id from result_alias where result_alias=t_result_alias)
)
)
) for
update;
--查找年龄
cursor c5 is
select u_age
from sample_user where sample_id in (
select
sample_id from aliquot where aliquot_id in
(
select aliquot_id from test
where test_id in
(
select test_id from
result where result_id
in
(select result_id
from result_user t where u_aliquat_id = t_u_aliquat_id ) and result_template_id
in
(select
result_template_id from result_alias where result_alias=t_result_alias)
)
)
) for
update;
begin
if t_result_alias is not null
then
for v_res in c loop
--original_result
if(regexp_like(t_result,‘(^#+)‘))
then
update result set original_result =
‘‘ where current of c;
p_rows :=
SQL%ROWCOUNT;
else
update result set original_result =
t_result where current of c;
p_rows := SQL%ROWCOUNT;
end
if;
--raw_numeric_result
if(regexp_like(t_result,‘(^[+-]?\d{0,}\.?\d{0,}$)‘))
then
update result set raw_numeric_result
= original_result where current of c;
end
if;
--formatted_result
select format into v_format from result_template where result_template_id =
v_res.result_template_id;
if(v_format is null) then
update result set formatted_result=original_result
where current of c;
end
if;
if(regexp_like(v_format,‘0\.0+‘)) then
select
to_number(lengthb(v_format)-instr(v_format,‘.‘)) into v_n from
dual;
update result set
formatted_result=trim(to_char(round(original_result,v_n),(select
to_char(rpad(‘99999990.99‘,9+v_n,9)) from dual)))where current of c;
end if;
--Conclusion
select description into v_des from
result_template where result_template_id =
v_res.result_template_id;
if(regexp_like(v_des,‘\-‘)
or regexp_like(v_des,‘－‘))
then
--单个指标
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘))
then
select
regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template where
result_template_id = v_res.result_template_id
;
select
regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template where
result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for
v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,3) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,4) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,3) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,4) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
if(regexp_like(v_des,‘<‘) or regexp_like(v_des,‘＜‘))
then
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from result_template
where result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
if(regexp_like(v_des,‘>‘) or regexp_like(v_des,‘＞‘))
then
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘) )
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_min from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from
result_template where result_template_id = v_res.result_template_id
;
end
if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_min from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
--如果参数是一个单词，肯定正常
if(regexp_like(t_result,‘\s*[a-zA-Z]+\s*‘))
then
--单词一样
if(instr(upper(v_des),upper(t_result))) >=0
then
update
result set Conclusion =‘‘ where current of c
;
else
update
result set Conclusion =‘↑‘ where current of c
;
end
if;
else
if(regexp_like(v_min,‘\d+(\.\d+)?‘) and to_number(t_result)<to_number(v_min))
then
update result set
Conclusion =‘↓‘ where current of c ;
end if;
if(regexp_like(v_max,‘\d+(\.\d+)?‘) and
to_number(t_result)>to_number(v_max) ) then
update result set Conclusion =‘↑‘ where current
of c;
end
if;
end if;
--formatted_unit,original_unit
update result
set formatted_unit =
(select formatted_unit
from result_template r where result_template_id =
v_res.result_template_id)
where current of
c;
update result set
original_unit=
(select original_unit from
result_template r where result_template_id =
v_res.result_template_id)
where current of
c;
--old_status
update result set old_status = v_res.old_status||v_res.status where
current of c;
--status
update result set status = ‘C‘ where
current of c;
--completed_on
update result set completed_on =
sysdate where current of c;
--completed_by
update result set completed_by =
v_res.created_by where current of c;
end
loop;
commit;
for v_res in c3
loop
--original_result
if(regexp_like(t_result,‘(^#+)‘)) then
update result set original_result = ‘‘ where current of
c3;
p_rows :=
SQL%ROWCOUNT;
end
if;
select expression into v_exp from
calculation where calculation_id
in
(select
calculation_id from result_template r where result_template_id =
v_res.result_template_id);
if(regexp_like(t_result,‘(^\d{0,}\.?\d{0,}$)‘) and
regexp_like(v_exp,‘\\+\d{0,}\.?\d{0,}$‘)) then
update result set original_result = to_number(t_result) +
to_number(substr(v_exp,instr(v_exp,‘*‘)+1)) where current of
c3;
p_rows :=
SQL%ROWCOUNT;
end if;
if(regexp_like(t_result,‘(^\d{0,}\.?\d{0,}$)‘) and
regexp_like(v_exp,‘\\*\d{0,}\.?\d{0,}$‘)) then
update result set original_result = to_number(t_result) *
to_number(substr(v_exp,instr(v_exp,‘*‘)+1)) where current of
c3;
p_rows :=
SQL%ROWCOUNT;
end if;
if(regexp_like(t_result,‘(^\d{0,}\.?\d{0,}$)‘) and
regexp_like(v_exp,‘\-\d{0,}\.?\d{0,}$‘)) then
update
result set original_result = to_number(t_result) -
to_number(substr(v_exp,instr(v_exp,‘*‘)+1)) where current of
c3;
p_rows :=
SQL%ROWCOUNT;
end if;
--raw_numeric_result
if(regexp_like(t_result,‘(^[+-]?\d{0,}\.?\d{0,}$)‘))
then
update result set raw_numeric_result
= original_result where current of c3;
end if;
--formatted_result
select format into v_format from
result_template where result_template_id =
v_res.result_template_id;
if(v_format is null) then
update result set formatted_result=original_result
where current of c3;
end
if;
if(regexp_like(v_format,‘0\.0+‘)) then
select
to_number(lengthb(v_format)-instr(v_format,‘.‘)) into v_n from
dual;
update result set
formatted_result=trim(to_char(round(original_result,v_n),(select
to_char(rpad(‘99999990.99‘,9+v_n,9)) from dual)))where current of c3;
end if;
--Conclusion
select description into v_des from result_template
where result_template_id = v_res.result_template_id;
if(regexp_like(v_des,‘\-‘) or regexp_like(v_des,‘－‘))
then
--单个指标
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘))
then
select
regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template where
result_template_id = v_res.result_template_id
;
select
regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template where
result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for
v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,3) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,4) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,3) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,4) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
if(regexp_like(v_des,‘<‘) or regexp_like(v_des,‘＜‘))
then
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from result_template
where result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
if(regexp_like(v_des,‘>‘) or regexp_like(v_des,‘＞‘))
then
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘) )
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_min from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from
result_template where result_template_id = v_res.result_template_id
;
end
if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_min from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
--如果参数是一个单词，肯定正常
if(regexp_like(t_result,‘\s*[a-zA-Z]+\s*‘))
then
--单词一样
if(instr(upper(v_des),upper(t_result))) >=0
then
update
result set Conclusion =‘‘ where current of c3
;
else
update
result set Conclusion =‘↑‘ where current of c3
;
end
if;
else
if(regexp_like(v_min,‘\d+(\.\d+)?‘) and to_number(t_result)<to_number(v_min))
then
update result set Conclusion =‘↓‘ where
current of c3 ;
end
if;
if(regexp_like(v_max,‘\d+(\.\d+)?‘) and to_number(t_result)>to_number(v_max)
) then
update result set Conclusion =‘↑‘
where current of c3;
end
if;
end if;
--formatted_unit,original_unit
update result
set formatted_unit =
(select formatted_unit
from result_template r where result_template_id =
v_res.result_template_id)
where current of
c3;
update result set
original_unit=
(select original_unit from
result_template r where result_template_id =
v_res.result_template_id)
where current of
c3;
--old_status
update result set old_status = v_res.old_status||v_res.status where
current of c3;
--status
update result set status = ‘C‘ where
current of c3;
--completed_on
update result set completed_on =
sysdate where current of c3;
--completed_by
update result set completed_by =
v_res.created_by where current of c3;
end
loop;
commit;
else
for v_res in c2
loop
--original_result
if(regexp_like(t_result,‘(^#+)‘)) then
update result set original_result = ‘‘ where current of
c2;
p_rows :=
SQL%ROWCOUNT;
else
update result set original_result =
t_result where current of c2;
p_rows := SQL%ROWCOUNT;
end if;
--raw_numeric_result
if(regexp_like(t_result,‘(^[+-]?\d{0,}\.?\d{0,}$)‘))
then
update result set raw_numeric_result
= original_result where current of c2;
end
if;
--formatted_result
select format into v_format from result_template where result_template_id =
v_res.result_template_id;
if(v_format is null) then
update result set formatted_result=original_result
where current of c2;
end if;
if(regexp_like(v_format,‘0\.0+‘)) then
select
to_number(lengthb(v_format)-instr(v_format,‘.‘)) into v_n from
dual;
update result set
formatted_result=trim(to_char(round(original_result,v_n),(select
to_char(rpad(‘99999990.99‘,9+v_n,9)) from dual)))where current of c2;
end if;
--Conclusion
select description into v_des from
result_template where result_template_id =
v_res.result_template_id;
if(regexp_like(v_des,‘\-‘) or regexp_like(v_des,‘－‘))
then
--单个指标
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘))
then
select
regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template where
result_template_id = v_res.result_template_id
;
select
regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template where
result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for
v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,3) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,4) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,3) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,4) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
if(regexp_like(v_des,‘<‘) or regexp_like(v_des,‘＜‘))
then
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from result_template
where result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_max from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_max from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
if(regexp_like(v_des,‘>‘) or regexp_like(v_des,‘＞‘))
then
if (not
regexp_like(v_des,‘/‘) and not regexp_like(v_des,‘\*‘) )
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from result_template
where result_template_id = v_res.result_template_id
;
else
--多个指标的情况,判断性别
if (regexp_like(v_des,‘/‘)) then
for v_sex in c4
loop
if(regexp_like(v_sex.u_subject_sex,‘女‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_min from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_sex.u_subject_sex,‘男‘))
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from
result_template where result_template_id = v_res.result_template_id
;
end
if;
end loop;
--多个指标的情况,判断年龄,以50岁为界
else
for
v_age in c5
loop
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)>50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,2) into v_min from result_template
where result_template_id = v_res.result_template_id
;
end
if;
if(regexp_like(v_age.u_age,‘\d+(\.\d+)?‘) and to_number(v_age.u_age)<=50)
then
select regexp_substr(v_des,‘\d+(\.\d+)?‘,1,1) into v_min from
result_template where result_template_id = v_res.result_template_id
;
end if;
end loop;
end
if;
end
if;
end if;
--如果参数是一个单词
if(regexp_like(t_result,‘\s*[a-zA-Z]+\s*‘))
then
--单词一样
if(instr(upper(v_des),upper(t_result))) >=0
then
update
result set Conclusion =‘‘ where current of c2
;
else
update
result set Conclusion =‘↑‘ where current of c2
;
end
if;
else
if(regexp_like(v_min,‘\d+(\.\d+)?‘) and to_number(t_result)<to_number(v_min))
then
update result set Conclusion =‘↓‘ where
current of c2 ;
end
if;
if(regexp_like(v_max,‘\d+(\.\d+)?‘) and to_number(t_result)>to_number(v_max)
) then
update result set Conclusion =‘↑‘
where current of c2;
end
if;
end if;
--formatted_unit,original_unit
update result
set formatted_unit =
(select formatted_unit
from result_template r where result_template_id =
v_res.result_template_id)
where current of
c2;
update result set
original_unit=
(select original_unit from
result_template r where result_template_id =
v_res.result_template_id)
where current of
c2;
--old_status
update result set old_status = v_res.old_status||v_res.status where
current of c2;
--status
update result set status = ‘C‘ where
current of c2;
--completed_on
update result set completed_on =
sysdate where current of c2;
--completed_by
update result set completed_by =
v_res.created_by where current of c2;
end
loop;
commit;
end if;
exception
when others
then
p_rows:=‘0‘;
dbms_output.put_line(‘sqlcode:‘||sqlcode);
dbms_output.put_line(‘sqlerrm:‘||sqlerrm);
rollback;
execute
immediate ‘alter trigger tu_post_result_status
enable‘;
end;
原文：http://www.cnblogs.com/wenbing/p/3605184.html