Oracle 11 g version is used the stored procedure is a query in his how to lock can be after the commit can execute the query again, or do you have any other way to handle it, this process is to store

The CREATE OR REPLACE
PROCEDURE "OPEN_SUB_ACCT" (acctno IN VARCHAR2, actlevel IN VARCHAR2, actamt IN VARCHAR2, subactno OUT VARCHAR2, the result OUT VARCHAR2, s OUT VARCHAR2, nacb OUT VARCHAR2)
AS
Curs VARCHAR2 (64);
Oacct VARCHAR2 (32).
The BEGIN
IF actlevel='02' THEN
The BEGIN
The BEGIN
Result:='Opening level 2 account begin';
FOR I IN (SELECT TO_NUMBER (RESV3) RESV3 FROM T_ACCT_MAST WHERE ACCT_LEVEL='02' AND ACCT_NO=acctno FOR UPDATE)
LOOP
IF curs IS NULL THEN
Curs:=i.R ESV3;
The ELSE
IF cursCurs:=i.R ESV3;
END IF;
END IF;
END LOOP;
- SELECT MAX (TO_NUMBER (RESV3)) AS cur_no INTO curs FROM T_ACCT_MAST WHERE ACCT_LEVEL='02' AND ACCT_NO=acctno;
Result: the result=| | '; Query the maximal account is' | | curs;
--select max("TO_NUMBER"(REGEXP_SUBSTR(sub_acct_no, '[^-]+', 1,2,'i'))) AS cur_no INTO curs from T_ACCT_MAST where (("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') between '9001' AND '9999') OR ("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') between '99001' AND '99999') OR ("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') BETWEEN '999001' AND '999999') OR ("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') between '9990001' AND '9999999')) and ACCT_NO=acctno AND "LENGTH"(REGEXP_SUBSTR(sub_acct_no, '[^-]+', 1,2,'i'))<14 GROUP BY ACCT_NO;
The EXCEPTION
The WHEN no_data_found THEN
Curs:='9000';
END;
IF curs='9999' THEN
Curs:='99000';
ELSIF curs='99999' THEN
Curs:='999000';
ELSIF curs='999999' THEN
Curs:='9999000';
ELSIF curs='9999999' THEN
Curs:='99999000';
END IF;
IF curs='99999999' THEN
S:='666666'.
Result: the result=| | '; The account has had 2, and The account can not be The opened ';
The RETURN;
END IF;
Curs:=curs + 1;

Subactno:="CONCAT" (" CONCAT "(acctno, '-'), curs);

INSERT INTO T_ACCT_MAST (ACCT_NO, SUB_ACCT_NO ACCT_BAL, RESV3) VALUES (acctno, subactno actamt, curs);

S:='111111'.
Result: the result=| | '; The account Opening level 2 end. ';

Commit the work.
The EXCEPTION
The WHEN OTHERS THEN
ROLLBACK;
END;
The ELSE
The BEGIN
The BEGIN
Result:='Opening level 3 or level 4 account begin';
FOR I IN (SELECT TO_NUMBER (RESV3) AS RESV3 FROM T_ACCT_MAST WHERE ACCT_LEVEL IN (' 03, '04') AND ACCT_NO=acctno FOR UPDATE)
LOOP
IF curs IS NULL THEN
Curs:=i.R ESV3;
The ELSE
IF cursCurs:=i.R ESV3;
END IF;
END IF;
END LOOP;
- SELECT MAX (TO_NUMBER (RESV3)) AS cur_no INTO curs FROM T_ACCT_MAST WHERE ACCT_LEVEL IN (' 03, '04') AND ACCT_NO=acctno;
Result: the result=| | '; Query the maximal account is' | | curs;
--select max("TO_NUMBER"(REGEXP_SUBSTR(sub_acct_no, '[^-]+', 1,2,'i'))) AS cur_no INTO curs from T_ACCT_MAST where (("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') between '0001' AND '8999') OR ("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') between '10000' AND '89999') OR ("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') BETWEEN '100000' AND '899999') OR ("REGEXP_SUBSTR"(SUB_ACCT_NO, '[^-]+', 1,2,'i') between '1000000' AND '8999999')) and ACCT_NO=acctno AND "LENGTH"(REGEXP_SUBSTR(sub_acct_no, '[^-]+', 1,2,'i'))<14 GROUP BY ACCT_NO;
The EXCEPTION
The WHEN no_data_found THEN
Curs:='0001';
END;
IF curs='8999' THEN
Curs:='10000';
ELSIF curs='89999' THEN
Curs:='100000';
ELSIF curs='899999' THEN
Curs:='1000000';
ELSIF curs='8999999' THEN
Curs:='10000000';
END IF;
IF curs='99999999' THEN
S:='666666'.
Result: the result=| | '; The account has had 2, and The account can not be The opened ';
The RETURN;
END IF;
Curs:=curs + 1;
IF length (curs) & gt; 4 THEN=
INSERT INTO T_ACCT_MAST (ACCT_NO, SUB_ACCT_NO ACCT_BAL, RESV3) VALUES (acctno, "CONCAT" (" CONCAT "(acctno, '-'), curs), actamt, curs);
ELSIF length (curs) & lt; 2 and length (curs) & gt; 0 THEN
INSERT INTO T_ACCT_MAST (ACCT_NO, SUB_ACCT_NO ACCT_BAL, RESV3) VALUES (acctno, "CONCAT" (" CONCAT "(acctno, '000'), curs), actamt, curs);
ELSIF length (curs) & lt; 3 and length (curs) & gt; 1 THEN
INSERT INTO T_ACCT_MAST (ACCT_NO, SUB_ACCT_NO ACCT_BAL, RESV3) VALUES (acctno, "CONCAT" (" CONCAT "(acctno, '00 -'), curs), actamt, curs);
ELSIF length (curs) & lt; 4 and length (curs) & gt; 2 THEN
INSERT INTO T_ACCT_MAST (ACCT_NO, SUB_ACCT_NO ACCT_BAL, RESV3) VALUES (acctno, "CONCAT" (" CONCAT "(acctno, '0'), curs), actamt, curs);
END IF;
S:='111111'.
Result: the result=| | '; Opening level 3 or level 4 account end. ';
IF length (curs) & gt; 4 THEN=
Subactno:="CONCAT" (" CONCAT "(acctno, '-'), curs);
ELSIF length (curs) & lt; 2 and length (curs) & gt; 0 THEN
Subactno:="CONCAT" (" CONCAT "(acctno, '000'), curs);
ELSIF length (curs) & lt; 3 and length (curs) & gt; 1 THEN
Subactno:="CONCAT" (" CONCAT "(acctno, '00 -'), curs);
ELSIF length (curs) & lt; 4 and length (curs) & gt; nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull