开发PL/SQL子程序——包

---

包用于逻辑组合的pl/sql类型，项，子程序，它有包规范和包体两部分组成。

例子：建立包规范

create or replace package emp_package is
g_deptno number(3):=30;
procedure add_employee(eno number ,name varchar2,salary number,dno number default g_deptno);
procedure fire_employee(eno number);
function get_sal(eno number) return number;
end emp_package;
/

例子：建立包体

create or replace package body emp_package is
function validate_deptno(v_deptno number ) return boolean
is v_temp int;
begin 
select 1 into v_temp from dept where deptno=v_deptno;
return true;
exception when no_data_found then
return false;
end;
procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
is
begin
if validate_deptno(dno) then
insert into emp(empno,ename,sal,deptno) values (eno,name,salary ,dno);
else
raise_application_error(-20010,'不存在该部门');
end if;
exception when dup_val_on_index then
raise_application_error(-20011,'该雇员已经存在');
end;
procedure fire_employee(eno number) is
begin
delete from emp where empno=eno;
if sql%notfound then
raise_application_error(-20012,'该雇员不存在');
end if;
end;
function get_sal(eno number) return number 
v_sal emp.sal%type;
begin
selectsal into v_sal from emp where empno=eno;
return v_sal;
exception when no_data_found then
raise_application_error(-20012,'该雇员不存在');
end;
end emp_package;
/

---

exec emp_package.g_deptno:=20
exec emp-package.add_employee(1112,'CLERK',2000,10)
var salary number
exec :salary:=emp_package.get-sal97788)
printf salary

---

删除包：

drop packae emp_package

显示包:

select text from user_source
where name='EMP_PACKAGE' AND type ='PACKAGE';

---