有Table记录每月的每一天:
1 select to_char(cday,'yyyy.mm.dd') from personnel.calendar
2 where class_group = 'A'
3* and to_char(cday,'yyyy.mm') = '2011.03'
SQL> /TO_CHAR(CD
----------
2011.03.01
2011.03.02
2011.03.03
2011.03.04
2011.03.05
2011.03.06
2011.03.07
2011.03.08
2011.03.09
2011.03.10
2011.03.11
2011.03.12
2011.03.13
2011.03.14
2011.03.15
2011.03.16
2011.03.17
2011.03.18
2011.03.19
2011.03.20
2011.03.21
2011.03.22
2011.03.23
2011.03.24
2011.03.25
2011.03.26
2011.03.27
2011.03.28
2011.03.29
2011.03.30
2011.03.31選取了 31 列SQL> 怎样使每一天变成列显示? 因为要考虑每个月,所以列不固定。请指教。
1 select to_char(cday,'yyyy.mm.dd') from personnel.calendar
2 where class_group = 'A'
3* and to_char(cday,'yyyy.mm') = '2011.03'
SQL> /TO_CHAR(CD
----------
2011.03.01
2011.03.02
2011.03.03
2011.03.04
2011.03.05
2011.03.06
2011.03.07
2011.03.08
2011.03.09
2011.03.10
2011.03.11
2011.03.12
2011.03.13
2011.03.14
2011.03.15
2011.03.16
2011.03.17
2011.03.18
2011.03.19
2011.03.20
2011.03.21
2011.03.22
2011.03.23
2011.03.24
2011.03.25
2011.03.26
2011.03.27
2011.03.28
2011.03.29
2011.03.30
2011.03.31選取了 31 列SQL> 怎样使每一天变成列显示? 因为要考虑每个月,所以列不固定。请指教。
劳尔马德里 请看这里
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?68177
CREATE OR REPLACE PROCEDURE get_vendor_product_amount (p_month VARCHAR2)
IS
sqlstr VARCHAR2 (4000);
BEGIN
sqlstr :=
'
CREATE OR REPLACE VIEW VENDOR_PRODUCT_AMOUNT
(VENDORID, CURRENCY, AMOUNT, INSERT_TIME)
AS
SELECT a.vendorid, b.currency, SUM (b.amount) amount,
TO_CHAR (a.insert_time, ''yyyy.mm.dd'') insert_time
FROM sales.product_order a,
(SELECT order_sheetno, currency, SUM (amount) amount
FROM sales.product_order_detail
WHERE TO_CHAR (insert_time, ''yyyy.mm'') = p_month
GROUP BY order_sheetno, currency) b
WHERE TO_CHAR (a.insert_time, ''yyyy.mm'') = p_month
AND a.order_sheetno = b.order_sheetno
GROUP BY vendorid, currency, TO_CHAR (a.insert_time, ''yyyy.mm.dd'')'; EXECUTE IMMEDIATE sqlstr;
END get_vendor_product_amount;
/创建存储过程未报错,但执行时报错:ORA-00900不知道是什么问题??是不是我的存储过程中写的有问题??
execute get_vendor_product_amount('2011.02');
CREATE OR REPLACE PROCEDURE get_vendor_product_amount (p_month VARCHAR2)IS
sqlstr VARCHAR2 (4000);
BEGIN
sqlstr :=
'
CREATE OR REPLACE VIEW VENDOR_PRODUCT_AMOUNT
(VENDORID, CURRENCY, AMOUNT, INSERT_TIME)
AS
SELECT a.vendorid, b.currency, SUM (b.amount) amount,
TO_CHAR (a.insert_time, ''yyyy.mm.dd'') insert_time
FROM sales.product_order a,
(SELECT order_sheetno, currency, SUM (amount) amount
FROM sales.product_order_detail
WHERE TO_CHAR (insert_time, ''yyyy.mm'') = '||p_month||'
GROUP BY order_sheetno, currency) b
WHERE TO_CHAR (a.insert_time, ''yyyy.mm'') = '||p_month||'
AND a.order_sheetno = b.order_sheetno
GROUP BY vendorid, currency, TO_CHAR (a.insert_time, ''yyyy.mm.dd'')'; EXECUTE IMMEDIATE sqlstr;
END get_vendor_product_amount;
试试
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?68177