我使用下面的语句在表tableName中查找出满足条件的记录,
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,records out SYS_REFCURSOR) is
p_str varchar2(1000);
begin
p_str :='select * from ' ||tableName|| ' where ' || field|| ' like ' ||'''%'||fieldValue||'%''';
open records for p_str;
execute immediate p_str; /*返回游标*/
end; 现在我要从满足上述条件的记录中取出介于startNum和entNum的记录,存储过程如下:
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,startNum in integer,entNum in integer,records out SYS_REFCURSOR)
整个语句怎么写呢?
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,records out SYS_REFCURSOR) is
p_str varchar2(1000);
begin
p_str :='select * from ' ||tableName|| ' where ' || field|| ' like ' ||'''%'||fieldValue||'%''';
open records for p_str;
execute immediate p_str; /*返回游标*/
end; 现在我要从满足上述条件的记录中取出介于startNum和entNum的记录,存储过程如下:
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,startNum in integer,entNum in integer,records out SYS_REFCURSOR)
整个语句怎么写呢?
不要用动态SQL,直接用游标
传进来的stratNum 和 entNum是number型吧!procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,
records out SYS_REFCURSOR)
is
begin open records for
select * from (
select t.*,rownum rn from ' ||tableName|| ' t
where ' || field|| ' like ' ||'''%'||fieldValue|;
)
where rn>= stratNum and rn <= entNum
end;
records out SYS_REFCURSOR)
is
begin open records for
select * from (
select t.*,rownum rn from ' ||tableName|| ' t
where ' || field|| ' like ' ||'''%'||fieldValue|;
)
where rn>= stratNum and rn <= entNum
end;
records out SYS_REFCURSOR)
is
begin open records for
select * from (
select t.*,rownum rn from ' ||tableName|| ' t
where ' || field|| ' like ' ||'''%'||fieldValue|;
)
where rn>= stratNum and rn <= entNum
end;
records out SYS_REFCURSOR)
is
begin open records for
select * from (
select t.*,rownum rn from ' ||tableName|| ' t
where ' || field|| ' like ' ||'''%'||fieldValue || '''
)
where rn>= stratNum and rn <= entNum
end;
records out SYS_REFCURSOR)
is
v_sql varchar2(200);
begin v_sql := 'select * from (' ||
'select t.*,rownum rn from ' ||tableName|| ' t ' ||
'where ' || field|| ' like ' ||'''%'||fieldValue || '''' ||
')' ||
' where rn>=10 and rn <= 100';
open records for v_sql;
end;
/
这里的10,100是指起始的记录条数,如果是一个变量的话,需要将变量拿到引号外面来
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,startNum in integer,entNum in integer,records out SYS_REFCURSOR) ;
startNum in integer,entNum in integer,records out SYS_REFCURSOR) is
p_str varchar2(1000);
begin
open records for
select * from (select t.*,rownum rn from ' ||tableName|| ' t
where ' ||field|| ' like '%' ||fieldValue || '%';)
where rn>= stratNum and rn <= entNum;
end;
end plan_searchTable;