PROMPT CREATE OR REPLACE PACKAGE pkg_session<\/p>\n
CREATE OR REPLACE PACKAGE pkg_session IS
\nTYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30);
\nPROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2);
\nPROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER);
\nPROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE);
\nFUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2 ;
\nFUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER;
\nFUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE;
\nPRAGMA RESTRICT_REFERENCES(get_val,WNDS,WNPS);
\nPRAGMA RESTRICT_REFERENCES(get_val_number,WNDS,WNPS);
\nPRAGMA RESTRICT_REFERENCES(get_val_date,WNDS,WNPS);
\nEND pkg_session;
\n\/<\/p>\n
CREATE OR REPLACE PACKAGE BODY pkg_session IS
\nsv_parameters parm_type;
\nPROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2)
\nIS
\nBEGIN
\n sv_parameters(TRIM(UPPER(p_idx))) := SUBSTR(p_value,1,1000);
\n RETURN;
\nEND set_val;<\/p>\n
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER)
\nIS
\nBEGIN
\n set_val(p_idx,TO_CHAR(p_value));
\n RETURN;
\nEND set_val;<\/p>\n
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE)
\nIS
\nBEGIN
\n set_val(p_idx,TO_CHAR(p_value,’YYYYMMDDHH24MISS’));
\n RETURN;
\nEND set_val;<\/p>\n
FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2
\nIS
\nBEGIN
\n RETURN sv_parameters(TRIM(UPPER(p_idx)));
\nEXCEPTION
\n WHEN NO_DATA_FOUND THEN
\n RETURN NULL;
\nEND get_val;<\/p>\n
FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER
\nIS
\nBEGIN
\n RETURN TO_NUMBER(get_val(p_idx));
\nEND get_val_number;<\/p>\n
FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE
\nIS
\nBEGIN
\n RETURN TO_DATE(get_val(p_idx),’YYYYMMDDHH24MISS’);
\nEND get_val_date;<\/p>\n
END pkg_session;
\n\/<\/p>\n
CREATE OR REPLACE VIEW VW_TEST
\nAS SELECT * FROM t WHERE ROWNUM<=pkg_session.get_val_number('P_ROWNUM');<\/p>\n
— \u672a\u8bbe\u53c2\u6570\u65f6\u6ca1\u6709\u6570\u636e
\nSELECT * FROM VW_TEST;<\/p>\n
— \u8bbe\u7f6e\u53c2\u6570\uff1a<\/p>\n
EXEC pkg_session.set_val('P_ROWNUM',10);<\/p>\n
— \u4ee5\u4e0b\u4f1a\u8fd4\u56de10\u884c\uff1a
\nSELECT * FROM VW_TEST;<\/p>\n
–\u4e24\u4e2a\u6761\u4ef6 \u5e76\u4e14\u5f53\u4e0d\u8bbe\u7f6e\u503c\u7684\u65f6\u5019\u4e0d\u542f\u7528
\nCREATE OR REPLACE VIEW VW_TEST2
\nAS SELECT * FROM t WHERE ROWNUM exec pkg_session.set_val(‘P_NAME’,’UCJMH’);<\/p>\n
PL\/SQL procedure successfully completed.<\/p>\n
SQL> select * from vw_test2;<\/p>\n
USERNAME
\n——————————
\nUCJMH
\n\u8fd9\u4e2a\u65f6\u5019\u5c31\u53ea\u4f1a\u6709\u4e00\u884c\u6570\u636e\u4e86<\/p>\n","protected":false},"excerpt":{"rendered":"
PROMPT CREATE OR REPLACE PACKAGE pkg_session CREATE OR […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[34,2],"class_list":["post-569","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-oracle_basics"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/569","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=569"}],"version-history":[{"count":1,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/569\/revisions"}],"predecessor-version":[{"id":894,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/569\/revisions\/894"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=569"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=569"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=569"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}