isitdevops.com/databases

TEST 2
===========================================================================================
CREATE OR REPLACE PACKAGE PHH AS
  TYPE insttyp IS REF CURSOR RETURN aud$%ROWTYPE;
  aud_rec aud$%ROWTYPE;
  PROCEDURE phh_open_refcursor (aud_cur IN OUT insttyp);
  PROCEDURE phh_close_refcursor (aud_cur IN OUT insttyp);
  PROCEDURE phh_fetch_refcursor (aud_cur IN OUT insttyp, aud_rec IN OUT aud$ROWTYPE);
END PHH;

CREATE OR REPLACE PACKAGE BODY PHH AS
  PROCEDURE phh_open_refcursor (aud_cur IN OUT insttyp) IS
  BEGIN
    IF NOT aud_cur%ISOPEN THEN
       OPEN aud_cur FOR SELECT * FROM aud$ WHERE ROWNUM     END IF;
  END phh_open_refcursor;

  PROCEDURE phh_close_refcursor (aud_cur IN OUT insttyp) IS
  BEGIN
    IF aud_cur%ISOPEN THEN
       CLOSE aud_cur;
    END IF;
  END phh_close_refcursor;

  PROCEDURE phh_fetch_refcursor (aud_cur IN OUT insttyp, aud_rec IN OUT aud$ROWTYPE) IS
  BEGIN
    FETCH aud_cur INTO aud_rec;
  END phh_fetch_refcursor;

  PROCEDURE loop_refcursor IS
    audit_cursor insttyp;
    audit_row aud$rowtype;
  BEGIN
    phh_open_refcursor ( audit_cursor );
    LOOP
      phh_fetch_refcursor ( audit_cursor, audit_row);
      EXIT when audit_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(‘SESSION_ID = ‘ || audit_row.sessionid);     
    END LOOP;
    phh_close_refcursor(audit_cursor);
  END loop_refcursor;

END PHH;

begin
  phh.loop_refcursor;
end;

============================================================================================

CREATE OR REPLACE PACKAGE PHH AS
  TYPE insttyp IS REF CURSOR RETURN aud$%ROWTYPE;
  FUNCTION phh_open_refcursor (tst_num number) RETURN insttyp;
END PHH;

CREATE OR REPLACE PACKAGE BODY PHH AS
  FUNCTION phh_open_refcursor (tst_num number) RETURN insttyp IS
  phh_refcursor insttyp;
  BEGIN
  IF NOT phh_refcursor%ISOPEN THEN
     OPEN phh_refcursor FOR SELECT * FROM aud$ WHERE ROWNUM   END IF;
  RETURN phh_refcursor;
  END;
END PHH;

DECLARE
TYPE insttyp IS REF CURSOR /* RETURN aud$%ROWTYPE (SYS_REFCURSOR) */;
auds aud$%ROWTYPE;
tst_cursor insttyp;
tst_num number := 3;
BEGIN

  tst_cursor := phh.phh_open_refcursor(tst_num);
  LOOP
    FETCH tst_cursor INTO auds;
    EXIT WHEN tst_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(‘SESSION_ID = ‘ || auds.sessionid);
  END LOOP;
  CLOSE tst_cursor;
end;

 

 

 

DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; — strong
TYPE genericcurtyp IS REF CURSOR; — weak
cursor1 empcurtyp;
cursor2 genericcurtyp;
my_cursor SYS_REFCURSOR; — didn’t need to declare a new type
TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
dept_cv deptcurtyp; — declare cursor variable

DECLARE
TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
tmp_cv TmpCurTyp; — declare cursor variable
TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
emp_cv EmpCurTyp; — declare cursor variable

DECLARE
dept_rec departments%ROWTYPE; — declare record variable
TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
dept_cv DeptCurTyp; — declare cursor variable

DECLARE
TYPE EmpRecTyp IS RECORD (
  employee_id NUMBER,
  last_name VARCHAR2(25),
  salary NUMBER(8,2));

TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; — declare cursor variable

DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
emp empcurtyp;
— after result set is built, process all the rows inside a single procedure
— rather than calling a procedure for each row

PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
  person employees%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE(‘—–‘);
  DBMS_OUTPUT.PUT_LINE(‘Here are the names from the result set:’);
  LOOP
    FETCH emp_cv INTO person;
    EXIT WHEN emp_cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(‘Name = ‘ || person.first_name || ‘ ‘ || person.last_name);
  END LOOP;
END;

BEGIN
— First find 10 arbitrary employees.
  OPEN emp FOR SELECT * FROM employees WHERE ROWNUM   process_emp_cv(emp);
  CLOSE emp;
— find employees matching a condition.
  OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE ‘R%’;
  process_emp_cv(emp);
  CLOSE emp;
END;
/