oracle 成绩分段 存储过程实现

将学生成绩score进行分段
start_score 起始分数
end_score 总分
section 分数间隔

//SUCCESS存储过程拼接
SELECT COUNT(*) AS COUNT FROM AAA WHERE SCORE >= 0 AND SCORE < 10
UNION ALL
SELECT COUNT(*) AS COUNT FROM AAA WHERE SCORE >= 10 AND SCORE < 20
UNION ALL
SELECT COUNT(*) AS COUNT FROM AAA WHERE SCORE >= 20 AND SCORE < 30
UNION ALL
SELECT COUNT(*) AS COUNT FROM AAA WHERE SCORE >= 30 AND SCORE < 40

// create package
create or replace package score_section_package is

  -- Author  : 罗蓉蓉
  -- Created : 2013/11/16 20:40:07
  -- Purpose :
 
  -- Public type declarations
  type return_cursor is ref cursor;
 
  -- Public constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Public variable declarations
  --<VariableName> <Datatype>;

  -- Public function and procedure declarations
  --function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;

end score_section_package;


// create produre
create or replace procedure score_section(start_score in Integer,end_score in Integer,section in Integer, p_current out score_section_package.return_cursor) is
   --BY 罗蓉蓉  
  

   --tempsql varchar2(300);--用来定义sql语句 
  -- insertsql varchar2(800);--用来定义sql语句 
   --current_time varchar2(10);
   --tjsj  varchar2(10);
   sql_score varchar2(1000);
   text number;
   count0 Integer;
   j number;
begin
    j := 1;
    text :=start_score;
   --count0 分数段
   count0 :=mod((end_score - start_score),section);
   if count0!=0 then
      count0 :=1+(end_score - start_score)/section;
   else  
      count0 :=(end_score - start_score)/section;
   end if;
      
    while j <= count0
    loop
     if (text+section)>end_score then
      sql_score :=sql_score||'select count(*) as count from AAA where score >= '||text||' and score <= '||end_score;
     else
        sql_score :=sql_score||'select count(*) as count from AAA where score >= '||text||' and score < '||(text+section);
       -- sql_score :=sql_score||' union all ';
     end if;
   --text :=start_score;
    text :=text+section;   
    -- @
     if j!=count0 then
      sql_score :=sql_score||' union all ';
     end if;
    -- @
     j := j + 1;
    end loop;
    open p_current for sql_score; end score_section;
 
//java 代码中调用存储过程
try {
                    Connection conn = DataAccess.getConnection();
                    CallableStatement cs = null;
                    ResultSet rs = null;
                    if (cs == null)
                        cs = conn.prepareCall("{call score_section(0,100,33,?)}");
                    cs.registerOutParameter(1, OracleTypes.CURSOR);
                    cs.execute();
                    rs = (ResultSet) cs.getObject(1);
                    while (rs.next()) {
                        System.out.println(rs.getString(1));
                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

评论

2 楼 1021082712 2014-04-14  

哈哈，我以后会改进滴，多谢提醒

1 楼 joaboo 2014-01-28  

LZ应该把原始数据,结果数据都贴出来..这样一大堆代码,太不直观了..