Skip to Content
0
Jun 30 at 10:26 AM

HANA CLOUD stored procedures syntax error

121 Views Last edit Jun 30 at 10:27 AM 2 rev

Hi. I'm trying to convert stored procedure written in mariadb to hana db.

The problem is that hana db's syntax is so much different and I'm suffering from lack of proper information...

I'm facing new errors everytime I edit the query statement. It's a bit embarrassing, but can someone help me out with what's the syntax problem of my procedure?

THANKS.

image.png

 
create
procedure PR_CARD_MASTER(OUT rtn_code CHAR, OUT rtn_msg VARCHAR(4000))
LANGUAGE SQLSCRIPT

AS
v_function_name VARCHAR(100) DEFAULT 'PR_CARD_MASTER';
done TINYINT DEFAULT NULL;

v_owner_reg_no VARCHAR(10);
v_card_num VARCHAR(16);
v_bank_code VARCHAR(4);
v_bank_name VARCHAR(20);
v_card_kind CHAR(1);
v_user_name VARCHAR(50);
v_avail_term VARCHAR(6);
v_use_status VARCHAR(20);
v_first_date TIMESTAMP;
v_cancel_date TIMESTAMP;
v_sett_date VARCHAR(2);
v_sett_bankcode VARCHAR(4);
v_sett_acco VARCHAR(20);
v_use_onelmt DECIMAL(18,4);
v_one_limit DECIMAL(18,4);
v_fore_lmt DECIMAL(18,4);
v_fore_uselmt DECIMAL(18,4);
v_stand_date VARCHAR(8);
v_stand_time VARCHAR(6);
v_befcardno VARCHAR(16);
v_corporatename VARCHAR(50);
v_card_gubun CHAR(1);
v_managedivname VARCHAR(50);
v_manageno VARCHAR(12);
v_ownersocialno VARCHAR(13);
v_cooperatecard VARCHAR(50);
v_indate VARCHAR(8);
v_intime VARCHAR(6);
v_type CHAR(1);
v_detail VARCHAR(20);
v_user_id VARCHAR(20);
v_issue_stat CHAR(1);
v_company_id VARCHAR(4);

BEGIN

FOR ITEM_A IN(
SELECT
owner_reg_no
, card_num
, bank_code
, bank_name
, card_kind
, user_name
, avail_term
, use_status
, first_date
, cancel_date
, sett_date
, sett_bankcode
, sett_acco
, use_onelmt
, one_limit
, fore_lmt
, fore_uselmt
, stand_date
, stand_time
, befcardno
FROM card_info


)

IF ITEM_A.use_status = 'pass' THEN
v_use_status := '1';
ELSEIF ITEM_A.use_status = 'failed' THEN
v_use_status := '3';
ELSE
v_use_status := '1';
END IF;


IF ITEM_A.first_date = '00000000' THEN
v_first_date := '';
ELSEIF trim(ITEM_A.first_date) IS NULL THEN
v_first_date := '';
ELSE
v_first_date := trim(to_date(ITEM_A.first_date));
END IF;


IF ITEM_A.cancel_date = '00000000' THEN
v_cancel_date := '';
ELSEIF trim(ITEM_A.cancel_date) IS NULL THEN
v_cancel_date := '';
ELSE
v_cancel_date := trim(to_date(ITEM_A.cancel_date));
END IF;

/* EACC_CARD_MASTER MERGE */
MERGE INTO EACC_CARD_MASTER ECM
USING DUMMY
ON (ECM.card_num = ITEM_A.card_num )

WHEN MATCHED THEN
UPDATE SET
ECM.card_num = ITEM_A.card_num,
ECM.owner_reg_no = ITEM_A.owner_reg_no
WHEN NOT MATCHED THEN
INSERT (
ECM.owner_reg_no,
ECM.card_num,
ECM.bank_code,
ECM.bank_name,
ECM.card_kind,
ECM.card_name,
ECM.avail_term,
ECM.use_status,
ECM.first_date,
ECM.cancel_date,
ECM.sett_date,
ECM.sett_bankcode,
ECM.sett_acco,
ECM.use_onelmt,
ECM.one_limit,
ECM.fore_lmt,
ECM.fore_uselmt,
ECM.stand_date,
ECM.stand_time,
ECM.befcardno,
ECM.indate,
ECM.intime,
ECM.company_id

) VALUES(
ITEM_A.owner_reg_no,
ITEM_A.card_num,
ITEM_A.bank_code,
ITEM_A.bank_name,
ITEM_A.card_kind,
ITEM_A.user_name,
ITEM_A.avail_term,
:v_use_status,
:v_first_date,
:v_cancel_date,
ITEM_A.sett_date,
ITEM_A.sett_bankcode,
ITEM_A.sett_acco,
ITEM_A.use_onelmt,
ITEM_A.one_limit,
ITEM_A.fore_lmt,
ITEM_A.fore_uselmt,
ITEM_A.stand_date,
ITEM_A.stand_time,
ITEM_A.befcardno,
to_varchar(CURRENT_TIMESTAMP, 'YYYYMMDD'),
to_varchar(CURRENT_TIMESTAMP, 'HHMMSS'),
'1000'

);



IF ITEM_A.use_status = 'failed' THEN
UPDATE EACC_CARD_MASTER SET
ISSUE_STAT = '2'
WHERE owner_reg_no = ITEM_A.owner_reg_no and card_num = ITEM_A.card_num;
END IF;

END FOR;



UPDATE card_info
SET send_yn = 'Y'
WHERE send_yn IS NULL;
EXEC 'COMMIT';

rtn_code := 'S';
rtn_msg := 'successfully processed';

END;

Attachments

image.png (233.6 kB)