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 t 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;
--两个条件 并且当不设置值的时候不启用
CREATE OR REPLACE VIEW VW_TEST2
AS SELECT * FROM t WHERE ROWNUM exec pkg_session.set_val('P_NAME','UCJMH');
PL/SQL procedure successfully completed.
SQL> select * from vw_test2;
USERNAME
------------------------------
UCJMH
这个时候就只会有一行数据了