oracle使用的是11g版本，在他的存盤程序一條查詢陳述句怎么加鎖可以在commit之后才可以再次執行這條查詢陳述句或者有沒有別的處理方式，這個是存盤程序
CREATE OR REPLACE
PROCEDURE "OPEN_SUB_ACCT" (acctno IN VARCHAR2, actlevel IN VARCHAR2, actamt IN VARCHAR2, subactno OUT VARCHAR2, result OUT VARCHAR2, s OUT VARCHAR2,nacb OUT VARCHAR2)
AS
curs VARCHAR2(64);
oacct VARCHAR2(32);
BEGIN
IF actlevel='02' THEN
BEGIN
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.RESV3;
ELSE
IF curs<i.RESV3 THEN
curs:=i.RESV3;
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:=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;
EXCEPTION
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:=result||';The account has been used, and the account can not be opened';
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:=result||';Opening level 2 account end.';
commit work;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
ELSE
BEGIN
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.RESV3;
ELSE
IF curs<i.RESV3 THEN
curs:=i.RESV3;
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:=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;
EXCEPTION
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:=result||';The account has been used, and the account can not be opened';
RETURN;
END IF;
curs:=curs+1;
IF length(curs)>=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)<2 and length(curs)>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)<3 and length(curs)>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)<4 and length(curs)>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:=result||'; Opening level 3 or level 4 account end.';
IF length(curs)>=4 THEN
subactno:="CONCAT"("CONCAT"(acctno, '-'),curs);
ELSIF length(curs)<2 and length(curs)>0 THEN
subactno:="CONCAT"("CONCAT"(acctno, '-000'),curs);
ELSIF length(curs)<3 and length(curs)>1 THEN
subactno:="CONCAT"("CONCAT"(acctno, '-00'),curs);
ELSIF length(curs)<4 and length(curs)>2 THEN
subactno:="CONCAT"("CONCAT"(acctno, '-0'),curs);
END IF;
commit work;
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
END;
END;
END IF;
END;
uj5u.com熱心網友回復：
有人回復我么，大神們uj5u.com熱心網友回復：
樓主把的陳述句簡化一下，盡量不要涉及你的具體業務，能說明問題就可以了。uj5u.com熱心網友回復：
CREATE OR REPLACEPROCEDURE "OPEN_SUB_ACCT"
AS
curs VARCHAR2(64);
oacct VARCHAR2(32);
BEGIN
IF actlevel='02' THEN
BEGIN
BEGIN
SELECT MAX(TO_NUMBER(RESV3)) AS cur_no INTO curs FROM T_ACCT_MAST WHERE ACCT_LEVEL = '02' AND ACCT_NO=acctno;
END;
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);
commit work;
END;
ELSE
BEGIN
BEGIN
SELECT MAX(TO_NUMBER(RESV3)) AS cur_no INTO curs FROM T_ACCT_MAST WHERE ACCT_LEVEL IN ('03','04') AND ACCT_NO=acctno;
END;
curs:=curs+1;
INSERT INTO T_ACCT_MAST (ACCT_NO,SUB_ACCT_NO,ACCT_BAL,RESV3) VALUES(acctno,subactno,actamt,curs);
commit work;
END;
END IF;
END;
我這個存盤程序是使用java中的靜態同步方法呼叫的，為啥會出現查詢的結果有可能會兩次一樣的情況呢
uj5u.com熱心網友回復：
public static synchronized Map<String,Object> createSubAcctNoCall(CallProduce cp) {
Connection conn = null;
CallableStatement stmt = null;
try {
conn = SpringUtil.getBean(DatabaseNormalDao.class).getConnection();
} catch (SQLException e2) {
log.error("未獲取連接", e2);
return null;
}
try {
stmt = conn.prepareCall("{ " + (cp.isFunction() ? "?=" : "") + "call " + cp.getCallSql() + "}");
int c = 1;
log.debug("已添加call程序：" + cp.getCallSql() + "," + (cp.getVals() != null ? cp.getVals().length : 0) + ","
+ (cp.getOuts() != null ? cp.getOuts().length : 0 + "")+"主賬號為"+cp.getVals()[0]+"賬戶類別為"+cp.getVals()[1]);
if (cp.getVals() != null && cp.getVals().length > 0) {
for (int i = 0; i < cp.getVals().length; i++) {
stmt.setString(c, cp.getVals()[i]);
c += 1;
}
}
if (cp.getOuts() != null && cp.getOuts().length > 0) {
for (int i = 0; i < cp.getOuts().length; i++) {
stmt.registerOutParameter(Integer.valueOf(cp.getOuts()[i]), Types.VARCHAR);
}
}
stmt.execute();
String subAcctNo = stmt.getString(4);
log.debug("開戶詳細資訊為: " + stmt.getString(5));
log.debug("分戶號為: " +subAcctNo);
String resultCode = stmt.getString(6);
Map<String,Object> map = new HashMap<String,Object>();
if(resultCode.equals("111111")) {
map.put(resultCode, subAcctNo);
}else if(resultCode.equals("666666")){
map.put(resultCode, stmt.getString(5));
}
if(stmt!=null) {
stmt.close();
stmt = null;
}
if(conn!=null) {
conn.close();
conn = null;
}
return map;
} catch (Exception e) {
log.error("開戶失敗",e);
try {
} catch (SQLException e1) {
log.error("關閉資源失敗",e1);
}
}
}
return null;
} 這個是java代碼
uj5u.com熱心網友回復：
沒人了嗎，大神們uj5u.com熱心網友回復：
沒有很看懂樓主的意思 大概就是邏輯流的控制吧 自己理清楚邏輯轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66569.html
標籤：開發
上一篇：OCP證書