Oracle创建存储过程、创建函数、创建包的实例

本文转自：http://www.cnblogs.com/helong/articles/2093807.html

一、Oracle创建存储过程

 1、基本语法

  
  

   
   create
   
    
   
   or
   
    
   
   replace
   
    
   
   procedure
   
    update_emp_sal
(Name 
   
   in
   
    out type,
 Name 
   
   in
   
    out type, ...
) 
   
   is
   
   

   
   begin
   
   
  

   
   end
   
    update_emp_sal;
  
  

2、写一个简单的例子修改emp表的ename字段

  
  

   
   create
   
    
   
   or
   
    
   
   replace
   
    
   
   procedure
   
    update_emp
(
v_empno  
   
   varchar2
   
   ,
v_ename  
   
   varchar2
   
   
) 
   
   is
   
   

   
   begin
   
   


   
   update
   
    emp 
   
   set
   
    ename
   
   =
   
   v_ename 
   
   where
   
    empno
   
   =
   
   v_empno;  


   
   end
   
    update_emp;
  
  

调用方法如下：

  
  

   
   SQL
   
   >
   
   exec
   
     update_emp(
   
   '
   
   7935
   
   '
   
   ,
   
   '
   
   test
   
   '
   
   );
  
  

2、有返回值的存储过程

就写一个简单的返回empno=7935的sal值

  
  

   
   create
   
    
   
   or
   
    
   
   replace
   
    
   
   procedure
   
    emp_out_sal
 (
 v_empno in 
   
   varchar2
   
   ,
 v_sal out 
   
   number
   
   
 ) 
   
   is
   
   
 vsal 
   
   number
   
   (
   
   7
   
   ,
   
   2
   
   );
 
   
   begin
   
   
 
 
   
   select
   
    sal 
   
   into
   
    vsal 
   
   from
   
    emp 
   
   where
   
    empno
   
   =
   
   v_empno;
 v_sal:
   
   =
   
   vsal;
 
   
   end
   
   ;
  
  

调用有返回值的过程

  
  

   
   SQL
   
   >
   
    
   
   var
   
    vsal 
   
   number
   
   
SQL
   
   >
   
    
   
   exec
   
    emp_out_sal(
   
   '
   
   7935
   
   '
   
   ,:vsal);

PL
   
   /
   
   SQL 
   
   procedure
   
    successfully completed
vsal

   
   --
   
   -------
   
   

   
   700
   
   

SQL
   
   >
   
    
   
   var
   
    vsal 
   
   number
   
   
SQL
   
   >
   
    call emp_out_sal(
   
   '
   
   7935
   
   '
   
   ,:vsal);

Method called
vsal

   
   --
   
   -------
   
   

   
   700
  
  

二、Oracle创建函数（function）

1、基本语法规则如下：

  
  

   
   create
   
    
   
   or
   
    
   
   replace
   
    
   
   function
   
    (Name 
   
   in
   
    type, Name 
   
   in
   
    type, ...) 
   
   return
   
    
   
   number
   
    
   
   is
   
   
  Result 
   
   number
   
   ;

   
   begin
   
   
  
  
   
   return
   
    (Result);

   
   end
   
    ;
  
  

2、写一个简单的查询例子查询出empno=7935的sal值

  
  

   
   create
   
    
   
   or
   
    
   
   replace
   
    
   
   function
   
    ret_emp_sal(v_ename 
   
   varchar2
   
   )

   
   return
   
    
   
   number
   
   

   
   is
   
   
v_sal 
   
   number
   
   (
   
   7
   
   ,
   
   2
   
   );

   
   begin
   
   

   
   select
   
    nvl(sal,
   
   0
   
   ) 
   
   into
   
    v_sal 
   
   from
   
    emp 
   
   where
   
    
   
   lower
   
   (ename)
   
   =
   
   lower
   
   (v_ename);

   
   return
   
    v_sal;

   
   end
   
   ;
  
  

调用此函数：

  
  

   
   SQL
   
   >
   
    
   
   var
   
    vsla 
   
   number
   
   
SQL
   
   >
   
    call ret_emp_sal
   
   (
   
   '
   
   7935
   
   '
   
   ) 
   
   into
   
    :vsal;

Method called
vsal

   
   --
   
   -------
   
   

   
   700
  
  

三、Oracle创建包

包用于组合逻辑相关的过程和函数，它由包规范和包体两个部分组成。包规范用于定义公用的常量、变量、过程和函数，创建包规范可以使用CREATE PACKAGE命令，创建包体可以使用CREATE PACKAGE BODY

1、创建包规范

  
  

   
    create
   
    package emp_pkg 
   
   is
   
   
 
   
   procedure
   
    emp_update_ename(v_empno 
   
   varchar2
   
   ,v_ename 
   
   varchar2
   
   );
 
   
   function
   
    emp_get_sal(v_empno 
   
   varchar2
   
   ) 
   
   return
   
    
   
   number
   
   ;
 
   
   end
   
   ;
  
  

2、创建包体

  
  

   
   create
   
    
   
   or
   
    
   
   replace
   
    package body emp_pkg

   
   is
   
   
    
   
   procedure
   
    emp_update_ename
    (
    v_empno 
   
   varchar2
   
   ,
    v_ename 
   
   varchar2
   
   
    )
    
   
   is
   
   
    vename 
   
   varchar2
   
   (
   
   32
   
   );
    
   
   begin
   
    
    
   
   update
   
    emp 
   
   set
   
    ename
   
   =
   
   v_ename 
   
   where
   
    empno
   
   =
   
   v_empno;
    
   
   commit
   
   ;
    
   
   select
   
    ename 
   
   into
   
    vename 
   
   from
   
    emp 
   
   where
   
    empno
   
   =
   
   v_empno;
   
    dbms_output.put_line(
   
   '
   
   雇员名称：
   
   '
   
   ||
   
   vename);
    
    
   
   end
   
   ;
    
    
   
   function
   
    emp_get_sal
    (
    v_empno 
   
   varchar2
   
   
    )
    
   
   return
   
    
   
   number
   
    
   
   is
   
   
    vsal 
   
   number
   
   (
   
   7
   
   ,
   
   2
   
   );
    
   
   begin
   
   
    
   
   select
   
    sal 
   
   into
   
    vsal 
   
   from
   
    emp 
   
   where
   
    empno
   
   =
   
   v_empno;
    
   
   return
   
    vsal;
    
   
   end
   
   ;

   
   end
   
   ;
  
  

在此提示，在没有创建包规范就创建包体，会失败，要使用包，必须先创建包规范，然后在创建包体

当要调用包的过程和函数时，在过程和函数的名称前加上包名作为前缀（包名.子程序名称），而如果要访问其他方案的包时需要在包的名称前加上方案的名称

（方案名称.包名.子程序名称）

实例：

  
  

   
   SQL
   
   >
   
    
   
   var
   
    vsla 
   
   number
   
   
SQL
   
   >
   
    call emp_pkg.emp_get_sal(
   
   '
   
   7935
   
   '
   
   ) 
   
   into
   
    :vsal;

Method called
vsal

   
   --
   
   -------
   
   

   
   700
   
   



SQL
   
   >
   
    
   
   exec
   
    emp_pkg.emp_update_ename(
   
   '
   
   7935
   
   '
   
   ,
   
   '
   
   helong
   
   '
   
   );

雇员名称：helong

SQL
   
   >
   
    call emp_pkg.emp_update_ename(
   
   '
   
   7935
   
   '
   
   ,
   
   '
   
   helong
   
   '
   
   );
Method called


   
   SQL
   
   >
   
    
   
   select
   
    
   
   *
   
    
   
   from
   
    dual;


   
   DUMMY
   
   

   
   --
   
   ---
   
   

   
   X

雇员名称：helong