虚
虚心进步
Unregistered / Unconfirmed
GUEST, unregistred user!
这是我的源代码:
private void button1_Click(object sender, System.EventArgs e)
{ string affect;
this.oleDbConnection1=new OleDbConnectio();
this.oleDbConnection1.ConnectionString="Provider=OraOLEDB.Oracle.1;User ID=ops$ed01;Data Source=csst;Extended Properties='';Persist Security Info=True;Password=ops$ed01";
this.oleDbCommand1=new OleDbCommand("UD_EXECUTE_SQL",this.oleDbConnection1);
this.oleDbCommand1.CommandType =CommandType.StoredProcedure;
OleDbParameter IdIN = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pSql", OleDbType.VarChar,100));
IdIN.Direction = ParameterDirection.Input;
IdIN.Value ="select 1 from dual";
OleDbParameter SQLRltOut = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pSqlResult", OleDbType.VarChar, 8));
SQLRltOut.Direction = ParameterDirection.Output;
OleDbParameter RltOut = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pResult", OleDbType.SmallInt, 8));
RltOut.Direction = ParameterDirection.Output;
OleDbParameter MsgOut = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pMsg", OleDbType.VarChar, 28));
MsgOut.Direction = ParameterDirection.Output;
try
{
this.oleDbConnection1.Open();
affect=this.oleDbCommand1.ExecuteNonQuery().ToString();
Console.WriteLine("Number of Rows affected: " + affect);
MessageBox.Show(SQLRltOut.Value.ToString());
MessageBox.Show(RltOut.Value.ToString());
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
}
this.oleDbConnection1.Close();
this.oleDbCommand1.Dispose();
this.oleDbConnection1.Dispose();
}
下面是存储过程的源代码:
CREATE OR REPLACE PROCEDURE
STDBA.UD_EXECUTE_SQL(pSql IN VARCHAR2,pSqlResult OUT VARCHAR2,pResult OUT NUMBER,pMsg OUT varchar2) AS
CURSOR_HANDLE INTEGER;
RESULT varchar2(500);
CNT NUMBER;
begin
IF length(pSql) < 10 or pSql is null then
pResult:=-1;
pMsg:='输入的SQL语句无效!';
pSqlResult:='';
RETURN;
END IF;
CURSOR_HANDLE:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURSOR_HANDLE,pSql,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(CURSOR_HANDLE,1,RESULT,500);
CNT:=DBMS_SQL.EXECUTE(CURSOR_HANDLE);
IF DBMS_SQL.FETCH_ROWS(CURSOR_HANDLE)=0 then
--UFS_RESULT:='0';
pResult:=-1;
pMsg:='没有符合该SQL语句的记录!';
pSqlResult:='';
/*DBMS_OUTPUT.PUT_LINE(UFS_RESULT);*/
else
DBMS_SQL.COLUMN_VALUE(CURSOR_HANDLE,1,RESULT);
pSqlResult:=RESULT;
pResult:=0;
pMsg:='该SQL语句的值已经求出!';
/*DBMS_OUTPUT.PUT_LINE(UFS_RESULT);*/
END IF;
DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);
RETURN;
EXCEPTION WHEN OTHERS then
pMsg:='存储过程 STDBA.UD_EXECUTE_SQL 执行错误,请检查!'||SQLERRM;
pResult:=-1;
RAISE_APPLICATION_ERROR(sqlcode,pMsg);
END UD_EXECUTE_SQL;
/
谢谢大哥们的帮忙!!!急得上火
private void button1_Click(object sender, System.EventArgs e)
{ string affect;
this.oleDbConnection1=new OleDbConnectio();
this.oleDbConnection1.ConnectionString="Provider=OraOLEDB.Oracle.1;User ID=ops$ed01;Data Source=csst;Extended Properties='';Persist Security Info=True;Password=ops$ed01";
this.oleDbCommand1=new OleDbCommand("UD_EXECUTE_SQL",this.oleDbConnection1);
this.oleDbCommand1.CommandType =CommandType.StoredProcedure;
OleDbParameter IdIN = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pSql", OleDbType.VarChar,100));
IdIN.Direction = ParameterDirection.Input;
IdIN.Value ="select 1 from dual";
OleDbParameter SQLRltOut = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pSqlResult", OleDbType.VarChar, 8));
SQLRltOut.Direction = ParameterDirection.Output;
OleDbParameter RltOut = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pResult", OleDbType.SmallInt, 8));
RltOut.Direction = ParameterDirection.Output;
OleDbParameter MsgOut = this.oleDbCommand1.Parameters.Add(new OleDbParameter("pMsg", OleDbType.VarChar, 28));
MsgOut.Direction = ParameterDirection.Output;
try
{
this.oleDbConnection1.Open();
affect=this.oleDbCommand1.ExecuteNonQuery().ToString();
Console.WriteLine("Number of Rows affected: " + affect);
MessageBox.Show(SQLRltOut.Value.ToString());
MessageBox.Show(RltOut.Value.ToString());
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
}
this.oleDbConnection1.Close();
this.oleDbCommand1.Dispose();
this.oleDbConnection1.Dispose();
}
下面是存储过程的源代码:
CREATE OR REPLACE PROCEDURE
STDBA.UD_EXECUTE_SQL(pSql IN VARCHAR2,pSqlResult OUT VARCHAR2,pResult OUT NUMBER,pMsg OUT varchar2) AS
CURSOR_HANDLE INTEGER;
RESULT varchar2(500);
CNT NUMBER;
begin
IF length(pSql) < 10 or pSql is null then
pResult:=-1;
pMsg:='输入的SQL语句无效!';
pSqlResult:='';
RETURN;
END IF;
CURSOR_HANDLE:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURSOR_HANDLE,pSql,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(CURSOR_HANDLE,1,RESULT,500);
CNT:=DBMS_SQL.EXECUTE(CURSOR_HANDLE);
IF DBMS_SQL.FETCH_ROWS(CURSOR_HANDLE)=0 then
--UFS_RESULT:='0';
pResult:=-1;
pMsg:='没有符合该SQL语句的记录!';
pSqlResult:='';
/*DBMS_OUTPUT.PUT_LINE(UFS_RESULT);*/
else
DBMS_SQL.COLUMN_VALUE(CURSOR_HANDLE,1,RESULT);
pSqlResult:=RESULT;
pResult:=0;
pMsg:='该SQL语句的值已经求出!';
/*DBMS_OUTPUT.PUT_LINE(UFS_RESULT);*/
END IF;
DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);
RETURN;
EXCEPTION WHEN OTHERS then
pMsg:='存储过程 STDBA.UD_EXECUTE_SQL 执行错误,请检查!'||SQLERRM;
pResult:=-1;
RAISE_APPLICATION_ERROR(sqlcode,pMsg);
END UD_EXECUTE_SQL;
/
谢谢大哥们的帮忙!!!急得上火