20人参与 • 2025-10-18 • Asp.net
system, system.text.regularexpressions, system.text, system.text.json, system.collections.genericusing 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(),
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.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 游标循环处理
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);
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自增表创建：
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：
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
输入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存储的资料请关注代码网其它相关文章！
您想发表意见！！点此发布评论
版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。
发表评论