create or replace function twofieldsadd( key_name in varchar2,
key in varchar2,
coname1 in varchar2,
coname2 in varchar2,
tname in varchar2 )
return varchar2
as
type rc is ref cursor;
str varchar2(8000);
sep varchar2(2);
val varchar2(8000);
tmp varchar2(10);
cur rc;
begin
tmp := 'EA';
open cur for 'select '||coname1 || '||''EA''||' || coname2 || ' from '|| tname || ' where ' || key_name || ' = :x ' using key;
loop
fetch cur into val;
exit when cur%notfound;
str := str || sep || val;
sep := ',';
end loop;
close cur;
return str;
end;
这是我之前看到的Oracle中自定义的一个函数,可以执行。但看不懂的地方是 where中”= :x “ 是什么意思,”using key“又是什么意思?如果where 有两个条件该如何写?
我写成
create or replace function threefieldsadd( key_name1 in varchar2,key_name2 in varchar2,
key1 in varchar2,key2 in varchar2,
coname1 in varchar2,
coname2 in varchar2,
tname in varchar2 )
return varchar2
as
type rc is ref cursor;
str varchar2(8000);
sep varchar2(2);
val varchar2(8000); cur rc;
begin
open cur for 'select '||coname1 || '||''EA''||' || coname2 || ' from '|| tname || ' where ' || key1_name || ' = :x and ' || key2_name || ' =:y ' using key1 + key2;
loop
fetch cur into val;
exit when cur%notfound;
str := str || sep || val;
sep := ',';
end loop;
close cur;
return str;
end;
调用会出错。请告诉分析。
key in varchar2,
coname1 in varchar2,
coname2 in varchar2,
tname in varchar2 )
return varchar2
as
type rc is ref cursor;
str varchar2(8000);
sep varchar2(2);
val varchar2(8000);
tmp varchar2(10);
cur rc;
begin
tmp := 'EA';
open cur for 'select '||coname1 || '||''EA''||' || coname2 || ' from '|| tname || ' where ' || key_name || ' = :x ' using key;
loop
fetch cur into val;
exit when cur%notfound;
str := str || sep || val;
sep := ',';
end loop;
close cur;
return str;
end;
这是我之前看到的Oracle中自定义的一个函数,可以执行。但看不懂的地方是 where中”= :x “ 是什么意思,”using key“又是什么意思?如果where 有两个条件该如何写?
我写成
create or replace function threefieldsadd( key_name1 in varchar2,key_name2 in varchar2,
key1 in varchar2,key2 in varchar2,
coname1 in varchar2,
coname2 in varchar2,
tname in varchar2 )
return varchar2
as
type rc is ref cursor;
str varchar2(8000);
sep varchar2(2);
val varchar2(8000); cur rc;
begin
open cur for 'select '||coname1 || '||''EA''||' || coname2 || ' from '|| tname || ' where ' || key1_name || ' = :x and ' || key2_name || ' =:y ' using key1 + key2;
loop
fetch cur into val;
exit when cur%notfound;
str := str || sep || val;
sep := ',';
end loop;
close cur;
return str;
end;
调用会出错。请告诉分析。
这是绑定变量,然后变量赋值
这是种提高SQL效率的方法,你搜索下绑定变量就知道,降低SQL的硬解析
就这么理解就行。
open cur for 'select ' ¦ ¦coname1 ¦ ¦ ' ¦ ¦''EA'' ¦ ¦' ¦ ¦ coname2 ¦ ¦ ' from ' ¦ ¦ tname ¦ ¦ ' where ' ¦ ¦ key1_name ¦ ¦ ' = '||key1||' and ' ¦ ¦ key2_name ¦ ¦ ' ='||key2|''''
key1 in varchar2,key2 in varchar2,
coname1 in varchar2,
coname2 in varchar2,
tname in varchar2 )
return varchar2
as
type rc is ref cursor;
str varchar2(8000);
sep varchar2(2);
val varchar2(8000); cur rc;
begin
open cur for 'select '||coname1 || '||''EA''||' || coname2 || ' from '|| tname || ' where ' || key_name1 || ' = ' || key1 || ' and ' || key_name2 || ' = ' || key2;
loop
fetch cur into val;
exit when cur%notfound;
str := str || sep || val;
sep := ',';
end loop;
close cur;
return str;
end;
写成这样函数不会出错,但调用这个方法的时候会出错。我写的sql语句是:
select picno,
position,
(sum(t_check_record.REJECTQUANTITY) +
sum(t_check_record.failquantity)) failquantity,
max((select THREEfieldsadd('PICNO','POSITION',
t_check_record_item.PICNO,t_check_record_item.POSITION,
'defectmquantity',
'CONTENT',
't_check_record_item')
from t_check_record_item
where PICNO = t_check_record.PICNO AND POSITION = t_check_record.POSITION
group by PICNO,position)) CONTENT
from t_check_record
WHERE DEPTCODE = 'BM007' and picno = '0200-09046'
group by picno, position错误提示:
---------------------------
错误
---------------------------
ORA-00904: 无效列名
ORA-06512: 在"SYSTEM.THREEFIELDSADD", line 15
ORA-06512: 在line 1
查看错误堆栈的程序源?
---------------------------
是(Y) 否(N)
---------------------------