PACKAGE WITH LOCAL FUNCTION
=============================
-
create or replace package my_pkg as
-
procedure my_proc(arg1 in varchar2);
-
function my_fun(arg1 in number) return varchar2;
-
end my_pkg;
PACKAGE BODY
===============
-
create or replace package body my_pkg as
-
-
function my_private_fun(arg1 in number) return varchar2
-
is
-
return_val varchar2(20);
-
begin
-
select col1 into return_val from tab2 where col2=arg1;
-
return return_val;
-
exception
-
when NO_DATA_FOUND THEN
-
return 'Sorry no data found.....!';
-
end my_private_fun;
-
-
function my_fun(arg1 in number)return varchar2
-
is
-
begin
-
return my_private_fun(arg1);
-
end my_fun;
-
-
procedure my_proc(arg1 in varchar2)
-
is
-
begin
-
update tab1 set col1=col1+1 where col2=arg1;
-
end my_proc;
-
end my_pkg;
Note:----In this case the function my_private_fun is a local function in the function body which is not been defined in the package. So this function is local to the package, so it can be accessed from within the package only. It cant be referenced through the package name.
and in PL/SQL the procedures and function must be defined before it is being used. Other wise user will get compilation error.
package
==========
-
CREATE OR REPLACE PACKAGE PACK
-
IS
-
P_ENAME EMP.ENAME%TYPE;
-
P_SAL EMP.SAL%TYPE;
-
P_JOB EMP.JOB%TYPE;
-
P_REC EMP%ROWTYPE;
-
CURSOR C IS SELECT * FROM EMP;
-
C_REC C%ROWTYPE;
-
PROCEDURE DEPT_INS
-
(
-
D_DEPTNO DEPT.DEPTNO%TYPE,
-
D_DNAME DEPT.DNAME%TYPE,
-
D_LOC DEPT.LOC%TYPE
-
);
-
FUNCTION ANSAL(ENO EMP.EMPNO%TYPE)
-
RETURN NUMBER;
-
END;
-
USE OF GLOBAL VARIABLES
-------------------------
-
BEGIN
-
SELECT ENAME,SAL,JOB INTO PACK.P_ENAME,PACK.P_SAL,PACK.P_JOB FROM EMP WHERE EMPNO=&NO;
-
DBMS_OUTPUT.PUT_LINE(PACK.P_ENAME||' '||PACK.P_SAL||' '||PACK.P_JOB);
-
END;
-
USE OF CURSOR
---------------------------------
-
BEGIN
-
OPEN PACK.C;
-
LOOP
-
FETCH PACK.C INTO PACK.C_REC;
-
EXIT WHEN PACK.C%NOTFOUND;
-
DBMS_OUTPUT.PUT_LINE(PACK.C_REC.ENAME);
-
-
END LOOP;
-
CLOSE PACK.C;
-
END;
-
----------------------------------
PACKAGE BODY
----------------------------------
-
CREATE OR REPLACE PACKAGE BODY PACK
-
IS
-
-
PROCEDURE DEPT_INS
-
(
-
D_DEPTNO DEPT.DEPTNO%TYPE,
-
D_DNAME DEPT.DNAME%TYPE,
-
D_LOC DEPT.LOC%TYPE
-
)
-
IS
-
BEGIN
-
INSERT INTO DEPT VALUES(D_DEPTNO,D_DNAME,D_LOC);
-
DBMS_OUTPUT.PUT_LINE('1 RECORD INSERTED....');
-
EXCEPTION
-
WHEN DUP_VAL_ON_INDEX THEN
-
DBMS_OUTPUT.PUT_LINE('DUPLICATE RECORD FOUND.....!');
-
WHEN OTHERS THEN
-
DBMS_OUTPUT.PUT_LINE('SOME OTHER ERROR......!');
-
END DEPT_INS;
-
-
FUNCTION ANSAL
-
(
-
ENO EMP.EMPNO%TYPE
-
)
-
RETURN NUMBER
-
AS
-
SALARY NUMBER(8);
-
BEGIN
-
SELECT (SAL+NVL(COMM,0))*12 INTO SALARY FROM EMP WHERE EMPNO=ENO;
-
RETURN SALARY;
-
END ANSAL;
-
END PACK;
-
