Fjli de blog分享 http://blog.sciencenet.cn/u/shipo Make or Break

博文

存储过程生成流水号

已有 7076 次阅读 2010-5-6 09:44 |个人分类:学习心得|系统分类:科研笔记| 存储过程, 流水号

(1)建一个存储生成流水号的表 SriaNum

--/*
CREATE  TABLE [dbo].[SriaNum] (
    [Num] [int] NOT NULL
)

--*/

(2)建立生成流水号的存储过程
--DROP PROC dpIDS_GetSerialNumber
--/*

CREATE  PROC dpIDS_GetSerialNumber
@SerialNumber VARCHAR(9) OUTPUT  -- 指明为输出参数
AS
IF NOT EXISTS(SELECT
            *
        FROM
            SriaNum)       
    BEGIN
        INSERT INTO SriaNum values(1)
    END
ELSE
    BEGIN
        UPDATE SriaNum SET Num=Num+1   
    END

SELECT
        @SerialNumber = REPLICATE('0',9-LEN(Num))+CONVERT(VARCHAR(9),Num)
  --生成[000000001, 999999999]范围内的流水号
FROM
        SriaNum

--*/

(3)执行存储过程
--/*
DECLARE @TEST VARCHAR(9)
EXECUTE [dbo].dpIDS_GetSerialNumber @TEST OUTPUT   -- 指明为输出变量
SELECT @TEST AS SERIALNUMBER
     -- 获得流水号
--*/

------------------------------------

环境:SQL SERVER 2005

******************************************

C#里调用存储过程并获得输出参数值

private  void GetSerialNumber()
{
            // Create an connection instance
            string Connection = "server = FJLI\SQLEXPRESS; uid = sa; pwd = 123456; database = TestOne";
            SqlConnection DataConn = new SqlConnection(Connection);
            // Open connection
            try
            {
                if (DataConn.State == ConnectionState.Closed)
                {
                    DataConn.Open();
                    Console.WriteLine("Database connection successful!");
                }
            }
            catch (SqlException sqex)
            {
                // Create connection failed
                Console.WriteLine(sqex.Message);
                return;
            }

            SqlCommand DBCmd = new SqlCommand("dpIDS_GetSerialNumber", DataConn);                                             DBCmd.CommandType = CommandType.StoredProcedure;     

            // Output parameter(值得注意的是:这里Add的输出变量必须与存储过程里的输出变量同名,否则会报告“dpIDS_GetSerialNumber 的@SerialNumber参数not supplied错误!)
            SqlParameter param = new SqlParameter("@SerialNumber", SqlDbType.VarChar, 9);            
            param.Direction = System.Data.ParameterDirection.Output;
            DBCmd.Parameters.Add(param);
            
            
            try
            {

                DBCmd.ExecuteNonQuery();
              
  string serialNumber = param.Value.ToString();  //得到输出参数的值
                System.Console.WriteLine("SerialNumber: " + serialNumber);
            }
            catch(SqlException sqex)
            {
                Console.WriteLine(sqex.Message);
            }                       

            try
            {
                if (DataConn.State == ConnectionState.Open)
                {
                    DataConn.Close();
                    Console.WriteLine("Database connection successful!");
                }
            }
            catch (SqlException sqex)
            {
                Console.WriteLine(sqex.Message);
            }

 }

********************补充**********************

Oracle环境下存储过程生成流水号[000000001,999999999].

(1)  先建一个序列,例如:MSG_SERIALNUMBER_SEQ,设置最小值、最大值、起始值、增量值、是否循环;

(2)  建存储过程,例如:msg_sp_getSerialNumber:

--生成[000000001, 999999999]范围内的流水号
CREATE  OR REPLACE PROCEDURE msg_sp_getSerialNumber(
SerialNumber OUT VARCHAR)                         
IS 
     icount number;
BEGIN  
     SELECT MSG_SERIALNUMBER_SEQ.NEXTVAL INTO icount FROM dual;    
     SerialNumber := LPAD(TO_CHAR(icount),9,'0');          
END msg_sp_getSerialNumber;

(3)  测试。在PL/SQL里右键刚刚建的存储过程,选择“TEST”,再F9。结束既可以看到流水号了!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

另外,也同样可以用SQL SERVER里面的方法,即建立一个存储流水号的表,存储过程如下:

CREATE  OR REPLACE PROCEDURE msg_sp_getSerialNumber (SerialNumber out VARCHAR2) as
   RC_COUNT NUMBER;  
   BEGIN
       SELECT COUNT(*) INTO RC_COUNT FROM MSG_SERIALNUMBER;
       IF  RC_COUNT=0   THEN       
           INSERT INTO MSG_SERIALNUMBER VALUES(1);  
       else
           UPDATE MSG_SERIALNUMBER SET NUM=NUM+1;           
       END IF;
  SELECT LPAD(Num,9,'0') into SerialNumber FROM MSG_SERIALNUMBER;
   END;



https://blog.sciencenet.cn/blog-427149-320445.html

上一篇:按图索骥——新街口豁口外的那些河儿
下一篇:DB Table 到 DataSet 生成 XML
收藏 IP: .*| 热度|

1 黄富强

发表评论 评论 (0 个评论)

数据加载中...
扫一扫,分享此博文

Archiver|手机版|科学网 ( 京ICP备07017567号-12 )

GMT+8, 2024-9-19 05:45

Powered by ScienceNet.cn

Copyright © 2007- 中国科学报社

返回顶部