mes 入库单号 锁表方案

 

1. 提取单别

create table z_doctype as

select distinct substr(INSTRUCTION_NUM, 0, 3) doctype from Z_INSTRUCTION_DOC where INSTRUCTION_NUM not like 'PP%'

 

单别表结构

 

程序优化方案如下:

me 入库单 优化-防止多人入库取到同一个单号方案

1. 原本取下一个单号的逻辑不变
2. 在取原本取下一个单号前加一个 锁表逻辑  执行语句

                  select * from Z_DOCTYPE where DOCTYPE='单据前缀' for update nowait

1. 上面的行锁 会随着  事物的  提交而自动解锁,无需其他代码

 

取最大单号表sql

select nvl2(max(INSTRUCTION_NUM),

'AGK' || to_char(sysdate, 'yyyymmdd') || substr( '0000'|| to_char(to_number(substr(max(INSTRUCTION_NUM), -4)) + 1),-4),

'AGK' || to_char(sysdate, 'yyyymmdd') || '0001'

)

from Z_INSTRUCTION_DOC t where INSTRUCTION_NUM like 'AGK' || to_char(sysdate, 'yyyymmdd') || '%';

 

select max(INSTRUCTION_NUM) from Z_INSTRUCTION_DOC t where INSTRUCTION_NUM like 'AGK' || to_char(sysdate, 'yyyymmdd') || '%'

 

 

c# 技术验证代码

try
            {
                textBox1.Text = "";
                db.UseTransaction(true);
                string sqlString = "select * from Z_DOCTYPE where DOCTYPE='" + comboBox1.Text + "' for update nowait ";
                db.Sql(sqlString).Execute();

                sqlString = "select nvl2(max(INSTRUCTION_NUM),\n" +
              "       '" + comboBox1.Text + "' || to_char(sysdate, 'yyyymmdd') || substr( '0000'|| to_char(to_number(substr(max(INSTRUCTION_NUM), -4)) + 1),-4),\n" +
              "       '" + comboBox1.Text + "' || to_char(sysdate, 'yyyymmdd') || '0001'\n" +
              "        )\n" +
              "from Z_INSTRUCTION_DOC t where INSTRUCTION_NUM like '" + comboBox1.Text + "' || to_char(sysdate, 'yyyymmdd') || '%'";

                textBox1.Text = db.Sql(sqlString).QuerySingle<string>();

            }
            catch (Exception ex)
            {
                db.Rollback();
                MessageBox.Show(ex.Message);
            }