Oracle函数

1. 创建函数的语法格式

CREATE [OR REPLACE] FUNCTION [schema.]function_name
[ ( argument[IN] datatype…,)]
RETURN datatype  
{IS | AS}
[description part 说明部分]
BEGIN
 SQL STATEMENT 语句序列
 RETURN （表达式）
[EXCEPTION 例外处理]
END [function_name 函数名];

说明:

参数列表是可选的,且只允许有输入参数。
函数的返回类型是必需的。并且表达式要返回的数值类型应与函数定义的RETURN子句中指定的类型相同。
在一个函数中，可以使用多个RETURN语句，但是只有一个RENTURN语句被执行(返回值只有一个)。

函数使用的场合

函数可以使用在任何表达式中，可以用在以下场合：
（1）SELECT子句。
（2）WHERE子句。
（3）INSERT语句中的VALUES。
（4）UPDATE的SET子句。

2.创建不带参的函数

例1:创建一个不带参数的标量函数,用于查询orderdetails表中最高订购数量.

CREATE OR REPLACE FUNCTION MaxQuantity  
RETURN orderdetails.quantity%type  
IS
V_maxquantity orderdetails.quantity%type;
BEGIN
    SELECT max(quantity) INTO V_maxquantity
    FROM orderdetails ;
RETURN V_maxquantity;
EXCEPTION  
  WHEN NO_DATA_FOUND THEN    RETURN 0;
  WHEN OTHERS THEN   RETURN  -1;
END;
对无参函数的调用方法:
DECLARE
   v_maxquant number;
BEGIN
    SELECT MaxQuantity INTO v_maxquant
     FROM DUAL;
     IF  v_maxquant=0 THEN
          dbms_output.put_line(‘没有任何订购的产品’);
     ELSIF    v_maxquant=-1 THEN
         dbms_output.put_line(‘发生其他错误’);
      ELSE
     dbms_output.put_line(‘最高销售量为’|| v_maxquant);
   END IF;
END;

3.创建带参的函数

例2:创建一个带参数的标量函数,用于查询orderdetails表中某产品的订购总数量。

CREATE OR REPLACE FUNCTION T_Quantity(prodid number)  
RETURN orderdetails.quantity%type  
IS
V_quantity orderdetails.quantity%type;
BEGIN
    SELECT sum(quantity) INTO V_quantity
    FROM orderdetails WHERE productid=prodid
    Group by productid;
RETURN V_quantity;
EXCEPTION  
  WHEN NO_DATA_FOUND THEN    RETURN 0;
  WHEN OTHERS THEN   RETURN  -1;
END;

对以上定义的带参函数进行调用,并查询出产品编号为10号的产品的编号、名称、类别、总销量

DECLARE
 pid number;
 cid number;
 pname varchar2(40);
 total_quant number;
BEGIN
  SELECT productid,productname,categoryid, T_Quantity(10)
  INTO pid,pname,cid,total_quant   
  FROM products
  WHERE productid=10;
   IF  total_quant=0 THEN
     dbms_output.put_line(‘没有任何订购的产品’);
ELSIF    total_quant=-1 THEN
      dbms_output.put_line(‘发生其他错误’);
 ELSE
    dbms_output.put_line(‘产品编号为:'|| pid ||  ‘    产品类型为:‘ || cid || ‘    产品名为:‘ || pname || ‘  产品销售总量为:’ || total_quant);
 END IF;
END;

4.存储过程与函数的区别

1、参数形式及返回值不同

函数有零个或多个参数，并且只有一个返回值;过程有零个或多个参数，其返回值是靠OUT参数带出来的，可传出多个值。
过程和函数都可以有IN参数，通过参数列表接受参数的输入。
函数不能有OUT参数，函数值的返回是靠RETURN子句返回的;过程可以由零个或多个OUT参数返回结果。

2、调用形式不同

过程可以作为单独可执行语句一样被调用，可以在PL/SQL块中单独出现。
函数可以在任何表达式能够出现的地方被调用。

5.存储过程和函数的优点

1）提高数据的安全性和完整性
      利用安全性的权限来控制那些没有足够权限的用户对数据库的间接访问；
      通过把相关联的表的操作集中到一起，来保证针对这些相关表执行一致的操作或任何操作都不做；
（2）改善操作性能
     多个用户使用同一个SQL语句时，只做依次语法分析。只在编译时进行语法分析，运行时不再重做，直接调用编译编码。
（3）节省存储空间
     多个不同应用，有同一个存储代码维护性高
（4）模块化