-- 作业:
-- 创建一个包 并调用包中包含向员工表
-- 更改指定员工姓名的函数
-- 查询指定员工姓名,工资的存储过程并且在调用中给工资涨30%
-- 插入一条记录的函数 (捕获并处理唯一性异常)
-- 删除指定员工记录的函数(捕获并处理数据不存在异常)
create or replace package dook_pack
is
DeptRec dept%rowtype;
function n_update(v_id emp.empno%type,v_name emp.ename%type)
return number;
procedure cun_sal(v_id emp.empno%type);
function n_insert(v_id emp.empno%type,v_name emp.ename%type,
v_sal emp.sal%type) return number;
function n_dal(v_id emp.empno%type)
return number;
end dook_pack;
--第一条
create or replace package body dook_pack
is
function n_update(v_id emp.empno%type,v_name emp.ename%type)
return number
is
begin
update emp set ename = v_name where empno = v_id;
if sql%found then
return 1;
else
return -1;
end if;
exception
when others then
return 0;
end n_update;
--第二条
procedure cun_sal(v_id emp.empno%type)
is
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal+sal*0.3 into v_name,v_sal from emp where empno=v_id;
dbms_output.put_line(v_name||'---'||v_sal);
exception
when no_data_found then
dbms_output.put_line('该员工不存在');
end cun_sal;
--第三条
function n_insert(v_id emp.empno%type,v_name emp.ename%type,
v_sal emp.sal%type)
return number
is
v_number number;
no_remaraing exception;
pragma exception_init(no_remaraing,-1);
begin
insert into emp(empno,ename,sal) values(v_id,v_name,v_sal);
v_number := 1;
return v_number;
exception
when no_remaraing then
v_number:=-1;
return v_number;
when others then
v_number:=0;
return v_number;
end n_insert;
--第三条
function n_dal(v_id emp.empno%type)
return number
is
begin
delete from emp where empno=v_id;
if sql%found then
return 1;
else
return -1;
end if;
exception
when others then
return 0;
end n_dal;
begin
null;
end dook_pack;
--调用包
---第一条
declare
v_c number;
begin
v_c:=dook_pack.n_update(100,'ttg');
if v_c = 1 then
dbms_output.put_line('更改成功~~');
elsif v_c = -1 then
dbms_output.put_line('员工不存在,没更改');
else
dbms_output.put_line('其他异常');
end if;
end;
--第二条
begin
dook_pack.cun_sal(7499);
end;