获取各数据库的存储过程和函数_获取数据库存储过程的指令-CSDN博客

本文链接：https://blog.csdn.net/u012269637/article/details/131111518

该代码片段展示了如何根据不同类型的数据库（如Oracle,GaussDB,MySQL等）编写SQL查询来获取存储过程和函数的信息。它使用预编译的SQL语句，根据数据库类型设置特定的查询条件，如所有者、对象类型等。

摘要由CSDN通过智能技术生成

获取各数据库的存储过程
if (DatabaseType.Oracle == (databaseType)) {//oracle
             sql = "select all_procedures.*,user_source.TEXT from all_procedures  " +
                     "inner join user_source " +
                     "on all_procedures.OBJECT_NAME=user_source.name " +
                     "where owner= ? and object_type='PROCEDURE'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, SchemaOrDbService);
         }else if ( DatabaseType.GaussDB100 == (databaseType)) {
             sql = "select * from all_procedures  " +
                     "where owner=? and object_type='PROCEDURE'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getUserName().toUpperCase());
         }else if ( DatabaseType.GaussDBHWY == (databaseType)) {
   sql = "select oid::regprocedure as proname,prosrc from pg_proc where pronamespace=(SELECT oid  FROM sys_namespace where nspname =?) and prokind='p' or prokind='P' ";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaName());
}else if(DatabaseType.DM == (databaseType)){
           sql =  "select pro.PROCEDURE_NAME as OBJECT_NAME,sysText.TXT as TEXT  from "+
                   " (SELECT  DISTINCT NULL AS PROCEDURE_CAT,SCHEMAS.NAME AS PROCEDURE_SCHEM,PROCS.NAME AS PROCEDURE_NAME, "+
                       "   'RESERVED1' AS RESERVED1,'RESERVED2' AS RESERVED2,'RESERVED3' AS RESERVED3, NULL AS REMARKS, 1 AS PROCEDURE_TYPE ,PROCS.ID "+
                        "  FROM (SELECT ID, PID, NAME FROM SYS.SYSOBJECTS WHERE TYPE$ = 'SCH' AND NAME LIKE ?  ESCAPE '!' )SCHEMAS,"+
                        "  (SELECT ID, SCHID, NAME FROM SYS.SYSOBJECTS WHERE TYPE$ = 'SCHOBJ' AND SUBTYPE$ = 'PROC' AND INFO1 = 1)PROCS WHERE SCHEMAS.ID = PROCS.SCHID "+ 
                        "  ) pro inner join SYSTEXTS sysText on pro.ID = sysText.id ";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, SchemaOrDbService);
         }else if (DatabaseType.MySQL == (databaseType)) {//必须具有读取msyql系统数据库的权限
           if(databaseMajorVersion >= 8){
              sql = "SHOW PROCEDURE STATUS WHERE db=? ";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, dataSource.getDbServiceName());
           }else{
              sql = "select * from mysql.proc where db= ? and type='PROCEDURE'";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, dataSource.getDbServiceName());
           }
         }else if(DatabaseType.SQLServer == (databaseType)|| DatabaseType.SQLServer2000 == (databaseType)){
             sql="select sysobjects.*,OBJECT_DEFINITION (id) AS ObjectDefinition  from sysobjects where xtype='P'";
   stmt = conn.prepareStatement(sql);
         }else if(DatabaseType.GBase == (databaseType)) {
           sql = "select * from gbase.proc where db= ?  and type='PROCEDURE'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getDbServiceName());
         }else if (DatabaseType.Sybase == (databaseType)) {//Sybase
             sql = " select * from ?.dbo.sysobjects where type='P'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaName());
         }else if (DatabaseType.Teradata == (databaseType)) {//Teradata
             sql = " select * from    Tables where DatabaseName=? and  TableKind='P'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getDbServiceName());
         }

获取各数据库的函数

 if (DatabaseType.PostgreSQL == (databaseType)
              || DatabaseType.GreenPlum == (databaseType)
              || DatabaseType.GaussDB200 == (databaseType)
              || DatabaseType.HWMPPDB == (databaseType)
      || DatabaseType.KADB == (databaseType)) {//postgresql
           sql = "select oid::regprocedure as proname,prosrc from pg_proc where pronamespace=(SELECT oid  FROM pg_namespace where nspname =?)";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaName());
           schema = new Schema(null, dataSource.getSchemaName());
         } else if (DatabaseType.GaussDBHWY == (databaseType)) {//
   sql = "select oid::regprocedure as proname,prosrc from sys_proc where pronamespace=(SELECT oid  FROM sys_namespace where nspname =?) and prokind='f' ";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaName());
   schema = new Schema(null, dataSource.getSchemaName());
} else if (DatabaseType.KES == (databaseType)) {//
   sql = "select oid::regprocedure as proname,prosrc from sys_proc where pronamespace=(SELECT oid  FROM sys_namespace where nspname =?)";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaName());
   schema = new Schema(null, dataSource.getSchemaName());
}else if (DatabaseType.Oracle == (databaseType)) {//oracle
             sql = "select all_procedures.*,user_source.TEXT from all_procedures  " +
                     "inner join user_source " +
                     "on all_procedures.OBJECT_NAME=user_source.name " +
                     "where owner=? and object_type='FUNCTION'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaOrDbService());
         }else if (DatabaseType.GaussDB100 == (databaseType)) {//
             sql = "select * from all_procedures  " +
                     "where owner=? and object_type='FUNCTION'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getUserName().toUpperCase());
         }else if(DatabaseType.DM == (databaseType)){
           sql =  "select pro.PROCEDURE_NAME as OBJECT_NAME,sysText.TXT as TEXT  from "+
                   " (SELECT  DISTINCT NULL AS PROCEDURE_CAT,SCHEMAS.NAME AS PROCEDURE_SCHEM,PROCS.NAME AS PROCEDURE_NAME, "+
                       "   'RESERVED1' AS RESERVED1,'RESERVED2' AS RESERVED2,'RESERVED3' AS RESERVED3, NULL AS REMARKS, 1 AS PROCEDURE_TYPE ,PROCS.ID "+
                        "  FROM (SELECT ID, PID, NAME FROM SYS.SYSOBJECTS WHERE TYPE$ = 'SCH' AND NAME LIKE ?  ESCAPE '!' )SCHEMAS,"+
                        "  (SELECT ID, SCHID, NAME FROM SYS.SYSOBJECTS WHERE TYPE$ = 'SCHOBJ' AND SUBTYPE$ = 'PROC' AND INFO1 = 0)PROCS WHERE SCHEMAS.ID = PROCS.SCHID "+ 
                        "  ) pro inner join SYSTEXTS sysText on pro.ID = sysText.id ";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaOrDbService());
         }else if (DatabaseType.MySQL == (databaseType)) {//必须具有读取msyql系统数据库的权限
           if(databaseMajorVersion >= 8){
              sql = "SHOW FUNCTION STATUS WHERE db=?";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, dataSource.getDbServiceName());
           }else{
              sql = "select * from mysql.proc where db=? and type='FUNCTION' ";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, dataSource.getDbServiceName());
           }
         } else if(DatabaseType.SQLServer == (databaseType)|| DatabaseType.SQLServer2000 == (databaseType)){
             sql="select sysobjects.*,OBJECT_DEFINITION (id) AS ObjectDefinition  from sysobjects where xtype in('FN','IF','TF')";
   stmt = conn.prepareStatement(sql);
         }else if(DatabaseType.GBase == (databaseType) ) {
            sql = "select * from gbase.proc where db= ? and type='FUNCTION' ";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getDbServiceName());
         }else if (DatabaseType.Sybase == (databaseType)) {//Sybase
             sql = " select * from ?.dbo.sysobjects where type='F'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getSchemaName());
         }else if (DatabaseType.Teradata == (databaseType)) {//Teradata
             sql = " select * from    Tables where DatabaseName=? and  TableKind='M'";
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getDbServiceName());

   stmt = conn.prepareStatement(sql);
   stmt.setString(1, dataSource.getDbServiceName());
         }