半岛权威十大直营(官方)网站

    dotConnect for Oracle的OracleScript类可以逐一执行几个SQL语句，例如，可以使用它来执行来自级脚本文件的大量的脚本。但是如果这些脚本是针对于SQL*Plus的，就可能会包含一些命令，在OracleScript不支持。今天就来看看怎么样在OracleScript上执行这些脚本。

    OracleScript不支持SQL*Plus命令，比如说设置或是执行。当OracleScript遇见一个这样的命令，就会抛出一个异常。对于这样的异常，dotConnect for Oracle提供了下面的脚本：

set define off;
select * from dept;
execute stored_procedure('parameter');

    每个SQL * Plus命令必须与新线（'\n'）字符结束，允许OracleScript检测SQL*Plus命令的结束以及一个新的语句的开始。

    处理这样的脚本，可以使用OracleScript对象的SqlStatementExecute事件。使用下面的代码可以创建一个OracleScript对象。

[C#]

OracleConnection conn = new OracleConnection("connection string");
static void Main(string[] args)
{
        OracleConnection conn = new OracleConnection("connection string");
        conn.Open();
        OracleScript script = new OracleScript("script text", conn);
           
        script.SqlStatementExecute += new SqlStatementExecuteEventHandler(script_SqlStatementExecute);
        script.Execute();
        conn.Close();
}

[Visual Basic]

Private conn As New OracleConnection("connection string")
Shared Sub Main(args As String())
        Dim conn As New OracleConnection("connection string")
        conn.Open()
        Dim script As New OracleScript("script text", conn)

        script.SqlStatementExecute += New SqlStatementExecuteEventHandler(script_SqlStatementExecute)
        script.Execute()
        conn.Close()
End Sub

    SqlStatementExecuteEventArgs类型有SqlPlusStatementType的属性，这就决定了SQL * Plus命令的类型。因此我们可以判断以及跳过各种不同的类型的SQL*Plus命令，或是在SqlStatementExecute事件处理程序上进行处理。下面的例子中，就是跳过命令以及在PL/SQL块上终止执行命令。

[C#]

static void script_SqlStatementExecute(object sender, SqlStatementExecuteEventArgs e)
{
   //we skip executing Set commands
   if (e.SqlPlusStatementType == SqlPlusStatementType.Set)
        {
                e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement;
                return;
        }

   //we process the execute command - it must be executed inside the PL/SQL block
        if (e.SqlPlusStatementType == SqlPlusStatementType.Execute)
        {
                var script=(OracleScript)sender;
                e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement;
                OracleCommand comm = new OracleCommand();
                comm.Connection = script.Connection;
                comm.CommandText = "begin " + e.Text.Remove(0, "execute".Length).Trim() + "; end;";
                comm.ExecuteNonQuery();
                comm.Dispose();
                return;
        }           
}

[Visual Basic]

Private Shared Sub script_SqlStatementExecute(sender As Object, e As SqlStatementExecuteEventArgs)
	'we skip executing Set commands
	If e.SqlPlusStatementType = SqlPlusStatementType.[Set] Then
		e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement
		Return
	End If

	'we process the execute command - it must be executed inside the PL/SQL block
	If e.SqlPlusStatementType = SqlPlusStatementType.Execute Then
		Dim script = DirectCast(sender, OracleScript)
		e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement
		Dim comm As New OracleCommand()
		comm.Connection = script.Connection
		comm.CommandText = "begin " & e.Text.Remove(0, "execute".Length).Trim() & "; end;"
		comm.ExecuteNonQuery()
		comm.Dispose()
		Return
	End If
End Sub