Oracle/MySQL/SQL Server创建/删除视图

创建视图

• Oracle

  CREATE OR REPLACE VIEW V_USER
  AS
  SELECT 
      ID,
      NAME,
      AGE
  FROM T_USER;
  COMMENT ON TABLE V_USER IS '用户视图';
  COMMENT ON COLUMN V_USER.ID IS 'ID';
  COMMENT ON COLUMN V_USER.NAME IS '名称';
  COMMENT ON COLUMN V_USER.AGE IS '年龄';

  
  
• MySQL

  CREATE OR REPLACE VIEW V_USER
  AS
  SELECT 
      ID,
      NAME,
      AGE
  FROM T_USER;
  // 不支持写备注

  
  
• SQL Server

  CREATE VIEW V_USER
  AS
  SELECT 
      ID,
      NAME,
      AGE
  FROM T_USER
  GO
  
  if exists (select 1 from  sys.extended_properties
             where major_id = object_id('V_USER') and minor_id = 0)
  begin
     declare @CurrentUser sysname
  select @CurrentUser = user_name()
  execute sp_dropextendedproperty 'MS_Description', 
     'user', @CurrentUser, 'view', 'V_USER'
  
  end
  
  
  select @CurrentUser = user_name()
  execute sp_addextendedproperty 'MS_Description', 
     '用户视图',
     'user', @CurrentUser, 'view', 'V_USER'
  GO
  
  if exists(select 1 from sys.extended_properties p where
        p.major_id = object_id('V_USER')
    and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ID')
  )
  begin
     declare @CurrentUser sysname
  select @CurrentUser = user_name()
  execute sp_dropextendedproperty 'MS_Description', 
     'user', @CurrentUser, 'view', 'V_USER', 'column', 'ID'
  
  end
  
  
  select @CurrentUser = user_name()
  execute sp_addextendedproperty 'MS_Description', 
     'ID',
     'user', @CurrentUser, 'view', 'V_USER', 'column', 'ID'
  GO
  
  if exists(select 1 from sys.extended_properties p where
        p.major_id = object_id('V_USER')
    and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'NAME')
  )
  begin
     declare @CurrentUser sysname
  select @CurrentUser = user_name()
  execute sp_dropextendedproperty 'MS_Description', 
     'user', @CurrentUser, 'view', 'V_USER', 'column', 'NAME'
  
  end
  
  
  select @CurrentUser = user_name()
  execute sp_addextendedproperty 'MS_Description', 
     '名称',
     'user', @CurrentUser, 'view', 'V_USER', 'column', 'NAME'
  GO
  
  if exists(select 1 from sys.extended_properties p where
        p.major_id = object_id('V_USER')
    and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'AGE')
  )
  begin
     declare @CurrentUser sysname
  select @CurrentUser = user_name()
  execute sp_dropextendedproperty 'MS_Description', 
     'user', @CurrentUser, 'view', 'V_USER', 'column', 'AGE'
  
  end
  
  
  select @CurrentUser = user_name()
  execute sp_addextendedproperty 'MS_Description', 
     '年龄',
     'user', @CurrentUser, 'view', 'V_USER', 'column', 'AGE'
  GO

  
  

删除视图

   

DROP VIEW V_USER; // Oracle/MySQL/SQL Server均支持

DROP VIEW IF EXISTS V_USER; // 仅MySQL支持