存储过程
create or replace procedure UpdateDivCell
(intFstRowId in Integer,
intBudgetId in Integer,
gSetid In Integer,
gYear In Integer,
spdivrowid Out Number
)
Is
SQLText varchar2(2048);
begin
SQLText:=' SELECT a.DivRowid, b.name bname, b.Code BCode FROM DivPay a LEFT JOIN temp_Budgetsection b ON a.year = b.year AND a.bid = b.bid'; If intFstRowId <> 0 Then
SQLText:=SQLText|| 'and a.BudgetId = '||0|| 'and a.FstRowId = '||intFstRowId; Else
SQLText:=SQLText|| 'and a.BudgetId = '||0|| 'and a.FstRowId= '||intBudgetId;
End If;
SQLText:='begin '||SQLText ||';end;';
execute immediate SQLText;
end UpdateDivCell;我想用spdivrowid 这个值返回查询结果里面的divrowid字段值,这个该怎么修改?
这是小弟第一次写存储过程,感激了!
create or replace procedure UpdateDivCell
(intFstRowId in Integer,
intBudgetId in Integer,
gSetid In Integer,
gYear In Integer,
spdivrowid Out Number
)
Is
SQLText varchar2(2048);
begin
SQLText:=' SELECT a.DivRowid, b.name bname, b.Code BCode FROM DivPay a LEFT JOIN temp_Budgetsection b ON a.year = b.year AND a.bid = b.bid'; If intFstRowId <> 0 Then
SQLText:=SQLText|| 'and a.BudgetId = '||0|| 'and a.FstRowId = '||intFstRowId; Else
SQLText:=SQLText|| 'and a.BudgetId = '||0|| 'and a.FstRowId= '||intBudgetId;
End If;
SQLText:='begin '||SQLText ||';end;';
execute immediate SQLText;
end UpdateDivCell;我想用spdivrowid 这个值返回查询结果里面的divrowid字段值,这个该怎么修改?
这是小弟第一次写存储过程,感激了!
execute immediate SQLText into spdivrowid; -- 这里加上into 子句,另外要确保查询语句只返回1条记录
execute immediate SQLText into v1,v2,v3
create ... procedure ..
Is
--声明集合类型
type myType is table of DivPay.DivRowid %TYPE;
v_myType myType;
begin
... EXECUTE IMMEDIATE 'SELECT DivRowid FROM DivPay'
BULK COLLECT INTO myType;
...
end;