储存过程¶

概述¶

存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可; 相对于SQL语句,它为用户提供了对数据更为方便的检索和更新方式.存储过程一般都是多条语句一起执行,所以会减少网络的开销.

存储过程和其他SQL语句的使用类似,只需要在执行命令 OscarCommand 之前,设置命令类型 CommandType 为 CommandType.StoredProcedure 这是必须的.这样驱动程序才会将这个SQL语句作为存储过程名来处理.

存储过程的参数的 Direction 属性为枚举值 ParameterDirection.

ParameterDirection有四个值,分别为 Input , Output , InputOutput , ReturnValue.

Input 类型的参数：参数值通过命令对象,传递给存储过程,驱动不会更改该参数的值.
Output 类型的参数：参数的值可以为Null,也可以是任何其他类型的值,驱动不会将该类型的值给存储过程,而是将相同位置的参数在存储过程中获得值,返回给用户.
InputOutput 类型的参数：该类型的参数的值传递给存储过程,并且接收改变后的值.
ReturnValue 类型的参数：参数获得存储过程执行后得到的值.

在 PROVIDER 中调用已储存过程的语法如下所示。

{过程名[(@ParametersName1, @ParametersName2, ...)]}

不带参数的储存过程的语法类似:

{过程名()}

这里无论带不带参数的存储过程的命令调用时,过程名后的圆括号不是必须的,例如: 有一个名为funcC的存储过程名,带有一个 inputoutput 参数,并且返回值为参数值减 3. 我们在创建命令对象 OscarCommand 对象时,可以采用如下的方法创建.

OscarCommand command = new OscarCommand("funcC", TheConnection);

OscarCommand command = new OscarCommand("funcC(@a)", TheConnection);

OscarCommand command = new OscarCommand("funcC(:a)", TheConnection);

以上三种方式创建的存储过程命令都是合法的,其中,第一种没有括号,也没有给出参数名,但是需要在后面的代码中,添加一个参数给命令对象,否则会抛出异常.

示例 1¶

private void TestCreateProcedureMethod()
{
    var command = _connect.CreateCommand();

    command.CommandText = "CREATE OR REPLACE PROCEDURE PROC000_000(INT, VARCHAR(64)) AS BEGIN INSERT INTO ProcTestTable VALUES($1, $2);END;";

    try
    {
        var result = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestExecuteProcedureMethod()
{
    TestCreateProcedureMethod();
    var command = _connect.CreateCommand();
    command.CommandType = System.Data.CommandType.StoredProcedure;

    command.CommandText = "PROC000_000(@v1,@v2)";

    var param1 = new OscarParameter("v1", OscarDbType.Int);

    param1.Direction = System.Data.ParameterDirection.Input;
    param1.Value = 13;

    command.Parameters.Add(param1);

    var param2 = new OscarParameter("v2", OscarDbType.VarChar);

    param2.Direction = System.Data.ParameterDirection.Input;
    param2.Value = "100";

    command.Parameters.Add(param2);

    try
    {
        var result = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
    DropProcecureMethod("PROC000_000");
}

private void DropProcecureMethod(string name)
{
    var command = _connect.CreateCommand();

    command.CommandText = $"DROP PROCEDURE {name} CASCADE;";

    try
    {
        var result = command.ExecuteNonQuery();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

示例 2¶

private void TestCreateProcedureMethod1()
{
    var command = _connect.CreateCommand();

    command.CommandText = "CREATE OR REPLACE PROCEDURE BEGINPROC_0 (v1 OUT INTEGER,v2 IN INTEGER) AS BEGIN SELECT COUNT(*) INTO v1 FROM ProcTestTable WHERE ID=v2;END;";

    try
    {
        var result = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

[TestMethod]
public void TestExecuteProcedureMethod2()
{
    TestCreateProcedureMethod1();
    var command = _connect.CreateCommand();
    command.CommandType = System.Data.CommandType.StoredProcedure;

    command.CommandText = "BEGINPROC_0 (@v1,@v2)";

    var param1 = new OscarParameter("v1", OscarDbType.Int);
    param1.Value = 1;

    param1.Direction = System.Data.ParameterDirection.Output;

    command.Parameters.Add(param1);

    var param2 = new OscarParameter("v2", OscarDbType.Int);

    param2.Direction = System.Data.ParameterDirection.Input;
    param2.Value = 11;

    command.Parameters.Add(param2);

    try
    {
        var result = command.ExecuteReader();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
    DropProcecureMethod("BEGINPROC_0");
}

private void DropProcecureMethod(string name)
{
    var command = _connect.CreateCommand();

    command.CommandText = $"DROP PROCEDURE {name} CASCADE;";

    try
    {
        var result = command.ExecuteNonQuery();
    }
    catch (Exception e)
    {
        Assert.Fail(message: e.Message);
    }
}

引用

OscarConnection
OscarCommand
OscarParameter
OscarDataReader
CommandType