create or replace package body emp_package is function validate_deptno(v_deptno number)return boolean is ----validate_deptno 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 validate_deptno;procedure add_employee(eno number, ename varchar2,sal number,dno number default g_deptno)is---add_employeebegin if validate_deptno(dno)then insert into emp(empno,ename,sal,deptno)values(eno,ename,sal,dno); else RAISE_ApPLICATION_ERROR(-20000,'不存在该部门'); end if;exception when dup_val_on_index then RAISE_APpLICATION_ERROR(-20011,'该雇员已存在');end add_employee;procedure fire_employee(eno number)is --------fire_employeebegin delete from emp where empno=eno; if sql%notfound then RAISE_APPLICATION_ERROR(-20012,'该雇员不存在'); end if;end fire_employee;function get_sal(eno number)return number is ---get_sal v_sal emp.sal%type;create or replace package body emp_package is procedure add_employee(eno numberk,ename varchar2,salary number,dno number default g_deptno)isbegin if validate_deptno(dno)then insert into emp(empno,ename,sal,deptno)values(eno,ename,salary,dno); else raise_application_error(-20010,'不存在该部门'); end if;exception when dup_val_on_index then raise_application_error(-20011,'该雇员已存在');end;
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 get_sal;end emp_package;调用包组件
1.在同一个包内调用
create or replace package body emp_package is
procedure add_employee(eno numberk,ename varchar2,salary number,dno number default g_deptno)isbegin if validate_deptno(dno)then insert into emp(empno,ename,sal,deptno)values(eno,ename,salary,dno); else raise_application_error(-20010,'不存在该部门'); end if;exception when dup_val_on_index then raise_application_error(-20011,'该雇员已存在');end;2.调用公用变量
declarebegin emp_package.g_deptno:=21;end;
3.调用公用过程
declarebegin emp_package.add_employee(1212,'yang',2000,10);------部门不给值会报错 emp_package.add_employee(2121,'tender',2000,20);end;
4.调用公用函数
declare salary number;begin salary:=emp_package.get_sal(7788); dbms_output.put_line(salary);end;
----当使用其他用户身份调用公用组件时，必须在组件名前加用户名和包名作为前缀
----SCOTT.EMP_PACKAGE.。。。。
----当调用远程数据库包的公用组件是，在组件名前加包名作为前缀在组件名后需要带有数据库链名作为后缀
----EMP_PACKAGE.ADD_EMPLOYEE@TENDER(1111,'SCOTT',1233,10)
----查看源代码
----select text from user_source where name='emp_package'and type='package'使用包重载
---建立包规范create or replace package overload is function get_sal(eno number)return number; function get_sal(enames varchar2)return number; procedure fire_employee(eno number); procedure fire_employee(enames varchar2);end;----建立包体create or replace package body overload is function get_sal(eno 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(-20020,'该雇员不存在');end get_sal;--------------------------------function get_sal(enames varchar2)return number is v_sal emp.sal%type;begin select sal into v_sal from emp where upper(ename)=upper(enames); return v_sal;exception when no_data_found then raise_application_error(-20020,'该雇员不存在');end get_sal;------------------------------------procedure fire_employee(eno number) isbegin delete from emp where empno=eno; if sql%notfound then raise_application_error(-20020,'该雇员不存在'); end if;end fire_employee;-------------------------procedure fire_employee(enames varchar2) isbegin delete from emp where upper(ename)=upper(enames); if sql%notfound then raise_application_error(-20020,'该雇员不存在'); end if;end fire_employee;end overload; ---调用重载函数和重载过程declarebegin dbms_output.put_line(overload.get_sal('yang')); dbms_output.put_line(overload.get_sal(1111));end;
使用包构造过程
---建立包规范create or replace package emp_pro_package is minsal number(6,2); maxsal number(6,2); procedure add_employee(eno number,enams varchar2,salary number,dno number); procedure upd_sal(eno number,salary number); procedure upd_sal(nams varchar2,salary number);end;
----建立包体create or replace package body overload is function get_sal(eno 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(-20020,'该雇员不存在');end get_sal;--------------------------------function get_sal(enames varchar2)return number is v_sal emp.sal%type;begin select sal into v_sal from emp where upper(ename)=upper(enames); return v_sal;exception when no_data_found then raise_application_error(-20020,'该雇员不存在');end get_sal;------------------------------------procedure fire_employee(eno number) isbegin delete from emp where empno=eno; if sql%notfound then raise_application_error(-20020,'该雇员不存在'); end if;end fire_employee;-------------------------procedure fire_employee(enames varchar2) isbegin delete from emp where upper(ename)=upper(enames); if sql%notfound then raise_application_error(-20020,'该雇员不存在'); end if;end fire_employee;end overload; ---调用重载函数和重载过程declarebegin dbms_output.put_line(overload.get_sal('yang')); dbms_output.put_line(overload.get_sal(1111));end;/*..使用包构造过程*/ ---建立包规范create or replace package emp_pro_package is minsal number(6,2); maxsal number(6,2); procedure add_employee(eno number,enams varchar2,salary number,dno number); procedure upd_sal(eno number,salary number); procedure upd_sal(nams varchar2,salary number);end;---建立包体create or replace package body emp_pro_package is procedure add_employee(eno number,names varchar2,salary number,dno number)isbegin if salary between minsal and maxsal then insert into emp(empno,ename,sal,deptno)values(eno,names,salary,dno); else raise_application_error(-20001,'工资不在范围内'); end if;exception when dup_val_index then raise_application_error(-20002,'该雇员已经存在');end;---------------------------procedure upd_sal(eno number,salary number)isbegin if salary between minsal and maxsal then update emp set sal=salary where empno=eno; if sql%notfound then raise_application_error(-20003,'该雇员不存在'); end if; else raise_application_error(-20001,'工资不在范围内'); end if; exception when dup_val_index then raise_application_error(-20002,'该雇员已经存在');end;--------------------------------procedure upd_sal(names varchar2,salary number)isbegin if salary between minsal and maxsal then update emp set sal=salary where upper(ename)=upper(names); if sql%notfound then raise_application_error(-20003,'该雇员不存在'); end if; else raise_application_error(-20001,'工资不在范围内'); end if; exception when dup_val_index then raise_application_error(-20002,'该雇员已经存在');end;----调用包公用组件declarebegin emp_pro_package.add_employee(1311,'MARY',3001,20); emp_pro_package.upd_sal('marry',20); end;-----纯度级别---WNDS用于限制函数不能修改数据库数据--- WNPS 用于限制函数不能修改包变量--- RNDS 用于限制函数不能读取数据库数据--- RNPS 用于限制函数不能读取包变量
