如何在同一行的C#中执行两个SQLplus语句?

huangapple go评论62阅读模式
英文:

How can I execute 2 SQLplus statements on the same 1 line C #

问题

I have 1 procedure that can be used on sqlplus but in C# I don't know how to write execute in cmd.commandtext

this is my command in SQL:

CREATE OR REPLACE PROCEDURE get_column1 (p_MaritalStatus IN VARCHAR2,p_cursor out SYS_REFCURSOR)
as
BEGIN
   Open p_cursor for
   SELECT column_name FROM all_tab_columns WHERE table_name = p_MaritalStatus ORDER BY column_id;
   dbms_sql.return_result(p_cursor);
EXCEPTION
WHEN no_data_found THEN 
NULL;
 END;

VARIABLE cur REFCURSOR;
execute get_column1('V_$SGA',:cur);

And in C#:

OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "VARIABLE cur REFCURSOR;execute get_column1('V_$SGA',:cur);";
cmd.CommandType = CommandType.StoredProcedure;
OracleDataReader dr = cmd.ExecuteReader();
英文:

I have 1 procedure that can be used on sqlplus but in C# I don't know how to write execute in cmd.commandtext

this is my command
sqlplus

CREATE OR REPLACE PROCEDURE get_column1 (p_MaritalStatus IN VARCHAR2,p_cursor out SYS_REFCURSOR)
as
BEGIN
   Open p_cursor for
   SELECT column_name FROM all_tab_columns WHERE table_name = p_MaritalStatus ORDER BY column_id;
   dbms_sql.return_result(p_cursor);
EXCEPTION
WHEN no_data_found THEN 
NULL;
 END;

VARIABLE cur REFCURSOR;
execute get_column1('V_$SGA',:cur);

c#

`OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "VARIABLE cur REFCURSOR;execute get_column1('V_$SGA',:cur);";
cmd.CommandType = CommandType.StoredProcedure;
OracleDataReader dr = cmd.ExecuteReader();`

答案1

得分: 1

当你将CommandType设置为StoredProcedure时,这意味着CommandText仅包含存储过程的名称。就像这样:

cmd.CommandText = "get_column1";
cmd.CommandType = CommandType.StoredProcedure;

您的命令不是存储过程,它是内联SQL。这个SQL调用存储过程的事实与此无关。只需删除设置CommandType的那一行,并接受默认的Text

我不常使用Oracle,所以我仍然不能100%确定它是否能够处理使用分号分隔的多个SQL语句,但对于SQL Server,这应该没有问题,所以我猜应该可以。

编辑:

根据下面发布的评论,您不能在单个命令中执行多个语句。在这种情况下,您可能需要使用两个单独的命令或在它们之间更改CommandText,最好在一个事务内执行。关于CommandType的说法仍然适用。

英文:

When you set CommandType to StoredProcedure, that means that the CommandText contains ONLY the name of a stored procedure. Something like this:

cmd.CommandText = "get_column1";
cmd.CommandType = CommandType.StoredProcedure;

Your command is NOT a stored procedure. It's inline SQL. The fact that that SQL invokes a stored procedure is neither here nor there. Just get rid of the line that sets CommandType and accept the default Text.

I don't really use Oracle so I'm still not 100% sure that it will work with multiple SQL statements separated by semicolons but the would work with SQL Server, so I'm guessing it will be fine.

EDIT:

According to a comment posted below, you cannot execute multiple statements together in a single command. In that case, you would presumably have use two separate commands or the same command twice and change the CommandText in between. You'd probably want to do so within a transaction. What I said about the CommandType still stands.

答案2

得分: 1

Oracle禁止在单个命令中使用多个语句,以防止SQL注入攻击。

对于您的用例,请不要尝试在SQL中声明变量,而是在C#中指定绑定参数:

CREATE OR REPLACE PROCEDURE get_column1 (
  p_MaritalStatus IN  all_tab_columns.table_name%TYPE,
  p_cursor        OUT SYS_REFCURSOR
)
AS
BEGIN
   OPEN p_cursor FOR
     SELECT column_name
     FROM   all_tab_columns
     WHERE  table_name = p_MaritalStatus
     ORDER BY column_id;
END;
/

然后(未经测试,因为我没有与C#连接的数据库,但它给出了一般的想法):

OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "get_column1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("P_MARITALSTATUS", OracleDbType.Char).Value = "V_$SGA";
cmd.Parameters.Add("P_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
OracleDataReader dr = cmd.ExecuteReader()
英文:

Oracle forbids multiple statements in a single command. This helps to prevent SQL injection attacks.

For your use case, do not try to declare the variable in SQL, specify the bind parameter in C#:

CREATE OR REPLACE PROCEDURE get_column1 (
  p_MaritalStatus IN  all_tab_columns.table_name%TYPE,
  p_cursor        OUT SYS_REFCURSOR
)
AS
BEGIN
   OPEN p_cursor FOR
     SELECT column_name
     FROM   all_tab_columns
     WHERE  table_name = p_MaritalStatus
     ORDER BY column_id;
END;
/

Then (untested as I do not have a C# connected database but it gives you the general idea):

OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "get_column1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("P_MARITALSTATUS", OracleDbType.Char).Value = "V_$SGA";
cmd.Parameters.Add("P_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
OracleDataReader dr = cmd.ExecuteReader()

huangapple
  • 本文由 发表于 2023年3月23日 09:44:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/75818630.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定