PL/SQL温习十二 包
--建立包规范
create or replace package emp_package
is
g_deptno number(3) := 30; --全局变量
procedure add_employee( --过程
eno in number,
name in varchar2,
salary in number,
dno in number default g_deptno
);
procedure fire_employee(eno in number); --过程
function get_sal(eno in number) return number; --函数
end emp_package;
/
--建立包体
create or replace package body emp_package
is
function validate_deptno(v_deptno in 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 in number,
name in varchar2,
salary in number,
dno in 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 in number) is --过程
begin
delete from emp where empno = eno;
if sql%notfound then
raise_application_error(-20012,'该雇员不存在');
end if;
end;
function get_sal(eno in number) return number
is
v_sal emp.sal%type;
begin
select sal 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;
/
调用：
调用包变量：SQL> exec emp_package.g_deptno := 20;
调用包过程：SQL> exec emp_package.add_employee(1111,'lixin',2000);
调用包函数：
SQL> var salary number;
SQL> exec :salary := emp_package.get_sal(7788);
PL/SQL procedure successfully completed
salary
---------
3000
以其他身份调用包的公共组件：
SQL> exec lixin.emp_package.add_employee(1236,'lixin2',20);
远程调用包组件：
SQL> exec emp_package.add_employee@orcl(1236,'lixin2',20);
---------------------------------------------------------------
包纯度级别：
create or replace package my_package is
minsal number;
maxsal number;
function max_sal return number;
function min_sal return number;
pragma restrict_references(max_sal,wnps);
pragma restrict_references(min_sal,wnps);
end;
/
wnps:不能修改变量
wnds:不能修改数据库
rnds:不能读取数据库
rnps不能读取包变量