欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Using of oracle ref cursor

程序员文章站 2022-04-26 09:54:56
...

1、强类型游标: CREATE OR REPLACE PACKAGE strongly_typed ISTYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;PROCEDURE child(p_return_rec OUT return_cur);PROCEDURE parent(p_NumRecs PLS_INTEGER);END strongly_typed;/ CREATE OR REPLACE

1、强类型游标:

CREATE OR REPLACE PACKAGE strongly_typed IS

TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);

END strongly_typed;
/
CREATE OR REPLACE PACKAGE BODY strongly_typed IS
PROCEDURE child(p_return_rec OUT return_cur) IS

BEGIN
  OPEN p_return_rec FOR 
  SELECT * FROM all_tables; 
END child;
--==================================================
PROCEDURE parent (p_NumRecs PLS_INTEGER) IS 
 p_retcur return_cur;
 at_rec   all_tables%ROWTYPE;
BEGIN
  child(p_retcur);

  FOR i IN 1 .. p_NumRecs
  LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.table_name || 
    ' - ' || at_rec.tablespace_name || 
    ' - ' || TO_CHAR(at_rec.initial_extent) || 
    ' - ' || TO_CHAR(at_rec.next_extent));
  END LOOP;
END parent;
END strongly_typed;
/

set serveroutput on

exec strongly_typed.parent(1);
exec strongly_typed.parent(8);

2、弱类型游标:

CREATE OR REPLACE PROCEDURE child (
 p_NumRecs IN PLS_INTEGER,
 p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
  OPEN p_return_cur FOR
  'SELECT * FROM all_tables WHERE rownum 

3、预定义游标变量:

CREATE TABLE employees (
empid   NUMBER(5),
empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS

TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;

BEGIN
  FETCH p_cursor BULK COLLECT INTO rec_array;

  FOR i IN rec_array.FIRST .. rec_array.LAST
  LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
END pass_ref_cur;
/
set serveroutput on

DECLARE
 rec_array SYS_REFCURSOR;
BEGIN
  OPEN rec_array FOR
  'SELECT empname FROM employees';

  pass_ref_cur(rec_array);
  CLOSE rec_array;
END;
/

-----------------------------------------------------

Dylan presents.