oracle 游标 返回结果集 例子

实际例子：用到Packages

包头：

CREATE OR REPLACE PACKAGE PACK_LED_LEDGER AS
-- 游标申明
  -- 返回台账数据的游标
  --TYPE CUR_LEDGER IS ref CURSOR;

-- 储存过程申请
  PROCEDURE SP_LED_LEDGER_FETCH(
    JLQJAZDD IN  VARCHAR2 DEFAULT '' ,  --安装地点  位号
    pltypename IN  VARCHAR2 DEFAULT '' , -- 台账类型编码
    pdtypename IN  VARCHAR2 DEFAULT '', -- 台账使用单位编码
    pttypename IN VARCHAR2 DEFAULT '', -- 台账管理类别编码
    pzttypename IN VARCHAR2 DEFAULT '', -- 台账使用状态编码
    ptoolname IN  VARCHAR2 DEFAULT '',-- 计量器具名称
    ptoolcode IN VARCHAR2 DEFAULT '', --计量器具编码
    psccjname IN  VARCHAR2 DEFAULT '', -- 生产厂家名称
   /* pjdrq IN VARCHAR2 DEFAULT '',--检定日期*/
    pispage IN VARCHAR2 DEFAULT '0', -- 是否分页，如果为1时分页，为0时不分页，默认不分页
    ppageer IN NUMBER DEFAULT 1,-- 第几页，
    prp IN NUMBER DEFAULT 15, -- 一页显示的行数
    presult OUT sys_refcursor, -- 输出的结果集 
    presultext OUT SYS_REFCURSOR -- 结果的一些扩展属性
  );

-- 生成扩展数据查询的sql
  FUNCTION Fn_LED_LEDGER_EXTSWL(
    pltypeid VARCHAR2
  ) RETURN VARCHAR2;
END PACK_LED_LEDGER;

body：

CREATE OR REPLACE PACKAGE BODY PACK_LED_LEDGER AS
  -- 查询台账的的储存过程的实现
  PROCEDURE SP_LED_LEDGER_FETCH(
    JLQJAZDD IN  VARCHAR2 DEFAULT '' ,  --安装地点  位号
    pltypename IN  VARCHAR2 DEFAULT '' , -- 台账类型名称
    pdtypename IN  VARCHAR2 DEFAULT '', -- 台账使用单位名称
    pttypename IN VARCHAR2 DEFAULT '', -- 台账管理类别名称
    pzttypename IN VARCHAR2 DEFAULT '', -- 台账使用状态名称
    ptoolname IN  VARCHAR2 DEFAULT '',-- 计量器具名称
    ptoolcode IN VARCHAR2 DEFAULT '', --计量器具编码
    psccjname IN  VARCHAR2 DEFAULT '', -- 生产厂家名称
   /* pjdrq IN VARCHAR2 DEFAULT '',--检定日期*/
    pispage IN VARCHAR2 DEFAULT '0', -- 是否分页，如果为1时分页，为0时不分页，默认不分页
    ppageer IN NUMBER DEFAULT 1,-- 第几页，
    prp IN NUMBER DEFAULT 15, -- 一页显示的行数
    presult OUT sys_refcursor, -- 输出的结果集 
    presultext OUT SYS_REFCURSOR -- 结果的一些扩展属性
  )
  IS
    pagebegin NUMERIC(10); -- 一页的起始行
    pageend NUMBER(10); -- 一页的结束行
    pagerows NUMBER(10); -- 每页显示的行数
    pltypeid VARCHAR2(50) DEFAULT ''; -- 台账类型编码，根据参数取得
    pdtypeid VARCHAR2(50) DEFAULT ''; -- 使用部门编码
    pttypeid VARCHAR2(50) DEFAULT ''; -- 台账管理类型编码
    pzttypeid VARCHAR2(50) DEFAULT ''; -- 台账使用状态编码
    pagesqlstr VARCHAR2(300) DEFAULT ''; -- 分布语句
    tjsqlstr VARCHAR2(1000) DEFAULT '';  -- 查询条件生成语句
    maintsql VARCHAR2(4000) DEFAULT ''; -- 查询主表的数据的sql语句
    maintjoinsqlheader VARCHAR2(500) DEFAULT ''; -- 主数据的表头
    exttsql VARCHAR2(4000) DEFAULT ''; -- 扩展表的查询sql语句
    aexttsql VARCHAR2(120) DEFAULT '';
    extfield VARCHAR2(120) DEFAULT '';
    extttjsql VARCHAR2(1000) DEFAULT ''; -- 扩展表的查询条件
    exttcursql VARCHAR2(1000) DEFAULT ''; --定义扩展表的游标
    alldatasqlstr VARCHAR2(8000);
    alldatasqlstrext VARCHAR2(8000);
    /*temp2 VARCHAR2(50) DEFAULT '';*/
    TYPE d_cur IS ref CURSOR;
    fields_cur d_cur;
  BEGIN
    pagerows := prp;

    -- 分页数据
    pagebegin := (ppageer-1)*pagerows+1;
    pageend :=ppageer*pagerows;

    tjsqlstr := '';

    IF pltypename IS NOT NULL AND length(pltypename)>0 THEN
      --SELECT typeid INTO pltypeid FROM led_ledger_type WHERE fullname = pltypename;--gh 屏蔽
      pltypeid := pltypename;
      tjsqlstr := ' ll.ltyepid = '''||pltypeid||''' ';
    END IF;

    IF pdtypename IS NOT NULL AND length(pdtypename)>0 THEN
      --SELECT typeid INTO pdtypeid FROM led_department_type WHERE fullname = pdtypename;
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' AND ll.dtypename LIKE ''%'||pdtypename||'%'' ';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.dtypename LIKE ''%'||pdtypename||'%'' ';
      END IF;
    END IF;


    IF pttypename IS NOT NULL AND length(pttypename)>0 THEN
      --SELECT typeid INTO pttypeid FROM led_tool_type WHERE fullname = pttypename;
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' AND ll.ttypename LIKE ''%'||pttypename||'%'' ';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.ttypename LIKE ''%'||pttypename||'%'' ';
      END IF;
    END IF;

    IF pzttypename IS NOT NULL AND length(pzttypename) >0 THEN
      --SELECT typeid INTO pzttypeid FROM led_syzt_type WHERE fullname = pzttypename;
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' AND ll.zttypename LIKE ''%'||pzttypename||'%'' ';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.zttypename LIKE ''%'||pzttypename||'%'' ';
      END IF;
    END IF;

    IF ptoolname IS NOT NULL AND length(ptoolname)>0 THEN
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' and ll.jlqjname LIKE ''%'||ptoolname||'%'' ';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.jlqjname LIKE ''%'||ptoolname||'%'' ';
      END IF;
    END IF;

    IF ptoolcode IS NOT NULL AND length(ptoolcode)>0 THEN
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' AND ll.jlqjcode LIKE ''%'||ptoolcode||'%'' ';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.jlqjcode LIKE ''%'||ptoolcode||'%'' ';
      END IF;
    END IF;

    IF psccjname IS NOT NULL AND length(psccjname)>0 THEN
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' AND ll.jlqjsccj LIKE ''%'||psccjname||'%'' ';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.jlqjsccj LIKE ''%'||psccjname||'%'' ';
      END IF;
    END IF;
    
    
    
        IF JLQJAZDD IS NOT NULL AND length(JLQJAZDD)>0 THEN
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' AND ll.JLQJAZDD LIKE ''%'||JLQJAZDD||'%'' ';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.JLQJAZDD LIKE ''%'||JLQJAZDD||'%'' ';
      END IF;
    END IF;
    
/*        IF pjdrq IS NOT NULL AND length(pjdrq)>0 THEN
          temp2 := to_date(pjdrq,'YYYY/MM/DD');
      IF length(tjsqlstr)>0 THEN
        tjsqlstr := tjsqlstr || ' where ll.test_date=temp2';
      ELSE
        tjsqlstr := tjsqlstr || ' ll.test_date=temp2 ';
      END IF;
    END IF;*/

    -- 结果扩展表的属性
    IF length(tjsqlstr)>0 THEN
      alldatasqlstrext :='SELECT count(*) allcount FROM led_ledger ll WHERE '||tjsqlstr; 
    ELSE
      alldatasqlstrext :='SELECT count(*) allcount FROM led_ledger ll'; 
    END IF;
     

    -- 生成分页语句
    IF pispage = '1' THEN
      pagesqlstr := 'true';
      IF tjsqlstr IS NOT NULL AND length(tjsqlstr)>0 THEN
        --tjsqlstr := '  ROWID IN(SELECT rid FROM( SELECT ROWID rid,ROWNUM rn ,jlqjid FROM led_ledger ll where '||tjsqlstr||' ORDER BY jlqjid DESC)WHERE rn BETWEEN '|| pagebegin ||' AND ' || pageend || ')';
        tjsqlstr := '  ROWID IN(SELECT rid FROM( SELECT ROWID rid,ROWNUM rn ,jlqjid FROM led_ledger ll where '||tjsqlstr||' ORDER BY jlqjid ASC)WHERE rn BETWEEN '|| pagebegin ||' AND ' || pageend || ')';
        
      ELSE
        -- tjsqlstr := '  ROWID IN(SELECT rid FROM( SELECT ROWID rid,ROWNUM rn ,jlqjid FROM led_ledger ORDER BY jlqjid DESC)WHERE rn BETWEEN '|| pagebegin ||' AND ' || pageend || ')';
        tjsqlstr := '  ROWID IN(SELECT rid FROM( SELECT ROWID rid,ROWNUM rn ,jlqjid FROM led_ledger ORDER BY jlqjid ASC)WHERE rn BETWEEN '|| pagebegin ||' AND ' || pageend || ')';
      END IF;
    END IF;

    -- 添加测试
    dbms_output.put_line(tjsqlstr);

    -- 生成其它条件
    --tjsqlstr := ' ll.ltyepid LIKE '''||pltypeid||'%'' AND ll.dtypeid LIKE '''||pdtypeid||'%'' AND ll.ttypeid LIKE '''||pttypeid||'%'' AND ll.zttypeid LIKE '''||pzttypeid||'%'' ';
    --tjsqlstr := tjsqlstr || ' and ll.jlqjname LIKE '''||ptoolname||'%'' AND ll.jlqjcode LIKE '''||ptoolcode||'%'' AND ll.jlqjsccj LIKE '''||psccjname||'%'' ';

    IF pagesqlstr IS NOT NULL AND length(pagesqlstr)>0 THEN
      IF length(tjsqlstr)>0 THEN
        maintsql := 'SELECT * FROM led_ledger WHERE '||tjsqlstr;
        extttjsql := 'SELECT xxxxguidflagxxx FROM led_ledger WHERE '||tjsqlstr;
      ELSE
        maintsql := 'SELECT * FROM led_ledger ';
        extttjsql := 'SELECT xxxxguidflagxxx FROM led_ledger ';
      END IF;
    ELSE
      IF length(tjsqlstr)>0 THEN
        maintsql := 'SELECT * FROM led_ledger ll WHERE '||tjsqlstr;
        extttjsql := 'SELECT xxxxguidflagxxx FROM led_ledger ll WHERE '||tjsqlstr;
      ELSE
        maintsql := 'SELECT * FROM led_ledger ll';
        extttjsql := 'SELECT xxxxguidflagxxx FROM led_ledger ll';
      END IF;
    END IF;




    --排序
    --maintsql := maintsql || ' order by li.JLQJID DESC ';

    -- 添加测试
    --dbms_output.put_line(maintsql);

    -- 添加主主数据的查询连接

    --maintjoinsqlheader := 'SELECT lldtt.*,lsyt.fullname AS zttypename FROM ( ';
    --maintjoinsqlheader := maintjoinsqlheader || ' SELECT lldt.*,ltt.fullname AS ttypename FROM( ';
    --maintjoinsqlheader := maintjoinsqlheader ||  ' SELECT llt.*,ldt.fullname AS dtypename FROM ( ';
    maintjoinsqlheader := maintjoinsqlheader ||  ' SELECT templl.*,llt.fullname AS ltypename FROM( ';

    maintsql := maintjoinsqlheader || maintsql;

    
    --排序
    IF pispage = '1' THEN
      maintsql := maintsql || ' ) templl,led_ledger_type llt WHERE templl.ltyepid = llt.typeid order by templl.jlqjid desc ';
    ELSE
      maintsql := maintsql || ' order by ll.jlqjid desc ) templl,led_ledger_type llt WHERE templl.ltyepid = llt.typeid ';
    END IF;
  

    --maintsql := maintsql || ' ) templl,led_ledger_type llt WHERE templl.ltyepid = llt.typeid ';
    --maintsql := maintsql || ' ) llt,led_department_type ldt WHERE llt.dtypeid = ldt.typeid ';
    --maintsql := maintsql || ' ) lldt,led_tool_type ltt WHERE lldt.ltyepid = ltt.typeid ';
    --maintsql := maintsql || ' ) lldtt,led_syzt_type lsyt WHERE lldtt.zttypeid = lsyt.typeid ';


    -- 添加测试
    --dbms_output.put_line(maintsql);

    --生成扩展数据的查询sql
    --exttcursql := 'SELECT extfieldname FROM led_ledger_extfield WHERE xxxxguidflagxxx in('||extttjsql||')';
    IF pltypeid IS NOT NULL AND length(pltypeid)>0 THEN
      exttcursql := 'SELECT extfieldname FROM led_ledger_extfield WHERE isdefault = 0 AND extfieldenable = 1 and ltypeid = '||pltypeid;
      --dbms_output.put_line(exttcursql);
    ELSE
      exttcursql := 'SELECT extfieldname FROM led_ledger_extfield WHERE isdefault = 0 AND extfieldenable = 1';
    END IF;
  

    --添加测试
    --dbms_output.put_line(exttsql);
    exttsql :=PACK_LED_LEDGER.FN_LED_LEDGER_EXTSWL(pltypeid);

    exttsql := exttsql || '  and xxxxguidflagxxx in ( ' || extttjsql || ' )';
    exttsql := exttsql || '  )GROUP BY xxxxguidflagxxx';

    --dbms_output.put_line(exttsql);

    alldatasqlstr := 'SELECT * FROM (' || maintsql || ') maint,(' || exttsql || ')extt WHERE maint.xxxxguidflagxxx = extt.xxxxguidflagxxxex(+)';

    alldatasqlstr := 'select * from (' || alldatasqlstr || ') ldata,';
/*    alldatasqlstr := alldatasqlstr || '( SELECT lc.* FROM led_check lc,(SELECT jlqjcode,max(check_date) AS lastcheckdate,max(checkid) as mcheckid FROM led_check GROUP BY jlqjcode)lcld WHERE lc.jlqjcode = lcld.jlqjcode AND lc.checkid = lcld.mcheckid)ledcdata WHERE ldata.jlqjcode = ledcdata.jlqjcode(+)';
*/   

    alldatasqlstr := alldatasqlstr || '( SELECT lc.* FROM led_check lc,(SELECT LEDGERID,max(check_date) AS lastcheckdate,max(checkid) as mcheckid FROM led_check GROUP BY LEDGERID)lcld WHERE lc.LEDGERID = lcld.LEDGERID AND lc.checkid = lcld.mcheckid)ledcdata WHERE ldata.JLQJID = ledcdata.LEDGERID(+)';



 
    --dbms_output.put_line(alldatasqlstr);


    OPEN presult FOR alldatasqlstr;

    OPEN presultext FOR alldatasqlstrext;

  END SP_LED_LEDGER_FETCH;



  -- 添加函数
  FUNCTION Fn_LED_LEDGER_EXTSWL(
    pltypeid VARCHAR2
  ) 
  RETURN VARCHAR2
  AS
    sqlstr VARCHAR2(4000);
    asqlstr VARCHAR2(200);
    fieldname VARCHAR2(100);
    CURSOR fields_cur IS
      SELECT extfieldname FROM led_ledger_extfield WHERE isdefault = 0 AND extfieldenable = 1 AND ltypeid LIKE  pltypeid||'%';
  BEGIN
    sqlstr := 'SELECT xxxxguidflagxxx as xxxxguidflagxxxex,';
  
    OPEN fields_cur;
  
    LOOP
      FETCH fields_cur INTO fieldname;
      EXIT WHEN fields_cur%NOTFOUND;
      
      asqlstr := 'max(decode(extfieldname,''' || fieldname || ''',extvalue,null)) as ' || fieldname;
      sqlstr := sqlstr || asqlstr || ',';
  
    END LOOP;
    CLOSE fields_cur;
    sqlstr := substr(sqlstr,0,length(sqlstr)-1); 
    sqlstr := sqlstr || ' FROM ( SELECT lld.xxxxguidflagxxx,lld.extvalue,llf.extfieldname FROM led_ledger_extdata lld,led_ledger_extfield llf WHERE lld.extfieldid = llf.extfieldid';

    RETURN sqlstr;

  END;

END PACK_LED_LEDGER;