Как правильно вызвать и получить результат хранимой процедуры из OracleSQL посредством OleDB, приложение Net.Framework, Windows Forms?
Процедура использует курсор для расчёта переменной.
create or replace Procedure profit
(str_date IN date, prf OUT NUMBER)
IS
val number := 0;
CURSOR c1
IS
SELECT (PJ.COST-
(SELECT SUM(EMP.SALARY)
FROM PROJECTS PRJ,
DEPARTMENTS_EMPLOYEES DEPE,
EMPLOYEES EMP
WHERE PRJ.ID = PJ.ID
AND PRJ.DEPARTMENT_ID = DEPE.DEPARTMENT_ID
AND DEPE.EMPLOYEE_ID = EMP.ID
GROUP BY PJ.NAME)
*((SELECT EXTRACT(MONTH FROM DATE_END_REAL)
FROM PROJECTS
WHERE PROJECTS.ID = PJ.ID) -
(SELECT EXTRACT(MONTH FROM DATE_BEG)
FROM PROJECTS
WHERE PROJECTS.ID = PJ.ID))) AS profit
FROM PROJECTS PJ
WHERE PJ.DATE_END_REAL > str_date
AND PJ.DATE_END_REAL < CURRENT_DATE
AND PJ.DEPARTMENT_ID IS NOT NULL;
BEGIN
prf := 0;
OPEN c1;
LOOP
FETCH c1 INTO val;
prf := prf + val;
VAL := 0;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END profit;
Попытка вызвать через C#:
OleDbCommand GetProfit = new OleDbCommand("profit(@str_date,@pfr)", OleDbConnection);
GetProfit.CommandType = CommandType.StoredProcedure;
GetProfit.Parameters.Add("@str_date", OleDbType.DBDate).Value = StartingDateForProfByPeriod.SelectionStart.ToString("yyyy-MM-dd");
GetProfit.Parameters.Add("@prf", OleDbType.Numeric);//.Value = profit;
GetProfit.Parameters[0].Direction = ParameterDirection.Input;
GetProfit.Parameters[1].Direction = ParameterDirection.Output;
var reader1 = GetProfit.ExecuteReader();
if (reader1.HasRows)
{
while (reader1.Read())
{
ProfOfProjForPeriod.Text += (Convert.ToInt32(reader1[0]));
}
}
Вызывает ошибку: System.Data.OleDb.OleDbException: "Неопознанная ошибка"
Ответы (1 шт):
Автор решения: VLADISLAV
→ Ссылка
Правильный способ вызова процедуры:
OleDbCommand GetProfit = new OleDbCommand("profit", OleDbConnection);
GetProfit.CommandType = CommandType.StoredProcedure;
GetProfit.Parameters.Add("str_date", OleDbType.DBDate).Value = StartingDateForProfByPeriod.SelectionStart;
GetProfit.Parameters.Add("prf", OleDbType.Numeric);//.Value = profit;
GetProfit.Parameters[0].Direction = ParameterDirection.Input;
GetProfit.Parameters[1].Direction = ParameterDirection.Output;
GetProfit.ExecuteNonQuery();
ProfOfProjForPeriod.Text = Convert.ToString(GetProfit.Parameters[1].Value);