试试这个: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));
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));
解决方案 »
- 为什么排序之后,查询出来的结果不对呢/
- 各学科top10 排名问题 ,oracle实现
- 请教oracle9i的vb连接问题
- 如何判断当前月是属于哪个季度的?
- 新手提问
- 在线等,oracle817安装以后java.exe无法运行
- oracle8i temp表空间已有5G被使用,oracle shutdown并重起后5G的空间未释放,怎么办呢?
- 问个比较菜的问题?
- 新建数据库
- 通过oracle9i client连接oracle9i server是不是就不用另外编写代码控制更新数据库啊
- 各位高手,看看从一个数据库的表中向另一个数据库的表中倒数据的sql语句怎么写啊?
- 谁来解释一下select * from XXXXX sample(50) t where rownum <= 5
我在过程结束后要返回b和c,我怎样来定义c,并在过程中给c赋值?
..c varchar
... select tname into c
from tab
where tname like ...
假如我的mytable表中有XX,YY两个字段,
我要返回的就是XX YY这两个字段!怎么做?
..c varchar
...
as
cursor cur_column is
select column_name
from all_tab_columns
where table_name = your_table_name;
begin
....
c:='';
for c_column in cur_column loop
c:=c||c_column;
end loop;
...
end;
/