Oracle DETERMINISTIC函数、PARALLEL_ENABLE函数、PIPELINED函数、RESULT_CACHE函数
1、DETERMINISTIC函数
-- 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;
2、PARALLEL_ENABLE函数
-- 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;
3、PIPELINED函数
-- 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; /
4、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;
posted on 2014-05-25 10:52 Sunny_NUAA 阅读(1616) 评论(0) 编辑 收藏 举报