oracle存储过程是段吗,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

-- constant := ;

-- Public variable declarations

--;

-- Public function and procedure declarations

--function () return ;

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();

}