oracle输出多行多列数据

--方法一匿名块中直接 dbms_output输出
declare

v_sql    varchar2(200);
v_cursor sys_refcursor;
type v_type is record(
    n1 varchar2(30));
v_rows v_type;
begin

v_sql := ' select substr（tax_organ_name,1,2)   from sk_tax_organ n where n.up_tax_organ=''cde5a0d73628497d84592c49be670ca5'' order by n.tax_organ_code asc';

open v_cursor for v_sql;

for i in 1 .. 36 loop
    fetch v_cursor
      into v_rows;
    dbms_output.put_line('1000' || to_char(i) || '0001' || v_rows.n1 || '国税' ||
                         chr(10) || '1000' || to_char(i) || '0002' ||
                         v_rows.n1 || '地税');
end loop;

close v_cursor;
end;

/*
100010001北京国税
100010002北京地税
*/

--方法二存储过程中游标输出结果集
create or replace procedure zxrs_hz (v_kssj varchar2,v_zzsj varchar2,v_jgbs varchar2,v_yyid varchar2,v_nfyfsj varchar2,my_cur out sys_refcursor)

is
my_sql varchar2(20000);
my_errmsg varchar2(50);

begin

if v_nfyfsj='year' then
my_sql:='SELECT    SUM(A.YY_ZXRS) AS "num", A.yym ,A.ZFWM, to_char(create_date,''yyyy'')||''年'' TIMEQ, B.APPLYNAME AS "yyname" FROM T_ZXRS_LOG_'||v_jgbs||' A INNER JOIN T_MAPPING_ADDRESS b ON b. ID = A.yym where create_date >=
to_date('''||v_kssj||''', ''yyyy-mm-dd hh24:mi:ss'') and create_date <=
to_date('''||v_zzsj||''', ''yyyy-mm-dd hh24:mi:ss'') and a.yym in ('||v_yyid||') GROUP BY a.yym, a.ZFWM, B.APPLYNAME,to_char(create_date,''yyyy'')';

open my_cur for my_sql;

elsif v_nfyfsj='month' then
my_sql:='SELECT    SUM(A.YY_ZXRS) AS "num", A.yym ,A.ZFWM, to_char(create_date,''yyyy'')||''年''||to_char(create_date,''mm'')||''月'' TIMEQ, B.APPLYNAME AS "yyname" FROM T_ZXRS_LOG_'||v_jgbs||' A INNER JOIN T_MAPPING_ADDRESS b ON b. ID = A.yym where create_date >=
to_date('''||v_kssj||''', ''yyyy-mm-dd hh24:mi:ss'') and create_date <=
to_date('''||v_zzsj||''', ''yyyy-mm-dd hh24:mi:ss'') and a.yym in ('||v_yyid||') GROUP BY a.yym, a.ZFWM, B.APPLYNAME,to_char(create_date,''yyyy'')||''年''||to_char(create_date,''mm'')||''月''';

open my_cur for my_sql;

elsif v_nfyfsj='day' then
my_sql:='SELECT    SUM(A.YY_ZXRS) AS "num", A.yym ,A.ZFWM, to_char(create_date,''yyyy'')||''年''||to_char(create_date,''mm'')||''月''||to_char(create_date,''dd'')||''日'' TIMEQ, B.APPLYNAME AS "yyname" FROM T_ZXRS_LOG_'||v_jgbs||' A INNER JOIN T_MAPPING_ADDRESS b ON b. ID = A.yym where create_date >=
to_date('''||v_kssj||''', ''yyyy-mm-dd hh24:mi:ss'') and create_date <=
to_date('''||v_zzsj||''', ''yyyy-mm-dd hh24:mi:ss'') and a.yym in ('||v_yyid||') GROUP BY a.yym, a.ZFWM, B.APPLYNAME,to_char(create_date,''yyyy'')||''年''||to_char(create_date,''mm'')||''月''||to_char(create_date,''dd'')||''日''';

open my_cur for my_sql;

end if;

exception when others then
my_errmsg:=sqlcode;
end;

/*
0   160   1000010001   2014年   测试1
0   160   1000010001   2015年   测试1
162   160   1000010001   2016年   测试1
26   161   1000010001   2016年   测试2
*/

oracle输出多行多列数据

推荐阅读