CREATE OR REPLACE PROCEDURE XXX.xXX(
//传进来的参数值,使用逗号隔开
workProSchname in varchar2(200),
xuenian in varchar2(200),
typeid in varchar2(200),
code in varchar2(200),
phase in varchar2(200),
classname in number(16)
)
AUTHID DEFINER
is
//定义一个变量
declare vsql varchar(5000);
begin
//拼接字符串
vsql:='select o.work_pro_schname,sum(work_pro_schaudit_passnum) as passnum,sum(work_pro_schaudit_faildnum) as faildnum,sum(work_pro_schaudit_dainum) as dainum
from WORKPROGRESS o inner join DEPART d on o.work_pro_schid = d.ID where d.org_code like''%'||code||'%'' and o.work_pro_phase in('||phase||')' ;
//判断传值是否为null,上面如果没有条件的话,可以使用where 1=1
if(workProSchname is not null) then
//符号||代表++,(目前遇到的存储,传值如果是String类型,则需要使用'''ss'''三个单引号将其包住)
vsql:=vsql||' and work_pro_schname in ('||workProSchname||')';
end if;
if (xuenian is not null) then
vsql:=vsql||' and work_pro_xuenian='||xuenian||'';
end if;
if (typeid is not null) then
vsql:=vsql||' and work_pro_typeid='''||typeid||'''';
end if;
if (classname is not null) then
vsql:=vsql||' and work_pro_classname='''||classname||'''';
end if;
//group by拼接到最后面
vsql:=vsql||'group by o.work_pro_schname';
//不要丢掉这句话
execute immediate vsql;
END;