--方法一 匿名块中直接 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)ismy_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' thenmy_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年 测试10 160 1000010001 2015年 测试1162 160 1000010001 2016年 测试126 161 1000010001 2016年 测试2*/
转载于:https://www.cnblogs.com/iyoume2008/p/6171067.html
相关资源：JAVA上百实例源码以及开源项目