I have a list of people and I want to create view for each person. If I Have 1 person, my view would be:
CREATE VIEW PersonInfo AS (
SELECT * FROM People WHERE id = 1000
);
But in fact, I have thousands of people, I want to create a stored procedure in Oracle to create view for each person, But View names are duplicated when I use it. How can I handle that problem? Thank you for helping in advance.
Your aim is interesting(presumably a related with training but not a real-world scenario), but a dynamic solution would be handled with a for loop by using EXECUTE IMMEDIATE of PL/SQL such as
SQL> CREATE OR REPLACE PROCEDURE Crt_People_Views AS
BEGIN
FOR p IN ( SELECT id FROM People )
LOOP
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW PersonInfo_'||p.id
||' AS SELECT * FROM People WHERE id = '||p.id;
END LOOP;
END;
/
SQL> EXEC Crt_People_Views; -- call the proc. which will create the desired views
What problem are you trying to solve by creating a view for every person?
Would it make more sense to create a single view that takes a parameter (person_id)?
Something like this?
CREATE OR REPLACE VIEW VIEW_ABC (parm1 INTEGER) AS
SELECT *
FROM XYZ
WHERE ….
Call like this.
Then, all we need do is,
SELECT *
FROM VIEW_ABC (101)
/
No probs. with bind variables. Nicely integrated as one would expect.
create view syntax diagram about parameters docs.oracle.com/database/121/SQLRF/…). Perhaps you're thinking of a pipelined table function that takes a parameter?