create or replace function p_create_plan(avc_appid in varchar2,
an_rec in number,
an_min in number) return number is
ld_effetime date;
lc_item char(4);
lc_type char(4);
lvc_terminal_id varchar(20);
ln_count number(8);
lc_serverid char(4);
ln_server_count number(4);
cursor cur_terminal is
select id
into lvc_terminal_id
from reg_terminal
where status = 'Y'
and itemcode = lc_item
and typecode = lc_type;
cursor cur_app_server is
select serverid
into lc_serverid
from reg_app_server
where status = 'Y';
begin
begin
select itemcode, typecode, effetime
into lc_item, lc_type, ld_effetime
from reg_app_info
where status = '1'
and id = avc_appid;
exception
when no_data_found then
return - 1;
end; select count(*)
into ln_server_count
from reg_app_server
where status = 'Y';
if ln_server_count = 0 then
return - 2;
end if; open cur_app_server;
ln_count := 0;
open cur_terminal;
loop
fetch cur_terminal
into lvc_terminal_id;
exit when cur_terminal%notfound;
ln_count := ln_count + 1;
if mod(ln_count, an_rec) = 0 then
ld_effetime := ld_effetime + (1 / 24 / 60 * an_min);
end if;
fetch cur_app_server
into lc_serverid;
if cur_app_server%notfound then
close cur_app_server;
open cur_app_server;
fetch cur_app_server
into lc_serverid;
end if;
insert into reg_app_plan
(id, appid, terminalid, effetime, serverid, status)
values
(seq_plan_id.nextval,
avc_appid,
lvc_terminal_id,
ld_effetime,
lc_serverid,
'1');
end loop;
close cur_terminal;
close cur_app_server;
update reg_app_info set status='2' where id=avc_appid;
return 0;
exception
when others then
return - 99;
end;
an_rec in number,
an_min in number) return number is
ld_effetime date;
lc_item char(4);
lc_type char(4);
lvc_terminal_id varchar(20);
ln_count number(8);
lc_serverid char(4);
ln_server_count number(4);
cursor cur_terminal is
select id
into lvc_terminal_id
from reg_terminal
where status = 'Y'
and itemcode = lc_item
and typecode = lc_type;
cursor cur_app_server is
select serverid
into lc_serverid
from reg_app_server
where status = 'Y';
begin
begin
select itemcode, typecode, effetime
into lc_item, lc_type, ld_effetime
from reg_app_info
where status = '1'
and id = avc_appid;
exception
when no_data_found then
return - 1;
end; select count(*)
into ln_server_count
from reg_app_server
where status = 'Y';
if ln_server_count = 0 then
return - 2;
end if; open cur_app_server;
ln_count := 0;
open cur_terminal;
loop
fetch cur_terminal
into lvc_terminal_id;
exit when cur_terminal%notfound;
ln_count := ln_count + 1;
if mod(ln_count, an_rec) = 0 then
ld_effetime := ld_effetime + (1 / 24 / 60 * an_min);
end if;
fetch cur_app_server
into lc_serverid;
if cur_app_server%notfound then
close cur_app_server;
open cur_app_server;
fetch cur_app_server
into lc_serverid;
end if;
insert into reg_app_plan
(id, appid, terminalid, effetime, serverid, status)
values
(seq_plan_id.nextval,
avc_appid,
lvc_terminal_id,
ld_effetime,
lc_serverid,
'1');
end loop;
close cur_terminal;
close cur_app_server;
update reg_app_info set status='2' where id=avc_appid;
return 0;
exception
when others then
return - 99;
end;
an_rec int,
an_min int) RETURNS int(11)
BEGIN
DECLARE ld_effetime date;
DECLARE lc_item varchar(4);
DECLARE lc_type varchar(4);
DECLARE lvc_terminal_id varchar(20);
DECLARE ln_count int;
DECLARE lc_serverid varchar(4);
DECLARE ln_server_count int;
DECLARE cur_terminal cursor for
select id
from reg_terminal
where status = 'Y'
and itemcode = lc_item
and typecode = lc_type;
declare continue handler for not found set done1 = 1;
DECLARE cur_app_server cursor for
select serverid
from reg_app_server
where status = 'Y';
declare continue handler for not found set done2 = 1;
select itemcode, typecode, effetime
into lc_item, lc_type, ld_effetime
from reg_app_info
where status = '1'
and id = avc_appid;
select count(*)
into ln_server_count
from reg_app_server
where status = 'Y';
if ln_server_count = 0 then
return - 2;
end if;
open cur_app_server;
set ln_count = 0;
open cur_terminal;
repeat
fetch cur_terminal
into lvc_terminal_id;
set ln_count = ln_count + 1;
if mod(ln_count, an_rec) = 0 then
set ld_effetime = ld_effetime + (1 / 24 / 60 * an_min);
end if;
fetch cur_app_server
into lc_serverid;
if done2 then
close cur_app_server;
open cur_app_server;
fetch cur_app_server
into lc_serverid;
end if;
insert into reg_app_plan
(appid, terminalid, effetime, serverid, status)
values
(
avc_appid,
lvc_terminal_id,
ld_effetime,
lc_serverid,
'1');
until done1 end repeat;
close cur_terminal;
close cur_app_server;
update reg_app_info set status='2' where id=avc_appid;
return 0;
END$$DELIMITER ;错误码: 1193
Unknown system variable 'done1'
DECLARE done2 int default 0;
错误码: 1338
Cursor declaration after handler declaration
DELIMITER $$
DROP FUNCTION IF EXISTS `tms`.`p_create_plan`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `p_create_plan`(avc_appid VARCHAR(20),
an_rec INT,
an_min INT) RETURNS INT(11)
BEGIN
DECLARE ld_effetime DATE;
DECLARE lc_item VARCHAR(4);
DECLARE lc_type VARCHAR(4);
DECLARE lvc_terminal_id VARCHAR(20);
DECLARE ln_count INT;
DECLARE lc_serverid VARCHAR(4);
DECLARE ln_server_count INT;
DECLARE done1 INT DEFAULT 0;
DECLARE done2 INT DEFAULT 0;
DECLARE cur_terminal CURSOR FOR
SELECT id
FROM reg_terminal
WHERE STATUS = 'Y'
AND itemcode = lc_item
AND typecode = lc_type;
DECLARE cur_app_server CURSOR FOR
SELECT serverid
FROM reg_app_server
WHERE STATUS = 'Y';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
SELECT itemcode, typecode, effetime
INTO lc_item, lc_type, ld_effetime
FROM reg_app_info
WHERE STATUS = '1'
AND id = avc_appid;
SELECT COUNT(*)
INTO ln_server_count
FROM reg_app_server
WHERE STATUS = 'Y';
IF ln_server_count = 0 THEN
RETURN - 2;
END IF;
OPEN cur_app_server;
SET ln_count = 0;
OPEN cur_terminal;
REPEAT
FETCH cur_terminal
INTO lvc_terminal_id;
SET ln_count = ln_count + 1;
IF MOD(ln_count, an_rec) = 0 THEN
SET ld_effetime = ld_effetime + (1 / 24 / 60 * an_min);
END IF;
FETCH cur_app_server
INTO lc_serverid;
IF done1 THEN
CLOSE cur_app_server;
OPEN cur_app_server;
FETCH cur_app_server
INTO lc_serverid;
set done1=0;
END IF;
INSERT INTO reg_app_plan
(appid, terminalid, effetime, serverid, STATUS)
VALUES
(
avc_appid,
lvc_terminal_id,
ld_effetime,
lc_serverid,
'1');
UNTIL done1 END REPEAT;
CLOSE cur_terminal;
CLOSE cur_app_server;
UPDATE reg_app_info SET STATUS='2' WHERE id=avc_appid;
RETURN 0;
END$$
DELIMITER ;
INTO lc_serverid;
IF done1 THEN
CLOSE cur_app_server;
OPEN cur_app_server;
FETCH cur_app_server
INTO lc_serverid;
set done1=0;
END IF;这样会死循环吧,怎么处理两个cursor共用一个 CONTINUE HANDLER 呢?