8,127
================================
PACKAGE
-----------------
Expand|Select|Wrap|Line Numbers
- create or replace package pack1
- as
- type mycur is ref cursor;
- end;
----------------------------
USING THE (GLOBAL) REFCURSOR IN A PROCEDURE
---------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- create or replace procedure testproc
- (
- no emp.deptno%type,
- rec out pack1.mycur
- )
- is
- begin
- open rec for select * from emp where deptno=no;
- end ;
CALLING THE PROCEDURE FROM ANNONYMOUS BLOCK
==============================================
Expand|Select|Wrap|Line Numbers
- declare
- mm pack1.mycur;
- ll emp%rowtype;
- begin
- testproc(10,mm);
- loop
- fetch mm into ll ;
- exit when mm%notfound;
- DBMS_OUTPUT.PUT_LINE(ll.empno||' ' ||ll.ename);
- end loop;
- end;
=================================
package
----------------
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE PACKAGE PKG_OVRLD
- AS
- FUNCTION TRANS_DATE(DT IN DATE)RETURN NUMBER;
- FUNCTION TRANS_DATE(DT IN NUMBER)RETURN DATE;
- END PKG_OVRLD;
------------------------
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE PACKAGE BODY PKG_OVRLD AS
- FUNCTION TRANS_DATE(DT IN DATE)RETURN NUMBER IS
- BEGIN
- RETURN ROUND((DT-TO_DATE('01011970','MMDDYYYY'))*86400);
- END TRANS_DATE;
- FUNCTION TRANS_DATE(DT IN NUMBER)RETURN DATE IS
- BEGIN
- RETURN (TO_DATE('01011970','MMDDYYYY') +(DT/(86400)));
- END TRANS_DATE;
- END PKG_OVRLD;
Also check PACKAGE - 4
