有一个表,名为tableName,它定义的列中有两个字段,字段名为field,orderField。当列field的值含有fieldValue时,则满足查询要求,并将它输出,输出的结果根据orderField进行排序,并且只输出介于startNum与entNum间的记录。SQL语句如何写?
存储过程的定义如下:
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,orderField in varchar2,
startNum in integer,entNum in integer,records out SYS_REFCURSOR);
注:records为输出的结果。
存储过程的定义如下:
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,orderField in varchar2,
startNum in integer,entNum in integer,records out SYS_REFCURSOR);
注:records为输出的结果。
create or replace procedure getRecordByUUID(
tableName in varchar2,
field in varchar2,
fieldValue in varchar2,
orderField in varchar2,
startNum in integer,
entNum in integer,
records out SYS_REFCURSOR
)as
v_sql varchar2(4000);
begin
v_sql :='select t.* from (select row_number() over(order by '||orderField||') rn,';
v_sql := v_sql||'tbl.* from '||tableName||' as tbl where '||field||' like ''%'||fieldValue||'%''
v_sql := v_sql||') t where t.rn >='||startNum||' and t.rn <='||entNum; --这里的>=和<=不知道是不是你那个意义上的介于。。
open records for v_sql;
end;
/
tableName in varchar2,
field in varchar2,
fieldValue in varchar2,
orderField in varchar2,
startNum in integer,
entNum in integer,
records out SYS_REFCURSOR
)as
v_sql varchar2(4000);
begin
v_sql :='select t.* from (select row_number() over(order by '||orderField||') rn,';
v_sql := v_sql||'tbl.* from '||tableName||' as tbl where '||field||' like ''%'||fieldValue||'%'';
v_sql := v_sql||') t where t.rn >='||startNum||' and t.rn <='||entNum; --这里的>=和<=不知道是不是你那个意义上的介于。。
open records for v_sql;
end;
/
v_sql := v_sql||') t where t.rn >='||startNum||' and t.rn <='||entNum;
语法有错误。能不能修改一下。谢谢
v_sql := v_sql||') t where t.rn >='||startNum||' and t.rn <='||entNum'; 这样试试。
--查询出来的结果行数介于startNum与entNum间,并且查询的结果以orderField来排序
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,orderField in varchar2,
likeFlag in boolean ,startNum in integer,entNum in integer,records out SYS_REFCURSOR) is
p_str varchar2(4000);
begin
p_str :='select t.* from (select row_number() over(order by '||orderField||') rn,';
p_str := p_str||'tbl.* from '||tableName||' as tbl where '||field||' like ''%'||fieldValue||'%'';
p_str := p_str||') t where t.rn >='||startNum||' and t.rn <='||entNum'; open records for p_str;/*返回游标*/
end;
看出来了:
p_str := p_str||'tbl.* from '||tableName||' as tbl where '||field||' like ''%'||fieldValue||'%'';
这句最后少了个'
p_str := p_str||'tbl.* from '||tableName||' as tbl where '||field||' like ''%'||fieldValue||'%''';
不好意思了。
--在表tableName中查找满足条件记录:条件为field(表中的字段名称)中含有fieldValue。
--likeFlag为TRUE时,模糊查询,即当field的值含有fieldValue时,就返回结果。当likeFlag为FALSE时,进行精确查询
--查询出来的结果行数介于startNum与entNum间,并且查询的结果以orderField来排序
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,orderField in varchar2,
startNum in integer,entNum in integer,records out SYS_REFCURSOR) is
p_str varchar2(4000);
begin
p_str :='select t.* from (select row_number() over(order by '||orderField||') rn,';
p_str := p_str||'tbl.* from '||tableName||' as tbl where '||field||' like ''%'||fieldValue||'%''';
--p_str := p_str||') t where t.rn >='||startNum||' and t.rn <='||entNum;
p_str := p_str||') t where rn between ' || startNum || ' and ' || entNum;
open records for p_str;/*返回游标*/
end; pl/sql developer语法检查没有错误,但测试程序运行时,发生了错误:ORA00907:缺失右括号
--在表tableName中查找满足条件记录:条件为field(表中的字段名称)中含有fieldValue。
--likeFlag为TRUE时,模糊查询,即当field的值含有fieldValue时,就返回结果。当likeFlag为FALSE时,进行精确查询
--查询出来的结果行数介于startNum与entNum间,并且查询的结果以orderField来排序
procedure getRecordByUUID(tableName in varchar2, field in varchar2,fieldValue in varchar2,orderField in varchar2,
startNum in integer,entNum in integer,records out SYS_REFCURSOR) is
p_str varchar2(4000);
begin
p_str :='select t.* from (select row_number() over(order by '||orderField||') rn,';
p_str := p_str||'tbl.* from '||tableName||' as tbl where '||field||' like ''%'||fieldValue||'%''';
p_str := p_str||') t where t.rn >='||startNum||' and t.rn <='||entNum;
open records for p_str;/*返回游标*/
end; pl/sql developer语法检查没有错误,但测试程序运行时,发生了错误:ORA00907:缺失右括号
tableName in varchar2,
field in varchar2,
fieldValue in varchar2,
orderField in varchar2,
likeFlag in integer ,
startNum in integer,
entNum in integer,
records out SYS_REFCURSOR
) is
p_str varchar2(4000);
begin
p_str :='select t.* from (select row_number() over(order by '||orderField||') rn,';
p_str := p_str||'tbl.* from '||tableName||' tbl where '||field||' like ''%'||fieldValue||'%''';
p_str := p_str||') t where t.rn >='||to_char(startNum)||' and t.rn <='||to_char(entNum);
open records for p_str;
end;
/去掉了里面的 as