我可以在cfloop中调用存储过程，并在Coldfusion中输出动态输出参数吗？ [英] Can I call a stored procedure in a cfloop and output dynamic out-parameters in Coldfusion?

问题描述

今晚的最后一个问题，仍然使用Coldfusion8和MySQL。

我有一个产品表，每个都有价格A，B和C.我需要检索所有价格（A_min，A_max，B_min，B_max，C_min，C_max）中A，B，C的最小值和最大值

我想我会创建一个存储过程，通过A，B，C像这样循环：

 < cfloop list =A，B，Cindex =what delimiters =，> 
< cfstoredproc procedure =proc_search_select_minmaxdatasource =dtb> 
< cfprocparam type =invalue =＃what＃cfsqltype =cf_sql_varcharmaxlength =15> 
< cfprocparam type =invalue =＃variables.xxx＃cfsqltype =cf_sql_varcharmaxlength =13> 
< cfprocparam type =invalue =＃variables.yyy＃cfsqltype =cf_sql_varcharmaxlength =13> 
< cfprocparam type =invalue =＃variables.zzz＃cfsqltype =cf_sql_textmaxlength =4> 
< cfprocparam type =outcfsqltype =cf_sql_decimalvariable =＃what＃_min> 
< cfprocparam type =outcfsqltype =cf_sql_decimalvariable =＃what＃_max> 
< / cfstoredproc> 
< / cfloop> 
  

所以想法是为A，B和C运行三次，得到变量A_min，A_max ，B_min ...退出循环。

但我遇到了我的out参数的问题，在MySQL中，我声明如下：

  CREATE ... PROCEDURE`proc_search_select_minmax`（...，OUT`outputMin` DECIMAL（12,2），OUT`outputMax` DECIMAL（12,2））
 
 .... 
 SET outputMin = min（what）; 
 SET outputMax = max（what）; 
  

Coldfusion错误说：

 错误执行数据库查询
 @ 
< cfprocparam type =outcfsqltype =cf_sql_decimalvariable =＃what＃_min> 
< cfprocparam type =outcfsqltype =cf_sql_decimalvariable =＃what＃_max> 
  

问题：

我的输出参数与MySQL内部相同的名称或正确的顺序足够？

更重要的是，我可以像这样动态设置输出变量吗？

解决方案

我从来不喜欢变量返回的方式这样做。有用但通常很困难（取决于订单等）。

我有2条建议。

首先，让输出成为一个数据集。在你的存储过程中创建一个具有2列最小和最大值的临时表（#myMinMax或其他） - 用插入填充表，然后选择它作为< cfstoredprocresult ..& / code>

其次，我可能会创建一个存储过程，循环并返回一个整个数据集与类型列...所以你会结束了具有类型（如在A中）最小（如在10中）和最大（在100中）的数据集... A的一行，B的一行和C的一个。到数据源的单个连接可以返回数据集 - 避免3（或更多）数据库调用。

Last question for tonight, still using Coldfusion8 and MySQL.

I have a table with products, each with Price A, B and C. I need to retrieve the min and max values for A,B,C across all prices (A_min, A_max, B_min, B_max, C_min, C_max)

I thought I would create a stored procedure and loop through A,B,C like so:

  <cfloop list="A,B,C" index="what" delimiters=",">
    <cfstoredproc procedure="proc_search_select_minmax" datasource="dtb">
        <cfprocparam type="in" value="#what#" cfsqltype="cf_sql_varchar" maxlength="15">
        <cfprocparam type="in" value="#variables.xxx#" cfsqltype="cf_sql_varchar" maxlength="13">
        <cfprocparam type="in" value="#variables.yyy#" cfsqltype="cf_sql_varchar" maxlength="13">
        <cfprocparam type="in" value="#variables.zzz#" cfsqltype="cf_sql_text" maxlength="4">
        <cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_min">
        <cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_max">   
     </cfstoredproc>
  </cfloop>

So the idea was to run this three times for A,B and C and get variables A_min, A_max, B_min... out of the loop.

But I'm having trouble with my out-parameters, which inside MySQL, I'm declaring like:

 CREATE ... PROCEDURE `proc_search_select_minmax`(..., OUT `outputMin` DECIMAL(12,2), OUT `outputMax` DECIMAL(12,2))

 .... 
 SET outputMin = min(what);
 SET outputMax = max(what);

Coldfusion error says:

Error Executing Database Query
@
<cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_min">
<cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_max">

Questions:
Do I have to give my out parameters the same name as inside MySQL or is the correct order enough?
More importantly, can I set output variables dynamically like this? If not, are there any other ways except calling the stored procedure three separate times?

解决方案

I never liked the variable return way of doing this. Useful but often difficult (depends on order etc).

I have 2 suggestions for you.

First, make the output a data set. In your stored procedure create a temp table (#myMinMax or whatever) with 2 columns minimum and maximum - populate the table with an insert and then select it out returning it as a <cfstoredprocresult..>

Secondly I would probably create a stored proc that does the looping and returns a whole dataset with a "type" column ... so you would end up with a dataset having type (as in A) minimum (as in 10) and maximum (as in 100) ... one row for A, one for B and one for C. A single connection to the datasource could return this dataset for you - avoiding 3 (or more) DB calls.

这篇关于我可以在cfloop中调用存储过程，并在Coldfusion中输出动态输出参数吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！