¶ÈÁ¿¿ìËÙ¿ª·¢Æ½Ì¨-רҵ¡¢¿ìËÙµÄÈí¼þ¶¨Öƿ쿪ƽ̨

±êÌâ: Oracle ÖÐ table º¯ÊýµÄÓ¦ÓÃdzÎö [´òÓ¡±¾Ò³]

×÷Õß: fteair    ʱ¼ä: 2020-2-3 18:14

×÷Õß: caixuqad    ʱ¼ä: 2020-4-9 17:59

×÷Õß: ³ÂÏþÁú    ʱ¼ä: 2020-7-9 16:29
±êÌâ: Oracle ÖÐ table º¯ÊýµÄÓ¦ÓÃdzÎö
±íº¯Êý¿É½ÓÊܲéѯÓï¾ä»òÓαê×÷ΪÊäÈë²ÎÊý£¬²¢¿ÉÊä³ö¶àÐÐÊý¾Ý¡£ÕâƪÎÄÕÂÖ÷Òª½éÉÜÁËOracle ÖÐ table º¯ÊýµÄÓ¦ÓÃdzÎö,ÐèÒªµÄÅóÓÑ¿ÉÒԲο¼Ï¡£




±íº¯Êý¿É½ÓÊܲéѯÓï¾ä»òÓαê×÷ΪÊäÈë²ÎÊý£¬²¢¿ÉÊä³ö¶àÐÐÊý¾Ý¡£¸Ãº¯Êý¿ÉÒÔƽÐÐÖ´ÐУ¬²¢¿É³ÖÐøÊä³öÊý¾ÝÁ÷£¬±»³Æ×÷¹ÜµÀʽÊä³ö¡£Ó¦ÓÃ±íº¯Êý¿É½«Êý¾Ýת»»·Ö½×¶Î´¦Àí£¬²¢Ê¡È¥Öмä½á¹ûµÄ´æ´¢ºÍ»º³å±í¡£


1. ÓÃÓα괫µÝÊý¾Ý
ÀûÓÃÓαê REF CURSOR ¿É½«Êý¾Ý¼¯£¨¶àÐмǼ£©´«µÝµ½PL/SQLº¯Êý£º
1
2
3
SELECT *
FROM TABLE (myfunction (CURSOR (SELECT *
         FROM mytab)));




2. ÀûÓÃÁ½¸öʵÌ廯ÊÓͼ£¨»ò±í£©×÷ΪÑù°åÊý¾Ý
1
2
3
4
5
6
7
8
9
10
11
CREATE MATERIALIZED VIEW sum_sales_country_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR, c.country_id country,
   SUM (sum_amount_sold) sum_amount_sold
FROM sum_sales_month_mv s, customers c
WHERE s.cust_id = c.cust_id
  AND c.country_id IN ('US', 'UK', 'FR', 'ES', 'JP', 'AU')
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4), c.country_id





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE MATERIALIZED VIEW sum_es_gend_mv
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR,
   s.calendar_month_desc cal_month, c.cust_gender,
   SUM (sum_amount_sold) sum_amount_sold
FROM sum_sales_month_mv s, customer c
WHERE s.cust_id = c.cust_id
  AND c.country_id = 'ES'
  AND sunstr (s.calendar_month_desc, 1, 4) = '2000'
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4),
   s.calendar_month_desc,
   c.cust_gender;



3. ¶¨Òå¶ÔÏóÀàÐͺͻùÓÚ¶ÔÏóÀàÐ͵ıíÀàÐÍ
¶¨Òå¶ÔÏóÀàÐͲ¢ÇÒΪ½øÒ»²½ÒýÓÃ×öºÃ×¼±¸¡£
£¨1£©¶¨Òå¶ÔÏóÀàÐÍ£ºTYPE sales_country_t
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE MATERIALIZED VIEW sum_es_gend_mv
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR,
   s.calendar_month_desc cal_month, c.cust_gender,
   SUM (sum_amount_sold) sum_amount_sold
FROM sum_sales_month_mv s, customer c
WHERE s.cust_id = c.cust_id
  AND c.country_id = 'ES'
  AND sunstr (s.calendar_month_desc, 1, 4) = '2000'
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4),
   s.calendar_month_desc,
   c.cust_gender;



£¨2£©¶¨Òå±íÀàÐÍ£ºTYPE SUM_SALES_COUNTRY_T_TAB
1
CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;



£¨3£©¶¨Òå¶ÔÏóÀàÐÍ£ºTYPE sales_gender_t
1
2
3
4
5
6
CREATE TYPE sales_gender_t AS OBJECT (
YEAR    VARCHAR2 (4),
country_id  CHAR (2),
cust_gender  CHAR (1),
sum_amount_sold NUMBER
);



£¨4£©¶¨Òå±íÀàÐÍ£ºTYPE SUM_SALES_GENDER_T_TAB
1
CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;



£¨5£©¶¨Òå¶ÔÏóÀàÐÍ£ºTYPE sales_roll_t
1
2
3
4
5
CREATE TYPE sales_roll_t AS OBJECT (
channel_desc  VARCHAR2 (20),
country_id  CHAR (2),
sum_amount_sold NUMBER
);



£¨6£©¶¨Òå±íÀàÐÍ£ºTYPE SUM_SALES_ROLL_T_TAB
1
CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;



£¨7£©¼ì²éһϽ¨Á¢µÄÀàÐÍ
1
2
3
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'TYPE';



4. ¶¨Òå°ü£ºCreate package and define REF CURSOR
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE PACKAGE cursor_pkg
I TYPE sales_country_t_rec IS RECORD (
  YEAR    VARCHAR (4),
  country   CHAR (2),
  sum_amount_sold NUMBER
);
TYPE sales_gender_t_rec IS RECORD (
  YEAR    VARCHAR2 (4),
  country_id  CHAR (2),
  cust_gender  CHAR (1),
  sum_amount_sold NUMBER
);
TYPE sales_roll_t_rec IS RECORD (
  channel_desc  VARCHAR2 (20),
  country_id  CHAR (2),
  sum_amount_sold NUMBER
);
TYPE sales_country_t_rectab IS TABLE OF sales_country_t_rec;
TYPE sales_roll_t_rectab IS TABLE OF sales_roll_t_rec;
TYPE strong_refcur_t IS REF CURSOR
  RETURN sales_country_t_rec;
TYPE row_refcur_t IS REF CURSOR
  RETURN sum_sales_country_mv%ROWTYPE;
TYPE roll_refcur_t IS REF CURSOR
  RETURN sales_roll_t_rec;
TYPE refcur_t IS REF CURSOR;
END corsor_pkg;



5. ¶¨Òå±íº¯Êý
£¨1£©¶¨Òå±íº¯Êý£ºFUNCTION Table_Ref_Cur_Week
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE OR REPLACE FUNCTION table_ref_cur_week (cur CURSOR.refcur_t)
RETURN sum_sales_country_t_tab
IS
YEAR    VARCHAR (4);
country   CHAR (2);
sum_amount_sold NUMBER;
objset   sum_sales_country_t_tab := sum_sales_country_t_tab ();
i     NUMBER     := 0;
BEGIN
LOOP
-- Fetch from cursor variable
  FETCH cur
  INTO YEAR, country, sum_amount_sold;
  EXIT WHEN cur%NOTFOUND;
      -- exit when last row is fetched
-- append to collection
  i := i + 1;
  objset.EXTEND;
  objset (i) := sales_country_t (YEAR, country, sum_amount_sold);
END LOOP;
CLOSE cur;
RETURN objset;
END;
/



£¨2£©¶¨Òå±íº¯Êý£ºFUNCTION Table_Ref_Cur_Strong
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE FUNCTION table_ref_cur_strong (cur cursor_pkg.strong_refcur_t)
RETURN sum_sales_country_t_tab PIPELINED
IS
YEAR    VARCHAR (4);
country   CHAR (2);
sum_amount_sold NUMBER;
i     NUMBER  := 0;
BEGIN
LOOP
  FETCH cur
  INTO YEAR, country, sum_amount_sold;
  EXIT WHEN cur%NOTFOUND;     -- exit when last row fetched
  PIPE ROW (sales_country_t (YEAR, country, sum_amount_sold));
END LOOP;
CLOSE cur;
RETURN;
END;
/



£¨3£©¶¨Òå±íº¯Êý£ºFUNCTION Table_Ref_Cur_row
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE FUNCTION table_ref_cur_row (cur cursor_pkg.row_refcur_t)
RETURN sum_sales_country_t_tab PIPELINED
IS
in_rec cur%ROWTYPE;
out_rec sales_country_t := sales_country_t (NULL, NULL, NULL);
BEGIN
LOOP
  FETCH cur
  INTO in_rec;
  EXIT WHEN cur%NOTFOUND;    -- exit when last row is fetched
  out_rec.YEAR := in_rec.YEAR;
  out_rec.country := in_rec.country;
  out_rec.sum_amount_sold := in_rec.sum_amount_sold;
  PIPE ROW (out_rec);
END LOOP;
CLOSE cur;
RETURN;
END;
/



£¨4£©¶¨Òå±íº¯Êý£ºFUNCTION Gender_Table_Ref_Cur_Week
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE FUNCTION gender_table_ref_cur_week (cur cursor_pkg.refcur_t)
RETURN sum_sales_gender_t_tab
IS
YEAR    VARCHAR2 (4);
country_id  CHAR (2);
cust_gender  CHAR (1);
sum_amount_sold NUMBER;
objset   sum_sales_gender_t_tab := sum_sales_gender_t_tab ();
i     NUMBER     := 0;
BEGIN
LOOP
  FETCH cur
  INTO YEAR, country_id, cust_gender, sum_amount_sold;
  EXIT WHEN cur%NOTFOUND;    -- exit when last row is fetched
  i := i + 1;
  objset.EXTEND;
  objset (i) :=
   sum_sales_gender_t (YEAR, country_id, cust_gender, sum_amount_sold);
END LOOP;
CLOSE cur;
RETURN objset;
END;
/



6. µ÷ÓÃ±íº¯Êý
ÏÂÁÐ SQL ²éѯÓï¾äµ÷ÓÃÒѱ»¶¨ÒåµÄ±íº¯Êý¡£
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM TABLE (table_ref_cur_week (CURSOR (SELECT *
           FROM sum_sales_country_mv)));
SELECT *
FROM TABLE (table_ref_cur_strong (CURSOR (SELECT *
            FROM sum_sales_country_mv)));
SELECT *
FROM TABLE (table_ref_cur_row (CURSOR (SELECT *
           FROM sum_sales_country_mv)));
SELECT *
FROM TABLE (table_ref_cur_week (CURSOR (SELECT *
           FROM sum_sales_country_mv
           WHERE country = 'AU')));



ÒÔÉÏËùÊöÊÇС±à¸ø´ó¼Ò½éÉܵÄOracle ÖÐ table º¯ÊýµÄÓ¦ÓÃdzÎö£¬Ï£Íû¶Ô´ó¼ÒÓÐËù°ïÖú¡£







»¶Ó­¹âÁÙ ¶ÈÁ¿¿ìËÙ¿ª·¢Æ½Ì¨-רҵ¡¢¿ìËÙµÄÈí¼þ¶¨Öƿ쿪ƽ̨ (http://p.delit.cn/) Powered by Discuz! X3.2