《Oracle PL/SQL开发指南》学习笔记31——源码调试——函数和过程（第二部分，函数）

 

1. 命名块函数原型

[{EDITIONALBE | NONEDITIONALBE}] FUNCTION function_name
( parameter [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, ... ]]) RETURN [ sql_data_type | plsql_data_type ]
[ AUTHID [ DEFINER | CURRENT_USER ]]
[ DETERMINISTIC | PARALLEL_ENABLE ]
[ PIPELINED ]
[ACCESSIBLE BY
( [{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)
[,[{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)]
[,... ]]])
[ RESULT_CACHE [ RELIES ON table_name [, table_name [, ...]]]] IS
declaration_statements
BEGIN
execution_statements
RETURN variable;
[EXCEPTION]
exception_handling_statements
END [function_name];
/

SQL*PLUS中用实参进行函数调用的原型：

CALL function_name(parameter [, parameter [, ...]])
INTO target_variable_name;

如果没有强制参数，原型会有所不同：

CALL function_name INTO target_variable_name;

PL/SQL块中带有强制参数的赋值为：

target_variable_name :=
function_name(parameter [, parameter [, ...]]);

没有强制参数时括号可省略：

target_variable_name := function_name;

也可以将函数的返回值作为另一函数的实参返回（实际工作中有时需要多层嵌套函数）：

external_function_name(function_name( parameter
[, parameter [, ...]]));

2.Oracle Pragma限制选项

Option	Description
RNDS	The RNDS option guarantees a function reads no data state. This means you  cannot include a SQL query of any type in the function. It also cannot call  any other named block that includes a SQL query. A PLS-00452 error is  raised during compilation if you have a query inside the function’s program  scope that violates the PRAGMA restriction.
WNDS	The WNDS option guarantees a function writes no data state. This means you  cannot include SQL statements that insert, update, or delete data. It also  cannot call any other named block that includes a SQL query. A PLS-00452  error is raised during compilation if you have a DML statement inside the  function’s program scope that violates the PRAGMA restriction.
RNPS	The RNPS option guarantees a function reads no package state, which means  that it does not read any package variables. This means you cannot access a  package variable in the function. It also cannot call any other named block that  reads package variables. A PLS-00452 error is raised during compilation if  you have a query inside the function’s program scope that violates the PRAGMA  restriction.
WNPS	The WNPS options guarantees a function writes no package state, which means  that it does not write any values to package variables. This means you cannot  change package variables or call another named block that changes them. A  PLS-00452 error is raised during compilation if you have a statement inside  the function’s program scope that violates the PRAGMA restriction.
TRUST	The TRUST option instructs the function not to check whether called programs  enforce other RESTRICT_REFERENCES options. The benefit of this option  is that you can slowly migrate code to the new standard. The risks include  changing the behavior or performance of SQL statements. For reference, the  other options guard conditions necessary to support function-based indexes  and parallel query operations.

3. DETERMINISTIC子句（第一次接触） 

DETERMINISTIC子句可用于保证函数对于任意次相同的输入产生相同的输出结果（确定性结果，deterministic result），而不诸如缓存等因素的影响。

示例：

SQL> CREATE OR REPLACE FUNCTION pv
  2  ( future_value    NUMBER
  3  , periods         NUMBER
  4  , interest        NUMBER )
  5  RETURN NUMBER DETERMINISTIC IS
  6  BEGIN
  7    RETURN future_value / ((1 + interest/100)**periods);
  8  END pv;
  9  /

函数已创建。

SQL>    VARIABLE result NUMBER
SQL> CALL pv(3000000,20,6) INTO :result;

调用完成。

SQL> COLUMN money_today FORMAT 999,999,999.00
SQL> COLUMN money_today FORMAT $999,999,999.00
SQL> SELECT :result AS money_today
  2  FROM dual;

     MONEY_TODAY
----------------
     $935,414.18

顺便了解一下物化视图吧：

Materialized Views 

Unlike a standard view in a relational database, a materialized view is a cached result set.  As a cached result set, it is stored as a concrete table.  Materialized views are more responsive to queries because they don’t demand resources  to dynamically build the view each time. The trade-off is that materialized views are often  slightly out of date because underlying data may change between when the view is cached  versus it is accessed.  You can use function-based indexes in materialized views provided they use deterministic  functions. Deterministic functions always produce the same result value when called with  any set of actual parameters. They also guarantee that they don’t modify package variables  or data in the database.  Consider using materialized views when the underlying table data changes infrequently  and query speed is important. Materialized views are possible solutions when developing  data warehouse fact tables.  You use deterministic functions inside materialized views and function-based indexes. Both  materialized views and function-based indexes must be rebuilt when you change the internal  working of deterministic functions.