안녕하세요 postgresql 시작한지 얼마 안됐습니다.
tpc-c 관련해서 프로시저를 하나 만들고 있는데 생각보다 작업이 어렵네요
CREATE OR REPLACE PROCEDURE Delivery
(
IN_mW_ID IN INTEGER,
IN_mO_CARRIER_ID IN INTEGER,
OUT_Result OUT VARCHAR(4000),
OUT_mSuccess OUT NATIVE_INTEGER
)
IS
sNativeError INTEGER;
DV_w_id INTEGER;
DV_o_carrier_id INTEGER;
DV_d_id INTEGER;
DV_c_id INTEGER;
DV_no_o_id INTEGER;
DV_o_total NUMERIC(15,2);
sBuffer VARCHAR(4000);
PROCEDURE SETERR
IS
BEGIN
sNativeError := SQLCODE;
END;
BEGIN
---------------------------------------------
-- INPUT ARGS, Initialize Variable
---------------------------------------------
DV_w_id := IN_mW_ID;
DV_o_carrier_id := IN_mO_CARRIER_ID;
<< RAMP_RETRY >>
sNativeError := 0;
sBuffer := '';
---------------------------------------------
-- GET OUTPUT
---------------------------------------------
FOR DV_d_id IN 1 .. 10
LOOP
BEGIN
SELECT no_o_id INTO DV_no_o_id
FROM new_order
WHERE no_w_id = DV_w_id AND
no_d_id = DV_d_id
ORDER BY no_o_id
FETCH 1 ;
EXCEPTION WHEN NO_DATA_FOUND THEN sBuffer := sBuffer || -1 || '|' || DV_d_id || '|';
CONTINUE;
WHEN OTHERS THEN SETERR;goto SQL_FINISH;
END;
BEGIN
DELETE FROM new_order
WHERE no_o_id = DV_no_o_id AND
no_d_id = DV_d_id AND
no_w_id = DV_w_id;
EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH;
END;
BEGIN
SELECT o_c_id INTO DV_c_id
FROM orders
WHERE o_id = DV_no_o_id AND
o_d_id = DV_d_id AND
o_w_id = DV_w_id;
EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH;
END;
BEGIN
UPDATE orders
SET o_carrier_id = DV_o_carrier_id
WHERE o_id = DV_no_o_id AND
o_d_id = DV_d_id AND
o_w_id = DV_w_id ;
EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH;
END;
BEGIN
UPDATE order_line
SET ol_delivery_d = sysdate
WHERE ol_o_id = DV_no_o_id AND
ol_d_id = DV_d_id AND
ol_w_id = DV_w_id;
EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH;
END;
BEGIN
SELECT SUM(ol_amount) INTO DV_o_total
FROM order_line
WHERE ol_o_id = DV_no_o_id AND
ol_d_id = DV_d_id AND
ol_w_id = DV_w_id;
EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH;
END;
BEGIN
UPDATE customer
SET c_balance = c_balance + DV_o_total,
c_delivery_cnt = c_delivery_cnt + 1
WHERE c_id = DV_c_id AND
c_d_id = DV_d_id AND
c_w_id = DV_w_id;
EXCEPTION WHEN OTHERS THEN SETERR;goto SQL_FINISH;
END;
-------------------------------------------
-- Make Output-String
-------------------------------------------
sBuffer := sBuffer || DV_no_o_id || '|' || DV_d_id || '|';
END LOOP;
-------------------------------------------
-- Output Param
-------------------------------------------
OUT_Result := sBuffer;
OUT_mSuccess := 1;
COMMIT;
goto END_STEP;
<< SQL_FINISH >>
ROLLBACK;
IF ( (sNativeError = -14007) OR (sNativeError = -14032) )
THEN
goto RAMP_RETRY;
END IF;
OUT_mSuccess := 0;
<< END_STEP >>
NULL;
END;
/
오라클 프로시저문인데 postgresql로 변경하고 싶은데 잘 안되네요 혹시 잘 아시는분 있으면
팁같은거 부탁드리겠습니다.
|
