- 论坛徽章:
- 0
|
--|| call queryAttemper (400,'',0,0,0)
在v_function_id 等于100的时候是没有问题的 但等于400和401时就不行
CREATE PROCEDURE queryAttemper (v_function_id SMALLINT,
v_attemper_no VARCHAR(10),
v_begin_date INTEGER,
v_end_date INTEGER,
v_pay_type INTEGER )
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE v_PROCEDURE_ID INTEGER;
DECLARE v_query_condition VARCHAR(4000);
DECLARE execStr VARCHAR(4000);
DECLARE l_rowcount INTEGER;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET l_error = '00000';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l_error = SQLSTATE;
IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN
RESIGNAL;
END IF;
END;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#query400"(
serial_no INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL ,
route_no INTEGER,
productCode CHAR( ,
productid INTEGER,
name VARCHAR(20),
unit VARCHAR(6),
quantity DECIMAL(19,1),
price DECIMAL(19,2),
amount DECIMAL(19,4)
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#query401"(
customerid INTEGER,
name VARCHAR(100),
address VARCHAR(100),
tele VARCHAR(20),
orderID INTEGER,
routenNO INTEGER,
customerSeq INTEGER,
PayName VARCHAR(10),
payStatus VARCHAR(10),
quantity DECIMAL(19,6),
amount DECIMAL(19,2),
flag INTEGER
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#query404"(
routeno INTEGER,
productid INTEGER,
productname VARCHAR(30),
productcode VARCHAR(30),
unit VARCHAR(6),
Quantity DECIMAL(19,2),
Quantity_50 DECIMAL(19,2)
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#queryAttemper405"(
ser_no INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL ,
customerid INTEGER,
name VARCHAR(100),
orderID VARCHAR(20),
routeNO INTEGER,
customerSeq INTEGER,
productId INTEGER,
productname VARCHAR(50),
quantity DECIMAL(19,1),
price DECIMAL(19,4),
amount DECIMAL(19,2),
flag INTEGER,
detail_flag INTEGER
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#queryAttemper406"(
routeNo INTEGER,
Quantity DECIMAL(19,1),
amount DECIMAL(19,2),
bankamount DECIMAL(19,2),
customercount INTEGER,
flag INTEGER
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#queryAttemper407"(
ser_no INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL ,
customerid INTEGER,
name VARCHAR(100),
orderID VARCHAR(20),
routeNO INTEGER,
customerSeq INTEGER,
productId INTEGER,
productname VARCHAR(50),
quantity DECIMAL(19,1),
price DECIMAL(19,4),
amount DECIMAL(19,2),
flag INTEGER,
detail_flag INTEGER
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#queryAttemperRoute"(
way_id INTEGER,
routeno VARCHAR(20),
CustomerCount INTEGER,
OrderDate INTEGER,
Quantity DECIMAL(19,2),
Amount DECIMAL(19,2),
BankAmount DECIMAL(19,2),
deliverName VARCHAR(20),
carno VARCHAR(20),
flag INTEGER,
cashAmount DECIMAL(19,2)
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#queryAttemper100"(
attemper_no INTEGER,
attemperDate INTEGER,
attemperTime INTEGER,
length real,
LoadRate real,
DriveTime INTEGER,
estimateTme INTEGER,
OPDate INTEGER,
CustomerCount INTEGER,
OrderDate INTEGER,
Quantity DECIMAL(19,6),
Amount DECIMAL(19,6),
BankAmount DECIMAL(19,6),
cashAmount DECIMAL(19,6),
opname CHARACTER (10),
carno CHARACTER (10),
routeNo INTEGER
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
BEGIN
--| 定义游标
DECLARE temp_cursor400 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT *
FROM SESSION."#query400";
DECLARE temp_cursor401 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT name,
address, tele, orderID, PayName,
payStatus, quantity, amount,
customerid, routenNO,customerSeq
FROM SESSION."#query401"
ORDER BY flag DESC,routenNO,customerSeq;
DECLARE temp_cursor404 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT *
FROM SESSION."#query404";
DECLARE temp_cursor3 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT name,
orderID,
productname,
quantity,
price,
amount,
customerid,
routeNO
FROM SESSION."#queryAttemper405"
ORDER BY flag,
customerid,
detail_flag,
customerSeq;
DECLARE temp_cursor4 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT routeno,
Amount,
deliverName,
BankAmount,
cashAmount,
OrderDate
FROM SESSION."#queryAttemperRoute"
ORDER BY flag,
way_id;
DECLARE temp_cursor5 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT name,
orderID,
productname,
quantity,
price,
amount,
customerid,
routeNO
FROM SESSION."#queryAttemper407"
ORDER BY flag,
customerid,
detail_flag,
customerSeq;
DECLARE temp_cursor6 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT*
FROM SESSION."#queryAttemper100";
SET v_PROCEDURE_ID = -70460000;
SET v_query_condition = ' ';
SET l_error = '00000';
IF (v_function_id NOT IN (100,400,401,404,405,406,407)) THEN
RETURN v_PROCEDURE_ID - 303;
END IF;
IF v_function_id = 100 THEN
IF COALESCE(v_attemper_no, '') <> '' THEN
SET v_query_condition = v_query_condition || ' AND routeNo=' || LTRIM(v_attemper_no);
END IF;
IF COALESCE(v_begin_date, 0) <> 0 THEN
SET v_query_condition = v_query_condition || ' AND Orderdate=' || RTRIM(CHAR(RTRIM(RTRIM(CHAR(v_begin_date)))));
END IF;
SET execStr = ' INSERT INTO SESSION."#queryAttemper100"
SELECT routeNo as attemper_no, attemperDate, attemperTime, length , LoadRate , DriveTime , estimateTime ,OPDate ,
CustomerCount, OrderDate , Quantity , Amount, BankAmount, Amount-BankAmount as cashAmount , opname, carno ,routeNo
FROM F_DeliverRoute where 1=1 ' || v_query_condition;
EXECUTE IMMEDIATE execStr;
OPEN temp_cursor6;
IF (l_rowcount < 1) THEN
RETURN v_PROCEDURE_ID - 301;
END IF;
END IF;
IF v_function_id = 400 THEN
IF COALESCE(v_attemper_no, '') <> '' THEN
SET v_query_condition = v_query_condition || ' AND a.routeNo=' || RTRIM(LTRIM(v_attemper_no));
END IF;
IF COALESCE(v_begin_date, 0) <> 0 THEN
SET v_query_condition = v_query_condition || ' AND a.Orderdate=' || RTRIM(CHAR(RTRIM(CHAR(v_begin_date))));
END IF;
SET l_error = '00000';
SET execStr = ' INSERT INTO SESSION."#query400"
SELECT ' ||v_attemper_no ||',b.productCode,b.productid,b.name,''条'',sum(d.quantity) ,avg(d.price) ,sum(d.amount)
FROM F_Way a, F_ProductInfo b, D_Order c,D_OrderDetail d
WHERE c.orderid=d.orderid and b.productid=d.productid and a.customerid=c.customerid ' || v_query_condition || 'group by b.productid,b.name , b.productCode
order by b.productid,b.name , b.productCode';
EXECUTE IMMEDIATE execStr;
OPEN temp_cursor400;
IF l_error <> '00000' THEN
--|| RETURN v_PROCEDURE_ID - 150;
RETURN 400;
END IF;
END IF;
IF v_function_id = 401 THEN
IF COALESCE(v_attemper_no, '') <> '' THEN
SET v_query_condition = v_query_condition || ' AND a.routeNo=' || RTRIM(LTRIM(v_attemper_no));
END IF;
IF COALESCE(v_begin_date, 0) <> 0 THEN
SET v_query_condition = v_query_condition || ' AND a.Orderdate=' || RTRIM(CHAR(RTRIM(RTRIM(CHAR(v_begin_date)))));
END IF;
IF COALESCE(v_pay_type, 0) <> 0 THEN
SET v_query_condition = v_query_condition || ' AND payStatus=3 AND c.payMode=' || RTRIM(CAST (RTRIM(CHAR(v_pay_type - 1)) AS VARCHAR(10)));
END IF;
SET l_error = '00000';
SET execStr = ' INSERT INTO SESSION."#query401"
SELECT a.customerid,c.customername ,c.address ,c.tele,c.orderID, a.routeno,a.customerseq,
case c.payMode when 0 then ''现金'' else ''金融卡'' end ,
case c.payStatus when 1 then ''未结清'' when 2 then ''扣款冻结'' when 3 then ''已结清'' else ''未结清'' end ,
c.totalquantity ,c.totalamount ,0
FROM F_Way a, D_Order c
WHERE a.customerid=c.customerid and a.orderdate=c.orderdateINT ' || v_query_condition || ' ORDER BY a.routeno,a.customerseq';
EXECUTE IMMEDIATE execStr;
open temp_cursor401;
IF l_error <> '00000' THEN
RETURN v_PROCEDURE_ID - 152;
END IF;
SET l_error = '00000';
INSERT INTO SESSION."#query401" SELECT 0,
'--合计',
'--合计',
'',
0,
routenNO,
0,
'',
'',
ROUND(SUM(quantity), 6),
ROUND(SUM(amount), 2),
1
FROM SESSION."#query401"
GROUP BY routenNO;
COMMIT;
IF l_error <> '00000' THEN
RETURN v_PROCEDURE_ID - 153;
END IF;
END IF;
IF v_function_id = 404 THEN
IF COALESCE(v_attemper_no, '') <> '' THEN
SET v_query_condition = v_query_condition || ' AND a.routeNo=' || LTRIM(v_attemper_no);
END IF;
IF COALESCE(v_begin_date, 0) <> 0 THEN
SET v_query_condition = v_query_condition || ' AND a.Orderdate=' || RTRIM(CHAR(RTRIM(RTRIM(CHAR(v_begin_date)))));
END IF;
SET l_error = '00000';
SET execStr = ' INSERT INTO SESSION."#query404"
SELECT a.routeno,d.productid,b.name,b.productcode,''条'' ,sum(d.Quantity) ,sum(d.Quantity)/50
FROM F_Way a ,productinfo b,D_Order c ,D_OrderDetail d
WHERE a.customerid=c.customerid and c.orderid=d.orderid and b.b.productid=d.productid ' || v_query_condition || '
GROUP BY a.routeno ,d.productid,d.ProductUnitID ,b.name,b.productcode
ORDER BY a.routeno ,d.productid,d.ProductUnitID ';
EXECUTE IMMEDIATE execStr;
OPEN temp_cursor404;
IF l_error <> '00000' THEN
RETURN v_PROCEDURE_ID - 154;
END IF;
END IF;
RETURN 100;
END;
END |
|