参考这个例子吧:create table test(id varchar2(3),work_date date,content varchar2(1000),primary key (id,work_date));
insert into test values('001',to_date('2003-02-01','yyyy-mm-dd'),'adfsadf');
insert into test values('001',to_date('2003-02-11','yyyy-mm-dd'),'adfsadf');
insert into test values('003',to_date('2003-02-11','yyyy-mm-dd'),'adfsadf');
commit;create or replace type my_object as object(
id varchar2(3),
work_date date
)
/
create or replace type myTableType
as table of my_object
/
CREATE OR REPLACE FUNCTION Uf_Result (as_id varchar2,as_date varchar2 default '2003-02')
RETURN Mytabletype
IS
My_Result Mytabletype := Mytabletype();
ll_num number;
ll_loop number;
ld_date date;
BEGIN
select to_date(to_date(as_date,'yyyy-mm')) into ld_date
from dual; select to_char(last_day(ld_date),'dd')
into ll_num
from dual;
FOR ll_loop IN 1..ll_num
LOOP
My_Result.EXTEND;
My_Result(ll_loop) := My_Object(as_id,ld_date ); select ld_date + 1 into ld_date
from dual;
END LOOP; RETURN My_Result;
END;
/
SELECT * FROM
TABLE(CAST(UF_RESULT('001') AS myTableType));select d.id,d.work_date,t.content
FROM TEST T ,(SELECT * FROM TABLE(CAST(UF_RESULT('001') AS myTableType))) d
WHERE T.Work_Date(+) = d.work_date and t.id(+) = d.id;
insert into test values('001',to_date('2003-02-01','yyyy-mm-dd'),'adfsadf');
insert into test values('001',to_date('2003-02-11','yyyy-mm-dd'),'adfsadf');
insert into test values('003',to_date('2003-02-11','yyyy-mm-dd'),'adfsadf');
commit;create or replace type my_object as object(
id varchar2(3),
work_date date
)
/
create or replace type myTableType
as table of my_object
/
CREATE OR REPLACE FUNCTION Uf_Result (as_id varchar2,as_date varchar2 default '2003-02')
RETURN Mytabletype
IS
My_Result Mytabletype := Mytabletype();
ll_num number;
ll_loop number;
ld_date date;
BEGIN
select to_date(to_date(as_date,'yyyy-mm')) into ld_date
from dual; select to_char(last_day(ld_date),'dd')
into ll_num
from dual;
FOR ll_loop IN 1..ll_num
LOOP
My_Result.EXTEND;
My_Result(ll_loop) := My_Object(as_id,ld_date ); select ld_date + 1 into ld_date
from dual;
END LOOP; RETURN My_Result;
END;
/
SELECT * FROM
TABLE(CAST(UF_RESULT('001') AS myTableType));select d.id,d.work_date,t.content
FROM TEST T ,(SELECT * FROM TABLE(CAST(UF_RESULT('001') AS myTableType))) d
WHERE T.Work_Date(+) = d.work_date and t.id(+) = d.id;
解决方案 »
- timesten与oracle性能测试
- ORA-12154: TNS:could not resolve the connect idendifier specified
- ORACLE DEVELOPER 2K有MAC版本么?~~~
- imp导入多个dmp备份文件怎么写?
- 关于oracle往有经验的大哥谈谈自己的想法,
- 想学PL/SQL。。!!!!!!!!!!
- 在win2000 Adwance Server 上安装Oracle后机器巨慢...死机.我的服务器可是1G内存4颗CPU!
- nologging的迷惑
- 請問語句的意思,謝謝
- windows 下 用mingw和oci怎么连接oracle 11g xe
- ado.net 连接Oracle 的套接字怎么写?
- 速度优化问题。大家给意见,
select count(*),TO_Char(F_DATE,'DD') from view1 group by TO_Char(F_DATE,'DD')的语句来达到目的。飘兄,您认为该??
SQL> select * from tdate;COL1 COL2 COL3
---------- ---------- ----------
c1 11-7月 -04 19-7月 -04SQL> select col2+rownum-1 from all_source,tdate where rownum<=(select col3-col2+1 from tdate);COL2+ROWNU
----------
11-7月 -04
12-7月 -04
13-7月 -04
14-7月 -04
15-7月 -04
16-7月 -04
17-7月 -04
18-7月 -04
19-7月 -04已选择9行。SQL>