Oracle开发包

最新推荐文章于 2022-07-10 14:26:16 发布

杨文寿

最新推荐文章于 2022-07-10 14:26:16 发布

阅读量912

点赞数

文章标签： oracle application function delete insert exception

本文链接：https://blog.csdn.net/Tender001/article/details/7457675

版权

建立包规范

create or replace package emp_package is
  g_deptno number(3):=30;
  procedure add_employee(eno number, ename varchar2,sal number,dno number default g_deptno);
  procedure fire_employee(eno number);
  function get_sal(eno number)return number;
end emp_package;---end 后面可以跟定义的名称

建立包体

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_employee
begin
  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_employee
begin
  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)is
begin
  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.调用公用变量

    
    
     
     declare
begin
 emp_package.g_deptno:=21;
end;
     
     


     
     3.调用公用过程

     
     
      
      declare
begin
 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) is
begin
  delete from emp where empno=eno;
  if sql%notfound then raise_application_error(-20020,'该雇员不存在');
  end if;
end fire_employee;-------------------------
procedure fire_employee(enames varchar2) is
begin
  delete from emp where upper(ename)=upper(enames);
  if sql%notfound then raise_application_error(-20020,'该雇员不存在');
  end if;
end fire_employee;
end overload;
  
---调用重载函数和重载过程
declare
begin
   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) is
begin
  delete from emp where empno=eno;
  if sql%notfound then raise_application_error(-20020,'该雇员不存在');
  end if;
end fire_employee;-------------------------
procedure fire_employee(enames varchar2) is
begin
  delete from emp where upper(ename)=upper(enames);
  if sql%notfound then raise_application_error(-20020,'该雇员不存在');
  end if;
end fire_employee;
end overload;
  
---调用重载函数和重载过程
declare
begin
   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)is
begin
  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)is
begin
  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)is
begin
  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;

----调用包公用组件
declare
begin
 emp_pro_package.add_employee(1311,'MARY',3001,20);
 emp_pro_package.upd_sal('marry',20);
 
end;





-----纯度级别
---WNDS用于限制函数不能修改数据库数据
--- WNPS 用于限制函数不能修改包变量
--- RNDS 用于限制函数不能读取数据库数据
--- RNPS  用于限制函数不能读取包变量