oracle 程序列出表名和对应的计数 [英] oracle procedure to list table names and corresponding count

问题描述

我遇到了一个问题.我有一个名为 gd_table_order 的表，其中包含表名.我需要将每个表的 table_name 和计数设置为目标.目标是一个视图.

I am stucked with an issue. I have a table called gd_table_order which contains tablenames. And i need to get the table_name and count of each table to a target one. target is a view.

我只举了一个有2列的例子，有10列是这样的.所以在程序中我们需要输入这个作为参数.

I only gave an example with 2 columns, there are 10 columns like this.So in the procedure we need to enter this as a parameter.

并且将生成相应的 10 个视图.以下是 2 个视图的示例.

And there will be corresponsding 10 views to be generate. below is a sample of 2 views.

V_CHECK_RECORDS_* 是输出视图名称

V_CHECK_RECORDS_* is the output view name

你能帮忙吗?

推荐答案

我不确定这里的确切要求是什么，但您可以使用以下方法从每个表中获取记录数.

I am not sure what is exact requirement here, But you can use the following approach to fetch the number of records from each table.

SQL> -- This is sample data
SQL> WITH SAMPLE_DATA(TNAME) AS
  2  (SELECT 'CUSTOMERS' FROM DUAL UNION ALL
  3  SELECT 'INTERVAL_TAB' FROM DUAL)
  4  -- Your query starts from here
  5  SELECT TABLE_NAME,
  6         TO_NUMBER(
  7         EXTRACTVALUE( XMLTYPE(
  8         DBMS_XMLGEN.GETXML('select count(*) c from ' || U.TABLE_NAME)
  9         ), '/ROWSET/ROW/C')) COUNT
 10    FROM USER_TABLES U JOIN SAMPLE_DATA S ON S.TNAME = U.TABLE_NAME;

TABLE_NAME           COUNT
--------------- ----------
CUSTOMERS                1
INTERVAL_TAB             0

SQL>

-- 更新

您可以按如下方式生成视图:

You can generate the view as follows :

-- 更新了本节

CREATE OR REPLACE VIEW V_CHECK_RECORDS_AUS AS
SELECT TABLE_NAME,
       TO_NUMBER(
           EXTRACTVALUE( XMLTYPE(
                   DBMS_XMLGEN.GETXML('select count(*) c from ' 
                      || U.TABLE_NAME || ' WHERE oe_name=''BUL''')
               ), '/ROWSET/ROW/C')) NUM_ROWS
  FROM USER_TAB_COLUMNS U JOIN GD_TABLE_ORDER S ON S.TABLE_NAME_AUS = U.TABLE_NAME 
 WHERE U.COLUMN_NAME = 'OE_NAME';

以同样的方式您可以生成其他视图.

In the same way you can generate other views.

-- 进一步更新

CREATE OR REPLACE VIEW V_CHECK_RECORDS_AUS AS
SELECT TABLE_NAME,
       CASE WHEN U.COLUMN_NAME IS NOT NULL THEN TO_NUMBER(
           EXTRACTVALUE( XMLTYPE(
                   DBMS_XMLGEN.GETXML('select count(*) c from ' 
                      || U.TABLE_NAME || ' WHERE ' || U.COLUMN_NAME || '=''BUL''')
               ), '/ROWSET/ROW/C')) 
         ELSE 0 END NUM_ROWS
  FROM GD_TABLE_ORDER S LEFT JOIN USER_TAB_COLUMNS U 
  ON S.TABLE_NAME_AUS = U.TABLE_NAME AND U.COLUMN_NAME = 'OE_NAME';

这篇关于oracle 程序列出表名和对应的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！