Menu
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.
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).
However I do not have DDL rights on the Db and hence cannot create the procedure.
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.
i.e. I want a procedure minus the create keyword, that would do my work, return the result and disappear.
Googled it a lot but couldn't find anything. :'( Just wondering if you folks know an elegant solution.
895
Creating a temporary procedure Like the temporary table, we can create temporary procedures as well. There are two types of temporary procedures, one is a local temporary stored procedure and another one is a global temporary procedure. These procedures are created in the tempdb database.
Two different sessions can have a temporary table with the same name. The temporary tables are useful when you want to use a small subset of the large table, and it is being used multiple times within a stored procedure.
In MySQL, the syntax of creating a temporary table is the same as the syntax of creating a normal table statement except the TEMPORARY keyword. Let us see the following statement which creates the temporary table: mysql> CREATE TEMPORARY TABLE table_name ( column_1, column_2, ..., table_constraints.
To create the MySQL Stored Procedure, open the MySQL workbench Connect to the MySQL Database copy-paste the code in the query editor window click on Execute. You can view the procedure under stored procedures.
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.
24
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)
I would consider using some ORM framework, but I am not a Java developer, so I can't recommend any of them.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
