请教个问题:
create table TAB_999
(
NAME VARCHAR2(8),
SUBJECT VARCHAR2(20),
SCORE NUMBER(3),
PRIMARY KEY (NAME, SUBJECT)
)NAME SUBJECT SCORE
AAA 语文 100
BBB 数学 100
BBB 语文 99
AAA 数学 100create or replace function Func_999
return varchar2
is
SqlStr varchar2(500);
Subject varchar2(10);
cursor v_cur is select distinct subject from tab_999;
begin
SqlStr := 'select name,';
open v_cur;
loop
fetch v_cur into Subject;
exit when v_cur%notfound;
SqlStr := SqlStr + 'sum(case subject when ''' || Subject || ''' score else 0 end ) as ' || Subject || ',';
end loop;
close v_cur; SqlStr := substr(SqlStr, 1, length(SqlStr) - 1) || ' from tab_999 group by name'; return SqlStr;
end;(
或:
create or replace function Func_999
return varchar2
is
SqlStr varchar2(500);
begin
SqlStr := 'select name,';
for v_cur in (select distinct subject from tab_999)
loop
SqlStr := SqlStr + 'sum(case subject when ''' || v_cur.subject || ''' score else 0 end ) as ' || v_cur.subject || ',';
end loop;
SqlStr := substr(SqlStr, 1, length(SqlStr) - 1) || ' from tab_999 group by name';
return SqlStr;
end;
)select Func_999 from dual
怎么会提示错误:
ora-06502:PL/SQL:数字或值错误:字符到数值的转换错误问题出在什么地方?该如何改写?如果Tab_999中记录为空时,就正常,结果是:select name from tab_999 group by name
create table TAB_999
(
NAME VARCHAR2(8),
SUBJECT VARCHAR2(20),
SCORE NUMBER(3),
PRIMARY KEY (NAME, SUBJECT)
)NAME SUBJECT SCORE
AAA 语文 100
BBB 数学 100
BBB 语文 99
AAA 数学 100create or replace function Func_999
return varchar2
is
SqlStr varchar2(500);
Subject varchar2(10);
cursor v_cur is select distinct subject from tab_999;
begin
SqlStr := 'select name,';
open v_cur;
loop
fetch v_cur into Subject;
exit when v_cur%notfound;
SqlStr := SqlStr + 'sum(case subject when ''' || Subject || ''' score else 0 end ) as ' || Subject || ',';
end loop;
close v_cur; SqlStr := substr(SqlStr, 1, length(SqlStr) - 1) || ' from tab_999 group by name'; return SqlStr;
end;(
或:
create or replace function Func_999
return varchar2
is
SqlStr varchar2(500);
begin
SqlStr := 'select name,';
for v_cur in (select distinct subject from tab_999)
loop
SqlStr := SqlStr + 'sum(case subject when ''' || v_cur.subject || ''' score else 0 end ) as ' || v_cur.subject || ',';
end loop;
SqlStr := substr(SqlStr, 1, length(SqlStr) - 1) || ' from tab_999 group by name';
return SqlStr;
end;
)select Func_999 from dual
怎么会提示错误:
ora-06502:PL/SQL:数字或值错误:字符到数值的转换错误问题出在什么地方?该如何改写?如果Tab_999中记录为空时,就正常,结果是:select name from tab_999 group by name
SqlStr := SqlStr || 'sum(case subject when ''' || Subject || ''' score else 0 end ) as ' || Subject || ',';
SqlStr := SqlStr + 'sum(case subject when ''' || Subject || ''' score else 0 end ) as ' || Subject || ',';
( SqlStr := SqlStr + 'sum(case subject when ''' || v_cur.subject || ''' score else 0 end ) as ' || v_cur.subject || ','; )
这条语句上,但不知该如何改写?有没有知道的人啊
谢谢phoenix_qiqi