淺析 C# 繫結變數與匿名區塊獲取序列當前值

我們將談到 C# 繫結變數與匿名區塊獲取序列當前值，首先需要在 Oracle 中進行一系列操作，接著是 C# 繫結變數具體操作。

1. 在 Oracle 中建立表、序列、觸發器：

SQL> create table TESTTAB (
    ID NUMBER,
    NAME VARCHAR2(10)
);

表被建立。

SQL> create sequence TESTTABSEQ
    minvalue 1
    maxvalue 999999999999999999999999999
    start with 1
    increment by 1
    cache 20;

序列被建立。

SQL> create or replace trigger Tr_testtabid
    before insert on testtab for each row
    begin
        SELECT TESTTABSEQ.NEXTVAL into :NEW.id from dual;
    end Tr_testtabid;
    /

觸發器被建立。

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