MySQL Forums
Forum List  »  Stored Procedures

debug in Stored procedure
Posted by: Ankur Kaushik
Date: October 19, 2016 04:09PM

I want to debug the procedure and get line number with error found , How to use the exception Handler .

Below procedure

BEGIN
-- @Author : Vishwnath M Patil.
-- Date : 15/10/2016
-- Version :1.4
DECLARE status TEXT;
-- Variables declaration
DECLARE l_cancel_ticketno_r,l_rebook_tickeno_r BIGINT DEFAULT NULL;
DECLARE l_application_in VARCHAR(20);
DECLARE l_request_type_cancel VARCHAR(2) DEFAULT 'C';
DECLARE l_request_type_rebook VARCHAR(2) DEFAULT 'R';
DECLARE r_paxcount_c ,r_paxcount_r INT(2);
DECLARE v_finished INT(1) DEFAULT 0;
DECLARE v_seatno INT(2) DEFAULT 0;
DECLARE v_bus_serviceno INT(10);
DECLARE v_bus_departuretm DATETIME;
DECLARE v_request_type_r VARCHAR(2);

DECLARE _rollback BOOL DEFAULT 0;
DECLARE l_ticket_no_in,v_user_code,v_r_guid_in VARCHAR(10);

#DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
#DECLARE CONTINUE HANDLER FOR NOT FOUND v_finished=1;
SET @l_ticket_no_in=g_ticket_no_in;
SET @l_request_type_cancel=l_request_type_cancel;
SET @l_request_type_rebook=l_request_type_rebook;
SET @v_user_code=g_user_code_in;
SET @v_r_guid_in=g_r_guid_in;


-- Table name Declaration .
SET @v_temp_rebook_details_tbl='ORS_PassengerList_Cancel_Rebook';
SET @v_tickets_tbl='tickets';
SET @v_ticketdetails_tbl='ticket_details';

SET @v_update_tickets=CONCAT('UPDATE ',@v_tickets_tbl,' SET TICKET_STATUS = \'D\',REF_TICKET_NO =? WHERE TICKET_NO =?');
SET @v_update_ticketdetails=CONCAT('UPDATE ',@v_ticketdetails_tbl,' SET SEAT_STATUS = \'C\' WHERE TICKET_NO =?');
SET @v_update_seats='UPDATE seats SET TICKET_NO = null,PASSENGER_NO = null,TICKET_TYPE = null,GUID = null,TEMP_SEAT_STATUS = \'A\' WHERE ticket_no=? and BUS_SERVICE_NO=? and DEPARTURE_TM=? ';
SET @v_where_clause='AND SEAT_NO=?';

IF g_cancel_type_in=1 THEN
SET @status='FUll';

ELSE
SET @status='PARTIAL';

block_cancellation_voucher : BEGIN
-- Ticket Can cellation Vouche Number Generated .
SET @cancel_ticket_entry=CONCAT('INSERT INTO ',@v_tickets_tbl,'(TICKET_TYPE, REQUEST_TYPE, TICKET_STATUS,BUS_SERVICE_NO, DEPARTURE_TM,FROM_STOP_CD, TILL_STOP_CD, BOARDING_STOP_CD,DESTINATION_STOP_CD,NUM_PAX,RESERVATION_AMT,ASN_AMT,AC_SERVICE_TAX,TOLL_TAX_AMT,TOTAL_FARE_AMT,CANCELLATION_RATE,REF_TICKET_NO,PRINT_CNT,ISSUE_TM,SESSION_NO,USER_CD,GUID,APPLICATION)
SELECT DISTINCT TICKET_TYPE, REQUEST_TYPE,TICKET_STATUS,BUS_SERVICE_NO, DEPARTURE_TM, FROM_STOP_CD, TILL_STOP_CD, BOARDING_STOP_CD,DESTINATION_STOP_CD,Count(PASSENGER_NO) as PAXNUM ,0,SUM(ASN_AMT) as ASN_AMT,0,0,SUM(FLOOR(FARE_AMT)) AS REFUND_AMT, 0, TICKET_NO,1,now(), session_id, USERCD, GUID,APPLICATION FROM ',@v_temp_rebook_details_tbl,' WHERE USERCD=? AND TICKET_NO=? AND REQUEST_TYPE=?');
-- Execute the cancel_ticket_entry .
PREPARE cancel_stm FROM @cancel_ticket_entry;
EXECUTE cancel_stm USING @v_user_code,@l_ticket_no_in,@l_request_type_cancel;

SET @l_cancel_ticketno_r=LAST_INSERT_ID(); -- Cancellation Voucher Number

SET @cancel_ticket_details_entry=CONCAT('INSERT INTO ',@v_ticketdetails_tbl,'(TICKET_NO,PASSENGER_NO,PASSENGER_NM,PASSENGER_AGE,PASSENGER_SEX,PASSENGER_TYPE,CONCESSION_CD,CONCESSION_RATE,IS_HOME_STATE_ONLY,QUOTA_FLAG,HOME_STATE_AMT,STATE_AMT_1,STATE_AMT_2,STATE_AMT_3,STATE_AMT_4,STATE_AMT_5,ADDITIONAL_AMT,ASN_AMT,FARE_AMT,SEAT_NO,SEAT_STATUS)
SELECT @l_cancel_ticketno_r,PASSENGER_NO,PASSENGER_NM,PASSENGER_AGE,PASSENGER_SEX,PASSENGER_TYPE, CONCESSION_CD,CONCESSION_RATE,IS_HOME_STATE_ONLY,QUOTA_FLAG,HOME_STATE_AMT - ROUND(HOME_STATE_AMT*0/100),STATE_AMT_1 - ROUND(STATE_AMT_1*0/100),STATE_AMT_2,STATE_AMT_3,STATE_AMT_4,STATE_AMT_5,ADDITIONAL_AMT,ASN_AMT,HOME_STATE_AMT - ROUND(HOME_STATE_AMT*0/100) + STATE_AMT_1 - ROUND(STATE_AMT_1*0/100),SEAT_NO,SEAT_STATUS FROM ',@v_temp_rebook_details_tbl,' Where USERCD = ? AND TICKET_NO=? AND REQUEST_TYPE=? order by PASSENGER_NO ASC');
PREPARE cancel_ticketdetails_stm FROM @cancel_ticket_details_entry;
EXECUTE cancel_ticketdetails_stm USING @v_user_code,@l_ticket_no_in,@l_request_type_cancel;
END block_cancellation_voucher;

block_rebook : BEGIN
SET @rebook_ticket_entry=CONCAT('INSERT INTO ',@v_tickets_tbl,'(TICKET_TYPE, REQUEST_TYPE, TICKET_STATUS,BUS_SERVICE_NO, DEPARTURE_TM,FROM_STOP_CD, TILL_STOP_CD, BOARDING_STOP_CD,DESTINATION_STOP_CD,NUM_PAX,RESERVATION_AMT,ASN_AMT,AC_SERVICE_TAX,TOLL_TAX_AMT,TOTAL_FARE_AMT,CANCELLATION_RATE,REF_TICKET_NO,PRINT_CNT,ISSUE_TM,SESSION_NO,USER_CD,GUID,APPLICATION)
SELECT DISTINCT TICKET_TYPE, REQUEST_TYPE,TICKET_STATUS,BUS_SERVICE_NO, DEPARTURE_TM, FROM_STOP_CD, TILL_STOP_CD, BOARDING_STOP_CD,DESTINATION_STOP_CD,Count(PASSENGER_NO) as PAXNUM ,0,SUM(ASN_AMT) as ASN_AMT,SUM(AC_SERVICE_TAX) as AC_SERVICE_TAX,0,SUM(FLOOR(FARE_AMT)) AS REFUND_AMT, 0, TICKET_NO,1,now(), session_id, USERCD, GUID,APPLICATION FROM ',@v_temp_rebook_details_tbl,' WHERE USERCD=? AND TICKET_NO=? AND REQUEST_TYPE=?');

PREPARE rebook_tickets_stm FROM @rebook_ticket_entry;
EXECUTE rebook_tickets_stm USING @v_user_code,@l_ticket_no_in,@l_request_type_rebook;
SET @l_rebook_tickeno_r=LAST_INSERT_ID(); -- Rebook Cancellation Voucher Number


SET @rebook_ticket_details_entry=CONCAT('INSERT INTO ',@v_ticketdetails_tbl,'(TICKET_NO,PASSENGER_NO,PASSENGER_NM,PASSENGER_AGE,PASSENGER_SEX,PASSENGER_TYPE,CONCESSION_CD,CONCESSION_RATE,IS_HOME_STATE_ONLY,QUOTA_FLAG,HOME_STATE_AMT,STATE_AMT_1,STATE_AMT_2,STATE_AMT_3,STATE_AMT_4,STATE_AMT_5,ADDITIONAL_AMT,ASN_AMT,AC_SERVICE_TAX,FARE_AMT,SEAT_NO,SEAT_STATUS)
SELECT @l_cancel_ticketno_r,PASSENGER_NO,PASSENGER_NM,PASSENGER_AGE,PASSENGER_SEX,PASSENGER_TYPE, CONCESSION_CD,CONCESSION_RATE,IS_HOME_STATE_ONLY,QUOTA_FLAG,HOME_STATE_AMT - ROUND(HOME_STATE_AMT*0/100),STATE_AMT_1 - ROUND(STATE_AMT_1*0/100),STATE_AMT_2,STATE_AMT_3,STATE_AMT_4,STATE_AMT_5,ADDITIONAL_AMT,ASN_AMT,AC_SERVICE_TAX,HOME_STATE_AMT - ROUND(HOME_STATE_AMT*0/100) + STATE_AMT_1 - ROUND(STATE_AMT_1*0/100),SEAT_NO,SEAT_STATUS FROM ',@v_temp_rebook_details_tbl,' Where USERCD = ? AND TICKET_NO=? AND REQUEST_TYPE=? order by PASSENGER_NO ASC');
PREPARE rebook_ticketdetails_stm FROM @rebook_ticket_details_entry;
EXECUTE rebook_ticketdetails_stm USING @v_user_code,@l_ticket_no_in,@l_request_type_rebook;


END block_rebook;
-- Update tickets & ticket_details table .
PREPARE tickets_update_stm FROM @v_update_tickets;
EXECUTE tickets_update_stm USING @l_cancel_ticketno_r,@l_ticket_no_in; -- reference ticket & inputed ticket no
PREPARE ticket_details_update_stm FROM @v_update_ticketdetails;
EXECUTE ticket_details_update_stm USING @l_ticket_no_in;

-- CURSOR Declaration for seats table updatation .
block_cursor : BEGIN

DECLARE cur_cancel_seats_update CURSOR FOR
SELECT SEAT_NO,bus_service_no,DEPARTURE_TM,REQUEST_TYPE FROM ORS_PassengerList_Cancel_Rebook WHERE USERCD =@v_user_code AND TICKET_NO=@l_ticket_no_in;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

OPEN cur_cancel_seats_update;

get_row : LOOP

FETCH cur_cancel_seats_update INTO v_seatno,v_bus_serviceno,v_bus_departuretm,v_request_type_r;
IF v_request_type_r='C' THEN
UPDATE seats SET TICKET_NO = null,PASSENGER_NO = null,TICKET_TYPE = null,GUID = null,TEMP_SEAT_STATUS = 'A' WHERE ticket_no=@l_ticket_no_in and BUS_SERVICE_NO=v_bus_serviceno and DEPARTURE_TM=v_bus_departuretm AND SEAT_NO=v_seatno;
ELSE
UPDATE seats SET TICKET_NO = @l_rebook_tickeno_r WHERE ticket_no=@l_ticket_no_in and BUS_SERVICE_NO=v_bus_serviceno and DEPARTURE_TM=v_bus_departuretm AND SEAT_NO=v_seatno;
END IF;
LEAVE get_row;
END IF;
SELECT v_seatno,v_bus_serviceno,v_bus_departuretm;
END LOOP get_row;
CLOSE cur_cancel_seats_update;
END block_cursor;

END IF;

-- Response Parameter Defined .
SELECT @status,@l_cancel_ticketno_r as cancel_ticket,l_rebook_tickeno_r as rebook_ticket_no;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
debug in Stored procedure
4403
October 19, 2016 04:09PM


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.