CREATE TABLE CITIES
(
REGION VARCHAR2 (30),
COUNRTY VARCHAR2 (30),
CITY VARCHAR2 (30)
);
Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATED','NEW YORK');
Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','FRANCE','PARIS');
Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','JAPAN','TOKYO');
Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','INDIA','MUMBAI');
Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','UNITED KINGDOM','LONDON');
Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATES','WASHINGTON DC');
COMMIT;
CREATE OR REPLACE PACKAGE PKG_PARAM AS
PROCEDURE SET_REGION (P_REGION IN VARCHAR2);
FUNCTION GET_REGION RETURN VARCHAR2;
END PKG_PARAM;
/
CREATE OR REPLACE PACKAGE BODY PKG_PARAM AS
REGION VARCHAR2 (30);
PROCEDURE SET_REGION (P_REGION IN VARCHAR2)
IS
BEGIN
REGION := P_REGION;
END;
FUNCTION GET_REGION RETURN VARCHAR2
IS
BEGIN
RETURN REGION;
END;
END PKG_PARAM;
/
CREATE OR REPLACE VIEW PARAM_VIEW AS
SELECT *
FROM CITIES
WHERE REGION=PKG_PARAM.GET_REGION;
SELECT * FROM PARAM_VIEW;
no rows returned....
EXEC PKG_PARAM.SET_REGION('ASIA');
SELECT * FROM PARAM_VIEW;
No comments:
Post a Comment