select * from (
select /*+parallel(jfsky.Product 2)*/ /*+ index(index_Title_ID) */ A.ID,A.Title,B.Company,A.Filepath from jfsky.Product A
inner join jfsky.company B on A.company=B.ID where 0<instr(A.title,'2N555') order by A.title,A.ID ) order by instr(title,'2N555')
这是我的select语句,,执行没问题,,但我还不太会oracle存储过程,特此求救,要求至少三个参数输入:页码\每页记录数\条件字符,如:'2N555'
速度要求越快越好,要查询能改查询条件,where 0<instr(A.title,'2N555') 和按查询条件排序instr(title,'2N555')
select /*+parallel(jfsky.Product 2)*/ /*+ index(index_Title_ID) */ A.ID,A.Title,B.Company,A.Filepath from jfsky.Product A
inner join jfsky.company B on A.company=B.ID where 0<instr(A.title,'2N555') order by A.title,A.ID ) order by instr(title,'2N555')
这是我的select语句,,执行没问题,,但我还不太会oracle存储过程,特此求救,要求至少三个参数输入:页码\每页记录数\条件字符,如:'2N555'
速度要求越快越好,要查询能改查询条件,where 0<instr(A.title,'2N555') 和按查询条件排序instr(title,'2N555')
i_nthPage NUMBER,
i_countPerPage NUMBER,
o OUT SYS_REFCURSOR) IS
BEGIN
OPEN o 'SELECT *
FROM (SELECT t.*,rownum rn
FROM (SELECT *
FROM (SELECT /*+parallel(jfsky.Product 2)*/ /*+ index(index_Title_ID) */
A.ID, A.Title, B.Company, A.Filepath
FROM jfsky.Product A
INNER JOIN jfsky.company B
ON A.company = B.ID
WHERE instr(A.title, :title) > 0
ORDER BY A.title, A.ID)
ORDER BY instr(title, :title)) t
WHERE rownum <= :i_nthPage * :countPerpage) WHERE
rn>(:nthPage - 1) * :countPerpage'
USING i_title, i_title, i_nthPage, i_countPerPage, i_nthPage, i_countPerPage;EXCEPTION
WHEN OTHERS THEN
OPEN o
SELECT NULL FROM dual WHERE 1 = 2;
END;
CREATE OR REPLACE PROCEDURE querybypage(i_title VARCHAR2,
i_nthPage NUMBER,
i_countPerPage NUMBER,
o OUT SYS_REFCURSOR) IS
BEGIN
OPEN o FOR 'SELECT *
FROM (SELECT t.*,rownum rn
FROM (SELECT *
FROM (SELECT /*+parallel(jfsky.Product 2)*/ /*+ index(index_Title_ID) */
A.ID, A.Title, B.Company, A.Filepath
FROM jfsky.Product A
INNER JOIN jfsky.company B
ON A.company = B.ID
WHERE instr(A.title, i_title) > 0
ORDER BY A.title, A.ID)
ORDER BY instr(title, :title)) t
WHERE rownum <= :i_nthPage * :countPerpage) WHERE
rn>(:nthPage - 1) * :countPerpage'
USING i_title, i_nthPage, i_countPerPage, i_nthPage, i_countPerPage;EXCEPTION
WHEN OTHERS THEN
OPEN o FOR
SELECT NULL FROM dual WHERE 1 = 2;
END;
is
n_precount number;
n_nextcount number;
begin
n_precount:=(n_page-1)*n_count+1;
n_nextcount:=n_page*n_count;
select * from (select * from (select /*+parallel(jfsky.Product 2)*/ /*+ index(index_Title_ID) */ A.ID,A.Title,B.Company,A.Filepath,rownum as numrows from jfsky.Product A
inner join jfsky.company B on A.company=B.ID where 0<instr(A.title,v_str) order by A.title,A.ID ) order by instr(title,v_str))) where numrows between n_precount and n_nextcount;
end;
is
n_precount number;
n_nextcount number;
begin
n_precount:=(n_page-1)*n_count+1;
n_nextcount:=n_page*n_count;
select * from (select * from (select /*+parallel(jfsky.Product 2)*/ /*+ index(index_Title_ID) */ A.ID,A.Title,B.Company,A.Filepath,rownum as numrows from jfsky.Product A
inner join jfsky.company B on A.company=B.ID where 0<instr(A.title,v_str) order by A.title,A.ID ) order by instr(title,v_str))) where numrows between n_precount and n_nextcount;
end;