如何調用Oracle PL/SQL(ODP.NET)

以下代碼使用的是: System.Data.OracleClient (.NET For Oracle Data Provider)，使用

1. 如何調用Oracle PL/SQL中有傳回值的Function？可以通過Command和Parameter對象匹配調用。

添加的Parameter被匹配為傳回值和參數，第一個Parameter是傳回值，然後都是傳入PL/SQL的參數。

private void button1_Click(object sender, System.EventArgs e)
  {
   string connectionString = "User ID=scott;Password=tiger;Data Source=FIREFOX;";
   string strPLSQL = "PLSQL_HELLO_WORLD.SAY_HELLO";
   
   OracleConnection conn = new OracleConnection(connectionString);
   OracleCommand cmd = new OracleCommand(strPLSQL, conn);
   cmd.CommandType = CommandType.StoredProcedure;
   
   // Param ReturnValue:
   cmd.Parameters.Add("PO_RETURN", OracleType.VarChar, 20);
   cmd.Parameters["PO_RETURN"].Direction = ParameterDirection.ReturnValue;
  
   // Param 1: 注意，這裡PI_NAME必須和PLSQL裡的名字相符（大小寫不敏感），否則拋出ORA-06550錯誤。
   cmd.Parameters.Add("PI_NAME", OracleType.VarChar, 10);
   cmd.Parameters["PI_NAME"].Direction = ParameterDirection.Input;
   cmd.Parameters["PI_NAME"].Value = "firefox";

   try
   {
    conn.Open();
    cmd.ExecuteNonQuery();
    string strRet = cmd.Parameters["PO_RETURN"].Value.ToString();
     MessageBox.Show(strRet);
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
   finally
   {
       conn.Close();
   }

  }

呵呵， 用點技巧來調用PL/SQL帶傳回值的Function： select ... from dual  

  /// <summary>
  /// Call the function of Oracle.
  /// </summary>
  /// <param name="sender"></param>
  /// <param name="e"></param>
  private void button2_Click(object sender, System.EventArgs e)
  {
      string connectionString = "User ID=scott;Password=tiger;Data Source=FIREFOX;";

   OracleConnection conn = new OracleConnection(connectionString);
   OracleCommand cmd = new OracleCommand();

   cmd.Connection = conn;
   cmd.CommandType = CommandType.Text;
   cmd.CommandText = "SELECT PLSQL_HELLO_WORLD.SAY_HELLO(:PI_NAME) FROM DUAL";

   cmd.Parameters.Add("PI_NAME", OracleType.VarChar, 20);
   cmd.Parameters["PI_NAME"].Direction = ParameterDirection.Input;
   cmd.Parameters["PI_NAME"].Value = "firefox";

   try
   {
    conn.Open();
    string strRet = cmd.ExecuteScalar() as string;
    MessageBox.Show(strRet);
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
   finally
   {
    conn.Close();
   }
  }

  如何返回遊標cursor, 這裡，cursor只能是output parameter。使用Command來擷取Cursor時必須使用ExecuteReader()

  private void button3_Click(object sender, System.EventArgs e)
  {
   string connectionString = "User ID=scott;Password=tiger;Data Source=FIREFOX;";
   string strPLSQL = "PLSQL_HELLO_WORLD.OPEN_ONE_CURSOR";
   
   OracleConnection conn = new OracleConnection(connectionString);
   OracleCommand cmd = new OracleCommand(strPLSQL, conn);
   cmd.CommandType = CommandType.StoredProcedure;
     
   // Param 1:
   cmd.Parameters.Add("N_EMPNO", OracleType.Number, 2);
   cmd.Parameters["N_EMPNO"].Direction = ParameterDirection.Input;
   cmd.Parameters["N_EMPNO"].Value = 0;
   // Param 2:
   cmd.Parameters.Add("IO_CURSOR", OracleType.Cursor);
   cmd.Parameters["IO_CURSOR"].Direction = ParameterDirection.Output;
           
   OracleDataReader odr = null;

   try
   {
    conn.Open();
    odr = cmd.ExecuteReader();
    while(odr.Read())
    {
     MessageBox.Show(odr.GetValue(0).ToString());
    }
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
   finally
   {
    if(odr != null)
     odr.Close();
    conn.Close();
   }
  }

當然也可以使用OracleDataAdapter:

private void button5_Click(object sender, System.EventArgs e)
  {
   string connectionString = "User ID=scott;Password=tiger;Data Source=FIREFOX;";
   string strPLSQL = "PLSQL_HELLO_WORLD.OPEN_ONE_CURSOR";

   OracleConnection conn = new OracleConnection(connectionString);
   OracleCommand cmd = new OracleCommand(strPLSQL, conn);
   cmd.CommandType = CommandType.StoredProcedure;
     
   // Param 1:
   cmd.Parameters.Add("N_EMPNO", OracleType.Number, 2);
   cmd.Parameters["N_EMPNO"].Direction = ParameterDirection.Input;
   cmd.Parameters["N_EMPNO"].Value = 30;
   // Param 2:
   cmd.Parameters.Add("IO_CURSOR", OracleType.Cursor);
   cmd.Parameters["IO_CURSOR"].Direction = ParameterDirection.Output;
           
   OracleDataAdapter adp = new OracleDataAdapter();
   adp.SelectCommand = cmd;
           
   try
   {
    DataTable dt = new DataTable();
    adp.Fill(dt);
    this.dataGrid1.DataSource = dt;
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
  }

 

附：PL/SQL

CREATE OR REPLACE PACKAGE BODY PLSQL_HELLO_WORLD is</p><p> FUNCTION SAY_HELLO(PI_NAME IN VARCHAR2) RETURN VARCHAR2 IS<br /> V_HELLO VARCHAR2(20);<br /> BEGIN<br /> IF PI_NAME IS NULL THEN<br /> V_HELLO := 'hello world!';<br /> ELSE<br /> V_HELLO := 'hello ' || PI_NAME;<br /> END IF;<br /> RETURN(V_HELLO);<br /> END SAY_HELLO;</p><p> PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,<br /> IO_CURSOR IN OUT TYPE_CURSOR)<br /> IS<br /> V_CURSOR TYPE_CURSOR;<br /> BEGIN<br /> IF N_EMPNO = 10 OR N_EMPNO = 20<br /> OR N_EMPNO = 30 THEN<br /> OPEN V_CURSOR FOR<br /> SELECT EMP.EMPNO,<br /> EMP.ENAME,<br /> DEPT.DEPTNO,<br /> DEPT.DNAME<br /> FROM EMP, DEPT<br /> WHERE EMP.DEPTNO = DEPT.DEPTNO<br /> AND EMP.DEPTNO = N_EMPNO;<br /> ELSE<br /> OPEN V_CURSOR FOR<br /> SELECT EMP.EMPNO,<br /> EMP.ENAME,<br /> DEPT.DEPTNO,<br /> DEPT.DNAME<br /> FROM EMP, DEPT<br /> WHERE EMP.DEPTNO = DEPT.DEPTNO;<br /> END IF;<br /> IO_CURSOR := V_CURSOR;<br /> END OPEN_ONE_CURSOR; </p><p> PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT TYPE_CURSOR,<br /> DEPTCURSOR OUT TYPE_CURSOR)<br /> IS<br /> V_CURSOR1 TYPE_CURSOR;<br /> V_CURSOR2 TYPE_CURSOR;<br /> BEGIN<br /> OPEN V_CURSOR1 FOR SELECT * FROM EMP;<br /> OPEN V_CURSOR2 FOR SELECT * FROM DEPT;<br /> EMPCURSOR := V_CURSOR1;<br /> DEPTCURSOR := V_CURSOR2;<br /> END OPEN_TWO_CURSORS;</p><p>END PLSQL_HELLO_WORLD;<br />