7.7.2　函数语法

函数的创建语句结构如下：

01 CREATE[OR REPLACE]FUNCTION[schema.]function_name

02 [

03 (parameter_declaration[,parameter_declaration])

04 ]

05 RETURN datatype

06 {IS|AS}

07 [declare_section]

08 BEGIN

09 statement[statement|pragma]…

10 [EXCEPTION exception_handler[exception_handler]…]

11 END[name];

【语法说明】

❑[OR REPLACE]：覆盖同名函数。

❑FUNCTION：关键词，表示创建的是函数。

❑schema：模式名称。

❑function_name：函数名称。

❑parameter_declaration：函数的参数。参数有IN、OUT、IN OUT三种类型。

❑RETURN datatype：表示函数的返回类型。

❑{IS|AS}：二选一。该项之后是PL/SQL块。

❑declare_section：语句块部分的变量声明。

❑第9行表示语句或子程序。

❑第10行表示异常处理部分。

函数的作用是计算数据，并返回结果，所以在PL/SQL块中至少有一个RETURN语句。函数不能用来操作数据库，这一点和SQL内置函数一样。在当前模式下创建函数需要有CREATE PROCEDURE系统权限。

【示例28】创建函数示例

函数根据当前的产品数量对产品价格进行打折计算。如果产品数量低于50就打七五折，如果产品数量等于或高于50则打九折。脚本如下：

01 CREATE FUNCTION pric

02 (v_pric IN NUMBER,v_qnty IN NUMBER)

03 RETURN NUMBER

04 IS

05 BEGIN

06 IF v_qnty＜50 THEN

07 RETURN(v_pric*0.75);

08 ELSE

09 RETURN(v_pric*0.9);

10 END IF;

11 END;

12 /

【代码解析】

❑第1行表示创建名称为pric的函数。

❑第2行声明函数的变量v_pric和v_qnty，分别表示价格和数量。这两个参数为输入类型的参数。

❑第3行表示返回类型为数字型。

❑第6～9行为判断产品数量，并根据产品数量对价格进行打折计算。

【调用函数】

在SQL*Plus中执行函数脚本，如果执行成功，那么该函数就可以正常调用了。执行以下查询脚本：

SELECT productid,productname,productprice,pric(productprice,quantity)FROM productinfo;

【执行效果】

调用函数结果见图7.33。

图　7.33　查询中调用pric函数

自定义的函数同SQL内置函数使用方式区别不大，它作为表达式的一部分，可以在语句中使用，也可以在PL/SQL块中使用。但是它不可以像存储过程一样独立运行。关于存储过程，会在第10章专门对其做介绍。

函数利用RETURN可以返回一个参数，某种情况下需要得到函数内的多个数据，那么可以采用OUT类型参数的方法，使其满足需求。

【示例29】IN OUT类型参数示例

函数将使用IN OUT类型的参数，函数有两个参数分别是产品类型编码和价格，求出该产品类型下比指定价格高的产品的平均价格，并返回该范围内最少的产品数量。

01 CREATE FUNCTION AVG_PRIC(V_CTGRY IN VARCHAR2,—产品类型和指定价格

02 V_PRIC IN OUT VARCHAR2)RETURN NUMBER IS

03 V_QNTY NUMBER;—利用min函数得到的产品数量

04

05 BEGIN

06 IF V_PRIC IS NULL THEN

07 V_PRIC:=0;

08 END IF;

09

10 SELECT AVG(PRODUCTPRICE),MIN(QUANTITY)

11 INTO V_PRIC,V_QNTY

12 FROM PRODUCTINFO

13 WHERE CATEGORY=V_CTGRY

14 AND PRODUCTPRICE＞V_PRIC;

15

16 RETURN V_QNTY;

17

18 EXCEPTION

19 WHEN NO_DATA_FOUND THEN

20 DBMS_OUTPUT.PUT_LINE('没有对应的数据！');

21 WHEN TOO_MANY_ROWS THEN

22 DBMS_OUTPUT.PUT_LINE('对应数据过多，请确认！');

23 END;

24 /

【代码解析】

❑第1～2行表示创建函数AVG_PRIC，函数包含两个参数：V_CTGRY表示产品类型编码，是输入参数；V_PRIC表示指定的价格，是输入输出参数。函数本身返回类型为数值型。

❑第3行表示PL/SQL块内部变量，表示产品数量。

❑第6～8行判断如果参数V_PRIC为空，就默认为0。

❑第10～14行表示得到平均价格和最少的产品数量，并赋值到参数内。

❑第16行表示函数返回值是指定范围内的最少的产品数量。

❑第18～22行是异常捕捉块。

【调用函数】

函数执行成功后，在PL/SQL块内调用，只有这样才能获取OUT类型参数的值，否则只能得到利用RETURN返回的值。调用脚本如下：

DECLARE

V_CTGRY VARCHAR2(10):='0100030002';—指定的产品类型编码

V_PRIC VARCHAR2(20):=1500;—指定的价格

V_QNTY VARCHAR2(20);—数量

BEGIN

V_QNTY:=AVG_PRIC(V_CTGRY,V_PRIC);—调用函数，函数内部会把V_PRIC重新赋值

DBMS_OUTPUT.PUT_LINE('平均价格：'||V_PRIC);

DBMS_OUTPUT.PUT_LINE('最低的产品数量是：'||V_QNTY);

END;

【执行效果】

在PL/SQL块中执行以上脚本，执行过程见图7.34。

图　7.34　PL/SQL调用函数