Oracle PL/SQL Package

Packages are used to logically combine procedures and functions, which consist of package specifications and package bodies.
1), we can use the Create Package command for creating packages such as:
I, create a package sp_package
II. Declare that the package has a process update_sal
III. Declare that the package has a function annual_income

--declares that the package has a stored procedure and a function
Create Package Sp_package is
   Procedure Update_sal (name VARCHAR2, newsal number);
   function Annual_income (name VARCHAR2) return number;
End

2), set up the package body can use the Create packages Body command
Implement package Sp_package to package

CREATE OR REPLACE Package BODY Sp_package is
  --Stored procedures
  PROCEDURE update_sal (NAME VARCHAR2, newsal number) is
  BEGIN
     UPDATE EMP SET SAL = newsal WHERE ename = NAME;
     COMMIT;
  END;
  
  --function
  FUNCTION annual_income (NAME VARCHAR2) RETURN number is
     Annual_salary number;
  BEGIN
     SELECT SAL * + NVL (COMM, 0) into the annual_salary from EMP WHERE ename = NAME;
     RETURN annual_salary;
  END;
END;
/

3), how to call the package process or function
When calling the package's procedure or function, you need to have the package name before the procedure and function, and if you want to access packages for other scenarios, add the scheme name before the package name. Such as:

--Call the stored procedure
sql> exec sp_package.update_sal (' SCOTT ', 8888);
--Call function
var income number;
Call Sp_package. Annual_income (' SCOTT ') into:income;
Print income;

See also: http://www.cnblogs.com/linjiqin/archive/2012/02/24/2367167.html

Oracle PL/SQL Package