函数:
create or replace function MaxId(TableName IN varchar2,FieldName IN varchar2)
return number
as
MId number(10,0);
Cursor cr1(Table_name varchar2,Field_Name varchar2) is
select max(Field_Name) from Table_name; --出错行
begin
MId:=0;
open cr1(TableName,FieldName);
......
Close cr1;
return MID;
end MaxId;
目的是指定表,以及字段,返回该字段的最大值
初学,大家能帮我纠正吗?
create or replace function MaxId(TableName IN varchar2,FieldName IN varchar2)
return number
as
MId number(10,0);
Cursor cr1(Table_name varchar2,Field_Name varchar2) is
select max(Field_Name) from Table_name; --出错行
begin
MId:=0;
open cr1(TableName,FieldName);
......
Close cr1;
return MID;
end MaxId;
目的是指定表,以及字段,返回该字段的最大值
初学,大家能帮我纠正吗?
select max(Field_Name) from Table_name; --出错行
中的from Table_name,必须是确定的表名
如果你的oracle是8i或以上:
create or replace function MaxId(TableName IN varchar2,FieldName IN varchar2)
return number
as
MId number(10,0);
begin
execute immediate 'select max(' || FieldName || ') from ' || Tablename into mid;
return MID;
end MaxId;
呵呵.
select zhjh_zc_njh.dwdm,ltrim(dmyy) as dwmc,ssdw,sum(nvl(ja,0)+nvl(sb,0)+nvl(qt,0)) as total
from zhjh_zc_njh,v_firm--zhjh_zzjg
where zhjh_zc_njh.jhnf = p_as_year and zhjh_zc_njh.dwdm = v_firm.dm(+)--zhjh_zzjg.dm
and jhdm = cur_as_arg
group by dwdm,dmyy,ssdw;
create or replace function MaxId(TableName IN varchar2,FieldName IN varchar2)
return number
as
SqlString varchar2(1000);
MId number;
CursorId number;
RowCount number;
begin
SqlString:='select MAX(:FIELDNAME) from :TABLENAME';
CursorId:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CursorId,SqlString,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(CursorId,':FIELDNAME',FieldName);
DBMS_SQL.BIND_VARIABLE(CursorId,':TABLENAME',TableName);
DBMS_SQL.DEFINE_COLUMN(CursorId,1,MId);
RowCount:=DBMS_SQL.EXECUTE(CursorId);
DBMS_SQL.COLUMN_VALUE(CursorId,1,MId);
DBMS_SQL.CLOSE_CURSOR(CursorId);
MId:=MId+1;
return MId;
end MaxId;然后下面调用,还是出错
declare
mid number;
begin
execute mid:=MaxId('EXPENSE','ID');
print mid;
end;
1、RowCount number;不要这么用,RowCount是个关键字,这样的话,得不到正确结果的。
2、SqlString:='select MAX(:FIELDNAME) from :TABLENAME';
CursorId:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CursorId,SqlString,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(CursorId,':FIELDNAME',FieldName);
DBMS_SQL.BIND_VARIABLE(CursorId,':TABLENAME',TableName);
应当没有错,我没有测试。但建议写成这个,简单
SqlString:='select MAX(' || FieldName || ') MId from ' || TableName;
CursorId:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CursorId,SqlString,DBMS_SQL.NATIVE);
3、可以用select MaxId(TableName,FieldName) from dual得到结果
4、还有问题,则是你写错了,以上修改后我是测试通过了的。
2、SqlString:='select MAX(:FIELDNAME) from :TABLENAME';
CursorId:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CursorId,SqlString,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(CursorId,':FIELDNAME',FieldName);
DBMS_SQL.BIND_VARIABLE(CursorId,':TABLENAME',TableName);
是错误的,你犯了同样的错误,因为oracle在PARSE句子SqlString的时候,发现表名不是合法的表名,见我的第一个回帖。
因为oracle在分析SqlString的时候,要保证表是存在的,所以,参数可以是别的,但绝对不应该是表名。
我给你改写后的就可以了。
RowCount变量一定要改为其它名字
function MaxId(TableName IN varchar2,FieldName IN varchar2)
return number
as
SqlString varchar2(1000);
MId number;
CursorId number;
RowEff number;
begin
SqlString:='select MAX(' || FieldName || ') MId from ' || TableName;
CursorId:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CursorId,SqlString,DBMS_SQL.NATIVE);
-- DBMS_SQL.BIND_VARIABLE(CursorId,':FIELDNAME',FieldName);
-- DBMS_SQL.BIND_VARIABLE(CursorId,':TABLENAME',TableName);
DBMS_SQL.DEFINE_COLUMN(CursorId,1,MId);
RowEff:=DBMS_SQL.EXECUTE(CursorId);
DBMS_SQL.COLUMN_VALUE(CursorId,1,MId);
DBMS_SQL.CLOSE_CURSOR(CursorId);
MId:=MId+1;
return MId;
end MaxId;
但用一下语句:
select MaxId('EXPENSE','ID') from all_users;
返回的是一堆空记录
我用
declare
l1 number;
begin
execute l1:=MaxId('EXPENSE','ID');
end;
但有语法错误