存储过程中的insert into的使用

Question

不太明白楼主的想法
我想把Collect_ID，Apply_ID直接放到select后面和group by后面就应该可以了啦。

Answer 1

意思是insert into中Collect_ID，Apply_ID的值要从变量Apply_Id_Value 与变量Collect_Value中取值，其它的值从select中取值。

Answer 2

CREATE OR REPLACE PROCEDURE Sumcollect(Apply_Id_Value VARCHAR2) IS
  Collect_Value   VARCHAR2(10);
BEGIN
  
  INSERT INTO Lz_t_Transit_Apply_Collect
    (Collect_Id
    ,Apply_Id
    ,Tree_Id
    ,Tree_Name
    ,Standard_Id
    ,Standard_Name
    ,Breed_Name
    ,Tree_Number
    ,Datum_Area
    ,Specification_Name)
    (SELECT  Lz_s_Transit_Apply_Collect.NEXTVAL
           ,Apply_Id
           ,Tree_Id
           ,Tree_Name
           ,Standard_Id
           ,Standard_Name
           ,Breed_Name
           ,SUM(Nvl(Tree_Number_2, 0)) Tree_Number
           ,SUM(Nvl(Datum_Area_2, 0)) Datum_Area
     FROM   Lz_t_Transit_Apply_Info
     WHERE  Apply_Id = Apply_Id_Value AND
            Paper_Class_Code <> '4'
     GROUP  BY Tree_Id
              ,Tree_Name
              ,Standard_Id
              ,Standard_Name
              ,Breed_Name);

Answer 3

那就是说对那个select语句来说是常量
那只要直接把两个变量名Apply_Id_Value,Collect_Value写在select后面就行了。SELECT Apply_Id_Value
       ,Collect_Value
           ,Tree_Id
           ,Tree_Name
           ,Standard_Id
           ,Standard_Name
           ,Breed_Name
           ,SUM(Nvl(Tree_Number_2, 0)) Tree_Number
           ,SUM(Nvl(Datum_Area_2, 0)) Datum_Area
     FROM   Lz_t_Transit_Apply_Info
     WHERE  Apply_Id = Apply_Id_Value AND
            Paper_Class_Code <> '4'
     GROUP  BY Tree_Id
              ,Tree_Name
              ,Standard_Id
              ,Standard_Name
              ,Breed_Name

Answer 4

出现如下错误：
PROCEDURE NPLY.SUMCOLLECT 编译错误错误: PL/SQL: ORA-02287: 此处不允许序号
行: 37
文本: (SELECT Lz_s_Transit_Apply_Collect_1.Nextval Collect_Id

Answer 5

Alkeen() ：
这样不行啊。出错。

Answer 6

INSERT INTO Lz_t_Transit_Apply_Collect
    (Collect_Id
    ,Apply_Id
    ,Tree_Id
    ,Tree_Name
    ,Standard_Id
    ,Standard_Name
    ,Breed_Name
    ,Tree_Number
    ,Datum_Area
    ,Specification_Name)
这有10各字段，可是下面的只有7个字段，就算再加上Apply_ID与Collect_ID那也只有9个字段！试问9个字段怎么付给10个字段？
SELECT Tree_Id
           ,Tree_Name
           ,Standard_Id
           ,Standard_Name
           ,Breed_Name
           ,SUM(Nvl(Tree_Number_2, 0)) Tree_Number
           ,SUM(Nvl(Datum_Area_2, 0)) Datum_Area
     FROM   Lz_t_Transit_Apply_Info
     WHERE  Apply_Id = Apply_Id_Value AND
            Paper_Class_Code <> '4'
     GROUP  BY Tree_Id
              ,Tree_Name
              ,Standard_Id
              ,Standard_Name
              ,Breed_Name);

Answer 7

对了，少打了一个，
select
，'CM' Specification_Name

Answer 8

Lz_s_Transit_Apply_Collect.NEXTVAL
其它的问题都解决了，现在就是上面这条语句，目的是取一个按原先的序号继续往下取，
在select 中如何解决。

Answer 9

问题解决了。
代码更改如下：
CREATE OR REPLACE PROCEDURE Lz_t_Transit_Sumcollect(Apply_Id_Value IN VARCHAR2) AS
BEGIN
  INSERT INTO Lz_t_Transit_Apply_Collect_1
    (Collect_Id
    ,Apply_Id
    ,Tree_Id
    ,Tree_Name
    ,Standard_Id
    ,Standard_Name
    ,Breed_Name
    ,Tree_Number
    ,Datum_Area
    ,Specification_Name)
    (SELECT Fun_Collectnextval('1') Collect_Id
           ,Apply_Id
           ,Tree_Id
           ,Tree_Name
           ,Standard_Id
           ,Standard_Name
           ,Breed_Name
           ,SUM(Nvl(Tree_Number_2, 0)) Tree_Number
           ,SUM(Nvl(Datum_Area_2, 0)) Datum_Area
           ,'CM' Specification_Name
     FROM   Lz_t_Transit_Apply_Info
     WHERE  Apply_Id = Apply_Id_Value AND
            Paper_Class_Code <> '4'
     GROUP  BY Tree_Id
              ,Tree_Name
              ,Standard_Id
              ,Standard_Name
              ,Breed_Name);
 END Lz_t_Transit_Sumcollect;
增加一个自定涵数：
CREATE OR REPLACE FUNCTION Fun_Collectnextval(Nextval_Sque VARCHAR2)
  RETURN VARCHAR2 AS
  p_Out VARCHAR2(10);
BEGIN
  IF Nextval_Sque = '1'
  THEN
    SELECT Lz_s_Transit_Apply_Collect_1.NEXTVAL
    INTO   p_Out
    FROM   Dual;
    RETURN p_Out;
  ELSIF Nextval_Sque = '2'
  THEN
    SELECT Lz_s_Transit_Apply_Collect_2.NEXTVAL
    INTO   p_Out
    FROM   Dual;
    RETURN p_Out;
  ELSE
    SELECT Lz_s_Transit_Apply_Collect.NEXTVAL
    INTO   p_Out
    FROM   Dual;
    RETURN p_Out;
  END IF;
END Fun_Collectnextval;

Answer 10

先看看有没有这个序列号Lz_s_Transit_Apply_Collect，
select Lz_s_Transit_Apply_Collect.NEXTVAL from dual如果有是不是在当前用户下，不在当前用户下，就给这个序列号建个同义词吧！