MySQL Forums
Forum List  »  InnoDB

My varible set two time
Posted by: Nader Agha Jafari
Date: July 23, 2008 11:37PM

Hi
I write a function and use 2 cursor in in. I need nested loop.

but in second loop I set a variable in if condition but outside the if condition the variable is stetted again.
I comment more part of my function but it dosent work yet.

I dont know why?
Here is my function


*************************************************************
DELIMITER $$

DROP FUNCTION IF EXISTS `senacis`.`operatorTimeResponse` $$
CREATE DEFINER=`root`@`192.168.1.100` FUNCTION `operatorTimeResponse`( op VARCHAR(50), t1 LONG, t2 LONG) RETURNS int(11)
BEGIN

BLOCK1: BEGIN
-- Dfine initialize varibale
DECLARE $reponseNum INT DEFAULT 0 ; -- Number of reponse operator reponse to contact
DECLARE $acceptNum INT DEFAULT 0 ; -- Number of accept operator accept that contact
DECLARE $minConvTime DOUBLE DEFAULT 0; -- minimum operator conversation Time
DECLARE $maxConvTime DOUBLE DEFAULT 0 ; -- maximum operator conversation time
DECLARE $aveConvTime DOUBLE DEFAULT 0 ; -- average operator conversation time
DECLARE $totalConvTime DOUBLE DEFAULT 0 ; -- total conversation time




-- Dfine initialize varibale
DECLARE $cseID BIGINT ;



-- cursor loop book-keeping
DECLARE no_more_rows BOOLEAN;

-- define a cursor to tarce the Alerts between t1 and t2
DECLARE cur_list CURSOR FOR
SELECT
CONTACT_SERVICE_EVENT_ID
FROM contact_detail_service_event

WHERE

(
-- accept = 4
action = 4
AND
agent_cd = op
AND
(action_time >= t1
AND
action_time < t2)
)
GROUP BY CONTACT_SERVICE_EVENT_ID ;

-- more cursor loop book-keeping
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

SELECT COUNT(action) INTO $reponseNum
FROM contact_detail_service_event
WHERE

(
-- alert = 1
action = 1
AND
agent_cd = op
AND
(action_time >= t1
AND
action_time < t2)
) ;


SELECT COUNT(action) INTO $acceptNum
FROM contact_detail_service_event
WHERE

(
-- accept = 4
action = 4
AND
agent_cd = op
AND
(action_time >= t1
AND
action_time < t2)
) ;



-- DELETE FROM _test1 ;

OPEN cur_list ;

loop_cur_list: LOOP

FETCH cur_list INTO
$cseID ;

IF no_more_rows THEN
CLOSE cur_list ;
LEAVE loop_cur_list;
END IF;

BLOCK2: BEGIN

-- cursor loop book-keeping
DECLARE no_more_rows2 BOOLEAN;
-- define varibales
DECLARE $csdeID BIGINT ;
DECLARE $agentCD VARCHAR(50) ;
DECLARE $action VARCHAR(50) ;
DECLARE $action_time LONG DEFAULT 0 ;

DECLARE $actionTimeConv LONG DEFAULT 0 ;
DECLARE $actionTimeAccept LONG DEFAULT 0 ;
DECLARE $convTime LONG DEFAULT 0 ;

-- define end of loop
DECLARE v_max_counter INT DEFAULT 3;
DECLARE v_counter INT DEFAULT 0;


DECLARE cur_list2 CURSOR FOR
SELECT
CONTACT_SERVICE_EVENT_ID,
agent_cd,
action,
action_time
FROM
contact_detail_service_event
WHERE
CONTACT_SERVICE_EVENT_ID = $cseID ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows2 = TRUE;
-- first check the no_more_rows2 handeler if they are less than 3(2 rows) then leave the loop
-- else it has 3 or more than it count the rows and if it's equal 3
-- agin leave the loop and we have access to end rows
OPEN cur_list2 ;

loop_cur_list2: LOOP

FETCH cur_list2 INTO
$csdeID,
$agentCD,
$action,
$action_time ;




IF no_more_rows2 THEN
CLOSE cur_list2 ;
LEAVE loop_cur_list2;
END IF;



SET v_counter := v_counter + 1 ;

IF v_counter = 2 THEN
IF $action = "4" THEN
SET $actionTimeAccept:= $action_time;
END IF;
END IF;

IF v_counter = 3 THEN
-- IF $action != 4 THEN
SET $actionTimeConv:= $action_time;
-- END IF;
END IF;
-- INSERT INTO _temp VALUES($csdeID,$agentCD,$action, v_counter) ;

INSERT INTO _temp VALUES($csdeID,$agentCD,$action, $actionTimeAccept) ;



IF v_counter = v_max_counter THEN

CLOSE cur_list2 ;
LEAVE loop_cur_list2; -- terminate repetition of region 'myLoop'
END IF ;


END LOOP loop_cur_list2 ;
-- IF ($act = 4 OR $act = 8 OR $act = 3 OR $act = 12 OR $act = 6 OR $act = 9 OR $act = 7) THEN
-- SET $actionTimeConv := $actionTime;
-- END IF;

-- SET $convTime := $actionTimeConv - $actAccept ;

-- INSERT INTO _temp VALUES($csdeID,$agentCD,$act, $actionTimeConv) ;

/* SET $convTime = 10 ;
INSERT INTO _temp VALUES($csdeID,$agentCD,$act, $convTime) ;

*/
-- accept = 4 and after it we dont have any operation
-- cutByOperatorC = 8
-- time out = 3
-- cutByCustomerC = 12
-- afterCompleteC = 6
-- systemCancelC = 9
-- cancelC = 7

/* IF $act = 4 THEN
SET unknownC = unknownC + 1 ;
ELSEIF act = 8 THEN
SET cutByOperatorC = cutByOperatorC + 1 ;
ELSEIF act = 12 THEN
SET cutByCustomerC = cutByCustomerC + 1 ;
ELSEIF act = 6 THEN
SET afterCompleteC = afterCompleteC + 1 ;
ELSEIF act = 9 THEN
SET systemCancelC = systemCancelC + 1 ;
ELSEIF act = 7 THEN
SET cancelC = cancelC + 1 ;
END IF ;
*/
END BLOCK2;

END LOOP loop_cur_list;
/*
DELETE FROM _operator_accept ;
INSERT INTO _operator_accept VALUES("unknown",unknownC) ;
INSERT INTO _operator_accept VALUES("cutByOperator",cutByOperatorC) ;
INSERT INTO _operator_accept VALUES("cutByCustomer",cutByCustomerC) ;
INSERT INTO _operator_accept VALUES("afterComplete",afterCompleteC) ;
INSERT INTO _operator_accept VALUES("systemCancel",systemCancelC) ;
INSERT INTO _operator_accept VALUES("cancel",cancelC) ;

*/

RETURN 1;

END BLOCK1;

END $$

DELIMITER ;

***************************************************************************

Options: ReplyQuote


Subject
Views
Written By
Posted
My varible set two time
3619
July 23, 2008 11:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.