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;
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;
解决方案 »
- 【求助】使用imp导入数据的时候出错
- [菜鸟问题]关于日期型和字符型之间的转换函数和索引
- sql文搞不定了.好象是group by的问题.大家帮我看看.
- ora-28009:connection as sys should be as sysdba or sysoper 错误,昨天还是可以的呢
- pl/sql 登陆问题?
- 一个关于null的问题,请高手解释一下
- 时间为什么显示成日期?
- 谁有比较详细的建立快照复制的资料???????????????????????????????????
- 谁做过oracle的advance queue啊???
- n阶方阵行列式的计算问题
- 自定义table在拼装sql语句时遇到的问题
- Oracle新手有几道题不会,求教!
PROCEDURE "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中的静态同步方法调用的,为啥会出现查询的结果有可能会两次一样的情况呢
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代码