oracle 中生成流水号方法代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 oracle 中生成流水号方法
作者:Dezai.CN / 发布于2011/6/22/ 840
<div>
<strong>思路：</strong>
通过一个流水号表，记录当前最大的流水号，以便下次取用。每次取号时，将流水号表更新成大的。
<strong>涉及的表：</strong>
　　流水号表hp_no：　　
<table style="border-collapse: collapse" border="0" cellspacing="0" cellpadding="0" width="232" height="89" x:str="">
    <colgroup><col style="width: 70pt" width="93" /><col style="width: 90pt" width="120" /></colgroup>
    <tbody>
        <tr style="height: 14.25pt" height="19">
            <td style="width: 70pt; height: 14.25pt" height="19" width="93" align="center">字段</td>
            <td style="width: 90pt" width="120" align="center">类型</td>
        </tr>
        <tr style="height: 14.25pt" height="19">
            <td style="height: 14.25pt" height="19">TYPE_NAME</td>
            <td>VARCHAR2(100)</td>
        </tr>
        <tr style="height: 14.25pt" height="19">
            <td style="height: 14.25pt" height="19">START_NO</td>
            <td>VARCHAR2(100)</td>
        </tr>
        <tr style="height: 14.25pt" height="19">
            <td style="height: 14.25pt" height="19">CURRENT_NO</td>
            <td>VARCHAR2(100)</td>
        </tr>
    </tbody>
</table>


<strong>代码：</strong>
1)通过函数得到最大值。 有三个参数，

　　第一个参数是类型，便于得到不同的流水号。
　　第二个参数是前缀，用于在流水号前边放置特别的字符。

　　第三个参数是生成的流水号长度。 

<span style="color: #0000ff">CREATE</span><span style="color: #000000"> </span><span style="color: #808080">OR</span><span style="color: #000000"> </span><span style="color: #ff00ff">REPLACE</span><span style="color: #000000"> </span><span style="color: #0000ff">FUNCTION</span><span style="color: #000000"> Hp_Test_GetNo(
P_TYPE_NAME </span><span style="color: #808080">IN</span><span style="color: #000000"> </span><span style="color: #0000ff">VARCHAR2</span><span style="color: #000000"> </span><span style="color: #0000ff">DEFAULT</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000">, </span><span style="color: #008080">--</span><span style="color: #008080">类型 参数1</span><span style="color: #008080">
</span><span style="color: #000000">P_START_NO </span><span style="color: #808080">IN</span><span style="color: #000000"> </span><span style="color: #0000ff">VARCHAR2</span><span style="color: #000000">, </span><span style="color: #008080">--</span><span style="color: #008080">前缀 参数2</span><span style="color: #008080">
</span><span style="color: #000000">P_LENGTH </span><span style="color: #808080">IN</span><span style="color: #000000"> </span><span style="color: #0000ff">NUMBER</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">得到流水号长度 参数3</span><span style="color: #008080">
</span><span style="color: #000000">)
</span><span style="color: #0000ff">RETURN</span><span style="color: #000000"> </span><span style="color: #0000ff">VARCHAR2</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">返回类型</span><span style="color: #008080">
</span><span style="color: #0000ff">IS</span><span style="color: #000000">
V_RESULT </span><span style="color: #0000ff">VARCHAR2</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">) :</span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000">; </span><span style="color: #008080">--</span><span style="color: #008080">结果 变量1</span><span style="color: #008080">
</span><span style="color: #000000">V_CURRENT_NO </span><span style="color: #0000ff">VARCHAR2</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">) :</span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000">; </span><span style="color: #008080">--</span><span style="color: #008080">当前值 变量2</span><span style="color: #008080">
</span><span style="color: #000000">V_NUM </span><span style="color: #0000ff">NUMBER</span><span style="color: #000000"> :</span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">; </span><span style="color: #008080">--</span><span style="color: #008080">补值 变量3</span><span style="color: #008080">
</span><span style="color: #000000">V_START_NO </span><span style="color: #0000ff">VARCHAR2</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">); </span><span style="color: #008080">--</span><span style="color: #008080">前缀 变量4</span><span style="color: #008080">
</span><span style="color: #000000">V_ROWID </span><span style="color: #0000ff">VARCHAR2</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">); </span><span style="color: #008080">--</span><span style="color: #008080">要更新行的RowID 变量4</span><span style="color: #008080">
</span><span style="color: #0000ff">BEGIN</span><span style="color: #000000">
V_START_NO :</span><span style="color: #808080">=</span><span style="color: #000000">P_START_NO; </span><span style="color: #008080">--</span><span style="color: #008080">参数值赋给变量</span><span style="color: #008080">
</span><span style="color: #000000">
</span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> </span><span style="color: #ff00ff">COUNT</span><span style="color: #000000"> (</span><span style="color: #808080">*</span><span style="color: #000000">)
</span><span style="color: #0000ff">INTO</span><span style="color: #000000"> V_NUM
</span><span style="color: #0000ff">FROM</span><span style="color: #000000"> HP_NO
</span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> TYPE_NAME </span><span style="color: #808080">=</span><span style="color: #000000"> P_TYPE_NAME </span><span style="color: #808080">AND</span><span style="color: #000000"> START_NO </span><span style="color: #808080">=</span><span style="color: #000000"> P_START_NO; </span><span style="color: #008080">--</span><span style="color: #008080">得到类型与前缀相同值的数量，主要用于判断是否产生过流水号</span><span style="color: #008080">
</span><span style="color: #000000">
</span><span style="color: #008080">--</span><span style="color: #008080">判断是否有资料 没有则产生初始值插入库中</span><span style="color: #008080">
</span><span style="color: #0000ff">IF</span><span style="color: #000000"> V_NUM </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">没有记录过时</span><span style="color: #008080">
</span><span style="color: #0000ff">THEN</span><span style="color: #000000">
V_CURRENT_NO :</span><span style="color: #808080">=</span><span style="color: #000000"> LPAD (</span><span style="color: #ff0000">'</span><span style="color: #ff0000">1</span><span style="color: #ff0000">'</span><span style="color: #000000">, P_LENGTH, </span><span style="color: #ff0000">'</span><span style="color: #ff0000">0</span><span style="color: #ff0000">'</span><span style="color: #000000">); </span><span style="color: #008080">--</span><span style="color: #008080">得到P_length长的0001流水号</span><span style="color: #008080">
</span><span style="color: #000000">V_RESULT :</span><span style="color: #808080">=</span><span style="color: #000000"> V_CURRENT_NO; </span><span style="color: #008080">--</span><span style="color: #008080">将前缀与流水号相连起来</span><span style="color: #008080">
</span><span style="color: #000000">
</span><span style="color: #0000ff">INSERT</span><span style="color: #000000"> </span><span style="color: #0000ff">INTO</span><span style="color: #000000"> HP_NO(TYPE_NAME, START_NO, CURRENT_NO)
</span><span style="color: #0000ff">VALUES</span><span style="color: #000000"> (P_TYPE_NAME, P_START_NO, V_CURRENT_NO); </span><span style="color: #008080">--</span><span style="color: #008080">将新的最大值记录下来</span><span style="color: #008080">
</span><span style="color: #0000ff">ELSE</span><span style="color: #000000"> 
</span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> ROWID, CURRENT_NO </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">
</span><span style="color: #0000ff">INTO</span><span style="color: #000000"> V_ROWID, V_CURRENT_NO
</span><span style="color: #0000ff">FROM</span><span style="color: #000000"> HP_NO
</span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> TYPE_NAME </span><span style="color: #808080">=</span><span style="color: #000000"> P_TYPE_NAME </span><span style="color: #808080">AND</span><span style="color: #000000"> START_NO </span><span style="color: #808080">=</span><span style="color: #000000"> P_START_NO; </span><span style="color: #008080">--</span><span style="color: #008080">得到最大值加1</span><span style="color: #008080">
</span><span style="color: #000000">
V_CURRENT_NO :</span><span style="color: #808080">=</span><span style="color: #000000"> LPAD (V_CURRENT_NO, P_LENGTH, </span><span style="color: #ff0000">'</span><span style="color: #ff0000">0</span><span style="color: #ff0000">'</span><span style="color: #000000">); 


</span><span style="color: #0000ff">UPDATE</span><span style="color: #000000"> HP_NO
</span><span style="color: #0000ff">SET</span><span style="color: #000000"> CURRENT_NO </span><span style="color: #808080">=</span><span style="color: #000000"> V_CURRENT_NO
</span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> ROWID </span><span style="color: #808080">=</span><span style="color: #000000"> V_ROWID; </span><span style="color: #008080">--</span><span style="color: #008080">更新最大值</span><span style="color: #008080">
</span><span style="color: #0000ff">END</span><span style="color: #000000"> </span><span style="color: #0000ff">IF</span><span style="color: #000000">; 

</span><span style="color: #0000ff">IF</span><span style="color: #000000"> V_START_NO </span><span style="color: #0000ff">IS</span><span style="color: #000000"> </span><span style="color: #0000ff">NULL</span><span style="color: #000000"> </span><span style="color: #808080">OR</span><span style="color: #000000"> V_START_NO</span><span style="color: #808080">=</span><span style="color: #ff0000">''</span><span style="color: #000000"> </span><span style="color: #0000ff">THEN</span><span style="color: #000000">
V_RESULT :</span><span style="color: #808080">=</span><span style="color: #000000"> V_CURRENT_NO;
</span><span style="color: #0000ff">ELSE</span><span style="color: #000000">
V_RESULT :</span><span style="color: #808080">=</span><span style="color: #000000">V_START_NO </span><span style="color: #808080">||</span><span style="color: #000000"> V_CURRENT_NO;
</span><span style="color: #0000ff">END</span><span style="color: #000000"> </span><span style="color: #0000ff">IF</span><span style="color: #000000">;

</span><span style="color: #0000ff">RETURN</span><span style="color: #000000"> V_RESULT;
</span><span style="color: #0000ff">END</span><span style="color: #000000">;
</span>
<span style="color: #000000">
<strong>使用存储过程调用函数的方法：</strong>
<span style="color: #008080">--</span><span style="color: #008080">调用function存储过程</span><span style="color: #008080">
</span><span style="color: #0000ff">CREATE</span><span style="color: #000000"> </span><span style="color: #808080">OR</span><span style="color: #000000"> </span><span style="color: #ff00ff">REPLACE</span><span style="color: #000000"> </span><span style="color: #0000ff">PROCEDURE</span><span style="color: #000000"> Hp_Pro_GetTestNo
(
v_FirstLetter </span><span style="color: #808080">in</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar2</span><span style="color: #000000">,
v_Result out </span><span style="color: #0000ff">varchar2</span><span style="color: #000000">
)
</span><span style="color: #0000ff">IS</span><span style="color: #000000">
v_FirstValue </span><span style="color: #0000ff">varchar2</span><span style="color: #000000">(</span><span>30);
v_SystDate </span><span style="color: #0000ff">varchar2</span><span style="color: #000000">(</span><span><strong>30</strong>);
</span><span>BEGIN
select</span><span style="color: #000000"> to_char(sysdate,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">yyyymm</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #0000ff">into</span><span style="color: #000000"> v_SystDate </span><span>from dual;
v_FirstValue:</span><span style="color: #808080">=</span><span style="color: #000000">(v_FirstLetter </span><span>|| v_SystDate);
v_Result:</span><span style="color: #808080">=</span><span style="color: #000000">hp_test_getno(</span><span style="color: #ff0000">'</span><span style="color: #ff0000">HpTest</span><span style="color: #ff0000">'</span><span style="color: #000000"> ,v_FirstValue,</span><span><strong>4</strong>);
</span><span>END;
</span>


<strong>结果：</strong>
<strong>　　调用存储过程<span style="color: #000000">Hp_Pro_GetTestNo</span>('hp',4);</strong>
<strong>　　结果为：hp2010020001 
</strong>
</span></div>
试试其它关键字
　流水号　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)