oracle 函数 输入值,查询数据,返回相应处理结果

create or replace function FUN_SEARCH_ORDERBY
( INSTACEID in varchar2,TYE IN varchar2)
return  varchar2
is
num number;
A number;
B number;
F number;
kv_ys varchar2(100);
kv_zg varchar2(100);
kv_tj varchar2(100);
cv_ys varchar2(100);
cv_zg varchar2(100);
cv_tj varchar2(100);
gv_ys varchar2(100);
gv_zg varchar2(100);
gv_tj varchar2(100);
begin
  num:=0;
  if TYE='0' then
    --说明为矿 已整改为3 已提交为2 已验收为1
   kv_ys :='80f3fdc9-25d5-48d9-aeb6-51f860ebc65e';
   select count(*) into A from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=kv_ys;--验收
   kv_zg :='1e9b5b88-a2b1-456c-a52f-197decb74c9c';
   select count(*) into B from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=kv_zg;--整改
   kv_tj :='a2342268-2f72-49be-9073-625220bed3b4';
   select count(*) into F from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=kv_tj;--提交  
    if A=1 THEN num:=1;
     elsif B=1 THEN num:=3;
     elsif F=1 THEN num:=2;
     end if;
   end if;
   
   if TYE='1' then
    --说明为厂
   cv_ys :='b31a3d30-301e-4bf2-98bc-c223b172dfb0';
   select count(*) into A from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=cv_ys;--验收
   cv_zg :='90d7c167-f9a9-4b4b-beed-ee1e43f9ec8e';
   select count(*) into B from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=cv_zg;--整改
   cv_tj :='bed2a800-adfc-4269-831f-cfee05bc6336';
   select count(*) into F from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=cv_tj;--提交  
    if A=1 THEN num:=1;
     elsif B=1 THEN num:=3;
     elsif F=1 THEN num:=2;
     end if;
   end if;
   
      if TYE='2' then
    --说明为厂
   gv_ys :='53b83eeb-ec91-4b59-bf2b-a5a80d6089f6';
   select count(*) into A from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=gv_ys;--验收
   gv_zg :='7e9cb10c-2119-475b-823b-9ce8e2d6490f';
   select count(*) into B from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=gv_zg;--整改
   gv_tj :='5e9880f8-e427-4fd3-9ee3-872095bb9e8b';
   select count(*) into F from workflowtask where Status =2  AND   InstanceID = INSTACEID  AND stepid=gv_tj;--提交  
    if A=1 THEN num:=1;
     elsif B=1 THEN num:=3;
     elsif F=1 THEN num:=2;
     end if;
   end if;

return num;
end;

 

posted on 2017-09-22 18:05  小东北  阅读(2924)  评论(0编辑  收藏  举报