select for update with [NO]WAIT

    SELECT

        CLIENT_CD

    FROM

        CHINA_CLIENT_POSITION

    WHERE

        CLIENT_CD = L_CLIENT_CD

    AND DSCR_CD = L_DSCR_CD

    FOR UPDATE

        NOWAIT

    ;

CREATE OR REPLACE PROCEDURE SP_CHN_EXEC_LOCK(

    I_ORD_NO        IN  VARCHAR

    , O_RESULT      OUT INTEGER

    , O_RESULT_MSG  OUT VARCHAR

) IS

    L_CLIENT_CD BV_USER_PROFILE.CLIENT_CD%TYPE;

    L_USER_ID   BV_USER_PROFILE.USER_ID%TYPE;

    L_DSCR_CD   CHINA_ORDER.DSCR_CD%TYPE;

    CURSOR CHINA_ORDER_LOCK

    IS

    SELECT

        ORD_NO

    FROM

        CHINA_ORDER

    WHERE

        ORD_NO = TO_NUMBER(I_ORD_NO)

    FOR UPDATE

        NOWAIT

    ;

    CURSOR CLNT_CASH_BALANCE_LOCK

    IS

    SELECT

        CLIENT_CD

    FROM

        CLNT_CASH_BALANCE

    WHERE

        CLIENT_CD = L_CLIENT_CD

    FOR UPDATE

        NOWAIT

    ;

    CURSOR CHINA_CLIENT_POSITION_LOCK

    IS

    SELECT

        CLIENT_CD

    FROM

        CHINA_CLIENT_POSITION

    WHERE

        CLIENT_CD = L_CLIENT_CD

    AND DSCR_CD = L_DSCR_CD

    FOR UPDATE

        NOWAIT

    ;

    CURSOR CHINA_EXECUTION_LOCK

    IS

    SELECT

        ORD_NO

    FROM

        CHINA_EXECUTION

    WHERE

        CLIENT_CD = L_CLIENT_CD

    AND DSCR_CD = L_DSCR_CD

    FOR UPDATE

        NOWAIT

    ;

    CURSOR CHINA_UNDELIVERED_TRADE_LOCK

    IS

    SELECT

        EXEC_NO

    FROM

        CHINA_UNDELIVERED_TRADE

    WHERE

        ORD_NO = TO_NUMBER(I_ORD_NO)

    FOR UPDATE

        NOWAIT

    ;

    CURSOR STOCK_CAPITAL_GAIN_LOCK

    IS

    SELECT

        CLIENT_CD

    FROM

        STOCK_CAPITAL_GAIN

    WHERE

        CLIENT_CD = L_CLIENT_CD

    FOR UPDATE

        NOWAIT

    ;

    CURSOR CLIENT_COMMISSION_LOCK

    IS

    SELECT

        CLIENT_CD

    FROM

        CLIENT_COMMISSION

    WHERE

        CLIENT_CD = L_CLIENT_CD

    FOR UPDATE

        NOWAIT

    ;

BEGIN

    O_RESULT := 1;

    O_RESULT_MSG := 'CHINA_ORDER_SELECT';

    SELECT

        CLIENT_CD

        , DSCR_CD

    INTO

        L_CLIENT_CD

        , L_DSCR_CD

    FROM

        CHINA_ORDER

    WHERE

        ORD_NO = TO_NUMBER(I_ORD_NO)

    AND ORD_SUB_NO = 1

    ;

    IF ( L_CLIENT_CD IS NULL ) THEN

        RETURN;

    END IF;

    O_RESULT := 2;

    O_RESULT_MSG := 'BV_USER_PROFILE_LOCK';

    SELECT

        USER_ID

    INTO

        L_USER_ID

    FROM

        BV_USER_PROFILE

    WHERE

        CLIENT_CD = L_CLIENT_CD

    FOR UPDATE

    ;

    IF ( L_USER_ID IS NULL ) THEN

        RETURN;

    END IF;

    O_RESULT := 3;

    O_RESULT_MSG := 'BV_USER_PASSWD_LOCK';

    SELECT

        USER_ID

    INTO

        L_USER_ID

    FROM

        BV_USER_PASSWD

    WHERE

        USER_ID = L_USER_ID

    FOR UPDATE

        NOWAIT

    ;

    O_RESULT := 4;

    O_RESULT_MSG := 'CHINA_ORDER_LOCK';

    OPEN CHINA_ORDER_LOCK;

    CLOSE CHINA_ORDER_LOCK;

    O_RESULT := 5;

    O_RESULT_MSG := 'CLNT_CASH_BALANCE_LOCK';

    OPEN CLNT_CASH_BALANCE_LOCK;

    CLOSE CLNT_CASH_BALANCE_LOCK;

    O_RESULT := 6;

    O_RESULT_MSG := 'CHINA_CLIENT_POSITION_LOCK';

    OPEN CHINA_CLIENT_POSITION_LOCK;

    CLOSE CHINA_CLIENT_POSITION_LOCK;

    O_RESULT := 7;

    O_RESULT_MSG := 'CHINA_EXECUTION_LOCK';

    OPEN CHINA_EXECUTION_LOCK;

    CLOSE CHINA_EXECUTION_LOCK;

    O_RESULT := 8;

    O_RESULT_MSG := 'CHINA_UNDELIVERED_TRADE_LOCK';

    OPEN CHINA_UNDELIVERED_TRADE_LOCK;

    CLOSE CHINA_UNDELIVERED_TRADE_LOCK;

    O_RESULT := 9;

    O_RESULT_MSG := 'STOCK_CAPITAL_GAIN_LOCK';

    OPEN STOCK_CAPITAL_GAIN_LOCK;

    CLOSE STOCK_CAPITAL_GAIN_LOCK;

    O_RESULT := 10;

    O_RESULT_MSG := 'CLIENT_COMMISSION_LOCK';

    OPEN CLIENT_COMMISSION_LOCK;

    CLOSE CLIENT_COMMISSION_LOCK;

    O_RESULT := 0;

    O_RESULT_MSG := NULL;

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            O_RESULT := -1;

        WHEN TOO_MANY_ROWS THEN

            O_RESULT := -2;

        WHEN OTHERS THEN

            NULL;

END;

/