Oracle Database package construction process instance

Oracle DatabasePackageConstruction ProcessThere is no name. It is the part that begins with begin and ends with end after other processes of the package are implemented. This article introduces an example of the construction process. Let's take a look at this part.

1. Baotou

 
 

  
  
create or replace package pkg_emp is  
  
  
  minsal number(6, 2);  
  
  
  maxsal number(6, 2);  
  
  
  procedure add_employee(eno    number,  
  
  
                         name   varchar2,  
  
  
                         salary number,  
  
  
                         dno    number);  
  
  
  procedure upd_sal(eno number, salary number);  
  
  
  procedure upd_sal(name varchar2, salary number);  
  
  
end pkg_emp; 
 
 

2. Package body

 
 

  
  
Create or replace package body pkg_emp is
  
  
 
  
  
Procedure add_employee (eno number,
  
  
Name varchar2,
  
  
Salary number,
  
  
Dno number) is
  
  
Begin
  
  
If salary between minsal and maxsal then
  
  
Insert into emp
  
  
(Empno, ename, sal, deptno)
  
  
Values
  
  
(Eno, name, salary, dno );
  
  
Else
  
  
Raise_application_error (-20001, 'salary out of range ');
  
  
End if;
  
  
Exception
  
  
When dup_val_on_index then
  
  
Raise_application_error (-20002, 'this employee already exists ');
  
  
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, 'employee No. Does not exist ');
  
  
End if;
  
  
Else
  
  
Raise_application_error (-20001, 'salary out of range ');
  
  
End if;
  
  
End;
  
  
 
  
  
Procedure upd_sal (name varchar2, salary number) is
  
  
Begin
  
  
If salary between minsal and maxsal then
  
  
Update emp set sal = salary where upper (ename) = upper (name );
  
  
If SQL % notfound then
  
  
Raise_application_error (-20004, 'employee No. Does not exist ');
  
  
End if;
  
  
Else
  
  
Raise_application_error (-20001, 'salary out of range ');
  
  
End if;
  
  
End;
 
 

3. Construction Process

 
 

  
  
begin    
  
  
select min(sal), max(sal) into minsal, maxsal from emp;    
  
  
end;  
 
 

Here is an introduction to the Oracle database package construction process instance. I hope this introduction will be helpful to you!