简单的存储过程

 

带有输入和输出参数的存储过程
create or replace procedure TASK_REDUCE_SSPG
(t_deptid in varchar2,  
  finish   out varchar2) as
  t_taskreducedate date;
  t_reduceid number(5);
  --查询正在进行中的阶段的时间
  cursor cur_enddate is
    select r.enddate
    from t_business_task_reduce r, t_business_task t
    where r.taskid = t.id
    and t.deptid = t_deptid
    and r.isfinishstate='1';
  begin
   finish:=80;
   t_reduceid:=0;
   open cur_enddate; --打开游标
   loop
      fetch cur_enddate --遍历
      into t_taskreducedate;
     exit when cur_enddate%notfound; --当游标为空跳出
     --当前时间没有阶段结束的
    if(trunc(sysdate-t_taskreducedate)<=0) then
        finish:=finish;
     end if;
    
    if(trunc(sysdate-t_taskreducedate)>0) and (trunc(sysdate-t_taskreducedate)<=3) then
      finish := finish;
    end if;
   
    if(cur_enddate%rowcount=1) and(trunc(sysdate-t_taskreducedate)>3)
      and (trunc(sysdate-t_taskreducedate)<=8) then
      finish := finish-30;
    end if;
   
    if(cur_enddate%rowcount>1) and (trunc(sysdate-t_taskreducedate)>3)
    and (trunc(sysdate-t_taskreducedate)<=8)  then
     if finish=50 then
          finish:=50;
       end if;
      if finish=20 then
        finish:=20;
      end if;
      if finish=80 then
        finish:=finish-30;
      end if;
     end if;
    
    if(trunc(sysdate-t_taskreducedate)>8) then
      finish := finish-70;
    end if;
   
    --判断红色线的最低
    if finish <=20 then
         finish := 20;
    end if;
    end loop;
    close cur_enddate; --关闭游标
  commit;
end;

调用上面的存储过程
public String findreducefinish(String deptid){
  String i="";
  String pSql = "{call TASK_REDUCE_SSPG(?,?)}";//存储过程名称
  Connection con = null;
  CallableStatement stCall = null;
  try {
   con = this.getSession().connection();
   stCall = con.prepareCall(pSql);
   stCall.setString(1, deptid);
   stCall.registerOutParameter(2, Types.VARCHAR);//输出值即返回的值
   stCall.execute();
   i = stCall.getString(2);  //取出输出值    
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return i;
 }

 

//添加方法的存储过程
public void Savehistory(TBusinessTaskHistory history){
  String pSql = "{call TASK_HISTORY(?,?,?,?,?,?,?)}";
  Connection con = null;
  CallableStatement stCall = null;
  try {
   con = this.getSession().connection();
   stCall = con.prepareCall(pSql);
   stCall.setString(1,history.getId());
   stCall.setString(2,history.getReduceid());
   stCall.setString(3,history.getRecordusername());
   stCall.setString(4,history.getRecorddeptname());
   stCall.setString(5,history.getNotion());
   stCall.setString(6,history.getOpsign());
   stCall.setString(7,history.getStatus());
   stCall.execute();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  } 
 }

create  or  replace  procedure  query_emp

as

     type emp_table  is  table  of  emp%rowtype;

     emps emp_table;

begin  

     select  * bulk collect  into  emps  from  emp;

     for  i  in  1..emps. count  loop

         dbms_output.put_line(emps(i).ename || ',' ||emps(i).job);

     end  loop; 

end ;