如何在MySQL中创建临时过程?

I am currently working on a portal application for a restaurant. The backend database used is Mysql and I am using Spring JDBC support for writing data access logic.

我目前正在为一家餐厅开发门户应用程序。使用的后端数据库是Mysql，我使用Spring JDBC支持编写数据访问逻辑。

Some of my DAOs are cluttered with multiple queries and are getting upto thousand lines of code in size. I think I can reduce the size and complexity by moving some of my logic into a SQL-PSM procedure (Since data binding and glue code in Java is an abomination from hell).

我的一些dao中充斥着多个查询，并且在大小上有上千行代码。我认为，通过将我的一些逻辑转移到SQL-PSM过程中(因为Java中的数据绑定和粘合代码是来自地狱的令人厌恶的东西)，我可以减少它的大小和复杂性。

However I do not have DDL rights on the Db and hence cannot create the procedure.

但是，我在Db上没有DDL权限，因此无法创建过程。

I was wondering if there is a way to create a discard-able\temporary procedure that you can execute directly like a DML statement without having to create it first.

我想知道是否有一种方法可以创建一个可丢弃的\临时过程，您可以像DML语句一样直接执行它，而不必首先创建它。

i.e. I want a procedure minus the create keyword, that would do my work, return the result and disappear.

例如，我想要一个过程减去create关键字，这将完成我的工作，返回结果并消失。

Googled it a lot but couldn't find anything. :'( Just wondering if you folks know an elegant solution.

在谷歌上搜了很多次，但什么也没找到。(我想知道你们是否知道一个优雅的解决方案。

2 个解决方案

#1

I guess you are looking for anonymous code blocks / anonymous procedures. Unfortunately it isn't supported in MySQL (it is supported in "big" RDBMSs such as Oracle or Sybase)

我猜你是在寻找匿名代码块/匿名程序。不幸的是，它在MySQL中不受支持(它在“大”rdbms(如Oracle或Sybase)中受到支持)

I would consider using some ORM framework, but I am not a Java developer, so I can't recommend any of them.

我将考虑使用ORM框架，但我不是Java开发人员，所以我不能推荐他们。

#2

I don't think you can. My recommendation would be to create the "stored procedure" in Java. Write a method that takes your inputs, performs queries and calculations, then returns the dataset.

我认为你不行。我的建议是在Java中创建“存储过程”。编写一个方法，接受您的输入，执行查询和计算，然后返回数据集。