like this:
Select ptd.FEE_TYPE_CODE, sum(decode(FEE_TYPE_CODE ,'R',1,0)) as t1,sum(decode(FEE_TYPE_CODE ,'V',1,0)) as t2,....
From PAYMENT_TXN pt,PAYMENT_TXN_DETAILS ptd
Where pt.BUSINESS_TXN_KEY_REF = ptd.BUSINESS_TXN_KEY_REF
And ptd. BUSINESS_TXN_CODE = ‘V001M1’
And ptd. BUSINESS_TXN_SUBCODE in (‘01’,’02’,’03’)
And FEE_TYPE_CODE = ‘R’
Group by ptd.FEE_TYPE_CODE;
Select ptd.FEE_TYPE_CODE, sum(decode(FEE_TYPE_CODE ,'R',1,0)) as t1,sum(decode(FEE_TYPE_CODE ,'V',1,0)) as t2,....
From PAYMENT_TXN pt,PAYMENT_TXN_DETAILS ptd
Where pt.BUSINESS_TXN_KEY_REF = ptd.BUSINESS_TXN_KEY_REF
And ptd. BUSINESS_TXN_CODE = ‘V001M1’
And ptd. BUSINESS_TXN_SUBCODE in (‘01’,’02’,’03’)
And FEE_TYPE_CODE = ‘R’
Group by ptd.FEE_TYPE_CODE;
From PAYMENT_TXN pt,PAYMENT_TXN_DETAILS ptd
Where pt.BUSINESS_TXN_KEY_REF = ptd.BUSINESS_TXN_KEY_REF
And ptd. BUSINESS_TXN_CODE = ‘V001M1’
And ptd. BUSINESS_TXN_SUBCODE in (‘01’,’02’,’03’)
And FEE_TYPE_CODE in(‘R’,‘V’,...) --放在一起不行么?
Group by ptd.FEE_TYPE_CODE
游标循环两次即可
不管怎么样,你的SQL总是都是要执行两遍的,只不过循环执行而已。。
type g_cursor is ref cursor;
create or replace procedure aaa(FEE_TYPE_CODE in varchar2,rs out g_cursor)
as
str varchar2(400);
begin
str='Select ptd.FEE_TYPE_CODE, count(*) as t2
From PAYMENT_TXN pt,PAYMENT_TXN_DETAILS ptd
Where pt.BUSINESS_TXN_KEY_REF = ptd.BUSINESS_TXN_KEY_REF
And ptd. BUSINESS_TXN_CODE = ‘V001M1’
And ptd. BUSINESS_TXN_SUBCODE in (‘01’,’02’,’03’)
And FEE_TYPE_CODE = '''||FEE_TYPE_CODE||'''
Group by ptd.FEE_TYPE_CODE'
end;
其他的是一样的
谢谢,我注意到了。
能给我你的邮箱吗?
我这几天在做一些报表,要写很多存储过程,希望有难题能像你请教!
execute immediate v_sql_str into v_col1,v_col2...;
15:22:10 SQL> declare
15:22:13 2 v_sql varchar2(200);
15:22:14 3 v_col1 tb.col1%type;
15:22:27 4 v_col2 tb.col2%type;
15:22:34 5 begin
15:22:37 6 v_sql:='select col1,col2 from tb where rownum=1';
15:22:37 7 execute immediate v_sql into v_col1,v_col2;
15:22:37 8 dbms_output.put_line(v_col1||';'||v_col2);
15:22:37 9 end;
15:22:37 10 /
1;aaaPL/SQL 过程已成功完成。已用时间: 00: 00: 00.47
15:22:37 SQL> select col1,col2 from tb where rownum=1;COL1 COL2
---------- ------------------------------
1 aaa已用时间: 00: 00: 00.15
15:22:51 SQL>