快捷導(dǎo)航

C#程序?qū)崿F(xiàn)將MySQL的存儲(chǔ)過(guò)程轉(zhuǎn)換成Oracle的存儲(chǔ)過(guò)程

更新時(shí)間：2025年10月18日 11:48:36 作者：weixin_30777913

文章介紹了如何使用C#控制臺(tái)應(yīng)用,將MySQL自增ID和批量插入語(yǔ)句轉(zhuǎn)換為Oracle兼容的SEQUENCE、觸發(fā)器和INSERTALL語(yǔ)法,實(shí)現(xiàn)存儲(chǔ)過(guò)程自動(dòng)適配,提升數(shù)據(jù)庫(kù)遷移效率,需要的朋友可以參考下

技術(shù)棧：

.NET Core 3.1+ 或 .NET 5/6/7/8 控制臺(tái)應(yīng)用
使用 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
{
    // 數(shù)據(jù)結(jié)構(gòu)擴(kuò)展：新增包名配置、權(quán)限角色信息
    public class ProcedureAnalysisResult
    {
        public string ProcedureName { get; set; } = "";
        public string PackageName { get; set; } = "PKG_MYSQL_CONVERT"; // 默認(rèn)包名，可自定義
        public List<string> GrantRoles { get; set; } = new List<string> { "APP_USER", "ADMIN" }; // 默認(rèn)授權(quán)角色
        public List<ParameterInfo> Parameters { get; set; } = new List<ParameterInfo>();
        public string FunctionDescription { get; set; } = "請(qǐng)手動(dòng)補(bǔ)充：根據(jù)代碼邏輯歸納業(yè)務(wù)功能。";
        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>();
    }

    // 原有數(shù)據(jù)結(jié)構(gòu)保持不變
    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; } = "可能是賦值或返回結(jié)果集";
    }

    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 存儲(chǔ)過(guò)程轉(zhuǎn) Oracle 工具（企業(yè)級(jí)終極版） ===");
            Console.WriteLine("請(qǐng)輸入您的 MySQL 存儲(chǔ)過(guò)程代碼（可多行，以 ===END=== 結(jié)束輸入）：");

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

            // 允許用戶自定義包名和授權(quán)角色
            Console.WriteLine($"\n當(dāng)前默認(rèn)包名：{analysis.PackageName}，默認(rèn)授權(quán)角色：{string.Join(",", analysis.GrantRoles)}");
            Console.WriteLine("是否使用默認(rèn)配置？（輸入N修改，其他鍵使用默認(rèn)）：");
            string customConfig = Console.ReadLine()?.Trim().ToUpper();
            if (customConfig == "N")
            {
                Console.WriteLine("請(qǐng)輸入自定義包名（如PKG_USER_MANAGE）：");
                string customPkg = Console.ReadLine()?.Trim();
                if (!string.IsNullOrEmpty(customPkg)) analysis.PackageName = customPkg;

                Console.WriteLine("請(qǐng)輸入授權(quán)角色（多個(gè)用逗號(hào)分隔，如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=== 分析結(jié)果（結(jié)構(gòu)化 JSON） ===");
            Console.WriteLine(json);

            Console.WriteLine("\n=== 生成 Oracle 企業(yè)級(jí)代碼（含PACKAGE+權(quán)限） ===");
            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 原有提取方法（保持兼容，無(wú)修改）
        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(),
                                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 ? "賦值語(yǔ)句（INTO）" : "結(jié)果集查詢"
                });
            }
        }

        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 核心優(yōu)化：生成企業(yè)級(jí)Oracle代碼（PACKAGE+權(quán)限）
        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. 生成臨時(shí)表
            if (analysis.TempTables.Count > 0)
            {
                sb.AppendLine("-- === 2. 臨時(shí)表定義（Oracle全局臨時(shí)表）===");
                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規(guī)范（PACKAGE SPEC）：聲明存儲(chǔ)過(guò)程接口
            sb.AppendLine($"-- === 3. 包規(guī)范（{analysis.PackageName} SPEC）===");
            sb.AppendLine($"CREATE OR REPLACE PACKAGE {analysis.PackageName} AS");
            sb.AppendLine();
            sb.AppendLine($"  -- 存儲(chǔ)過(guò)程接口聲明（參數(shù)與原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($"  -- 可在此添加更多存儲(chǔ)過(guò)程/函數(shù)接口（模塊化擴(kuò)展）");
            sb.AppendLine($"END {analysis.PackageName};");
            sb.AppendLine("/\n");

            // 4. 生成PACKAGE體（PACKAGE BODY）：實(shí)現(xiàn)存儲(chǔ)過(guò)程邏輯
            sb.AppendLine($"-- === 4. 包體（{analysis.PackageName} BODY）===");
            sb.AppendLine($"CREATE OR REPLACE PACKAGE BODY {analysis.PackageName} AS");
            sb.AppendLine();
            sb.AppendLine($"  -- 存儲(chǔ)過(guò)程實(shí)現(xiàn)");
            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 變量聲明（包體內(nèi)局部變量）
            foreach (var v in analysis.Variables)
            {
                string oracleType = MapMySqlTypeToOracle(v.DataType, v.TypeLength);
                sb.AppendLine($"    {v.Name} {oracleType}; -- MySQL原類(lèi)型: {v.DataType}{(v.TypeLength.HasValue ? $"({v.TypeLength})" : "")}");
            }

            // 4.2 游標(biāo)聲明（包體內(nèi)局部游標(biāo)）
            if (analysis.Cursors.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 游標(biāo)定義（包體內(nèi)局部游標(biāo)）");
                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處理（包體內(nèi)邏輯）
            if (analysis.BatchInserts.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 批量插入（Oracle INSERT ALL 語(yǔ)法）");
                foreach (var batch in analysis.BatchInserts)
                {
                    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 游標(biāo)循環(huán)處理
            if (analysis.Cursors.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 游標(biāo)循環(huán)處理");
                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($"      -- 游標(biāo)數(shù)據(jù)處理（原MySQL邏輯）");
                    sb.AppendLine($"    END LOOP {cursor.LoopLabel};");
                    sb.AppendLine($"    CLOSE {cursor.CursorName};");
                }
            }

            // 4.5 普通DML/SELECT處理
            sb.AppendLine();
            sb.AppendLine($"    -- 普通業(yè)務(wù)邏輯");
            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);
                    sb.AppendLine($"    {oracleDml}");
                }
            }

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

            // 4.7 異常處理
            if (analysis.ExceptionHandlers.Count > 0 || analysis.Cursors.Count > 0)
            {
                sb.AppendLine();
                sb.AppendLine($"    -- 異常處理（含游標(biāo)清理）");
                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 || '（行號(hào)：' || SQLCODE || '）');");
            }

            sb.AppendLine($"  END {analysis.ProcedureName};");
            sb.AppendLine();
            sb.AppendLine($"  -- 可在此添加更多存儲(chǔ)過(guò)程/函數(shù)實(shí)現(xiàn)（模塊化擴(kuò)展）");
            sb.AppendLine($"END {analysis.PackageName};");
            sb.AppendLine("/\n");

            // 5. 生成權(quán)限分配語(yǔ)句（GRANT）
            sb.AppendLine($"-- === 5. 權(quán)限分配語(yǔ)句（控制訪問(wèn)權(quán)限）===");
            foreach (var role in analysis.GrantRoles)
            {
                string cleanRole = role.Trim();
                if (string.IsNullOrEmpty(cleanRole)) continue;
                // 授權(quán)包的執(zhí)行權(quán)限
                sb.AppendLine($"GRANT EXECUTE ON {analysis.PackageName} TO {cleanRole};");
                // 若有臨時(shí)表，授權(quán)臨時(shí)表的操作權(quán)限
                foreach (var tempTable in analysis.TempTables)
                {
                    sb.AppendLine($"GRANT INSERT, UPDATE, DELETE, SELECT ON {tempTable.TableName} TO {cleanRole};");
                }
                // 若有自增表，授權(quán)表的操作權(quán)限
                foreach (var autoTable in analysis.AutoIncrementTables)
                {
                    sb.AppendLine($"GRANT INSERT, UPDATE, DELETE, SELECT ON {autoTable} TO {cleanRole};");
                }
                sb.AppendLine();
            }

            // 6. 生成調(diào)用示例
            sb.AppendLine($"-- === 6. 存儲(chǔ)過(guò)程調(diào)用示例（包內(nèi)調(diào)用）===");
            sb.AppendLine($"-- 調(diào)用格式：{analysis.PackageName}.{analysis.ProcedureName}(參數(shù)列表)");
            string callParams = string.Join(", ", analysis.Parameters.Select(p => 
            {
                if (p.Mode == "OUT") return "NULL /* OUT參數(shù)需用變量接收 */";
                return p.DataType switch 
                {
                    "INT" or "INTEGER" => "0",
                    "VARCHAR" or "VARCHAR2" => "'測(cè)試值'",
                    "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('調(diào)用異常：' || SQLERRM);");
            sb.AppendLine($"END;");
            sb.AppendLine("/");

            return sb.ToString();
        }

        #region 輔助方法（保持兼容，新增權(quán)限相關(guān)邏輯）
        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", "/* 已通過(guò)SEQUENCE實(shí)現(xiàn)自增 */", 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適配：自動(dòng)生成Oracle SEQUENCE與觸發(fā)器

核心邏輯

自增表識(shí)別：通過(guò)兩種場(chǎng)景判斷含自增ID的表：
1. CREATE TABLE 中含 AUTO_INCREMENT 關(guān)鍵字（如 id INT AUTO_INCREMENT ）；
2. INSERT 語(yǔ)句字段不含 id （默認(rèn) id 為自增字段，如 INSERT INTO users(name) VALUES(‘a’) ）。
SEQUENCE生成：按 seq_表名_id 規(guī)范生成序列（如 seq_users_id ），默認(rèn)起始值1、步長(zhǎng)1，配置 NOCACHE NOCYCLE 避免緩存浪費(fèi)。
觸發(fā)器自動(dòng)關(guān)聯(lián)：生成 trg_表名_id 觸發(fā)器，在 INSERT 時(shí)自動(dòng)為 id 字段賦值 SEQUENCE.NEXTVAL ，完全模擬MySQL自增行為。

示例轉(zhuǎn)換

MySQL自增表創(chuàng)建：

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

生成Oracle代碼：

-- 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;
/

MySQL批量INSERT優(yōu)化：轉(zhuǎn)換為Oracle INSERT ALL語(yǔ)法

核心邏輯

批量INSERT識(shí)別：提取MySQL中 INSERT INTO … VALUES(…),(…) 格式的批量語(yǔ)句（如 VALUES((1,‘a’),(2,‘b’)) ）。
語(yǔ)法轉(zhuǎn)換規(guī)則：
1. 拆分行值：將 (1,‘a’),(2,‘b’) 拆分為 (1,‘a’) 和 (2,‘b’) ；
2. 自增表適配：若表含自增ID，自動(dòng)在字段列表添加 id ，值列表添加 SEQUENCE.NEXTVAL ；
3. 生成 INSERT ALL ：通過(guò) INSERT ALL INTO 表名(字段) VALUES(值) SELECT 1 FROM DUAL 實(shí)現(xiàn)批量插入（Oracle批量插入必須以 SELECT 1 FROM DUAL 結(jié)尾）。

示例轉(zhuǎn)換

MySQL批量INSERT：

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

生成Oracle代碼：

-- 批量插入（轉(zhuǎn)換為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

完整運(yùn)行效果示例（含自增+批量INSERT）

輸入MySQL存儲(chǔ)過(guò)程：

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

生成Oracle代碼（關(guān)鍵部分）：

-- 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. 存儲(chǔ)過(guò)程 batch_add_users
CREATE OR REPLACE PROCEDURE batch_add_users(
       IN dept_id NUMBER(10)
)
IS
BEGIN

  -- 批量插入（轉(zhuǎn)換為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

  -- 普通業(yè)務(wù)邏輯
  INSERT INTO logs (id, content) VALUES (seq_logs_id.NEXTVAL, '批量添加用戶');

  COMMIT;

  -- 異常處理（含游標(biāo)清理）
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('異常：' || SQLERRM || '（行號(hào)：' || SQLCODE || '）');
END;
/
 ```

以上就是C#程序?qū)崿F(xiàn)將MySQL的存儲(chǔ)過(guò)程轉(zhuǎn)換成Oracle的存儲(chǔ)過(guò)程的詳細(xì)內(nèi)容，更多關(guān)于C# MySQL存儲(chǔ)轉(zhuǎn)換成Oracle存儲(chǔ)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: