Oracle数据库---包

--根据员工号或员工姓名获取员工的信息
--根据员工号或员工姓名删除员工的信息
--创建包规范
create or replace package overload_pkg
is
function get_info(eno number) return emp%rowtype;
function get_info(name varchar2) return emp%rowtype;

procedure del_emp(eno number);
procedure del_emp(name varchar2);
end;

--创建包体
create or replace package body overload_pkg
is
function get_info(eno number) return emp%rowtype
is
emp_record emp%rowtype;
begin
select * into emp_record from emp where empno = eno;
return emp_record;
exception
when no_data_found then
raise_application_error(-20020,'不存在此员工！');
end;

function get_info(name varchar2) return emp%rowtype
is
emp_record emp%rowtype;
begin
select * into emp_record from emp where ename = name;
return emp_record;
exception
when no_data_found then
raise_application_error(-20020,'不存在此员工！');
end;

procedure del_emp(eno number)
is
begin
delete from emp where empno = eno;
if sql%notfound then
raise_application_error(-20020,'不存在此员工');
end if;
end;

procedure del_emp(name varchar2)
is
begin
delete from emp where ename = name;
if sql%notfound then
raise_application_error(-20020,'不存在此员工');
end if;
end;
end;

--调用
--根据员工号查询员工信息
declare
emp_record emp%rowtype;
e_no_emp exception;
pragma exception_init(e_no_emp,-20020);
begin
emp_record:= overload_pkg.get_info(&no);
dbms_output.put_line('员工号：'||emp_record.empno||'姓名：'||emp_record.ename||'工资：'||emp_record.sal);
exception
when e_no_emp then
dbms_output.put_line(sqlerrm);
end;

--根据员工姓名查询员工信息
declare
emp_record emp%rowtype;
e_no_emp exception;
pragma exception_init(e_no_emp,-20020);
begin
emp_record:= overload_pkg.get_info('&name');
dbms_output.put_line('员工号：'||emp_record.empno||'姓名：'||emp_record.ename||'工资：'||emp_record.sal);
exception
when e_no_emp then
dbms_output.put_line(sqlerrm);
end;

--根据员工号删除员工信息
declare
e_no_emp exception;
pragma exception_init(e_no_emp, -20020);
begin
overload_pkg.del_emp(&no);
commit;
exception
when e_no_emp then
dbms_output.put_line(sqlerrm);
rollback;
end;

select * from emp;

--根据员工的姓名删除员工的信息
declare
e_no_emp exception;
pragma exception_init(e_no_emp, -20020);
begin
overload_pkg.del_emp('&name');
commit;
exception
when e_no_emp then
dbms_output.put_line(sqlerrm);
rollback;
end;