oracle 临时表使用例子并用CURSOR返回结果集的例子

包中带过程 要自己定义一个type [t_cursor] is ref cursor游标，返回的时候就直接 PROCEDURE find_scan_diff_date(dept_code      tb_bar_recode.dept_code%TYPE,
                                oper_start_tm  varchar2,
                                oper_end_tm    varchar2,
                                oper_type_code tb_bar_recode.oper_type_code%TYPE,
                                c_ref          OUT t_cursor);

存储过程 用系统默认的 sys_refcursor 游标类型 定义变量就OK了

 

1：生成包

--生成包
CREATE OR REPLACE PACKAGE pkg_bar_scan_diff IS

  /*TYPE waybillnotype IS RECORD(
    waybill_no tb_bar_recode.waybill_no%TYPE);*/

  TYPE t_cursor IS REF CURSOR /*RETURN waybillnotype*/;

  PROCEDURE find_scan_diff_date(dept_code      tb_bar_recode.dept_code%TYPE,
                                oper_start_tm  varchar2,
                                oper_end_tm    varchar2,
                                oper_type_code tb_bar_recode.oper_type_code%TYPE,
                                c_ref          OUT t_cursor);

END pkg_bar_scan_diff;

 

 

2：生成包体

   

--生成包体  
CREATE OR REPLACE PACKAGE BODY pkg_bar_scan_diff IS

/**
 * 1 ：根据点部代码，扫描类型，扫描时间 查出所有的扫描单号的母单号 并插入临时表；
 * 2 ：查出所有的 母单和子单;
 * 3 ：业务数据查询功能 可扩展;
 **/
  PROCEDURE find_scan_diff_date(dept_code      tb_bar_recode.dept_code%TYPE,
                                oper_start_tm  varchar2,
                                oper_end_tm    varchar2,
                                oper_type_code tb_bar_recode.oper_type_code%TYPE,
                                c_ref          OUT t_cursor) AS

    str        varchar(2000);
    v_statr_Tm varchar2(19) := oper_start_tm;
    v_end_Tm   varchar2(19) := oper_end_tm;
    v_i        int;
    v_j        int;

  BEGIN
    
    EXECUTE IMMEDIATE 'truncate TABLE tl_scan_waybill_record';
    EXECUTE IMMEDIATE 'truncate TABLE tl_scan_base_record';
    -- 查出所有的扫描单号的 母单号 并插入临时表;
    str := ' INSERT INTO tl_scan_waybill_record (waybill_no) (SELECT distinct t.waybill_no FROM tb_bar_recode t '
           ||' WHERE t.dept_code = '''||dept_code||''' '
           ||' AND   t.oper_type_code = '''||oper_type_code||''' '
           ||' AND   to_char(t.oper_tm,''yyyy-mm-dd hh24:mi:ss'') >= '''||v_statr_Tm||''' '
           ||' AND   to_char(t.oper_tm,''yyyy-mm-dd hh24:mi:ss'') < '''||v_end_Tm||''' '
           ||' AND substr(t.waybill_no, 0, 2) <> ''00'' '
           ||' union '
           ||' SELECT DISTINCT w.waybill_no '
           ||' FROM tb_bar_recode T, tb_waybill_child w, tb_waybill bill '
           ||' WHERE bill.waybill_id = w.waybill_id AND bill.waybill_type_code = ''1'' '
           ||' AND T.waybill_no = w.waybill_child_no '
           ||' AND T.dept_code = '''||dept_code||''' '
           ||' AND T.oper_type_code = '''||oper_type_code||''' '
           ||' AND   to_char(T.oper_tm,''yyyy-mm-dd hh24:mi:ss'') >= '''||v_statr_Tm||''' '
           ||' AND   to_char(T.oper_tm,''yyyy-mm-dd hh24:mi:ss'') < '''||v_end_Tm||''' '
           ||' AND substr(T.waybill_no, 0, 2) = ''00'' ) ';   EXECUTE IMMEDIATE str;
    --业务数据查询;
    str:=  ' SELECT a.waybill_no,b.waybill_child_no,b.is_flag,b.WAYBILL_NO_STATE, '
           ||'      a.cons_name,a.quantity,a.consignor_cont_name, '
           ||'      a.consignor_phone,a.consignor_mobile,a.addressee_cont_name, '
           ||'      a.addressee_phone,a.addressee_mobile,a.source_zone_code,a.dest_zone_code '
           ||' FROM tb_waybill a,tl_scan_base_record b '
           ||'WHERE a.waybill_no = b.waybill_no '
           ||'  and a.waybill_type_code = ''1'' ';
    open c_ref for str;
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;
END pkg_bar_scan_diff;

 

 

 

 

3：java程序调用

  

public List<BarSurplusWaybill> findAllSurplusDiff(final String deptCode,final String startDate
			,final String endDate,final String operType){
		Connection conn = this.getSession().connection();
		List<BarSurplusWaybill> lst = new ArrayList<BarSurplusWaybill>();
		CallableStatement cs;
		try {
			cs = conn.prepareCall("{ Call PKG_BAR_SCAN_DIFF.FIND_SCAN_DIFF_DATE(?,?,?,?,?) }");
			cs.setString(1, deptCode);
			cs.setString(2, startDate);
			cs.setString(3, endDate);
			cs.setString(4, operType);
			cs.registerOutParameter(5,OracleTypes.CURSOR);
			cs.execute();
			ResultSet rs = (ResultSet)cs.getObject(5);
			while(rs.next()){
				BarSurplusWaybill waybill = new BarSurplusWaybill();
				waybill.setWaybillNo((String)(rs.getString("waybill_no")==null?"":rs.getString("waybill_no")));
				lst.add(waybill);
		    }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for(int i = 0;i<lst.size();i++){
			System.out.println(lst.get(i).getWaybillNo());
		}
		return lst;
	}