小程式-Oracle&C#全表格查詢搜尋單一值

前言

目前公司在做MES導入，常發現要功能與Table之間的對應關係>>很麻煩(就算有SA文件)
就做了一個小工具給自己方便查詢，順便拍成DEMO影片

希望能幫助到需要的人
有更好的做法，也真心希望能跟我反映、討論!!
(這也是發文的動力所在 ^^)

YOUTUBE影片(建議觀看) :

使用軟體 :

LINQ PAD專業版

Source Code :

GitHub Demo 連結

Oracle SQL Script :

/*
全表格搜尋，需要使用
表格:
    取得表格跟欄位資訊的 all_tab_columns
    欄位註解 all_col_comments
    表格註解 all_tab_comments
ps:
    其實也可以用user_* 系列
    但個人喜歡 all_* 系列表格 :)
*/
--這邊使用with as 來做表格分解，可讀性會好很多
with table_data as (
    select * from all_tab_columns 
    where owner = 'MES' --排除不必要的使用者資料
    and Data_type = 'NVARCHAR2' --指定想要的資料類型
    --如果想要提升效率，可以自己添加想要的條件，像:以欄位總數量<??為條件
    and sample_size < 50000 and data_length > 20
    and sample_size > 0
)
--接著查詢註解表格
,col_comments as (
    select * from all_col_comments where owner = 'MES'
)
,table_comment as (
    select * from all_tab_comments where owner = 'MES'
)
/*
需要:
    表格名稱
    欄位名稱、ID
    註解
    類型
    大小
*/
select 
    a.table_name--表格名稱
    ,b.comments
    ,a.column_id
    ,a.column_name
    ,c.comments
    ,a.data_type --欄位類型
from table_data A 
left join table_comment B on a.table_name = b.table_name
left join col_comments C on a.table_name = c.table_name and a.COLUMN_NAME = C.COLUMN_NAME
order by a.table_name,a.COLUMN_id --以表格、跟欄位ID做排序

C# 程式:

--接著組合成我們要的查詢

​
void Main()
{
    /*
    C# 這邊的環境:
    需要:
        Dapper (可以在Nuget上搜尋下載)
    感謝收看
        這就是Oracle版本的全表格查詢
        假如有更好的方式，歡迎下方留言
        
        假如有客制化的需求可以去Github下載Source Code
        自己添加、修改
        
        感謝 ^_^
    */
    
    //搜尋字串，這邊以工廠員工人員編號來查詢
    var str_search = "CG179001IM";
    
    //建立connection，這邊用LINQPAD封裝的，請各自調整
    using (var con = this.Connection)
    {
        con.Open();
​
        //把剛剛組合好的sql語句放過來
        var tab_datas = con.Query($@"
/*
全表格搜尋，需要使用
表格:
    取得表格跟欄位資訊的 all_tab_columns
    欄位註解 all_col_comments
    表格註解 all_tab_comments
ps:
    其實也可以用user_* 系列
    但個人喜歡 all_* 系列表格 :)
*/
​
--這邊使用with as 來做表格分解，可讀性會好很多
with table_data as (
    select * from all_tab_columns 
    where owner = 'MES' --排除不必要的使用者資料
    and Data_type = 'NVARCHAR2' --指定想要的資料類型
    --如果想要提升效率，可以自己添加想要的條件，像:以欄位總數量<??為條件
    and sample_size < 50000 and data_length > 20
    and sample_size > 0
)
--接著查詢註解表格
,col_comments as (
    select * from all_col_comments where owner = 'MES'
)
,table_comment as (
    select * from all_tab_comments where owner = 'MES'
)
--接著組合成我們要的查詢
/*
需要:
    表格名稱
    欄位名稱、ID
    註解
    類型
    大小
*/
select 
    a.table_name--表格名稱
    ,b.comments
    ,a.column_id
    ,a.column_name
    ,c.comments
    ,a.data_type --欄位類型
    ,a.sample_size
from table_data A 
left join table_comment B on a.table_name = b.table_name
left join col_comments C on a.table_name = c.table_name and a.COLUMN_NAME = C.COLUMN_NAME
order by a.table_name,a.COLUMN_id --以表格、跟欄位ID做排序
        ").ToList();
        
        //先建立等等會使用到的變數
        //欄位筆數
        var index = 0;
        //總資料數 : sample_size的總和
        var total_data = 0;
        //結果list，使用dynamic來實做
        var list_search_objs = new List<dynamic>();
        //開始組合遍歷查詢的sql
        tab_datas.ForEach(p=>{
            //dynamic 轉 IDictionary 方便 oracle大寫key字串處理
            var ids_p = (IDictionary<string,object>) p;
            index++;
            total_data += p.SAMPLE_SIZE;
            //表格、欄位變數
            var tablename = ids_p["table_name".ToUpper()];
            var colname = ids_p["column_name".ToUpper()];
​
            //驗證是否有符合的資料
            //這邊不使用count > 0 方式，而是使用exist 方式，原因效率高
            var sql = $@"
            select case when (
                exists(
                    select null
                    from {tablename} 
                    where {colname} = :str_search--搜尋字串
                )
            ) then 1 else 0 end value
            from dual 
            ";
            if (con.Query<int>(sql, new {str_search=str_search}).SingleOrDefault() == 1) //如果sql返回1才是有資料
            {
                //假如有資料，取得表格的資料
                var list_objs = con.Query($@"select * from {tablename} where {colname} = :str_search", new {str_search = str_search});
                //結果物件
                var resulet = new {         
                    tablename = tablename,
                    colname = colname,
                    count = list_objs.Count(),
                    result = list_objs
                };
                list_search_objs.Add(resulet);
                $@"NO.{index} 符合條件，資料: {tablename} 表格，{colname} 欄位，共 {list_objs.Count()} 筆。".Println(); //println是我下面寫的擴充方法，個人習慣
            }
        });
        
        //打印所有結果
        $@"====總共查詢 {index} 筆欄位資料，總資料量 : {total_data} ====".Println();
        list_search_objs.Dump();
    }
}
​
// Define other methods and classes here
public static class MyExtension
{
    public static void Println(this object obj)
    {
        Console.WriteLine(obj);
    }
​
    public static void LINQPAD_excel(this object obj)
    {
        var writer = LINQPad.Util.CreateXhtmlWriter();
        writer.Write(obj);
​
​
        string file = $@"{Path.GetTempPath()}{Guid.NewGuid()}.html";
        System.IO.File.WriteAllText(file, writer.ToString());
​
        // Open the file in excel
        Process.Start("Excel", file);
    }
}