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;