存储过程不会返回任何结果 [英] Stored procedure isn't returning any results

问题描述

Hello Experts，



我在查询中，查询结果的每一行都应该有一个循环，以便将参数传递给外部Proc。这里没有任何错误。

Hello Experts,

I am Query in which there should be a loop for each row of the results from the Query to pass a parameter to external Proc. Here it is without any error.

BEGIN 

DECLARE v_BIN CHAR(10);
DECLARE RESULT INTEGER DEFAULT 0 ; 

FOR v_row AS 

    SELECT T2 . ICITEM , T2 . BABLOC , C . ISSIZE , D . UNITSPER 
    
    , E . NBRRCKBINB , F . ICDSC1 , F . ICDSC2 , T2 . TQOH , T2 . TQCM 
    
    , CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER ) 
    
    NBR_USED_RACKS 
    
    , E . NBRRCKBINB - ( CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER ) ) 
    
    NBR_OPEN_RACKS
    
    FROM ( 
    
    SELECT A . BACMP , A . BALOC , MIN ( A . BAITEM ) ICITEM 
    
    , A . BABLOC , INTEGER ( SUM ( A . BAQOH ) ) TQOH , 
    
    INTEGER ( SUM ( A . BAQCM ) ) TQCM 
    
    FROM TESTDATA . VINBINI A WHERE A . BALOC = '13' AND 1 = A . BACMP AND 
    
    A . BAQOH - A . BAQCM > 0 GROUP BY A . BACMP , A . BALOC , A . BABLOC HAVING 
    
    MIN ( A . BAITEM ) = MAX ( A . BAITEM ) 
    
    AND SUM ( A . BAQOH - A . BAQCM ) > 0 
    
    ) AS T2 
    
    , TESTDATA . PALITMLOC B , TESTDATA . VINITEMSIZ C , TESTDATA . PALSIZQTY D , TESTDATA . PALBINPF E 
    
    , TESTDATA . VINITEM F 
    
    WHERE T2 . BACMP = B . TACOMP AND T2 . ICITEM = B . ICITEM 
    
    AND T2 . BALOC = B . IALOC AND T2 . ICITEM = F . ICITEM 
    
    AND T2 . ICITEM = C . ISITEM 
    
    AND B . PALLETID = D . PALLETID 
    
    AND C . ISSIZE = D . ISSIZE 
    
    AND E . TACOMP = T2 . BACMP 
    
    AND E . IALOC = T2 . BALOC 
    
    AND E . IMBLOC = T2 . BABLOC 
    
    AND E . PALLETID = B . PALLETID 
    
        ORDER BY 1 , 2	 
 
    DO	 
	 	 
        SET v_BIN = v_row.BABLOC;	                                               
   	call TESTDATA.PARECR24SP (  '01' , '13  ',v_BIN ,RESULT) ;                               
END FOR;

END;

现在我希望在输出窗口中打印/显示结果。这里只说成功消息。



这对我来说真的是件大事。自从过去三天以来，我一直在撞墙。请帮助

Now I want the results to be printed/ shown in output window. Here it says just success message.

This is really a big thing now for me. I banging my head to the wall since last three days. please help

推荐答案

您创建的过程旨在遍历行并调用子程序。如果你想返回一个结果集，你需要以不同的方式设计它：

- 使用动态结果集

- 定义一个游标

- 打开一个游标

- 返回



查看示例1：调用返回单个结果集的存储过程 [ ^ ]



ADDITION



如果查询是否正确，问题是什么我真的不能说因为我对表格和逻辑都一无所知。



如果它会有语法问题，它们会显示为错误，但如果它有逻辑错误并返回错误的行或根本没有行，则由语句逻辑决定。



因为你需要将结果返回给主叫方，进行以下测试。这是原始查询的一部分，所以它应该返回一些有意义的东西。



我注意到的一件事是你错过了关键字CURSOR ......

The procedure you've created is designed to loop through the rows and call the sub program. If you want to return a result set you need to design it differently:
- use DYNAMIC RESULT SETS
- define a cursor
- open a cursor
- return

Have a look at the procedure definition in Example 1: Calling a stored procedure that returns a single result set[^]

ADDITION

What comes to the question if the query is correct or not I really can't say since I know nothing about the tables nor the logic.

If it would have syntactical problems they would show as errors but if it has logical errors and returns wrong rows or no rows at all, that's up to the statement logic.

Since you need to return the result to the calling side, have a test run with the following. That's part of your original query so it should return something meaningful.

One thing I noticed is that you were missing the keyword CURSOR...

CREATE PROCEDURE ResultTest () LANGUAGE SQL
 DYNAMIC RESULT SETS 1
 BEGIN
    DECLARE v_row CURSOR FOR 
    SELECT A.BACMP, 
           A.BALOC, 
           A.BAITEM,    
           A.BABLOC,
           A.BAQOH, 
           A.BAQCM
   FROM TESTDATA.VINBINI A 
   WHERE A.BALOC = '13' 
   AND   1 = A.BACMP 
   AND   A.BAQOH - A.BAQCM > 0;

    OPEN v_row;

    RETURN;
END

这篇关于存储过程不会返回任何结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！