최근 포토로그


오라클 테이블 함수

M$ 에서는 테이블형 함수는 함수하나만 만들면 족하지만,
위대하신 오라클 님하는 다르다.

만들게 많아요.

까불지말고 아래것들은 이걸 참조하야됭.

슬퍼요.

 

Pipelined Table Functions

Table functions are used to return collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be complete before they are returned causing bottlenecks during large Extraction Transformation Load (ETL) operations. Pipelining negates the need to build huge collections by piping rows out of the function as they are created, allowing subsequent processing to start before the entire collection is fully populated. This article gives a simple example of a regular and a pipelined table function.

In order to create a table function we need to define a table type in the database. In this example we also define a row type as our table contains multiple columns:

DROP FUNCTION get_all_objects;
DROP TYPE object_table_type;
DROP TYPE object_row_type;

CREATE TYPE object_row_type AS OBJECT (
OWNER VARCHAR2(30),
OBJECT_TYPE VARCHAR2(18),
OBJECT_NAME VARCHAR2(30),
STATUS VARCHAR2(7)
);
/

CREATE TYPE object_table_type AS TABLE OF object_row_type;
/

Next we create a regular table function which returns a collection containing a subset of the columns from ALL_OBJECTS:

CREATE OR REPLACE FUNCTION get_all_objects (
p_owner IN VARCHAR2 DEFAULT '%',
p_object_type IN VARCHAR2 DEFAULT '%',
p_object_name IN VARCHAR2 DEFAULT '%',
p_status IN VARCHAR2 DEFAULT '%')
RETURN object_table_type AS

v_tab object_table_type := object_table_type();
BEGIN
FOR cur IN (SELECT owner, object_type, object_name, status
FROM all_objects
WHERE owner LIKE p_owner
AND object_type LIKE p_object_type
AND object_name LIKE p_object_name
AND status LIKE p_status
ORDER BY owner, object_name, object_type)
LOOP
v_tab.extend;
v_tab(v_tab.last) := object_row_type(cur.owner, cur.object_type, cur.object_name, cur.status);
END LOOP;
RETURN v_tab;
END;
/
SHOW ERRORS

If we query this table function with the following statement the collection is fully populated before any rows are returned giving a lag of 5 seconds before any rows are returned to my SQL*Plus session:

SELECT *
FROM TABLE(get_all_objects) a;

If we now replace this function with the pipelined version below this time lag drops to approximately 2 seconds:

CREATE OR REPLACE FUNCTION get_all_objects (
p_owner IN VARCHAR2 DEFAULT '%',
p_object_type IN VARCHAR2 DEFAULT '%',
p_object_name IN VARCHAR2 DEFAULT '%',
p_status IN VARCHAR2 DEFAULT '%')
RETURN object_table_type PIPELINED AS
BEGIN
FOR cur IN (SELECT owner, object_type, object_name, status
FROM all_objects
WHERE owner LIKE p_owner
AND object_type LIKE p_object_type
AND object_name LIKE p_object_name
AND status LIKE p_status
ORDER BY owner, object_name, object_type)
LOOP
PIPE ROW(object_row_type(cur.owner, cur.object_type, cur.object_name, cur.status));
END LOOP;
RETURN;
END;




단일 테이블 행을 리턴할 때는 좀 더 간단하다.



DROP TABLE gradereport;
CREATE TABLE gradereport (student VARCHAR2(30), subject
VARCHAR2(30), weight NUMBER, grade NUMBER);

INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
INSERT INTO gradereport VALUES('Mark','Chemistry', 4,3);
INSERT INTO gradereport VALUES('Mark','Maths', 3,3);
INSERT INTO gradereport VALUES('Mark','Economics', 3,4);

CREATE OR replace TYPE gpa AS TABLE OF NUMBER;


CREATE OR replace FUNCTION weighted_average(input_values
sys_refcursor)
RETURN gpa PIPELINED IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
-- The function accepts a ref cursor and loops through all the input rows.
LOOP
FETCH input_values INTO weight, grade;
EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average.
total_weight := total_weight + weight;
total := total + grade*weight;
END LOOP;
PIPE ROW (total / total_weight);
-- The function returns a single result.
RETURN;
END;



-- The result comes back as a nested table with a single row.
-- COLUMN_VALUE is a keyword that returns the contents of a nested table.

select weighted_result.column_value from
table(weighted_average(cursor(select weight,grade from gradereport))) weighted_result;





덧글

댓글 입력 영역