I have a stored procedure that makes very complex joins and returns 1 row of data (LIMIT 1 is used). In my application I then use that data to make some calculations. This way I try to keep business logic out of stored procedures. But now I need to get that data in another stored function to also make some calculations. The reason I want to create the stored function is because it will be called in another query that returs thousands of rows.
我有一个存储过程,它使得非常复杂的连接并返回1行数据(使用LIMIT 1)。在我的应用程序中,我然后使用该数据进行一些计算。这样我就会尝试将业务逻辑保留在存储过程之外。但是现在我需要在另一个存储函数中获取该数据以进行一些计算。我想创建存储函数的原因是因为它将在另一个返回数千行的查询中调用。
Is it possible? I don't want to duplicate complex join logic in the stored function... The only solution I see is to use output parameters.
可能吗?我不想在存储的函数中复制复杂的连接逻辑...我看到的唯一解决方案是使用输出参数。
-----------------------------------
PS. I decided to explain you my situation, maybe you will offer me another decision.
----------------------------------- PS。我决定向你解释我的情况,也许你会给我另一个决定。
I need to calculate delivery duty paid price (it's final price, including packaging, delivering and customs clearance) of the goods. The calculation of this delivery duty paid price is a bit complex, and I don't want to keep complex logic in my stored procedures and functions. So I created a stored procedure that selects all the data necessary to make the calculation of the price and I use that data in my application to calculate the price. For now everything works good.
我需要计算货物的交货税价格(最终价格,包括包装,交货和清关)。计算这个交付税的付出代价有点复杂,我不想在我的存储过程和函数中保留复杂的逻辑。所以我创建了一个存储过程,选择了计算价格所需的所有数据,并在我的应用程序中使用该数据来计算价格。现在一切都很好。
But now I need to create a price-list with delivery duty paid prices and we have thousands of goods. So if I call my stored procedure for every peace of goods it will take thousands of round-trips (queries) to the server. That is why I want to create a function that will call the stored procedure and calculate the price based on returned data. And then I want to use it like this:
SELECT Description, blablabla, Weight, ..., GetDeliveryDutyPaidPrice(...) FROM pricelist;
但现在我需要创建一个具有交付税价格的价格表,我们有数千种商品。因此,如果我为每个货物的和平调用我的存储过程,它将需要数千次往返(查询)到服务器。这就是为什么我想创建一个函数来调用存储过程并根据返回的数据计算价格。然后我想像这样使用它:SELECT Description,blablabla,Weight,...,GetDeliveryDutyPaidPrice(...)FROM pricelist;
Any ideas?
1 个解决方案
#1
Instead of a stored procedure, make it a view. That should solve your problems.
而不是存储过程,使它成为一个视图。那应该可以解决你的问题。
EDIT:
To be precise, what you want to do is create a view which contains all of the columns which you use in your calculations. A view is similar to a stored procedure in that it allows the underlying DB engine to optimize for its performance, yet it can be queried the same way that a normal table can, using a WHERE clause. So your underlying implementation of your tables can have only the columns that each table needs (thereby making your schema simple), but the view can compose all of the different columns from the different tables into one single "table view" which can be queried using the WHERE clause. In this way, you can gain the benefits of stored procedures (optimizing your SQL joins for commonly used joins) and be able to filter your results appropriately (using the WHERE clause) while still keeping your logic out of your database.
确切地说,您要做的是创建一个视图,其中包含您在计算中使用的所有列。视图类似于存储过程,因为它允许底层数据库引擎针对其性能进行优化,但是可以使用WHERE子句以与普通表相同的方式查询它。因此,您的表的底层实现只能包含每个表所需的列(从而使您的模式简单),但视图可以将不同表中的所有不同列组成一个单独的“表视图”,可以使用它查询WHERE子句。通过这种方式,您可以获得存储过程的好处(优化常用连接的SQL连接),并能够适当地过滤结果(使用WHERE子句),同时仍然保持逻辑不在数据库中。
Really. Views are a very very very good thing. Use them.
真。意见是非常非常好的事情。使用它们。
#1
Instead of a stored procedure, make it a view. That should solve your problems.
而不是存储过程,使它成为一个视图。那应该可以解决你的问题。
EDIT:
To be precise, what you want to do is create a view which contains all of the columns which you use in your calculations. A view is similar to a stored procedure in that it allows the underlying DB engine to optimize for its performance, yet it can be queried the same way that a normal table can, using a WHERE clause. So your underlying implementation of your tables can have only the columns that each table needs (thereby making your schema simple), but the view can compose all of the different columns from the different tables into one single "table view" which can be queried using the WHERE clause. In this way, you can gain the benefits of stored procedures (optimizing your SQL joins for commonly used joins) and be able to filter your results appropriately (using the WHERE clause) while still keeping your logic out of your database.
确切地说,您要做的是创建一个视图,其中包含您在计算中使用的所有列。视图类似于存储过程,因为它允许底层数据库引擎针对其性能进行优化,但是可以使用WHERE子句以与普通表相同的方式查询它。因此,您的表的底层实现只能包含每个表所需的列(从而使您的模式简单),但视图可以将不同表中的所有不同列组成一个单独的“表视图”,可以使用它查询WHERE子句。通过这种方式,您可以获得存储过程的好处(优化常用连接的SQL连接),并能够适当地过滤结果(使用WHERE子句),同时仍然保持逻辑不在数据库中。
Really. Views are a very very very good thing. Use them.
真。意见是非常非常好的事情。使用它们。