视图的编辑

1、pg

create or replace view pg_view_1 AS select id from oper_log;

drop view pg_view_1;

comment on view "public"."test_view_11" is '视图注释12'

2、mysql

create or replace view mysql_view_1 AS select id from dept_info;

drop view mysql_view_1;

不能修改注释；

3、dm

create or replace view dm_view_1 AS select id FROM dm_student;

drop view dm_view_1;

comment on view dm_view_1 is '视图注释'

4、oracle

create or replace view oracle_view_1 AS select dt from test_1;

drop view oracle_view_1;

comment on table oracle_view_1 is '视图注释'

1、dm

--视图数量
SELECT COUNT(*) TOTAL_NUM FROM ALL_VIEWS WHERE OWNER='DATA-MASTER-DEV';

--视图名称、注释
SELECT b.VIEW_NAME,d.CREATED AS CREATE_TIME,d.LAST_DDL_TIME AS UPDATE_TIME,c.COMMENTS
FROM
( SELECT a.*, ROWNUM AS rowno
  FROM (SELECT * FROM ALL_VIEWS WHERE OWNER='DATA-MASTER-DEV' ORDER BY VIEW_NAME ASC) a
  WHERE ROWNUM <= 10
) b
LEFT JOIN all_tab_comments c ON b.VIEW_NAME=c.TABLE_NAME AND c.OWNER=b.OWNER
LEFT JOIN ALL_OBJECTS d ON d.OWNER=b.OWNER AND b.VIEW_NAME = d.OBJECT_NAME
WHERE b.rowno > 0;

--视图字段信息
SELECT A.VIEW_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.DATA_LENGTH, C.DATA_PRECISION, C.DATA_SCALE
FROM ALL_VIEWS A
JOIN ALL_TAB_COLUMNS C ON A.OWNER = C.OWNER AND A.VIEW_NAME = C.TABLE_NAME
WHERE A.OWNER='DATA-MASTER-DEV'
AND A.VIEW_NAME IN('dm_view_1');

--视图定义
SELECT VIEW_NAME, TEXT FROM ALL_VIEWS WHERE OWNER='DATA-MASTER-DEV'
AND VIEW_NAME IN('dm_view_1');

2、oracle

--视图数量
SELECT COUNT(*) TOTAL_NUM FROM ALL_VIEWS WHERE OWNER='LDS';

--视图名称、注释
SELECT b.VIEW_NAME, d.CREATED CREATE_TIME, d.LAST_DDL_TIME UPDATE_TIME, c.COMMENTS
FROM
( SELECT a.*, ROWNUM AS rowno FROM
  (SELECT * FROM ALL_VIEWS WHERE OWNER='LDS' ORDER BY VIEW_NAME ASC) a 
  WHERE ROWNUM <= 10
) b
LEFT JOIN all_tab_comments c ON c.OWNER=b.OWNER AND b.VIEW_NAME=c.TABLE_NAME
LEFT JOIN ALL_OBJECTS d ON d.OWNER=b.OWNER AND b.VIEW_NAME = d.OBJECT_NAME
WHERE b.rowno > 0;

--视图字段信息
SELECT A.VIEW_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.DATA_LENGTH, C.DATA_PRECISION, C.DATA_SCALE, D.COMMENTS
FROM ALL_VIEWS A, ALL_TAB_COLUMNS C, ALL_COL_COMMENTS D
WHERE A.OWNER='LDS' AND A.OWNER = C.OWNER AND A.VIEW_NAME = C.TABLE_NAME
AND C.OWNER=D.OWNER AND C.TABLE_NAME=D.TABLE_NAME AND C.COLUMN_NAME=D.COLUMN_NAME
AND A.VIEW_NAME IN('oracle_view_1','oracle_view_2');

--视图定义
SELECT VIEW_NAME, TEXT FROM ALL_VIEWS WHERE OWNER='LDS'
AND VIEW_NAME IN('oracle_view_1','oracle_view_2');

3、mysql

--视图数量
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='rbc_test' AND TABLE_TYPE='VIEW';

--视图名称、注释
SELECT TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA='rbc_test' AND TABLE_TYPE='VIEW' 
order by TABLE_NAME 
LIMIT 0,10;

--视图字段信息
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS DATA_LENGTH,
		CASE
		WHEN NUMERIC_PRECISION IS NOT NULL THEN NUMERIC_PRECISION
		WHEN DATETIME_PRECISION IS NOT NULL THEN DATETIME_PRECISION
		ELSE ''
		END AS DATA_PRECISION,
		NUMERIC_SCALE AS DATA_SCALE, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='rbc_test' AND TABLE_NAME IN ('mysql_view_1','mysql_view_2') order by ORDINAL_POSITION asc;

--视图定义
SELECT TABLE_NAME, VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_SCHEMA='rbc_test' AND TABLE_NAME IN('mysql_view_1','mysql_view_2');

4、pg

--视图数量
SELECT COUNT(*) TOTAL_NUM FROM
pg_class AS a, pg_namespace AS b
WHERE a.relnamespace=b.oid AND b.nspname='public' AND (a.relkind='m' or a.relkind='v');

--视图名称、注释
SELECT a.relname AS VIEW_NAME,
cast(OBJ_DESCRIPTION(a.relfilenode,'pg_class') as varchar) AS COMMENT
FROM pg_class AS a
JOIN pg_namespace AS b ON a.relnamespace=b.OID
WHERE b.nspname='public' and (a.relkind='m' or a.relkind='v') 
order by a.relname 
LIMIT 10 OFFSET 0;

--视图字段信息
SELECT a.relname AS VIEW_NAME,
b.attname AS COLUMN_NAME,
format_type(b.atttypid,b.atttypmod) as DATA_TYPE,
col_description(b.attrelid, b.attnum) AS COMMENT,
CASE WHEN atttypid IN (1042,1043) THEN
CASE WHEN atttypmod = -1 THEN null
ELSE atttypmod - 4
END
ELSE null
END AS DATA_LENGTH
FROM pg_class AS a 
join pg_attribute AS b ON b.attrelid = a.oid AND b.attnum>0
join pg_type c on c.oid = b.atttypid
JOIN pg_namespace d ON a.relnamespace=d.OID
where d.nspname='public' and (a.relkind='m' or a.relkind='v')
AND a.relname IN ('test_view_1','test_view_2') 
order by b.attnum asc;

--视图定义
SELECT viewname, definition FROM pg_views 
where schemaname='public' AND viewname in('test_view_1','test_view_2');