oracle创建的存储过程有问题，本人第一写有点不会，需要看下问题

Question

项目有个主表得数据来自子表和子表关联表里，需要去重然后抽取对应字段，存到主表

如下是我写得存储过程,大体思路是相通过查询出得结果集循环插入到PPS_MBOM_INFO_TEST 表中去
seq_pps_mbom_info_test.nextval是创建得主表序列号

CREATE OR REPLACE PROCEDURE "test"
BEGIN
FOR list IN (SELECT
DISTINCT
pmd.ACCOUNT_NUMBER AS ACCOUNT_NUMBER,
ppv.MLOC AS PPVMLOC,
ppv.BOM_NUM AS PPVBOM_NUM,
ppv.SERIALNUM AS PPVSERIALNUM
FROM
PPS_MBOM_DETAILED_0415 pmd
LEFT JOIN (
SELECT
a.TOP_PLAN_NUM,
a.MLOC,
a.BOM_NUM,
a.SERIALNUM
FROM
PPS_PLAN_VIN a
LEFT JOIN ( SELECT TOP_PLAN_NUM, max( VERSIONS ) AS max_VERSIONS FROM PPS_PLAN_VIN GROUP BY TOP_PLAN_NUM ) b ON a.TOP_PLAN_NUM = b.TOP_PLAN_NUM
AND a.VERSIONS = b.max_VERSIONS
) ppv ON ppv.TOP_PLAN_NUM = pmd.account_number) loop

insert into PPS_MBOM_INFO_TEST a values(seq_pps_mbom_info_test.nextval.nextval,list.ACCOUNT_NUMBER ,list.PPVMLOC,list.PPVBOM_NUM,list.PPVSERIALNUM);
END;
commit;
END;

Try to start debugging

Success: Debugging started

Debugging ends with error

ORA-06550: line 1, column 14:
PLS-00905: object PPS.test is invalid
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

请大家看下，谢谢大家

Answer 1

少了as,没有end loop

CREATE OR REPLACE PROCEDURE "test" as
BEGIN
  FOR list IN (SELECT DISTINCT pmd.ACCOUNT_NUMBER AS ACCOUNT_NUMBER,
                               ppv.MLOC           AS PPVMLOC,
                               ppv.BOM_NUM        AS PPVBOM_NUM,
                               ppv.SERIALNUM      AS PPVSERIALNUM
                 FROM PPS_MBOM_DETAILED_0415 pmd
                 LEFT JOIN (SELECT a.TOP_PLAN_NUM,
                                  a.MLOC,
                                  a.BOM_NUM,
                                  a.SERIALNUM
                             FROM PPS_PLAN_VIN a
                             LEFT JOIN (SELECT TOP_PLAN_NUM,
                                              max(VERSIONS) AS max_VERSIONS
                                         FROM PPS_PLAN_VIN
                                        GROUP BY TOP_PLAN_NUM) b
                               ON a.TOP_PLAN_NUM = b.TOP_PLAN_NUM
                              AND a.VERSIONS = b.max_VERSIONS) ppv
                   ON ppv.TOP_PLAN_NUM = pmd.account_number) loop
  
    insert into PPS_MBOM_INFO_TEST a
    values
      (seq_pps_mbom_info_test.nextval.nextval,
       list.ACCOUNT_NUMBER,
       list.PPVMLOC,
       list.PPVBOM_NUM,
       list.PPVSERIALNUM);
  END loop;
  commit;
END;