CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Lê Nhật Minh
Lê Nhật Minh

Reputation: 67

Create view using stored procedure Oracle

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.

Upvotes: 1

Views: 1309

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

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

Upvotes: 4

Beefstu
Beefstu

Reputation: 857

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.

Upvotes: -1

Related Questions