Oracle SYS_REFCURSOR不能用作返回类型 [英] Oracle SYS_REFCURSOR couldn't use as a return type
问题描述
我需要使用oracle 11g中的成员函数提取并显示db中所有记录的所有年份.
I need to extract and display all the years for all the records in db using member function in oracle 11g.
CREATE or replace TYPE BODY student_t AS
MEMBER FUNCTION getYear RETURN SYS_REFCURSOR IS
yearDOB SYS_REFCURSOR;
BEGIN
for c in (SELECT EXTRACT(YEAR FROM s.dob) c_year from student s)
loop
yearDOB := c.c_year;
end loop;
return yearDOB;
END;END;/
由于我需要为提取函数返回多个值,因此我声明了SYS_REFCURSOR类型的变量以返回.但这会产生以下错误.
Since I need to return multiple values for the extract function I have declared a SYS_REFCURSOR type variable to return. But it will generate following errors.
7/1 PL/SQL: Statement ignored,
7/14 PLS-00382: expression is of wrong type
更改为以下答案后的输出.
output after changed as following answer.
光标声明:1
光标声明:1
1993
1995
光标声明:1
光标声明:1
1993
1995
学生类型如下.
create type student_t as object(
stno char(4),
dob date)/
alter type student_t add member function getYear RETURN SYS_REFCURSOR cascade;/
推荐答案
我不确定您要实现的目标,但我认为您误解了oracle中的对象概念.
在我的示例中,我假设
1)使用此脚本create table student of student_t;
I'm not sure what you are trying to achieve but I think you misunderstood concept of object in oracle.
In my example i'm assuming
1) Table studens is create with this script create table student of student_t;
drop table student;
创建类型规范和正文.
Create type spec and body.
create or replace type student_t as object(
stno char(4),
dob date,
member function getYear return number
)
;
create or replace type body student_t as
member function getYear return number is
begin
return EXTRACT(YEAR FROM self.dob);
end;
end;
创建studnet表
create table student of student_t;
填充表格
declare
v_student student_t;
begin
for i in 0 .. 10 loop
insert into student values(student_t('ST'||+mod(i,3),to_date('01-01-'||to_char(2000+i),'dd-mm-yyyy')));
end loop;
commit;
end;
查询.
select s.*,s.getYear() from student s;
这篇关于Oracle SYS_REFCURSOR不能用作返回类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!