创建函数 CREATE OR REPLACE FUNCTION F_TEST(V_WEEK INTEGER, V_TYPE INTEGER) RETURN INTEGER IS --参数说明: --v_week:以周以参数 --v_type:返回准时或晚到或总数.分别为1.2.3 V_COUNTS INTEGER := 0; BEGIN --表示准时到达 IF V_TYPE = 1 THEN 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;
--表示晚到 ELSIF V_TYPE = 2 THEN SELECT SUM(DECODE(SIGN((T1.ARR_DATE - T1.SUB_DATE) * 24 - T2.REQ_HOUR), -1, 1, 0)) 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;
ELSIF v_type=3 THEN SELECT COUNT(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;
END IF; RETURN V_COUNTS; EXCEPTION WHEN OTHERS THEN RETURN 9999; END F_TEST; 创建存储过程返回动态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;
SQL> select * from t1;SUB_DATE ARR_DATE PRE_DATE ARR_CITY ----------- ----------- ----------- -------------------- 2004-10-13 2004-10-15 2004-10-15 西安 2004-11-15 2004-11-16 2004-11-18 北京 2004-11-14 2004-11-16 2004-11-15 上海 2004-11-13 2004-11-15 2004-11-14 广州SQL> select * from t2; PRICE ARR_CITY REQ_HOUR ----------- -------------------- ------------ 西安 20.00 上海 10.00 北京 130.00 广州 130.00SQL> SELECT 0 sign,'周' ,41 ,46 from t1 union SELECT 1 sign,'准时' ,f_test(41,1) ,f_test(46,1) FROM t1,t2 WHERE t1.arr_city=t2.arr_city union SELECT 2 sign,'晚到' ,f_test(41,2) ,f_test(46,2) FROM t1,t2 WHERE t1.arr_city=t2.arr_city union SELECT 3 sign,'总数' ,f_test(41,3) ,f_test(46,3) FROM t1,t2 WHERE t1.arr_city=t2.arr_city order by 1; SIGN '周' 41 46 ---------- ---- ---------- ---------- 0 周 41 46 1 准时 1 1 2 晚到 0 2 3 总数 1 3S
具體做發樓主最好自己嘗試一下做過程加臨時表可以解決
http://community.csdn.net/Expert/topic/3421/3421956.xml?temp=.9704859
?前台程序将行列转换
假设视图myview如下:
周 准时到达记录总数 晚到达记录总数 总记录数
--- ---------------- -------------- --------
34 36 33 3
. . . .
. . . .
. . . .用ASP取得该视图。
显示时,记录集 中的值放在页面相应的位置上。(当然记录集中的行将显示在页面的列上)
CREATE OR REPLACE FUNCTION F_TEST(V_WEEK INTEGER, V_TYPE INTEGER)
RETURN INTEGER IS
--参数说明:
--v_week:以周以参数
--v_type:返回准时或晚到或总数.分别为1.2.3
V_COUNTS INTEGER := 0;
BEGIN
--表示准时到达
IF V_TYPE = 1 THEN
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;
--表示晚到
ELSIF V_TYPE = 2 THEN
SELECT SUM(DECODE(SIGN((T1.ARR_DATE - T1.SUB_DATE) * 24 - T2.REQ_HOUR),
-1,
1,
0))
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;
ELSIF v_type=3 THEN
SELECT COUNT(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;
END IF;
RETURN V_COUNTS;
EXCEPTION
WHEN OTHERS THEN
RETURN 9999;
END F_TEST;
创建存储过程返回动态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;
SQL> select * from t1;SUB_DATE ARR_DATE PRE_DATE ARR_CITY
----------- ----------- ----------- --------------------
2004-10-13 2004-10-15 2004-10-15 西安
2004-11-15 2004-11-16 2004-11-18 北京
2004-11-14 2004-11-16 2004-11-15 上海
2004-11-13 2004-11-15 2004-11-14 广州SQL> select * from t2; PRICE ARR_CITY REQ_HOUR
----------- -------------------- ------------
西安 20.00
上海 10.00
北京 130.00
广州 130.00SQL> SELECT 0 sign,'周' ,41 ,46 from t1 union SELECT 1 sign,'准时' ,f_test(41,1) ,f_test(46,1) FROM t1,t2 WHERE t1.arr_city=t2.arr_city union SELECT 2 sign,'晚到' ,f_test(41,2) ,f_test(46,2) FROM t1,t2 WHERE t1.arr_city=t2.arr_city union SELECT 3 sign,'总数' ,f_test(41,3) ,f_test(46,3) FROM t1,t2 WHERE t1.arr_city=t2.arr_city order by 1; SIGN '周' 41 46
---------- ---- ---------- ----------
0 周 41 46
1 准时 1 1
2 晚到 0 2
3 总数 1 3S