oracle 包创建

oracle 包的创建

一）创建基本包

--head

create or replace package pac_emp is
 procedure add_employee(eno number, name varchar2,salary number ,dno number);
 end pac_emp;

—body

create or replace package body pac_emp is
 function validate_deptno(v_deptno number)
 return boolean
 is
 v_temp int;
 begin
 select count(deptno) into v_temp from scott.dept where deptno=v_deptno;
 if v_temp>0 then
 return true;
 else
 return false;
 end if;
 end;
 procedure add_employee(eno number,name varchar2,salary number,dno number)
 is
 begin
 if validate_deptno(dno) then
 insert into scott.emp(empno,ename,sal,deptno)
 values(eno,name,salary,dno);
 else
 raise_application_error(-20010,'no');
 end if;
 exception
 when dup_val_on_index then
 raise_application_error(-20011,'yes');
 end;
 end pac_emp;

二）包的重载

--head

create or replace package overload is
function get_sal(eno number) return number;
function get_sal(name varchar2) return number;
procedure del_employee(eno number);
procedure del_employee(name varchar2);
end;

 

--body

create or replace package body overload is
function get_sal(eno number) return number
  is
  v_sal emp.sal%type;
  begin
    select sal into v_sal from emp where empno=eno;
    return v_sal;
    exception
      when no_data_found then
        raise_application_error(-20020,'no user');
 end;
 function get_sal(name varchar2) return number
   is
   v_sal emp.sal%type;
   begin
     select sal into v_sal from emp where ename=name;
     return v_sal;
     exception
       when no_data_found then
       raise_application_error(-20020,'no user name');
 end;
 procedure del_employee(eno number) is
   begin
     delete from emp where empno=eno;
     if sql%notfound then
       raise_application_error(-20020,'del no user');
       end if;
   end;
 procedure del_employee(name varchar2) is
   begin
     delete from emp where upper(ename)=upper(name);
     if sql%notfound then
       raise_application_error(-20200,'del name no user');
       end if;
     end;
 end;

SQL> declare
  2  v_sal number;
  3  v_sal2 number;
  4  begin
  5  v_sal:=overload.get_sal(2000);
  6  v_sal2:=overload.get_sal('rhys');
  7  dbms_output.put_line(v_sal);
  8  dbms_output.put_line(v_sal2);
  9  end;
 10  /
 
PL/SQL procedure successfully completed
 
SQL> set serveroutput on
SQL> r
 
3001
3001
 
PL/SQL procedure successfully completed
 
SQL> 

三）使用包构造过程

 

--判断工资是不是大于最小值小于最大值，然后在判断用户名是否存在，如果存在那么判断工资是不是在这个范围，如果不在这个范围那么就取消
--如果在这个范围，那么就进行更新工资。
--head
create or replace package emp_pack is
salmin number;
salmax number;
procedure ins_emp(v_empno emp.empno%type,v_ename emp.ename%type,v_deptno emp.deptno%type,v_sa emp.sal%type);
procedure change_sal(v_sal emp.sal%type,v_empn emp.empno%type);
procedure change_sal(v_ena emp.ename%type,v_sall emp.sal%type);
end;
--body

 
create or replace package body emp_pack
is
  
procedure ins_emp(v_empno emp.empno%type,v_ename emp.ename%type,v_deptno emp.deptno%type,v_sa emp.sal%type)
  is
  v_verdict varchar2(20);
  begin
    select count(ename) into v_verdict from emp where empno=v_empno;
    if v_verdict=0 then
      if v_sa between salmin and salmax then
        if v_deptno in(10,20,30,40) then
        insert into emp(empno,ename,deptno,sal) values(v_empno,v_ename,v_deptno,v_sa);
        else
          raise_application_error(-20001,'the deptno is not exist or the dup_val_on_index error');
        end if;
       else
         raise_application_error(-20002,'the sal is not between  maxvalue and minvalue');
      end if;
    else
      raise_application_error(-20003,'the user already exists');
    end if;
 exception
   when others then
     raise_application_error(-20004,'the error please check');
   
 end;
procedure change_sal(v_sal emp.sal%type,v_empn emp.empno%type)
  is
  begin
    if v_sal between salmin and salmax then
      update emp set sal=v_sal where empno=v_empn;
    else
      raise_application_error(-20005,'the sal is not between minvalue and maxvalue');
    end if;
end;
procedure change_sal(v_ena emp.ename%type,v_sall emp.sal%type)
  is
  begin
    if v_sall between salmin and salmax then
      update emp set sal=v_sall where upper(ename)=upper(v_ena);
    else
      raise_application_error(-20005,'the sal is not between minvalue and maxvalue');
    end if;
end;
begin
  select min(sal),max(sal) into salmin,salmax from emp;
  end;