-- 自动创建查询视图 vw_bas_agency_full_query
-- 用于查询 BAS_AGENCY_INFO(主表)、BAS_AGENCY_EXT(扩展表)、BAS_AGENCY_EXT_ELE(补充信息表)
-- 三个表都有的字段以主表为准
declare
  v_sql varchar2(32767);
begin
  -- sql 头部
  v_sql := 'CREATE OR REPLACE VIEW vw_bas_agency_full_query AS SELECT ';
  -- sql 查询 字段
  -- BAS_AGENCY_INFO 表
  for infoRow in (SELECT t.COLUMN_NAME
                    FROM user_tab_columns T
                   where t.TABLE_NAME = 'BAS_AGENCY_INFO'
                   order by t.COLUMN_ID) loop
    v_sql := v_sql || '  A.' || infoRow.COLUMN_NAME || ',';
  end loop;

  --  BAS_AGENCY_EXT 表
  dbms_output.put_line('');
  for extRow in (SELECT t.COLUMN_NAME
                   FROM user_tab_columns T
                  where t.TABLE_NAME = 'BAS_AGENCY_EXT'
                    and t.COLUMN_NAME not in
                        (SELECT t2.COLUMN_NAME
                           FROM user_tab_columns T2
                          where t2.TABLE_NAME in ('BAS_AGENCY_INFO'))
                  order by t.COLUMN_ID) loop
    v_sql := v_sql || '  B.' || extRow.COLUMN_NAME || ',';
  end loop;

  -- BAS_AGENCY_EXT_ELE 表
  dbms_output.put_line('');
  for extRow in (SELECT t.COLUMN_NAME
                   FROM user_tab_columns T
                  where t.TABLE_NAME = 'BAS_AGENCY_EXT_ELE'
                    and t.COLUMN_NAME not in
                        (SELECT t2.COLUMN_NAME
                           FROM user_tab_columns T2
                          where t2.TABLE_NAME in
                                ('BAS_AGENCY_INFO', 'BAS_AGENCY_EXT'))
                  order by t.COLUMN_ID) loop
    v_sql := v_sql || '  C.' || extRow.COLUMN_NAME || ',';
  end loop;

  -- 去掉结尾多余的逗号
  v_sql := substr(v_sql, 1, length(v_sql) - 1);

  -- sql 尾部
  dbms_output.put_line('');
  v_sql := v_sql || '  FROM bas_agency_info A
  LEFT JOIN BAS_AGENCY_EXT B
    ON a.agency_id = b.agency_id
  LEFT JOIN BAS_AGENCY_EXT_ELE C
    ON a.biz_key = c.biz_key
 WHERE A.is_deleted = 2 AND A.version LIKE ''%rcdtwh''';

  -- 打印 sql
  dbms_output.put_line(v_sql);

  -- 执行 SQL
  execute immediate v_sql;

end;