Oracle PL/SQL Tutorial/Object Oriented/table function — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 13:04, 26 мая 2010
Содержание
- 1 Pipelined Table Functions
- 2 Table Functions involving Object Types
- 3 Table Functions with table of numbers
- 4 Table function with aggregate function and group by
- 5 Table function with varray column
- 6 Update statement with table function
- 7 Use table function on varray type value
- 8 Use table function to convert table collection to a table
Pipelined Table Functions
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE TYPE address AS OBJECT
2 (line1 VARCHAR2(20), 3 line2 VARCHAR2(20), 4 city VARCHAR2(20), 5 state_code VARCHAR2(2), 6 zip VARCHAR2(13)); 7 /
Type created. SQL> SQL> CREATE or replace TYPE temp_adds IS TABLE OF address;
2 /
Type created. SQL> SQL> CREATE OR REPLACE FUNCTION myProc
2 RETURN temp_adds PIPELINED
3 IS
4 addressValue address;
5 BEGIN
6 FOR i IN 1..5 LOOP
7 IF (i=1) THEN
8 addressValue := address("St.",null,"York","NY","22222");
9 ELSIF (i=2) THEN
10 addressValue := address("Suite","Blvd","B","IL","33333");
11 ELSIF (i=3) THEN
12 addressValue := address("1 Dr.",null,"P","NJ","33333");
13 ELSIF (i=4) THEN
14 addressValue := address("#9","Avenue","Dallas","TX","11111");
15 ELSIF (i=5) THEN
16 addressValue := address("1 Ct.",null,"F","MA","44444");
17 END IF;
18 PIPE ROW(addressValue);
19 END LOOP;
20 RETURN;
21 END;
22 /
Function created. SQL> show errors No errors. SQL> SQL> SELECT * FROM TABLE(myProc); LINE1 LINE2 CITY ST
-------------------- -------------------- --
ZIP
St. null York NY 22222 Suite Blvd B IL 33333 1 Dr. null P NJ 33333
- 9 Avenue Dallas TX
11111 1 Ct. null F MA 44444
5 rows selected. SQL> SQL> drop type address force; Type dropped. SQL> SQL> drop type temp_adds force; Type dropped.</source>
Table Functions involving Object Types
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE address AS OBJECT
2 (line1 VARCHAR2(20), 3 line2 VARCHAR2(20), 4 city VARCHAR2(20), 5 state_code VARCHAR2(2), 6 zip VARCHAR2(13)); 7 /
Type created. SQL> CREATE or replace TYPE temp_adds IS TABLE OF address;
2 /
Type created. SQL> SQL> CREATE OR REPLACE FUNCTION f_table_obj RETURN temp_adds
2 IS
3
4 v_temp_adds temp_adds :=temp_adds();
5
6 BEGIN
7
8 v_temp_adds.EXTEND(5);
9
10 v_temp_adds(1):= address("a",null,"New York","NY","10020");
11
12 v_temp_adds(2):= address("S","Blvd","Bloomington","IL","33333");
13
14 v_temp_adds(3):= address("1 Dr.",null,"Vancouver","NJ","22222");
15
16 v_temp_adds(4):= address("#9","H Avenue","Dallas","TX","11111");
17
18 v_temp_adds(5):= address("1 Ct.",null,"Franklin","MA","44444");
19
20 RETURN (v_temp_adds);
21
22 END;
23 /
Function created. SQL> show errors No errors. SQL> SQL> SELECT * FROM TABLE(f_table_obj); LINE1 LINE2 CITY ST
-------------------- -------------------- --
ZIP
a null New York NY 10020 S Blvd Bloomington IL 33333 1 Dr. null Vancouver NJ 22222
- 9 H Avenue Dallas TX
11111 1 Ct. null Franklin MA 44444
5 rows selected. SQL> SQL> SQL> drop type address force; Type dropped. SQL> SQL> drop type temp_adds force; Type dropped.</source>
Table Functions with table of numbers
<source lang="sql">
SQL> create or replace type numberTableType is table of number;
2 /
Type created. SQL> SQL> create or replace function f_table return numberTableType
2 is 3 v_numarray numberTableType :=numberTableType(); 4 begin 5 FOR i in 1..10 loop 6 v_numarray.EXTEND; 7 v_numarray(i):=i+100; 8 END LOOP; 9 RETURN (v_numarray); 10 end; 11 /
Function created. SQL> SQL> SQL> SQL> SELECT * FROM TABLE(f_table); COLUMN_VALUE
101
102
103
104
105
106
107
108
109
110
10 rows selected. SQL> SQL></source>
Table function with aggregate function and group by
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> SQL> create or replace type emp_type
2 as object 3 (empno number(4), 4 ename varchar2(10), 5 job varchar2(9), 6 mgr number(4), 7 hiredate date, 8 sal number(7, 2), 9 comm number(7, 2) 10 ); 11 /
Type created. SQL> SQL> SQL> create or replace type emp_tab_type
2 as table of emp_type 3 /
Type created. SQL> SQL> SQL> create or replace type dept_type
2 as object 3 ( deptno number(2), 4 dname varchar2(14), 5 loc varchar2(13), 6 emps emp_tab_type 7 ) 8 /
Type created. SQL> SQL> create or replace view dept_or
2 of dept_type 3 with object identifier(deptno) 4 as 5 select deptno, dname, loc, 6 cast ( multiset ( 7 select empno, ename, job, mgr, hiredate, sal, comm 8 from emp 9 where emp.deptno = dept.deptno ) 10 as emp_tab_type ) 11 from dept 12 /
View created. SQL> SQL> select deptno, dname, loc, count(*)
2 from dept_or d, table ( d.emps ) 3 group by deptno, dname, loc 4 /
DEPTNO DNAME LOC COUNT(*)
-------------- ------------- ----------
20 RESEARCH DALLAS 5
10 ACCOUNTING NEW YORK 3
30 SALES CHICAGO 6
SQL> drop type dept_type; Type dropped. SQL> SQL> drop type emp_tab_type; Type dropped. SQL> SQL> drop type emp_type; Type dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL> drop table dept; Table dropped. SQL></source>
Table function with varray column
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
2 /
Type created. SQL> SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY, 3 direct_addresses numberVarryType 4 );
Table created. SQL> SQL> SQL> INSERT INTO address_list VALUES("OFF101",numberVarryType(1001,1002,1003,1004)); 1 row created. SQL> SQL> SQL> SELECT list_id,column_value FROM address_list,TABLE(direct_addresses); LIST_I COLUMN_VALUE
------------
OFF101 1001 OFF101 1002 OFF101 1003 OFF101 1004 4 rows selected. SQL> SQL> SQL> drop table address_list; Table dropped.</source>
Update statement with table function
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> create or replace type emp_type
2 as object 3 (empno number(4), 4 ename varchar2(10), 5 job varchar2(9), 6 mgr number(4), 7 hiredate date, 8 sal number(7, 2), 9 comm number(7, 2) 10 ); 11 /
Type created. SQL> create or replace type emp_tab_type
2 as table of emp_type 3 /
Type created. SQL> create or replace type dept_type
2 as object 3 ( deptno number(2), 4 dname varchar2(14), 5 loc varchar2(13), 6 emps emp_tab_type 7 ) 8 /
Type created. SQL> SQL> create or replace view dept_or
2 of dept_type 3 with object identifier(deptno) 4 as 5 select deptno, dname, loc, 6 cast ( multiset ( select empno, ename, job, mgr, hiredate, sal, comm 7 from emp 8 where emp.deptno = dept.deptno ) 9 as emp_tab_type ) 10 from dept 11 /
View created. SQL> SQL> update TABLE ( select p.emps from dept_or p where deptno = 20 ) set ename = lower(ename)
2 /
update TABLE ( select p.emps from dept_or p where deptno = 20 ) set ename = lower(ename)
*
ERROR at line 1: ORA-25015: cannot perform DML on this nested table view column
SQL> SQL> drop type dept_type; Type dropped. SQL> SQL> drop type emp_tab_type; Type dropped. SQL> SQL> drop type emp_type; Type dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL> drop table dept; Table dropped. SQL> SQL></source>
Use table function on varray type value
<source lang="sql">
SQL> SQL> SQL> column numlist format a60 SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> SQL> create table e
2 as 3 select empno, ename, init, mgr, deptno 4 from employees;
Table created. SQL> SQL> SQL> create or replace type numberVarray as varray(4) of varchar2(20);
2 /
Type created. SQL> SQL> SQL> alter table e add (numlist numberVarray); Table altered. SQL> SQL> describe e;
Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(8) INIT VARCHAR2(5) MGR NUMBER(4) DEPTNO NUMBER(2) NUMLIST NUMBERVARRAY
SQL> SQL> SQL> update e set numlist = numberVarray("4231","06-12345678"); 10 rows updated. SQL> SQL> SQL> break on empno SQL> SQL> select empno, n.* from e, TABLE(e.numlist) n;
EMPNO COLUMN_VALUE
--------------------
1 4231
06-12345678
2 4231
06-12345678
3 4231
06-12345678
4 4231
06-12345678
5 4231
06-12345678
6 4231
06-12345678
7 4231
06-12345678
8 4231
06-12345678
9 4231
06-12345678
10 4231
EMPNO COLUMN_VALUE
--------------------
10 06-12345678
20 rows selected. SQL> SQL> select empno, numlist from e; ERROR: OCI-22303: type "SYS"."KOTAD" not found OCI-21522: attempted to use an invalid connection in OCI (object mode only)
SQL> SQL> drop table e; Table dropped. SQL> SQL> drop table employees; Table dropped.</source>
Use table function to convert table collection to a table
<source lang="sql">
SQL> SQL> SQL> create table ord(
2 order_no integer primary key 3 ,empl_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> create or replace type sqlMONTH_TABLEtype as table of date;
2 /
Type created. SQL> SQL> create or replace function month_generator
2 (p_num_months in number) 3 RETURN sqlMONTH_TABLEtype 4 AS 5 month_table sqlMONTH_TABLEtype := sqlMONTH_TABLEtype(); 6 BEGIN 7 8 for i in 1..p_num_months loop 9 month_table.extend(1); 10 month_table(i) := add_months(sysdate, -i); 11 end loop; 12 return(month_table); 13 14 END; 15 /
Function created. SQL> SQL> select to_Char(x.column_value, "mm/yyyy") , nvl(avg(total_order_price),0) as avg_Sales
2 from TABLE( month_generator(12) ) x, ord 3 where to_Char(x.column_value, "mm/yyyy") = to_Char(order_date(+), "mm/yyyy") 4 group by to_Char(x.column_value, "mm/yyyy") ;
TO_CHAR AVG_SALES
----------
04/2008 0 03/2008 0 02/2008 0 10/2007 0 01/2008 0 05/2008 0 06/2008 0 12/2007 0 11/2007 0 07/2007 0 09/2007 0 TO_CHAR AVG_SALES
----------
08/2007 0 12 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> SQL></source>
