本人以前是使用SQL数据库,现在因工作的需要。突然改用oracle,用PL/SQL写代码,因为惯性和对oracle的不熟悉.下面的包代码的写法中(1是包头,2是包体)仍然脱离不了SQL的风格。因此,无法显示出想实现的结果。请教各位,在下面的代码中有何错误之处,应如何更证。
1、
FUNCTION Get_cpwlxhqd_Note(
part_no_ In Varchar2
) RETURN number;2、
FUNCTION Get_cpwlxhqd_Note(
part_no_ In Varchar2
) RETURN number
Is
temp_ number;
Begin
declare AALEVEL_ number;temp_ number;
begin
select DOP_STRUCTURE_LEVEL,QTY_PER_ASSEMBLY into AALEVEL_,temp_ from X_CPWLXHQD where PART_NO='PART_NO_'
end; while AALEVEL_<>0
begin
select temp_=temp_* QTY_PER_ASSEMBLY from X_CPWLXHQD where DOP_ORDER_ID=(select PARENT_DOP_ORDER_ID where DOP_STRUCTURE_LEVEL=AALEVEL_)
set AALEVEL_=AALEVEL_-1
end; Return temp_;
End Get_cpwlxhqd_Note;
1、
FUNCTION Get_cpwlxhqd_Note(
part_no_ In Varchar2
) RETURN number;2、
FUNCTION Get_cpwlxhqd_Note(
part_no_ In Varchar2
) RETURN number
Is
temp_ number;
Begin
declare AALEVEL_ number;temp_ number;
begin
select DOP_STRUCTURE_LEVEL,QTY_PER_ASSEMBLY into AALEVEL_,temp_ from X_CPWLXHQD where PART_NO='PART_NO_'
end; while AALEVEL_<>0
begin
select temp_=temp_* QTY_PER_ASSEMBLY from X_CPWLXHQD where DOP_ORDER_ID=(select PARENT_DOP_ORDER_ID where DOP_STRUCTURE_LEVEL=AALEVEL_)
set AALEVEL_=AALEVEL_-1
end; Return temp_;
End Get_cpwlxhqd_Note;
into temp_
from X_CPWLXHQD where DOP_ORDER_ID=
(select PARENT_DOP_ORDER_ID where DOP_STRUCTURE_LEVEL=AALEVEL_) ; AALEVEL_:=AALEVEL_-1 ;
CREATE OR REPLACE PROCEDURE GET_CPWLXHQD_NOTE(IN_PART_NO IN X_CPWLXHQD.PART_NO%TYPE,
OUT_TEMP OUT X_CPWLXHQD.QTY_PER_ASSEMBLY%TYPE) IS
V_AALEVEL X_CPWLXHQD.DOP_STRUCTURE_LEVEL%TYPE;
V_TEMP_QTY X_CPWLXHQD.QTY_PER_ASSEMBLY%TYPE;
BEGIN SELECT DOP_STRUCTURE_LEVEL, QTY_PER_ASSEMBLY
INTO V_AALEVEL, OUT_TEMP
FROM X_CPWLXHQD
WHERE PART_NO = IN_PART_NO; WHILE V_AALEVEL <>0 LOOP SELECT QTY_PER_ASSEMBLY
INTO V_TEMP_QTY
FROM X_CPWLXHQD
WHERE DOP_ORDER_ID =
(SELECT PARENT_DOP_ORDER_ID WHERE DOP_STRUCTURE_LEVEL = V_AALEVEL);
OUT_TEMP := OUT_TEMP * V_TEMP_QTY;
V_AALEVEL := V_AALEVEL - 1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
END GET_CPWLXHQD_NOTE;
1、
FUNCTION Get_cpwlxhqd_Note(
part_no_ In Varchar2
) RETURN number; 2、
FUNCTION Get_cpwlxhqd_Note(
part_no_ In Varchar2
) RETURN number
Is
AALEVEL_ number;
temp_ number;
begin
select DOP_STRUCTURE_LEVEL,QTY_PER_ASSEMBLY into AALEVEL_,temp_ from X_CPWLXHQD where PART_NO='PART_NO_' ; while AALEVEL_ <>0
loop
select temp_* QTY_PER_ASSEMBLY into temp_ from X_CPWLXHQD where DOP_ORDER_ID=(select PARENT_DOP_ORDER_ID where DOP_STRUCTURE_LEVEL=AALEVEL_);
AALEVEL_:=AALEVEL_-1 ;
end loop; Return temp_;
End Get_cpwlxhqd_Note;