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

 

1. 确定性按值传递函数

SQL> /* Formatted on 2018/12/9 15:09:37 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION fv (current_value    NUMBER := 0,
  2                                 periods          NUMBER := 1,
  3                                 interest         NUMBER)
  4     RETURN NUMBER
  5     DETERMINISTIC
  6  IS
  7  BEGIN
  8     /* Compounded Daily Interest. */
  9     RETURN   current_value
 10            * (1 + ( (1 + ( (interest / 100) / 365)) ** 365 - 1) * periods);
 11  END fv;
 12  /

函数已创建。

SQL> VARIABLE future_value NUMBER
SQL> CALL fv (current_value => 10000, periods => 5, interest => 4) INTO :future_value
  2  /

调用完成。


SQL> SELECT round(:future_value,2) 终值 FROM dual;

      终值
----------
  12040.42

2. WITH子句中嵌入函数

SQL> /* Formatted on 2018/12/9 15:15:27 (QP5 v5.256.13226.35538) */
SQL> WITH FUNCTION glue (pv_first_name VARCHAR2, pv_last_name VARCHAR2)
  2          RETURN VARCHAR2
  3       IS
  4          lv_full_name   VARCHAR2 (100);
  5       BEGIN
  6          lv_full_name := pv_first_name || ' ' || pv_last_name;
  7          RETURN lv_full_name;
  8       END;
  9
 10  SELECT glue (a.first_name, a.last_name) AS person
 11    FROM contact a;
 12  /

PERSON
--------------------------------------------------------------------------------
Randi Winn
Brian Winn
Oscar Vizquel
Doreen Vizquel
Meaghan Sweeney
Matthew Sweeney
Ian Sweeney
Clinton Ward
Brandt Ward
Moss Jane
Gretelz Jonah