《完整项目实例.docx》由会员分享，可在线阅读，更多相关《完整项目实例.docx（40页珍藏版）》请在三一办公上搜索。
1、完整项目实例第25章 完整项目实例 作业collectData的代码如下所示： declare datebuffer datetime -存时间临时变量 declare cur_emp cursor 游标 /将表fcTable中Cdate列中的最大值赋给游标变量cur_emp set cur_emp = Cursor local for select max(CDate) from fcTable /打开游标 open cur_emp /将游标中的值取出赋予给变量datebuffer fetch next from cur_emp into datebuffer /将按条件检索出的值插入到表f
2、cTable(表fcTable在bussiness服务器中) insert into fcTable select * from bussiness.hrdb.dbo.fctable where CDatedatebuffer insert into fcTable select * from nssmlib.hrdb.dbo.fctable where CDatedatebuffer insert into fcTable select * from nssmoffice.hrdb.dbo.fctable where CDatedatebuffer close cur_emp dealloc
3、ate cur_emp GO query.jsp代码如下： 查询结果 姓 名： 员工号： 部门： 日期 一号卡机记录 二号卡机记录 三号卡机记录 % Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver).newInstance; Connection con=java.sql.DriverManager.getConnection(jdbc:microsoft:sqlser
4、ver:/127.0.0.1:1455;DatabaseName=HrDB,bn,bn); Statement stmt=con.createStatement; /获取输入框的值 String strStaffNum=request.getParameter(staffNum); String strBeginYear=request.getParameter(beginyear); String strBeginMonth=request.getParameter(beginmonth); String strBeginDay=request.getParameter(beginday);
5、 String strEndYear=request.getParameter(endyear); String strEndMonth=request.getParameter(endmonth); String strEndDay=request.getParameter(endday); String strStartDate=strBeginYear+strBeginMonth+strBeginDay; String strEndDate=strEndYear+strEndMonth+strEndDay; /调用存储过程sp_Refurbish String strSql1= call
6、 sp_Refurbish ; CallableStatement sqlStmt1 =con.prepareCall(strSql1); sqlStmt1.execute; sqlStmt1.close; /调用存储过程staffNo String strSql = call staffNo(?,?,?); CallableStatement sqlStmt = con.prepareCall(strSql); /向存储过程传递变量 sqlStmt.setString(1, strStartDate); sqlStmt.setString(2, strEndDate); sqlStmt.se
7、tString(3, strStaffNum); sqlStmt.execute; sqlStmt.close; /结果集 ResultSet rst=stmt.executeQuery(select distinct(dkDate) from dkDate order by dkDate); /按要求输出结果集的内容 while(rst.next) String strDate=rst.getString(1); out.println(); out.println(+strDate+); out.println( ); Statement stmt0=con.createStatement
8、; ResultSet rst0= stmt0.executeQuery(select firstCardRecord from staffNoQueryTemp1 where dkDate=+strDate+); while(rst0.next) out.println(); out.println(+rst0.getString(1)+); out.println(); stmt0.close; rst0.close; out.println( ); out.println( ); Statement stmt2=con.createStatement; ResultSet rst2= s
9、tmt2.executeQuery(select secondCardRecord from staffNoQueryTemp2 where dkDate=+rst.getString(1)+); while(rst2.next) out.println(); out.println(+rst2.getString(1)+); out.println(); stmt2.close; rst2.close; out.println( ); out.println( ); Statement stmt3=con.createStatement; ResultSet rst3= stmt3.exec
10、uteQuery(select thirdCardRecord from staffNoQueryTemp3 where dkDate=+rst.getString(1)+); while(rst3.next) out.println(); out.println(+rst3.getString(1)+); out.println(); stmt3.close; rst3.close; out.println(); out.println(); /关闭连接、释放资源 rst.close; stmt.close; con.close; % sp_Refurbish的代码如下： CREATE PR
11、OCEDURE sp_Refurbish AS declare datebuffer datetime -存时间临时变量 declare cur_emp cursor 游标 /将表fcTable中Cdate列中的最大值赋给游标变量cur_emp set cur_emp = Cursor local for select max(CDate) from fcTable /打开游标 open cur_emp /将游标中的值取出赋予给临时变量datebuffer fetch next from cur_emp into datebuffer insert into fcTable select *
12、from bussiness.hrdb.dbo.fctable where CDatedatebuffer insert into fcTable select * from nssmlib.hrdb.dbo.fctable where CDatedatebuffer insert into fcTable select * from nssmoffice.hrdb.dbo.fctable where CDatedatebuffer close cur_emp deallocate cur_emp GO staffNo的代码如下： CREATE PROCEDURE staffNo strbeg
13、inDate varchar(8), strendDate varchar(8), SelNo varchar(80) AS declare beginDate datetime declare endDate datetime declare datebuffer datetime -存时间临时变量 declare staffno char(10) -员工号 declare staff varchar(80) -员工 declare dkdate datetime -查出的打卡时间 declare kqlName char(20) declare cur_emp cursor /清空表sta
14、ffNoQueryTemp1、staffNoQueryTemp2、staffNoQueryTemp3、dkDate DELETE FROM staffNoQueryTemp1 DELETE FROM staffNoQueryTemp2 DELETE FROM staffNoQueryTemp3 DELETE FROM dkDate /将参数的值转换成datetime类型后赋给一个变量 select beginDate = Convert(datetime,strbeginDate) select endDate = Convert(datetime,strendDate) select dat
15、ebuffer = beginDate /循环 while datebuffer=endDate begin set cur_emp = Cursor local for select CDate, KqlName from V_Fcard where StaffNo=selNo and datepart(yy,CDate)=datepart(yy,datebuffer) and datepart(mm,CDate)=datepart(mm,datebuffer) and datepart(dd,CDate)=datepart(dd,datebuffer) order by CDate ope
16、n cur_emp Fetch NEXT From cur_emp into dkdate,kqlName /当游标还没到最后 while(fetch_status=0) begin insert into dkDate values(Convert(char(10),datebuffer,120) if(kqlName=1号机) begin insert into staffNoQueryTemp1 values(Convert(char(10),datebuffer,120), Convert(char(20),dkdate,108) end else if (kqlName=2号机) b
17、egin insert into staffNoQueryTemp2 values(Convert(char(10),datebuffer,120), Convert(char(20),dkdate,108) end else begin insert into staffNoQueryTemp3 values(Convert(char(10),datebuffer,120), Convert(char(20),dkdate,108) end Fetch NEXT From cur_emp into dkdate,kqlName end select datebuffer = dateAdd(
18、day,1,datebuffer) end /释放游标资源 close cur_emp deallocate cur_emp GO allStaffQuery.jsp代码如下： 查询全部员工考勤记录 员工号 员工名 日期 一号卡机记录 二号卡机记录 三号卡机记录 % /请参考上面注释 Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver).newInstance; Connection con=java.sql.DriverManager.getConnection(jdbc:microsoft:sqlserver:/127.0.
19、0.1:1455;DatabaseName=HrDB,bn,bn); Statement stmt=con.createStatement; String strBeginYear=request.getParameter(beginyear); String strBeginMonth=request.getParameter(beginmonth); String strBeginDay=request.getParameter(beginday); String strEndYear=request.getParameter(endyear); String strEndMonth=re
20、quest.getParameter(endmonth); String strEndDay=request.getParameter(endday); String strStartDate=strBeginYear+strBeginMonth+strBeginDay; String strEndDate=strEndYear+strEndMonth+strEndDay; String strSql1= call sp_Refurbish ; CallableStatement sqlStmt1 =con.prepareCall(strSql1); sqlStmt1.execute; sql
21、Stmt1.close; String strSql = call science_allStaffQuery(?,?); CallableStatement sqlStmt = con.prepareCall(strSql); sqlStmt.setString(1, strStartDate); sqlStmt.setString(2, strEndDate); sqlStmt.execute; sqlStmt.close; ResultSet rst=stmt.executeQuery(select distinct(staffNo),staff,CDate from scienceSt
22、affNo order by staffNo,CDate); while(rst.next) String strNo=rst.getString(1); String strStaff=rst.getString(2); String strDate=rst.getString(3); out.println(); out.println(+strNo+); out.println(+strStaff+); out.println(+strDate+); out.println( ); Statement stmt0=con.createStatement; ResultSet rst0=
23、stmt0.executeQuery(select firstCardRecord from scienceDeptQueryTemp1 where dkDate=+strDate+ and staffNo=+strNo+ ); while(rst0.next) out.println(); out.println(+rst0.getString(1)+); out.println(); stmt0.close; rst0.close; out.println( ); out.println( ); Statement stmt2=con.createStatement; ResultSet
24、rst2= stmt2.executeQuery(select secondCardRecord from scienceDeptQueryTemp2 where dkDate=+strDate+and staffNo=+strNo+ ); while(rst2.next) out.println(); out.println(+rst2.getString(1)+); out.println(); stmt2.close; rst2.close; out.println( ); out.println( ); Statement stmt3=con.createStatement; Resu
25、ltSet rst3= stmt3.executeQuery(select thirdCardRecord from scienceDeptQueryTemp3 where dkDate=+strDate+and staffNo=+strNo+ ); while(rst3.next) out.println(); out.println(+rst3.getString(1)+); out.println(); stmt3.close; rst3.close; out.println(); out.println(); out.println(); /关闭连接、释放资源 rst.close; s
26、tmt.close; con.close; % science_allStaffQuery代码如下： CREATE PROCEDURE science_allStaffQuery strbeginDate varchar(8), strendDate varchar(8) as declare beginDate datetime declare endDate datetime declare datebuffer datetime -存时间临时变量 declare staffno char(10) -员工号 declare staff varchar(80) -员工 declare dkd
27、ate datetime -查出的打卡时间 declare kqlName char(20) declare cur_emp cursor declare cur_dept cursor /请参考上面相关注释 DELETE FROM scienceDeptQueryTemp1 DELETE FROM scienceDeptQueryTemp2 DELETE FROM scienceDeptQueryTemp3 DELETE FROM dkDate DELETE FROM scienceStaffNo select beginDate = Convert(datetime,strbeginDat
28、e) select endDate = Convert(datetime,strendDate) select datebuffer = beginDate while datebuffer=endDate begin set cur_dept = Cursor global scroll dynamic for select StaffNo ,Staff ,CDate,KqlName from V_scienceQuery where datepart(yy,datebuffer)=datepart(yy,CDate) and datepart(mm,datebuffer)=datepart(mm,CDate) and datepart(dd,datebuffer)=datepart(dd,CDate) order by StaffNo open cur_dept Fetch NEXT From cur_dept into staffno,staff,dkdate,kqlName while Fetch_status=0 begin insert into scienceStaffNo values(staffno,staff,Convert(char(20),
