| Back to logs list
74402 2009 年 01 月 30 日 22:21 Reading (loading. ..) Comments (6) Category: Personal Diary
5 of crack, 5 years, oracle, do not write down something,
gucci shoes, the hard disk a go, all the ashes. . . As asprotect and pe structure. . . I do not remember, really. . .
took about 3 months of function, assault, forced to learn the necessary means to enhance the efficiency of function in a very exaggerated example of using rollup, decode, case mix, a word only to generate, generated some comments The result is exactly the same, case the efficiency of the high-speed fast, optimized no less than 10 times, the end result was basically satisfied. . . The following is the complete code.
CREATE OR REPLACE PROCEDURE YLF.p_oldvehiclecheck (
v_empid VARCHAR2 DEFAULT 'FFFFFFFFFF',
v_tid VARCHAR2 DEFAULT 'LQ01',
v_staid VARCHAR2 DEFAULT '11 ',
v_instartdate ; VARCHAR2 DEFAULT '2006-05-25 ',
v_inenddate VARCHAR2 DEFAULT '2005-06-24',
; v_return OUT VARCHAR2
)
IS
tmpvar NUMBER;
v_reportid VARCHAR2 (20);
v_startdate ; VARCHAR2 (20);
v_enddate VARCHAR2 (20);
v_operationdate ; VARCHAR2 (20);
v_recordid VARCHAR2 (10);
v_stationname VARCHAR2 (50);
/****************************************** ************************************
NAME: ; P_OLDVEHICLECHECK xxxxxx audit situation tables
PURPOSE:
REVISIONS:
Ver Date Author Description
- ------- ---------- --------------- ------ ------------------------------
1.0 2007-1-16 1. Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: P_OLDVEHICLECHECK
Sysdate: 2007-1-16
Date and Time: 2007-1-16, 11:08:04, and 2007-1-16 11:08:04
Username: (set in TOAD Options, Procedure Editor)
Table Name: REPORT_OLDVEHICLEDISPOSAL (set in the Object ***********************************/
BEGIN
tmpvar: = 0;
v_reportid: = v_staid | | '-' | | SUBSTR (v_inenddate,
gucci heels, 1, 7);
v_startdate: = v_instartdate; ; - | | '00:00:00';
v_enddate: =
TO_CHAR ((TO_DATE (v_inenddate, 'YYYY-MM-DD') + 1), 'YYYY-MM-DD');
- | | '00:00:00';
v_operationdate: = TO_CHAR (SYSDATE, 'YYYY-MM- DD HH24: MI: SS ');
v_recordid: = 0;
SELECT REPLACE (NAME,
gucci sale,' xxxxxx ','')
; INTO v_stationname
FROM station
WHERE staid = v_staid;
- will report the statistics of the month has been set to remove the flag report
UPDATE report_oldvehiclecheck
SET cancelbytid = v_tid,
cancelbyempid = v_empid,
canceldate = v_operationdate
WHERE reportid = v_reportid AND cancelbytid IS NULL;
/ * FOR s IN (SELECT DECODE (GROUPING (a.crid),
1, 'Total',
(SELECT b.NAME
FROM checkreason b
WHERE b.crid = a.crid)
) NAME,
COUNT (*) c
FROM checkreason a
GROUP BY ROLLUP (crid)
; ORDER BY crid)
LOOP
v_recordid: = LPAD (v_recordid + 1, 2, '0 ');
INSERT INTO report_oldvehiclecheck
(reportid, kindname, recordid, startdate, enddate,
empid, tid, operationdate , staid, stationname,
realtimedate, localcount, localtonnage, ecdemiccount,
ecdemictonnage , totalcount, totaltonnage
)
VALUES (v_reportid, s.NAME, v_recordid, v_startdate, v_enddate,
v_empid, v_tid, v_operationdate, v_staid,
cheap gucci heels, v_stationname,
; TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 0, 0, 0,
0, 0 , 0
);
END LOOP;
IF v_staid '01 '
THEN
FOR s IN
(SELECT DECODE (GROUPING (reason), 1, 'Total', reason) reason,
SUM
(CASE
; WHEN SUBSTR (registrationnumber,
cheap gucci shoes, 1, 2) IN
('01 ', '02', '03 ',' 50 ', '51', '52 ')
THEN 1
; ELSE 0
END
) AS vlc,
; SUM
; (CASE
WHEN SUBSTR (registrationnumber, 1, 2) IN
; ; ('01 ', '02', '03 ', '50', '51 ', '52')
; THEN tonnage
ELSE 0
; END
;) AS vlt,
SUM
(CASE
; ; WHEN SUBSTR (registrationnumber, 1, 2) NOT IN
; ('01 ', '02 ', '03', '50 ', '51', '52 ')
; THEN 1
ELSE 0
; END
;) AS vec,
SUM
(CASE
; WHEN SUBSTR (registrationnumber, 1, 2) NOT IN
; ; ('01 ', '02', '03 ', '50', '51 ', '52')
; THEN tonnage
ELSE 0
; END
;) AS vet,
COUNT (*) AS vac, SUM (tonnage) AS vat
FROM vehiclecheck
WHERE detaindate BETWEEN v_instartdate AND v_inenddate
AND cancelbytid IS NULL
; AND ctid 'FFFFFFFFFF'
AND tid IN (SELECT tid
FROM terminal
; WHERE staid = v_staid)
GROUP BY ROLLUP (reason ))
LOOP
UPDATE report_oldvehiclecheck
SET localcount = s.vlc,
localtonnage = s.vlt,
ecdemiccount = s.vec,
ecdemictonnage = s.vet,
; totalcount = s.vac,
totaltonnage = s.vat
WHERE reportid = v_reportid
AND cancelbytid IS NULL
AND kindname = s.reason;
END LOOP;
ELSE
FOR s IN
(SELECT DECODE (GROUPING (reason), 1, 'together meter ', reason) reason,
SUM
(CASE
; WHEN SUBSTR (registrationnumber, 1, 2) IN
('01 ' , '02 ', '03', '50 ', '51', '52 ')
THEN 1
; ELSE 0
END
) AS vlc,
; SUM
; (CASE
WHEN SUBSTR (registrationnumber, 1, 2) IN
; ; ('01 ', '02', '03 ', '50', '51 ', '52')
; THEN tonnage
ELSE 0
; END
;) AS vlt,
SUM
(CASE
; WHEN SUBSTR (registrationnumber, 1, 2) NOT IN
; ; ('01 ', '02', '03 ', '50', '51 ', '52')
; THEN 1
ELSE 0
END
;) AS vec,
SUM
(CASE
; WHEN SUBSTR (registrationnumber, 1, 2) NOT IN
; ('01 ', '02', '03 ', '50 ',
gucci sunglasses 2011, '51', '52 ')
; THEN tonnage
ELSE 0
; END
;) AS vet,
COUNT (*) AS vac, SUM (tonnage) AS vat
FROM vehiclecheck
WHERE detaindate BETWEEN v_instartdate AND v_inenddate
AND cancelbytid IS NULL
; AND ctid 'FFFFFFFFFF'
GROUP BY ROLLUP (reason))
LOOP
UPDATE report_oldvehiclecheck
SET localcount = s.vlc,
localtonnage = s.vlt,
ecdemiccount = s . vec,
ecdemictonnage = s.vet,
; totalcount = s.vac,
totaltonnage = s.vat
WHERE reportid = v_reportid
AND cancelbytid IS NULL
AND kindname = s.reason;
END LOOP;
END IF;
* /
IF v_staid '01 '
THEN
INSERT INTO report_oldvehiclecheck
; SELECT ; ; / * + CPU_COSTING * /
v_reportid,
; DECODE (GROUPING (crid), 1, '09 ', crid) crid,
v_staid ,
v_stationname,
; v_tid,
v_empid,
; DECODE (GROUPING (crid),
; 1, 'together ; dollars',
; (SELECT NAME
FROM checkreason
WHERE crid = a.crid))
kindname,
; SUM (localcount),
; SUM (localtonnage),
; SUM (ecdemiccount),
SUM (ecdemictonnage),
SUM (totalcount ),
SUM (totaltonnage),
v_operationdate,
NULL,
; NULL,
NULL,
; v_startdate,
v_enddate,
; TO_CHAR (SYSDATE, 'YYYY-MM-DD')
FROM (SELECT DECODE (GROUPING (reason),
; ; 1, '99 ',
(SELECT crid
; FROM checkreason
; ; WHERE NAME = v.reason))
; ; crid,
SUM (CASE
WHEN SUBSTR (registrationnumber, 1, 2) IN
;
Hybrid Mode
