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;