I have a table for example Personas that has 20 data, now I create a view from that table and call it Personas_V , which has the 20 data of the table Personas .
If I add 2 data to the table Personas would have 22 data in this table but in the view I'm still having 20, is not it automatically updated?
The script that you run was:
CREATE OR REPLACE FORCE VIEW "PERSONAS_V" as SELECT MSID.Identification_PK AS Identification_PK FROM PERSONAS MSID;
In your case you are modifying the data of the original table, which should be reflected in the view. If you are not seeing the changes reflected try to execute the following command:
ALTER VIEW NombreVista
COMPILE;
I leave a link to the official documentation
Since Oracle does not have the following option in SQLserver:
EXEC sp_RefreshView NombreVista
It could be happening that the metadata of the view is not updated automatically when you modify the tables, but in Oracle this would not make much sense.
If the view were materialized, a possible solution would be:
SQL> CREATE materialized view log on emp
2 WITH rowid, primary key, sequence (deptno, job)
3 INCLUDING new values
4 /
Materialized view log created.
SQL> CREATE materialized view emp_mv
2 REFRESH fast on commit
3 as
4 SELECT deptno, job from emp
5 GROUP by deptno, job
6 /
Materialized view created.
To leave it as an annotation, the views have two fundamental purposes:
If you have a query that requires joining several tables, or you have a logic or complex calculations, you can encode all that logic in a view and then select it from the view as if it were a table.
A view can select certain columns and / or rows of a table and the permissions set in the view instead of the underlying tables. This allows only the data that a user needs to see to appear.
PD. Are you using "OR REPLACE FORCE VIEW" for some reason or because you have seen and run it without further? It is used for stored procedures. The "force" clause tells Oracle to replace the view that it already exists. In principle, without knowing more about your case, I would say it is not necessary.
I leave you an example code because with the information you supply, I could not do it with your data. If you extend it, I could help you.
To create a view in Oracle it is enough with:
create view vd1
as select *
from dept2
where loc like ‘D%’;
And this will be updated automatically every time we access the view. If we insert new data in the view, they will be seen in both tables:
INSERT INTO vd1 (deptno, dname, loc)
VALUES (70,'MARKETING','DENVER');
Now, if we insert a data that does not fulfill the condition that it begins with D, it will not give us an error but it will not be shown in the view; yes in the table. For example:
INSERT INTO vd1 (deptno, dname, loc)
VALUES (80,'LOGISTICS','MIAMI');
All this would be fixed with the clause check option , in that case, to introduce Miami, the changes would not be reflected neither in the table nor in the view.
CREATE VIEW vd2
AS SELECT *
FROM dept2
WHERE LOC like ‘D%’
WITH CHECK OPTION;