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();

              }

 

             

         }