的为大哥帮忙解决下我的存储过程问题

Question

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;

Answer 1

'SBUDGET_EXPENSE_ID' 'SCONSTRUCTION' 是过程的参数,只读的,不能在过程中修改

Answer 2

要先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;