oracle存储过程
来源：互联网 发布：福建广电网络客服电话 编辑：程序博客网 时间：2024/06/06 19:51
最近写了一个存储过程，用来给没填写周报的员工发送邮件。技术有限，写的比较繁琐
CREATE OR REPLACE PROCEDURE PROC_WEEKALARM( flag out varchar)as --部门游标 type curedept is ref cursor; V_CURSOR2 curedept; --周报类型游标 type curetype is ref cursor; V_CURSOR3 curetype; --部门下面员工游标 type curestaff is ref cursor; V_CURSOR curestaff; --查询项目经理的员工id游标 CURSOR cureprjstaff is select staffid from ( select staff.staffid from t_prj_info info inner join emp_staffbase staff on info.prj_person = staff.staffid inner join emp_staffrelation re on re.staffid = staff.staffid inner join sys_dept dep on re.deptid = dep.deptid and (dep.chargeid is not null or dep.chargeid!='') left join ( select nvl(count(*),0) rzs ,prj.rpt_prjid from t_rpt_prj prj where prj.rpt_weeknum=(to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1)) group by prj.rpt_prjid )a on a.rpt_prjid = info.prj_id and a.rzs is null or a.rzs=0 where info.prj_sts <>'PRJ002002' )b group by staffid; --查询所有的副总的员工id游标 CURSOR curecharge is select dep.chargeid from sys_dept dep where dep.chargeid is not null group by dep.chargeid; -- 项目名称 type curepjname is ref cursor; V_CURSOR1 curepjname; --周报提醒行数据 deptid WEEKALARM.DEPTID%TYPE; --部门id alarmtype WEEKALARM.ALARMTYPE%TYPE;--周报提醒类型 weeknum WEEKALARM.WEEKNUM%TYPE; --周次 content WEEKALARM.Emailcontent%type; --内容 staffname WEEKALARM.STAFFNAME%TYPE; --员工名字 tempdeptid WEEKALARM.DEPTID%TYPE; --临时部门id sqlstr varchar2(4000); sqlstr1 varchar2(4000); sqlstr2 varchar2(4000); temptent varchar2(4000); cstaffid weekalarm.staffid%type; -- 员工id email weekalarm.staffemail%type; firstday varchar2(30); --上周第一天 endday varchar2(30); --上周最后一天 titletype WEEKALARM.EMAILTITLETYPE%type; --邮件标题类型 title WEEKALARM.EMAILTITLE%type; --邮件标题 chageid sys_dept.chargeid%type; --副总的员工id deptname sys_dept.deptname%type; --部门名称 altype weekalarm.alarmtype%type; --周报类型 BEGIN firstday := to_char(trunc(sysdate-7,'d'),'yyyy-mm-dd'); endday := to_char(trunc(sysdate-7,'d')+6,'yyyy-mm-dd'); -- 设置默认值 flag:='0'; delete from weekalarm; --将上周没有填写周报日志的员工插入到weekalarm INSERT INTO WEEKALARM(WEEKALARMID,STAFFID,ALARMTYPE,Emailtitletype,Emailtitle,STAFFNAME ,STAFFEMAIL,DEPTID,WEEKNUM,EMAILCONTENT,SENDFLAG,CREATEDATE,MODIFYDATE) select SEQ_SYS.NEXTVAL ,staff.staffid ,'ZBTXLX001' ,'ZBYJBT001' ,title.paramname ,staff.staffname ,staff.email ,re.deptid ,to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1)--'周次格式为 2013-32 2013第32周' ,staff.staffname||':<br/>'||'你好,'||'你'||to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1)||'周次('||firstday||'~'||endday||')有日志没有填写。请到内部系统'||'<a href='''||'http://oa.dimine.net:6666/oa/login.jsp'||'''>'||'迪迈内部系统'||'</a>去填写，谢谢!' ,'0' ,sysdate ,sysdate from emp_staffbase staff left join( select nvl(count(*),0) rzs,detail.rpt_createid from t_rpt_psn_daydetail detail where to_char(detail.rpt_date,'yyyy-mm-dd')>to_char(sysdate-8,'yyyy-mm-dd') and to_char(detail.rpt_date,'yyyy-mm-dd')<=to_char(sysdate-2,'yyyy-mm-dd') group by detail.rpt_createid) a on a.rpt_createid = staff.staffid inner join emp_staffrelation re on re.staffid = staff.staffid inner join sys_dept dep on re.deptid = dep.deptid and (dep.chargeid is not null or dep.chargeid!='') inner join sys_dict title on title.dictid='ZBYJBT001' where a.rzs is null or a.rzs<5; --项目经理项目周报未填的插入到weekalarm open cureprjstaff; loop fetch cureprjstaff into cstaffid; exit when cureprjstaff%NOTFOUND; sqlstr:= 'select staff.staffid,staff.staffname,title.paramname,staff.email,dep.deptid ,to_char(sysdate,''yyyy'')||(to_char(sysdate,''WW'')-1)'--'周次格式为 2013-32 2013第32周' ||' ,info.prj_name ' ||' from t_prj_info info ' ||' inner join emp_staffbase staff on ' ||' info.prj_person = staff.staffid and info.prj_person='''||cstaffid||'''' ||' inner join emp_staffrelation re on re.staffid = staff.staffid ' ||' inner join sys_dept dep on re.deptid = dep.deptid and (dep.chargeid is not null or dep.chargeid!='''') ' ||' inner join sys_dict title on title.dictid=''ZBYJBT002''' ||' left join ( ' ||' select nvl(count(*),0) rzs ,prj.rpt_prjid from t_rpt_prj prj ' ||' where prj.rpt_weeknum=(to_char(sysdate,''yyyy'')||(to_char(sysdate,''WW'')-1)) ' ||' group by prj.rpt_prjid ' ||' )a ' ||' on a.rpt_prjid = info.prj_id and a.rzs is null or a.rzs=0 ' ||' where info.prj_sts <>''PRJ002002'''; --打开查询一项目经理所有的项目 open V_CURSOR1 for sqlstr; content :=''; loop --拼接同一个项目经理的项目 fetch V_CURSOR1 into cstaffid,staffname, title,email,deptid,weeknum,temptent; exit when V_CURSOR1%notfound; content := content||temptent||','; end loop; close V_CURSOR1; --去除最后一个调号 content:= substr(content,0,length(content)-1); content:=content||'<br/>'||(to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1))||'周次('||firstday||'~'||endday||')项目周报未填写。请到内部系统'||'<a href='''||'http://oa.dimine.net:6666/oa/login.jsp'||'''>'||'迪迈内部系统'||'</a>去填写，谢谢!'; --将项目经理的项目周报日志插入到数据库 INSERT INTO WEEKALARM(WEEKALARMID,STAFFID,ALARMTYPE,Emailtitletype,Emailtitle,STAFFNAME,STAFFEMAIL,DEPTID,WEEKNUM,EMAILCONTENT,SENDFLAG,CREATEDATE,MODIFYDATE) values (SEQ_SYS.NEXTVAL,cstaffid,'ZBTXLX002','ZBYJBT002',title,staffname,email,deptid,weeknum,content,'0',sysdate,sysdate); end loop; close cureprjstaff; --将所有的员工要发送给部门经理的插入到数据库 --打开游标 open curecharge; loop fetch curecharge into chageid; exit when curecharge%NOTFOUND; sqlstr2:='select ala.alarmtype from weekalarm ala where (ala.alarmtype=''ZBTXLX002'' or ala.alarmtype=''ZBTXLX001'')' ||'and ala.weeknum='''||(to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1))||'''' ||'group by ala.alarmtype'; --周报类型游标 open V_CURSOR3 for sqlstr2; loop fetch V_CURSOR3 into altype ; exit when V_CURSOR3%NOTFOUND; content:=''; --部门游标 sqlstr1:='SELECT week.deptid,week.alarmtype,week.weeknum FROM WEEKALARM week ' ||' inner join sys_dept dep on dep.deptid=week.deptid and dep.chargeid='''||chageid||'''' ||' where week.weeknum='''||to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1)||''''||' and week.alarmtype='''||altype||'''' ||'GROUP BY week.deptid,week.alarmtype,week.weeknum'; open V_CURSOR2 for sqlstr1; loop fetch V_CURSOR2 into deptid,alarmtype,weeknum ; --select dep.deptname into deptname from sys_dept dep where dep.deptid = deptid; exit when V_CURSOR2%NOTFOUND; sqlstr:='SELECT alarm.staffname,alarm.emailcontent,dep.deptname FROM WEEKALARM alarm ' ||'inner join sys_dept dep on dep.deptid=alarm.deptid ' ||' WHERE alarm.DEPTID ='''||deptid ||''' and alarm.alarmtype='''||alarmtype||''' and alarm.weeknum='''||weeknum||''''; open V_CURSOR for sqlstr; loop fetch V_CURSOR into staffname,temptent,deptname; exit when V_CURSOR%notfound; --如果是项目周报则加上周报的内容 if alarmtype='ZBTXLX002' then content:=content||'<tr>'||'<td align=''center'' class=''newtd1'' >'||deptname||'</td>'||'<td align=''center'' class=''newtd1'' >'||staffname||'</td>'||'<td align=''center'' class=''newtd1''>'||substr(temptent,0,instr(temptent,'<br/>')-1)||'</td>'||'</tr> '; else if alarmtype='ZBTXLX001' then content:=content||'<tr>'||'<td align=''center'' class=''newtd1'' >'||deptname||'</td>'||'<td align=''center'' class=''newtd1'' >'||staffname||'</td>'||'</tr> '; end if; end if; -- dbms_output.put_line(alarmtype); end loop; close V_CURSOR; end loop; close V_CURSOR2; --关闭游标 --截取邮件内容最后一个'，' content:= substr(content,0,length(content)-1); if altype='ZBTXLX002' then --如果类型是项目周报，则周报的标题类型为 'ZBYJBT004' titletype :='ZBYJBT004'; select di.paramname into title from sys_dict di where di.dictid='ZBYJBT004' ; content:= '以下表格是'||to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1)||'周次('||firstday||'~'||endday||')项目周报未填写的员工，敬请提醒。谢谢!'||'<table align=''center'' class=''newtable'' border=''0'' cellpadding=''4'' cellspacing=''1'' width=''100%''>' ||'<tr><td align=''center'' class=''newtd1'' colspan=''3''>'||'项目周报未填写员工'||'</tr><tr><td align=''center'' class=''newtd1''>部门</td><td align=''center'' class=''newtd1''>姓名</td><td align=''center'' class=''newtd1''>项目名称</td></tr>'||content||'</table>'; else if altype='ZBTXLX001' then --如果类型是个人日志，则周报的标题类型为'ZBYJBT003' titletype :='ZBYJBT003'; select di.paramname into title from sys_dict di where di.dictid='ZBYJBT003' ; content:='以下表格是'||to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1)||'周次('||firstday||'~'||endday||')个人周报未填写,敬请提醒。谢谢!'||'<table align=''center'' class=''newtable'' border=''0'' cellpadding=''4'' cellspacing=''1'' width=''100%''>' ||'<tr><td align=''center'' class=''newtd1'' colspan=''2''>'||'个人周报未填写员工'||'</tr><tr><td align=''center'' class=''newtd1''>部门</td><td align=''center'' class=''newtd1''>姓名</td></tr>' ||content||'</table>' ||'<br/>'; end if; end if; -- 通过游标查出这个部门的项目经理或者员工没填日志的的内容拼好，然后插入项目周报提醒，分发给部门经理或者副总 --给部门经理发送邮件 tempdeptid := deptid; -- 给分管副总发送邮件 INSERT INTO WEEKALARM(WEEKALARMID,STAFFID,ALARMTYPE,Emailtitletype,Emailtitle,STAFFNAME,STAFFEMAIL,DEPTID,WEEKNUM,EMAILCONTENT,SENDFLAG,CREATEDATE,MODIFYDATE) select SEQ_SYS.NEXTVAL ,sta.staffid ,'ZBTXLX004' , titletype ,title ,sta.staffname ,sta.email ,staff.deptid ,to_char(sysdate,'yyyy')||(to_char(sysdate,'WW')-1)--'周次格式为 2013-32 2013第32周' ,content ,'0' ,sysdate ,sysdate from sys_dept dept inner join emp_staffrelation staff on dept.chargeid=staff.staffid inner join emp_staffbase sta on staff.staffid = sta.staffid where dept.deptid = tempdeptid; end loop; close V_CURSOR3; end loop; close curecharge; commit; --存储过程执行成功 flag:=1; END;
- Oracle：存储过程，存储函数
- Oracle-存储过程 存储函数
- Oracle存储过程、存储函数
- oracle 存储函数，存储过程
- Oracle存储过程
- 调试oracle存储过程。
- ORACLE 存储过程实战
- Oracle 存储过程
- oracle存储过程使用
- Oracle 创建存储过程
- 调试oracle存储过程
- oracle java存储过程
- 创建Oracle存储过程
- 加密oracle存储过程
- oracle存储过程分页
- oracle存储过程笔记
- ORACLE存储过程--注意事项
- oracle procedure 存储过程
- javaio学习笔记-字符流类(1)
- Qt QTreeWidget节点的添加+双击响应+删除详解
- Java基础21：IO
- android System Server 分析
- 面试题——设计一个程序：运行报错Stack Overflow Error
- oracle存储过程
- iMatrix平台新版本近日发布，功能不断完善中！
- unity3d .SendMessage() , Start()，StartCoroutine（）的先后顺序问题
- java集合框架【3】 java1.5新特性 ConcurrentHashMap、Collections.synchronizedMap、Hashtable讨论
- POJ3368_Frequent Values_solution
- C++时间函数
- JAVA异常机制
- 违反完整约束条件() – 未找到父项关键字
- 一个经典实用的iptables shell脚本
