存储过程中的insert into的使用

[yesoon]

有如下的存储过程
CREATE OR REPLACE PROCEDURE Sumcollect(Apply_Id_Value VARCHAR2) IS
Collect_Value VARCHAR2(10);
BEGIN
SELECT Lz_s_Transit_Apply_Collect.NEXTVAL
INTO Collect_Value
FROM Dual;
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 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);
其中里面的Apply_ID与Collect_ID我如何加入insert into中啊？

[yesoon]

问题解决了。
代码更改如下：
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;

[czrain]

先看看有没有这个序列号Lz_s_Transit_Apply_Collect，
select Lz_s_Transit_Apply_Collect.NEXTVAL from dual

如果有是不是在当前用户下，不在当前用户下，就给这个序列号建个同义词吧！

[Alkeen]

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

[yesoon]

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

[yesoon]

在线等，急啊。

[czrain]

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);

[Alkeen]

那就是说对那个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

[yesoon]

出现如下错误：
PROCEDURE NPLY.SUMCOLLECT 编译错误

错误: PL/SQL: ORA-02287: 此处不允许序号
行: 37
文本: (SELECT Lz_s_Transit_Apply_Collect_1.Nextval Collect_Id

[yesoon]

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

[fanjim0211]

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);

[yesoon]

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

[yesoon]

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