函数

函数用于返回特定数据

CREATE [OR REPLACE] FUNCTION func_name

[(arg1 [MODE] DATATYPE),

 (arg2 [MODE] DATATYPE)...]  --当指定参数类型时，不能指定其长度；MODE：IN、OUT、IN OUT(未指定时为IN)

RETURN DATATYPE   --用于指定返回值的数据类型，不能指定其长度

IS|AS

BEGIN 

    statement    --建立函数时，函数头必须包含带有RETURN子句，函数体内至少要包含一条RETURN子句

END;

一、无参函数

SQL>CREATE OR REPLACE FUNCTION GET_HELLO

RETURN VARCHAR2

IS

v_str VARCHAR2(12);

BEGIN

   SELECT 'HelloWorld' INTO v_str FROM DUAL;

   RETURN v_str;

END;

/

调用函数

因为函数有返回值，因此只能作为表达式的一部分进行调用

1、使用变量接受返回值

SQL> var str varchar2(12);

SQL> exec :str:=get_hello;

 

PL/SQL procedure successfully completed

str

---------

HelloWorld

2、使用SQL直接调用函数

SQL> select get_hello from dual;

 

GET_HELLO

--------------------------------------------------------------------------------

HelloWorld

3、使用包dbms_output调用函数

SQL> set serveroutput on;     --输出数据或信息

SQL>  exec dbms_output.put_line('你好:'||get_hello);

 

你好:HelloWorld

 

PL/SQL procedure successfully completed

二、带有IN参数的函数

    当定义函数时，如果不指定参数模式，则默认为输入参数，所以IN关键字即可指定也可以不指定

SQL>CREATE OR REPLACE FUNCTION get_sal(name VARCHAR2)

RETURN NUMBER

IS

v_sal emp.sal%type;

BEGIN

  SELECT sal INTO v_sal FROM emp WHERE upper(ename)=upper(name);

  RETURN v_sal;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

  RAISE_APPLICATION_ERROR(-20002,'没有该员工');

END;

存在名称

SQL> var salary number;

SQL> exec :salary:=get_sal('JAMES');

 

PL/SQL procedure successfully completed

salary

---------

950

 

SQL> exec :salary:=get_sal('scott');

 

PL/SQL procedure successfully completed

salary

---------

3000

 

不存在名称

SQL> exec :salary:=get_sal('sunvoid');

 

begin :salary:=get_sal('sunvoid'); end;

 

ORA-20002: 没有该员工

ORA-06512: 在 "UIADM.GET_SAL", line 10

ORA-06512: 在 line 2

三、带有OUT参数的函数

一般函数只需要返回单个数据，如果需要同时返回多个数据，需使用输出参数

SQL>CREATE OR REPLACE FUNCTION get_sal(eno VARCHAR2,salary OUT NUMBER)

RETURN NUMBER

IS

BEGIN

  SELECT sal INTO salary FROM emp WHERE empno=eno;

  RETURN salary;

EXCEPTION 

  WHEN NO_DATA_FOUND THEN

  RAISE_APPLICATION_ERROR(-20002,'没有该员工');

END;

/

SQL> var v_sal number;

SQL> exec :v_sal:=get_sal('1001',:salary);

 

PL/SQL procedure successfully completed

v_sal

---------

800

salary

---------

800

四、带有IN OUT参数的函数

在调用函数之前，需要通过变量给IN OUT参数传递数据，函数调用完成后，Oracle会将参数的返回结果通过该变量传递回来

SQL>CREATE OR REPLACE FUNCTION get_max(num1 IN OUT NUMBER,num2 IN OUT NUMBER)

RETURN NUMBER

IS

v_max NUMBER;

v_min NUMBER;

BEGIN

  IF num1<num2 THEN

    v_max:=num2;

    v_min:=num1;

  ELSE 

    v_max:=num1;

    v_min:=num2;

   END IF;

   num1:=num1/10;

   num2:=num2/10;

   RETURN v_max;

END;

/

SQL> var result1 number;

SQL> var result2 number;

SQL> var max number;

SQL> exec :result1:=10;

 

PL/SQL procedure successfully completed

result1

---------

10

 

SQL> exec :result2:=20;

 

PL/SQL procedure successfully completed

result2

---------

20

 

SQL> exec :max:=get_max(:result1,:result2);

 

PL/SQL procedure successfully completed

max

---------

20

result1

---------

1

result2

---------

2

五、函数调用限制

    函数必须要返回数据，所以只能作为表达式的一部分调用。

    在SQL语句中调用：

        SELECT命令的选择列表

        WHERE和HAVING子句中

        CONNECT BY，START WITH，ORDER BY以及GROUP BY子句中

        INSERT命令的Values子句中

        UPDATE命令的Set子句中

    在SQL语句中的限制

        SQL只能调用存储函数(服务器端)，不能调用客户端程序

        调用函数只能带有输入参数(IN),不能调(OUT)、(IN OUT)参数

        只能使用SQL支持的标准数据类型，不能使用PL/SQL特有数据类型(Boolean、Table、Record)

        函数不能包含INSERT、DELETE、UPDATE语句

六、查看函数源代码

函数建立之后，Oracle会将当前用户的函数名及源代码信息保存到USER_SOURCE数据字典中

SQL> desc user_source;

Name Type           Nullable Default Comments                                                                                                      

---- -------------- -------- ------- --------------

NAME VARCHAR2(30)   Y                Name of the object                                                                                            

TYPE VARCHAR2(12)   Y                Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",

                                     "PACKAGE", "PACKAGE BODY" or "JAVA SOURCE" 

LINE NUMBER         Y                Line number of this line of source                                                                            

TEXT VARCHAR2(4000) Y                Source text   

SQL>SELECT text FROM USER_SOURCE WHERE NAME='GET_SAL' AND TYPE='FUNCTION';

TEXT

--------------------------------------------------------------------------------

FUNCTION get_sal(eno VARCHAR2,salary OUT NUMBER)

RETURN NUMBER

IS

BEGIN

  SELECT sal INTO salary FROM emp WHERE empno=eno;

  RETURN salary;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

  RAISE_APPLICATION_ERROR(-20002,'没有该员工');

END;

七、删除函数

SQL>drop function get_sal;

Function dropped