oracle游标 包总结,oracle在存储过程中镶套使用游标总结

create or replace

PROCEDURE sxb_lx

AS

weekcount NUMBER;-------声明变量周数

montday   NUMBER;-------声明变量每月第一天

yearday DATE;-----------声明变量每年第一天

lastday DATE;-----------声明变量开始日期

endday DATE;------------声明变量结束日期

currentyear NUMBER;-----声明变量当前年

currentweek NUMBER;-----声明变量当前周

tempyixun   NUMBER;-----声明变量已巡

BEGIN

DECLARE

CURSOR time_cur IS SELECT DISTINCT TO_CHAR(patraldate,'yyyy')                              AS currentyear,

TO_CHAR(patraldate,'iw')                                AS currentweek,

TRUNC(patraldate,'iw')+3                                AS lastday,

TRUNC(patraldate,'iw')+9                                AS endday,

TRUNC(add_months(last_day(patraldate),-1)+1)            AS monthday,

TRUNC(patraldate,'yyyy')                                AS yearday

FROM patrolpoletable

WHERE updatedate>=sysdate-6;--------创建游标1获取计算巡视结果所需的时间参数(开始日期、结束日期、当前年、当前周、每月第一天、每年第一天)

CURSOR stus_cur IS SELECT a.department,

a.entercode

FROM usertable a ,departmenttable b

WHERE a.department=b.department AND b.workstatus  =1

AND b.isshudi     =0;----创建游标2获取计算巡视结果所需的人员编号(entercode)

var_time_cur time_cur%rowtype;---------游标1赋值

cur_stu stus_cur%rowtype;--------------游标2赋值

BEGIN-------------------------------------开始

OPEN time_cur;--------------------------开始游标1

LOOP------------------------------------开始循环

FETCH time_cur INTO var_time_cur;-----获得游标1的值

EXIT WHEN time_cur%notfound;----------游标1的出口

OPEN stus_cur ;-----------------------打开游标2

LOOP----------------------------------开始循环

FETCH stus_cur INTO cur_stu;--------获得游标2的值

EXIT WHEN stus_cur%notfound;--------游标2的出口

------------------------计算本周已巡------------------

SELECT COUNT(*) INTO tempyixun

FROM( SELECT DISTINCT b.circuitryname,

b.poleid,

TO_CHAR(b.patraldate,'yyyy-mm-dd')

FROM PATROLPOLETABLE b

WHERE b.circuitryname NOT LIKE '%备份%' AND b.patraldate > var_time_cur.lastday

AND b.patraldate < var_time_cur.endday

AND b.people IN (SELECT DISTINCT PEOPLE

FROM checkroutetaskinfotable

WHERE taskid IN(SELECT DISTINCT taskid

FROM V_CHECK_INFO

WHERE people=cur_stu.entercode)

UNION

SELECT cur_stu.entercode FROM dual

)

);

------------------------更新数据库本周已巡

UPDATE ManPatrolweektable b SET b.yixun = tempyixun

WHERE b.patrolpeople= cur_stu.entercode AND b.patrolyear = var_time_cur.currentyear

AND b.patrolweek = var_time_cur.currentweek;

------------------------计算本月已巡：本月第一天到当前节点的巡视结果

SELECT COUNT(*) INTO tempyixun

FROM( SELECT DISTINCT b.circuitryname,

b.poleid,

TO_CHAR(b.patraldate,'yyyy-mm-dd')

FROM PATROLPOLETABLE b

WHERE b.circuitryname NOT LIKE '%备份%' AND b.patraldate > var_time_cur.monthday

AND b.patraldate < var_time_cur.endday

AND b.people IN(SELECT DISTINCT PEOPLE

FROM checkroutetaskinfotable

WHERE taskid IN(SELECT DISTINCT taskid

FROM V_CHECK_INFO

WHERE people=cur_stu.entercode)

UNION

SELECT cur_stu.entercode FROM dual

)

);

-------------------------更新数据库本月已巡

UPDATE ManPatrolweektable b SET b.monthyixun    = tempyixun

WHERE b.patrolpeople= cur_stu.entercode AND b.patrolyear    =var_time_cur.currentyear

AND b.patrolweek    =var_time_cur.currentweek;

DBMS_OUTPUT.put_line(tempyixun);

------------------------计算本年已巡：本年第一天到当前节点的巡视结果

SELECT COUNT(*) INTO tempyixun

FROM( SELECT DISTINCT b.circuitryname,

b.poleid,

TO_CHAR(b.patraldate,'yyyy-mm-dd')

FROM PATROLPOLETABLE b

WHERE b.circuitryname NOT LIKE '%备份%' AND b.patraldate > var_time_cur.yearday

AND b.patraldate < var_time_cur.endday

AND b.people IN(SELECT DISTINCT PEOPLE

FROM checkroutetaskinfotable

WHERE taskid IN (SELECT DISTINCT taskid

FROM V_CHECK_INFO

WHERE people=cur_stu.entercode)

UNION

SELECT cur_stu.entercode FROM dual

)

);

------------------------更新数据库本年已巡

UPDATE ManPatrolweektable b SET b.yearyixun = tempyixun

WHERE b.patrolpeople= cur_stu.entercode AND b.patrolyear = var_time_cur.currentyear

AND b.patrolweek    =var_time_cur.currentweek;

DBMS_OUTPUT.put_line(tempyixun);

END LOOP;

CLOSE stus_cur;

END LOOP;

CLOSE time_cur;

END;

---------------更新巡视日期

DECLARE

CURSOR xunshi_cur IS SELECT *

FROM (SELECT c.lastpatraltime,

a.patrolpeople,

a.circuitry,

a.poleid

FROM ManUnPatrolweektable a, WEEKREPORT b ,V_PATRAL_POLE c

WHERE patrolyear = currentyear AND patrolweek = currentweek

AND patrolyear = b.yearnumber

AND b.weeknumber = patrolweek

AND a.circuitry  = c.circuitryname

AND a.poleid = c.poleid

AND c.people = a.patrolpeople

AND c.lastpatraltime > b.startdate

AND c.lastpatraltime < b.enddate

);

--定义rowtype

cur_xunshi xunshi_cur%rowtype;

/*开始执行*/

BEGIN

--开启游标

OPEN xunshi_cur;

--loop循环

LOOP

--循环条件

EXIT WHEN xunshi_cur%notfound;

--游标值赋值到rowtype

FETCH xunshi_cur INTO cur_xunshi;

--更新数据库本月已巡

UPDATE ManUnPatrolweektable b SET b.patroldate =cur_xunshi.lastpatraltime

WHERE b.patrolpeople= cur_xunshi.patrolpeople AND b.patrolyear = currentyear

AND b.patrolweek = currentweek

AND b.circuitry  = cur_xunshi.circuitry

AND b.poleid     = cur_xunshi.poleid;

--结束循环

END LOOP;

--关闭游标

CLOSE xunshi_cur;

END;

DBMS_OUTPUT.put_line('end ');

END sxb_lx;