CREATE OR REPLACE PROCEDURE sto_fixedProjectBudget
(
sbudget_expense_id in number,
scode_all in varchar,
sconstruction in number,
smachine in number,
sinstall in number,
sother in number,
ssums in number
)
is
CURSOR MyCursor is select budget_expense_id,code_all from project where id in (select parent_id from project where parent_id>0);
begin
FETCH MyCursor into sbudget_expense_id,scode_all;
open MyCursor;
WHILE MyCursor%found loop
select sum(construct_budget) into sconstruction--,smachine=sum(machine_budget),sinstall=sum(install_budget),sother=sum(other_budget),
--ssums=sum(construct_budget)+sum(machine_budget)+sum(install_budget)+sum(other_budget)
from (
SELECT PROJECT.*, PROJECT_EXPENSE.construction AS construct_budget,
PROJECT_EXPENSE.install AS install_budget,PROJECT_EXPENSE.machine AS machine_budget,
PROJECT_EXPENSE.other AS other_budget, PROJECT_EXPENSE.PROJECT_SUM AS sum_budget
FROM PROJECT,PROJECT_EXPENSE where PROJECT.budget_expense_id = PROJECT_EXPENSE.id) v_projectBudgetInfo
where code_all like code_all+'%' and id not in (select parent_id from project where parent_id>0);
update PROJECT_EXPENSE
set construction=sconstruction,machine=smachine,install=sinstall,other=sother,project_sum=ssums
where id = sbudget_expense_id;
commit;
FETCH MyCursor INTO sbudget_expense_id,scode_all;
end loop;
close MyCursor;
END sto_fixedProjectBudget;
各位大哥帮忙看下我的存储过程老是报错
PROCEDURE EOS_PPM.STO_FIXEDPROJECTBUDGET 编译错误
错误：PLS-00403: 表达式 'SBUDGET_EXPENSE_ID' 不能作为 SELECT/FETCH 语句的 INTO 目标
行：14
文本：FETCH MyCursor into sbudget_expense_id,scode_all;
错误：PL/SQL: SQL Statement ignored
行：14
文本：FETCH MyCursor into sbudget_expense_id,scode_all;
错误：PLS-00403: 表达式 'SCONSTRUCTION' 不能作为 SELECT/FETCH 语句的 INTO 目标
行：17
文本：select sum(construct_budget) into sconstruction--,smachine=sum(machine_budget),sinstall=sum(install_budget),sother=sum(other_budget),
错误：PLS-00403: 表达式 'SBUDGET_EXPENSE_ID' 不能作为 SELECT/FETCH 语句的 INTO 目标
行：30
文本：FETCH MyCursor INTO sbudget_expense_id,scode_all;
错误：PL/SQL: SQL Statement ignored
行：30
文本：FETCH MyCursor INTO sbudget_expense_id,scode_all;
------解决方案--------------------
'SBUDGET_EXPENSE_ID' 'SCONSTRUCTION' 是过程的参数,只读的,不能在过程中修改
------解决方案--------------------
- SQL code
要先open才能fetch。修改如下：CREATE OR REPLACE PROCEDURE STO_FIXEDPROJECTBUDGET(SBUDGET_EXPENSE_ID IN NUMBER, SCODE_ALL IN VARCHAR, SCONSTRUCTION IN NUMBER, SMACHINE IN NUMBER, SINSTALL IN NUMBER, SOTHER IN NUMBER, SSUMS IN NUMBER) IS CURSOR MYCURSOR IS SELECT BUDGET_EXPENSE_ID, CODE_ALL FROM PROJECT WHERE ID IN (SELECT PARENT_ID FROM PROJECT WHERE PARENT_ID > 0);BEGIN OPEN MYCURSOR; LOOP FETCH MYCURSOR INTO SBUDGET_EXPENSE_ID, SCODE_ALL; EXIT WHERE MYCURSOR%NOTFOUND; SELECT SUM(CONSTRUCT_BUDGET) INTO SCONSTRUCTION --,smachine=sum(machine_budget),sinstall=sum(install_budget),sother=sum(other_budget), --ssums=sum(construct_budget)+sum(machine_budget)+sum(install_budget)+sum(other_budget) FROM (SELECT PROJECT.*, PROJECT_EXPENSE.CONSTRUCTION AS CONSTRUCT_BUDGET, PROJECT_EXPENSE.INSTALL AS INSTALL_BUDGET, PROJECT_EXPENSE.MACHINE AS MACHINE_BUDGET, PROJECT_EXPENSE.OTHER AS OTHER_BUDGET, PROJECT_EXPENSE.PROJECT_SUM AS SUM_BUDGET FROM PROJECT, PROJECT_EXPENSE WHERE PROJECT.BUDGET_EXPENSE_ID = PROJECT_EXPENSE.ID) V_PROJECTBUDGETINFO WHERE CODE_ALL LIKE CODE_ALL + '%' AND ID NOT IN (SELECT PARENT_ID FROM PROJECT WHERE PARENT_ID > 0); UPDATE PROJECT_EXPENSE SET CONSTRUCTION = SCONSTRUCTION, MACHINE = SMACHINE, INSTALL = SINSTALL, OTHER = SOTHER, PROJECT_SUM = SSUMS WHERE ID = SBUDGET_EXPENSE_ID; END LOOP; COMMIT; CLOSE MYCURSOR;END STO_FIXEDPROJECTBUDGET;