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

 

1. 非确定性按值传递函数（遗留问题：这段代码怎么测试呢？）

/* Formatted on 2018/12/9 20:44:04 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FUNCTION full_name (pv_search_name VARCHAR2)
   RETURN VARCHAR2
IS
   /* Declare local return variable. */
   lv_retval   VARCHAR2 (50);

   /* Declare a dynamic cursor. */
   CURSOR get_names (
      cv_search_name    VARCHAR2)
   IS
           SELECT c.first_name, c.middle_name, c.last_name
             FROM contact c
            WHERE    REGEXP_LIKE (c.first_name, cv_search_name, 'i')
                  OR REGEXP_LIKE (c.last_name, cv_search_name, 'i')
           OFFSET 1 ROWS
      FETCH FIRST 1 ROWS ONLY;                      -- New Oracle 12c feature.
BEGIN
   /* Check for a middle name. */
   FOR i IN get_names ('^.*' || pv_search_name || '.*$')
   LOOP
      IF i.middle_name IS NOT NULL
      THEN
         lv_retval :=
            i.first_name || ' ' || i.middle_name || ' ' || i.last_name;
      ELSE
         lv_retval := i.first_name || ' ' || i.last_name;
      END IF;
   END LOOP;
END;
/

2. 启用DML的按值传递函数

SQL> -- Create avatar table.
SQL> CREATE TABLE avatar
  2  ( avatar_id    NUMBER GENERATED AS IDENTITY
  3  , avatar_name  VARCHAR2(30));

表已创建。

SQL> -- Create add_avatar function.
SQL> CREATE OR REPLACE FUNCTION add_avatar
  2  ( pv_avatar_name  VARCHAR2 ) RETURN BOOLEAN IS
  3    /* Set function to perform in its own transaction scope. */
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5    /* Set default return value. */
  6    lv_retval  BOOLEAN := FALSE;
  7  BEGIN
  8    /* Insert row into avatar. */
  9    INSERT INTO avatar (avatar_name)
 10    VALUES (pv_avatar_name);
 11    /* Save change inside its own transaction scope. */
 12    COMMIT;
 13    /* Reset return value to true for complete. */
 14    lv_retval := TRUE;
 15    RETURN lv_retval;
 16  END;
 17  /

函数已创建。

SQL> -- Create an anonymous block program.
SQL> DECLARE
  2    /* Declare local variable. */
  3    lv_avatar  VARCHAR2(30);
  4    /* Declare a local cursor. */
  5    CURSOR capture_result
  6    (cv_avatar_name  VARCHAR2) IS
  7      SELECT   avatar_name
  8      FROM     avatar
  9      WHERE    avatar_name = cv_avatar_name;
 10  BEGIN
 11    IF add_avatar('Earthbender') THEN
 12      dbms_output.put_line('Record Inserted');
 13      ROLLBACK;
 14    ELSE
 15      dbms_output.put_line('No Record Inserted');
 16    END IF;
 17    OPEN capture_result('Earthbender');
 18    FETCH capture_result INTO lv_avatar;
 19    CLOSE capture_result;
 20    dbms_output.put_line('Value ['||lv_avatar||']');
 21  END;
 22  /

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL> /
Record Inserted
Value [Earthbender]

PL/SQL 过程已成功完成。

SQL> select * from avatar
  2  ;

 AVATAR_ID AVATAR_NAME
---------- ------------------------------
         1 Earthbender
         2 Earthbender

3. 递归函数

SQL> /* Formatted on 2018/12/9 21:10:04 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION factorial (n BINARY_DOUBLE)
  2     RETURN BINARY_DOUBLE
  3  IS
  4  BEGIN
  5     IF n <= 1
  6     THEN
  7        RETURN 1;
  8     ELSE
  9        RETURN n * factorial (n - 1);
 10     END IF;
 11  END factorial;
 12  /

函数已创建。

SQL> /* Formatted on 2018/12/9 21:13:13 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION "Fibonacci" (n BINARY_DOUBLE)
  2     RETURN BINARY_DOUBLE
  3  IS
  4  BEGIN
  5     /* Set the base case. */
  6     IF n < 2
  7     THEN
  8        RETURN n;
  9     ELSE
 10        RETURN fibonacci (n - 2) + fibonacci (n - 1);
 11     END IF;
 12  END "Fibonacci";
 13  /

函数已创建。


SQL> /* Formatted on 2018/12/9 21:13:53 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION FibonacciSequence
  2     RETURN VARCHAR2
  3  IS
  4     /* Declare an output variable. */
  5     lv_output   VARCHAR2 (40);
  6  BEGIN
  7     /* Loop through enough for the DaVinci Code myth. */
  8     FOR i IN 1 .. 8
  9     LOOP
 10        IF lv_output IS NOT NULL
 11        THEN
 12           lv_output :=
 13              lv_output || ', ' || LTRIM (TO_CHAR ("Fibonacci" (i), '999'));
 14        ELSE
 15           lv_output := LTRIM (TO_CHAR ("Fibonacci" (i), '999'));
 16        END IF;
 17     END LOOP;
 18
 19     RETURN lv_output;
 20  END;
 21  /

函数已创建。

SQL> SELECT FibonacciSequence from dual;

FIBONACCISEQUENCE
--------------------------------------------------------------------------------
1, 1, 2, 3, 5, 8, 13, 21

4. 按引用传递函数

SQL> /* Formatted on 2018/12/9 21:21:04 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION counter (
  2     pv_number         IN OUT INTEGER,
  3     pv_increment_by   IN     INTEGER DEFAULT 1)
  4     RETURN VARCHAR2
  5  IS
  6     /* Declare a return value. */
  7     lv_return   VARCHAR2 (50) := 'Inbound [';
  8  BEGIN
  9     /* Add inbound value. */
 10     lv_return := lv_return || pv_number || '] ';
 11
 12     /* Replace a null value to ensure increment. */
 13     IF pv_number IS NOT NULL
 14     THEN
 15        pv_number := pv_number + pv_increment_by;
 16     ELSE
 17        pv_number := 1;
 18     END IF;
 19
 20     /* Add inbound value. */
 21     lv_return := lv_return || 'Outbound [' || pv_number || ']';
 22
 23     /* Return increment_by module. */
 24     RETURN lv_return;
 25  END;
 26  /

函数已创建。

SQL> /* Formatted on 2018/12/9 21:22:53 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     /* Declare an increment by value. */
  3     lv_counter        INTEGER := 0;
  4     lv_increment_by   INTEGER := 1;
  5  BEGIN
  6     /* Loop through five times. */
  7     FOR i IN 1 .. 5
  8     LOOP
  9        DBMS_OUTPUT.put_line (
 10           'Counter [' || i || '] {' || counter (lv_counter) || '}');
 11     END LOOP;
 12  END;
 13  /
Counter [1] {Inbound [0] Outbound [1]}
Counter [2] {Inbound [1] Outbound [2]}
Counter [3] {Inbound [2] Outbound [3]}
Counter [4] {Inbound [3] Outbound [4]}
Counter [5] {Inbound [4] Outbound [5]}

PL/SQL 过程已成功完成。

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2     /* Declare an increment by value. */
  3     lv_counter        INTEGER := 0;
  4     lv_increment_by   INTEGER := 2;
  5  BEGIN
  6     /* Loop through five times. */
  7     FOR i IN 1 .. 5
  8     LOOP
  9        DBMS_OUTPUT.put_line (
 10           'Counter [' || i || '] {' || counter (lv_counter, lv_increment_by) || '}');
 11     END LOOP;
 12* END;
SQL> /
Counter [1] {Inbound [0] Outbound [2]}
Counter [2] {Inbound [2] Outbound [4]}
Counter [3] {Inbound [4] Outbound [6]}
Counter [4] {Inbound [6] Outbound [8]}
Counter [5] {Inbound [8] Outbound [10]}

PL/SQL 过程已成功完成。