Oracle PL/SQL Tutorial/Object Oriented/table function
Содержание
- 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
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.
Table Functions involving Object Types
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.
Table Functions with table of numbers
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>
Table function with aggregate function and group by
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>
Table function with varray column
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.
Update statement with table function
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>
Use table function on varray type value
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.
Use table function to convert table collection to a table
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>