oracle_给视图传参数

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 asselect code,title,A1,A2,A3,B1,B2,C1,C2,C3,C4,C5,D,E,F,M,N,P,boy,girl,stuisnumber,stuendnumber,yidifrom(selectdistinct(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)) xright 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 areawhere substr(area.area_code,0,2)='21' and substr(area.area_code,3,2)<>00and substr(area.area_code,5,2)='00') yleft outer join (selectdistinct(substr(stu_area_code,1,4)) areacode,count(stu.stu_area_code) yidifrom sys_student  stuwhere 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)) zon z.areacode=y.code) kon k.code=x.areacodeorder 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();//查询统计 listsuper.closeSession();return list;}