CREATE PROCEDURE SP_AUDITDEPARTMENT(
I_ACCSUITID IN NUMBER,
I_BILLID IN CHAR
)
AS
CURSOR CUR_DEPARTMENTINCOME IS
SELECT
A.F_DEPARTMENTID,
B.F_DEPARTMENT,
B.F_ISINCOME,
B.F_ISACHIEVEMENT,
SUM( A.F_QUANTITY ) AS F_QUANTITY,
SUM( A.F_COSTPRICE * A.F_QUANTITY ) AS F_COSTAMOUNT,
SUM( A.F_DISCOUNTAMT ) AS F_DISCOUNTAMT,
SUM( A.F_EXTRAAMOUNT ) AS F_EXTRAAMOUNT,
SUM( A.F_SUBTOTAL ) AS F_SUBTOTAL
FROM
TPOSBILLCONSUME A,
TPOSDEPARTMENT B
WHERE
( A.F_BILLID=I_BILLID )AND
( A.F_DEPARTMENTID=B.F_DEPARTMENTID )
GROUP BY
A.F_DEPARTMENTID,
B.F_DEPARTMENT,
B.F_ISINCOME,
B.F_ISACHIEVEMENT;
R_PARENTDEPARTID CHAR(6);
R_PARENTDEPART CHAR(32);
BEGIN
R_PARENTDEPARTID:=getpartid(I_BILLID);
R_PARENTDEPART:=getdeptart(I_BILLID);
FOR V_ROW IN CUR_DEPARTMENTINCOME LOOP
INSERT INTO TPOSBILLVOUCHER( F_BILLID, F_TYPE, F_ACCITEMID, F_ACCITEM, F_CLASSID, F_CLASS, F_QUANTITY, F_AMOUNT,
F_RMBAMOUNT, F_ISINCOME, F_ISACHIEVEMENT, F_DISCOUNTAMT, F_EXTRAAMOUNT, F_COSTAMOUNT )
VALUES( I_BILLID, '部门', V_ROW.F_DEPARTMENTID, V_ROW.F_DEPARTMENT, R_PARENTDEPARTID, R_PARENTDEPART, V_ROW.F_QUANTITY, V_ROW.F_SUBTOTAL,
V_ROW.F_SUBTOTAL, V_ROW.F_ISINCOME, V_ROW.F_ISACHIEVEMENT, V_ROW.F_DISCOUNTAMT, V_ROW.F_EXTRAAMOUNT, V_ROW.F_COSTAMOUNT );
--求助: SQL Server 有没有 V_ROW.这种用法, 如果用变量的话 需要定义十个变量,对十个变量赋值.比较麻烦.
-- 有时候如果游标用的是select * from ... 的话.可能有二三十个变量...
END LOOP;
END;
/
I_ACCSUITID IN NUMBER,
I_BILLID IN CHAR
)
AS
CURSOR CUR_DEPARTMENTINCOME IS
SELECT
A.F_DEPARTMENTID,
B.F_DEPARTMENT,
B.F_ISINCOME,
B.F_ISACHIEVEMENT,
SUM( A.F_QUANTITY ) AS F_QUANTITY,
SUM( A.F_COSTPRICE * A.F_QUANTITY ) AS F_COSTAMOUNT,
SUM( A.F_DISCOUNTAMT ) AS F_DISCOUNTAMT,
SUM( A.F_EXTRAAMOUNT ) AS F_EXTRAAMOUNT,
SUM( A.F_SUBTOTAL ) AS F_SUBTOTAL
FROM
TPOSBILLCONSUME A,
TPOSDEPARTMENT B
WHERE
( A.F_BILLID=I_BILLID )AND
( A.F_DEPARTMENTID=B.F_DEPARTMENTID )
GROUP BY
A.F_DEPARTMENTID,
B.F_DEPARTMENT,
B.F_ISINCOME,
B.F_ISACHIEVEMENT;
R_PARENTDEPARTID CHAR(6);
R_PARENTDEPART CHAR(32);
BEGIN
R_PARENTDEPARTID:=getpartid(I_BILLID);
R_PARENTDEPART:=getdeptart(I_BILLID);
FOR V_ROW IN CUR_DEPARTMENTINCOME LOOP
INSERT INTO TPOSBILLVOUCHER( F_BILLID, F_TYPE, F_ACCITEMID, F_ACCITEM, F_CLASSID, F_CLASS, F_QUANTITY, F_AMOUNT,
F_RMBAMOUNT, F_ISINCOME, F_ISACHIEVEMENT, F_DISCOUNTAMT, F_EXTRAAMOUNT, F_COSTAMOUNT )
VALUES( I_BILLID, '部门', V_ROW.F_DEPARTMENTID, V_ROW.F_DEPARTMENT, R_PARENTDEPARTID, R_PARENTDEPART, V_ROW.F_QUANTITY, V_ROW.F_SUBTOTAL,
V_ROW.F_SUBTOTAL, V_ROW.F_ISINCOME, V_ROW.F_ISACHIEVEMENT, V_ROW.F_DISCOUNTAMT, V_ROW.F_EXTRAAMOUNT, V_ROW.F_COSTAMOUNT );
--求助: SQL Server 有没有 V_ROW.这种用法, 如果用变量的话 需要定义十个变量,对十个变量赋值.比较麻烦.
-- 有时候如果游标用的是select * from ... 的话.可能有二三十个变量...
END LOOP;
END;
/
要么改用其他方法