Question

如何将表名作为参数变量传给PLSQL中的函数

Answer 1

用动态SQL就可以了
CREATE OR REPLACE PROCEDURE COMMDEVIDEPAGE(P_TABLENAME IN VARCHAR2,
P_SIZE IN NUMBER,
P_PAGENOW IN NUMBER,
P_COUNT OUT NUMBER,
P_COUTPAGE OUT NUMBER,
P_CURSOR OUT SYS_REFCURSOR) IS
V_SQL VARCHAR2(1000);
V_SIZE NUMBER;
V_PAGENOW NUMBER;
V_BEGIN NUMBER;
V_END NUMBER;
BEGIN
V_SQL := 'SELECT COUNT(*) FROM ' || P_TABLENAME;
EXECUTE IMMEDIATE V_SQL
INTO P_COUNT; --计算总记录数
--求总页数
IF MOD(P_COUNT, P_SIZE) = 0 THEN
P_COUTPAGE := FLOOR(P_COUNT / P_SIZE);
ELSE
P_COUTPAGE := FLOOR(P_COUNT / P_SIZE) + 1;
END IF;
V_BEGIN := (P_PAGENOW - 1) * P_SIZE + 1;
V_END := P_PAGENOW * P_SIZE;

OPEN P_CURSOR FOR 'Select p.* From (Select Rid
From (Select Rid, Rownum Rn
From (Select Rowid Rid
From ' || P_TABLENAME || '
Where Promo_Id Is Not Null
Order By Promo_Id)
Where Rownum <=' || V_END || ')
Where Rn >=' || V_BEGIN || ') t,
' || P_TABLENAME || ' p
Where p.Rowid = t.Rid';
END COMMDEVIDEPAGE;