根据ODI底层表查询项目结构

本文主要是介绍根据ODI底层表查询项目结构，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

    /*ODI项目结构查询 项目-文件夹-（程序包、接口、存储过程）*/SELECT *FROM (SELECT spjt.project_name,spjt.project_code,spjt.i_project,sfld.folder_name,sfld.i_folder,(SELECT wm_concat(sth.full_text)FROM snp_txt_header sth, snp_obj_state sos, snp_object sobWHERE sth.i_txt_orig = 123AND sth.i_txt = sos.i_txt_memoAND sos.i_instance = sfld.i_folderAND sob.i_objects = sos.i_objectAND sob.int_java_name ='com.sunopsis.dwg.dbobj.SnpFolder') AS txt_folder,spck.pack_name AS object_name,spck.i_package AS object_id,'1程序包' AS object_typeFROM snp_project spjt, snp_folder sfld, snp_package spckWHERE 1 = 1AND spjt.i_project = sfld.i_projectAND spck.i_folder = sfld.i_folderUNION ALLSELECT spjt.project_name,spjt.project_code,spjt.i_project,sfld.folder_name,sfld.i_folder,(SELECT wm_concat(sth.full_text)FROM snp_txt_header sth, snp_obj_state sos, snp_object sobWHERE sth.i_txt_orig = 123AND sth.i_txt = sos.i_txt_memoAND sos.i_instance = sfld.i_folderAND sob.i_objects = sos.i_objectAND sob.int_java_name ='com.sunopsis.dwg.dbobj.SnpFolder') AS txt_folder,spp.pop_name AS object_name,spp.i_pop AS object_id,'2接口' AS object_typeFROM snp_project spjt, snp_folder sfld, snp_pop sppWHERE 1 = 1AND spjt.i_project = sfld.i_projectAND spp.i_folder = sfld.i_folderUNION ALLSELECT spjt.project_name,spjt.project_code,spjt.i_project,sfld.folder_name,sfld.i_folder,(SELECT wm_concat(sth.full_text)FROM snp_txt_header sth, snp_obj_state sos, snp_object sobWHERE sth.i_txt_orig = 123AND sth.i_txt = sos.i_txt_memoAND sos.i_instance = sfld.i_folderAND sob.i_objects = sos.i_objectAND sob.int_java_name ='com.sunopsis.dwg.dbobj.SnpFolder') AS txt_folder,stt.trt_name AS object_name,stt.i_trt AS object_id,'3存储过程' AS object_typeFROM snp_project spjt, snp_folder sfld, snp_trt sttWHERE 1 = 1AND spjt.i_project = sfld.i_projectAND stt.i_folder = sfld.i_folderAND stt.trt_type='U')ORDER BY project_name, folder_name, object_type, object_name;/*基础表*/--项目SELECT * FROM snp_project spjt;--文件夹SELECT * FROM snp_folder sfld;--程序包SELECT * FROM snp_package spck;--接口SELECT * FROM snp_pop spp;--存储过程SELECT * FROM snp_trt stt WHERE 1=1 AND stt.trt_type='U';