create or replace function get_cname(v_id int ) return varchar2 deterministic is f varchar2(200); begin select cname into f from t2 where object_id = v_id; return f; end; /
INSERT /*+ APPEND */ INTO TEST_TAB SELECT LEVEL, 'Description for ' || LEVEL, CASE WHEN MOD(level, 5) = 0 THEN 10 WHEN MOD(level, 3) = 0 THEN 20 ELSE 30 END FROM DUAL CONNECT BY LEVEL >= 500000; COMMIT;
SELECT NUM_COL, COUNT(*) FROM TEST_TAB GROUP BY NUM_COL ORDER BY NUM_COL;
SET SERVEROUTPUT ON BEGIN DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task'); EXCEPTION WHEN OTHERS THEN NULL; END; /
DECLARE l_task VARCHAR2(30) := 'test_task'; l_sql_stmt VARCHAR2(32767); l_try NUMBER; l_status NUMBER; BEGIN -- Create the TASK DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
-- Chunk the table by the ROWID DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID ( TASK_NAME => l_task, TABLE_OWNER => 'XB', /* Replace the TABLE_OWNER as appropriately */ TABLE_NAME => 'TEST_TAB', BY_ROW => TRUE, CHUNK_SIZE => 2500 );
-- DML to be execute in parallel l_sql_stmt := 'UPDATE test_tab t SET t.num_col = t.num_col + 10 WHERE rowid BETWEEN :start_id AND :end_id';
-- Run the task DBMS_PARALLEL_EXECUTE.RUN_TASK ( TASK_NAME => l_task, SQL_STMT => l_sql_stmt, LANGUAGE_FLAG => DBMS_SQL.NATIVE, PARALLEL_LEVEL => 10 );
-- If there is error, RESUME it for at most 2 times. l_try := 0; l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
WHILE(l_try > 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) LOOP l_try := l_try + 1; DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task); l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task); END LOOP;
-- Done with processing; drop the task DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM); END; /
DECLARE l_task VARCHAR2(30) := 'test_task'; l_sql_stmt VARCHAR2(32767); l_try NUMBER; l_status NUMBER; BEGIN -- Create the TASK DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
-- Chunk the table by the ID DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL ( TASK_NAME => l_task, TABLE_OWNER => 'XB', /* Replace the TABLE_OWNER value as appropriately */ TABLE_NAME => 'TEST_TAB', TABLE_COLUMN => 'REC_ID', CHUNK_SIZE => 2500 );
-- Procedure to be execute in parallel l_sql_stmt := 'BEGIN PROCESS_UPDATE(:start_id, :end_id); END;';
-- If there is error, RESUME it for at most 2 times. l_try := 0; l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
WHILE(l_try > 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) LOOP l_try := l_try + 1; DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task); l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task); END LOOP;
-- Done with processing; drop the task DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM); END; /
SET SERVEROUTPUT ON DECLARE l_chunk_sql VARCHAR2(1000); l_sql_stmt VARCHAR2(1000); l_try NUMBER; l_status NUMBER; BEGIN -- Create the TASK DBMS_PARALLEL_EXECUTE.CREATE_TASK ('test_task'); -- Chunk the table by NUM_COL l_chunk_sql := 'SELECT DISTINCT NUM_COL, NUM_COL FROM TEST_TAB'; DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL ( TASK_NAME => 'test_task', SQL_STMT => l_chunk_sql, BY_ROWID => false );
-- Execute the DML in parallel -- the WHERE clause contain a condition on num_col, which is the chunk -- column. In this case, grouping rows is by num_col. l_sql_stmt := 'UPDATE test_tab t SET t.num_col = t.num_col + 10 WHERE num_col BETWEEN :start_id AND :end_id'; DBMS_PARALLEL_EXECUTE.RUN_TASK ( TASK_NAME => 'test_task', SQL_STMT => l_sql_stmt, LANGUAGE_FLAG => DBMS_SQL.NATIVE, PARALLEL_LEVEL => 10 );
-- If there is error, RESUME it for at most 2 times. L_try := 0; L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
WHILE(l_try > 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED) LOOP L_try := l_try + 1; DBMS_PARALLEL_EXECUTE.RESUME_TASK('test_task'); L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task'); END LOOP;
-- Done with processing; drop the task DBMS_PARALLEL_EXECUTE.DROP_TASK('test_task'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM); END; /
CREATE TYPE t_tf_obj AS OBJECT ( id NUMBER, name VARCHAR2(50) ); /
CREATE TYPE t_tf_type IS TABLE OF t_tf_obj; /
CREATE OR REPLACE FUNCTION get_tab (cnt IN NUMBER) RETURN t_tf_type AS l_tab t_tf_type := t_tf_type(); BEGIN FOR i IN 1 .. cnt LOOP l_tab.extend; l_tab(l_tab.last) := t_tf_obj(i, 'Name is ' || i); END LOOP;
RETURN l_tab; END; /
XB@ora12c> SELECT * FROM TABLE(get_tab(10));
ID NAME ---------- ------------------------- 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5 6 Name is 6 7 Name is 7 8 Name is 8 9 Name is 9 10 Name is 10
10 rows selected.
如果版本是12.2及以上，可以连关键字TABLE都省掉
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
XB@ora12c> SELECT * FROM get_tab(10);
ID NAME ---------- -------------------------------- 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5 6 Name is 6 7 Name is 7 8 Name is 8 9 Name is 9 10 Name is 10
CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS BEGIN FOR i IN 1 .. cnt LOOP PIPE ROW(t_tf_obj(i, 'Name is ' || i)); END LOOP;
RETURN; END; /
XB@ora12c> SELECT * FROM get_tab_ptf(10);
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5 6 Name is 6 7 Name is 7 8 Name is 8 9 Name is 9 10 Name is 10
CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS BEGIN FOR i IN 1 .. cnt LOOP PIPE ROW(t_tf_obj(i, 'Name is ' || i)); END LOOP; RETURN; END; /
Function created.
XB@ora12c> XB@ora12c> SELECT * FROM get_tab_ptf(10) where rownum>=5;
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5
CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS BEGIN FOR i IN 1 .. cnt LOOP PIPE ROW(t_tf_obj(i, 'Name is ' || i)); END LOOP; RETURN; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('EXCEPTION'); RAISE; END; /
XB@ora12c> SELECT * FROM get_tab_ptf(10) where rownum>=5;
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5
CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS BEGIN FOR i IN 1 .. cnt LOOP PIPE ROW(t_tf_obj(i, 'Name is ' || i)); END LOOP; RETURN; EXCEPTION WHEN NO_DATA_NEEDED THEN DBMS_OUTPUT.put_line('NO_DATA_NEEDED'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.put_line('EXCEPTION'); RAISE; END; /
Function created.
XB@ora12c> XB@ora12c> SELECT * FROM get_tab_ptf(10) where rownum>=5;
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5
CREATE OR REPLACE FUNCTION get_stat RETURN NUMBER AS l_sum NUMBER; BEGIN SELECT a.value INTO l_sum FROM v$mystat a, v$statname b WHERE a.statistic# = b.statistic# AND b.name = 'session pga memory'; RETURN l_sum; END get_stat; /
declare v_begin number; v_end number; BEGIN v_begin:=sys.get_stat; FOR cur IN (SELECT * FROM TABLE(get_tab(10000))) LOOP NULL; END LOOP; v_end:=sys.get_stat; DBMS_OUTPUT.put_line('Begin memory is: '||v_begin); DBMS_OUTPUT.put_line('End memory is: '||v_end); DBMS_OUTPUT.put_line('Total memory used: '||(v_end-v_begin)); END; 16 / Begin memory is: 4753336 End memory is: 9013176 Total memory used: 4259840
set serveroutput on; declare v_begin number; v_end number; BEGIN v_begin:=sys.get_stat; FOR cur IN (SELECT * FROM TABLE(get_tab_ptf(10000))) LOOP NULL; END LOOP; v_end:=sys.get_stat; DBMS_OUTPUT.put_line('Begin memory is: '||v_begin); DBMS_OUTPUT.put_line('End memory is: '||v_end); DBMS_OUTPUT.put_line('Total memory used: '||(v_end-v_begin)); END; 16 / Begin memory is: 4032440 End memory is: 4032440 Total memory used: 0
XB@ora12c> SELECT * 2 FROM TABLE(get_tab_ptf(10));
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5 6 Name is 6 7 Name is 7 8 Name is 8 9 Name is 9 10 Name is 10
10 rows selected.
XB@ora12c> @x
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2gpbj20w5kmbn, child number 0 ------------------------------------- SELECT * FROM TABLE(get_tab_ptf(10))
XB@ora12c> select /*+ DYNAMIC_SAMPLING(2)*/ * FROM TABLE(get_tab_ptf(10));
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5 6 Name is 6 7 Name is 7 8 Name is 8 9 Name is 9 10 Name is 10
10 rows selected.
XB@ora12c> @x Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 76sppwrcf832x, child number 0 ------------------------------------- select /*+ DYNAMIC_SAMPLING(2)*/ * FROM TABLE(get_tab_ptf(10))