适用范围,源表更新较为频繁。代替物化视图不能增量更新数据的缺点。
1.先建立一个表用于存储查询结果数据,如下所示:
CREATE TABLE emp_max AS
SELECT empno,ename,hiredate,deptno FROM
(SELECT empno,ename,hiredate,deptno,row_number() over(partition by deptno order by hiredate) row_num FROM emp)
WHERE row_num=1;
2.建立管道函数,实现数据增量更新操作:
CREATE OR REPLACE PACKAGE pkg_test
IS
TYPE test_table IS TABLE OF emp_max%rowtype;
FUNCTION func_test RETURN test_table PIPELINED;
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
IS
FUNCTION func_test RETURN test_table PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_lastdate DATE;
v_row emp_max%rowtype;
v_cur SYS_REFCURSOR;
BEGIN
SELECT MAX(hiredate) INTO v_lastdate FROM emp_max;
MERGE INTO emp_max a
USING
(
SELECT empno,ename,hiredate,deptno FROM
(SELECT empno,ename,hiredate,deptno,row_number() over(partition by deptno order by hiredate) row_num FROM emp WHERE