带有局部功能的包装
============================
create or replace package my_pkg as
procedure my_proc(arg1 in varchar2);
function my_fun(arg1 in number) return varchar2;
end my_pkg;
包装体
===============
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;
注意:----在这种情况下,函数my_private_fun是函数主体中的本地函数,尚未在程序包中定义。
因此,此功能是包的本地功能,因此只能从包内部进行访问。
不能通过程序包名称进行引用。
在PL / SQL中,必须先定义过程和功能,然后再使用它。 其他明智的用户将得到编译错误。
包
==========
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;
全局变量的使用
-------------------------
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;
使用游标
---------------------------------
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;
----------------------------------
包装体
----------------------------------
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;
From: https://bytes.com/topic/oracle/insights/658653-pl-sql-package-4-a