如何调用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

  FUNCTION SAY_HELLO(PI_NAME IN VARCHAR2) RETURN VARCHAR2 IS
    V_HELLO VARCHAR2(20);
  BEGIN
    IF PI_NAME IS NULL THEN
       V_HELLO := 'hello world!';
    ELSE
       V_HELLO := 'hello ' || PI_NAME;
    END IF;
    RETURN(V_HELLO);
  END SAY_HELLO;
  
  PROCEDURE OPEN_ONE_CURSOR (N_EMPNO   IN NUMBER,
                             IO_CURSOR IN OUT TYPE_CURSOR)
    IS 
        V_CURSOR TYPE_CURSOR; 
    BEGIN 
        IF N_EMPNO = 10 OR N_EMPNO = 20 
        OR N_EMPNO = 30 THEN
             OPEN V_CURSOR FOR 
           SELECT EMP.EMPNO, 
                  EMP.ENAME, 
                  DEPT.DEPTNO, 
                  DEPT.DNAME 
             FROM EMP, DEPT 
            WHERE EMP.DEPTNO = DEPT.DEPTNO 
                  AND EMP.DEPTNO = N_EMPNO;
        ELSE 
             OPEN V_CURSOR FOR 
             SELECT EMP.EMPNO, 
                    EMP.ENAME, 
                    DEPT.DEPTNO, 
                    DEPT.DNAME 
                 FROM EMP, DEPT 
              WHERE EMP.DEPTNO = DEPT.DEPTNO;
        END IF;
        IO_CURSOR := V_CURSOR; 
  END OPEN_ONE_CURSOR; 
  

  PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR  OUT TYPE_CURSOR,
                              DEPTCURSOR OUT TYPE_CURSOR)
  IS 
        V_CURSOR1 TYPE_CURSOR; 
        V_CURSOR2 TYPE_CURSOR; 
  BEGIN 
        OPEN V_CURSOR1 FOR SELECT * FROM EMP;
        OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
        EMPCURSOR  := V_CURSOR1; 
        DEPTCURSOR := V_CURSOR2; 
  END OPEN_TWO_CURSORS;

END PLSQL_HELLO_WORLD;