欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle数据库---包

程序员文章站 2022-04-15 14:49:20
--根据员工号或员工姓名获取员工的信息--根据员工号或员工姓名删除员工的信息--创建包规范CREATE OR REPLACE PACKAGE overload_pkgIS FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE; FUNCTION get_i ......

--根据员工号或员工姓名获取员工的信息
--根据员工号或员工姓名删除员工的信息
--创建包规范
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;