此存储过程为SQL转ORACLE,最后那一步始终报错,
期盼高手赐教CREATE OR REPLACE PROCEDURE UNFOLDBOM_EXPORT
(V_CODE VARCHAR2) AS
V_ITEMCODE VARCHAR2(40);
V_MANUALNO VARCHAR2(40);
V_STR_DATE DATE;
V_END_DATE DATE;
V_APPLYQTY NUMBER;
V_S NUMBER;
BEGIN
EXECUTE IMMEDIATE 'truncate table TEMPPROCESS';
delete FROM BMS_PROCESS_EXPORT where code=V_CODE;
SELECT manual_no,from_date,to_date into V_MANUALNO,V_STR_DATE,V_END_DATE FROM BMS_CANCEL WHERE code=V_CODE;
DECLARE CURSOR EXPORTCURSOR IS
select bms_export_d.item_code,sum(bms_export_d.apply_qty) from bms_export,bms_export_d
where bms_export.bms_export_id=bms_export_d.bms_export_id
and bms_export.trade_code in (select trade_code from bms_cancel_setting where cancel_column_code='EXPORT_BTO_USED_QTY')
and bms_export.im_status_code='P'
and bms_export.apply_date between V_STR_DATE and V_END_DATE
group by bms_export_d.item_code; BEGIN
OPEN EXPORTCURSOR; FETCH EXPORTCURSOR INTO V_ITEMCODE,V_APPLYQTY; WHILE (EXPORTCURSOR%FOUND) LOOP begin
V_S:=1;
INSERT into TEMPPROCESS SELECT ' ',V_CODE,V_ITEMCODE,V_APPLYQTY,V_ITEMCODE,V_APPLYQTY,ITEM_CODE_D ,SUM(GROSS_QTY*V_APPLYQTY),ITEM_CODE_D_CLASS,V_S,
V_ITEMCODE|| '◇'|| to_char(V_APPLYQTY) || '→' || ITEM_CODE_D|| '◇' ||to_char(SUM(GROSS_QTY*V_APPLYQTY)) ,
BEGIN_DATE,END_DATE ,ISBUY
FROM BMS_BOM_EXPORT
WHERE ITEM_CODE=V_ITEMCODE
AND TAG<>'X'
AND CODE=V_CODE
GROUP BY ITEM_CODE,ITEM_CODE_D,ITEM_CODE_D_CLASS,BEGIN_DATE,END_DATE,ISBUY;
WHILE (sql%rowcount)>0 LOOP
BEGIN
V_S:=V_S+1;
INSERT into TEMPPROCESS SELECT ' ', V_CODE,V_ITEMCODE,V_APPLYQTY,A.ITEM_CODE,B.GROSS_QTY,A.ITEM_CODE_D,A.GROSS_QTY*B.GROSS_QTY,A.ITEM_CODE_D_CLASS,V_S,
B.PROCESS|| '→' || A.ITEM_CODE_D|| '◇'||to_char(A.GROSS_QTY*B.GROSS_QTY),
A.BEGIN_DATE,A.END_DATE,A.ISBUY
FROM BMS_BOM_EXPORT A,
TEMPPROCESS B
WHERE A.ITEM_CODE=B.ITEM_CODE_D
AND B.STEP=V_S-1
AND A.CODE=V_CODE
AND B.ITEM_CODE_D_CLASS IN ('HALB','FERT')
AND B.ITEM_CODE=V_ITEMCODE
AND A.TAG<>'X' ;
END; END LOOP; FETCH EXPORTCURSOR INTO V_ITEMCODE,V_APPLYQTY;
END ;
END LOOP;
CLOSE EXPORTCURSOR;
END;
INSERT INTO BMS_PROCESS_EXPORT(CODE,MANUAL_NO,PROCESS,ROH,FERT,USE_QTY,EXPORT_QTY,CREATE_DATE,ISBUY,Begin_Date,End_Date)
SELECT V_CODE,V_MANUALNO,PROCESS ,T.ITEM_CODE_D ,T.ITEM_CODE ,T.GROSS_QTY,T.APPLY_QTY,V_END_DATE,nvl(ISBUY,'N'),V_STR_DATE ,V_END_DATE
FROM TEMPPROCESS T
WHERE ITEM_CODE_D_CLASS='ROH'; BEGIN
SELECT
A.ROH ,
SUM(A.USE_QTY) (此处报错,错误:PLS-00428: 在此 SELECT 语句中缺少 INTO 子句)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH; END;
END;
期盼高手赐教CREATE OR REPLACE PROCEDURE UNFOLDBOM_EXPORT
(V_CODE VARCHAR2) AS
V_ITEMCODE VARCHAR2(40);
V_MANUALNO VARCHAR2(40);
V_STR_DATE DATE;
V_END_DATE DATE;
V_APPLYQTY NUMBER;
V_S NUMBER;
BEGIN
EXECUTE IMMEDIATE 'truncate table TEMPPROCESS';
delete FROM BMS_PROCESS_EXPORT where code=V_CODE;
SELECT manual_no,from_date,to_date into V_MANUALNO,V_STR_DATE,V_END_DATE FROM BMS_CANCEL WHERE code=V_CODE;
DECLARE CURSOR EXPORTCURSOR IS
select bms_export_d.item_code,sum(bms_export_d.apply_qty) from bms_export,bms_export_d
where bms_export.bms_export_id=bms_export_d.bms_export_id
and bms_export.trade_code in (select trade_code from bms_cancel_setting where cancel_column_code='EXPORT_BTO_USED_QTY')
and bms_export.im_status_code='P'
and bms_export.apply_date between V_STR_DATE and V_END_DATE
group by bms_export_d.item_code; BEGIN
OPEN EXPORTCURSOR; FETCH EXPORTCURSOR INTO V_ITEMCODE,V_APPLYQTY; WHILE (EXPORTCURSOR%FOUND) LOOP begin
V_S:=1;
INSERT into TEMPPROCESS SELECT ' ',V_CODE,V_ITEMCODE,V_APPLYQTY,V_ITEMCODE,V_APPLYQTY,ITEM_CODE_D ,SUM(GROSS_QTY*V_APPLYQTY),ITEM_CODE_D_CLASS,V_S,
V_ITEMCODE|| '◇'|| to_char(V_APPLYQTY) || '→' || ITEM_CODE_D|| '◇' ||to_char(SUM(GROSS_QTY*V_APPLYQTY)) ,
BEGIN_DATE,END_DATE ,ISBUY
FROM BMS_BOM_EXPORT
WHERE ITEM_CODE=V_ITEMCODE
AND TAG<>'X'
AND CODE=V_CODE
GROUP BY ITEM_CODE,ITEM_CODE_D,ITEM_CODE_D_CLASS,BEGIN_DATE,END_DATE,ISBUY;
WHILE (sql%rowcount)>0 LOOP
BEGIN
V_S:=V_S+1;
INSERT into TEMPPROCESS SELECT ' ', V_CODE,V_ITEMCODE,V_APPLYQTY,A.ITEM_CODE,B.GROSS_QTY,A.ITEM_CODE_D,A.GROSS_QTY*B.GROSS_QTY,A.ITEM_CODE_D_CLASS,V_S,
B.PROCESS|| '→' || A.ITEM_CODE_D|| '◇'||to_char(A.GROSS_QTY*B.GROSS_QTY),
A.BEGIN_DATE,A.END_DATE,A.ISBUY
FROM BMS_BOM_EXPORT A,
TEMPPROCESS B
WHERE A.ITEM_CODE=B.ITEM_CODE_D
AND B.STEP=V_S-1
AND A.CODE=V_CODE
AND B.ITEM_CODE_D_CLASS IN ('HALB','FERT')
AND B.ITEM_CODE=V_ITEMCODE
AND A.TAG<>'X' ;
END; END LOOP; FETCH EXPORTCURSOR INTO V_ITEMCODE,V_APPLYQTY;
END ;
END LOOP;
CLOSE EXPORTCURSOR;
END;
INSERT INTO BMS_PROCESS_EXPORT(CODE,MANUAL_NO,PROCESS,ROH,FERT,USE_QTY,EXPORT_QTY,CREATE_DATE,ISBUY,Begin_Date,End_Date)
SELECT V_CODE,V_MANUALNO,PROCESS ,T.ITEM_CODE_D ,T.ITEM_CODE ,T.GROSS_QTY,T.APPLY_QTY,V_END_DATE,nvl(ISBUY,'N'),V_STR_DATE ,V_END_DATE
FROM TEMPPROCESS T
WHERE ITEM_CODE_D_CLASS='ROH'; BEGIN
SELECT
A.ROH ,
SUM(A.USE_QTY) (此处报错,错误:PLS-00428: 在此 SELECT 语句中缺少 INTO 子句)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH; END;
END;
SELECT
A.ROH ,
SUM(A.USE_QTY) into "自定义变量1", "自定义变量2"(此处报错,错误:PLS-00428: 在此 SELECT 语句中缺少 INTO 子句)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH; END;
要用一个游标来循环输出可以在开始定义一个游标变量
declare
type cursor_t ref cursor;
cursor_1 cursor_ty;
roh number;
sumqty number;
begin
open cursor_1 for SELECT
A.ROH ,
SUM(A.USE_QTY) sumqty(此处报错,错误:PLS-00428: 在此 SELECT 语句中缺少 INTO 子句)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH;
loop
fetch cusor_1 into roh,sumqty;
exit when cursor_1%notfound;
dbms_output.put_line(roh,sumqty);
end loop;
close cursor_1;
end;
SELECT
A.ROH ,
SUM(A.USE_QTY) (此处报错,错误:PLS-00428: 在此 SELECT 语句中缺少 INTO 子句)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH; 存储过程中不能直接执行sql语句
如果要用到select的结果就用游标,如果是update语句就用immediate
可以在存儲過程的參數列表中增加兩個out的變量來作爲返回,然後
SELECT
A.ROH ,
SUM(A.USE_QTY) into p_roh, p_sum_use_qty
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH;
BEGIN
SELECT
A.ROH ,
SUM(A.USE_QTY) (此处报错,错误:PLS-00428: 在此 SELECT 语句中缺少 INTO 子句)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH; END;
改为:
BEGIN
Excute immediate '
SELECT
A.ROH ,
SUM(A.USE_QTY)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH';
END;
v1 varchar2;
v2 number;
SELECT
A.ROH ,
SUM(A.USE_QTY) into
v1,v2
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE
GROUP BY A.ROH
ORDER BY A.ROH;
Excute immediate '
SELECT
A.ROH ,
SUM(A.USE_QTY)
FROM BMS_PROCESS_EXPORT A
WHERE A.CODE=V_CODE (这样子定义成动态SQL,V_CODE 这个变量就成了字段了啊)
GROUP BY A.ROH
ORDER BY A.ROH';
END;