Eason [資料科學//Python學習/資料庫] & [拍片&剪片]

--建立人力資源管理表hr_man

create table hr_man (name varchar2(10),job varchar2(10),salary number,comm number);

create or replace package hr_pack as
--新增一名員工
function inert_emp (v_name varchar2,v_job varchar2,v_sal number,v_comm number)
return varchar2;
--刪除一名員工
procedure move_emp (p_name varchar2);
--增加員工薪資
procedure add_sal (p_name varchar2);
--增加員工comm
procedure add_comm (p_name varchar2);
end hr_pack;

create or replace package body hr_pack as
function inert_emp (v_name varchar2,v_job varchar2,v_sal number,v_comm number)
return varchar2
as
begin
insert into hr_man values(v_name,v_job,v_sal,v_comm);
return v_name;
end inert_emp;
procedure move_emp (p_name varchar2) is
begin
delete from hr_man where name=p_name;
end move_emp;
procedure add_sal (p_name varchar2) is
begin
update hr_man set salary=salary*1.5 where name=p_name;
end add_sal;
procedure add_comm (p_name varchar2) is
v_comm number;
invaild_comm exception;
begin
select comm into v_comm from hr_man where name=p_name;
if v_comm is null then
raise invaild_comm;
else
update hr_man set comm=v_comm+0.1;
end if;
exception
when invaild_comm then
dbms_output.put_line('此員工沒有comm');
end add_comm;

end hr_pack;

*****************

執行:

set serveroutput on;
begin
dbms_output.put_line(hr_pack.inert_emp('1234','rd',100000,null));   --funtion使用
hr_pack.move_emp('eason');  --程序使用
hr_pack.add_sal('aa');                      --程序使用
hr_pack.add_comm('1234');    --程序使用
end;