C#程序实现将MySQL的存储过程转换成Oracle的存储过程

2025-10-19 10:37 开发作者： weixin_30777913

技术栈：

.NET Core 3.1+ 或 .NET 5/6/7/8 控制台应用
使用 System, System.Text.RegularExpressions, System.Text, System.Text.json, System.Collections.Generic

Program.cs（完整代码）

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Text.Json;

namespace MySqlToOracleConverter
{
    // 数据结构扩展：新增包名配置、权限角色信息
    public class ProcedureAnalysisResult
    {
        public string ProcedureName { get; set; } = "";
        public string PackageName { get; set; } = "PKG_MYSQL_CONVERT"; // 默认包名，可自定义
        public List<string> GrantRoles { get; set; } = new List<string> { "APP_USER", "ADMIN" }; // 默认授权角色
        public List<ParameterInfo> Parameters { get; set; } = new List<ParameterInfo>();
        public string FunctionDescription { get; set; } = "请手动补充：根据代码逻辑归纳业务功能。";
        public List<string> DmlStatements { get; set; } = new List<string>();
        public List<SelectInfo> SelectQueries { get; set; } = new List<SelectInfo>();
        public List<VariableInfo> Variables { get; set; } = new List<VariableInfo>();
        public List<ControlFlowInfo> ControlFlows { get; set; } = new List<ControlFlowInfo>();
        public List<CursorInfo> Cursors { get; set; } = new List<CursorInfo>();
        public List<string> ExceptionHandlers { get; set; } = new List<string>();
        public List<string> CalledProceduresFunctions { get; set; } = new List<string>();
        public List<string> UserVariables { get; set; } = new List<string>();
        public List<TempTableInfo> TempTables { get; set; } = new List<TempTableInfo>();
        public List<string> TransactionControls { get; set; } = new List<string>();
        public List<string> AutoIncrementTables { get; set; } = new List<string>();
        public List<BATchInsertInfo> BatchInserts { get; set; } = new List<BatchInsertInfo>();
    }

    // 原有数据结构保持不变
    public class BatchInsertInfo
    {
        public string TableName { get; set; } = "";
        public string Columns { get; set; } = "";
        public List<string> ValueRows { get; set; } = new List<string>();
    }

    public class SequenceInfo
    {
        public string SequenceName { get; set; } = "";
        public string TableName { get; set; } = "";
        public string ColumnName { get; set; } = "id";
        public int StartWith { get; set; } = 1;
        public int IncrementBy { get; set; } = 1;
    }

    public class TempTableInfo
    {
        public string TableName { get; set; } = "";
        public string ColumnDefinitions { get; set; } = "";
    }

    public class ParameterInfo
    {
        public string Mode { get; set; } = "";
        public string Name { get; set; } = "";
        public string DataType { get; set; } = "";
        public int? TypeLength { get; set; }
    }

    public class SelectInfo
    {
        public string Sql { get; set; } = "";
        public string UsageHint { get; set; } = "可能是赋值或返回结果集";
    }

    public class VariableInfo
    {
        public string Name { get; set; } = "";
        public string DataType { get; set; } = "";
        public int? TypeLength { get; set; }
        public string UsageHint { get; set; } = "";
    }

    public class ControlFlowInfo
    {
        public string Type { get; set; } = "";
        public string ContentSnippet { get; set; } = "";
        public string OriginalCode { get; set; } = "";
    }

    public class CursorInfo
    {
        public string CursorName { get; set; } = "";
        public string SelectQuery { get; set; } = "";
        public string FetchInto { get; set; } = "";
        public string LoopLabel { get; set; } = "";
    }

    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("=== MySQL 存储过程转 Oracle 工具（企业级终极版） ===");
            Console.WriteLine("请输入您的 MySQL 存储过程代码（可多行，以 ===END=== 结束输入）：");

            string input = ReadMultilineInput("===");
            var analysis = AnalyzeMySqlStoredProcedure(input);

            // 允许用户自定义包名和授权角色
            Console.WriteLine($"\n当前默认包名：{analysis.PackageName}，默认授权角色：{string.Join(",", analysis.GrantRoles)}");
            Console.WriteLine("是否使用默认配置？（输入N修改，其他键使用默认）：");
            string customConfig = Console.ReadLine()?.Trim().ToUpper();
            if (customConfig == "N")
            {
                Console.WriteLine("请输入自定义包名（如PKG_USER_MANAGE）：");
                string customPkg = Console.ReadLine()?.Trim();
                if (!string.IsNullOrEmpty(customPkg)) analysis.PackageName = customPkg;

                Console.WriteLine("请输入授权角色（多个用逗号分隔，如APP_USER,ADMIN）：");
                string customRoles = Console.ReadLine()?.Trim();
                if (!string.IsNullOrEmpty(customRoles)) analysis.GrantRoles = customRoles.Split(',').ToList();
            }

            string json = JsonSerializer.Serialize(analysis, new JsonSerializerOptions { WriteIndented = true });
            Console.WriteLine("\n=== 分析结果（结构化 JSON） ===");
            Console.WriteLine(json);

            Console.WriteLine("\n=== 生成 Oracle 企业级代码（含PACKAGE+权限） ===");
            string oracleCode = GenerateOracleEnterpriseCode(analysis);
            Console.WriteLine(oracleCode);
        }

        static string ReadMultilineInput(string endMarker)
        {
            string input = "";
            string line;
            while (!string.IsNullOrEmpty(line = Console.ReadLine()))
            {
                if (line.Trim() == endMarker)
                    break;
                input += line + "\n";
            }
            return input;
        }

        static ProcedureAnalysisResult AnalyzeMySqlStoredProcedure(string sql)
        {
            var result = new ProcedureAnalysisResult();
            ExtractProcedureNameAndParams(sql, result);
            ExtractDmlStatements(sql, result);
            ExtractSelectQueries(sql, result);
            ExtractVariables(sql, result);
            ExtractControlFlows(sql, result);
            ExtractCursors(sql, result);
            ExtractExceptionHandlers(sql, result);
            ExtractCalledProceduresAndFunctions(sql, result);
            ExtractUserVariables(sql, result);
            ExtractTempTables(sql, result);
            ExtractTransactionControls(sql, result);
            ExtractAutoIncrementTables(sql, result);
            ExtractBatchInserts(sql, result);
            return result;
        }

        #region 原有提取方法（保持兼容，无修改）
        static void ExtractProcedureNameAndParams(string sql, ProcedureAnalysisResult r)
        {
            var procMatch = Regex.Match(sql, @"CREATE\s+PROCEDURE\s+(?:IF NOT EXISTS\s+)?([^\s(]+)\s*\((.*?)\)", RegexOptions.IgnoreCase);
            if (procMatch.Success)
            {
                r.ProcedureName = procMatch.Groups[1].Value.Trim();
                string paramsSection = procMatch.Groups[2].Value.Trim();
                if (!string.IsNullOrEmpty(paramsSection))
                {
                    var paramMatches = Regex.Matches(paramsSection, @"(IN|OUT|INOUT)\s+([^\s,]+)\s+([^\s,(]+)(?:\((\d+)\))?", RegexOptions.IgnoreCase);
                    foreach (Match m in paramMatches)
                    {
                        if (m.Groups.Count >= 4)
                        {
                            r.Parameters.Add(new ParameterInfo
                            {
                                Mode = m.Groups[1].Value.Trim().ToUpper(),
    python                            Name = m.Groups[2].Value.Trim(),
                                DataType = m.Groups[3].Value.Trim().ToUpper(),
                                TypeLength = m.Groups[4].Success ? int.Parse(m.Groups[4].Value) : (int?)null
                            });
                        }
                    }
                }
            }
        }

        static void ExtractAutoIncrementTables(string sql, ProcedureAnalysisResult r)
        {
            var createTableMatches = Regex.Matches(sql, @"CREATE\s+(TEMPORARY\s+)?TABLE\s+([^\s(]+)\s*\([^)]*AUTO_INCREMENT[^)]*\)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in createTableMatches)
            {
                string tableName = m.Groups[2].Value.Trim();
                if (!r.AutoIncrementTables.Contains(tableName) && !string.IsNullOrEmpty(tableName))
                    r.AutoIncrementTables.Add(tableName);
            }

            var insertMatches = Regex.Matches(sql, @"INSERT\s+INTO\s+([^\s(]+)\s*\([^)]*\)\s+VALUES", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in insertMatches)
            {
                string tableName = m.Groups[1].Value.Trim();
                string columns = Regex.Match(m.Value, @"\(([^)]*)\)", RegexOptions.Singleline).Groups[1].Value;
                if (!columns.Contains("id", StringComparison.OrdinalIgnoreCase) && !r.AutoIncrementTables.Contains(tableName))
                    r.AutoIncrementTables.Add(tableName);
            }
        }

        static void ExtractBatchInserts(string sql, ProcedureAnalysisResult r)
        {
            var batchMatches = Regex.Matches(sql, @"INSERT\s+INTO\s+([^\s(]+)\s*\(([^)]*)\)\s+VALUES\s*\(([^;]*)\);", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in batchMatches)
            {
                if (m.Groups.Count < 4) continue;
                string tableName = m.Groups[1].Value.Trim();
                string columns = m.Groups[2].Value.Trim();
                string valuesblock = m.Groups[3].Value.Trim();

                var valueRows = Regex.Split(valuesBlock, @"\)\s*,\s*\(");
                List<string> cleanRows = new List<string>();
                foreach (var row in valueRows)
                {
                    string cleanRow = row.Trim().Trim('(').Trim(')');
                    if (!string.IsNullOrEmpty(cleanRow))
                        cleanRows.Add($"({cleanRow})");
                }

                if (cleanRows.Count > 1)
                {
                    r.BatchInserts.Add(new BatchInsertInfo
                    {
                        TableName = tableName,
                        Columns = columns,
                        ValueRows = cleanRows
                    });
                }
            }
        }

        static void ExtractVariables(string sql, ProcedureAnalysisResult r)
        {
            var varMatches = Regex.Matches(sql, @"DECLARE\s+([^\s]+)\s+([^\s,(]+)(?:\((\d+)\))?(?:\s+DEFAULT\s+[^;]+)?", RegexOptions.IgnoreCase);
            foreach (Match m in varMatches)
            {
                if (m.Groups.Count >= 3)
                {
                    r.Variables.Add(new VariableInfo
                    {
                        Name = m.Groups[1].Value.Trim(),
                        DataType = m.Groups[2].Value.Trim().ToUpper(),
                        TypeLength = m.Groups[3].Success ? int.Parse(m.Groups[3].Value) : (int?)null
                    });
                }
            }
        }

        static void ExtractControlFlows(string sql, ProcedureAnalysisResult r)
        {
            var ifMatches = Regex.Matches(sql, @"\bIF\b.*?\bEND IF\b;", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in ifMatches) r.ControlFlows.Add(new ControlFlowInfo { Type = "IF", OriginalCode = m.Value.Trim() });

            var whileMatches = Regex.Matches(sql, @"\bWHILE\b.*?\bEND WHILE\b;", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in whileMatches) r.ControlFlows.Add(new ControlFlowInfo { Type = "WHILE", OriginalCode = m.Value.Trim() });

            var loopMatches = Regex.Matches(sql, @"\bLOOP\b.*?\bEND LOOP\b;", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in loopMatches) r.ControlFlows.Add(new ControlFlowInfo { Type = "LOOP", OriginalCode = m.Value.Trim() });

            var caseMatches = Regex.Matches(sql, @"\bCASE\b.*?\bEND CASE\b;", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in caseMatches) r.ControlFlows.Add(new ControlFlowInfo { Type = "CASE", OriginalCode = m.Value.Trim() });
        }

        static void ExtractCursors(string sql, ProcedureAnalysisResult r)
        {
            var cursorDeclares = Regex.Matches(sql, @"DECLARE\s+([^\s]+)\s+CURSOR\s+FOR\s+(.+?);", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match declareMatch in cursorDeclares)
            {
                string cursorName = declareMatch.Groups[1].Value.Trim();
                string selectQuery = declareMatch.Groups[2].Value.Trim();
                string fetchPattern = $@"FETCH\s+{cursorName}\s+INTO\s+([^;]+);";
                var fetchMatch = Regex.Match(sql, fetchPattern, RegexOptions.IgnoreCase | RegexOptions.Singleline);
                string loopLabelPattern = $@"(\w+):\s+LOOP\s+.*?FETCH\s+{cursorName}";
                var loopLabelMatch = Regex.Match(sql, loopLabelPattern, RegexOptions.IgnoreCase | RegexOptions.Singleline);

                r.Cursors.Add(new CursorInfo
                {
                    CursorName = cursorName,
                    SelectQuery = selectQuery,
                    FetchInto = fetchMatch.Success ? fetchMatch.Groups[1].Value.Trim() : "",
                    LoopLabel = loopLabelMatch.Success ? loopLabelMatch.Groups[1].Value.Trim() : $"{cursorName}_loop"
                });
            }
        }

        static void ExtractTempTables(string sql, ProcedureAnalysisResult r)
        {
            var tempMatches = Regex.Matches(sql, @"CREATE\s+TEMPORARY\s+TABLE\s+([^\s(]+)\s*\((.*?)\);", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in tempMatches)
            {
                if (m.Groups.Count >= 3)
                {
                    r.TempTables.Add(new TempTableInfo
                    {
                        TableName = m.Groups[1].Value.Trim(),
                        ColumnDefinitions = m.Groups[2].Value.Trim()
                    });
                }
            }
        }

        static void ExtractDmlStatements(string sql, ProcedureAnalysisResult r)
        {
            var dmlKeywords = new[] { "INSERT", "UPDATE", "DELETE" };
            foreach (var keyword in dmlKeywords)
            {
                var matches = Regex.Matches(sql, $@"\b{keyword}\b[^;]*;", RegexOptions.IgnoreCase);
                foreach (Match m in matches) r.DmlStatements.Add(m.Value.Trim());
            }
        }

        static void ExtractSelectQueries(string sql, ProcedureAnalysisResult r)
        {
            var selectMatches = Regex.Matches(sql, @"\bSELECT\b[^;]*;", RegexOptions.IgnoreCase);
            foreach (Match m in selectMatches)
            {
                r.SelectQueries.Add(new SelectInfo
                {
                    Sql = m.Value.Trim(),
                    UsageHint = m.Value.IndexOf("INTO", StringComparison.OrdinalIgnoreCase) >= 0 ? "赋值语句（INTO）" : "结果集查询"
                });
            }
        }

        static void ExtractExceptionHandlers(string sql, ProcedureAnalysisResult r)
        {
            var handlerMatches = Regex.Matches(sql, @"DECLARE\s+HANDLER\s+FOR\s+(.+?)\s+(.+?);", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            foreach (Match m in handlerMatches) r.ExceptionHandlers.Add(m.Value.Trim());
        }

        static void ExtractCalledProceduresAndFunctions(string sql, ProcedureAnalysisResult r)
        {
            var callMatches = Regex.Matches(sql, @"\bCALL\s+([^\s(]+)|\b([^\s(]+)\s*\(", RegexOptions.IgnoreCase);
            foreach (Match m in callMatches)
            {
                foreach (Group g in m.Groups)
                {
                    if (g.Success && !string.IsNullOrEmpty(g.Value) && !g.Value.Equals("CALL", StringComparison.OrdinalIgnoreCase) && !r.CalledProceduresFunctions.Contains(g.Value))
                        r.CalledProceduresFunctions.Add(g.Value.Trim());
                }
            }
        }

        static void ExtractUserVariables(string sql, ProcedureAnalysisResult r)
        {
            var userVarMatches = Regex.Matches(sql, @"@\w+", RegexOptions.IgnoreCase);
            foreach (Match m in userVarMatches) if (!r.UserVariables.Contains(m.Value)) r.UserVariables.Add(m.Value);
        }

        static void ExtractTransactionControls(string sql, ProcedureAnalysisResult r)
        {
            var transMatches = Regex.Matches(sql, @"\b(COMMIT|ROLLBACK)\b", RegexOptions.IgnoreCase);
            foreach (Match m in transMatches) r.TransactionControls.Add(m.Value.Trim().ToUpper());
        }
        #endregion

        #region 核心优化：生成企业级Oracle代码（PACKAGE+权限）
        static string GenerateOracleEnterpriseCode(ProcedureAnalysisResult analysis)
        {
            var sb = new StringBuilder();

            // 1. 生成SEQUENCE（自增适配）
            if (analysis.AutoIncrementTables.Count > 0)
            {
                sb.AppendLine("-- === 1. SEQUENCE定义（适配MySQL自增ID）===");
                foreach (var tableName in analysis.AutoIncrementTables)
                {
                    SequenceInfo seq = CreateSequenceForTable(tableName);
                    sb.AppendLine($"CREATE SEQUENCE {seq.SequenceName}");
                    sb.AppendLine($"  START WITH {seq.StartWith}");
                    sb.AppendLine($"  INCREMENT BY {seq.IncrementBy}");
                    sb.AppendLine($"  NOCACHE NOCYCLE;");
                    sb.AppendLine($"CREATE OR REPLACE TRIGGER trg_{tableName}_{seq.ColumnName}");
                    sb.AppendLine($"  BEFORE INSERT ON {tableName}");
                    sb.AppendLine($"  FOR EACH ROW");
                    sb.AppendLine($"BEGIN");
                    sb.AppendLine($"  IF :NEW.{seq.ColumnName} IS NULL THEN");
                    sb.AppendLine($"    SELECT {seq.SequenceName}.NEXTVAL INTO :NEW.{seq.ColumnName} FROM DUAL;");
                    sb.AppendLine($"  END IF;");
                    sb.AppendLine($"END;");
                    sb.AppendLine("/\n");
                }
            }

            // 2. 生成临时表
            if (analysis.TempTables.Count > 0)
            {
                sb.AppendLine("-- === 2. 临时表定义（Oracle全局临时表）===");
                foreach (var tempTable in analysis.TempTables)
                {
                    string oracleCols = ConvertMySqlTempTableColsToOracle(tempTable.ColumnDefinitions);
                    sb.AppendLine($"CREATE GLOBAL TEMPORARY TABLE {tempTable.TableName} (");
                    sb.AppendLine($"  {oracleCols}");
                    sb.AppendLine(") ON COMMIT DELETE ROWS;");
                    sb.AppendLine();
                }
            }

            // 3. 生成PACKAGE规范（PACKAGE SPEC）：声明存储过程接口
            sb.AppendLine($"-- === 3. 包规范（{analysis.PackageName} SPEC）===");
            sb.AppendLine($"CREATE OR REPLACE PACKAGE {analysis.PackageName} AS");
            sb.AppendLine();
            sb.AppendLine($"  -- 存储过程接口声明（参数与原MySQL一致）");
            sb.AppendLine($"  PROCEDURE {analysis.ProcedureName}(");
            for (int i = 0; i < analysis.Parameters.Count; i++)
            {
                var p = analysis.Parameters[i];
                string oracleType = MapMySqlTypeToOracle(p.DataType, p.TypeLength);
                string mode = p.Mode switch { "IN" => "IN ", "OUT" => "OUT ", "INOUT" => "IN OUT ", _ => "IN " };
                string paramLine = $"    {mode}{p.Name} {oracleType}";
                if (i < analysis.Parameters.Count - 1) paramLine += ",";
                sb.AppendLine(paramLine);
            }
            sb.AppendLine($"  );");
            sb.AppendLine();
            sb.AppendLine($"  -- 可在此添加更多存储过程/函数接口（模块化扩展）");
            sb.AppendLine($"END {analysis.PackageName};");
            sb.AppendLine("/\n");

            // 4. 生成PACKAGE体（PACKAGE BODY）：实现存储过程逻辑
            sb.AppendLine($"-- === 4. 包体（{analysis.PackageName} BODY）===");
            sb.AppendLine($"CREATE OR REPLACE PACKAGE BODY {analysis.PackageName} AS");
            sb.AppendLine();
            sb.AppendLine($"  -- 存储过程实现");
            sb.AppendLine($"  PROCEDURE {analysis.ProcedureName}(");
            for (int i = 0; i < analysis.Parameters.Count; i++)
            {
                var p = analysis.Parameters[i];
                string oracleType = MapMySqlTypeToOracle(p.DataType, p.TypeLength);
                string mode = p.Mode switch { "IN" => "IN ", "OUT" => "OUT ", "INOUT" => "IN OUT ", _ => "IN " };
                string paramLine = $"    {mode}{p.Name} {oracleType}";
                if (i < analysis.Parameters.Count - 1) paramLine += ",";
                sb.AppendLine(paramLine);
            }
            sb.AppendLine($"  )");
            sb.AppendLine($"  IS");

            // 4.1 变量声明（包体内局部变量）
            foreach (var v in analysis.Variables)
            {
                string oracleType = MapMySqlTypeToOracle(v.DataType, v.TypeLength);
                sb.AppendLine($"    {v.Name} {oracleType}; -- MySQL原类型: {v.DataType}{(v.TypeLength.HasValue ? $"({v.TypeLength})" : "")}");
            }

            // 4.2 游标声明（包体内局部游标）
            if (analysis.Cursors.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 游标定义（包体内局部游标）");
                foreach (var cursor in analysis.Cursors)
                {
                    string oracleSelect = ConvertMySqlSelectToOracle(cursor.SelectQuery);
                    sb.AppendLine($"    CURSOR {cursor.CursorName} IS {oracleSelect};");
                    sb.AppendLine($"    {cursor.CursorName}_notfound BOOLEAN := FALSE;");
                }
            }

            sb.AppendLine();
            sb.AppendLine($"  BEGIN");

            // 4.3 批量INSERT处理（包体内逻辑）
            if (analysis.BatchInserts.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 批量插入（Oracle INSERT ALL 语法）");
                foreach (var batch in analysis.BatandroidchInserts)
                {
                    string finalColumns = batch.Columns;
                    List<string> finalValues = new List<string>();
                    if (analysis.AutoIncrementTables.Contains(batch.TableName))
                    {
                        string seqName = $"seq_{batch.TableName}_id";
                        if (!batch.Columns.Contains("id", StringComparison.OrdinalIgnoreCase编程客栈))
                            finalColumns = $"id, {batch.Columns}";
                        foreach (var row in batch.ValueRows)
                        {
                            string rowWithSeq = row.Replace("(", $"({seqName}.NEXTVAL, ");
                            finalValues.Add(rowWithSeq);
                        }
                    }
                    else
                    {
                        finalValues = batch.ValueRows;
                    }

                    sb.AppendLine($"    INSERT ALL");
                    foreach (var val in finalValues)
                    {
                        sb.AppendLine($"      INTO {batch.TableName} ({finalColumns}) VALUES {val}");
                    }
                    sb.AppendLine($"    SELECT 1 FROM DUAL;");
                }
            }
            // 4.4 游标循环处理
            if (analysis.Cursors.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 游标循环处理");
                foreach (var cursor in analysis.Cursors)
                {
                    if (string.IsNullOrEmpty(cursor.FetchInto)) continue;
                    sb.AppendLine($"    OPEN {cursor.CursorName};");
                    sb.AppendLine($"    {cursor.LoopLabel}: LOOP");
                    sb.AppendLine($"      FETCH {cursor.CursorName} INTO {cursor.FetchInto};");
                    sb.AppendLine($"      IF {cursor.CursorName}%NOTFOUND THEN");
                    sb.AppendLine($"        SET {cursor.CursorName}_notfound := TRUE;");
                    sb.AppendLine($"        EXIT {cursor.LoopLabel};");
                    sb.AppendLine($"      END IF;");
                    sb.AppendLine($"      -- 游标数据处理（原MySQL逻辑）");
                    sb.AppendLine($"    END LOOP {cursor.LoopLabel};");
                    sb.AppendLine($"    CLOSE {cursor.CursorName};");
                }
            }

            // 4.5 普通DML/SELECT处理
            sb.AppendLine();
            sb.AppendLine($"    -- 普通业务逻辑");
            foreach (var sel in analysis.SelectQueries)
            {
                string oracleSelect = ConvertMySqlSelectToOracle(sel.Sql);
                sb.AppendLine($"    {oracleSelect}");
            }
            foreach (var dml in analysis.DmlStatements)
            {
                if (!IsBatchInsert(dml, analysis.BatchInserts))
                {
                    string oracleDml = ConvertMySqlDmlToOracle(dml, analysis.AutoIncrementTables);
   javascript                 sb.AppendLine($"    {oracleDml}");
                }
            }

            // 4.6 事务控制
            foreach (var trans in analysis.TransactionControls)
            {
                sb.AppendLine($"    {trans};");
            }

            // 4.7 异常处理
            if (analysis.ExceptionHandlers.Count > 0 || analysis.Cursors.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 异常处理（含游标清理）");
                sb.AppendLine($"    EXCEPTION");
                sb.AppendLine($"      WHEN OTHERS THEN");
                foreach (var cursor in analysis.Cursors)
                {
                    sb.AppendLine($"        IF {cursor.CursorName}%ISOPEN THEN");
                    sb.AppendLine($"          CLOSE {cursor.CursorName};");
                    sb.AppendLine($"        END IF;");
                }
                foreach (var handler in analysis.ExceptionHandlers)
                {
                    string oracleException = ConvertMySqlHandlerToOracle(handler);
                    sb.AppendLine($"        {oracleException}");
                }
                sb.AppendLine($"        DBMS_OUTPUT.PUT_LINE('{analysis.ProcedureName} 异常：' || SQLERRM || '（行号：' || SQLCODE || '）');");
            }

            sb.AppendLine($"  END {analysis.ProcedureName};");
            sb.AppendLine();
            sb.AppendLine($"  -- 可在此添加更多存储过程/函数实现（模块化扩展）");
            sb.AppendLine($"END {analysis.PackageName};");
            sb.AppendLine("/\n");

            // 5. 生成权限分配语句（GRANT）
            sb.AppendLine($"-- === 5. 权限分配语句（控制访问权限）===");
            foreach (var role in analysis.GrantRoles)
            {
                string cleanRole = role.Trim();
                if (string.IsNullOrEmpty(cleanRole)) continue;
                // 授权包的执行权限
                sb.AppendLine($"GRANT EXECUTE ON {analysis.PackageName} TO {cleanRole};");
                // 若有临时表，授权临时表的操作权限
                foreach (var tempTable in analysis.TempTables)
                {
                    sb.AppendLine($"GRANT INSERT, UPDATE, DELETE, SELECT ON {tempTable.TableName} TO {cleanRole};");
                }
                // 若有自增表，授权表的操作权限
                foreach (var autoTable in analysis.AutoIncrementTables)
                {
                    sb.AppendLine($"GRANT INSERT, UPDATE, DELETE, SELECT ON {autoTable} TO {cleanRole};");
                }
                sb.AppendLine();
            }

            // 6. 生成调用示例
            sb.AppendLine($"-- === 6. 存储过程调用示例（包内调用）===");
            sb.AppendLine($"-- 调用格式：{analysis.PackageName}.{analysis.ProcedureName}(参数列表)");
            string callParams = string.Join(", ", analysis.Parameters.Select(p => 
            {
                if (p.Mode == "OUT") return "NULL /* OUT参数需用变量接收 */";
                return p.DataType switch 
                {
                    "INT" or "INTEGER" => "0",
                    "VARCHAR" or "VARCHAR2" => "'测试值'",
                    "DATE" or "DATETIME" => "SYSDATE",
                    _ => "NULL"
                };
            }));
            sb.AppendLine($"BEGIN");
            sb.AppendLine($"  {analysis.PackageName}.{analysis.ProcedureName}({callParams});");
            sb.AppendLine($"  COMMIT;");
            sb.AppendLine($"EXCEPTION");
            sb.AppendLine($"  WHEN OTHERS THEN");
            sb.AppendLine($"    ROLLBACK;");
            sb.AppendLine($"    DBMS_OUTPUT.PUT_LINE('调用异常：' || SQLERRM);");
            sb.AppendLine($"END;");
            sb.AppendLine("/");

            return sb.ToString();
        }

        #region 辅助方法（保持兼容，新增权限相关逻辑）
        static SequenceInfo CreateSequenceForTable(string tableName)
        {
            return new SequenceInfo
            {
                SequenceName = $"seq_{tableName}_id",
                TableName = tableName,
                ColumnName = "id",
                StartWith = 1,
                IncrementBy = 1
            };
        }

        static bool IsBatchInsert(string dml, List<BatchInsertInfo> batches)
        {
            foreach (var batch in batches)
            {
                if (dml.Contains($"INSERT INTO {batch.TableName}", StringComparison.OrdinalIgnoreCase) && dml.Contains("VALUES", StringComparison.OrdinalIgnoreCase))
                {
                    return true;
                }
            }
            return false;
        }

        static string ConvertMySqlDmlToOracle(string mySqlDml, List<string> autoTables)
        {
            string oracleDml = mySqlDml;
            foreach (var table in autoTables)
            {
                if (oracleDml.Contains($"INSERT INTO {table}", StringComparison.OrdinalIgnoreCase) && !oracleDml.Contains("id", StringComparison.OrdinalIgnoreCase))
                {
                    string seqName = $"seq_{table}_id";
                    oracleDml = Regex.Replace(oracleDml, @"(INSERT INTO \w+)\s*\(([^)]*)\)", $"$1 (id, $2)", RegexOptions.IgnoreCase);
                    oracleDml = Regex.Replace(oracleDml, @"VALUES\s*\(([^)]*)\)", $"VALUES ({seqName}.NEXTVAL, $1)", RegexOptions.IgnoreCase);
                }
            }
            oracleDml = Regex.Replace(oracleDml, @"NOW\(\)", "SYSDATE", RegexOptions.IgnoreCase);
            oracleDml = Regex.Replace(oracleDml, @"AUTO_INCREMENT", "/* 已通过SEQUENCE实现自增 */", RegexOptions.IgnoreCase);
            return oracleDml;
        }

        static string ConvertMySqlTempTableColsToOracle(string mySqlCols)
        {
            string[] colArray = mySqlCols.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries);
            List<string> oracleCols = new List<string>();
            foreach (string col in colArray)
            {
                var colMatch = Regex.Match(col.Trim(), @"([^\s]+)\s+([^\s(]+)(?:\((\d+)\))?", RegexOptions.IgnoreCase);
                if (colMatch.Success)
                {
                    string colName = colMatch.Groups[1].Value.Trim();
                    string mySqlType = colMatch.Groups[2].Value.Trim().ToUpper();
                    int? length = colMatch.Groups[3].Success ? int.Parse(colMatch.Groups[3].Value) : (int?)null;
                    string oracleType = MapMySqlTypeToOracle(mySqlType, length);
                    oracleCols.Add($"    {colName} {oracleType}");
                }
            }
            return string.Join(",\n", oracleCols);
        }

        static string ConvertMySqlSelectToOracle(string mySqlSelect)
        {
            string oracleSelect = mySqlSelect;
            var limitMatch = Regex.Match(oracleSelect, @"LIMIT\s+(\d+),\s*(\d+)", RegexOptions.IgnoreCase);
            if (limitMatch.Success)
            {
                int offset = int.Parse(limitMatch.Groups[1].Value);
                int count = int.Parse(limitMatch.Groups[2].Value);
                oracleSelect = Regex.Replace(oracleSelect, @"LIMIT\s+\d+,\s*\d+", "", RegexOptions.IgnoreCase);
                oracleSelect = $"SELECT * FROM (SELECT t.*, ROWNUM rn FROM ({oracleSelect}) t WHERE ROWNUM <= {offset + count}) WHERE rn > {offset}";
            }
            else
            {
                oracleSelect = Regex.Replace(oracleSelect, @"LIMIT\s+(\d+)", "WHERE ROWNUM <= $1", RegexOptions.IgnoreCase);
            }
            oracleSelect = Regex.Replace(oracleSelect, @"NOW\(\)", "SYSDATE", RegexOptions.IgnoreCase);
            oracleSelect = Regex.Replace(oracleSelect, @"CURDATE\(\)", "TRUNC(SYSDATE)", RegexOptions.IgnoreCase);
            return oracleSelect;
        }

        static string MapMySqlTypeToOracle(string mySqlType, int? length)
        {
            return mySqlType switch
            {
                "INT" or "INTEGER" => "NUMBER(10)",
                "SMALLINT" => "NUMBER(5)",
                "TINYINT" => "NUMBER(3)",
                "BIGINT" => "NUMBER(19)",
                "VARCHAR" or "VARCHAR2" or "CHAR" => length.HasValue ? $"VARCHAR2({length})" : "VARCHAR2(4000)",
                "TEXT" => "VARCHAR2(4000)",
                "LONGTEXT" => "CLOB",
                "DATETIME" => "TIMESTAMP",
                "TIMESTAMP" => "TIMESTAMP",
                "DATE" => "DATE",
                "BOOLEAN" or "BOOL" => "NUMBER(1)",
                "DECIMAL" or "NUMERIC" => length.HasValue ? $"NUMBER({length})" : "NUMBER",
                "FLOAT" => "BINARY_FLOAT",
                "DOUBLE" => "BINARY_DOUBLE",
                "BLOB" => "BLOB",
                "CLOB" => "CLOB",
                _ => length.HasValue ? $"VARCHAR2({length})" : "VARCHAR2(4000)"
            };
        }

        static string ConvertMySqlHandlerToOracle(string mySqlHandler)
        {
            if (Regex.IsMatch(mySqlHandler, @"EXIT\s+HANDLER\s+FOR\s+SQLEXCEPTION", RegexOptions.IgnoreCase))
            {
                string action = Regex.Match(mySqlHandler, @"FOR\s+SQLEXCEPTION\s+(.+?);", RegexOptions.IgnoreCase | RegexOptions.Singleline).Groups[1].Value;
                return $"{action};";
            }
            if (Regex.IsMatch(mySqlHandler, @"CONTINUE\s+HANDLER\s+FOR\s+NOT FOUND", RegexOptions.IgnoreCase))
            {
                string action = Regex.Match(mySqlHandler, @"FOR\s+NOT FOUND\s+(.+?);", RegexOptions.IgnoreCase | RegexOptions.Singleline).Groups[1].Value;
                return $"{action};";
            }
            return "DBMS_OUTPUT.PUT_LINE('未知异常');";
        }
        #endregion
        #endregion
    }
}

MySQL自增ID适配：自动生成Oracle SEQUENCE与触发器

核心逻辑

自增表识别：通过两种场景判断含自增ID的表：
1. CREATE TABLE 中含 AUTO_INCREMENT 关键字（如 id INT AUTO_INCREMENT ）；
2. INSERT 语句字段不含 id （默认 id 为自增字段，如 INSERT INTO users(name) VALUES(‘a’) ）。
SEQUENCE生成：按 seq_表名_id 规范生成序列（如 seq_users_id ），默认起始值1、步长1，配置 NOCACHE NOCYCLE 避免缓存浪费。
触发器自动关联：生成 trg_表名_id 触发器，在 INSERT 时自动为 id 字段赋值 SEQUENCE.NEXTVAL ，完全模拟MySQL自增行为。

示例转换

MySQL自增表创建：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

生成Oracle代码：

-- SEQUENCE定义（适配MySQL自增ID）
CREATE SEQUENCE seq_users_id
  START WITH 1
  INjavascriptCREMENT BY 1
  NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER trg_users_id
  BEFORE INSERT ON users
  FOR EACH ROW
BEGIN
  IF :NEW.id IS NULL THEN
    SELECT seq_users_id.NEXTVAL INTO :NEW.id FROM DUAL;
  END IF;
END;
/

MySQL批量INSERT优化：转换为Oracle INSERT ALL语法

核心逻辑

批量INSERT识别：提取MySQL中 INSERT INTO … VALUES(…),(…) 格式的批量语句（如 VALUES((1,‘a’),(2,‘b’)) ）。
语法转换规则：
1. 拆分行值：将 (1,‘a’),(2,‘b’) 拆分为 (1,‘a’) 和 (2,‘b’) ；
2. 自增表适配：若表含自增ID，自动在字段列表添加 id ，值列表添加 SEQUENCE.NEXTVAL ；
3. 生成 INSERT ALL ：通过 INSERT ALL INTO 表名(字段) VALUES(值) SELECT 1 FROM DUAL 实现批量插入（Oracle批量插入必须以 SELECT 1 FROM DUAL 结尾）。

示例转换

MySQL批量INSERT：

INSERT INTO users(name) VALUES('张三'),('李四'),('王五');

生成Oracle代码：

-- 批量插入（转换为Oracle INSERT ALL）
INSERT ALL
  INTO users (id, name) VALUES (seq_users_id.NEXTVAL, '张三')
  INTO users (id, name) VALUES (seq_users_id.NEXTVAL, '李四')
  INTO users (id, name) VALUES (seq_users_id.NEXTVAL, '王五')
SELECT 1 FROM DUAL; -- 必须添加SELECT 1 FROM DUAL

完整运行效果示例（含自增+批量INSERT）

输入MySQL存储过程：

CREATE PROCEDURE batch_add_users(IN dept_id INT)
BEGIN
  -- 批量插入3个用户
  INSERT INTO users(name, dept_id) VALUES
    ('张三', dept_id),
    ('李四', dept_id),
    ('王五', dept_id);
  
  -- 插入自增表（省略id）
  INSERT INTO logs(content) VALUES('批量添加用户');
  
  COMMIT;
END;

生成Oracle代码（关键部分）：

-- 1. SEQUENCE定义（适配MySQL自增ID）
CREATE SEQUENCE seq_users_id
  START WITH 1
  INCREMENT BY 1
  NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER trg_users_id
  BEFORE INSERT ON users
  FOR EACH ROW
BEGIN
  IF :NEW.id IS NULL THEN
    SELECT seq_users_id.NEXTVAL INTO :NEW.id FROM DUAL;
  END IF;
END;
/

CREATE SEQUENCE seq_logs_id
  START WITH 1
  INCREMENT BY 1
  NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER trg_logs_id
  BEFORE INSERT ON logs
  FOR EACH ROW
BEGIN
  IF :NEW.id IS NULL THEN
    SELECT seq_logs_id.NEXTVAL INTO :NEW.id FROM DUAL;
  END IF;
END;
/

-- 3. 存储过程 batch_add_users
CREATE OR REPLACE PROCEDURE batch_add_users(
       IN dept_id NUMBER(10)
)
IS
BEGIN

  -- 批量插入（转换为Oracle INSERT ALL）
  INSERT ALL
    INTO users (id, name, dept_id) VALUES (seq_users_id.NEXTVAL, '张三', dept_id)
    INTO users (id, name, dept_id) VALUES (seq_users_id.NEXTVAL, '李四', dept_id)
    INTO users (id, name, dept_id) VALUES (seq_users_id.NEXTVAL, '王五', dept_id)
  SELECT 1 FROM DUAL; -- 必须添加SELECT 1 FROM DUAL

  -- 普通业务逻辑
  INSERT INTO logs (id, content) VALUES (seq_logs_id.NEXTVAL, '批量添加用户');

  COMMIT;

  -- 异常处理（含游标清理）
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('异常：' || SQLERRM || '（行号：' || SQLCODE || '）');
END;
/
 ```

以上就是C#程序实现将MySQL的存储过程转换成Oracle的存储过程的详细内容，更多关于C# MySQL存储转换成Oracle存储的资料请关注编程客栈(www.devze.com)其它相关文章！

继续阅读：C# MySQL存储转换成Oracle存储 MySQL存储过程 Oracle存储过程

C#程序实现将MySQL的存储过程转换成Oracle的存储过程

目录

技术栈：

Program.cs（完整代码）

MySQL自增ID适配：自动生成Oracle SEQUENCE与触发器

核心逻辑

示例转换

MySQL批量INSERT优化：转换为Oracle INSERT ALL语法

核心逻辑

示例转换

完整运行效果示例（含自增+批量INSERT）

更多精彩内容

精彩评论

最新开发

基于WinForm实现通用自动更新系统的完整流程

C#程序实现将MySQL的存储过程转换成Oracle的存储过程

SpringBoot打包为外部配置包的技巧分享

SpringBoot外部化配置的最佳实践指南

使用Python将CSV文件转换为PDF的实践指南

开发排行榜

springboot后端存储富文本内容的思路与步骤(含图片内容)

PyCharm运行python测试,报错“没有发现测试”/“空套件”的解决

return base64.b64encode(b).decode(

基于C语言实现钻石棋游戏的示例代码

Sublime Text 3解决中文乱码问题（实测可用）