oracle存储过程

最新推荐文章于 2022-12-23 14:20:07 发布
最新推荐文章于 2022-12-23 14:20:07 发布
阅读量672 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/aiyinsitan215/article/details/10491647
版权 
最近写了一个存储过程，用来给没填写周报的员工发送邮件。技术有限，写的比较繁琐
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;


 

aiyinsitan215
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle 存储过程语句总结
07-05 5299
oracle 循环语句总结： 1、Exit When循环： create or replace procedure proc_test_exit_when is  i number;  begin  i:=0;  LOOP  Exit When(i>5);  Db
oracle 存储过程
11-20
Oracle存储过程不仅包括SQL语句，还可以包含PL/SQL（Oracle的编程语言）块，如条件判断、循环结构、异常处理等，极大地提高了数据库操作的效率和灵活性。 1. **存储过程的创建**： 创建存储过程的基本语法是`...
oracle 的exit,oracle存储过程中return和exit区别概述及测试
weixin_36472625的博客
04-03 155
复制代码 代码如下:create or replace procedure Test5(o_cellphone in varchar2) isv_cellphone cc_quiz_stat.cellphone %type;v_name cc_quiz_stat %rowtype;v_state cc_quiz_stat.state %type;begindeclarecursor cur_cc ...
oracle中exit和≤,[数据库]oracle存储过程中return和exit区别
weixin_39634052的博客
04-10 123
[数据库]oracle存储过程中return和exit区别0 2013-02-19 12:00:15createorreplaceprocedureTest5(o_cellphoneinvarchar2)isv_cellphonecc_quiz_stat.cellphone%type;v_namecc_quiz_stat%rowtype;v_state...
oracle函数exit,oracle存储过程中return和exit区别概述及测试
weixin_39542340的博客
04-05 148
代码如下:create or replace procedure Test5(o_cellphone in varchar2) isv_cellphone cc_quiz_stat.cellphone %type;v_name cc_quiz_stat %rowtype;v_state cc_quiz_stat.state %type;begindeclarecursor cur_cc issel...
oracle存储过程中return和exit区别
Dream_ling的博客
07-24 5789
create or replace procedure Test5(o_cellphone in varchar2) is   v_cellphone cc_quiz_stat.cellphone %type;   v_name      cc_quiz_stat %rowtype;   v_state     cc_quiz_stat.state %type; begin   decl...
帆软报表Oracle存储过程解决storeParameter1参数试用插件
12-12
总结起来，"帆软报表Oracle存储过程解决storeParameter1参数试用插件"主要是针对在调用无参数Oracle存储过程时出现的异常问题提供的一种解决方案。通过安装并配置这个插件，用户可以顺利地在帆软报表中调用不包含...
oracle存储过程unwrap解密工具.zip
04-28
Oracle存储过程unwrap解密工具主要用于处理Oracle数据库中的加密存储过程。在Oracle数据库系统中，为了保护敏感代码或数据，开发人员有时会选择对存储过程进行加密。然而，当需要查看、调试或恢复这些加密的存储过程...
C# 传入自定义列表List 到Oracle存储过程
09-11
本文将详细讲解如何在C#中使用自定义列表(List)作为参数调用Oracle存储过程，以及实现这一功能的关键技术和注意事项。 首先，我们需要了解Oracle数据库中的PL/SQL类型，例如VARCAR2、NUMBER等，它们对应于C#中的...
springboot整合mybatis调用oracle存储过程
最新发布
08-17
本文将深入探讨如何在Spring Boot项目中整合MyBatis，实现调用Oracle存储过程并处理游标返回的数据。 首先，我们需要在Spring Boot项目中引入相关的依赖。在`pom.xml`文件中添加Oracle JDBC驱动（ojdbc66-oracle...
oracle存储过程循环更新数据库,oracle 存储过程循环语法
weixin_39868248的博客
04-07 1503
1、简单循环语法loopstatements;end loop;例子：counter := 0;loopcounter := counter + 1;exit when counter = 5;end loop;备注：exit语句立即结束循环，exit when 语句在指定条件出现时停止循环(可以出现在循环代码中的任意位置)2、while循环语法while condition loopstatem...
oracle 存储过程详细解释
小小彭
11-16 588
create or replace procedure URP_USER1 is v_err varchar2(512); --错误代码 CURSOR user1 is select t.* from URP_DEPT1 t where t.yydz is null; URP_USER1 user1%rowtype; begin open user1; loop FETCH user1 INTO URP_USER...
ORACLE 存储过程的五大循环
zhanglujie2008的专栏
04-05 1294
1、Exit When循环： create or replace procedure proc_test_exit_when is i number; begin i:=0; LOOP Exit When(i>5); Dbms_Output.put_line(i); i:=i+1; END LOOP; end proc_test_exit_when; --结果：0 1 2 3
oracle存储过程----------循环语句，判断语句，游标简单使用
m0_58934558的博客
12-23 4445
EXIT WHEN --条件满足，退出循环语句。语句：FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP。[cursor变量名称]([参数名称] [参数类型])fetch [cursor变量] into [循环变量]when [cursor变量] %notfound;END LOOP [循环标签];[SQL语句，可以使用传入参数]语句： WHILE LOOP。每循环一次，循环变量自动加1；语句：IF 判断条件 THEN。跟在IN REVERSE。
%notfound的理解
lpx982083543的博客
11-09 5831
%notfound的理解——oracle存储过程 . 文档中的解释：It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE. 这个解释更加精妙： %NOT
oracle循环语句小结
weixin_33739523的博客
11-10 181
主要有以下五种循环：Exit When、Loop、While、For(普通循环)、For(游标循环)，下面举例一一说明(均为存储过程)。 1、Exit When循环： create or replace procedure proc_test_exit_when is i number; begin i:=0; LOOP Exit When(i>5);...
%NOTFOUND 用法
wacthamu的专栏
10-30 3638
文档中的解释：It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE. 这个解释更加精妙： %NOTFOUND is the logical opposi
游标v_cur的%notfound
THEWORLDKIND的专栏
08-04 2289
当游标的值为空的时候，处理无效员工的id时就会出现错误. 比如： 创建一个程序检索某个员工的工龄    a.创建一个函数GET_SERVICE_YRS，检索某个员工总的工龄；     此函数应该接受员工ID作为输入参数，返回工龄。增加例外处理无效的员工id输入；    b.调用函数，你能使用下面的数据 EXECUTE DBMS_OUTPUT.PUT_LINE(get_service_y
oracle关于存储过程游标cursor和循环loop使用
ckh_user的博客
08-15 5792
关于oracle 存储过程游标cursor和循环loop使用
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值