oracle_package.sql

package:简化应用程序设计，提高应用性能，而且可以实现信息隐藏，子程序重载等功能。




包用于逻辑组合相关的PL/SQL types, variables, and subprograms。它由包头(package specification)和包体(package body)两部分组成。首先要建立包头，再建立包体。


包头只用来定义公共组件。

---包头语法


create or replace package pkg_name

is|as

public type and item declarations

subprogram specifications


end package_name;



Specify the package specification, which can contain type definitions, cursor declarations, variable declarations, constant declarations, exception declarations, PL/SQL subprogram specifications, and call specifications, which are declarations of a C or Java routine expressed in PL/SQL.



---包体语法


create or replace package body pkg_name

is|as

private type and item declarations

subprogram bodies


end pkg_name;




需求：

1.用过程来实现添加员工信息(empno,ename,sal,deptno)

2.用过程来实现删除某个员工

3.用函数来查询某个员工的薪水





Error(8,11): PLS-00323: subprogram or cursor 'PRO_DELETE_EMPLOYEE' is declared in a package specification and must be defined in the package body





1.包的建立




create or replace package body pkg_emp is


function fun_valid_deptno(v_deptno number) return boolean

is

   v_tmp number;

begin

   select 1 into v_tmp from dept where deptno=v_deptno;

   return true;

exception

when no_data_found then

   return false;

end;


procedure pro_add_emp(v_empno number,v_ename varchar2,v_salary number,v_deptno number default g_deptno)

is

begin

if fun_valid_deptno(v_deptno) then

   insert into emp(empno,ename,sal,deptno) values(v_empno,v_ename,v_salary,v_deptno);

else

   raise_application_error(-20001,'deptno not exist');

end if;


exception

when dup_val_on_index then

   raise_application_error(-20002,'empno is already exist');

end;



procedure pro_delete_employee(v_empno number)

is

begin

delete from emp where empno=v_empno;

if sql%notfound then

   raise_application_error(-20003,'empno is not exist.');

end if;

end;



function fun_get_sal(v_empno number) return number

is

v_sal number;

begin

select sal into v_sal from emp where empno=v_empno;

return v_sal;


exception

when others then

   raise_application_error(-20003,'empno is not exist');

end;


end pkg_emp;



2.包的重载特性


--------------------------------------------------------

---package code

create or replace package pkg_emp is


g_deptno number :=20;


procedure pro_add_emp(v_empno number,v_ename varchar2,v_salary number,

v_deptno number default g_deptno );


procedure pro_delete_employee(v_empno number);


procedure pro_delete_employee(v_ename varchar2);



function fun_get_sal(v_empno number) return number;


function fun_get_sal(v_ename varchar2) return number;



end pkg_emp;


--------------------------------------------------------

---package body code


--------------------------------------------------------

create or replace package  body pkg_emp is



function fun_valid_deptno(v_deptno number) return boolean is

v_tmp number;

begin



select 1 into v_tmp from dept where deptno=v_deptno;

return true;

exception when no_data_found then

return false;


end;



procedure pro_add_emp(v_empno number,v_ename varchar2,v_salary number,

v_deptno number default g_deptno )



is

begin

if fun_valid_deptno(v_deptno) then

INSERT INTO emp(empno,ename,sal,deptno) values(v_empno,v_ename,v_salary,v_deptno);

else

raise_application_error(-20001,'deptno is not exist.');

end if;

exception when dup_val_on_index then

raise_application_error(-20002,'empno is exist.');



end;


procedure pro_delete_employee(v_empno number)

is

begin

delete from emp where empno=v_empno;

if sql%notfound then

raise_application_error(-20003,'empno is not exist.');

end if;

end;



procedure pro_delete_employee(v_ename varchar2)

is

begin

delete from emp where ename=v_ename;

if sql%notfound then

raise_application_error(-20003,'ename is not exist.');

end if;

end;





function fun_get_sal(v_empno number) return number

is

v_sal emp.sal%type;


begin

select sal into v_sal from emp where empno=v_empno;

return v_sal;

exception

when  others then

raise_application_error(-20003,'empno is not exist.');

end;



function fun_get_sal(v_ename varchar2) return number

is

v_sal emp.sal%type;


begin

select sal into v_sal from emp where ename=v_ename;

return v_sal;

exception

when  others then

raise_application_error(-20003,'ename is not exist.');

end;

end pkg_emp;


--------------------------------------------------------





3.包的构造过程



在包中定义全局变量后，需要初始化全局变量。此时可以使用包构造过程，包的构造过程没有任何名称，它是在包体中实现了包的其他子程序之后，以BEGIN开始，以END结束.


---------------------------------------------------


---package code

create or replace PACKAGE pkg_sal is

v_minsal number(6,2);

v_maxsal number(6,2);


procedure pro_update_sal(v_sal number,v_empno number);

procedure pro_update_sal(v_sal number,v_ename varchar2);


end pkg_sal;

-----------------------------------------------


---package body code


create or replace PACKAGE body pkg_sal is


 procedure pro_update_sal(v_sal number, v_empno number) is

 begin

   if v_sal between v_minsal and v_maxsal then

     update emp set sal = v_sal where empno = v_empno;


     if sql%notfound then

       raise_application_error(-20001, 'empno is not exist.');

     end if;


   else

     raise_application_error(-20001, 'salary is not range.');


   end if;


 end;


 procedure pro_update_sal(v_sal number, v_ename varchar2)


  is

 begin

   if v_sal between v_minsal and v_maxsal then

     update emp set sal = v_sal where ename = v_ename;


     if sql%notfound then

       raise_application_error(-20002, 'ename is not exist.');


     end if;


   else

     raise_application_error(-20001, 'salary is not range.');


   end if;

 end;


begin

 select min(sal), max(sal) into v_minsal, v_maxsal from emp;


end pkg_sal;

---------------------------------------------------------------