另外下面参数未用?V_BDATE DATE, V_EDATE DATE
条件也需要修改
相关贴子http://community.csdn.net/Expert/topic/3584/3584714.xml?temp=.6848566创建存储过程返回动态sql语句
CREATE OR REPLACE PROCEDURE P_TEST_TOP(V_BDATE DATE,
V_EDATE DATE,
v_sql OUT VARCHAR2) AS
--此过程用于返回结果
TYPE V_CURSOR IS REF CURSOR;
MYCUR V_CURSOR;
V_TEMP VARCHAR2(20);
V_UNION VARCHAR2(20);
V_TEMP2 VARCHAR2(1000);
V_TEMP3 VARCHAR2(1000);
V_TEMP4 VARCHAR2(1000);BEGIN
v_sql := ' SELECT 0 sign,''周'' ';
V_TEMP2 := ' SELECT 1 sign,''准时'' ';
V_TEMP3 := ' SELECT 2 sign,''晚到'' ';
V_TEMP4 := ' SELECT 3 sign,''总数'' ';
V_UNION := ' union ';
OPEN MYCUR FOR
SELECT DISTINCT CEIL(TO_NUMBER(TO_CHAR(SUB_DATE, 'DDD')) / 7) FROM T1;
LOOP
FETCH MYCUR
INTO V_TEMP;
EXIT WHEN MYCUR%NOTFOUND;
V_SQL := V_SQL || ' ,' || V_TEMP;
V_TEMP2 := V_TEMP2 || ' ,f_test(' || V_TEMP || ',1)';
V_TEMP3 := V_TEMP3 || ' ,f_test(' || V_TEMP || ',2)';
V_TEMP4 := V_TEMP4 || ' ,f_test(' || V_TEMP || ',3)';
END LOOP;
V_SQL := V_SQL || ' from t1';
V_TEMP2 := V_TEMP2 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP3 := V_TEMP3 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP4 := V_TEMP4 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
CLOSE MYCUR;
V_SQL := V_SQL || V_UNION || V_TEMP2 || V_UNION || V_TEMP3 || V_UNION ||
V_TEMP4 || 'order by 1';END P_TEST_TOP;
条件也需要修改
相关贴子http://community.csdn.net/Expert/topic/3584/3584714.xml?temp=.6848566创建存储过程返回动态sql语句
CREATE OR REPLACE PROCEDURE P_TEST_TOP(V_BDATE DATE,
V_EDATE DATE,
v_sql OUT VARCHAR2) AS
--此过程用于返回结果
TYPE V_CURSOR IS REF CURSOR;
MYCUR V_CURSOR;
V_TEMP VARCHAR2(20);
V_UNION VARCHAR2(20);
V_TEMP2 VARCHAR2(1000);
V_TEMP3 VARCHAR2(1000);
V_TEMP4 VARCHAR2(1000);BEGIN
v_sql := ' SELECT 0 sign,''周'' ';
V_TEMP2 := ' SELECT 1 sign,''准时'' ';
V_TEMP3 := ' SELECT 2 sign,''晚到'' ';
V_TEMP4 := ' SELECT 3 sign,''总数'' ';
V_UNION := ' union ';
OPEN MYCUR FOR
SELECT DISTINCT CEIL(TO_NUMBER(TO_CHAR(SUB_DATE, 'DDD')) / 7) FROM T1;
LOOP
FETCH MYCUR
INTO V_TEMP;
EXIT WHEN MYCUR%NOTFOUND;
V_SQL := V_SQL || ' ,' || V_TEMP;
V_TEMP2 := V_TEMP2 || ' ,f_test(' || V_TEMP || ',1)';
V_TEMP3 := V_TEMP3 || ' ,f_test(' || V_TEMP || ',2)';
V_TEMP4 := V_TEMP4 || ' ,f_test(' || V_TEMP || ',3)';
END LOOP;
V_SQL := V_SQL || ' from t1';
V_TEMP2 := V_TEMP2 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP3 := V_TEMP3 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP4 := V_TEMP4 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
CLOSE MYCUR;
V_SQL := V_SQL || V_UNION || V_TEMP2 || V_UNION || V_TEMP3 || V_UNION ||
V_TEMP4 || 'order by 1';END P_TEST_TOP;
晚到达时间记录也有两种情况 if pro_type='c' 时 使用
(arr_date[转成小时]-subdate[转成小时] )>t2.req_hour T1.ARR_CITY = T2.ARR_CITY 或者
if pro_type='M' 时
b_sub_date > b_arr_date
西西基本解决了问题
CREATE OR REPLACE PROCEDURE P_TEST_TOP(V_BDATE DATE,
V_EDATE DATE,
v_sql OUT VARCHAR2) 参数未用啊
我又迷迷糊糊了,
这两个有什么不同?
晚到达时间记录也有两种情况 if pro_type='c' 时 使用
(arr_date[转成小时]-subdate[转成小时] )>t2.req_hour T1.ARR_CITY = T2.ARR_CITY 或者
if pro_type='M' 时
b_sub_date > b_arr_date
SUM(DECODE(SIGN((T1.ARR_DATE - T1.SUB_DATE) * 24 - T2.REQ_HOUR),
-1,
0,
1))
使用这个
b_sub_date <= b_arr_date关键是如何判断,使加参数或者 if 什么的
SELECT SUM(DECODE(SIGN((T1.ARR_DATE - T1.SUB_DATE) * 24 - T2.REQ_HOUR),
-1,
0,
1))
INTO V_COUNTS
FROM T1, T2
WHERE T1.ARR_CITY = T2.ARR_CITY
AND CEIL(TO_NUMBER(TO_CHAR(T1.SUB_DATE, 'DDD')) / 7) = V_WEEK;
sum()是你查询的内容,where后面才是条件啊
(T1.ARR_DATE - T1.SUB_DATE) * 24 - T2.REQ_HOUR
是不是就换成t1.b_sub_date <= t2.b_arr_date怎么判断?
V_EDATE DATE,
V_TYPE VARCHAR2,
V_SQL OUT VARCHAR2) AS
/*此过程用于返回结果
参数描述:
V_BDATE:开始日期
V_EDATE:结束日期 这两个日期也就是你要查的2004-08-20 到 2004-10-08 这两个参数
V_TYPE :C.表示ON TIME 的条件
ACT_ATAMIT - ACT_INSERTDATE(小时默认为23:00) < = LEAD TIME LEAD TIME
在T2ALEADTIME中查出 条件为:
ACT_SHIPFROM_PROVINCE.T2ASHIPMENTNO_KPI = FROM_PROVINCE.T2ALEADTIME
ACT_SHIPFROM_CITY.T2ASHIPMENTNO_KPI = FROM_CITY.T2ALEADTIME ACT_PROVINCE1.T2ASHIPMENTNO_KPI = TO_PROVINCE.T2ALEADTIME
OFF TIME 的条件 ACT_ATAMIT - ACT_INSERTDATE(小时默认为23:00) > LEAD TIME
M.表示ON TIME 的条件 ACT_ATADESTNPORT < = ACT_ETADATE
OFF TIME 的条件 ACT_ATADESTNPORT > ACT_ETADATE
ALL.表示TOTAL = 在T2ASHIPMENTNO_KPI表中满足查询条件的 ACT_SHPMTNO的数量*/ TYPE V_CURSOR IS REF CURSOR;
MYCUR V_CURSOR;
V_TEMP VARCHAR2(20);
V_UNION VARCHAR2(20);
V_TEMP2 VARCHAR2(1000);
V_TEMP3 VARCHAR2(1000);
V_TEMP4 VARCHAR2(1000);BEGIN
V_SQL := ' SELECT 0 sign,''周'' ';
V_TEMP2 := ' SELECT 1 sign,''准时'' ';
V_TEMP3 := ' SELECT 2 sign,''晚到'' ';
V_TEMP4 := ' SELECT 3 sign,''总数'' ';
V_UNION := ' union ';
IF V_TYPE = 'C' THEN
OPEN MYCUR FOR
SELECT DISTINCT CEIL(TO_NUMBER(TO_CHAR(SUB_DATE, 'DDD')) / 7)
FROM T1 WHERE T1.SUB_DATE>=V_BDATE AND V_BDATE<=V_EDATE;
LOOP
FETCH MYCUR
INTO V_TEMP;
EXIT WHEN MYCUR%NOTFOUND;
V_SQL := V_SQL || ' ,' || V_TEMP;
V_TEMP2 := V_TEMP2 || ' ,f_test(' || V_TEMP || ',1)';
V_TEMP3 := V_TEMP3 || ' ,f_test(' || V_TEMP || ',2)';
V_TEMP4 := V_TEMP4 || ' ,f_test(' || V_TEMP || ',3)';
END LOOP;
V_SQL := V_SQL || ' from t1';
V_TEMP2 := V_TEMP2 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP3 := V_TEMP3 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP4 := V_TEMP4 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
CLOSE MYCUR;
V_SQL := V_SQL || V_UNION || V_TEMP2 || V_UNION || V_TEMP3 || V_UNION ||
V_TEMP4 || 'order by 1';
ELSIF V_TYPE = 'M' THEN
OPEN MYCUR FOR
SELECT DISTINCT CEIL(TO_NUMBER(TO_CHAR(SUB_DATE, 'DDD')) / 7)
FROM T1 WHERE T1.SUB_DATE>=V_BDATE AND V_BDATE<=V_EDATE;
LOOP
FETCH MYCUR
INTO V_TEMP;
EXIT WHEN MYCUR%NOTFOUND;
V_SQL := V_SQL || ' ,' || V_TEMP;
V_TEMP2 := V_TEMP2 || ' ,f_test(' || V_TEMP || ',1)';
V_TEMP3 := V_TEMP3 || ' ,f_test(' || V_TEMP || ',2)';
V_TEMP4 := V_TEMP4 || ' ,f_test(' || V_TEMP || ',3)';
END LOOP;
V_SQL := V_SQL || ' from t1';
V_TEMP2 := V_TEMP2 || ' FROM t1,t2 WHERE t1.arr_city<=t2.arr_city ';
V_TEMP3 := V_TEMP3 || ' FROM t1,t2 WHERE t1.arr_city<=t2.arr_city ';
V_TEMP4 := V_TEMP4 || ' FROM t1,t2 WHERE t1.arr_city<=t2.arr_city ';
CLOSE MYCUR;
V_SQL := V_SQL || V_UNION || V_TEMP2 || V_UNION || V_TEMP3 || V_UNION ||
V_TEMP4 || 'order by 1';
ELSE
OPEN MYCUR FOR
SELECT DISTINCT CEIL(TO_NUMBER(TO_CHAR(SUB_DATE, 'DDD')) / 7)
FROM T1 WHERE T1.SUB_DATE>=V_BDATE AND V_BDATE<=V_EDATE;
LOOP
FETCH MYCUR
INTO V_TEMP;
EXIT WHEN MYCUR%NOTFOUND;
V_SQL := V_SQL || ' ,' || V_TEMP;
V_TEMP2 := V_TEMP2 || ' ,f_test(' || V_TEMP || ',1)';
V_TEMP3 := V_TEMP3 || ' ,f_test(' || V_TEMP || ',2)';
V_TEMP4 := V_TEMP4 || ' ,f_test(' || V_TEMP || ',3)';
END LOOP;
V_SQL := V_SQL || ' from t1';
V_TEMP2 := V_TEMP2 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP3 := V_TEMP3 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
V_TEMP4 := V_TEMP4 || ' FROM t1,t2 WHERE t1.arr_city=t2.arr_city ';
CLOSE MYCUR;
V_SQL := V_SQL || V_UNION || V_TEMP2 || V_UNION || V_TEMP3 || V_UNION ||
V_TEMP4 || 'order by 1';
END IF;
END P_TEST_TOP;