oracle_给视图传参数

最新推荐文章于 2025-05-19 15:11:37 发布

原创最新推荐文章于 2025-05-19 15:11:37 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

oracel 专栏收录该内容

10 篇文章

订阅专栏

create or replace package pkg_report as 
   function get_values return varchar2;
   function get_valuee return varchar2;
   procedure set_values(ps_times in varchar2);
   procedure set_valuee(ps_times in varchar2);
end pkg_report;

create or replace view view_student1 as
select code,title,A1,A2,A3,B1,B2,C1,C2,C3,C4,C5,D,E,F,M,N,P,boy,girl,stuisnumber,stuendnumber,yidi
from
(select
distinct(substr(stu_area_code,1,4)) areacode,
count(DECODE(stu_model,'A1',stu_model))  A1,
count(DECODE(stu_model,'A2',stu_model)) A2,
count(DECODE(stu_model,'A3',stu_model)) A3,
count(DECODE(stu_model,'B1',stu_model)) B1,
count(DECODE(stu_model,'B2',stu_model)) B2,
count(DECODE(stu_model,'C1',stu_model)) C1,
count(DECODE(stu_model,'C2',stu_model)) C2,
count(DECODE(stu_model,'C3',stu_model)) C3,
count(DECODE(stu_model,'C4',stu_model)) C4,
count(DECODE(stu_model,'C5',stu_model)) C5,
count(DECODE(stu_model,'D',stu_model)) D,
count(DECODE(stu_model,'E',stu_model)) E,
count(DECODE(stu_model,'F',stu_model)) F,
count(DECODE(stu_model,'M',stu_model)) M,
count(DECODE(stu_model,'N',stu_model)) N,
count(DECODE(stu_model,'P',stu_model)) P,
count(DECODE(stu_sex,'男',stu_sex)) boy,
count(DECODE(stu_sex,'女',stu_sex)) girl,
count(DECODE(stu_isend,'0',stu_isend)) stuisnumber,--- 培训中
count(DECODE(stu_isend,'1',stu_isend)) stuendnumber-----结束培训
from sys_student where to_char(stu_apply_date,'yyyy-MM-dd')
between pkg_report.GET_VALUES() and pkg_report.GET_VALUEE()
 group by substr(stu_area_code,1,4)) x
right outer join
(select y.title,y.code,z.yidi from
(select area.area_title title,substr(area.area_code,1,4) code from sys_area area
where substr(area.area_code,0,2)='21' and substr(area.area_code,3,2)<>00
and substr(area.area_code,5,2)='00') y
left outer join (select
distinct(substr(stu_area_code,1,4)) areacode,
count(stu.stu_area_code) yidi
from sys_student  stu
where substr(stu.stu_area_code,0,4)<>substr(stu.stu_reg_add_code,0,4) and
 to_char(stu.stu_apply_date,'yyyy-MM-dd') between pkg_report.GET_VALUES() and pkg_report.GET_VALUEE()
group by substr(stu.stu_area_code,1,4)) z
on z.areacode=y.code
) k
on k.code=x.areacode
order by k.code

public List<SysStudentStatic> getStudentStatic(String[] args) {
	StringBuffer hql=new StringBuffer();
	Date d=new Date();
	SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
	String  startyear="1970-01-01";
	String endyear=sdf.format(d);
	hql.append("  from SysStudentStatic");
	SQLQuery sqlQuery= (SQLQuery) super.getSqlQuery("{call pro_studentstatic(?,?)}");//调用数据库存储过程，往视图传查询时间条件
	sqlQuery.setString(0, startyear);
	sqlQuery.setString(1, endyear);
	sqlQuery.executeUpdate();
	List<SysStudentStatic> list=super.getQuery(hql.toString()).list();//查询统计 list
	super.closeSession();
	return list;
}