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

1. 按值传递函数

SQL> /* Formatted on 2018/12/9 14:23:51 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION hello_whom (pv_name IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     /* Default name value. */
  5     lv_name   VARCHAR2 (10) := 'World';
  6  BEGIN
  7     /* Check input name and substitute a valid value. */
  8     IF pv_name IS NOT NULL
  9     THEN
 10        lv_name := pv_name;
 11     END IF;
 12
 13     /* Return the phrase. */
 14     RETURN 'Hello ' || lv_name || '!';
 15  END;
 16  /

函数已创建。

2. 使用ACCESSIBLE BY子句为函数添加白名单（仅白名单中的函数、过程、类型、包等对象才能调用此函数，否则报错）

SQL> /* Formatted on 2018/12/9 14:31:13 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION hello_whom (pv_name IN VARCHAR2)
  2     RETURN VARCHAR2
  3     ACCESSIBLE BY (FUNCTION video.gateway,
  4                    PROCEDURE video.backdoor,
  5                    PACKAGE video.api,
  6                    TYPE video.hobbit)
  7  IS
  8     /* Default name value. */
  9     lv_name   VARCHAR2 (10) := 'World';
 10  BEGIN
 11     /* Check input name and substitute a valid value. */
 12     IF pv_name IS NOT NULL
 13     THEN
 14        lv_name := pv_name;
 15     END IF;
 16
 17     /* Return the phrase. */
 18     RETURN 'Hello ' || lv_name || '!';
 19  END;
 20  /

函数已创建。

SQL> SELECT hello_whom ('Pandeng') AS 打招呼 FROM DUAL;
SELECT hello_whom ('Pandeng') AS 打招呼 FROM DUAL
       *
第 1 行出现错误:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: 没有足够的权限访问对象 HELLO_WHOM

3. 函数的封装

SQL> /* Formatted on 2018/12/9 14:40:58 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION hello_whom (pv_name IN VARCHAR2)
  2     RETURN VARCHAR2
  3     ACCESSIBLE BY (FUNCTION gateway,
  4                    PROCEDURE backdoor,
  5                    PACKAGE api,
  6                    TYPE hobbit)
  7  IS
  8     /* Default name value. */
  9     lv_name   VARCHAR2(10) := 'World';
 10  BEGIN
 11  /* Check input name and substitute a valid value. */
 12  IF pv_name IS NOT NULL
 13  THEN
 14  lv_name := pv_name;
 15  END IF;
 16
 17  /* Return the phrase. */
 18  RETURN 'Hello ' || lv_name || '!';
 19  END;
 20  /

函数已创建。

SQL> /* Formatted on 2018/12/9 14:42:58 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION gateway (pv_name IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4  BEGIN
  5     /* Return the phrase. */
  6     RETURN hello_whom (pv_name);
  7  END;
  8  /

函数已创建。

SQL>  SELECT gateway ('Pandeng') AS 打招呼 FROM DUAL
  2  ;

打招呼
--------------------------------------------------------------------------------
Hello Pandeng!

4. 系统引用游标函数

SQL> /* Formatted on 2018/12/9 14:49:57 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION get_full_titles
  2     RETURN SYS_REFCURSOR
  3  IS
  4     lv_title_cursor   SYS_REFCURSOR;
  5  BEGIN
  6     OPEN lv_title_cursor FOR SELECT item_title, item_subtitle FROM item;
  7
  8     RETURN lv_title_cursor;
  9  END;
 10  /

函数已创建。

SQL> /* Formatted on 2018/12/9 15:00:14 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     /* Declare a record and collection type. */
  3     TYPE full_title_record IS RECORD
  4     (
  5        item_title      item.item_title%TYPE,
  6        item_subtitle   item.item_subtitle%TYPE
  7     );
  8
  9     lv_full_title_table   FULL_TITLE_RECORD;
 10     /* Declare a system reference cursor variable. */
 11     lv_title_cursor       SYS_REFCURSOR;
 12  BEGIN
 13     /* Assign the reference cursor function result. */
 14     lv_title_cursor := get_full_titles;
 15
 16     /* Print one element at a time. */
 17     LOOP
 18        FETCH lv_title_cursor INTO lv_full_title_table;
 19
 20        EXIT WHEN lv_title_cursor%NOTFOUND;
 21        DBMS_OUTPUT.put_line (
 22           'Title [' || lv_full_title_table.item_title || ']');
 23     END LOOP;
 24  END;
 25  /
Title [The Patriot]
Title [The Patriot]
Title [Around the World in 80 Days]
Title [Casino Royale]
Title [Die Another Day]
Title [Tomorrow Never Dies]
Title [Brave Heart]
Title [Camelot]
Title [Scrooge]
Title [The Hunt for Red October]
Title [The Lord of the Rings - Fellowship of the Ring]
Title [The Lord of the Rings - The Return of the King]
Title [Pirates of the Caribbean - The Curse of the Black Pearl]
Title [Pirates of the Caribbean - At World's End]