mysql 存储过程loop_mysql的带游标的存储过程

BEGIN

BEGINCREATE TEMPORARY TABLE IF NOT EXISTS

func_orgunits  -- 不存在则创建临时�?

(  `id` int(11) NOT NULL COMMENT

'主键',  `e_id` int(11) NOT NULL COMMENT

'人员ID',  `d_id` int(11) NOT NULL COMMENT

'组织ID',  `type` int(5) DEFAULT NULL COMMENT

'类别(班主任，学生，校长等)(类型�?99.毕业,1.在读班级状�?',

`def` int(1) DEFAULT '0' COMMENT

'是否主所属部�?0:标识需要升�?毕业的学�?1:不需升级，毕业的学生)',

`user_id` int(11) DEFAULT NULL,  `create_date`

datetime DEFAULT NULL COMMENT '创建时间',

`update_date` datetime DEFAULT NULL COMMENT '更新时间');TRUNCATE TABLE

func_orgunits;  --

使用前先清空临时表�?#向临时表插入数据BEGIN

declare _did INT; --

学校id    declare

_eid INT; -- 人员eid

declare _highdid INT

;    --

升入班级id

DECLARE _smsvalue INT; --

学期    DECLARE

_tempallorder INT; --

临时allorder

DECLARE _olddid INT; --

原班�?id

DECLARE _newdid INT;--

升入班级id

DECLARE _tempdid INT; -- 临时did

DECLARE _alldid int; -- 过滤alldid  DECLARE _listdid

int; -- 下一个  DECLARE done INT DEFAULT 0;

DECLARE edone INT DEFAULT 0;

DECLARE threedone INT DEFAULT 0;

BEGIN

#定义保单数据游标

DECLARE rs_cursor CURSOR FOR SELECT d_id from

pt_configure where d_id>0 AND

`NAME`='auto_adjust_class' AND

`VALUE`=date_format(now(),'%m-%d');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =

1;#结束标识

OPEN

rs_cursor;

policySeqLoop:LOOP#循环保单数据游标

FETCH rs_cursor INTO

_did;

IF done = 1

THEN

LEAVE

policySeqLoop;#跳出循环

ELSE

SELECT b.ALLORDER INTO _tempallorder FROM

org_units b WHERE b.id=_did LIMIT

0,1;

BEGIN

DECLARE endors_cusor CURSOR FOR SELECT a.id FROM org_units a WHERE

a.ALLORDER LIKE concat('%',_tempallorder,'%') AND a.level=3 AND

a.high_level IS NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone

= 1;#结束标识

OPEN

endors_cusor;

endorsSeqLoop:LOOP#循环批单数据

FETCH endors_cusor INTO

_olddid;

IF edone = 1

THEN

LEAVE

endorsSeqLoop;

ELSE

INSERT INTO

func_orgunits(id,e_id,d_id,type,def,user_id,create_date,update_date)

SELECT

p.id,p.e_id,p.d_id,p.type,p.def,p.user_id,p.create_date,p.update_date

FROM org_unit_emps p;

UPDATE org_emp SET

`status`=99,out_time=NOW(),update_time=now() WHERE id

in

(select e_id from org_unit_emps where def=0 and

d_id=_olddid) AND `status`=1;

INSERT INTO

org_emp_move(semester,e_id,d_id,end_time,reason,dispositon,note,type,user_id,create_time)

select

_smsvalue,id,d_id,Now(),'毕业','','',99,0,NOW() from org_emp where id

in

(select e_id from org_unit_emps where def=0 and

d_id=_olddid);

DELETE FROM org_unit_emps_his WHERE e_id

in

(select e_id from org_unit_emps where def=0 and

d_id=_olddid);

INSERT INTO

org_unit_emps_his(e_id,d_id,class_name,grade_name,school_name,title,create_date,update_date)

SELECT

e_id,d_id,org_name,grade_name,SCHOOL_NAME,41,NOW(),NOW() FROM

v_class_emps

WHERE e_id in(select e_id from org_unit_emps

where def=0 and d_id=_olddid);

DELETE FROM org_unit_emps WHERE e_id

in

(select e_id from func_orgunits where def=0 and

d_id=_olddid);

BEGIN

-- DECLARE three_cusor CURSOR FOR

SELECT

a.id,a.high_level FROM org_units a WHERE

a.high_level=_olddid;

-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET threedone = 1;#结束标识

-- OPEN

three_cusor;

-- threeSeqLoop:LOOP#循环需升级信息

-- FETCH three_cusor  INTO _tempdid,_newdid;

--

����ѭ��

-- IF threedone = 1

THEN

--  LEAVE

threeSeqLoop;

--

ELSE

SELECT a.id,a.HIGH_LEVEL INTO _tempdid,_newdid FROM org_units a

WHERE a.high_level=_olddid LIMIT 0,1;

UPDATE org_emp SET d_id=_newdid WHERE id IN (SELECT e_id FROM

org_unit_emps WHERE d_id=_tempdid AND

def=0);

INSERT INTO

org_emp_move(semester,e_id,d_id,start_time,reason,dispositon,note,type,user_id,create_time)

select _smsvalue,id,_newdid,Now(),'升级',_newdid,'',15,0,NOW() from

org_emp where id in

(select e_id from org_unit_emps where def=0 and

d_id=_tempdid);

INSERT INTO

org_unit_emps(e_id,d_id,type,def,user_id,create_date,update_date)

SELECT id,_newdid,20,0,0,NOW(),NOW() FROM func_orgunits WHERE def=0

and d_id=_tempdid;

SELECT a.id,a.HIGH_LEVEL INTO _tempdid,_alldid FROM org_units a

WHERE a.high_level=_newdid LIMIT

0,1;

UPDATE org_emp SET d_id=_alldid WHERE id IN (SELECT e_id FROM

org_unit_emps WHERE d_id=_tempdid AND

def=0);

INSERT INTO

org_emp_move(semester,e_id,d_id,start_time,reason,dispositon,note,type,user_id,create_time)

select _smsvalue,id,_alldid,Now(),'升级',_alldid,'',15,0,NOW() from

org_emp where id in

(select e_id from org_unit_emps where def=0 and

d_id=_tempdid);

INSERT INTO

org_unit_emps(e_id,d_id,type,def,user_id,create_date,update_date)

SELECT id,_alldid,20,0,0,NOW(),NOW() FROM func_orgunits WHERE def=0

and

d_id=_tempdid;

SELECT a.id,a.HIGH_LEVEL INTO _tempdid,_listdid FROM org_units a

WHERE a.high_level=_alldid LIMIT

0,1;

UPDATE org_emp SET d_id=_listdid WHERE id IN (SELECT e_id FROM

org_unit_emps WHERE d_id=_tempdid AND

def=0);

INSERT INTO

org_emp_move(semester,e_id,d_id,start_time,reason,dispositon,note,type,user_id,create_time)

select _smsvalue,id,_listdid,Now(),'升级',_listdid,'',15,0,NOW() from

org_emp where id in

(select e_id from org_unit_emps where def=0 and

d_id=_tempdid);

INSERT INTO

org_unit_emps(e_id,d_id,type,def,user_id,create_date,update_date)

SELECT id,_listdid,20,0,0,NOW(),NOW() FROM func_orgunits WHERE

def=0 and

d_id=_tempdid;

END;

END

IF;

END

LOOP;

CLOSE

endors_cusor;

END;

END IF;

END

LOOP;

CLOSE rs_cursor;

COMMIT;

END;

END;

END;    END