如何将Oracle SQL语句拆分为ADO.NET使用

Question

如何正确地拆分SQL语句以发送给Oracle ADO.NET客户端？例如，假设您在文本文件中有以下代码并希望执行这些语句：

CREATE TABLE foo (bar VARCHAR2(100));
INSERT INTO foo (bar) VALUES('one');
INSERT INTO foo (bar) VALUES('two');

我认为尝试在一个命令中发送所有内容会导致Oracle抱怨";"。我的第一个想法是在";"字符上进行拆分，然后逐个发送。

但是，存储过程也可能包含分号，那么如何使拆分程序保持整个存储过程的完整性？它需要查找begin/end语句或"/"吗？

在这些方面，ODP.NET和Microsoft Oracle Provider之间有区别吗？

- Ted Elliott

Answer 1

没有DDL，你可以使用BEGIN和END将语句包含起来来创建一个匿名的PL/SQL块：

BEGIN
  INSERT INTO foo (bar) VALUES('one');
  INSERT INTO foo (bar) VALUES('two');
END;

要执行DDL操作（例如CREATE TABLE），您需要使用动态PL/SQL：

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE foo (bar VARCHAR2(100))';
  EXECUTE IMMEDIATE 'INSERT INTO foo (bar) VALUES(:v)' USING 'one';
  EXECUTE IMMEDIATE 'INSERT INTO foo (bar) VALUES(:v)' USING 'two';
END;

插入语句也是动态的，因为在运行代码块之前表格并不存在，所以会导致编译失败。

注意：这是一个不寻常的需求，通常应用程序不应该创建表格！

- Tony Andrews

---

我正在尝试执行用户输入的SQL语句，并让应用程序正确地将其切分以发送给ADO.NET客户端。 - Ted Elliott

哎呀 - 我不想支持用户输入的 SQL！还不如直接让他们访问 SQL Plus？ - Tony Andrews

Answer 2

一家名为devart（www.devart.com）的公司发布了一个名为dotConnect for Oracle的库。

该库包含一个名为OracleScript的类，它具有将包含多个语句的SQL脚本分离的能力。

- Carl

Answer 3

扩展Tony的答案，您可以使用匿名块来实现此目的，只需确保字符串按预期工作即可。这是一个简单的例子，基本上是在分号上进行拆分并创建块。

using System;
using System.Data;
using System.Text;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace ODPSample
{
    class Class1
    {

        private static string formatAnonBlock(string userData)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Begin ");
            string[] statements = userData.Split(';');
            foreach (string s in statements)
            {
                if (s.Length > 0)
                {
                    sb.AppendFormat(" EXECUTE IMMEDIATE '{0}';", s.Replace("'", "''"));
                }
            }
            sb.Append(" END ; ");
            return sb.ToString();
        }
        static void Main(string[] args)
        {
            Console.WriteLine("Demo: Anon Block");

            // Connect
            string connectStr = "User Id=scott;Password=tiger;Data Source=database";

            string userInputtedSQL;
            userInputtedSQL = "Create table ABC(val varchar2(50)); insert into ABC values('123');insert into ABC values('567');";

            string anonBlock;
            anonBlock = formatAnonBlock(userInputtedSQL);
            Console.WriteLine(anonBlock);

            OracleConnection connection = new OracleConnection(connectStr);
            OracleCommand cmd = new OracleCommand(anonBlock, connection);


            try
            {
                connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine("Done");
        }
    }
}

- Harrison