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

时间:2022-01-27 16:49:12

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


11  

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


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中创建“存储过程”。编写一个方法,接受您的输入,执行查询和计算,然后返回数据集。

#1


11  

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


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中创建“存储过程”。编写一个方法,接受您的输入,执行查询和计算,然后返回数据集。