Oracle_PL_ SQL_ 教程：函数

--######################################--

--函数

--王林 2012.07.06

--######################################--

 

--PL/SQL中有值传递和引用传递函数，两种函数都返回输出值。

--函数输出值可以是任何SQL或PL/SQL数据类型

--可以在SQL语句中使用返回SQL数据类型的函数，返回PL/SQL数据类型的函数只能用于PL/SQL/块中。

--例外的是：不能在SQL查询中调用包含DML操作的存储函数,如果这样做会引发ORA-14551错误，说明不能在查询中使用DML

--不过，可在插入、更新和删除中调用执行DML操作的函数

--函数也可以包含嵌套命名块（他们是局部函数和过程）。可以在函数的声明块中定义命名块，同样可以在执行块中嵌套命名块。

--三种模式的形参：只读参数IN模式，只写形参OUT模式，用于读写形参的IN OUT模式

--通用形式：

CREATE [OR REPLACE] FUNCTION fun_name

[( arg1 [IN][OUT][NOCOPY] sql_datatype | plsql_datatype

 , arg2 [IN][OUT][NOCOPY] sql_datatype | plsql_datatype )]

RETURN { sql_datatype | plsql_datatype }

--PRAGMA必须在包规范而不是在包主体中定义，一个函数只能有一个PRAGMA，

[ AUTHID [ DEFINER ] [ CURRENT_USER ]]

[ DETERMINISTIC | PARALLEL_ENABLE ]

[ PIPELINED ]

--ORACLE11g引入跨会话结果缓存，通过RESULT_CACHE字句定义函数可以实现这一功能。对这些函数的每一个调用存储实参和结果。当函数被再次调用时，就可以寻找跨会话缓存中的结果，避免代码再次运行。其结果存储在SGA中。

[ RESULT_CACHE [ RELIES_ON table_name ]]

{ IS | AS }

BEGIN

    execution_statements

    RETURN VARIABLE;

[ EXCEPTION ]

    exception_handling_statements

END [ fun_name ];

 

--######################################--

/*

（1）DETERMINISTIC字句：用于保证函数对于任何输入总是以相同的方式工作，这一类型的保证要求函数不从外数据源（如包或者数据库）

读或者写数据。只有确定性函数（DETERMINISTIC function）工作在物化视图和基于函数的索引中。对于用于SQL语句字句（如WHERE、

ORDER BY或GROUP BY）中的用户定义的函数，或者SQL对象类型方法（MAP或ORDER）来说，他们是被推荐的方法。

确定性函数通常用完全相同的方式处理参数。这意味着不管你提交什么值，函数都以相同的方式工作，不应对包变量或来自数据库的数据有内部依赖性*/

 

--Demo1:计算投资的现值：

CREATE OR REPLACE FUNCTION pv

( future_value NUMBER

, periods      NUMBER

, interest     NUMBER )

RETURN NUMBER DETERMINISTIC 

IS

BEGIN

    RETURN future_value/((1+interest)**periods);

END pv;

--SQL Command->编译->执行：

var res NUMBER

call pv(10000,5,6) into :res;

--结果：

Method called

res

---------

0.594990182661986

 

--######################################--

/*

(2)PARALLEL_ENABLE字句：让函数支持并行查询能力。这一类型的保证要求函数不从外数据源（如包或者数据库）读或者写数据。

可以考虑为并行操作指定安全的函数以提高吞吐量，但ORACLE11g优化器也可能在认为函数对于并行操作来说安全时才运行未指定的函数。

JAVA方法和任何外部C程序对于并行操作来说从来都是不安全的。

物化视图：和关系数据库中的标准视图不一样，物化视图是缓存的结果集。作为缓存的结果集，他存储为具体表、

物化视图对查询能作出更快的响应，因为他不要求每次都用资源动态构建视图。代价就是物化视图通常有些过时，因为从视图缓存时到被访问时，

底部数据可能发生了改变，如果底层数据表不是很频繁的改变，而查询速度很重要，可以考虑使用物化视图，比如数据库仓库。

*/

 

--Demo2:下列函数支持并行SQL操作，将姓、名和中间名合并为单个字符：

CREATE OR REPLACE FUNCTION pv

( last_name   VARCHAR2 

, first_name  VARCHAR2

, middle_initial VARCHAR2)

RETURN VARCHAR2 PARALLEL_ENABLE

IS

BEGIN

    RETURN last_name

        || ','

        || first_name

        || ' '

        || middle_initial;

END;

--SQL Command->编译->执行：

SELECT pv(last_name , first_name, middle_initial) AS full_name 

  FROM contact 

 ORDER BY last_name,first_name,middle_initial;

--并行操作并不总发生，对于较小的数据集来说，并行操作是昂贵的。ORACLE11G优化器会判断何时以并行模式运行操作。有时，如果函数没有标记为启用并行，则优化器也会并行运行他。    

 

--######################################--

--函数参数的几种形式

 

--######################################--

--1.没有参数的函数

 

--Demo3:

CREATE OR REPLACE FUNCTION get_user

RETURN VARCHAR2 

IS

    v_user VARCHAR2 (20);

BEGIN

    SELECT a.username 

      INTO v_user

      FROM user_users a 

     WHERE ROWNUM < 2;

    RETURN v_user;

END get_user;

 

--######################

--调用函数时有形参的时候要括号，没有形参的时候不要括号

--函数用于返回特定数据。执行时的找一个变量接收函数的返回值

--调用方法1：SQL窗口

SELECT get_user FROM dual;

--调用方法2：命令窗口   

var v_name varchar2(50)   

exec :v_name := get_user;

--调用方法3：SQL窗口

exec dbms_output.put_line('The user is：'||get_user);      

 

--######################################--

--2.带有IN参数的函数       

 

--Demo4:

CREATE OR REPLACE FUNCTION pv(v_id IN NUMBER) 

RETURN VARCHAR2 

AS   

    v_name VARCHAR2 (50);   

BEGIN     

    SELECT a.ename INTO v_name 

      FROM scott.emp a 

     WHERE a.empno = v_id;   

    RETURN v_name;   

EXCEPTION    

    WHEN no_data_found THEN     

        raise_application_error(-20001, ' ID is wrong');   

END pv;  

--调用方法1：SQL窗口

SELECT pv(7536) FROM dual;

--调用方法2：命令窗口

exec dbms_output.put_line(pv(7536));

 

--######################################--

--3.带out参数的函数 

 

--Demo5:

CREATE OR REPLACE FUNCTION get_info

( e_name     VARCHAR2 

, job    OUT VARCHAR2 ) 

RETURN NUMBER 

IS  

    res NUMBER; 

BEGIN 

   SELECT a.sal

        , a.job 

     INTO res

        , job 

     FROM scott.emp a 

    WHERE a.ename = e_name; 

   RETURN (res); 

END get_info; 

--SQL Command窗口->编译->执行：

var job varchar2(20) 

var dname varchar2(20) 

exec :dname := get_info('SCOTT',:job) 

 

--######################################--

--4.带in out参数的函数 

 

--Demo6:

CREATE OR REPLACE FUNCTION demo

( num1        NUMBER 

, num2 IN OUT NUMBER ) 

RETURN NUMBER 

IS 

    v_result    NUMBER (6); 

    v_remainder NUMBER; 

BEGIN 

    v_result    := num1 / num2; 

    v_remainder := MOD (num1 , num2); 

    num2        := v_remainder; 

    RETURN (v_result); 

EXCEPTION

    WHEN zero_divide THEN 

        raise_application_error (-20000 , 'you have made one 0' ); 

END demo; 

--SQL Command->编译->执行：

var result1 NUMBER;

var result2 NUMBER;

exec :result2 :=30

--执行结果: 

PL/SQL procedure successfully completed

result2

---------

30

--继续->执行：

SQL> EXEC :result1 := demo (100 , :result2);

--执行结果: 

PL/SQL procedure successfully completed

result1

---------

3

result2

---------

10

 

--######################################--

/*

函数调用限制

1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数。

2、SQL只能调用带有输入参数,不能带有输出,输入输出参数的函数。

3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)。

4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句。

*/