ORACLE创建包和包体及包中函数和存储过程

背景：在Oracle数据库创建包，在包创建函数Function和存储过程Procedure。在java项目中调用。

创建包，并在下面声明包含的函数和存储过程：

create or replace package fpbjcs.pkg_fpyzjh is

  -- Public function and procedure declarations
  function get_fpyzjh_by_fjdm(fromDate in varchar2, toDate in varchar2)
    return fpsl_tab;

  function get_hjje_by_fjdm(fromDate in varchar2, toDate in varchar2)
    return fpsl_tab;

  function get_fpyzjh_by_cyc(fromDate in varchar2, toDate in varchar2)
    return fpsl2_tab;

  function get_hjje_by_cyc(fromDate in varchar2, toDate in varchar2)
    return fpsl2_tab;
end pkg_fpyzjh;

解释说明：fpbjcs为Oracle表空间下的用户。pkg_fpyzjh为包名。function后面是函数名称，同时指定输入参数和返回类型。

PL/SQL块分为匿名块与命名块，命名块又包含子程序、包和触发器。

过程和函数统称为PL/SQL子程序，我们可以将商业逻辑、企业规则写成过程或函数保存到数据库中，以便共享。

过程和函数均存储在数据库中，并通过参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程不返回数据。

1. 存储过程概念

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中。经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

2. 创建过程

语法：

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
   [ ( parameter_declaration [, parameter_declaration ]... ) ]
   [ invoker_rights_clause ]
   { IS | AS }
   { [ declare_section ] body | call_spec | EXTERNAL} ;

说明：

procedure_name：过程名称。

parameter_declaration：参数声明，格式如下：

parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
          | { OUT | IN OUT } [ NOCOPY ] datatype 

IN：输入参数。

OUT：输出参数。

IN OUT：输入输出参数。

invoker_rights_clause：这个过程使用谁的权限运行，格式：

AUTHID { CURRENT_USER | DEFINER }

declare_section：声明部分。

body：过程块主体，执行部分。

一般只有在确认procedure_name过程是新过程或是要更新的过程时，才使用OR REPALCE关键字，否则容易删除有用的过程。

3. 使用过程参数

当建立过程时，既可以指定过程参数，也可以不提供任何参数。

过程参数包括输入参数、输出参数和输入输出参数，其中输入参数（IN）用于接收调用环境的输入数据，输出参数（OUT）用于将输出数据传递到调用环境，而输入输出参数（IN OUT）不仅要接收输入数据，而且还要输出数据到调用环境。

5. 函数介绍

函数是一段独立的PL/SQL程序代码，它执行某个特定的、明确的任务。通常，函数将处理从程序的调用部分传递给它的信息，然后返回单个值。信息通过称为参数的特殊标识符传递给函数，然后通过RETURN语句返回。

6. 创建函数

语法：

CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
  [ ( parameter_declaration [, parameter_declaration]... ) 
  ]
  RETURN datatype
  [ { invoker_rights_clause
    | DETERMINISTIC
    | parallel_enable_clause
    | RESULT_CACHE  [ relies_on_clause ]
    }...
  ]
  { { AGGREGATE | PIPELINED }  USING [ schema. ] implementation_type
  | [ PIPELINED ] { IS | AS } { [ declare_section ] body 
                              | call_spec
                              | EXTERNAL
                              }
  } ;

示例1：

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS
     acc_bal NUMBER(11,2);
   BEGIN 
      SELECT order_total INTO acc_bal FROM orders 
      WHERE customer_id = acc_no; 
      RETURN(acc_bal); 
    END;

函数参数也有输入、输出、输入输出三种模式：IN、OUT、IN OUT是形参的模式。若省略，则为IN模式。

和过程一样，IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。

OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。

IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。

调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。