oracle带参数,可以建立带参数的视图吗？

PROMPT CREATE OR REPLACE PACKAGE pkg_session

CREATE OR REPLACE PACKAGE pkg_session IS

TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30);

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2);

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER);

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE);

FUNCTION  get_val(p_idx IN VARCHAR2) RETURN VARCHAR2 ;

FUNCTION  get_val_number(p_idx IN VARCHAR2) RETURN NUMBER;

FUNCTION  get_val_date(p_idx IN VARCHAR2) RETURN DATE;

PRAGMA RESTRICT_REFERENCES(get_val,WNDS,WNPS);

PRAGMA RESTRICT_REFERENCES(get_val_number,WNDS,WNPS);

PRAGMA RESTRICT_REFERENCES(get_val_date,WNDS,WNPS);

END pkg_session;

/

CREATE OR REPLACE PACKAGE BODY pkg_session IS

sv_parameters parm_type;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2)

IS

BEGIN

sv_parameters(TRIM(UPPER(p_idx))) := SUBSTR(p_value,1,1000);

RETURN;

END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER)

IS

BEGIN

set_val(p_idx,TO_CHAR(p_value));

RETURN;

END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE)

IS

BEGIN

set_val(p_idx,TO_CHAR(p_value,'YYYYMMDDHH24MISS'));

RETURN;

END set_val;

FUNCTION  get_val(p_idx IN VARCHAR2) RETURN VARCHAR2

IS

BEGIN

RETURN sv_parameters(TRIM(UPPER(p_idx)));

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN NULL;

END get_val;

FUNCTION  get_val_number(p_idx IN VARCHAR2) RETURN NUMBER

IS

BEGIN

RETURN TO_NUMBER(get_val(p_idx));

END get_val_number;

FUNCTION  get_val_date(p_idx IN VARCHAR2) RETURN DATE

IS

BEGIN

RETURN TO_DATE(get_val(p_idx),'YYYYMMDDHH24MISS');

END get_val_date;

END pkg_session;

/

CREATE OR REPLACE VIEW VW_TEST

AS SELECT * FROM USER_TABLES WHERE ROWNUM<=pkg_session.get_val_number('P_ROWNUM');

-- 未设参数时没有数据

SELECT * FROM VW_TEST;

--- 设置参数：

EXEC pkg_session.set_val('P_ROWNUM',10);

-- 以下会返回10行：

SELECT * FROM VW_TEST;

[本帖最后由 newkid 于 2009-9-8 22:32 编辑]