Pages

Wednesday, November 12, 2014

Create a parameterized view in Oracle Database

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