oracle pl/sql之包(package)
oracle_package.sqlpackage:简化应用程序设计，提高应用性能，而且可以实现信息隐藏，子程序重载等功能。
包用于逻辑组合相关的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 packagebody 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
whenothers 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
whenothers 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;
---------------------------------------------------------------
页:
[1]