java web调用存储过程实现分页sql语句要求按条件查找
sql语句是这样的sql="select*fromcom_addresswhereusername=?";以前不加条件存储过程是这样CREATEORREPLACEPROC...
sql 语句是这样的 sql="select * from com_address where username=?";
以前不加条件存储过程是这样CREATE OR REPLACE PROCEDURE split_page
(
v_tableName IN VARCHAR2,
v_pageSize IN NUMBER,
v_currentPage IN NUMBER,
v_totalRows OUT NUMBER,
v_totalPage OUT NUMBER,
v_cursor OUT mypackage.mycursor
)
IS
v_begin NUMBER;
v_end NUMBER;
v_sql VARCHAR2(500);
BEGIN
v_begin:=(v_currentPage-1)*v_pageSize+1;
v_end:=v_pageSize*v_currentPage;
v_sql:='SELECT t2.* FROM (SELECT rownum rn,t1.* FROM (SELECT * FROM '||v_tableName||' ORDER BY empno) t1 WHERE rownum<='||v_end||') t2 WHERE t2.rn>='||v_begin;
OPEN v_cursor FOR v_sql;
v_sql:='SELECT COUNT(*) FROM '||v_tableName;
EXECUTE IMMEDIATE v_sql INTO v_totalRows;
IF(MOD(v_totalRows,v_pageSize)=0) THEN
v_totalPage:=v_totalRows/v_pageSize;
ELSE
v_totalPage:=v_totalRows/v_pageSize+1;
END IF;
END;
/
加入了where 条件后 ,存储过程怎么改,还有条件的传值? 展开
以前不加条件存储过程是这样CREATE OR REPLACE PROCEDURE split_page
(
v_tableName IN VARCHAR2,
v_pageSize IN NUMBER,
v_currentPage IN NUMBER,
v_totalRows OUT NUMBER,
v_totalPage OUT NUMBER,
v_cursor OUT mypackage.mycursor
)
IS
v_begin NUMBER;
v_end NUMBER;
v_sql VARCHAR2(500);
BEGIN
v_begin:=(v_currentPage-1)*v_pageSize+1;
v_end:=v_pageSize*v_currentPage;
v_sql:='SELECT t2.* FROM (SELECT rownum rn,t1.* FROM (SELECT * FROM '||v_tableName||' ORDER BY empno) t1 WHERE rownum<='||v_end||') t2 WHERE t2.rn>='||v_begin;
OPEN v_cursor FOR v_sql;
v_sql:='SELECT COUNT(*) FROM '||v_tableName;
EXECUTE IMMEDIATE v_sql INTO v_totalRows;
IF(MOD(v_totalRows,v_pageSize)=0) THEN
v_totalPage:=v_totalRows/v_pageSize;
ELSE
v_totalPage:=v_totalRows/v_pageSize+1;
END IF;
END;
/
加入了where 条件后 ,存储过程怎么改,还有条件的传值? 展开
1个回答
展开全部
存储过程是可以带入参的,同时也有出参,所以你这里加入条件只要加一个入参参数就行,调用的时候把参数传递进来
追问
谢谢,我要加入web中实现,但不知道参数加哪,谢谢你帮忙能不能把上面加参的地方写一下啊
追答
举个简单例子
创建带参存储过程:
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;
调用存储过程:
proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
proc.setString(1, "100");
proc.setString(2, "TestOne");
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询