c#中里有pl/sql匿名块和绑定变量在执行insert后获取当前序列值

1.oracle中建立表、序列、触发器:

 

SQL> create table TESTTAB

  2  (

  3    ID   NUMBER,

  4    NAME VARCHAR2(10)

  5  );

 

表被创建

 

SQL> create sequence TESTTABSEQ

  2  minvalue 1

  3  maxvalue 999999999999999999999999999

  4  start with 1

  5  increment by 1

  6  cache 20;

 

序列被创建

 

SQL> create or replace trigger Tr_testtabid

  2  before insert on testtab for each row

  3 

  4  begin

  5    SELECT TESTTABSEQ.NEXTVAL into :NEW.id from dual;

  6   end Tr_testtabid;

  7  /

 

触发器被创建

 

 

2.主要的C# 代码:

    using ORAC = System.Data.OracleClient;

     private void button1_Click(object sender, System.EventArgs e)

         {

              try

              {

             

                   string str_Sql = @"begin insert into testtab(name) values('test'); select TESTTABSEQ.Currval into :ID from dual; end;";

                   ORAC.OracleCommand cmd= new ORAC.OracleCommand(str_Sql,this.oracleConnection1);

 

                   ORAC.OracleParameter parm = new ORAC.OracleParameter("ID",ORAC.OracleType.Number);

                   parm.Direction = ParameterDirection.Output;

                   cmd.Parameters.Add(parm);

 

                   if(this.oracleConnection1.State == System.Data.ConnectionState.Closed)

                   {

                       this.oracleConnection1.Open();

                   }

                   cmd.ExecuteNonQuery();

 

 

                   this.textBox1.Text = cmd.Parameters[0].Value.ToString();

              }

              catch(Exception ex)

              {

                   MessageBox.Show("发生错误!");

                  

              }

              finally

              {

                   this.oracleConnection1.Close();

              }

 

             

         }

阅读更多
个人分类: .NET
上一篇c#中利用pl/sql匿名块和游标获取多个结果集
下一篇oracle中获取视图引用的表
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭