Oracle DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数

1、DETERMINISTIC函数

Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数
-- Create deterministic PV function.
CREATE OR REPLACE FUNCTION pv
( future_value NUMBER
, periods NUMBER
, interest NUMBER )
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN future_value / ((1 + interest/100)**periods);
END pv;
Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数

2、PARALLEL_ENABLE函数

Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数
-- Creates the parallel enabled MERGE function.
CREATE OR REPLACE FUNCTION merge
( last_name VARCHAR2
, first_name VARCHAR2
, middle_name VARCHAR2 )
RETURN VARCHAR2 PARALLEL_ENABLE IS
BEGIN
RETURN last_name ||, ||first_name|| ||middle_name;
END;
Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数


3、PIPELINED函数

Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数
-- Create a package specification with only structures to support pipelined functions.
CREATE OR REPLACE PACKAGE pipelining_library IS

  -- Create a record structure.
  TYPE common_lookup_record IS RECORD
  ( common_lookup_id NUMBER
  , common_lookup_type VARCHAR2(30)
  , common_lookup_meaning VARCHAR2(255));

  -- Create a PL/SQL collection type.
  TYPE common_lookup_table IS TABLE OF common_lookup_record;

  END pipelining_library;
/

-- Create a pipelined function for a row of data.
CREATE OR REPLACE FUNCTION get_common_lookup_record_table
( pv_table_name VARCHAR2, pv_column_name VARCHAR2 )
RETURN pipelining_library.common_lookup_table
PIPELINED IS

 -- Declare a local variables.
 lv_counter INTEGER := 1;
 lv_table PIPELINING_LIBRARY.COMMON_LOOKUP_TABLE := pipelining_library.common_lookup_table();

  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT common_lookup_id
    , common_lookup_type
    , common_lookup_meaning
    FROM common_lookup
    WHERE common_lookup_table = UPPER(table_name_in)
    AND common_lookup_column = UPPER(table_column_name_in);

BEGIN
  FOR i IN c (pv_table_name, pv_column_name) LOOP
    lv_table.EXTEND;
    lv_table(lv_counter) := i;
    PIPE ROW(lv_table(lv_counter));
    lv_counter := lv_counter + 1;
  END LOOP;
END;
/

COLUMN common_lookup_id FORMAT 9999 HEADING "ID"
COLUMN common_lookup_type FORMAT A16 HEADING "Lookup Type"
COLUMN common_lookup_meaning FORMAT A30 HEADING "Lookup Meaning"

-- Query the values from the table.
SELECT *
FROM TABLE(get_common_lookup_record_table(ITEM,ITEM_TYPE));

-- Showing how to leverage a pipelined function return in a PL/SQL context.
DECLARE
  CURSOR cv_sample IS
    SELECT *
    FROM TABLE(get_common_lookup_record_table(ITEM,ITEM_TYPE));
BEGIN
  FOR i IN cv_sample LOOP
    dbms_output.put([||i.common_lookup_id||]);
    dbms_output.put([||i.common_lookup_type||]);
    dbms_output.put_line([||i.common_lookup_meaning||]);
  END LOOP;
END;
/
Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数

4、RESULT_CACHE函数

Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数
CREATE OR REPLACE FUNCTION get_common_lookup
( table_name VARCHAR2, column_name VARCHAR2 ) RETURN LOOKUP
RESULT_CACHE RELIES_ON(common_lookup) IS
  -- A local variable of the user-defined scalar collection type.
  lookups LOOKUP;

  -- A cursor to concatenate the columns into one string with a delimiter.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT common_lookup_id|||
    || common_lookup_type|||
    || common_lookup_meaning
    FROM common_lookup
    WHERE common_lookup_table = UPPER(table_name_in)
    AND common_lookup_column = UPPER(table_column_name_in);
BEGIN
  OPEN c(table_name, column_name);
  LOOP
    FETCH c BULK COLLECT INTO lookups;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  RETURN lookups;
END get_common_lookup;
Oracle 
DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数

 

Oracle DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数,布布扣,bubuko.com

Oracle DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数

上一篇:Oracle的pipelined函数实现高性能大数据处理


下一篇:MVC中的自定义控件