--创建一个包 createorreplace package types as type cursorType is ref cursor; end types;
--创建存储过程,游标类型返回参数 createorreplaceprocedure SP_Fee_Instance(v_company invarchar, v_sdate in nvarchar2, v_edate in nvarchar2,p_cursor in out types.cursorType) is -- vs_sql varchar2(2000); --company varchar2(100); --公司编码 --start_date varchar2(10); --计划付款时间段[起] --end_date varchar2(10); --计划付款时间段[止] begin IF NVL(v_company,'') <>''THEN OPEN p_cursor FOR select a.company,a.pact_name,a.pact_code,i.payment_date,sum(i.payment_fee) as payment_fee,i.payment_condition,i.payment_remark from htgl_pact_apply a innerjoin htgl_fee_instance i on a.apply_id = i.apply_id where a.payment_status=2 and a.company = v_company and i.payment_date between to_date(v_sdate,'YYYY-MM-DD') and to_date(v_edate,'YYYY-MM-DD') groupby a.company,a.pact_name,a.pact_code,i.payment_date,i.payment_condition,i.payment_remark; null; -- dbms_output.put_line(company); -- dbms_output.put_line(start_date); ELSE OPEN p_cursor FOR select a.company,a.pact_name,a.pact_code,i.payment_date,sum(i.payment_fee) as payment_fee,i.payment_condition,i.payment_remark from htgl_pact_apply a innerjoin htgl_fee_instance i on a.apply_id = i.apply_id where a.payment_status=2 and i.payment_date between to_date(v_sdate,'YYYY-MM-DD') and to_date(v_edate,'YYYY-MM-DD') groupby a.company,a.pact_name,a.pact_code,i.payment_date,i.payment_condition,i.payment_remark; null; ENDIF; -- EXECUTE IMMEDIATE vs_sql; -- EXECUTE IMMEDIATE vs_sql into i; end SP_Fee_Instance; --下面是调用方法 --execute SP_Fee_Instance('ass','','')
--创建一个包create or replace package typesastype cursorType is ref cursor;end types;--创建存储过程,游标类型返回参数create or replace procedure SP_Fee_Instance(v_company in varchar, v_sdate in nvarchar2, v_edat