存储过程中的insert into的使用
有如下的存储过程
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中啊？
问题点数：50、回复次数：12Top
1 楼Alkeen（）回复于 2005-04-19 09:37:55 得分 0
不太明白楼主的想法
我想把Collect_ID，Apply_ID直接放到select后面和group by后面就应该可以了啦。Top
2 楼yesoon（）回复于 2005-04-19 09:40:10 得分 0
意思是insert into中Collect_ID，Apply_ID的值要从变量Apply_Id_Value 与变量Collect_Value中取值，其它的值从select中取值。Top
3 楼yesoon（）回复于 2005-04-19 09:56:30 得分 0
在线等，急啊。Top
4 楼czrain（蝴蝶剑）回复于 2005-04-19 10:15:48 得分 20
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);
Top
5 楼Alkeen（）回复于 2005-04-19 10:22:39 得分 20
那就是说对那个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_NameTop
6 楼yesoon（）回复于 2005-04-19 10:23:29 得分 0
出现如下错误：
PROCEDURE NPLY.SUMCOLLECT 编译错误
错误: PL/SQL: ORA-02287: 此处不允许序号
行: 37
文本: (SELECT Lz_s_Transit_Apply_Collect_1.Nextval Collect_IdTop
7 楼yesoon（）回复于 2005-04-19 10:27:07 得分 0
Alkeen() ：
这样不行啊。出错。Top
8 楼fanjim0211（fjm）回复于 2005-04-19 10:39:10 得分 10
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);
Top
9 楼yesoon（）回复于 2005-04-19 10:45:13 得分 0
对了，少打了一个，
select
，'CM' Specification_NameTop
10 楼yesoon（）回复于 2005-04-19 11:39:11 得分 0
Lz_s_Transit_Apply_Collect.NEXTVAL
其它的问题都解决了，现在就是上面这条语句，目的是取一个按原先的序号继续往下取，
在select 中如何解决。Top
11 楼yesoon（）回复于 2005-04-19 15:12:25 得分 0
问题解决了。
代码更改如下：
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;
Top
12 楼czrain（蝴蝶剑）回复于 2005-04-19 15:19:51 得分 0
先看看有没有这个序列号Lz_s_Transit_Apply_Collect，
select Lz_s_Transit_Apply_Collect.NEXTVAL from dual
如果有是不是在当前用户下，不在当前用户下，就给这个序列号建个同义词吧！Top
