本文主要是介绍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();
}
这篇关于oracle 成绩分段 存储过程实现的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!