Oracle View及dbms_metadata.get_ddl看对象生成的代码

最新推荐文章于 2025-07-14 10:06:03 发布

原创最新推荐文章于 2025-07-14 10:06:03 发布 · 621 阅读

CC 4.0 BY-SA版权

这篇博客讨论了SQL中创建和管理视图的操作，包括创建、替换、删除视图，以及视图的只读约束和检查约束。还提到了使用DBMS_METADATA.GET_DDL获取视图定义的方法，并展示了如何处理视图的DML操作限制，特别是当试图在只读视图上进行插入或删除操作时。最后，提到了如何通过synonym和视图来管理数据库对象。

部署运行你感兴趣的模型镜像

View
	SQL> create view abc31 as select * from employees where salary>10000; View created.	创建视图
	SQL> create or replace view av10000 as select * from employees where salary>=10000; View created.	创建或替代
	SQL> create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000 with read only; View created. SQL>select CONSTRAINT_NAME,TABLE_NAME,STATUS from user_constraints where TABLE_NAME='AV10000'; SYS_C008958 SQL> alter view AV10000 drop constraint SYS_C008958; View altered. SQL> select CONSTRAINT_NAME,TABLE_NAME,STATUS from user_constraints where TABLE_NAME='AV10000'; no rows selected SQL> delete from AV10000 where salary='12300' * ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view 去掉只读的约束，还是不能删除。。。 SQL> create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000; View created. SQL> delete from AV10000 where salary='12300'; 1 row deleted.	只读不能通过视图去插入原表，会自动产生约束用默认名去掉只读的约束，还是不能删除，说明只读属性还在创建的视图上面
	SQL> create or replace view av10000 as select * from employees where salary>=10000 with read only constraints avcon; View created.	加只读约束
	select CONSTRAINT_NAME,TABLE_NAME,STATUS from user_constraints where CONSTRAINT_NAME='AVCON'; CONSTRAINT_NAME TABLE_NAME STATUS -------------------------------------------------------------------------------- AVCON AV10000 ENABLED	查约束
	SQL> create or replace view av10000 as select * from employees where salary>=10000 with check option constraints avcon2; View created.	加检查约束
	select CONSTRAINT_NAME,TABLE_NAME,STATUS from user_constraints where TABLE_NAME='AV10000'; CONSTRAINT_NAME TABLE_NAME STATUS -------------------------------------------------------------------------------- AVCON2 AV10000 ENABLED	查约束，视图在约束表和表一样都在TABLE_NAME列
	SQL> create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000; SQL> insert into av10000 values('997','peter','1230','a997@b.com',to_date('2021-02-05','YYYY-MM-DD'),'AD_PRES'); SQL>	没有约束符合原表的not null,unique,foreign key,pk才可以插入
	SQL> create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000 with check option constraints avcon2; View created. SQL> insert into av10000 values('998','peter','12300','a998@b.com',to_date('2021-02-05','YYYY-MM-DD'),'AD_PRES'); SQL> insert into av10000 values('997','peter','1230','a997@b.com',to_date('2021-02-05','YYYY-MM-DD'),'AD_PRES');	加了with check option 不符合视图where insert都会失败来源多表的视图不能做insert/delete等操作
	可以用本方法看dbms_metadata.get_ddl可以看当前表、视图、索引、存储过程、功能的再次产生的代码，如果本身是OWNER，其第三个参数可以不用 select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual; 所以用这个可以看create table newtab as select * from oldtable；到底过去了什么只有not null和check其它的约束都没有 select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual; select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual; select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual; SQL> SELECT DBMS_METADATA.GET_DDL('VIEW','AV10000') from dual; CREATE OR REPLACE FORCE EDITIONABLE VIEW "HR"."AV10000" ("EMPLOYEE_ID", "LAST_NAME", "SALARY", "EMAIL", "HIRE_DATE", "JOB_ID") AS select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000	可以查看视图产生的代码 SQL>create view dept_s as select * dept; SQL> create synonym dept_s for dept_v; Synonym created. SQL> drop table dept; Table dropped. SQL> select * from dept_s; select * from dept_s * ERROR at line 1: ORA-04063: view "HR.DEPT_V" has errors 会有一个错误，因为视图是无效的