1.需求说明
系统中多个业务ID生成规则为:固定前缀+顺序号,考虑到可能删除和顺序号到头的原因,因此需要一个存储过程,查找出可用的顺序号
2.存储过程
--生成cno的规则
--cno_pre:前缀,即非顺序号的部分
--table_name:表名
--col_name:CNO字段名
--suf_length:顺序号长度
--min_use_cno:返回的最小可用值
create or replace procedure cpa_noacct_cno_create_pro(cno_pre in varchar,
table_name in varchar,
col_name in varchar,
suf_length in number,
min_use_cno out varchar) as
sel_sql varchar(1000);
begin
for i in 1 .. power(10, suf_length) - 1 loop
insert into CPA_ID_CREATE_TAB values (lpad(i, suf_length, '0'));
end loop;
sel_sql := 'select min(t.id) from (select id from CPA_ID_CREATE_TAB';
sel_sql := sel_sql || ' minus select substr(t.' || col_name || ',-' ||
suf_length || ') id ';
sel_sql := sel_sql || ' from cpa_noacct t where t.' || col_name ||
' like ''' || cno_pre;
sel_sql := sel_sql || '''||' || '''' || '%' || '''' || ') t';
dbms_output.put_line(sel_sql);
EXECUTE IMMEDIATE sel_sql
into min_use_cno;
end cpa_noacct_cno_create_pro;
如上,其中犯错的地方是:
1.在procedure后的参数,不需要指定长度
2.Oracle的幂函数为power(x,y),意为x的y次方
3.单引号的转义,第一个和最后一个单引号是原始的单引号,第二个是用作转义符,第三个被转义
select '''' from dual
4.把字符串当做sql执行要用
EXECUTE IMMEDIATE,在sql字符串中不要有 into 变量.而在EXECUTE IMMEDIATE后使用into 变量
5.左补零函数为lpad(字段名,要求长度,当长度不够时用来补充的字符)
6.
dbms_output是有长度限制的,可以在plsql的中进行设置
7.求差集,用minus
8.临时表的创建
7.求差集,用minus
8.临时表的创建
CREATE GLOBAL TEMPORARY TABLE T_TEST
(
ID VARCHAR2(32),
NAME VARCHAR2(32)
) ON COMMIT DELETE ROWS;
3.在mybatis中的调用
代码如下
在xml中
<!--生成cno-->
<select id="getMinCno" statementType="CALLABLE" parameterType="java.util.HashMap" resultType="java.util.Map">
<![CDATA[
{call cpa_noacct_cno_create_pro
(#{pre,mode=IN,jdbcType=VARCHAR},
#{tableName,mode=IN,jdbcType=VARCHAR},
#{colName,mode=IN,jdbcType=VARCHAR},
#{length,mode=IN,jdbcType=INTEGER},
#{minCno,mode=OUT,jdbcType=VARCHAR})}
]]>
</select>
注意:
1.要指定mode为IN或者OUT
2.如果存储过程中返回值用OUT,则直接使用用来传递参数的MAP进行接收
3.如果使用return,则需要指定返回值.如
#{res,mode=OUT,jdbcType=VARCHAR}={}
4.在call外层要使用{}包裹
调用示例
Map<String,String> params = new HashMap<String, String>();
params.put("pre","14040121t");
params.put("tableName","cpa_noacct");
params.put("colName","nocpa_Cno");
params.put("length","3");
params.put("minCno","-1");
cpaCpafAppServiceImpl.test(params);
System.out.println(params);
总结:
1.对mybatis调用存储过程不熟练,上网找了好多,都是讲了用法,没有讲遇到的问题
2.oracle的存储过程不熟练,一边查资料,一边写
3.粗心大意