Pl/sql-package - 4

[debasisdas]

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;