oracle pl/sql之包(package)

wxyfj

　　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;
　　---------------------------------------------------------------